Bug #50646 Merge engine query results affected by pack_key differences with binary fields
Submitted: 27 Jan 2010 4:41 Modified: 14 Jul 2010 21:22
Reporter: Jonathon Coombes Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Server: Merge storage engine Severity:S2 (Serious)
Version:5.0, 5.1, 5.5.99 bzr, 5.1.36-enterprise-commercial-classic OS:Any
Assigned to: CPU Architecture:Any
Tags: merge, OEM, pack_key

[27 Jan 2010 4:41] Jonathon Coombes
Description:
The manual states that different table definitions between base and merge table is not permitted, but settings such as pack_keys does not affect the outcome. This does not appear to be the case, particularly when dealing with binary data type fields and/or indexes.

mysql> alter table zz engine=merge insert_method=first pack_keys=1 union (ProfilerEventHourlyRollup_14627,ProfilerEventHourlyRollup_14628);
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> select count(1) from zz where SrcIPAddress=unhex('030A020502000000000000000000000000');
+----------+
| count(1) |
+----------+
| 4 |
+----------+
1 row in set (0.00 sec)

mysql> select count(1) from ProfilerEventHourlyRollup_14628 where SrcIPAddress=unhex('030A020502000000000000000000000000'); 
+----------+
| count(1) |
+----------+
| 12 |
+----------+
1 row in set (0.00 sec)

mysql> select count(1) from ProfilerEventHourlyRollup_14627 where SrcIPAddress=unhex('030A020502000000000000000000000000');
+----------+
| count(1) |
+----------+
| 4 |
+----------+
1 row in set (0.00 sec)

mysql> alter table zz engine=merge insert_method=first pack_keys=1 union (ProfilerEventHourlyRollup_14628,ProfilerEventHourlyRollup_14627);
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> select count(1) from zz where SrcIPAddress=unhex('030A020502000000000000000000000000'); 
+----------+
| count(1) |
+----------+
| 12 |
+----------+
1 row in set (0.00 sec)

How to repeat:
Create tables with binary fields with indexes and then a merge table.

Try the above statements (or similar) on the created tables.
[27 Jan 2010 7:43] Sveta Smirnova
Thank you for the report.

But version 5.1.36 is old. Additionally I can not repeat described behavior. Please try with current version 5.1.42 and if problem still exists indicate which package do you use (filename you downloaded).
[28 Jan 2010 4:17] MySQL Verification Team
Repeated again in 5.1.42 version.

mysql>  alter table zz engine=merge insert_method=first pack_keys=1 union (ProfilerEventHourlyRollup_14627,ProfilerEventHourlyRollup_14628);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>  select count(1) from zz where SrcIPAddress=unhex('030A020502000000000000000000000000');
+----------+
| count(1) |
+----------+
|        4 |
+----------+
1 row in set (0.00 sec)

mysql> select count(1) from ProfilerEventHourlyRollup_14628 where SrcIPAddress=unhex('030A020502000000000000000000000000');
+----------+
| count(1) |
+----------+
|       31 |
+----------+
1 row in set (0.00 sec)

mysql> select count(1) from ProfilerEventHourlyRollup_14627 where SrcIPAddress=unhex('030A020502000000000000000000000000');
+----------+
| count(1) |
+----------+
|        4 |
+----------+
1 row in set (0.00 sec)

mysql>  alter table zz engine=merge insert_method=first pack_keys=1 union (ProfilerEventHourlyRollup_14628,ProfilerEventHourlyRollup_14627);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>  select count(1) from zz where SrcIPAddress=unhex('030A020502000000000000000000000000');
+----------+
| count(1) |
+----------+
|       31 |
+----------+
1 row in set (0.00 sec)

mysql> select @@version;
+--------------------------------------+
| @@version                            |
+--------------------------------------+
| 5.1.42-enterprise-commercial-classic |
+--------------------------------------+
1 row in set (0.00 sec)

mysql>
[28 Jan 2010 4:22] MySQL Verification Team
Note that this directly relates to the pack_keys since setting both the merge table and underlying tables to pack_keys=0, it appears to be fine:

mysql> alter table zz engine=merge insert_method=first pack_keys=1 union
    -> (ProfilerEventHourlyRollup_14627,ProfilerEventHourlyRollup_14628);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select count(1) from zz where
    -> SrcIPAddress=unhex('030A020502000000000000000000000000');
+----------+
| count(1) |
+----------+
|       35 |
+----------+
1 row in set (0.00 sec)

mysql> select count(1) from ProfilerEventHourlyRollup_14628 where
    -> SrcIPAddress=unhex('030A020502000000000000000000000000');
+----------+
| count(1) |
+----------+
|       31 |
+----------+
1 row in set (0.00 sec)

mysql> select count(1) from ProfilerEventHourlyRollup_14627 where
    -> SrcIPAddress=unhex('030A020502000000000000000000000000');
+----------+
| count(1) |
+----------+
|        4 |
+----------+
1 row in set (0.00 sec)

mysql> alter table zz engine=merge insert_method=first pack_keys=1 union
    -> (ProfilerEventHourlyRollup_14628,ProfilerEventHourlyRollup_14627);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select count(1) from zz where
    -> SrcIPAddress=unhex('030A020502000000000000000000000000');
+----------+
| count(1) |
+----------+
|       35 |
+----------+
1 row in set (0.00 sec)

mysql>
[28 Jan 2010 7:04] Sveta Smirnova
Thank you for the feedback.

You says bug should be repeatable with pack_keys=1 and not repeatable with pack_keys=0, but wrote:

mysql> alter table zz engine=merge insert_method=first pack_keys=1 union
    -> (ProfilerEventHourlyRollup_14627,ProfilerEventHourlyRollup_14628);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select count(1) from zz where
    -> SrcIPAddress=unhex('030A020502000000000000000000000000');
+----------+
| count(1) |
+----------+
|       35 |
+----------+
1 row in set (0.00 sec)

Which value of pack_keys should be set to each table to repeat the bug?
[28 Jan 2010 16:48] MySQL Verification Team
Sveta,

Jonathon has cited above the only package that shows this error. Please, try to verify with that package and with the last OEM package available, like 5.1.42-enterprise-commercial-classic, if I am not wrong.
[28 Jan 2010 19:37] Sveta Smirnova
Sinisa,

still not repeatable with mysql-classic-5.1.42-linux-x86_64-glibc23.tar.gz
[28 Jan 2010 21:05] MySQL Verification Team
Sveta,

The problem appears when one or more of the base tables have pack_keys=1, rather than all of them (including the merge table) being the same. 

ProfilerEventHourlyRollup_14627  => pack_keys=0
ProfilerEventHourlyRollup_14628  => pack_keys=0
zz => pack_keys=0

This all works for the count statements that are shown.

ProfilerEventHourlyRollup_14627  => pack_keys=1
ProfilerEventHourlyRollup_14628  => pack_keys=0
zz => pack_keys=1

This, however, does not. 

Here it is again in version 5.1.42-enterprise-commercial-classic OEM software.

mysql> alter table ProfilerEventHourlyRollup_14627 pack_keys=1;
Query OK, 115 rows affected (0.01 sec)
Records: 115  Duplicates: 0  Warnings: 0

mysql> alter table ProfilerEventHourlyRollup_14628 pack_keys=1;
Query OK, 2768 rows affected (0.11 sec)
Records: 2768  Duplicates: 0  Warnings: 0

mysql> alter table zz engine=merge insert_method=first pack_keys=1 union (ProfilerEventHourlyRollup_14627,ProfilerEventHourlyRollup_14628);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select count(1) from zz where SrcIPAddress=unhex('030A020502000000000000000000000000');
+----------+
| count(1) |
+----------+
|       35 |
+----------+
1 row in set (0.00 sec)

mysql>  select count(1) from ProfilerEventHourlyRollup_14628 where SrcIPAddress=unhex('030A020502000000000000000000000000'); 
+----------+
| count(1) |
+----------+
|       31 |
+----------+
1 row in set (0.00 sec)

mysql> select count(1) from ProfilerEventHourlyRollup_14627 where SrcIPAddress=unhex('030A020502000000000000000000000000');
+----------+
| count(1) |
+----------+
|        4 |
+----------+
1 row in set (0.00 sec)

mysql> alter table zz engine=merge insert_method=first pack_keys=1 union (ProfilerEventHourlyRollup_14628,ProfilerEventHourlyRollup_14627);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select count(1) from zz where SrcIPAddress=unhex('030A020502000000000000000000000000');
+----------+
| count(1) |
+----------+
|       35 |
+----------+
1 row in set (0.00 sec)

mysql> alter table ProfilerEventHourlyRollup_14628 pack_keys=0;
Query OK, 2768 rows affected (0.05 sec)
Records: 2768  Duplicates: 0  Warnings: 0

mysql> alter table zz engine=merge insert_method=first pack_keys=1 union (ProfilerEventHourlyRollup_14627,ProfilerEventHourlyRollup_14628);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select count(1) from zz where SrcIPAddress=unhex('030A020502000000000000000000000000');
+----------+
| count(1) |
+----------+
|        4 |
+----------+
1 row in set (0.00 sec)

mysql> select count(1) from ProfilerEventHourlyRollup_14628 where SrcIPAddress=unhex('030A020502000000000000000000000000'); 
+----------+
| count(1) |
+----------+
|       31 |
+----------+
1 row in set (0.00 sec)

mysql> select count(1) from ProfilerEventHourlyRollup_14627 where SrcIPAddress=unhex('030A020502000000000000000000000000');
+----------+
| count(1) |
+----------+
|        4 |
+----------+
1 row in set (0.00 sec)

You can see here that as soon as one of the base tables has a pack_key value that is different, the results are incorrect for the merge table. 

Please also remember that for this bug to appear, the schema has to have the binary data type as a field in the tables.
[29 Jan 2010 7:02] Sveta Smirnova
Thank you for the feedback.

Verified as described. Repeatable with 5.1 from bzr as well.
[15 Feb 2010 17:38] Sveta Smirnova
BINARY and CHAR data types are affected, VARCHAR is not.
[15 Feb 2010 18:16] Paul DuBois
Added note to manual pending fix for this bug:

In some cases, differing PACK_KEYS table option values among the
MERGE and underlying tables cause unexpected results if the
underlying tables contain CHAR or BINARY columns. As a workaround,
use ALTER TABLE to ensure that all involved tables have the same
PACK_KEYS value. 

http://dev.mysql.com/doc/refman/5.1/en/merge-table-problems.html
[14 Jul 2010 21:22] MySQL Verification Team
With the new partitioning capabilities and the very simple workaround, this bug will not be fixed.