ER_KEY_NOT_FOUND error without locks_unsafe_for_binlog in INSERT..SELECT

[Raghavendra D Prabhu]

This is possibly an upstream bug.

The issue here is

# 2014-03-15T19:08:08 [39848] Query: INSERT INTO `table1_innodb_default_key_pk_parts_2_varchar_20` ( `col_varchar_200_utf8_key` ) SELECT `col_bigint_key` FROM `table500_innodb_default_int` AS X WHERE X . `col_enum_utf8_key` < 47 ORDER BY `col_bigint`,`col_bigint_key`,`col_char_12_utf8`,`col_char_12_utf8_key`,`col_date`,`col_date_key`,`col_enum_utf8`,`col_enum_utf8_key`,`col_int`,`col_int_key`,`col_varchar_200_utf8`,`col_varchar_200_utf8_key`,`pk` LIMIT 50 failed: 1032 Can't find record in 'table500_innodb_default_int'

This happens possibly due to innodb_lock_wait_timeout on the
SELECT, making INSERT produce it.

This is reproduceable only when innodb_locks_unsafe_for_binlog=OFF (default now). With ON it doesn't occur.

Also, it doesn't break replication.

[Raghavendra D Prabhu (raghavendra-prabhu)]

This can break RQG test though, which signals it as STATUS_DATABASE_CORRUPTION due to ER_KEY_NOT_FOUND.

[Raghavendra D Prabhu (raghavendra-prabhu)]

I this happening even with locks_unsafe_for_binlog ON here: http://jenkins.percona.com/job/PXC-5.5-mrandgen/181/BTYPE=release,Host=pxc-rqg/console

12:39:54 # 2014-03-15T12:36:11 [16185] ErrorLogAlarm Reporter will monitor the log file /rqg/workspace/PXC-5.5-mrandgen/BTYPE/release/Host/pxc-rqg/181/dml-mN5/node0/data/../mysql.err
12:39:55 # 2014-03-15T12:36:12 [16184] Query: REPLACE INTO `table100_innodb_default_key_pk_parts_4_int` ( `col_char_12_utf8` ) VALUES ( 61 ) failed: 1213 Deadlock found when trying to get lock; try restarting transaction. Further errors of this kind will be suppressed.
12:41:26 # 2014-03-15T12:37:43 [16194] Query: REPLACE INTO `table1_innodb_default_key_pk_parts_2_varchar_20` ( `col_char_12_utf8` ) SELECT `col_varchar_200_utf8_key` FROM `table100_innodb_compressed_char_10` AS X ORDER BY `col_bigint`,`col_bigint_key`,`col_char_12_utf8`,`col_char_12_utf8_key`,`col_date`,`col_date_key`,`col_enum_utf8`,`col_enum_utf8_key`,`col_int`,`col_int_key`,`col_varchar_200_utf8`,`col_varchar_200_utf8_key`,`pk` LIMIT 50 failed: 1032 Can't find record in 'table100_innodb_compressed_char_10'
12:45:32 # 2014-03-15T12:41:49 [16212] Query: REPLACE INTO `table100_innodb_compressed_key_pk_parts_4_char_10` ( `col_date_key` ) SELECT `col_char_12_utf8_key` FROM `table5000_innodb_default_key_pk_parts_4_varchar_20` AS X ORDER BY `col_bigint`,`col_bigint_key`,`col_char_12_utf8`,`col_char_12_utf8_key`,`col_date`,`col_date_key`,`col_enum_utf8`,`col_enum_utf8_key`,`col_int`,`col_int_key`,`col_varchar_200_utf8`,`col_varchar_200_utf8_key`,`pk` LIMIT 100 failed: 1032 Can't find record in 'table5000_innodb_default_key_pk_parts_4_varchar_20'

[Krunal Bauskar (krunal-bauskar)]

Haven't seen this failing in 5.6 for while now. Will close this bug for now. Revisit if needed.

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PXC-996