Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
5.5.30
-
None
Description
Initially reported on the sales list. Quoting the complete text of the report from the mail, additional information follows the quote:
System: Windows 32 and 64 bit (tested on XP/32, 2003/32 and 2008/64)
Effected: MariaDB up to 5.5.30I have a specific (attached) Aria table with 2 columns, the fist being indexed as non-unique.
CREATE TABLE `test` (
`id` SMALLINT(5) UNSIGNED NOT NULL,
`kundenid` SMALLINT(5) UNSIGNED NOT NULL,
INDEX `id` (`id`)
)
ENGINE=Aria;In this table there are 42 records, 13 of them having id=4. When I add 2 more with id 4 and query all entries with id=4, it only shows me the 13 that already were in the database, but not the 2 newly added. They are only shown when I query all records.
How to reproduce:
1) Install a fresh copy of MariaDB (5.5.30)
2) Copy attached my.ini in the appropriate folder and
3) the test.* files in the test database.
4) Run following queries:SELECT * FROM test;
/* Affected rows: 0 Found rows: 42 Warnings: 0 Duration for 1 query: 0,000 sec. */SELECT * FROM test WHERE id=4;
/* Affected rows: 0 Found rows: 13 Warnings: 0 Duration for 1 query: 0,000 sec. */INSERT INTO `test` (`id`, `kundenid`) VALUES (4, 12345);
INSERT INTO `test` (`id`, `kundenid`) VALUES (4, 24587);
/* Affected rows: 2 Found rows: 0 Warnings: 0 Duration for 2 queries: 0,016 sec. */SELECT * FROM test;
/* Affected rows: 0 Found rows: 44 Warnings: 0 Duration for 1 query: 0,000 sec. */SELECT * FROM test WHERE id=4;
/* Affected rows: 0 Found rows: 13 Warnings: 0 Duration for 1 query: 0,000 sec. */Only
REPAIR TABLE `test`;
recovers the missing records when selecting WHERE id=4:REPAIR TABLE `test`;
/* Affected rows: 0 Found rows: 1 Warnings: 0 Duration for 1 query: 0,062 sec. */
SELECT * FROM test WHERE id=4;
/* Affected rows: 0 Found rows: 15 Warnings: 0 Duration for 1 query: 0,000 sec. */
Additional information
The issue is reproducible on Linux as well, even without the my.ini file. The problems start with the first INSERT. On a release build, it causes the table corruption, on the debug build it causes assertion failure.
On a debug build:
mysqld: 5.5/storage/maria/ma_key.c:103: transid_store_packed: Assertion `trid >= info->s->state.create_trid' failed.
|
[ERROR] mysqld got signal 6 ;
|
#7 0x00007fd94ec0a192 in __GI___assert_fail (assertion=0xe0fed8 "trid >= info->s->state.create_trid", file=0xe0fe90 "5.5/storage/maria/ma_key.c", line=103, function=0xe0fff0 "transid_store_packed") at assert.c:103
|
#8 0x00000000009b3cf9 in transid_store_packed (info=0x7fd904013490, to=0x7fd904017bbb "\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245", trid=44) at 5.5/storage/maria/ma_key.c:103
|
#9 0x00000000009b46a1 in _ma_make_key (info=0x7fd904013490, int_key=0x7fd9503cd8e0, keynr=0, key=0x7fd904017bb7 "", record=0x7fd904008e68 "\377\004", filepos=298, trid=44) at 5.5/storage/maria/ma_key.c:326
|
#10 0x00000000009d62ba in maria_write (info=0x7fd904013490, record=0x7fd904008e68 "\377\004") at 5.5/storage/maria/ma_write.c:188
|
#11 0x000000000095c805 in ha_maria::write_row (this=0x7fd90400c6d8, buf=0x7fd904008e68 "\377\004") at 5.5/storage/maria/ha_maria.cc:1265
|
#12 0x00000000007d06c9 in handler::ha_write_row (this=0x7fd90400c6d8, buf=0x7fd904008e68 "\377\004") at 5.5/sql/handler.cc:5162
|
#13 0x00000000005f57a7 in write_record (thd=0x3f60bd0, table=0x7fd90400bd10, info=0x7fd9503cdb50) at 5.5/sql/sql_insert.cc:1850
|
#14 0x00000000005f33fc in mysql_insert (thd=0x3f60bd0, table_list=0x7fd904006628, fields=..., values_list=..., update_fields=..., update_values=..., duplic=DUP_ERROR, ignore=false) at 5.5/sql/sql_insert.cc:995
|
#15 0x0000000000611d4e in mysql_execute_command (thd=0x3f60bd0) at 5.5/sql/sql_parse.cc:2935
|
#16 0x0000000000619df6 in mysql_parse (thd=0x3f60bd0, rawbuf=0x7fd904006508 "INSERT INTO `test` (`id`, `kundenid`) VALUES (4, 12345)", length=55, parser_state=0x7fd9503ce550) at 5.5/sql/sql_parse.cc:5756
|
#17 0x000000000060d598 in dispatch_command (command=COM_QUERY, thd=0x3f60bd0, packet=0x404fd11 "INSERT INTO `test` (`id`, `kundenid`) VALUES (4, 12345)", packet_length=55) at 5.5/sql/sql_parse.cc:1068
|
#18 0x000000000060c7dd in do_command (thd=0x3f60bd0) at 5.5/sql/sql_parse.cc:794
|
#19 0x0000000000710e36 in do_handle_one_connection (thd_arg=0x3f60bd0) at 5.5/sql/sql_connect.cc:1266
|
#20 0x0000000000710821 in handle_one_connection (arg=0x3f60bd0) at 5.5/sql/sql_connect.cc:1181
|
#21 0x00007fd94f9dae9a in start_thread (arg=0x7fd9503cf700) at pthread_create.c:308
|
The stack trace from
revision-id: sanja@askmonty.org-20130314170720-7qhmgzam5831ak30
|
revno: 3699
|
On a release build:
MariaDB [test]> check table test extended; |
+-----------+-------+----------+----------+age done |
| Table | Op | Msg_type | Msg_text | |
+-----------+-------+----------+----------+ |
| test.test | check | status | OK | |
+-----------+-------+----------+----------+ |
1 row in set (0.12 sec) |
|
|
MariaDB [test]> show index in test; |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ |
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ |
| test | 1 | id | 1 | id | A | 5 | NULL | NULL | | BTREE | | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ |
1 row in set (0.00 sec) |
|
|
MariaDB [test]> select count(*) from test; |
+----------+ |
| count(*) | |
+----------+ |
| 42 |
|
+----------+ |
1 row in set (0.06 sec) |
|
|
MariaDB [test]> show table status like 'test'; |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+-----------------+----------------------------------------+ |
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+-----------------+----------------------------------------+ |
| test | Aria | 10 | Page | 42 | 390 | 16384 | 68719484928 | 16384 | 0 | NULL | 2013-01-07 16:46:42 | 2013-03-18 01:38:11 | 2013-03-18 01:38:51 | utf8_general_ci | NULL | page_checksum=1 | Loginrechte für die Nielsen-Statistik | |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+-----------------+----------------------------------------+ |
1 row in set (0.05 sec) |
|
|
MariaDB [test]> INSERT INTO `test` (`id`, `kundenid`) VALUES (4, 12345); |
Query OK, 1 row affected (0.05 sec)
|
|
|
MariaDB [test]> check table test extended; |
+-----------+-------+----------+---------------------------------------------------------------+ |
| Table | Op | Msg_type | Msg_text | |
+-----------+-------+----------+---------------------------------------------------------------+ |
| test.test | check | error | Found key at page 8192 that points to record outside datafile | |
| test.test | check | error | Corrupt | |
+-----------+-------+----------+---------------------------------------------------------------+ |
2 rows in set (0.05 sec) |
However, I couldn't reproduce the problem by dumping the table and restoring the dump. I could try more if I get any hint on what can possibly cause this. Meanwhile I asked the reporter by email for more information on how they got the table in this state, haven't got a response yet.
The issue was that the table had a marker 'transaction id of creation time' that was bigger than the current transaction id for the server.
This could happen if:
Normally MariaDB should have noticed that the file was copied and done an automatic zerofill on the table. The test to detect this didn't however work in the case where the table was just repaired before copied.
I have now fixed this and will push this in to the 5.5 tree.
I did the additional fixes:
Until you have the next release, you should do a 'aria_chk --zerofill' on the table before you install it on another server than the original one.