33
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 ...
- 446
23
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-...
- 7,649
17
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 (...
- 62.2k
14
votes
Accepted
Upgrade all MySQL columns, tables, and databases from utf8mb3 to utf8mb4
Here is a query what will generate all the necessary update statements.
It temporarily disables foreign key checks so that the update statements succeed.
It updates the default character set for each ...
13
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 ...
- 182k
13
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. ...
- 239
12
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(...
- 62.2k
11
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 ...
- 97.4k
11
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 ...
- 111
11
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 ...
- 2,086
10
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 plug ins?
Please file a bug at bugs.mysql.com.
Meanwhile, do ...
- 77k
10
votes
Accepted
Are enums still evil (in MySQL 8.0)?
Nothing has changed between MySQL 5.5 and 8.0 to make ENUMs any more or less evil than they were before.
I think calling it "evil" is hyperbolic. A programming language feature isn't evil. ...
- 13.9k
9
votes
My disk is full of binlog files
With MySQL8, they have turned on binary logging by default and the default purge (expiry/deletion) of binary logs is set to 30days.
Once you are in your SSH and in mysql, you can use the below ...
- 91
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 ...
- 13.9k
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
- 196
8
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 ...
8
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
- 7,917
8
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 ...
- 196
7
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. ...
- 809
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 ...
- 77k
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=...
- 362
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 ...
- 208
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 ...
- 161
6
votes
Accepted
How to select all columns plus one alias column in MySQL
I figured out the solution. I simple had do repeat the table name, as follows:
SELECT bin_to_uuid(id) as uuid, table_name.* FROM table_name
But if you have a solution that does not require to repeat ...
- 243
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 &...
- 77k
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 ...
- 7,917
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) ...
- 77k
6
votes
What is the best alternative for MySQL Query Cache?
Direct Alternatives :
use mariadb :
They Still support query caching ( https://mariadb.com/kb/en/incompatibilities-and-feature-differences-between-mariadb-104-and-mysql-80/ )
proxysql ( https://...
- 61
6
votes
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
It's actually a MySQL 8.0 bug (reported for MySQL 8.0.27-debug / 8.0.28), but also existing before (8.0.23) and seems to also exist on fork (MariaDB/Percona/AWS Aurora/...).
You can check if the bug ...
- 209
Only top scored, non community-wiki answers of a minimum length are eligible