Questions tagged [mysql-8.0]

MySQL version 8.0 - please also tag with mysql for search purposes.

Filter by
Sorted by
Tagged with
1
vote
1answer
17 views

Make sure date of departure is different than date of arrival?

I have the following tables: Flights (id_flight) Airports (id_airport) Departures_Arrivals (id_flight, id_airport), which is the Junction Table between Flights and Airports and it has the following ...
0
votes
0answers
23 views

MYSQL storage increasing unexpectedly (RDS)

I am total newbie to database game. & I am using mysql 8.0 RDS 20GB free tier. I don't have much data on it.(just 300 Mb) But I always get a mail that you have exceeded 85% of storage limit.(...
0
votes
1answer
14 views

Methods for tracking processing time for long running ADD INDEX call in MySQL

I've set off index creation on a very large table in MySQL and while I expected it to take a long time, I'm 5 days in and wondering if there's any way to debug potential issues or simply let it run. ...
0
votes
0answers
14 views

How to “catchup” data after a backup

I need to transfer a database to another server with hopefully 0 downtime. I have Percona Mysql 8 on the destination and Mariadb on the source. I have successfully used Percona XtraBackup to create ...
0
votes
0answers
11 views

MySQL 8 vs MySQL 5: Why wildcards misbehave in GRANTs command?

I came across with one of my old projects (using MySQL 5 and PHP 5.5). I've just wanted to test that project using MySQL 8 and PHP7.0. To install the DB i normally do: ... mysql -u root -p < ...
0
votes
1answer
22 views

How to make MySQL 8 alert when integer overflow happens during INSERT … ON DUPLICATE KEY?

Let's consider we have a simple table with auto-incrementing integer ID and some unique column: CREATE TABLE `test` ( `id` int unsigned NOT NULL AUTO_INCREMENT, `value` tinyint DEFAULT NULL, ...
0
votes
0answers
9 views

Syntax error when update root password

I forgot MySQL root password so I am trying to change it by starting it in safe mode and running following command but getting syntax error. MySQL version is 8. What is the correct syntax to update ...
0
votes
0answers
12 views

SQL Polybase - Connect to MySQL ODBC - Issue with decimal data type

hope someone has a clue about this... I'm using SQL 2019 Polybase to connect to a MySQL 8 Server using an ODBC (MySQL 8.0.20 ODBC Driver). CREATE EXTERNAL DATA SOURCE eds_test1 WITH ( LOCATION = '...
1
vote
0answers
20 views

MySQL condition filtering estimate while planning includes `A.col op B.col` but excludes `A.col op val or B.col op val`

In the condition filtering feature that MySQL introduced from version 5.7, has filter estimates for various condition formats in the join order, to estimate the number of rows that enter the join ...
1
vote
1answer
48 views
+200

Generating a password that matches the Password Validation Component

MySQL 8 has a pretty sophisticated "Password Validation Component" (this replaces the validate_password plugin). It has a lot of options, validate_password.number_count validate_password....
0
votes
1answer
35 views

Is Oracle 18c XE good enough to start learning to be a DBA? [closed]

I don't know if the esteemed community here will find this question silly, but at the same time I thought that I would get a good answer to my question here. As of now, I am into writing SQL queries/...
1
vote
1answer
30 views

Transferring innodb table

I have a 30GB innoDB-only database that keeps having Lost connection to MySQL server during query when dumping when running mysqldump -u root -p --compress --max_allowed-packet=1G --net-buffer-...
1
vote
2answers
100 views

Performance issues after upgrading to MySQL 8

UPDATE (tl;dr;): I filed a bug report here: https://bugs.mysql.com/bug.php?id=99593 which has since been acknowledged and a workaround provided. See answer below for details. Certain queries seem to ...
-1
votes
1answer
33 views

Out of sort memory, consider increasing server sort buffer size after upgrading to MySQL 8

I recently upgraded a server to MySQL 8.0.20 from 5.7.30 and encountered this error on a query where it had never come up in the past. It seems to be related somehow to the inclusion of ...
0
votes
1answer
18 views

How prepare and transfer database from mysql 5.7 to 8.0.19 since mysql_upgrade has been removed

I noticed that the mysql_upgrade script is no longer available with mysql 8.0.16, and I'm running 8.0.19, so after creating a database backup from version 5.7 and imported to the version 8.0.19. So, ...
0
votes
0answers
11 views

How to take single database backup using mysqlbackup command in Mysql 8.0.18 version DB

I have a mysql instance(8.0.18-commercial MySQL enterprise server) with 8 databases in it and out of those I only need to take the backup of DB named 'Test1'. Please note that its 300 GB sized DB ...
0
votes
1answer
12 views

confusing error when providing a user defined function with an invalid enum value

I have a function thats called GET_TRANSLATION and it can return different results based on different select queries based on the enum value it is provided. Here is the full code: CREATE FUNCTION ...
0
votes
1answer
17 views

How can I monitor the import progress of a large CSV File in mysql?

I am trying to import an existing CSV file into my database and I want to see the progress. I usually use pipe viewer when I do my sql files but for csv files I cannot figure out how to do it. I use ...
0
votes
2answers
34 views

DataGrip Issue Load Data Local Infile

I am having trouble using the LOAD DATA LOCAL INFILE statement in Datagrip. Suppose I have source_data.csv such as: rating,statement -2,"I'm hungry!" 5,"I'm satisfied." 1,"Ok, but could be better." ...
1
vote
1answer
21 views

Scripting the setting up of a MySQL database - best practices?

I am a relative newbie to database administration. I am setting up my first database on my localhost in MySQL and want to understand best practices. This is my current workflow: # do_everything.sql ...
0
votes
0answers
20 views

MySQL 8 - Optimize query using ST_Contains or ST_Within

I have a table called locations with ~70000 records which includes a geometry column of points and a spatial index as follows: UPDATE locations SET geom = ST_GeomFromText(CONCAT( 'POINT(', latitude, '...
1
vote
2answers
39 views

Database insanely (and weirdly inconsistently) slow on 8.0

I recently tried moving a large DB to 8.0, and am seeing insanely bad speeds (100x slower in some cases). I've spent the last 2 days googling this and feel like I've tried everything and have not had ...
1
vote
1answer
16 views

Grant privileges to all users on a single table

Is it possible to grant privileges (such as SELECT, INSERT, UPDATE, etc) to all the users for a specific table at the same time? Here is what I tried but it didn't work out... GRANT SELECT ON ...
1
vote
2answers
30 views

SQL case statement not executing else part

Problem statement: Create a function to search a student with roll_no; if exists show "Student Name and Course" else show "Roll No not exist". CREATE FUNCTION search(roll_num VARCHAR(50)) RETURNS ...
1
vote
1answer
24 views

Show all rows even when count is null

I want to see all the rows from one table and then do a count on the second table to see how many match, but want to see it all even if there are 0 matches in the second table. My query: SELECT a....
0
votes
0answers
41 views

In 8.0.17 EPSG Spatial reference systems are not available at installation? [duplicate]

In 8.0.17 INFORMATION_SCHEMA.ST_SPATIAL_REFERENCE_SYSTEMS is not prepopulated with ORGANIZATION="EPSG" records, leaving the table with only a single record, that for the default SRID=0. A count in ...
0
votes
1answer
28 views

MySQL How to Script to create indexes on all date columns in database

I have a lot of tables in the database that are using a datetime stamp - I need to create a script that will create indexes on all columns in each table that does not already have an index . So far I ...
0
votes
1answer
26 views

Creating stored procedure in MySQL-8

I am trying to create a SP which will run batch update on about 100 million rows in a table. I created the below SP for it: delimiter // CREATE PROCEDURE `bulk_update`(IN table_name varchar(30)) ...
0
votes
0answers
6 views

How to install Percona Server 8.0 at Ubuntu 19.04 / 19.10?

I have 2 Ubuntu servers with OS versions 19.04 (disco) and 19.10 (eoan). Running latest MySQL 8.0 on both servers. I would like to try out Percona Server to see if it gives any speed boost as it ...
0
votes
0answers
12 views

Recover databases (DB files are intact) from mysql sever 5.7 on 8.0.10 (ubuntu 20.04)

I have recently upgraded from 18.04.3 LTS to 20.04 ubuntu LTS release. 20.04 comes with 8.0.19 version, so upon upgrade, the mysql servcer could not start. First there were problems with sdl_mode, ...
0
votes
0answers
16 views

Change Table format from within MySQL 8?

Is there a mysql command to change the output format of a table? Specifically, I'd like to get rid of the formatting '-'s and '+'s: +------+------------+ | ID | Name | +------+------------+ | ...
0
votes
0answers
190 views

DBeaver: Access denied for user 'root'@'localhost' (using password: YES)

Context: I am creating a new Schema and looking to import .sql file generated from mysql dump. I have server running: MySQL 8.0.19. I am using DBeaver client Version 7.0.2 and have setup a new MySQL ...
0
votes
0answers
27 views

Mysql8 ST_GeomFromGeoJSON(geo_json) throws error Longitude -180.000000 is out of range in function st_geomfromgeojson

I have a table with polygons stored as geojson. CREATE TABLE `location_boundaries` ( `id` INT UNSIGNED NOT NULL, `name` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci', `...
0
votes
0answers
55 views

Calculating historical data over date ranges for up to 1 million rows

I need to be able to calculate the average sales of employees over a period of time. At the moment, I am doing this by using MySQL to get the total sales per employee per day: SELECT ANY_VALUE(...
0
votes
0answers
20 views

How to configure MYSQL 8 as case insenstive on ubuntu 19.10

I am using MySQL 8 in ubuntu 19.10 and I want to set lower_case_table_names = 1 in /etc/mysql/my.cnf file. But after adding this line server fails to start. I have followed various solutions like ...
1
vote
1answer
26 views

How to measure amount of MySQL 8 INNODB table used to store blobs?

I'm trying to understand a discrepancy in the file size on disk of my tables and the size of the information stored in them. I'm using MySQL 8 hosted on RDS (InnoDB, one file per table), so getting ...
0
votes
0answers
26 views

MySQL 8: Most performant query to obtain latest version of each record

We have a database with over 7 million entries, and we are struggling to find the best SQL query to obtain the latest entries for each a & b & c combination on each version. For instance: ...
1
vote
1answer
18 views

Can a MySQL 5.6 master replicate to a 5.7 slave, which in turn replicates to a MySQL 8 slave?

According to the docs, "MySQL supports replication from one release series to the next higher release series. For example, you can replicate from a master running MySQL 5.6 to a slave running MySQL 5....
0
votes
1answer
30 views

Get the id of multiple inserted/updated rows

I insert my _as_ci accent sensitive, case-insensitive rows. Tail is unique, id is auto_increment. INSERT INTO tails (tail) VALUES ('Hello'), ('cafe'), ('Café') ON DUPLICATE KEY UPDATE tail = VALUES(...
1
vote
1answer
167 views

DBeaver Error 1146 (42S02) on SQL database restore- Table 'acumatica.AUNotification' doesn't exist [closed]

I am trying to restore a database dump from a file for an online SQL course I'm taking into DBeaver. When I restore it, however, it runs for a little bit and then I get the error message ERROR 1146 (...
1
vote
1answer
19 views

MySQL: Unable To Create Generated Column With Expression That Uses DATE_ADD() Function

There might be a really obvious solution to my question, but I am stuck and did not manage find out what I am doing wrong, so you guys might tell me. I am not able to create a nullable stored column ...
2
votes
2answers
166 views

How import mysqldump .sql file into MySQL 5.7 when exported from MySQL 8

I accidentally installed MySQL 8 and did a ton of work there, creating a lot of data. I then backed up all of the databases using mysqldump. Later, I realized that I needed to downgrade back to MySQL ...
0
votes
1answer
39 views

Invalid default value for DATETIME column?

I'm trying to execute my DDL. I'm using docker-compose. version: '3.3' services: mysql: image: mysql:latest # command: ['mysqld', '--character-set-server=utf8mb4', '--collation-server=...
0
votes
2answers
30 views

Significant processing time creating indexes on a large MySQL table

I'm creating indexes on a few tables I've recently created within MySQL. The first (call below) completed within ~20 minutes and was applied to a table with a little over 20M records: mysql> ...
0
votes
1answer
44 views

Creating MySQL table with 100+ columns

I have a dataset to be loaded to MySQL that contains 100+ columns of assorted data items. I'd planned to pivot the dataset to be row-oriented in the vein of RDBMS best practice but absent a PIVOT ...
0
votes
1answer
30 views

Adding indexes to very large tables in MySQL

I have a very large table (600M+ records, 260G of data on disk) within MySQL that I need to add indexes to. I'd expected to add them directly, but doing some searching and some recommend creating a ...
0
votes
2answers
84 views

Lock table size issue when deleting on MySQL

I'm attempting to delete errant records from a table based on certain dates and running into the following error: mysql> delete from myTable where dataDate in ('2011-07-12'); ERROR 1206 (HY000): ...
0
votes
0answers
37 views

mysql 8 ndbcluster distributed privileges

I've setup a mysql 8 ndbcluster but cannot get the distributed privileges working. following how to setup distributed privileges using NDB_STORED_USER (https://dev.mysql.com/doc/refman/8.0/en/mysql-...
1
vote
1answer
23 views

What are these Errors in a Previously Successful MySQL Script?

Several years ago I began work on a project that uses MySQL for the database. Much of the development happened prior to MySQL 8, but I last deployed the project on an early version of MySQL 8. And, ...
0
votes
0answers
52 views

Loading 260GB worth of CSV into MySQL in Linux

I'm bulk loading a few thousand CSVs totaling ~260GB and ~4.5 billion rows to MySQL using mysqlimport calls on each CSV executed within a bash script. The MySQL installation and the data itself are ...

1
2 3 4 5 6