Skip to main content

Questions tagged [mysql-8.0]

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

Filter by
Sorted by
Tagged with
38 votes
1 answer
17k 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?
Talk Nerdy To Me's user avatar
30 votes
3 answers
37k 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?
shaoyihe's user avatar
  • 457
25 votes
5 answers
48k 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 ...
Roy Hinkley's user avatar
14 votes
6 answers
19k 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 ...
IGGt's user avatar
  • 2,156
12 votes
2 answers
5k 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 ...
Florent Poujol's user avatar
12 votes
1 answer
27k 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; +---------+------------...
BadHorsie's user avatar
  • 223
10 votes
2 answers
6k 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(...
newbie's user avatar
  • 207
10 votes
2 answers
10k 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 ...
Stephen Ostermiller's user avatar
10 votes
4 answers
6k views

What is the best alternative for MySQL Query Cache? [closed]

The Query Cache has been removed in MySQL 8. Is there any better alternative for Query Cache in MySQL?
Dipin V A's user avatar
  • 109
9 votes
2 answers
17k 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 ...
ML61's user avatar
  • 103
9 votes
4 answers
8k 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 ...
Ryan's user avatar
  • 385
9 votes
3 answers
14k 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 ...
John's user avatar
  • 402
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 ...
Ali_Hr's user avatar
  • 173
6 votes
1 answer
9k 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 ...
ad_on_is's user avatar
  • 201
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 ...
Chris's user avatar
  • 191
5 votes
4 answers
10k 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, ...
drookie's user avatar
  • 238
5 votes
1 answer
10k 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 ...
Evan Carroll's user avatar
  • 63.9k
5 votes
2 answers
10k 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 ...
Tommaso's user avatar
  • 51
5 votes
2 answers
22k 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 ...
Michael Wallace's user avatar
5 votes
2 answers
3k 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 ...
the_nuts's user avatar
  • 362
5 votes
2 answers
4k 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 ...
gentiane's user avatar
  • 161
5 votes
1 answer
5k 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'...
teamcrisis's user avatar
5 votes
0 answers
3k views

Upgrade from MySQL 5.7 to MySQL 8 massive drop in performance occasionally

I have recently upgraded from MySQL 5.7 to 8 on several databases innodb engine with the same schema but different data hosted on RDS, performance was fine on all of them until the last one which was ...
Colm Canavan's user avatar
4 votes
2 answers
1k 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 ...
fvalasiad's user avatar
4 votes
3 answers
9k 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 ...
Eaten by a Grue's user avatar
4 votes
2 answers
36k 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 ...
cc15's user avatar
  • 91
4 votes
1 answer
1k 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 ...
oucil's user avatar
  • 506
4 votes
1 answer
2k 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&...
undefinedman's user avatar
4 votes
2 answers
3k 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 ...
Nuno's user avatar
  • 829
4 votes
3 answers
534 views

failed pt-online-schema-change left behind triggers. How to delete?

We had a failed pt-osc - the server ran out of disk space. And now the triggers are left behind SHOW TRIGGERS; pt_osc_xxx_production_orders_ins pt_osc_xxx_production_orders_upd ...
phil's user avatar
  • 153
4 votes
2 answers
1k 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?
jsHate's user avatar
  • 177
4 votes
1 answer
3k views

MySQL 8.0.30+: redo_log_capacity vs log_file_size?

Cheers. We're trying to track down the cause of a significant increase in IO load after switching from MySQL 5.7 to 8.0.30, and one issue we've noticed is related to this part of documentation: The ...
myxal's user avatar
  • 43
4 votes
2 answers
631 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 ...
Arun S R's user avatar
  • 207
4 votes
1 answer
502 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). ...
Benjamin C.'s user avatar
4 votes
2 answers
1k views

max_binlog_files on MySQL Server?

I read recently about a setting in Percona Server called max_binlog_files which restricts the total number of binlog files. This is exactly what I need. My binlog_expire_logs_seconds is currently ...
Eaten by a Grue's user avatar
4 votes
2 answers
234 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 ...
Istvan Tabanyi's user avatar
4 votes
1 answer
2k 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 ...
Simon Ninon's user avatar
4 votes
1 answer
2k 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 ...
Benjamin D. Bloom's user avatar
4 votes
1 answer
1k 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), ...
user3343767's user avatar
4 votes
0 answers
312 views

MySQL: how to kill a user lock held by a terminated thread

We're using MySQL 8.0.32 hosted by AWS RDS. mysql> SHOW VARIABLES LIKE 'version'; +---------------+--------+ | Variable_name | Value | +---------------+--------+ | version | 8.0.32 | +-------...
Alex's user avatar
  • 161
4 votes
1 answer
1k views

AWS Aurora Mysql seemingly not picking PRIMARY or secondary index

Relevant System Info: Aurora Mysql 8.0.mysql_aurora.3.03 rg6.xl instances (1 writer, 2 read replicas) Total size:5.5TB (all databases combined, or just looking at the most recent Snapshot) I have been ...
Randoneering's user avatar
4 votes
0 answers
1k 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 ...
Sisir's user avatar
  • 141
4 votes
0 answers
730 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 ...
Wilson Neto's user avatar
3 votes
2 answers
915 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 ...
DarkCenobyte's user avatar
3 votes
1 answer
505 views

"#1292 - Incorrect datetime value" when time is within one hour

I'm running into a bizarre error with a timestamp column in a MySQL DB. When inserting the date/time value, it fails with a #1292 error if the time is between 02:00:00 and 02:59:59. Here is a sample ...
lcdservices's user avatar
3 votes
2 answers
848 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 ...
Michael Wallace's user avatar
3 votes
1 answer
418 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 ...
Jack Douglas's user avatar
3 votes
2 answers
3k 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 ...
Aaron J Spetner's user avatar
3 votes
1 answer
8k 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 ...
Sajid Maqsood's user avatar
3 votes
3 answers
2k views

MySQL 8 not using Spatial Index?

I have an issue where whatever I do I cannot get MySQL to use a spatial index. A query like the following then takes 5 seconds! Any way to force this to use the index at all? WITH ranked_reports ...
Lee Armstrong's user avatar

1
2 3 4 5
14