[MDEV-11167] InnoDB: Warning: using a partial-field key prefix in search, results in assertion failure or "Can't find record" error - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Critical
Resolution: Fixed
Affects Version/s: 10.0(EOL), 10.1(EOL), 10.2(EOL)
Fix Version/s: 10.1.38, 10.0.38, 10.2.20
Component/s: Partitioning, Storage Engine - InnoDB, Views
Labels:
None

Description

10.0 c3592ca7b886
2017-10-29 20:13:21 7fa1aa52a700 InnoDB: Warning: using a partial-field key prefix in search.
InnoDB: index `f2` of table `test`.`t` /* Partition `p1` */. Last data field length 6 bytes,
InnoDB: key ptr now exceeds key end by 5 bytes.
InnoDB: Key value in the MySQL format:
len 6; hex 000000000204; asc ;
2017-10-29 20:13:21 7fa1aa52a700 InnoDB: Assertion failure in thread 140332324005632 in file row0sel.cc line 2498
InnoDB: Failing assertion: 0

#5  0x00007fa1a846c3fa in abort () from /lib/x86_64-linux-gnu/libc.so.6

#6  0x00007fa1a1060e72 in row_sel_convert_mysql_key_to_innobase (tuple=0x7fa1945c43f0, buf=0x7fa1945c43ec "", buf_len=4, index=0x7fa1945ac278, key_ptr=0x7fa19479e40e '\245' <repeats 42 times>, "\377", key_len=6, trx=0x7fa194552278) at /data/src/10.0/storage/innobase/row/row0sel.cc:2498

#7  0x00007fa1a0f47856 in ha_innodb::index_read (this=0x7fa194481888, buf=0x7fa1944293a8 "\373", key_ptr=0x7fa19479e403 "", key_len=6, find_flag=HA_READ_KEY_EXACT) at /data/src/10.0/storage/innobase/handler/ha_innodb.cc:8114

#8  0x00007fa1a0f48ba3 in ha_innodb::rnd_pos (this=0x7fa194481888, buf=0x7fa1944293a8 "\373", pos=0x7fa19479e403 "") at /data/src/10.0/storage/innobase/handler/ha_innodb.cc:8687

#9  0x000000000083e828 in handler::ha_rnd_pos (this=0x7fa194481888, buf=0x7fa1944293a8 "\373", pos=0x7fa19479e403 "") at /data/src/10.0/sql/handler.cc:2648

#10 0x0000000000df1030 in ha_partition::rnd_pos (this=0x7fa194480888, buf=0x7fa1944293a8 "\373", pos=0x7fa19479e401 "\001") at /data/src/10.0/sql/ha_partition.cc:5063

#11 0x000000000083e7de in handler::ha_rnd_pos (this=0x7fa194480888, buf=0x7fa1944293a8 "\373", pos=0x7fa19479e401 "\001") at /data/src/10.0/sql/handler.cc:2648

#12 0x000000000071bd34 in multi_update::do_updates (this=0x7fa19475c308) at /data/src/10.0/sql/sql_update.cc:2365

#13 0x000000000071c35e in multi_update::send_eof (this=0x7fa19475c308) at /data/src/10.0/sql/sql_update.cc:2501

#14 0x00000000006ac107 in do_select (join=0x7fa19475c3c8, fields=0x7fa1aa528bd0, table=0x0, procedure=0x0) at /data/src/10.0/sql/sql_select.cc:17661

#15 0x0000000000688d11 in JOIN::exec_inner (this=0x7fa19475c3c8) at /data/src/10.0/sql/sql_select.cc:3093

#16 0x00000000006861ce in JOIN::exec (this=0x7fa19475c3c8) at /data/src/10.0/sql/sql_select.cc:2379

#17 0x0000000000689570 in mysql_select (thd=0x7fa19cb69070, rref_pointer_array=0x7fa19cb6d3a0, tables=0x7fa1945a4160, wild_num=0, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=1342177408, result=0x7fa19475c308, unit=0x7fa19cb6ca08, select_lex=0x7fa19cb6d0f8) at /data/src/10.0/sql/sql_select.cc:3318

#18 0x0000000000719679 in mysql_multi_update (thd=0x7fa19cb69070, table_list=0x7fa1945a4160, fields=0x7fa19cb6d210, values=0x7fa19cb6d6c8, conds=0x0, options=0, handle_duplicates=DUP_ERROR, ignore=false, unit=0x7fa19cb6ca08, select_lex=0x7fa19cb6d0f8, result=0x7fa1aa5292e0) at /data/src/10.0/sql/sql_update.cc:1597

#19 0x000000000064e4bd in mysql_execute_command (thd=0x7fa19cb69070) at /data/src/10.0/sql/sql_parse.cc:3377

#20 0x0000000000656c5e in mysql_parse (thd=0x7fa19cb69070, rawbuf=0x7fa1945a4088 "UPDATE v SET f2 = 1", length=19, parser_state=0x7fa1aa529640) at /data/src/10.0/sql/sql_parse.cc:6569

#21 0x000000000064979d in dispatch_command (command=COM_QUERY, thd=0x7fa19cb69070, packet=0x7fa1a25ef071 "UPDATE v SET f2 = 1", packet_length=19) at /data/src/10.0/sql/sql_parse.cc:1296

#22 0x0000000000648a9d in do_command (thd=0x7fa19cb69070) at /data/src/10.0/sql/sql_parse.cc:999

#23 0x0000000000768954 in do_handle_one_connection (thd_arg=0x7fa19cb69070) at /data/src/10.0/sql/sql_connect.cc:1377

#24 0x00000000007686c6 in handle_one_connection (arg=0x7fa19cb69070) at /data/src/10.0/sql/sql_connect.cc:1292

#25 0x0000000000ac91fe in pfs_spawn_thread (arg=0x7fa19cb19670) at /data/src/10.0/storage/perfschema/pfs.cc:1861

#26 0x00007fa1aa167494 in start_thread (arg=0x7fa1aa52a700) at pthread_create.c:333

#27 0x00007fa1a852093f in clone () from /lib/x86_64-linux-gnu/libc.so.6

Also fails on 10.1 (38e12db478fde), 10.2 (e5678c3fac27af), 10.3 (ecee3c71e1e740). These are not first revisions where it started happening, in fact, it can be a rather old problem.

While fixing, please check all test cases below. They have subtle differences, but on some reason cause or don't cause crashes on different versions. Maybe it's just non-determinism, or maybe those differences are somehow important.

Crash in 10.3
--source include/have_innodb.inc
--source include/have_partition.inc

CREATE TABLE t1 (a INT) ENGINE=InnoDB;
CREATE TABLE t2 (b INT, c INT, KEY(b)) ENGINE=InnoDB PARTITION BY HASH(c) PARTITIONS 2;
CREATE ALGORITHM = MERGE VIEW v AS SELECT a, b FROM t1 STRAIGHT_JOIN t2 WHERE b = 'foo' WITH CHECK OPTION;

INSERT INTO t1 VALUES (1),(2);
INSERT IGNORE INTO t2 VALUES (2,2),('three',3),(4,4);
UPDATE v SET a = NULL ORDER BY a, b;

DROP TABLE t1, t2;

Crash in 10.0, 10.2 and 10.3
--source include/have_innodb.inc
--source include/have_partition.inc

SET GLOBAL innodb_stats_persistent= ON;

CREATE TABLE t (f1 INT, f2 INT, KEY(f2)) ENGINE=InnoDB PARTITION BY HASH (f1) PARTITIONS 2;
INSERT IGNORE INTO t VALUES (NULL,0),(NULL,0),(0,21),(4,0),(1,8),(5,66);
CREATE ALGORITHM=MERGE VIEW v AS SELECT t1.* FROM t t1 JOIN t t2 WHERE t1.f1 < t2.f2 WITH LOCAL CHECK OPTION;
UPDATE v SET f2 = NULL;

Crash in all 10.x
--source include/have_innodb.inc
--source include/have_partition.inc

CREATE TABLE t (f1 INT, f2 INT, f3 INT, KEY(f2))
ENGINE=InnoDB
PARTITION BY RANGE (f1) (
PARTITION p0 VALUES LESS THAN (1),
PARTITION p1 VALUES LESS THAN (128),
PARTITION p2 VALUES LESS THAN MAXVALUE
);
INSERT INTO t VALUES (NULL,0,24),(NULL,0,0),(0,21,0),(4,0,NULL),(1,8,2),(5,66,0);

CREATE ALGORITHM=MERGE VIEW v AS SELECT t1.* FROM t t1 JOIN t t2 WHERE t1.f1 < t2.f2 WITH LOCAL CHECK OPTION;
UPDATE v SET f2 = 1;

# Cleanup
DROP VIEW v;
DROP TABLE t;

Attachments

Issue Links

is duplicated by

MDEV-14408 Assertion `0' failed in row_sel_convert_mysql_key_to_innobase

Closed

relates to

MDEV-16240 Assertion `0' failed in row_sel_convert_mysql_key_to_innobase

Closed

Activity

Ascending order - Click to sort in descending order

Marko Mäkelä added a comment - 2017-10-31 17:27

For the 10.0 test, the crash occurs in

mysqltest: At line 12: query 'UPDATE v SET f2 = 1' failed: 2013: Lost connection to MySQL server during query

on index f2 of partition p1. The search key is 6 bytes 0x204, which very likely is a DB_ROW_ID. The dict_sys->db_row_id is 0x206.

The problem seems to be that when searching for a row id (which only exists if the table lacks a PRIMARY KEY or a UNIQUE index on NOT NULL columns), the active index should be changed to the internal clustered index (identified by MAX_KEY). In fact, we do have ha_innobase::active_index == 64 here.

I am a bit unsure what should be changed and where. The MySQL 5.7 code for ha_innobase and ha_innopart looks similar for rnd_pos() and index_read().

Marko Mäkelä added a comment - 2017-10-31 17:27 For the 10.0 test, the crash occurs in mysqltest: At line 12: query 'UPDATE v SET f2 = 1' failed: 2013: Lost connection to MySQL server during query on index f2 of partition p1. The search key is 6 bytes 0x204, which very likely is a DB_ROW_ID. The dict_sys->db_row_id is 0x206. The problem seems to be that when searching for a row id (which only exists if the table lacks a PRIMARY KEY or a UNIQUE index on NOT NULL columns), the active index should be changed to the internal clustered index (identified by MAX_KEY). In fact, we do have ha_innobase::active_index == 64 here. I am a bit unsure what should be changed and where. The MySQL 5.7 code for ha_innobase and ha_innopart looks similar for rnd_pos() and index_read().

Marko Mäkelä added a comment - 2018-02-13 06:17

For what it is worth, there was a SQL layer change related to this in MySQL 5.6.8 in 2012.

Marko Mäkelä added a comment - 2018-02-13 06:17 For what it is worth, there was a SQL layer change related to this in MySQL 5.6.8 in 2012.

Marko Mäkelä added a comment - 2018-06-21 14:31

I cannot reproduce any crash in 10.3 9dc81f7d387050dd62f2307b15c63c3a3f5ea1b0. Instead, errors will be flagged for the UPDATE statements:

--source include/have_innodb.inc

--source include/have_partition.inc

CREATE TABLE t1 (a INT) ENGINE=InnoDB;

CREATE TABLE t2 (b INT, c INT, KEY(b)) ENGINE=InnoDB PARTITION BY HASH(c) PARTITIONS 2;

CREATE ALGORITHM = MERGE VIEW v AS SELECT a, b FROM t1 STRAIGHT_JOIN t2 WHERE b = 'foo' WITH CHECK OPTION;

INSERT INTO t1 VALUES (1),(2);

INSERT IGNORE INTO t2 VALUES (2,2),('three',3),(4,4);

--error ER_TRUNCATED_WRONG_VALUE

UPDATE v SET a = NULL ORDER BY a, b;

DROP VIEW v;

DROP TABLE t1, t2;

SET GLOBAL innodb_stats_persistent= ON;

CREATE TABLE t (f1 INT, f2 INT, KEY(f2)) ENGINE=InnoDB PARTITION BY HASH (f1) PARTITIONS 2;

INSERT IGNORE INTO t VALUES (NULL,0),(NULL,0),(0,21),(4,0),(1,8),(5,66);

CREATE ALGORITHM=MERGE VIEW v AS SELECT t1.* FROM t t1 JOIN t t2 WHERE t1.f1 < t2.f2 WITH LOCAL CHECK OPTION;

--error ER_VIEW_CHECK_FAILED

UPDATE v SET f2 = NULL;

DROP VIEW v;

DROP TABLE t;

CREATE TABLE t (f1 INT, f2 INT, f3 INT, KEY(f2))

ENGINE=InnoDB

PARTITION BY RANGE (f1) (

  PARTITION p0 VALUES LESS THAN (1),

  PARTITION p1 VALUES LESS THAN (128),

  PARTITION p2 VALUES LESS THAN MAXVALUE

);

INSERT INTO t VALUES (NULL,0,24),(NULL,0,0),(0,21,0),(4,0,NULL),(1,8,2),(5,66,0);

CREATE ALGORITHM=MERGE VIEW v AS SELECT t1.* FROM t t1 JOIN t t2 WHERE t1.f1 < t2.f2 WITH LOCAL CHECK OPTION;

--error ER_VIEW_CHECK_FAILED

UPDATE v SET f2 = 1;

DROP VIEW v;

DROP TABLE t;

Marko Mäkelä added a comment - 2018-06-21 14:31 I cannot reproduce any crash in 10.3 9dc81f7d387050dd62f2307b15c63c3a3f5ea1b0. Instead, errors will be flagged for the UPDATE statements: --source include/have_innodb.inc --source include/have_partition.inc CREATE TABLE t1 (a INT ) ENGINE=InnoDB; CREATE TABLE t2 (b INT , c INT , KEY (b)) ENGINE=InnoDB PARTITION BY HASH(c) PARTITIONS 2; CREATE ALGORITHM = MERGE VIEW v AS SELECT a, b FROM t1 STRAIGHT_JOIN t2 WHERE b = 'foo' WITH CHECK OPTION ; INSERT INTO t1 VALUES (1),(2); INSERT IGNORE INTO t2 VALUES (2,2),( 'three' ,3),(4,4); --error ER_TRUNCATED_WRONG_VALUE UPDATE v SET a = NULL ORDER BY a, b; DROP VIEW v; DROP TABLE t1, t2; SET GLOBAL innodb_stats_persistent= ON ; CREATE TABLE t (f1 INT , f2 INT , KEY (f2)) ENGINE=InnoDB PARTITION BY HASH (f1) PARTITIONS 2; INSERT IGNORE INTO t VALUES ( NULL ,0),( NULL ,0),(0,21),(4,0),(1,8),(5,66); CREATE ALGORITHM=MERGE VIEW v AS SELECT t1.* FROM t t1 JOIN t t2 WHERE t1.f1 < t2.f2 WITH LOCAL CHECK OPTION ; --error ER_VIEW_CHECK_FAILED UPDATE v SET f2 = NULL ; DROP VIEW v; DROP TABLE t; CREATE TABLE t (f1 INT , f2 INT , f3 INT , KEY (f2)) ENGINE=InnoDB PARTITION BY RANGE (f1) ( PARTITION p0 VALUES LESS THAN (1), PARTITION p1 VALUES LESS THAN (128), PARTITION p2 VALUES LESS THAN MAXVALUE ); INSERT INTO t VALUES ( NULL ,0,24),( NULL ,0,0),(0,21,0),(4,0, NULL ),(1,8,2),(5,66,0); CREATE ALGORITHM=MERGE VIEW v AS SELECT t1.* FROM t t1 JOIN t t2 WHERE t1.f1 < t2.f2 WITH LOCAL CHECK OPTION ; --error ER_VIEW_CHECK_FAILED UPDATE v SET f2 = 1; DROP VIEW v; DROP TABLE t;

Marko Mäkelä added a comment - 2018-06-21 14:53

10.2 d79bf0009a17f0020203003a97ce7e83449aeb3a
CURRENT_TEST: innodb.mdev-11167
mysqltest: At line 25: query 'UPDATE v SET f2 = NULL' failed with wrong errno 2013: 'Lost connection to MySQL server during query', instead of 1369...
…
Version: '10.2.16-MariaDB-debug-log' socket: '/dev/shm/10.2/mysql-test/var/tmp/mysqld.1.sock' port: 16000 Source distribution
2018-06-21 17:39:12 140200100906752 [Warning] InnoDB: Using a partial-field key prefix in search, index `f2` of table `test`.`t` /* Partition `p1` */. Last data field length 6 bytes, key ptr now exceeds key end by 5 bytes. Key value in the MySQL format:
len 6; hex 000000000209; asc ;
mysqld: /mariadb/10.2/storage/innobase/row/row0sel.cc:2740: void row_sel_convert_mysql_key_to_innobase(dtuple_t, byte, ulint, dict_index_t, const byte, ulint, trx_t*): Assertion `0' failed.

If I comment out the statement, it will crash similarly on the last UPDATE:

mysqltest: At line 39: query 'UPDATE v SET f2 = 1' failed with wrong errno 2013: 'Lost connection to MySQL server during query', instead of 1369...

…

2018-06-21 17:42:07 140219663877888 [Warning] InnoDB: Using a partial-field key prefix in search, index `f2` of table `test`.`t` /* Partition `p1` */. Last data field length 6 bytes, key ptr now exceeds key end by 5 bytes. Key value in the MySQL format:

 len 6; hex 00000000020f; asc       ;

In 10.1, both XtraDB and InnoDB fail to notice the first error:

10.1 c09a8b5b36edb494e2bcc93074c06e26cd9f2b92
mysqltest: At line 14: query 'UPDATE v SET a = NULL ORDER BY a, b' succeeded - should have failed with errno 1292...

After commenting out the --error, they crashe on the 3rd UPDATE (not the 2nd one), due to the same problem:

mysqltest: At line 39: query 'UPDATE v SET f2 = 1' failed with wrong errno 2013: 'Lost connection to MySQL server during query', instead of 1369...

Note: I was still able to repeat ~~MDEV-16240~~ on 10.3.

Marko Mäkelä added a comment - 2018-06-21 14:53 10.2 d79bf0009a17f0020203003a97ce7e83449aeb3a CURRENT_TEST: innodb.mdev-11167 mysqltest: At line 25: query 'UPDATE v SET f2 = NULL' failed with wrong errno 2013: 'Lost connection to MySQL server during query', instead of 1369... … Version: '10.2.16-MariaDB-debug-log' socket: '/dev/shm/10.2/mysql-test/var/tmp/mysqld.1.sock' port: 16000 Source distribution 2018-06-21 17:39:12 140200100906752 [Warning] InnoDB: Using a partial-field key prefix in search, index `f2` of table `test`.`t` /* Partition `p1` */. Last data field length 6 bytes, key ptr now exceeds key end by 5 bytes. Key value in the MySQL format: len 6; hex 000000000209; asc ; mysqld: /mariadb/10.2/storage/innobase/row/row0sel.cc:2740: void row_sel_convert_mysql_key_to_innobase(dtuple_t*, byte*, ulint, dict_index_t*, const byte*, ulint, trx_t*): Assertion `0' failed. If I comment out the statement, it will crash similarly on the last UPDATE : mysqltest: At line 39: query 'UPDATE v SET f2 = 1' failed with wrong errno 2013: 'Lost connection to MySQL server during query', instead of 1369... … 2018-06-21 17:42:07 140219663877888 [Warning] InnoDB: Using a partial-field key prefix in search, index `f2` of table `test`.`t` /* Partition `p1` */. Last data field length 6 bytes, key ptr now exceeds key end by 5 bytes. Key value in the MySQL format: len 6; hex 00000000020f; asc ; In 10.1, both XtraDB and InnoDB fail to notice the first error: 10.1 c09a8b5b36edb494e2bcc93074c06e26cd9f2b92 mysqltest: At line 14: query 'UPDATE v SET a = NULL ORDER BY a, b' succeeded - should have failed with errno 1292... After commenting out the --error , they crashe on the 3rd UPDATE (not the 2nd one), due to the same problem: mysqltest: At line 39: query 'UPDATE v SET f2 = 1' failed with wrong errno 2013: 'Lost connection to MySQL server during query', instead of 1369... Note: I was still able to repeat MDEV-16240 on 10.3.

Marko Mäkelä added a comment - 2018-06-21 14:58

I believe that InnoDB is merely catching the error; the problem should be somewhere in updatable views.

Marko Mäkelä added a comment - 2018-06-21 14:58 I believe that InnoDB is merely catching the error; the problem should be somewhere in updatable views.

Oleksandr Byelkin added a comment - 2018-07-11 13:49

There was all rnd_init for this table, still it is not clear what innodb think is not right.

Oleksandr Byelkin added a comment - 2018-07-11 13:49 There was all rnd_init for this table, still it is not clear what innodb think is not right.

Oleksandr Byelkin added a comment - 2018-07-12 09:07

with no view it works, so will try to find difference in the execution...

Oleksandr Byelkin added a comment - 2018-07-12 09:07 with no view it works, so will try to find difference in the execution...

Oleksandr Byelkin added a comment - 2018-07-16 08:16

Biggest complication is that WITH CHECK OPTION makes sql level also set the same table but with other opened TABLE object in different position. I can not understand why innodb can not do it.

Oleksandr Byelkin added a comment - 2018-07-16 08:16 Biggest complication is that WITH CHECK OPTION makes sql level also set the same table but with other opened TABLE object in different position. I can not understand why innodb can not do it.

Oleksandr Byelkin added a comment - 2018-07-16 13:38

I do not see why rnd_init and than rnd_pos is not enough for Innodb to perporme operation AFAIK it correspond to protocol with Engine, if no I need description what is wrong in rnd_init/rnd_pos sequence.

Oleksandr Byelkin added a comment - 2018-07-16 13:38 I do not see why rnd_init and than rnd_pos is not enough for Innodb to perporme operation AFAIK it correspond to protocol with Engine, if no I need description what is wrong in rnd_init/rnd_pos sequence.

Marko Mäkelä added a comment - 2018-08-29 13:39

I think that I found the problem:

break ha_innobase::change_active_index

command 1

continue

end

run

up 5

p/x *key_ptr@key_len

I will see the following:

#5  0x0000555555f693f6 in ha_innobase::index_read (this=0x7fff9c076910,

    buf=0x7fff9c01b590 <incomplete sequence \373>, key_ptr=0x7fff9c08526b "",

    key_len=6, find_flag=HA_READ_KEY_EXACT)

    at /mariadb/10.2/storage/innobase/handler/ha_innodb.cc:9407

$1 = {0x0, 0x0, 0x0, 0x0, 0x2, 0xf}

This looks like a perfectly plausible DB_ROW_ID (hidden 48-bit auto-increment from a global sequence) for the GEN_CLUST_INDEX. Alas, the chosen index is wrong, so the m_prebuilt->search_tuple is the secondary index f2 (keynr=0) instead of the hidden index keynr=64. Searching back the log for {{this=0x7fff9c076910,}, the latest call is this:

Thread 28 "mysqld" hit Breakpoint 1, ha_innobase::change_active_index (

    this=0x7fff9c076910, keynr=0)

    at /mariadb/10.2/storage/innobase/handler/ha_innodb.cc:9577

InnoDB is assuming that the tuple is for the index that it was asked to use:

(gdb) p *m_prebuilt->search_tuple->fields

$2 = {data = 0x7fff9c07a178, ext = 0, spatial_status = 0, len = 4, type = {

    prtype = 1027, mtype = 6, len = 4, mbminlen = 0, mbmaxlen = 0}}

(gdb) p *m_prebuilt->index->fields@m_prebuilt->index->n_fields

$3 = {{col = 0x7fff9c0361f4, name = {m_name = 0x7fff9c03628b "f2"},

    prefix_len = 0, fixed_len = 4}, {col = 0x7fff9c03620c, name = {

      m_name = 0x7fff9c036291 "DB_ROW_ID"}, prefix_len = 0, fixed_len = 6}}

(gdb) p m_prebuilt->index->name

$4 = {m_name = 0x7f6acc071258 "f2"}

Note: mtype=DATA_INT=6, not DATA_SYS=8 like it should be for the GEN_CLUST_INDEX aka m_prebuilt->table->indexes.start.

sanja, please add the missing call change_active_index(MAX_KEY). (And be sure to repeat the test on a table that does have a PRIMARY KEY or a NOT NULL UNIQUE key.)

Marko Mäkelä added a comment - 2018-08-29 13:39 I think that I found the problem: break ha_innobase::change_active_index command 1 continue end run up 5 p/x *key_ptr@key_len I will see the following: #5 0x0000555555f693f6 in ha_innobase::index_read (this=0x7fff9c076910, buf=0x7fff9c01b590 <incomplete sequence \373>, key_ptr=0x7fff9c08526b "", key_len=6, find_flag=HA_READ_KEY_EXACT) at /mariadb/10.2/storage/innobase/handler/ha_innodb.cc:9407 $1 = {0x0, 0x0, 0x0, 0x0, 0x2, 0xf} This looks like a perfectly plausible DB_ROW_ID (hidden 48-bit auto-increment from a global sequence) for the GEN_CLUST_INDEX . Alas, the chosen index is wrong, so the m_prebuilt->search_tuple is the secondary index f2 ( keynr=0 ) instead of the hidden index keynr=64 . Searching back the log for {{this=0x7fff9c076910,}, the latest call is this: Thread 28 "mysqld" hit Breakpoint 1, ha_innobase::change_active_index ( this=0x7fff9c076910, keynr=0) at /mariadb/10.2/storage/innobase/handler/ha_innodb.cc:9577 InnoDB is assuming that the tuple is for the index that it was asked to use: (gdb) p *m_prebuilt->search_tuple->fields $2 = {data = 0x7fff9c07a178, ext = 0, spatial_status = 0, len = 4, type = { prtype = 1027, mtype = 6, len = 4, mbminlen = 0, mbmaxlen = 0}} (gdb) p *m_prebuilt->index->fields@m_prebuilt->index->n_fields $3 = {{col = 0x7fff9c0361f4, name = {m_name = 0x7fff9c03628b "f2"}, prefix_len = 0, fixed_len = 4}, {col = 0x7fff9c03620c, name = { m_name = 0x7fff9c036291 "DB_ROW_ID"}, prefix_len = 0, fixed_len = 6}} (gdb) p m_prebuilt->index->name $4 = {m_name = 0x7f6acc071258 "f2"} Note: mtype=DATA_INT=6 , not DATA_SYS=8 like it should be for the GEN_CLUST_INDEX aka m_prebuilt->table->indexes.start . sanja , please add the missing call change_active_index(MAX_KEY) . (And be sure to repeat the test on a table that does have a PRIMARY KEY or a NOT NULL UNIQUE key.)

Marko Mäkelä added a comment - 2018-08-30 05:10

Here is the stack trace of the latest (offending) call to ha_innobase::change_active_index(keynr=0) for this handle, from a different invocation (different this pointer of ha_innobase):

Thread 28 "mysqld" hit Breakpoint 1, ha_innobase::change_active_index (this=0x7fffa4076600, keynr=0)

    at /mariadb/10.2/storage/innobase/handler/ha_innodb.cc:9577

#0  ha_innobase::change_active_index (this=0x7fffa4076600, keynr=0)

    at /mariadb/10.2/storage/innobase/handler/ha_innodb.cc:9577

#1  0x0000555555f69014 in ha_innobase::index_init (this=0x7fffa4076600, keynr=0, sorted=true)

    at /mariadb/10.2/storage/innobase/handler/ha_innodb.cc:9231

#2  0x0000555555a808dc in handler::ha_index_init (this=0x7fffa4076600, idx=0, sorted=true)

    at /mariadb/10.2/sql/handler.h:2804

#3  0x0000555556414f1f in ha_partition::index_init (this=0x7fffa40757a0, inx=0, sorted=true)

    at /mariadb/10.2/sql/ha_partition.cc:5278

#4  0x0000555555a808dc in handler::ha_index_init (this=0x7fffa40757a0, idx=0, sorted=true)

    at /mariadb/10.2/sql/handler.h:2804

#5  0x0000555555eb785a in QUICK_RANGE_SELECT::reset (this=0x7fffa40268c0)

    at /mariadb/10.2/sql/opt_range.cc:11348

#6  0x0000555555b68a11 in join_init_read_record (tab=0x7fffa4081a28)

    at /mariadb/10.2/sql/sql_select.cc:19649

#7  0x0000555555b6870b in join_init_quick_read_record (tab=0x7fffa4081a28)

    at /mariadb/10.2/sql/sql_select.cc:19596

#8  0x0000555555b66910 in sub_select (join=0x7fffa4016e00, join_tab=0x7fffa4081a28,

    end_of_records=false) at /mariadb/10.2/sql/sql_select.cc:18739

#9  0x0000555555b67086 in evaluate_join_record (join=0x7fffa4016e00, join_tab=0x7fffa4081678,

    error=0) at /mariadb/10.2/sql/sql_select.cc:18962

#10 0x0000555555b66b25 in sub_select (join=0x7fffa4016e00, join_tab=0x7fffa4081678,

    end_of_records=false) at /mariadb/10.2/sql/sql_select.cc:18781

#11 0x0000555555b65ef9 in do_select (join=0x7fffa4016e00, procedure=0x0)

    at /mariadb/10.2/sql/sql_select.cc:18286

#12 0x0000555555b40b50 in JOIN::exec_inner (this=0x7fffa4016e00)

    at /mariadb/10.2/sql/sql_select.cc:3609

#13 0x0000555555b3fffc in JOIN::exec (this=0x7fffa4016e00) at /mariadb/10.2/sql/sql_select.cc:3404

#14 0x0000555555b411c2 in mysql_select (thd=0x7fffa4000cf8, tables=0x7fffa4011818, wild_num=0,

    fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0,

    select_options=1342177408, result=0x7fffa4016d38, unit=0x7fffa4004750, select_lex=0x7fffa4004e88)

    at /mariadb/10.2/sql/sql_select.cc:3804

#15 0x0000555555be43d0 in mysql_multi_update (thd=0x7fffa4000cf8, table_list=0x7fffa4011818,

    fields=0x7fffa4004fb0, values=0x7fffa4005480, conds=0x0, options=0, handle_duplicates=DUP_ERROR,

    ignore=false, unit=0x7fffa4004750, select_lex=0x7fffa4004e88, result=0x7ffff4f9c8b0)

    at /mariadb/10.2/sql/sql_update.cc:1597

#16 0x0000555555afa3de in mysql_execute_command (thd=0x7fffa4000cf8)

    at /mariadb/10.2/sql/sql_parse.cc:4348

#17 0x0000555555b05d2b in mysql_parse (thd=0x7fffa4000cf8,

    rawbuf=0x7fffa4011740 "UPDATE v SET f2 = 1", length=19, parser_state=0x7ffff4f9d1b0,

    is_com_multi=false, is_next_command=false) at /mariadb/10.2/sql/sql_parse.cc:8009

All 4 calls to ha_innobase::change_active_index() for this object are with keynr=0, with a similar stack trace. I was using the following test case against 10.2:

--source include/have_innodb.inc

--source include/have_partition.inc

CREATE TABLE t1 (a INT) ENGINE=InnoDB;

CREATE TABLE t2 (b INT, c INT, KEY(b)) ENGINE=InnoDB PARTITION BY HASH(c) PARTITIONS 2;

CREATE ALGORITHM = MERGE VIEW v AS SELECT a, b FROM t1 STRAIGHT_JOIN t2 WHERE b = 'foo' WITH CHECK OPTION;

INSERT INTO t1 VALUES (1),(2);

INSERT IGNORE INTO t2 VALUES (2,2),('three',3),(4,4);

--error ER_TRUNCATED_WRONG_VALUE

UPDATE v SET a = NULL ORDER BY a, b;

DROP VIEW v;

DROP TABLE t1, t2;

SET GLOBAL innodb_stats_persistent= ON;

CREATE TABLE t (f1 INT, f2 INT, KEY(f2)) ENGINE=InnoDB PARTITION BY HASH (f1) PARTITIONS 2;

INSERT IGNORE INTO t VALUES (NULL,0),(NULL,0),(0,21),(4,0),(1,8),(5,66);

CREATE ALGORITHM=MERGE VIEW v AS SELECT t1.* FROM t t1 JOIN t t2 WHERE t1.f1 < t2.f2 WITH LOCAL CHECK OPTION;

#--error ER_VIEW_CHECK_FAILED

#UPDATE v SET f2 = NULL;

DROP VIEW v;

DROP TABLE t;

CREATE TABLE t (f1 INT, f2 INT, f3 INT, KEY(f2))

ENGINE=InnoDB

PARTITION BY RANGE (f1) (

  PARTITION p0 VALUES LESS THAN (1),

  PARTITION p1 VALUES LESS THAN (128),

  PARTITION p2 VALUES LESS THAN MAXVALUE

);

INSERT INTO t VALUES (NULL,0,24),(NULL,0,0),(0,21,0),(4,0,NULL),(1,8,2),(5,66,0);

CREATE ALGORITHM=MERGE VIEW v AS SELECT t1.* FROM t t1 JOIN t t2 WHERE t1.f1 < t2.f2 WITH LOCAL CHECK OPTION;

--error ER_VIEW_CHECK_FAILED

UPDATE v SET f2 = 1;

DROP VIEW v;

DROP TABLE t;

It crashes on the last UPDATE statement, instead of returning an error.

Marko Mäkelä added a comment - 2018-08-30 05:10 Here is the stack trace of the latest (offending) call to ha_innobase::change_active_index(keynr=0) for this handle, from a different invocation (different this pointer of ha_innobase ): Thread 28 "mysqld" hit Breakpoint 1, ha_innobase::change_active_index (this=0x7fffa4076600, keynr=0) at /mariadb/10.2/storage/innobase/handler/ha_innodb.cc:9577 #0 ha_innobase::change_active_index (this=0x7fffa4076600, keynr=0) at /mariadb/10.2/storage/innobase/handler/ha_innodb.cc:9577 #1 0x0000555555f69014 in ha_innobase::index_init (this=0x7fffa4076600, keynr=0, sorted=true) at /mariadb/10.2/storage/innobase/handler/ha_innodb.cc:9231 #2 0x0000555555a808dc in handler::ha_index_init (this=0x7fffa4076600, idx=0, sorted=true) at /mariadb/10.2/sql/handler.h:2804 #3 0x0000555556414f1f in ha_partition::index_init (this=0x7fffa40757a0, inx=0, sorted=true) at /mariadb/10.2/sql/ha_partition.cc:5278 #4 0x0000555555a808dc in handler::ha_index_init (this=0x7fffa40757a0, idx=0, sorted=true) at /mariadb/10.2/sql/handler.h:2804 #5 0x0000555555eb785a in QUICK_RANGE_SELECT::reset (this=0x7fffa40268c0) at /mariadb/10.2/sql/opt_range.cc:11348 #6 0x0000555555b68a11 in join_init_read_record (tab=0x7fffa4081a28) at /mariadb/10.2/sql/sql_select.cc:19649 #7 0x0000555555b6870b in join_init_quick_read_record (tab=0x7fffa4081a28) at /mariadb/10.2/sql/sql_select.cc:19596 #8 0x0000555555b66910 in sub_select (join=0x7fffa4016e00, join_tab=0x7fffa4081a28, end_of_records=false) at /mariadb/10.2/sql/sql_select.cc:18739 #9 0x0000555555b67086 in evaluate_join_record (join=0x7fffa4016e00, join_tab=0x7fffa4081678, error=0) at /mariadb/10.2/sql/sql_select.cc:18962 #10 0x0000555555b66b25 in sub_select (join=0x7fffa4016e00, join_tab=0x7fffa4081678, end_of_records=false) at /mariadb/10.2/sql/sql_select.cc:18781 #11 0x0000555555b65ef9 in do_select (join=0x7fffa4016e00, procedure=0x0) at /mariadb/10.2/sql/sql_select.cc:18286 #12 0x0000555555b40b50 in JOIN::exec_inner (this=0x7fffa4016e00) at /mariadb/10.2/sql/sql_select.cc:3609 #13 0x0000555555b3fffc in JOIN::exec (this=0x7fffa4016e00) at /mariadb/10.2/sql/sql_select.cc:3404 #14 0x0000555555b411c2 in mysql_select (thd=0x7fffa4000cf8, tables=0x7fffa4011818, wild_num=0, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=1342177408, result=0x7fffa4016d38, unit=0x7fffa4004750, select_lex=0x7fffa4004e88) at /mariadb/10.2/sql/sql_select.cc:3804 #15 0x0000555555be43d0 in mysql_multi_update (thd=0x7fffa4000cf8, table_list=0x7fffa4011818, fields=0x7fffa4004fb0, values=0x7fffa4005480, conds=0x0, options=0, handle_duplicates=DUP_ERROR, ignore=false, unit=0x7fffa4004750, select_lex=0x7fffa4004e88, result=0x7ffff4f9c8b0) at /mariadb/10.2/sql/sql_update.cc:1597 #16 0x0000555555afa3de in mysql_execute_command (thd=0x7fffa4000cf8) at /mariadb/10.2/sql/sql_parse.cc:4348 #17 0x0000555555b05d2b in mysql_parse (thd=0x7fffa4000cf8, rawbuf=0x7fffa4011740 "UPDATE v SET f2 = 1", length=19, parser_state=0x7ffff4f9d1b0, is_com_multi=false, is_next_command=false) at /mariadb/10.2/sql/sql_parse.cc:8009 All 4 calls to ha_innobase::change_active_index() for this object are with keynr=0 , with a similar stack trace. I was using the following test case against 10.2: --source include/have_innodb.inc --source include/have_partition.inc CREATE TABLE t1 (a INT ) ENGINE=InnoDB; CREATE TABLE t2 (b INT , c INT , KEY (b)) ENGINE=InnoDB PARTITION BY HASH(c) PARTITIONS 2; CREATE ALGORITHM = MERGE VIEW v AS SELECT a, b FROM t1 STRAIGHT_JOIN t2 WHERE b = 'foo' WITH CHECK OPTION ; INSERT INTO t1 VALUES (1),(2); INSERT IGNORE INTO t2 VALUES (2,2),( 'three' ,3),(4,4); --error ER_TRUNCATED_WRONG_VALUE UPDATE v SET a = NULL ORDER BY a, b; DROP VIEW v; DROP TABLE t1, t2; SET GLOBAL innodb_stats_persistent= ON ; CREATE TABLE t (f1 INT , f2 INT , KEY (f2)) ENGINE=InnoDB PARTITION BY HASH (f1) PARTITIONS 2; INSERT IGNORE INTO t VALUES ( NULL ,0),( NULL ,0),(0,21),(4,0),(1,8),(5,66); CREATE ALGORITHM=MERGE VIEW v AS SELECT t1.* FROM t t1 JOIN t t2 WHERE t1.f1 < t2.f2 WITH LOCAL CHECK OPTION ; # --error ER_VIEW_CHECK_FAILED # UPDATE v SET f2 = NULL ; DROP VIEW v; DROP TABLE t; CREATE TABLE t (f1 INT , f2 INT , f3 INT , KEY (f2)) ENGINE=InnoDB PARTITION BY RANGE (f1) ( PARTITION p0 VALUES LESS THAN (1), PARTITION p1 VALUES LESS THAN (128), PARTITION p2 VALUES LESS THAN MAXVALUE ); INSERT INTO t VALUES ( NULL ,0,24),( NULL ,0,0),(0,21,0),(4,0, NULL ),(1,8,2),(5,66,0); CREATE ALGORITHM=MERGE VIEW v AS SELECT t1.* FROM t t1 JOIN t t2 WHERE t1.f1 < t2.f2 WITH LOCAL CHECK OPTION ; --error ER_VIEW_CHECK_FAILED UPDATE v SET f2 = 1; DROP VIEW v; DROP TABLE t; It crashes on the last UPDATE statement, instead of returning an error.

Oleksandr Byelkin added a comment - 2018-09-03 15:20

somehow was not able to crash last 10.0:
TODO: check all modern versions.

Oleksandr Byelkin added a comment - 2018-09-03 15:20 somehow was not able to crash last 10.0: TODO: check all modern versions.

Elena Stepanova added a comment - 2018-10-13 15:25 - edited

sanja: regarding 10.0 – still crashes for me, but it's non-deterministic, so you might need to run with --repeat=N. You'll need to add --error 1369 before UPDATE to allow it run until it crashes. For example, now when I ran them on bd21904357d95631fbbb15defe4b023dce6a24a2 the 2nd test case failed with error 1369 on the first repetition and crashed on the next one.

Elena Stepanova added a comment - 2018-10-13 15:25 - edited sanja : regarding 10.0 – still crashes for me, but it's non-deterministic, so you might need to run with --repeat=N . You'll need to add --error 1369 before UPDATE to allow it run until it crashes. For example, now when I ran them on bd21904357d95631fbbb15defe4b023dce6a24a2 the 2nd test case failed with error 1369 on the first repetition and crashed on the next one.

Oleksandr Byelkin added a comment - 2018-11-02 16:55

revision-id: e9a7c9e28c8f28447a29c59585d08f88ce0772db (mariadb-10.1.35-40-ge9a7c9e28c8)
parent(s): 62dbf4f18d47a1e26f5fd7a0d6683494fb203a44
author: Oleksandr Byelkin
committer: Oleksandr Byelkin
timestamp: 2018-11-02 17:54:35 +0100
message:

~~MDEV-11167~~: InnoDB: Warning: using a partial-field key prefix in search, results in assertion failure or "Can't find record" error

Fix ha_rnd_init() argument (we do not doing scan but use rnd_pos)

Oleksandr Byelkin added a comment - 2018-11-02 16:55 revision-id: e9a7c9e28c8f28447a29c59585d08f88ce0772db (mariadb-10.1.35-40-ge9a7c9e28c8) parent(s): 62dbf4f18d47a1e26f5fd7a0d6683494fb203a44 author: Oleksandr Byelkin committer: Oleksandr Byelkin timestamp: 2018-11-02 17:54:35 +0100 message: MDEV-11167 : InnoDB: Warning: using a partial-field key prefix in search, results in assertion failure or "Can't find record" error Fix ha_rnd_init() argument (we do not doing scan but use rnd_pos)

Sergei Golubchik added a comment - 2018-11-02 17:19

ok to push

Sergei Golubchik added a comment - 2018-11-02 17:19 ok to push

People

Assignee:: Oleksandr Byelkin

Reporter:: Elena Stepanova

Votes:: 0 Vote for this issue

Watchers:: 4 Start watching this issue

Dates

Created:: 2016-10-28 15:22

Updated:: 2024-07-08 01:11

Resolved:: 2018-11-07 08:40

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server