| Bug #119805 | create_tmp_field assert fail | ||
|---|---|---|---|
| Submitted: | 29 Jan 3:21 | Modified: | 14 Jul 14:48 |
| Reporter: | Chunling Qin | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S2 (Serious) |
| Version: | 8.0.45 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[29 Jan 4:15]
Chaithra Marsur Gopala Reddy
Hi Chunling Qin, Thank you for the test case. Verified as described.
[21 Jun 14:48]
MySQL Admin
Posted by developer: Bug status updated to 'Patch pending'
[24 Jun 8:48]
MySQL Admin
Posted by developer: Bug status updated to 'Patch approved'
[26 Jun 14:48]
MySQL Admin
Posted by developer: Bug status updated to 'Documenting'
[14 Jul 14:48]
Edward Gilmore
Posted by developer: Added the following note to the MySQL Server 26.7.0 release notes: An INSERT ... SELECT through a view with ON DUPLICATE KEY UPDATE could cause an assertion failure.
[7 Aug 14:35]
Jean-François Gagné
Note that this only happens on debug build.
sql="CREATE TABLE mysql_1 (
col_int int,
col_varchar varchar(2000),
col_date date,
col_timestamp timestamp,
col_numeric numeric,
KEY k2 (col_int,col_date),
KEY k1 (col_numeric));"
sql="$sql INSERT IGNORE INTO mysql_1 (col_int,col_varchar,col_date,col_timestamp,col_numeric) VALUES
(NULL, 'z', '1999-11-18 07:25:13.005498', '2004-02-23 23:19:07.050275', 2),
(1, 'kxceks', '2030-06-08', '2015-06-25', 4),
(1, 'xc', '1988-06-20 00:45:10.036952', '2000-02-02 19:37:56.035563', b'0'),
(1, NULL, '2034-01-16 08:16:51.047742', '1973-08-03', NULL),
(1, 'ceksa', '2004-06-02 09:41:11.001950', '1992-11-10', 0),
(3, 'r', '1996-03-09', '2003-04-08', b'0'),
(NULL, 'english', '2030-09-25', '2008-05-16', b'1'),
(9, 'g', '2010-10-05', '2016-12-22', b'0'),
(1, 'z', '1982-05-08 05:43:11.061183', '2022-11-12', 7),
(8, 'x', '2006-04-08 18:19:40.062049', '1987-10-12', b'0');"
sql="$sql CREATE VIEW view1chqin AS SELECT * FROM mysql_1 WHERE col_int > 0;"
sql="$sql INSERT INTO view1chqin (col_int, col_varchar)
SELECT col_int, col_varchar
FROM view1chqin
WHERE col_int > 100
LIMIT 10
ON DUPLICATE KEY UPDATE col_varchar = VALUES(col_varchar);"
function run_test() {
dbdeployer deploy single $v | pv -tN dbdepl. > /dev/null
cd ~/sandboxes/msb_${v//./_}
./use -N <<< "SELECT VERSION(); CREATE DATABASE test_jfg1"
{ ./use test_jfg1 <<< "$sql" 2>&1 && echo OK || echo KO; } | pv -tN use
./stop | pv -tN stop > /dev/null
cp ~/opt/mysql/$v/bin/mysqld{,-org}
cp ~/opt/mysql/$v/bin/mysqld{-debug,}
./start | pv -tN start > /dev/null
./use -N <<< "SELECT VERSION(); CREATE DATABASE test_jfg2"
{ ./use test_jfg2 <<< "$sql" 2>&1 && echo OK || echo KO; } | pv -tN use
./stop | pv -tN stop > /dev/null
cp ~/opt/mysql/$v/bin/mysqld{-org,}
cd; rm -rf ~/sandboxes/msb_${v//./_}
}
v=mysql_8.0.46; run_test
dbdepl.: 0:00:08
8.0.46
OK
use: 0:00:00
stop: 0:00:03
start: 0:00:08
8.0.46-debug
ERROR 2013 (HY000) at line 1: Lost connection to MySQL server during query
KO
use: 0:00:16
stop: 0:00:00
[7 Aug 14:35]
Jean-François Gagné
> Version: 8.0.45
> Added the following note to the MySQL Server 26.7.0 release notes: [...]
This indeed looks fixed in 26.7.0, but as shown below, this still affects 8.4.11 and 9.7.2 (and 8.0.46 as shown in previous comment, but because 8.0 is not supported anymore, it is expected that this is not fixed there).
Can we expect this to be fixed in all supported versions ?
for v in mysql_26.7.0 mysql_8.4.11 mysql_9.7.2; do echo; echo $v; run_test; done | tail -n +2
mysql_26.7.0
dbdepl.: 0:00:06
26.7.0
OK
use: 0:00:00
stop: 0:00:02
start: 0:00:08
26.7.0-debug
OK
use: 0:00:00
stop: 0:00:03
mysql_8.4.11
dbdepl.: 0:00:05
8.4.11
OK
use: 0:00:00
stop: 0:00:02
start: 0:00:08
8.4.11-debug
ERROR 2013 (HY000) at line 1: Lost connection to MySQL server during query
use: 0:00:17KO
stop: 0:00:00
mysql_9.7.2
dbdepl.: 0:00:05
9.7.2
OK
use: 0:00:00
stop: 0:00:02
start: 0:00:08
9.7.2-debug
ERROR 2013 (HY000) at line 1: Lost connection to MySQL server during query
KO
use: 0:00:19
stop: 0:00:00
Description: CREATE TABLE `mysql_1` ( `col_int` int, `col_varchar` varchar(2000), `col_date` date, `col_timestamp` timestamp , `col_numeric` numeric, /*Indices*/ KEY k2 (`col_int`,`col_date`), KEY k1 (`col_numeric`)) /* Indices QNO 4 CON_ID 9 */ ALTER TABLE `mysql_1` DISABLE KEYS /* QNO 5 CON_ID 9 */ INSERT IGNORE INTO mysql_1 (col_int,col_varchar,col_date,col_timestamp,col_numeric) VALUES (NULL, 'z', '1999-11-18 07:25:13.005498', '2004-02-23 23:19:07.050275', 2) , (1, 'kxceks', '2030-06-08', '2015-06-25', 4) , (1, 'xc', '1988-06-20 00:45:10.036952', '2000-02-02 19:37:56.035563', b'0') , (1, NULL, '2034-01-16 08:16:51.047742', '1973-08-03', NULL) , (1, 'ceksa', '2004-06-02 09:41:11.001950', '1992-11-10', 0) , (3, 'r', '1996-03-09', '2003-04-08', b'0') , (NULL, 'english', '2030-09-25', '2008-05-16', b'1') , (9, 'g', '2010-10-05', '2016-12-22', b'0') , (1, 'z', '1982-05-08 05:43:11.061183', '2022-11-12', 7) , (8, 'x', '2006-04-08 18:19:40.062049', '1987-10-12', b'0') /* QNO 6 CON_ID 9 */ ; COMMIT /* QNO 7 CON_ID 9 */ ; ALTER TABLE `mysql_1` ENABLE KEYS /* QNO 8 CON_ID 9 */ ; CREATE VIEW view1chqin AS SELECT * FROM mysql_1 WHERE col_int > 0; INSERT INTO view1chqin (col_int, col_varchar) SELECT col_int, col_varchar FROM view1chqin WHERE col_int > 100 LIMIT 10 ON DUPLICATE KEY UPDATE col_varchar = VALUES(col_varchar); After running the above sqls, mysql crash: #8 0x55a09e7fa196 _Z16create_tmp_fieldP3THDP5TABLEP4ItemNS3_4TypeEP14Mem_root_arrayI8Func_ptrEPP5FieldSC_bbbb at /data/mysql-server/sql/sql_tmp_table.cc:503 #9 0x55a09e7fc81b _Z16create_tmp_tableP3THDP16Temp_table_paramRK14mem_root_dequeIP4ItemEP5ORDERbbyyPKc at /data/mysql-server/sql/sql_tmp_table.cc:1143 #10 0x55a09e593cb2 _ZN4JOIN25create_intermediate_tableEP7QEP_TABRK14mem_root_dequeIP4ItemER14ORDER_with_srcb at /data/mysql-server/sql/sql_executor.cc:244 #11 0x55a09e72570c _ZN4JOIN20make_tmp_tables_infoEv at /data/mysql-server/sql/sql_select.cc:4450 #12 0x55a09e5f2a2c _ZN4JOIN8optimizeEb at /data/mysql-server/sql/sql_optimizer.cc:1022 #13 0x55a09e7188f4 _ZN11Query_block8optimizeEP3THDb at /data/mysql-server/sql/sql_select.cc:2001 #14 0x55a09e81922a _ZN16Query_expression8optimizeEP3THDP5TABLEbb at /data/mysql-server/sql/sql_union.cc:1019 #15 0x55a09e7154df _ZN11Sql_cmd_dml13execute_innerEP3THD at /data/mysql-server/sql/sql_select.cc:999 #16 0x55a09e714207 _ZN11Sql_cmd_dml7executeEP3THD at /data/mysql-server/sql/sql_select.cc:785 #17 0x55a09e64a8be _Z21mysql_execute_commandP3THDb at /data/mysql-server/sql/sql_parse.cc:3691 #18 0x55a09e6440ea _Z20dispatch_sql_commandP3THDP12Parser_state at /data/mysql-server/sql/sql_parse.cc:5385 #19 0x55a09e63ed05 _Z16dispatch_commandP3THDPK8COM_DATA19enum_server_command at /data/mysql-server/sql/sql_parse.cc:2055 #20 0x55a09e64215e _Z10do_commandP3THD at /data/mysql-server/sql/sql_parse.cc:1440 #21 0x55a09e9597f1 _ZL17handle_connectionPv at /data/mysql-server/sql/conn_handler/connection_handler_per_thread.cc:303 #22 0x55a0a1892eb9 _ZL16pfs_spawn_threadPv at /data/mysql-server/storage/perfschema/pfs.cc:3050 #23 0x7f7742a75276 <unknown> #24 0x7f7742afc83b <unknown> AI analyze: /data/mysql-server/sql/sql_tmp_table.cc:503 call stack: create_tmp_field (sql_tmp_table.cc:503) -> create_tmp_table (sql_tmp_table.cc:1143) -> JOIN::create_intermediate_table (sql_executor.cc:244) -> JOIN::make_tmp_tables_info (sql_select.cc:4450) -> JOIN::optimize (sql_optimizer.cc:1022) -> Query_block::optimize How to repeat: CREATE TABLE `mysql_1` ( `col_int` int, `col_varchar` varchar(2000), `col_date` date, `col_timestamp` timestamp , `col_numeric` numeric, /*Indices*/ KEY k2 (`col_int`,`col_date`), KEY k1 (`col_numeric`)) /* Indices QNO 4 CON_ID 9 */ ALTER TABLE `mysql_1` DISABLE KEYS /* QNO 5 CON_ID 9 */ INSERT IGNORE INTO mysql_1 (col_int,col_varchar,col_date,col_timestamp,col_numeric) VALUES (NULL, 'z', '1999-11-18 07:25:13.005498', '2004-02-23 23:19:07.050275', 2) , (1, 'kxceks', '2030-06-08', '2015-06-25', 4) , (1, 'xc', '1988-06-20 00:45:10.036952', '2000-02-02 19:37:56.035563', b'0') , (1, NULL, '2034-01-16 08:16:51.047742', '1973-08-03', NULL) , (1, 'ceksa', '2004-06-02 09:41:11.001950', '1992-11-10', 0) , (3, 'r', '1996-03-09', '2003-04-08', b'0') , (NULL, 'english', '2030-09-25', '2008-05-16', b'1') , (9, 'g', '2010-10-05', '2016-12-22', b'0') , (1, 'z', '1982-05-08 05:43:11.061183', '2022-11-12', 7) , (8, 'x', '2006-04-08 18:19:40.062049', '1987-10-12', b'0') /* QNO 6 CON_ID 9 */ ; COMMIT /* QNO 7 CON_ID 9 */ ; ALTER TABLE `mysql_1` ENABLE KEYS /* QNO 8 CON_ID 9 */ ; CREATE VIEW view1chqin AS SELECT * FROM mysql_1 WHERE col_int > 0; INSERT INTO view1chqin (col_int, col_varchar) SELECT col_int, col_varchar FROM view1chqin WHERE col_int > 100 LIMIT 10 ON DUPLICATE KEY UPDATE col_varchar = VALUES(col_varchar);