Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Minor
-
Resolution: Fixed
-
Affects Version/s: 5.5.25
-
Fix Version/s: 5.5.33a
-
Component/s: Partitioning
-
Labels:
-
Environment:Debian Squeeze
Description
Hi there,
It seems there is a bug with the partioning when you want to migrate from a mysql 5.1 to MariaDB 5.5.
We noticed the issue after trying to check the consistency of our MariaDB 5.5 server with the mysql 5.1 master, and noticed we had some duplicate key. We tried to reproduce the duplicate key issue but didn't succeed, instead we crash the 5.5 server. So it seems there is definitely something wrong with the partioning between those two version.
FYI concerning the duplicate here what we can see :
CREATE TABLE `attributes_values_offers` (
`value_id` int(20) NOT NULL,
`offer_id` int(20) NOT NULL,
`reference` tinyint(1) DEFAULT '1',
`source_id` int(11) NOT NULL DEFAULT '0',
`crud` enum('created','updated','deleted','default') COLLATE utf8_bin DEFAULT 'default',
`date_last_change` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`offer_id`,`value_id`,`source_id`),
KEY `value_id` (`value_id`),
KEY `crud` (`crud`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
/*!50100 PARTITION BY KEY ()
PARTITIONS 60 */ |
MariaDB [part]> select * from attributes_values_offers where offer_id=512 and value_id=4845491;
+----------+----------+-----------+-----------+---------+---------------------+
| value_id | offer_id | reference | source_id | crud | date_last_change |
+----------+----------+-----------+-----------+---------+---------------------+
| 4845491 | 512 | 1 | 0 | default | 2007-10-31 09:40:40 |
+----------+----------+-----------+-----------+---------+---------------------+
1 row in set (0.00 sec)
MariaDB [part]> delete from attributes_values_offers where offer_id=512 and value_id=4845491;
Query OK, 1 row affected (0.00 sec)
MariaDB [part]> select * from attributes_values_offers where offer_id=512 and value_id=4845491;
+----------+----------+-----------+-----------+---------+---------------------+
| value_id | offer_id | reference | source_id | crud | date_last_change |
+----------+----------+-----------+-----------+---------+---------------------+
| 4845491 | 512 | 1 | 0 | default | 2007-10-31 09:40:40 |
+----------+----------+-----------+-----------+---------+---------------------+
1 row in set (0.00 sec)
MariaDB [part]> insert into attributes_values_offers (value_id,offer_id,source_id) values (4845491,512,0);
Query OK, 1 row affected (0.00 sec)
MariaDB [part]> select * from attributes_values_offers where offer_id=512 and value_id=4845491;
+----------+----------+-----------+-----------+---------+---------------------+
| value_id | offer_id | reference | source_id | crud | date_last_change |
+----------+----------+-----------+-----------+---------+---------------------+
| 4845491 | 512 | 1 | 0 | default | 2007-10-31 09:40:40 |
| 4845491 | 512 | 1 | 0 | default | 2012-08-20 14:39:50 |
+----------+----------+-----------+-----------+---------+---------------------+
2 rows in set (0.00 sec)
Please note that the issue is not seen when using a table with no partition.
The issue we were able to reproduce is to create two new tables, one with partitions the other one without. When we migrate from 5.1 to MariaDB 5.5 and we delete an entry from the table with partitions the 5.5 server crashes. The other table correctly delete the entry.
Here is the crash output :
120820 14:10:35 InnoDB: Assertion failure in thread 139815058048768 in file row0mysql.c line 1480 InnoDB: Failing assertion: node->pcur->rel_pos == BTR_PCUR_ON InnoDB: We intentionally generate a memory trap. InnoDB: Submit a detailed bug report to http://bugs.mysql.com. InnoDB: If you get repeated assertion failures or crashes, even InnoDB: immediately after the mysqld startup, there may be InnoDB: corruption in the InnoDB tablespace. Please refer to InnoDB: http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html InnoDB: about forcing recovery. 120820 14:10:35 [ERROR] mysqld got signal 6 ; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. To report this bug, see http://kb.askmonty.org/en/reporting-bugs We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. Server version: 5.5.25-MariaDB-mariadb1~squeeze-log key_buffer_size=2147483648 read_buffer_size=4194304 max_used_connections=1 max_threads=5002 thread_count=1 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 32899141 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. Thread pointer: 0x0x7f51eb6fc230 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... stack_bottom = 0x7f293ade7e78 thread_stack 0x30000 /usr/sbin/mysqld(my_print_stacktrace+0x2e)[0x7f50fb850e2e] /usr/sbin/mysqld(handle_fatal_signal+0x4ac)[0x7f50fb46385c] /lib/libpthread.so.0(+0xeff0)[0x7f50fab71ff0] /lib/libc.so.6(gsignal+0x35)[0x7f50f944d1b5] /lib/libc.so.6(abort+0x180)[0x7f50f944ffc0] /usr/sbin/mysqld(+0x7628df)[0x7f50fb7018df] /usr/sbin/mysqld(+0x744beb)[0x7f50fb6e3beb] /usr/sbin/mysqld(_ZN7handler13ha_delete_rowEPKh+0x6d)[0x7f50fb467b2d] /usr/sbin/mysqld(+0x8888ea)[0x7f50fb8278ea] /usr/sbin/mysqld(_ZN7handler13ha_delete_rowEPKh+0x6d)[0x7f50fb467b2d] /usr/sbin/mysqld(_Z12mysql_deleteP3THDP10TABLE_LISTP4ItemP10SQL_I_ListI8st_orderEyy+0xba3)[0x7f50fb5a1a33] /usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x2804)[0x7f50fb312854] /usr/sbin/mysqld(_Z11mysql_parseP3THDPcjP12Parser_state+0x1cc)[0x7f50fb31617c] /usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x149c)[0x7f50fb31762c] /usr/sbin/mysqld(_Z24do_handle_one_connectionP3THD+0x133)[0x7f50fb3d6c73] /usr/sbin/mysqld(handle_one_connection+0x51)[0x7f50fb3d6dd1] /lib/libpthread.so.0(+0x68ca)[0x7f50fab698ca] /lib/libc.so.6(clone+0x6d)[0x7f50f94ea92d] Trying to get some variables. Some pointers may be invalid and cause the dump to abort. Query (0x7f51eb7940a8): delete from avo_part where value_id=4845491 and offer_id = 402 Connection ID (thread ID): 1 Status: NOT_KILLED Optimizer switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains information that should help you find out what is causing the crash. 120820 14:10:36 mysqld_safe Number of processes running now: 0 120820 14:10:36 mysqld_safe mysqld restarted 120820 14:10:36 [ERROR] An old style --language value with language specific part detected: /usr/share/mysql/english/ 120820 14:10:36 [ERROR] Use --lc-messages-dir without language specific part instead. 120820 14:10:36 [Warning] option 'aria-sort-buffer-size': unsigned value 10737418240 adjusted to 4294967295 120820 14:10:37 InnoDB: The InnoDB memory heap is disabled 120820 14:10:37 InnoDB: Mutexes and rw_locks use GCC atomic builtins 120820 14:10:37 InnoDB: Compressed tables use zlib 1.2.3.4 120820 14:10:37 InnoDB: Using Linux native AIO 120820 14:10:37 InnoDB: Initializing buffer pool, size = 140.0G 120820 14:10:42 InnoDB: Completed initialization of buffer pool 120820 14:10:42 InnoDB: highest supported file format is Barracuda. InnoDB: The log sequence number in ibdata files does not match InnoDB: the log sequence number in the ib_logfiles! 120820 14:10:43 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer...
In order to repeat the server crash :
1- Under mysql 5.1 server create two tables :
CREATE TABLE `avo_part` (
`value_id` int(20) NOT NULL,
`offer_id` int(20) NOT NULL,
`reference` tinyint(1) DEFAULT '1',
`source_id` int(11) NOT NULL DEFAULT '0',
`crud` enum('created','updated','deleted','default') COLLATE utf8_bin DEFAULT 'default',
`date_last_change` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`offer_id`,`value_id`,`source_id`),
KEY `value_id` (`value_id`),
KEY `crud` (`crud`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
alter table avo_part partition by key() PARTITIONS 60;
CREATE TABLE `avo_nopart` (
`value_id` int(20) NOT NULL,
`offer_id` int(20) NOT NULL,
`reference` tinyint(1) DEFAULT '1',
`source_id` int(11) NOT NULL DEFAULT '0',
`crud` enum('created','updated','deleted','default') COLLATE utf8_bin DEFAULT 'default',
`date_last_change` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`offer_id`,`value_id`,`source_id`),
KEY `value_id` (`value_id`),
KEY `crud` (`crud`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
2- Insert data within the two tables :
insert into avo_part (value_id,offer_id,source_id) values (4845491,402,0); insert into avo_nopart (value_id,offer_id,source_id) values (4845491,402,0);
3- Migrate your server to 5.5
4- Start and run mysql_upgrade then restart
5- Try to play this on avo_nopart (It should work) :
delete from avo_nopart where value_id=4845491 and offer_id = 402;
6- Try to play this on avo_part :
delete from avo_part where value_id=4845491 and offer_id = 402;
The server crashes.
From now on the only workaround we found is to either delete the partioning on the 5.1.
Or simply run this command as soon as you' ve upgraded :
ALTER table avo_part remove partitioning; ALTER TABLE avo_part PARTITION BY KEY() PARTITIONS 60;
Please note we also tested with mysql 5.5 server and we have the exact same issue.
Gliffy Diagrams
Attachments
Issue Links
- links to
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Reproducible on Maria 5.3.7 => 5.5 revno 3497 as described above:
- On 5.1 - 5.3, execute
Execute before upgrade
CREATE TABLE `avo_part` (
`value_id` int(20) NOT NULL,
`offer_id` int(20) NOT NULL,
`reference` tinyint(1) DEFAULT '1',
`source_id` int(11) NOT NULL DEFAULT '0',
`crud` enum('created','updated','deleted','default') COLLATE utf8_bin DEFAULT 'default',
`date_last_change` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`offer_id`,`value_id`,`source_id`),
KEY `value_id` (`value_id`),
KEY `crud` (`crud`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
alter table avo_part partition by key() PARTITIONS 60;
insert into avo_part (value_id,offer_id,source_id) values (4845491,402,0);
- Stop server,
- start 5.5 server on the same datadir,
- run mysql_upgrade,
- execute
Execute after upgrade
delete from avo_part where value_id=4845491 and offer_id = 402;
#4 0x00007fa35ee0a3a5 in __GI_raise (sig=6) at ../nptl/sysdeps/unix/sysv/linux/raise.c:64
#5 0x00007fa35ee0db0b in __GI_abort () at abort.c:92
#6 0x0000000000a4586a in row_update_for_mysql (mysql_rec=0x3709d68 "\371\263\357I",
prebuilt=0x37aa7c8) at storage/xtradb/row/row0mysql.c:1480
#7 0x0000000000a19c55 in ha_innobase::delete_row (this=0x36efde8, record=0x3709d68 "\371\263\357I")
at storage/xtradb/handler/ha_innodb.cc:6414
#8 0x00000000007cad26 in handler::ha_delete_row (this=0x36efde8, buf=0x3709d68 "\371\263\357I")
at sql/handler.cc:5125
#9 0x0000000000c84287 in ha_partition::delete_row (this=0x362a128, buf=0x3709d68 "\371\263\357I")
at sql/ha_partition.cc:3721
#10 0x00000000007cad26 in handler::ha_delete_row (this=0x362a128, buf=0x3709d68 "\371\263\357I")
at sql/handler.cc:5125
#11 0x0000000000908146 in mysql_delete (thd=0x7fa338059820, table_list=0x3624290, conds=0x3624e90,
order_list=0x7fa33805c4a8, limit=18446744073709551615, options=0)
at sql/sql_delete.cc:344
#12 0x000000000060f8ed in mysql_execute_command (thd=0x7fa338059820)
at sql/sql_parse.cc:3050
#13 0x0000000000617269 in mysql_parse (thd=0x7fa338059820,
rawbuf=0x3624158 "delete from avo_part where value_id=4845491 and offer_id = 402", length=62,
parser_state=0x7fa360536550) at sql/sql_parse.cc:5736
#14 0x000000000060aadf in dispatch_command (command=COM_QUERY, thd=0x7fa338059820,
packet=0x7fa338112121 "delete from avo_part where value_id=4845491 and offer_id = 402",
packet_length=62) at sql/sql_parse.cc:1055
#15 0x0000000000609d96 in do_command (thd=0x7fa338059820)
at sql/sql_parse.cc:794
If the same is run with a MyISAM table, there is no crash, but the record does not get deleted:
delete from avo_part2 where value_id=4845491 and offer_id = 402; ERROR 1032 (HY000): Can't find record in 'avo_part2' MariaDB [test]> select * from avo_part2 where value_id=4845491 and offer_id = 402; +----------+----------+-----------+-----------+---------+---------------------+ | value_id | offer_id | reference | source_id | crud | date_last_change | +----------+----------+-----------+-----------+---------+---------------------+ | 4845491 | 402 | 1 | 0 | default | 2012-08-20 21:04:56 | +----------+----------+-----------+-----------+---------+---------------------+ 1 row in set (0.00 sec)
Please note that as you mentionned for MyIsam the record can go undeleted for InnoDB and in that case you can be in a position where your table has two entry for a PK.
That's what happened to us and make us notice the bug, but I can't find a way to reproduce a simple test case.
So it may be the record is deleted from the index but not in the data file ? In that specific case if you try to do something like :
ALTER TABLE t REMOVE PARTITIONING;
It throws an exception, warning you there's duplicate content in the table.
Also filed as http://bugs.mysql.com/bug.php?id=66634
Need to check if it's still reproducible on MariaDB and MySQL, since the filed upstream bug is a dead-end: it is marked as a duplicate of another bug which, in turn, is private, so there is no way to know if it's fixed or not.
The problem stopped being reproducible from 5.5.31 release (can't set it as 'Fix version' because it's archived).
Apparently, it was fixed by this revision:
revno: 3077.184.38 [merge]
revision-id: mattias.jonsson@oracle.com-20130130165152-o4tivhs5ty6n6nqa
parent: mysql-builder@oracle.com-20130130090252-1jn0qz7broyqxxa0
parent: mysql-builder@oracle.com-20130130141719-mnu1x79d7w3zd5qe
committer: Mattias Jonsson <mattias.jonsson@oracle.com>
branch nick: topush-5.5
timestamp: Wed 2013-01-30 17:51:52 +0100
message:
Bug#14521864: MYSQL 5.1 TO 5.5 BUGS PARTITIONING
Due to an internal change in the server code in between 5.1 and 5.5
(wl#2649) the hash function used in KEY partitioning changed
for numeric and date/time columns (from binary hash calculation
to character based hash calculation).
Also enum/set changed from latin1 ci based hash calculation to
binary hash between 5.1 and 5.5. (bug#11759782).
These changes makes KEY [sub]partitioned tables on any of
the affected column types incompatible with 5.5 and above,
since the calculation of partition id differs.
Also since InnoDB asserts that a deleted row was previously
read (positioned), the server asserts on delete of a row that
is in the wrong partition.
The solution for this situation is:
1) The partitioning engine will check that delete/update will go to the
partition the row was read from and give an error otherwise, consisting
of the rows partitioning fields. This will avoid asserts in InnoDB and
also alert the user that there is a misplaced row. A detailed error
message will be given, including an entry to the error log consisting
of both table name, partition and row content (PK if exists, otherwise
all partitioning columns).
2) A new optional syntax for KEY () partitioning in 5.5 is allowed:
[SUB]PARTITION BY KEY [ALGORITHM = N] (list_of_cols)
Where N = 1 uses the same hashing as 5.1 (Numeric/date/time fields uses
binary hashing, ENUM/SET uses charset hashing) N = 2 uses the same
hashing as 5.5 (Numeric/date/time fields uses charset hashing,
ENUM/SET uses binary hashing). If not set on CREATE/ALTER it will
default to 2.
This new syntax should probably be ignored by NDB.
3) Since there is a demand for avoiding scanning through the full
table, during upgrade the ALTER TABLE t PARTITION BY ... command is
considered a no-op (only .frm change) if everything except ALGORITHM
is the same and ALGORITHM was not set before, which allows manually
upgrading such table by something like:
ALTER TABLE t PARTITION BY KEY ALGORITHM = 1 () or
ALTER TABLE t PARTITION BY KEY ALGORITHM = 2 ()
4) Enhanced partitioning with CHECK/REPAIR to also check for/repair
misplaced rows. (Also works for ALTER TABLE t CHECK/REPAIR PARTITION)
CHECK FOR UPGRADE:
If the .frm version is < 5.5.3
and uses KEY [sub]partitioning
and an affected column type
then it will fail with an message:
KEY () partitioning changed, please run:
ALTER TABLE `test`.`t1` PARTITION BY KEY ALGORITHM = 1 (a)
PARTITIONS 12
(i.e. current partitioning clause, with the addition of
ALGORITHM = 1)
CHECK without FOR UPGRADE:
if MEDIUM (default) or EXTENDED options are given:
Scan all rows and verify that it is in the correct partition.
Fail for the first misplaced row.
REPAIR:
if default or EXTENDED (i.e. not QUICK/USE_FRM):
Scan all rows and every misplaced row is moved into its correct
partitions.
5) Updated mysqlcheck (called by mysql_upgrade) to handle the
new output from CHECK FOR UPGRADE, to run the ALTER statement
instead of running REPAIR.
This will allow mysql_upgrade (or CHECK TABLE t FOR UPGRADE) to upgrade
a KEY [sub]partitioned table that has any affected field type
and a .frm version < 5.5.3 to ALGORITHM = 1 without rebuild.
Also notice that if the .frm has a version of >= 5.5.3 and ALGORITHM
is not set, it is not possible to know if it consists of rows from
5.1 or 5.5! In these cases I suggest that the user does:
(optional)
LOCK TABLE t WRITE;
SHOW CREATE TABLE t;
(verify that it has no ALGORITHM = N, and to be safe, I would suggest
backing up the .frm file, to be used if one need to change to another
ALGORITHM = N, without needing to rebuild/repair)
ALTER TABLE t <old partitioning clause, but with ALGORITHM = N>;
which should set the ALGORITHM to N (if the table has rows from
5.1 I would suggest N = 1, otherwise N = 2)
CHECK TABLE t;
(here one could use the backed up .frm instead and change to a new N
and run CHECK again and see if it passes)
and if there are misplaced rows:
REPAIR TABLE t;
(optional)
UNLOCK TABLES;
Also reproducible on MySQL 5.1.63 => 5.5.25 and MySQL 5.1.65 => 5.5.27.