Questions tagged [mysql-8.0]
MySQL version 8.0 - please also tag with mysql for search purposes.
445
questions
25
votes
1
answer
12k
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?
- 355
24
votes
3
answers
20k
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?
- 377
23
votes
4
answers
36k
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 ...
- 406
10
votes
5
answers
10k
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 ...
- 1,920
8
votes
2
answers
5k
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 ...
- 295
7
votes
2
answers
2k
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(...
- 177
7
votes
1
answer
10k
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
7
votes
3
answers
11k
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 ...
- 362
6
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
6k
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 ...
- 56.3k
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 ...
- 331
5
votes
2
answers
99
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 ...
- 153
5
votes
1
answer
6k
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
4
votes
2
answers
687
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
1
answer
487
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
9k
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;
+---------+------------...
- 143
4
votes
2
answers
542
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
3k
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 ...
4
votes
0
answers
491
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
5
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, ...
- 197
3
votes
1
answer
606
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&...
- 217
3
votes
2
answers
1k
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 ...
- 609
3
votes
2
answers
3k
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 ...
- 31
3
votes
2
answers
139
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 ...
- 193
3
votes
1
answer
347
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 ...
- 37.5k
3
votes
1
answer
256
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). ...
- 133
3
votes
1
answer
385
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
247
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
691
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
169
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
3k
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
5k
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 ...
- 43
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,284
3
votes
1
answer
611
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 ...
3
votes
1
answer
10k
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
993
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 ...
3
votes
2
answers
1k
views
MySQL 8.0 Tuning Advice Needed - Slow Locking & Various Issues
I recently upgraded my database to MySQL 8.0 and I'm using phpmyadmin to get status information. I also upgraded my virtual server to 4GB RAM and 2 vCPUs, intended to be a MySQL-only server for my ...
- 33
3
votes
2
answers
139
views
Why max(id) with join not working or how to optimize it with join?
Below is my MESSAGES_THREAD table:
CREATE TABLE `messages_thread` (
`id` varchar(64) NOT NULL,
`creator` mediumint(9) NOT NULL,
`other` mediumint(9) NOT NULL,
`time` datetime DEFAULT ...
- 101
3
votes
1
answer
2k
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 ...
- 141
3
votes
3
answers
844
views
High disk IO with MySQL master-master replication
We've been evaluating an active-passive master-master replication setup for our MySQL database, running on version 8.0. While diagnosing some general slowness issues, we were surprised to see very ...
- 151
3
votes
0
answers
521
views
Mysql memory usage increasing after upgrading to 8 and converting some tables from MyISAM to InnoDB
I am having an issue with MySQL gradually consuming more memory every day which seems to have coincided with converting some tables from MyISAM to InnoDB two weeks ago. You can see the trend in memory ...
- 31
3
votes
0
answers
906
views
Cannot access MYSQL safe mode using skip-grant-tables on Windows
I am running a MySQL server 8.0.21 on a Windows Server system.
Recently I ran into an issue where my root user lost all its privileges.
I am trying to gain access to the MySQL server using:
mysqld ...
3
votes
0
answers
2k
views
MySQL Group Replication - Peer address is not valid
I have been trying to set up an MySQL Innodb Cluster, initially with two databases, both MySQL 8.0.19.
But I keep getting the same errors.
On the first one (username@1.2.3.4:3306), I have created ...
- 1,920
3
votes
0
answers
152
views
check if MySQL is using the Henry Spencer implementation for REGEX
Prior to MySQL 8.0.4, MySQL used the Henry Spencer implementation for REGEX and you could use this for word boundaries:
'[[:<:]]' and '[[:>:]]'
From 8.0.4 MySQL is using the ICU implementation ...
- 131
3
votes
1
answer
826
views
MySQL creates tables very slow
On my home machine running Ubuntu 18.04 and MySQL version 8.0.12 with better hardware, table creation, database creation and so on are very slow. On my other computer with older hardware (slower SSD), ...
- 31
2
votes
2
answers
24k
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 ...
- 71
2
votes
3
answers
2k
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 ...
- 525
2
votes
1
answer
3k
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 ...
2
votes
2
answers
8k
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 ...
- 193
2
votes
1
answer
5k
views
Server quit without updating PID file when trying to start MySQL server?
So I created my .bash_profile, opened it in a text editor and added:
export PATH=$PATH:/usr/local/mysql/bin
and then I saved it. Next, I tried running
sudo /usr/local/mysql/support-files/mysql.server ...
- 73