| Bug #85796 | table_share->tmp_table != NO_TMP_TABLE || m_lock_type == 1' at handler::ha_updat | ||
|---|---|---|---|
| Submitted: | 5 Apr 2017 5:36 | Modified: | 3 Oct 2017 18:39 |
| Reporter: | Shipra Jain | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: DDL | Severity: | S3 (Non-critical) |
| Version: | 8.0.2 (mysql-trunk-meta-sync) | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[5 Apr 2017 8:18]
Dmitry Lenev
Posted by developer:
The assertion failure seems to be caused by the fact that we
incorrectly set lock type for the base table which we try to
update. In fact we don't correctly set the lock type for
view which uses this table either:
(gdb) f 8
#8 0x0000000001eb8a1c in handler::ha_update_row (this=0x7fe64421d830, old_data=0x7fe64421f130 "\347\377\200", new_data=0x7fe64421eef0 "\347\377\200")
at /log/RQG/shipjain/mysql-meta-sync-vigdis28/sql/handler.cc:8354
8354 DBUG_ASSERT(table_share->tmp_table != NO_TMP_TABLE ||
(gdb) p m_lock_type
$24 = 0
(gdb) p table
$25 = (TABLE *) 0x7fe64425b750
(gdb) p table.reginfo.lock_type
$26 = TL_READ
(gdb) p table->pos_in_table_list
$27 = (TABLE_LIST *) 0x7fe644288d40
(gdb) p table->pos_in_table_list->m_lock_descriptor
$28 = {type = TL_READ_DEFAULT, action = THR_DEFAULT}
(gdb) p table->pos_in_table_list->table_name
$29 = 0x7fe6442202e0 "t1_base_N3"
(gdb) p table->pos_in_table_list->referencing_view->table_name
$30 = 0x7fe64421bf58 "t1_view_N2"
(gdb) p table->pos_in_table_list->referencing_view->m_lock_descriptor
$31 = {type = TL_READ_DEFAULT, action = THR_DEFAULT}
(gdb) p table->pos_in_table_list->referencing_view->referencing_view->table_name
$32 = 0x7fe6441457a0 "t1_view_S3"
(gdb) p table->pos_in_table_list->referencing_view->referencing_view->m_lock_descriptor
$33 = {type = TL_WRITE_DEFAULT, action = THR_WAIT}
[5 Apr 2017 9:23]
Dmitry Lenev
Posted by developer:
The problem is also repeatable in mysql-trunk.
Here is the simplified test case for it:
CREATE TABLE t1_base_N3
(`col_varchar_64_key` varchar(64),
`col_float_key` float,
`col_varchar_64` varchar(64),
`col_int_key` int,
`col_decimal` decimal,
`col_int` int,
pk int,
`col_blob` blob,
`col_decimal_key` decimal,
`col_float` float,
`col_blob_key` blob,
key (`col_varchar_64_key` ),
key (`col_float_key` ),
key (`col_int_key` ),
primary key (pk),
key (`col_decimal_key` ),
key (`col_blob_key` (255)));
insert into t1_base_N3(pk) values(3),(4);
CREATE TABLE t1_part_N6
(pk INTEGER PRIMARY KEY);
insert into t1_part_N6(pk) values(3),(4);
create view t1_view_N2 AS
select `t1_base_N3`.`col_decimal_key` AS `col_decimal_key`,
`t1_base_N3`.`col_float` AS `col_float`,
`t1_base_N3`.`col_varchar_64_key` AS `col_varchar_64_key`,
`t1_base_N3`.`col_blob` AS `col_blob`,
`t1_base_N3`.`col_decimal` AS `col_decimal`,
`t1_base_N3`.`col_int` AS `col_int`,
`t1_base_N3`.`col_float_key` AS `col_float_key`,
`t1_base_N3`.`col_int_key` AS `col_int_key`,
`t1_base_N3`.`col_blob_key` AS `col_blob_key`,
`t1_base_N3`.`col_varchar_64` AS `col_varchar_64`,
`t1_base_N3`.`pk` AS `pk`
from t1_base_N3 where `t1_base_N3`.`pk` between 3 and (3 + 1);
create view t1_view_S3 AS
select `A`.`col_decimal` AS `col_decimal`,
`A`.`pk` AS `pk`,
`A`.`col_blob` AS `col_blob`
from (select `t1_view_N2`.`col_decimal` AS `col_decimal`,
`t1_view_N2`.`pk` AS `pk`,
`t1_view_N2`.`col_blob` AS `col_blob`
from `t1_view_N2`) AS `A`
where `A`.`pk` between 3 and (3 + 1);
UPDATE t1_view_S3 AS A NATURAL JOIN t1_part_N6 B
SET A.col_decimal = 2, B.pk = 0;
DROP VIEW t1_view_S3, t1_view_N2;
DROP TABLE t1_part_N6, t1_base_N3;
[3 Oct 2017 18:39]
Paul DuBois
Posted by developer: Fixed in 8.0.4. An assertion could be raised for updates when a view or derived table considered read only had nested references not seen as read only.
Description: Assert in table_share->tmp_table != NO_TMP_TABLE || m_lock_type == 1' at handler::ha_update_row in sql/handler.cc Failing query: UPDATE testdb_S . t1_view_S3 AS A NATURAL JOIN testdb_N5 . t1_part_N6 B SET A. `col_decimal` = 2 , B. `pk` = 0 Seen on meta-sync branch with latest commit as commit 8f46ba8ba7f6bf4013b7b54076c5483d99e35654 Date: Fri Mar 31 23:22:18 2017 +0800 (gdb) bt #0 0x00007fe70c33e771 in pthread_kill () from /lib64/libpthread.so.0 #1 0x0000000002672ae3 in my_write_core (sig=6) at /log/RQG/shipjain/mysql-meta-sync-vigdis28/mysys/stacktrace.cc:291 #2 0x0000000001d7acc5 in handle_fatal_signal (sig=6) at /log/RQG/shipjain/mysql-meta-sync-vigdis28/sql/signal_handler.cc:231 #3 <signal handler called> #4 0x00007fe70ad365d7 in raise () from /lib64/libc.so.6 #5 0x00007fe70ad37cc8 in abort () from /lib64/libc.so.6 #6 0x00007fe70ad2f546 in __assert_fail_base () from /lib64/libc.so.6 #7 0x00007fe70ad2f5f2 in __assert_fail () from /lib64/libc.so.6 #8 0x0000000001eb8a1c in handler::ha_update_row (this=0x7fe64421d830, old_data=0x7fe64421f130 "\347\377\200", new_data=0x7fe64421eef0 "\347\377\200") at /log/RQG/shipjain/mysql-meta-sync-vigdis28/sql/handler.cc:8354 #9 0x00000000020a6bc1 in Query_result_update::do_updates (this=0x7fe644288740) at /log/RQG/shipjain/mysql-meta-sync-vigdis28/sql/sql_update.cc:2502 #10 0x00000000020a6f6d in Query_result_update::send_eof (this=0x7fe644288740) at /log/RQG/shipjain/mysql-meta-sync-vigdis28/sql/sql_update.cc:2584 #11 0x0000000001a409db in do_select (join=0x7fe644289fe8) at /log/RQG/shipjain/mysql-meta-sync-vigdis28/sql/sql_executor.cc:1209 #12 0x0000000001a3e097 in JOIN::exec (this=0x7fe644289fe8) at /log/RQG/shipjain/mysql-meta-sync-vigdis28/sql/sql_executor.cc:258 #13 0x0000000001ade226 in Sql_cmd_dml::execute_inner (this=0x7fe644146b78, thd=0x7fe6440db260) at /log/RQG/shipjain/mysql-meta-sync-vigdis28/sql/sql_select.cc:736 #14 0x00000000020a3a4b in Sql_cmd_update::execute_inner (this=0x7fe644146b78, thd=0x7fe6440db260) at /log/RQG/shipjain/mysql-meta-sync-vigdis28/sql/sql_update.cc:1558 #15 0x0000000001addd05 in Sql_cmd_dml::execute (this=0x7fe644146b78, thd=0x7fe6440db260) at /log/RQG/shipjain/mysql-meta-sync-vigdis28/sql/sql_select.cc:619 #16 0x0000000001a8bed5 in mysql_execute_command (thd=0x7fe6440db260, first_level=true) at /log/RQG/shipjain/mysql-meta-sync-vigdis28/sql/sql_parse.cc:3349 #17 0x0000000001a9143f in mysql_parse (thd=0x7fe6440db260, parser_state=0x7fe6ec1305c0) at /log/RQG/shipjain/mysql-meta-sync-vigdis28/sql/sql_parse.cc:5336 #18 0x0000000001a87b14 in dispatch_command (thd=0x7fe6440db260, com_data=0x7fe6ec130dc0, command=COM_QUERY) at /log/RQG/shipjain/mysql-meta-sync-vigdis28/sql/sql_parse.cc:1593 #19 0x0000000001a8699b in do_command (thd=0x7fe6440db260) at /log/RQG/shipjain/mysql-meta-sync-vigdis28/sql/sql_parse.cc:1180 #20 0x0000000001d6cd7a in handle_connection (arg=0x717fbc0) at /log/RQG/shipjain/mysql-meta-sync-vigdis28/sql/conn_handler/connection_handler_per_thread.cc:322 #21 0x00000000026a6d89 in pfs_spawn_thread (arg=0x700dfa0) at /log/RQG/shipjain/mysql-meta-sync-vigdis28/storage/perfschema/pfs.cc:2407 #22 0x00007fe70c339df5 in start_thread () from /lib64/libpthread.so.0 #23 0x00007fe70adf760d in clone () from /lib64/libc.so.6 How to repeat: gdb /log/RQG/shipjain/rqg/storagetrunk/1491305678/83log/log/RQG/shipjain/mysql-meta-sync-vigdis28/sql/mysqld /log/RQG/shipjain/rqg/storagetrunk/1491305678/83log/dev/shm/vardir/1491305678/4/1/data/core.19880 on vigdis28 using RMR for concurency_2.yy grammar