MySQL Partitioning Hash Changes, GTID Replication Bugs, Client Crash, and TokuDB Optimize Table Analysis

TokuDB·Feature Analysis·Optimize Table

The author reports a physical upgrade that leads to data loss due to differences in the key‑hash algorithm between MySQL 5.1 and later versions (5.5/5.6). In MySQL 5.1 a key‑partitioned table correctly returns rows for both non‑null and null values, but after altering the table to use the newer algorithm the non‑null row becomes invisible.

drop table t1;

create table t1 (c1 int , c2 int) 
PARTITION BY KEY (c2) partitions 5; 
insert into t1  values(1,1785089517),(2,null);
MySQL> select * from t1 where c2=1785089517;
+------+------------+
| c1   | c2         |
+------+------------+
|    1 | 1785089517 |
+------+------------+
1 row in set (0.00 sec)
MySQL> select * from t1 where c2 is null;
+------+------+
| c1   | c2   |
+------+------+
|    2 | NULL |
+------+------+
1 row in set (0.00 sec)

Running the same instance under MySQL 5.5/5.6 makes the non‑null row disappear:

alter table t1 PARTITION BY KEY ALGORITHM = 1 (c2)  partitions 5;
MySQL> select * from t1 where c2 is null;
+------+------+
| c1   | c2   |
+------+------+
|    2 | NULL |
+------+------+
1 row in set (0.00 sec)
MySQL> select * from t1 where c2=1785089517;
Empty set (0.00 sec)

The root cause is a change in the hash function implementation. MySQL 5.1 uses my_hash_sort_bin for non‑null values, while 5.5/5.6 use my_hash_sort_simple , leading to different partition assignments.

void my_hash_sort_bin(const CHARSET_INFO *cs __attribute__((unused)),
                     const uchar *key, size_t len,ulong *nr1, ulong *nr2)
{ ... }

In 5.5/5.6 the same key hashes to a different partition, so the row cannot be found. The null‑value algorithm remains unchanged, so NULL rows are still queryable.

void my_hash_sort_simple(const CHARSET_INFO *cs,
                         const uchar *key, size_t len,
                         ulong *nr1, ulong *nr2)
{ ... }

Compatibility is provided by the syntax ALTER TABLE … PARTITION BY KEY ALGORITHM=1 introduced in MySQL 5.5.31, which restores the original hash behavior:

alter table t1 PARTITION BY KEY ALGORITHM = 1 (c2) partitions 5;
select * from t1 where c2=1785089517;
+------+------------+
| c1   | c2         |
+------+------------+
|    1 | 1785089517 |
+------+------------+
1 row in set (0.00 sec)

MySQL's CHECK TABLE … FOR UPGRADE also detects the old algorithm and suggests the same ALTER statement.

CHECK TABLE t1  FOR UPGRADE\G
... Msg_text: KEY () partitioning changed, please run:
ALTER TABLE `test`.`t1` PARTITION BY KEY /*!50611 ALGORITHM = 1 */ (c2) PARTITIONS 5

MySQL·Bug Hunt·Client Crash Example

A client crash occurs when a large dump file is created with stderr redirected (e.g., mysqldump ... >test.dmp 2>&1 ), mixing error messages into the dump. The MySQL client reads a stray single quote in the first line, enters an infinite search for a matching quote, exhausts memory, and segfaults.

Key limits discovered:

max_allowed_packet controls the maximum size of BLOB/CLOB fields (1 KB – 1 GB in MySQL 5.5).

mysqldump splits batch INSERTs based on opt_net_buffer_length ; if a row exceeds this size it is forced into its own INSERT.

The client allocates at most 1 GB for a batch buffer ( #define MAX_BATCH_BUFFER_SIZE (1024L * 1024L * 1024L) ).

The resolution is to avoid redirecting stderr into the dump file.

GTID_PURGED and AUTO_POSITION Replication Bug

Setting SET GLOBAL GTID_PURGED='…' on a master after importing data can create a situation where the master’s binlog contains GTIDs that the slave has not yet executed. The slave, using MASTER_AUTO_POSITION=1 , should error, but older versions silently replicate, causing data loss.

The fix adds a pre‑check: if gtid_purged is not a subset of slave_gtid_executed , the master aborts with error 1236.

Replicate‑do‑db with GTID Gaps

When filtering replication with replicate-do-db , the slave’s Executed_Gtid_Set becomes fragmented because events for other databases are skipped, leaving gaps. This can cause missing binlog files and replication errors. A bugfix (revno 5860) makes the SQL thread record an empty transaction for filtered events, preserving GTID continuity, and also handles temporary tables in mixed statement/row formats.

TokuDB·Feature Analysis·Optimize Table

Running OPTIMIZE TABLE on a TokuDB table repeatedly increases the size of the main index files (e.g., from 47 MB to 79 MB) because TokuDB writes dirty pages to the end of the file, leaving fragments that are later reclaimed during checkpoints. The engine is designed to be “no‑fragmentation”, so OPTIMIZE TABLE provides little benefit and is generally unnecessary.

Key takeaways:

InnoDB‑style hash algorithm changes require ALTER TABLE … ALGORITHM=1 for compatibility.

Avoid redirecting stderr into dump files to prevent client crashes.

GTID_PURGED must be a subset of the slave’s executed GTIDs; otherwise replication should fail.

Replication filtering can fragment GTID sets; the official fix records empty transactions for filtered events.

TokuDB’s storage engine already manages fragmentation, making OPTIMIZE TABLE largely redundant.