Questions tagged [mysql-8.0]
MySQL version 8.0 - please also tag with mysql for search purposes.
254
questions
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 ...
