Questions tagged [mysql-8.0]
MySQL version 8.0 - please also tag with mysql for search purposes.
566
questions
29
votes
1
answer
14k
views
What happened to MySQL 6 & 7?
With the recent release of MySQL 8, I can't seem to find any information about why version numbers 6 and 7 were skipped. Anyone know?
- 405
26
votes
3
answers
27k
views
Why did MySQL remove the query cache feature after version 8.0?
Why did MySQL remove the query cache feature after version 8.0?
- 407
25
votes
5
answers
43k
views
Table mysql/innodb_index_stats has length mismatch in the column name table_name. Please run mysql_upgrade
In mysqld.log I have thousands of lines like:
Table mysql/innodb_index_stats has length mismatch in the column name table_name. Please run mysql_upgrade
So, according to virtually every post on the ...
- 449
13
votes
6
answers
14k
views
Disable Binary Logs in MySQL 8.0
How can I disable binary logging on MySQL 8.0?
I am needing to import a large MySQLDump file, and don't want to fill the Binary logs with many GB of data. In the past I would have set log_bin=0 in ...
- 2,046
9
votes
2
answers
4k
views
Mysql 8 ST_GeomFromText giving error Latitude out of range in function st_geomfromtext. It must be within [-90.000000, 90.000000]
I am trying the following insert query on mysql 8.
Insert Into fence Set
fenceName='aa',
radius=2,
fenceGeometry=ST_GeomFromText('POINT(...
- 197
9
votes
3
answers
12k
views
Extreme performance loss due to moving from Mysql 5.7.23 to Mysql 8.0 (status, config included) [closed]
Been sitting on this for 12 hours straight (it's noon now, so I will read/respond when I woke up again).
I have made the big fault of recommending a database upgrade of our productive environment to ...
- 402
8
votes
1
answer
17k
views
MySQL event scheduler waiting on empty queue since server restarted 12 days ago
I noticed a process on the server which has been running for more than 12 days, which I think coincides with the last time MySQL was restarted.
mysql> SHOW FULL PROCESSLIST;
+---------+------------...
- 183
8
votes
3
answers
6k
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 ...
- 327
7
votes
2
answers
10k
views
My disk is full of binlog files
I just realized that my server is growing by 2 GB per day, which is not normal.
I looked at my folder and the folder / var / lib / mysql reached 26 GB
Normally it should be about 4 GB
A month ago I ...
- 83
7
votes
2
answers
2k
views
Are enums still evil (in MySQL 8.0)?
So I have a disagreement with a coworker about using enums vs lookup tables.
This very old article from 2011 and MyQL5.5 still gets referenced again and again by those not liking enums.
But we are ...
- 173
7
votes
1
answer
11k
views
I can't start mysqld.service -> status : Data Dictionary upgrade from MySQL 5.7 in progress in centos 7
I've installed mysql 8 on centos 7
and when i want to start the mysqld service , it says :
Job for mysqld.service failed because the control process exited with error code. See "systemctl status ...
- 173
6
votes
2
answers
6k
views
Upgrade all MySQL columns, tables, and databases from utf8mb3 to utf8mb4
I have thousands of columns across hundreds of tables in about a hundred databases inside a MySQL instance that need to be upgraded from utf8mb3 to utf8mb4. Is there a way to generate ALTER ...
5
votes
2
answers
3k
views
Why is this MySQL query taking so long?
I'm looking to do a relatively simply filter from one table called prices.
prices is a very large table (~2G records). Sample data, query and indexes included below. I realize the table being ...
- 181
5
votes
1
answer
8k
views
How do I create a point with an SRID in MySQL?
MySQL supports using ST_PointFromText to construct a point with an SRID,
ST_PointFromText(wkt[, srid [, options]])
Constructs a Point value using its WKT representation and SRID.
This ...
- 60.5k
5
votes
2
answers
2k
views
MySQL crashing at about 70k open files
Server running community 8.0.14, we hit the number of open files a few days ago, so I increased the limit but after a while the server crashed with this in the error log:
InnoDB: We intentionally ...
- 352
5
votes
1
answer
7k
views
MySQL restore lost data after update from 5.7 -> 8.0.11
I have a docker-compose file where I use the official mysql:latest image as my database server.
After a docker pull mysql and a docker-compose up the image updated from 5.7 -> 8.0.11
This update ...
- 191
5
votes
1
answer
3k
views
MySQL Workbench mysqldump: 'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces
When using workbench on Mysql 8, I get the following error:
mysqldump: Error: 'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces
I'...
- 151
4
votes
2
answers
871
views
Is using strings as keys for reference to other tables bad in terms of memory usage?
So i have to deal with the following scenario:
I have a table , users , which holds all of the users of my application .
Each user has an email which is unique and thus could be the primary key of ...
- 43
4
votes
2
answers
29k
views
How to set lowercase_table_name
I've been trying to setup lower_case_table_names for ages in MySQL 8.0 without success. What is the procedure to set this up in 8.0?
In previous versions of mysql I was able to override the variable ...
- 91
4
votes
1
answer
789
views
Why is the Innodb Memcached plugin being deprecated in MySQL 8.0.22?
I originally used "the handlersocket" plugin for NoSQL access to InnoDB tables, then it eventually couldn't be built on newer versions so I migrated to the "Memcached" plugin which ...
- 506
4
votes
1
answer
1k
views
Why 0/false returns true when using JSON_EXTRACT in MySQL
I have a condition to select a JSON document value that is falsy.
My rows:
1. {"my_value": false}
2. {"my_value": 0}
3. {"my_value": "0"}
4. {"my_value&...
- 227
4
votes
2
answers
6k
views
Can't set log_bin_trust_function_creators=1 on machine startup - MySQL 8.0
I'm trying to put the log_bin_trust_function_creators=1 parameter into the my.cnf configuration file to set the variable correctly on startup on a machine with Ubuntu 20.04 LTS. But every time I save ...
- 41
4
votes
2
answers
843
views
Does MySQL 8 ASCII vs utf8mb4_0900_ai_ci size differ when only using ASCII characters?
If I only use only ASCII characters, will VARCHAR (255) with utf8mb4_0900_ai_ci be larger on disk than VARCHAR (255) using ASCII?
- 177
4
votes
2
answers
15k
views
How to select all columns plus one alias column in MySQL
It is a simple question, but i could not find the answer.
I want to select all the columns in my table with a SELECT * statement, but i also need to alias a UUID column this way: BIN_TO_UUID(ID) as ...
- 233
4
votes
2
answers
461
views
Unable to use result of a "subquery in select clause" in a "insert.. select.. on duplicate update" query
Possible Unanswered Duplicate Question: Insert select - using selected value in ON DUPLICATE KEY UPDATE
I am not able to use a subquery result column in the select clause as a value to update in a ...
- 207
4
votes
1
answer
393
views
MySQL 8.0 Replication filters lost between restarts
I don't know if I fell on a bug or a feature, I read the documentation several times but haven't found the information.
I have two MySQL servers, both on 8.0.15 and hosted on a Windows Server (x64). ...
- 143
4
votes
2
answers
111
views
Incorrect database name `` after mysql@8 upgrade
After MySQL upgrade we ran into a breaking change as we have some really strange queries like this one:
use somedatabase;
SELECT * FROM `Table` where ``.`Table`.`id` = 1
With MySQL@5 this query runs ...
- 143
4
votes
2
answers
3k
views
Install mysql-shell from apt repository
I want to install and configure a high availability cluster for Mysql 8. To automate that, I want to use use apt repository (via ansible).
My servers are debian jessy (8) distribution.
I succesfully ...
- 151
4
votes
1
answer
1k
views
Query works manually, not from stored procedure - Safe update error
I have a relatively simple update query that works fine when I manually execute it from a Mysql Workbench query window, but will not work when I call it as a stored procedure. The stored procedure in ...
4
votes
0
answers
262
views
MySQL replication - Follower stuck and behind
I understand there are lots of similar problems and threads but non of those solution helped me. Please read through the thread I will try to give as much information as possible:
I have setup a ...
- 141
4
votes
0
answers
628
views
How to get the Slave commit timestamp using MySql replication?
I've been looking for ways to collect information about the replication delays on MySql 8. I found an alternative solution, but I imagine there's a more straightforward way to do that.
By reading its ...
- 41
3
votes
4
answers
9k
views
mysql 8.0, updates are sometimes stalling
Update: The scope of this question does not cover basic performance tweaks and considers these are done already. So if you are happy to share some evident variables like innodb_io_capacity, ...
- 208
3
votes
3
answers
5k
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 ...
3
votes
2
answers
726
views
Why MySQL 8 doesn't return any rows when using a table with a descending PRIMARY KEY, 2 indexes, and valid join conditions with existing rows
Using 2 tables (A & B), joinable by their PK, with the PRIMARY KEY of A being sorted DESC (PK in descending order as it's allowed by MySQL 8), and A having 2 indexes in ASC ordering.
When I ...
- 209
3
votes
2
answers
2k
views
Having charset "utf8mb4" - does it mean every character takes 4 bytes, or only those that need 4 bytes?
I have a VARCHAR(80) utf8mb4 column, and I'm testing adding some ASCII & Emoji characters, and use LENGTH() and CHAR_LENGTH() to understand the differences.
From reading in different places, my ...
- 758
3
votes
2
answers
614
views
How to store values that include numbers and strings? Example: 1.25, trace, not applicable, etc
I created a database for nutritional table information. This database have values like carbohydrates, protein and fats of a food (there is a total of 28 fields).
The problem is that there are special ...
- 233
3
votes
1
answer
396
views
Why does REGEXP_REPLACE concatenate with a previous result?
This looks like strange behaviour, and I get the same on the MySQL command line as via db<>fiddle:
create table t(foo varchar(255));
insert into t (foo) values
('the quick brown fox'),
('jumped ...
- 39k
3
votes
2
answers
507
views
MySQL 8 - Access denied when dropping procedures
On our MySQL 8.0.23 server, when a user tries to drop a procedure created by another user they get the following error:
Error Code: 1227
Access denied; you need (at least one of) the SYSTEM_USER ...
- 133
3
votes
1
answer
5k
views
Why am I not able to add a user?
I'm running command grant all privileges on *.* to 'username'@localhost identified by 'strong password';, but I get the error:
ERROR 1064 (42000): You have an error in your SQL syntax; check the ...
3
votes
2
answers
476
views
mysql takes too long to just add a column
MySQL took 4.5 minutes to just add a column to a table having 150,000 rows.
Is there anything wrong in it or this is just normal. Is there any other alternative to fasten things?
ALTER TABLE job_posts
...
3
votes
1
answer
764
views
MySQL 8.0 Metadata Lock Extension for Foreign Keys causing requests to hang
We are using AWS RDS and we upgraded from MySQL 5.6.41 to MySQL 8.0.21 last December.
Since mid-January, we noticed some problems causing our replica to be stuck after running migrations adding ...
- 135
3
votes
1
answer
845
views
Mysql get records more then 3 in interval of 1 minute, return group_concat ID
Currently i have this dataset, i need to return grouped ids that are within the range of 60 seconds and have more than 3.
CREATE TABLE test
(
`id` bigint NOT NULL AUTO_INCREMENT,
created_date ...
- 239
3
votes
1
answer
482
views
Testing MySQL adaptive_hash_index option
We recently migrated our application to an entirely new infrastructure, going from a single server managing everything to a kubernetes cluster and dedicated database server running on AWS RDS. On ...
- 131
3
votes
2
answers
897
views
MySQL incorrectly saying serverID non unique when adding an instance to the InnoDB cluster
I Get the following error when adding the second instance to a MYSQL Innodb Cluster
ERROR: Cannot add instance 'XXX' to the cluster because it has the same server ID of a member of the cluster. ...
- 135
3
votes
1
answer
262
views
Increased disk consumption after upgrading to MySQL 8
I have a MySQL 8 DB in AWS RDS, and after upgrading from MySQL 5.7 to 8 a couple months ago, we have noticed free disk space being consumed at a very rapid rate, at least double the previous rate.
...
- 33
3
votes
2
answers
4k
views
MySQL Error seems unfixable: Index column size too large
I'm getting: ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes.
There are many questions about this here, I tried many of them. No luck. My context:
MySQL 8.0.13 ...
- 31
3
votes
2
answers
3k
views
Mysql temp table insert from json can't handle null values
The MySql-8 JSON_TABLE function allows you to handle JSON data as if it was a table.
I'm trying to use it to populate a temporary table using the generated table but I believe I'm hitting a bug.
...
- 1,324
3
votes
1
answer
157
views
MySQL Locking and Isolation Level
Could someone help me to understand this MySQL behavior?
I was trying to add a new column on a table using this statement:
ALTER TABLE `tb_example`
ADD COLUMN `score` INT NULL,
ALGORITHM = ...
- 133
3
votes
1
answer
18k
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 ...
- 129
3
votes
3
answers
1k
views
RDS MySQL 8.0.16 has empty "INFORMATION_SCHEMA.ST_SPATIAL_REFERENCE_SYSTEMS" table
On aws RDS I have an mysql 8.0.16 instance running.
My problem is now, that the INFORMATION_SCHEMA.ST_SPATIAL_REFERENCE_SYSTEMS table is empty.
This is a problem for me because I would like to create ...
