|
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().
|
|
For what it is worth, there was a SQL layer change related to this in MySQL 5.6.8 in 2012.
|
|
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;
|
|
|
|
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.
|
|
I believe that InnoDB is merely catching the error; the problem should be somewhere in updatable views.
|
|
There was all rnd_init for this table, still it is not clear what innodb think is not right.
|
|
with no view it works, so will try to find difference in the execution...
|
|
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.
|
|
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.
|
|
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.)
|
|
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.
|
|
somehow was not able to crash last 10.0:
TODO: check all modern versions.
|
|
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.
|
|
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)
|
|
ok to push
|