Why MySQL Key Partition Queries Fail After Upgrade and How to Fix Them

MySQL – Key Partition Algorithm Evolution Analysis

This section explains a data‑loss incident caused by a physical upgrade. Under MySQL 5.1 a table partitioned by KEY works correctly, but the same table started failing after being started with MySQL 5.5/5.6.

drop table t1;

create table t1 (c1 int , c2 int) 
PARTITION BY KEY (c2) partitions 5;
insert into t1 values(1,1785089517),(2,null);
select * from t1 where c2=1785089517;
select * from t1 where c2 is null;

When the instance is run with MySQL 5.5/5.6 the row with a non‑null value cannot be found.

alter table t1 PARTITION BY KEY ALGORITHM = 1 (c2) partitions 5;
select * from t1 where c2 is null;
select * from t1 where c2=1785089517;

Root‑cause analysis

Tracing the source shows that MySQL 5.1 and MySQL 5.5/5.6 use different hash algorithms for KEY partitions.

MySQL 5.1 uses my_hash_sort_bin for non‑null values:

void my_hash_sort_bin(const CHARSET_INFO *cs __attribute__((unused)),
                     const uchar *key, size_t len, ulong *nr1, ulong *nr2)
{
  const uchar *pos = key;
  key += len;
  for (; pos < (uchar*)key; pos++)
  {
    nr1[0] ^= (ulong) ((((uint) nr1[0] & 63) + nr2[0]) * ((uint)*pos)) + (nr1[0] << 8);
    nr2[0] += 3;
  }
}

This algorithm places the row (1, 1785089517) into partition 3.

MySQL 5.5/5.6 use my_hash_sort_simple for non‑null values:

void my_hash_sort_simple(const CHARSET_INFO *cs,
                         const uchar *key, size_t len,
                         ulong *nr1, ulong *nr2)
{
  register uchar *sort_order = cs->sort_order;
  const uchar *end;
  end = skip_trailing_space(key, len);
  for (; key < (uchar*)end; key++)
  {
    nr1[0] ^= (ulong) ((((uint) nr1[0] & 63) + nr2[0]) * ((uint) sort_order[(uint) *key])) + (nr1[0] << 8);
    nr2[0] += 3;
  }
}

This algorithm puts the same row into partition 5, so queries using the old hash cannot locate it.

Both versions share the same algorithm for NULL values, so rows with NULL are still found.

Compatibility handling

MySQL 5.5.31+ provides the syntax ALTER TABLE … PARTITION BY KEY ALGORITHM = 1 to retain the old hash behavior. Running the following after upgrading restores correct query results:

alter table t1 PARTITION BY KEY ALGORITHM = 1 (c2) partitions 5;

select * from t1 where c2=1785089517;

The CHECK TABLE … FOR UPGRADE command also detects tables that need this conversion and reports an error prompting the same ALTER TABLE statement.

MySQL – Client Crash Example

Background

A customer exported a large table with mysqldump (≈50 GB) and then tried to import it using mysql -e 'source test.dmp'. The client crashed with a segmentation fault.

Root cause

The dump file began with a warning line because the dump command redirected both stdout and stderr to the same file ( mysqldump … >test.dmp 2>&1). The warning contained an unmatched single quote, causing the client’s add_line parser to search indefinitely for a matching quote, exhausting memory and crashing.

Further investigation

Maximum max_allowed_packet limits the size of CLOB columns (1 KB – 1 GB in MySQL 5.5). mysqldump splits large INSERT statements based on opt_net_buffer_length; if a single row exceeds this size, it forces a split.

The client’s maximum batch buffer size is defined as #define MAX_BATCH_BUFFER_SIZE (1024L * 1024L * 1024L), i.e., 1 GB.

Thus the limits on max_allowed_packet and MAX_BATCH_BUFFER_SIZE are aligned.

RDS remediation principle

The crash is caused by user misuse of mysqldump. Aliyun RDS will report the issue to the user but will not apply a code fix; instead, users are guided to use the tool correctly.

MySQL – GTID_PURGED Breaks AUTO_POSITION Replication

Bug description

In MySQL 5.6.22, setting GLOBAL GTID_PURGED on the master with a GTID set that the slave has not yet replicated does not raise an error. The master silently sends those GTIDs to the slave, causing the slave to miss transactions and become inconsistent.

Background

Binlog contains Previous_gtids_log_event (a single event at the start) and multiple Gtid_log_event entries.

During replication the slave sends its Retrieved_Gtid_Set + Executed_Gtid_Set to the master. The master scans its binlogs from newest to oldest, looking for the first file whose Previous_gtids_log_event is a superset of the slave’s set.

If the master’s binlog was rotated after SET GLOBAL GTID_PURGED, the new binlog’s Previous_gtids_log_event may not contain the missing GTIDs, so the scan stops at the old binlog (which may be empty) without error.

Fix

The official fix adds a pre‑scan check: if GTID_PURGED is larger than the slave’s executed set, the master aborts with error 1236, the same message shown when the slave cannot find required GTIDs.

MySQL – Replicate‑Do‑DB with GTID Continuity Issue

Problem description

When splitting a large instance into multiple instances, each replica uses replicate-do-db to filter only the relevant database’s events. The slave’s Executed_Gtid_Set becomes fragmented because events for filtered databases are skipped, leaving gaps in the GTID sequence.

These gaps can cause problems when a new replica is created from a filtered slave and the master later purges old binlogs; the new slave may request GTIDs that no longer exist.

Fix

A MySQL bugfix makes the SQL thread record an empty transaction for filtered events, preserving the GTID position. For statement‑based binlog format this adds a placeholder GTID; for row‑based format the existing check_table_map already creates empty transactions. The patch also handles CREATE/DROP TEMPORARY TABLE statements, ensuring they generate empty transactions in row‑based replication as well.