1. Ibdconnect And Secondary Keys

Ibdconnect And Secondary Keys

Ibdconnect is a tool to connect an ibd file to a foreign ibdata file. It’s important to understand how it works as secondary keys may cause peculiar behavior.

Here is a table with a unique secondary key:

CREATE TABLE t1 (
id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
value varchar(255) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (id),
UNIQUE KEY uni_value (value)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

When I update the InnoDB dictionary with ibdconnect, it all goes well:

# ./ibdconnect -f  t1.idb -o  /var/lib/mysql/ibdata1 -d test -t t1

Updating test/t1 (table_id 13) with id 0xA3060000
SYS_TABLES is updated successfully

SYS_INDEXES is updated successfully
Ibdconnect modifies ibdata1 content, so checksums are wrong now. Innochecksum_changer from Percona Recvery Toolkit does it:

# ./innochecksum_changer -f /var/lib/ibdata1

Then, I tried to access the table:

root@localhost [(none)]> desc test.t1;
+——-+———————+——+—–+———+—————-+
| Field | Type                | Null | Key | Default | Extra          |
+——-+———————+——+—–+———+—————-+
| id    | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| value | varchar(255)        | NO   | UNI | NULL    |                |
+——-+———————+——+—–+———+—————-+
2 rows in set (0.00 sec)

Good.

Let’s select something from it:

root@localhost [(none)]> select * from test.t1 limit 5;
….<no response>….<ctrl+C>

Ctrl-C — sending “KILL QUERY 3” to server …
Ctrl-C — query aborted.
ERROR 2013 (HY000): Lost connection to MySQL server during query

In the error log I saw this:

121011 23:34:56  InnoDB: Assertion failure in thread 1096689984 in file btr0sea.c line 1549
InnoDB: Failing assertion: index->id == btr_page_get_index_id(page)
InnoDB: We intentionally generate a memory trap.

Index_id from the dictionary doesn’t match index_id from the InnoDB page. Every InnoDB page at the 0x42 position has index_id.

After some investigation, I found that MySQL was trying to read the table by secondary index:

root@localhost [(none)]> explain select * from test.t1 limit 5\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
type: index
possible_keys: NULL
key: uni_value
key_len: 767
ref: NULL
rows: 1
Extra: Using index
1 row in set (0.00 sec)

That’s strange, but let’s rewrite the query to read from the PRIMARY index:

root@localhost [(none)]> explain select * from test.t1 order by id limit 5\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
type: index
possible_keys: NULL
key: PRIMARY
key_len: 8
ref: NULL
rows: 1
Extra:
1 row in set (0.00 sec)

And now it works:

root@localhost [(none)]> select * from test.t1 order by id limit 5\G
*************************** 1. row ***************************
id: 1
value: aaa
*************************** 2. row ***************************
id: 2
value: bbb
*************************** 3. row ***************************
id: 3
value: ccc
*************************** 4. row ***************************
id: 4
value: ddd
*************************** 5. row ***************************
id: 5
value: eee
5 rows in set (0.00 sec)

Now we can dump the table:

mysqldump –skip-lock-tables test t1 > t1.sql

However, it doesn’t work because mysqldump sends “SELECT * FROM t1”.

We can instruct it to use the PRIMARY index:



# mysqldump –order-by-primary –skip-lock-tables test t1> t1.sql

And it went well.

 

Conclusions:

  • Sometimes MySQL doesn’t choose PRIMARY index for SELECT * table queries
  • You learnt new option –order-by-primary
  • It’s clear that ibdconnect doesn’t update the dictionary properly. A potential bug.

Have a question? Ask the experts!

Previous Post Next Post