When Index Condition Pushdown is used, you cannot trust rows examined as reported by the slow query log (nor in some Performance Schema tables) and you cannot trust the global status Innodb_rows_read (nor the InnoDB Metrics dml_reads). These can lead to an incorrect query complexity analysis, to a mistaken estimation of the total load of a MySQL server, and to a lot of wasted time. As we go through the details in this post, we will understand why a SELECT * is sometimes faster than only selecting the columns from an index (we would normally expect the opposite) and we will visit inner workings of the MySQL Storage Engine Architecture. But first, let's start by exposing the problem.
J-F Gagné's MySQL Blog
Tuesday, March 8, 2022
Wednesday, January 19, 2022
Crashing MySQL with Malicious Intent and a lot of Determination
A year ago, I blogged about An Unprivileged User can crash your MySQL Server. At the time, I explained how to protect yourself against this problem. A few weeks ago, I revisited this vulnerability in a follow-up post in which I explained the fix, claimed that the MySQL 5.7 default configuration for Group Replication is still problematic, and explained a tuning to avoid the vulnerability. In this last post in the series, I explain how to exploit this vulnerability to crash older version of MySQL (or untuned Group Replication in 5.7), but this needs a lot of determination.
Monday, December 13, 2021
Trick to Simulate a Linux Server with less RAM
Tuesday, November 30, 2021
Follow-up on an Unprivileged User can Crash your MySQL Server
A year ago, I blogged about An Unprivileged User can Crash your MySQL Server. At the time, I presented how to protect yourself against this problem without explaining how to generate a crash. In this post, I am revisiting this vulnerability, not giving the exploit yet, but presenting the fix. Also, because the default configuration of Group Replication in 5.7 is still vulnerable (it is not in 8.0), I show the adjustment to make to avoid problems.
Friday, November 12, 2021
Duplicate Entry in ALTER TABLE (and in OPTIMIZE TABLE)
A few weeks ago and in MySQL 5.7, I had an ALTER TABLE that failed with a duplicate entry error. This is old news as it is happening since MySQL 5.6, but I only saw this recently because I am normally using online schema change from the Percona Toolkit (pt-osc) or GitHub's online schema migration (gh-ost). I do not like that and I am disappointed this has not been improved, so this post is partially a rant, but not only, because I explain why ALTER TABLE is failing and I give solutions to avoid the failure.
Saturday, December 5, 2020
Fixing low durability GTID replica with Voodoo
At my FOSDEM talk earlier this year, I gave a trick for fixing a crashed GTID replica. I never blogged about this, so now is a good time. What is pushing me to write on this today is my talk at MinervaDB Athena 2020 this Friday. At this conference, I will present more details about MySQL replication crash safety. So you know what to do if you want to learn more about this subject. Let's now talk about voodoo.
Sunday, October 18, 2020
An Unprivileged User can crash your MySQL Server
Yes, your read the title correctly: an unprivileged user can crash your MySQL Server. This applies for the default configuration of MySQL 8.0.21 (and it is probably the case for all MySQL 8 GA versions). Depending on your configuration, it might also be the case for MySQL 5.7. This needs malicious intent and a lot of determination, so no need to panic as this will not happen by accident. I am not sharing the exploit in this post (I might do it later), but I am telling you how to avoid the problem.
Sunday, June 7, 2020
Update on Planet for the MySQL Community (June 7, 2020)
Thursday, May 14, 2020
Planet [for the] MySQL Community: Pluto Beta
Tuesday, May 12, 2020
Planet MySQL Community: Requirements RFC
Friday, April 24, 2020
Planning for the AFTER Planet MySQL (bis)
As written in a previous post, the state of Planet MySQL is unhealthy ! I am still aggregated there for now as, before leaving what was the best news-feed for the MySQL Community, we need a replacement. This post aims at starting a discussion on this replacement.
Planning for the AFTER Planet MySQL
State of Planet MySQL: unhealthy !
I have been complaining for some time about Planet MySQL filtering content that I think it should not. Shlomi made a courageous decision on this and explained it in his recent post (Pulling his blog out of Planet MySQL aggregator, over community concerns). I am thinking of doing the same. What are your thoughts on this ? Please share them in the comments below (or on Shlomi's blog).
Monday, March 30, 2020
JFG MySQL Advice during COVID-19
We are all impacted by COVID-19. On my side, this virus disrupted my travel, professional and personal plans for the upcoming months. To overcome these disruptions, and following the idea of someone on MySQL Community Slack, I am experimenting with giving MySQL advice on a 30-minute basis. You can book a slot using this link:
Monday, January 27, 2020
A Legacy Behavior of MySQL Corrupting Restored Backups (replicate-same-server-id = OFF)
Thursday, January 9, 2020
Puzzled by MySQL Replication (War Story)
Monday, October 7, 2019
The dark side of super_read_only
Tuesday, July 16, 2019
MySQL Master Replication Crash Safety Part #5a: making things faster without reducing durability - using better hardware
MySQL Master Replication Crash Safety Part #5: faster without reducing durability (under the hood)
Tuesday, July 9, 2019
MySQL Master Replication Crash Safety Part #4: benchmarks of high and low durability
MySQL Master Replication Crash Safety part #4: benchmarks (under the hood)
Tuesday, June 25, 2019
Getting past 2400 threads with sysbench and dbdeployer for benchmarking MySQL 5.7.26
Wednesday, May 8, 2019
Not enforcing SSL on CloudSQL, really !
Wednesday, May 1, 2019
Care with using the max_connections beta database flag on CloudSQL...
Tuesday, February 26, 2019
MySQL Master High Availability and Failover: more thoughts
This is a great series, and I recommend its reading for everybody implementing master failover, with or without Orchestrator, even if you are not fully automating the process yet. Taking a step back, I realized that service discovery is only one of the five parts of a full MySQL Master Failover Strategy; this post is about these five parts. In some follow-up posts, I might analyze some deployments using the framework presented in this post.
Tuesday, February 12, 2019
MySQL Master Replication Crash Safety Part #3: GTID
Tuesday, January 8, 2019
Care when changing MASTER_DELAY with CHANGE MASTER TO (delayed replication)
TL&DR: be careful when using CHANGE MASTER TO MASTER_DELAY = N: it might wipe your relay logs !
Thursday, November 15, 2018
MySQL Master Replication Crash Safety Part #2: lagging slaves
Tuesday, November 13, 2018
How to install Percona Server 5.7 on Debian/Ubuntu without a root password prompt
Tuesday, October 30, 2018
On the consequences of sync_binlog != 1 (part #1)
Tuesday, October 23, 2018
Arg: relay_log_space_limit is (still) not dynamic !
Tuesday, September 11, 2018
Unforeseen use case of my GTID work: replicating from AWS Aurora to Google CloudSQL
The surprising part for me is that it uses my 4-year-old work for online migration to GTID with MySQL 5.6. This is a completely unforeseen use case of my work as I never thought that my hack would be useful after Oracle include an online migration path to GTID in MySQL 5.7 (Percona did something similar for MySQL 5.6).
Monday, August 27, 2018
Another Post on the Percona Community Blog, Bug Activities on Replication Crash Safety, and Percona Live Europe
Thursday, June 28, 2018
JFG Posted on the Percona Community Blog - A Nice Feature in MariaDB 10.3: no InnoDB Buffer Pool in Core Dumps
Thursday, April 19, 2018
Some bugs and spring pilgrimage to Percona Live Santa Clara 2018
Saturday, January 27, 2018
Next week in Brussels: Parallel Replication at the MySQL Pre-FOSDEM Day
Thursday, January 11, 2018
More Write Set in MySQL: Group Replication Certification
Monday, January 8, 2018
Write Set in MySQL 5.7: Group Replication
Wednesday, January 3, 2018
An update on Write Set (parallel replication) bug fix in MySQL 8.0
Tuesday, November 28, 2017
Here is the CREATE TABLE of death
Thursday, October 19, 2017
A crashing bug in MySQL: the CREATE TABLE of death (more fun with InnoDB Persistent Statistics)
Wednesday, August 16, 2017
The danger of no Primary Key when replicating in RBR (and a partial protection with MariaDB 10.1)
TL;DR2: MariaDB 10.1 has an interesting way to protect against missing a primary key (innodb_force_primary_key) but it could be improved.
A few weeks ago, I was called off hours because replication delay on all the slaves from a replication chain was high and growing. It was not the first time this happened on that chain, so I thought right away that this was probably an UPDATE or DELETE of many rows on a table without a primary key. Let's see what is the problem with this and to understand that, we have to talk about binary log formats.
Monday, August 14, 2017
More Details about InnoDB Compression Levels (innodb_compression_level)
TL;DR: tuning innodb_compression_level is not very useful for my dataset.
Thursday, August 10, 2017
Why we still need MyISAM (for read-only tables)
In the previous post, I shared my experience with InnoDB table compression on a read-only dataset. In it, I claimed, without giving much detail, that using MyISAM and myisampack would result is a more compact storage on disk. In this post, I will share more details about this claim.
Monday, August 7, 2017
An Adventure in InnoDB Table Compression (for read-only tables)
Monday, July 24, 2017
How far can you go with MySQL or MariaDB ?
- MyISAM permits data and index files to grow up to 256 TiB by default, but this limit can be changed up to the maximum permissible size of 65,536 TiB (256^7 − 1 bytes).
- The maximum tablespace size depends on the InnoDB page size: 64 TiB for 16 KiB pages.
(The maximum tablespace size is also the maximum size for a [InnoDB] table.)
Wednesday, July 19, 2017
InnoDB Basics - Compaction: when and when not
Wednesday, July 5, 2017
Fun with InnoDB Persistent Statistics
[...]
Phase 4/7: Running 'mysql_fix_privilege_tables'
ERROR 1062 (23000) at line 586: Duplicate entry 'schema-table_name#P#partition_name_truncated' for key 'PRIMARY'
ERROR 1062 (23000) at line 590: Duplicate entry 'schema-table_name#P#partition_name_truncated' for key 'PRIMARY'
ERROR 1062 (23000) at line 593: Duplicate entry 'schema-table_name#P#partition_name_truncated' for key 'PRIMARY'
FATAL ERROR: Upgrade failed
Monday, May 22, 2017
Better Replication when running both InnoDB and MyRocks (or other Storage-Engines)
Why am I posting this here ? Mostly because I want to share with you that:
- I am also involved in this project,
- I am working closely with Kristian on this feature,
- and that Booking.com is financing Kristian's time on this development.