|
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.
|