33 votes
Accepted

Why is this MySQL query taking so long?

Instead of '2020-04-30' = date_add(op.dataDate, INTERVAL 14 DAY); Use op.dataDate = date_sub('2020-04-30', INTERVAL 14 DAY); Your first statement will be interpreted as "add 14 days to all ...
user avatar
  • 3,442
25 votes
Accepted

What happened to MySQL 6 & 7?

According to Dave Stockes, a MySQL Community Manager for Oracle: (from his blog post: MySQL 8 is coming) Years ago, before the Sun Microsystems purchase of MySQL AB, there was a version of MySQL ...
user avatar
18 votes
Accepted

Why did MySQL remove the query cache feature after version 8.0?

There is a detailed blog from the MySQL server team about this, where Matt Lord says: The query cache has been disabled-by-default since MySQL 5.6 (2013) as it is known to not scale with high-...
user avatar
  • 6,390
12 votes

Table mysql/innodb_index_stats has length mismatch in the column name table_name. Please run mysql_upgrade

Maybe this information is useful. https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-23.html Note This release includes a change to the innodb_index_stats and innodb_table_stats system tables. ...
user avatar
  • 229
12 votes
Accepted

Mysql 8 ST_GeomFromText giving error Latitude out of range in function st_geomfromtext. It must be within [-90.000000, 90.000000]

That's because on MySQL 8, they did the unthinkable and implemented the stupid method of doing geography coordinates (like MySQL). This broke backwards compatability. When you use Geometry on MySQL (...
user avatar
  • 56.2k
11 votes

Why did MySQL remove the query cache feature after version 8.0?

Good riddance !!! It is a challenge for most database developers to correctly estimate the size of the most common result sets in their applications. Having a large query cache was just a big bandage ...
user avatar
10 votes
Accepted

How to set lowercase_table_name

The server variable lower_case_table_names is described in the relevant documentation page: Identifier Case Sensitivity, where it also mentions: lower_case_table_names can only be configured when ...
user avatar
10 votes
Accepted

How do I create a point with an SRID in MySQL?

MySQL 8.0+ MySQL supports Point(x,y) which is a GIS function that constructs a point. With MySQL 8.0 and newer, you can further assign an SRID to that point with ST_SRID(srid) SELECT ST_SRID( Point(...
user avatar
  • 56.2k
9 votes
Accepted

Why is the Innodb Memcached plugin being deprecated in MySQL 8.0.22?

The memcached plugin never did skip the overhead of InnoDB. It was an API, not a storage engine. Your data posted to the memcached plugin was still subject to transactions, still written to InnoDB's ...
user avatar
  • 9,796
8 votes

Disable Binary Logs in MySQL 8.0

In addition to specifying the corresponding command line argument, you can also add an option to my.cnf: Edit my.cnf and add skip-log-bin in the [mysqld] section. Restart mysqld Enjoy your lack of ...
user avatar
8 votes
Accepted

I can't start mysqld.service -> status : Data Dictionary upgrade from MySQL 5.7 in progress in centos 7

This worked for me: Clear the mysql data directory at /var/lib/mysql assuming that you have backups or you don't really need that data anymore. Then sudo service mysqld start
user avatar
7 votes
Accepted

Why 0/false returns true when using JSON_EXTRACT in MySQL

Hope you're doing great. I would like to see your schema to make sure all is good. I tried to recreate what I believe you did. Here is what I did: mysql> create database test295895; mysql> use ...
user avatar
6 votes

Why did MySQL remove the query cache feature after version 8.0?

(I agree with the other Answer, but here is my 2-cents-worth.) As implemented, ... The QC cannot work with Galera or Group Replication, both of which are getting more traction in the HA arena. When ...
user avatar
  • 68.8k
6 votes

Table mysql/innodb_index_stats has length mismatch in the column name table_name. Please run mysql_upgrade

Do you have any clue what is going on when the Warning is being generated? It sounds like some form of version mismatch. Do you have any Plugins? Please file a bug at bugs.mysql.com . Meanwhile, ...
user avatar
  • 68.8k
6 votes
Accepted

MySQL crashing at about 70k open files

The issue is a Mysql bug (https://bugs.mysql.com/bug.php?id=94185 ) Anyway, some tuning mitigated the issue and stopped the number of open files to grow. Specifically, we set: innodb_lru_scan_depth=...
user avatar
  • 331
6 votes
Accepted

mysql 8.0, updates are sometimes stalling

That's an 8.0 optimizer issue. Can easily be fixed either doubling the range_optimizer_max_mem_size (and, if this is not helping, doubling it further and further) or just by setting it to zero, this ...
user avatar
  • 197
6 votes

mysql 8.0, updates are sometimes stalling

I found your question after looking into why I often had poor performance with inserts and updates, and with connection frequently in the state "waiting for handler commit". What I have subsequently ...
user avatar
6 votes
Accepted

Performance issues after upgrading to MySQL 8

Shane Bester made a suggestion as a comment on my bug report which explains that this is a known bug and will be resolved in next release. More importantly, there is a workaround which I've tried ...
user avatar
6 votes

Can't set log_bin_trust_function_creators=1 on machine startup - MySQL 8.0

Put it under mysqld, there it should be found and set [mysqld] log-bin-trust-function-creators = 1
user avatar
  • 7,601
6 votes

Why is this MySQL query taking so long?

The formulation is the main problem. After rewriting the query, the index you have can be used for the entire WHERE clause. SELECT * FROM op.prices WHERE ticker = 'AAPL' AND expDate &...
user avatar
  • 68.8k
6 votes
Accepted

MySQL event scheduler waiting on empty queue since server restarted 12 days ago

The event_Scheduler is, as it's name suggests, a way to Schedule Events (in this case queries) to run in MySQL at a given time. It is simply waiting for an event to trigger it and tell it to do ...
user avatar
  • 1,920
6 votes
Accepted

Is using strings as keys for reference to other tables bad in terms of memory usage?

Yes MySql and all other rdms will store the complete email as varchar and reserve space for the number of bytes it needs or the maximal size depending on the rdms. Integer with up to 8 bytes for a big ...
user avatar
  • 7,601
6 votes

Is using strings as keys for reference to other tables bad in terms of memory usage?

If there is one email per user, add a column to the users table. In general 1:1 mappings are not a good idea between tables; simply combine the tables. UUIDs are a bad idea (for multiple reasons) ...
user avatar
  • 68.8k
5 votes
Accepted

Why does REGEXP_REPLACE concatenate with a previous result?

It's a known bug, fixed in 8.0.12. REGEXP_RELACE() results from one result set row could carry forward to the next row, resulting in accumulation of previous results in the current row.
user avatar
  • 30.8k
5 votes
Accepted

How to write a query to get concatenated names of riders with the same weight?

You're looking for GROUP_CONCAT: SELECT weight, GROUP_CONCAT(name SEPARATOR ' ') as name_list FROM Rider GROUP BY weight
user avatar
5 votes

MySQL Coalesce date missing results, why?

Because it converts the datetimes to strings and does the comparisons with the string values. And '2019-12-19 00:00:00' <= '2019-12-19' is false in text comparison.
5 votes

Does MySQL 8 ASCII vs utf8mb4_0900_ai_ci size differ when only using ASCII characters?

Unless MySQL does something strange, using only ASCII characters (i.e. only values 0 - 127) should be the exact same encoding, and hence the exact same size, between ASCII, UTF-8, and many other 8-bit ...
user avatar
5 votes

Having charset "utf8mb4" - does it mean every character takes 4 bytes, or only those that need 4 bytes?

Actually, my answer is in the Documentation: https://mariadb.com/kb/en/unicode/ UTF-8 encoding using one to three bytes per character. Basic Latin letters, numbers and punctuation use one byte. ...
user avatar
  • 609
4 votes
Accepted

MySQL restore lost data after update from 5.7 -> 8.0.11

I was finally successful restoring the data. My problem was, that I tried to restore the data with a docker-compose, which somehow didn't work. So I took a different approach 1. Run a docker image ...
user avatar
  • 191
4 votes

Extreme performance loss due to moving from Mysql 5.7.23 to Mysql 8.0 (status, config included)

Review of GLOBAL STATUS and VARIABLES Observations: Version: 8.0.13 240 GB of RAM Uptime = 10:47:09; some GLOBAL STATUS values may not be meaningful yet. You are not running on Windows. Running 64-...
user avatar
  • 68.8k

Only top scored, non community-wiki answers of a minimum length are eligible