[MDEV-27744] LPAD in vcol created in ORACLE mode makes table corrupted in non-ORACLE Created: 2022-02-04  Updated: 2023-11-13  Resolved: 2023-11-08

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - InnoDB, Virtual Columns
Affects Version/s: 10.3, 10.4, 10.5, 10.6, 10.7, 10.8, 10.9, 10.10, 10.11, 11.0
Fix Version/s: 10.5.24, 10.6.17, 10.11.7, 11.0.5, 11.1.4, 11.2.3, 11.3.2, 11.4.1

Type: Bug Priority: Critical
Reporter: Roel Van de Paar Assignee: Alexander Barkov
Resolution: Fixed Votes: 1
Labels: corruption, not-10.2, regression

Issue Links:
Blocks
blocks MDEV-29095 REGEXP_REPLACE treats empty strings d... Closed
is blocked by MDEV-31153 New methods Schema::make_item_func_* ... Closed
is blocked by MDEV-31174 New class Native_functions_hash Closed
is blocked by MDEV-31184 Remove parser tokens DECODE_MARIADB_S... Closed
is blocked by MDEV-31187 Add class Sql_mode_save_for_frm_handling Closed
Relates
relates to MDEV-14013 sql_mode=EMPTY_STRING_IS_NULL Closed
relates to MDEV-17890 Server crash on DELETE with YEAR fiel... Closed
relates to MDEV-19338 InnoDB: Failing assertion: !cursor->i... Closed
relates to MDEV-20484 Server crashes in mem_heap_dup / inno... Closed
relates to MDEV-22739 !cursor->index->is_committed() in row... Closed
relates to MDEV-23563 InnoDB: Failing assertion: !cursor->i... Closed
relates to MDEV-25796 Failing assertion: !cursor->index->is... Open
relates to MDEV-26405 Assertion `0' failed in row_upd_sec_i... Closed
relates to MDEV-26453 Assertion `0' failed in row_upd_sec_i... Closed
relates to MDEV-27145 Failing assertion: !cursor->index->is... Closed
relates to MDEV-28248 Wrong note "function .. has the same ... Open

 Description   

Likely related to: MDEV-17890 (fixed), MDEV-26453 (fixed), MDEV-19338 (fixed), MDEV-20484 (Elena), MDEV-27145 (user reported), MDEV-25796 (idem), MDEV-22739 (idem), MDEV-23563 (idem), MDEV-26405 (idem), and possibly related to MDEV-20640. Issue may be very lightly sporadic.

SET sql_mode='';
 
CREATE TABLE t (d INT,b VARCHAR(1),c CHAR(1),g CHAR(1) GENERATED ALWAYS AS (SUBSTR(b,0,0)) VIRTUAL,PRIMARY KEY(b),KEY g(g)) ENGINE=InnoDB;
 
INSERT INTO t VALUES (0);
 
SET sql_mode='ORACLE';
 
INSERT INTO t SET c=REPEAT (1,0);
 
ALTER TABLE t CHANGE COLUMN a b INT;
 
DELETE FROM t;
 
SET sql_mode='';
 
SET GLOBAL table_open_cache=DEFAULT;
 
INSERT INTO t SET c='0';

Leads to:

10.8.1 0c5d1342ae6b5ab3256848be7a83e5c3b1f21566 (Optimized)

 
2022-02-04 19:16:51 0 [Note] /test/MD290122-mariadb-10.8.1-linux-x86_64-opt/bin/mysqld: ready for connections.
 
Version: '10.8.1-MariaDB'  socket: '/test/MD290122-mariadb-10.8.1-linux-x86_64-opt/socket.sock'  port: 23542  MariaDB Server
 
2022-02-04 19:16:56 4 [ERROR] InnoDB: Record in index `g` of table `test`.`t` was not found on update: TUPLE (info_bits=0, 2 fields): {NULL,[0](0x)} at: COMPACT RECORD(info_bits=0, 1 fields): {[8]infimum (0x696E66696D756D00)}
 
2022-02-04 19:16:56 0x14a4d41c1700  InnoDB: Assertion failure in file /test/10.8_opt/storage/innobase/row/row0ins.cc line 221
 
InnoDB: Failing assertion: !cursor->index->is_committed()

10.8.1 0c5d1342ae6b5ab3256848be7a83e5c3b1f21566 (Optimized)

 
Core was generated by `/test/MD290122-mariadb-10.8.1-linux-x86_64-opt/bin/mysqld --no-defaults --core-'.
 
Program terminated with signal SIGABRT, Aborted.
 
#0  __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:50
 
[Current thread is 1 (Thread 0x14a4d41c1700 (LWP 2318309))]
 
(gdb) bt
 
#0  __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:50
 
#1  0x000014a4ea04a859 in __GI_abort () at abort.c:79
 
#2  0x0000560797ffde61 in ut_dbg_assertion_failed (expr=expr@entry=0x560798c63698 "!cursor->index->is_committed()", file=file@entry=0x560798c631a0 "/test/10.8_opt/storage/innobase/row/row0ins.cc", line=line@entry=221) at /test/10.8_opt/storage/innobase/ut/ut0dbg.cc:60
 
#3  0x0000560797fef767 in row_ins_sec_index_entry_by_modify (mtr=0x14a4d41bea90, thr=0x14a45003fff8, entry=0x14a45001f480, heap=0x14a4500406a8, offsets_heap=<optimized out>, offsets=0x14a4d41be4b8, cursor=0x14a4d41be520, mode=2, flags=0) at /test/10.8_opt/storage/innobase/row/row0ins.cc:221
 
#4  row_ins_sec_index_entry_low (flags=<optimized out>, mode=<optimized out>, index=0x14a45001feb0, offsets_heap=<optimized out>, heap=<optimized out>, entry=<optimized out>, trx_id=<optimized out>, thr=<optimized out>) at /test/10.8_opt/storage/innobase/row/row0ins.cc:3117
 
#5  0x0000560798747e4e in row_ins_sec_index_entry (index=0x14a45001feb0, entry=0x14a45001f480, thr=0x14a45003fff8, check_foreign=<optimized out>) at /test/10.8_opt/storage/innobase/row/row0ins.cc:3331
 
#6  0x000056079874a413 in row_ins_index_entry (thr=0x14a45003fff8, entry=<optimized out>, index=<optimized out>) at /test/10.8_opt/storage/innobase/row/row0ins.cc:3390
 
#7  row_ins_index_entry_step (thr=0x14a45003fff8, node=<optimized out>) at /test/10.8_opt/storage/innobase/row/row0ins.cc:3556
 
#8  row_ins (thr=0x14a45003fff8, node=<optimized out>) at /test/10.8_opt/storage/innobase/row/row0ins.cc:3702
 
#9  row_ins_step (thr=thr@entry=0x14a45003fff8) at /test/10.8_opt/storage/innobase/row/row0ins.cc:3848
 
#10 0x000056079875c2e5 in row_insert_for_mysql (mysql_rec=mysql_rec@entry=0x14a4500259a8 <incomplete sequence \371>, prebuilt=0x14a45003f830, ins_mode=ROW_INS_NORMAL) at /test/10.8_opt/storage/innobase/row/row0mysql.cc:1318
 
#11 0x00005607986a571a in ha_innobase::write_row (this=0x14a450022830, record=0x14a4500259a8 <incomplete sequence \371>) at /test/10.8_opt/storage/innobase/handler/ha_innodb.cc:7777
 
#12 0x00005607983b8cd0 in handler::ha_write_row (this=0x14a450022830, buf=0x14a4500259a8 <incomplete sequence \371>) at /test/10.8_opt/sql/handler.cc:7519
 
#13 0x000056079812381d in write_record (thd=thd@entry=0x14a450000c58, table=table@entry=0x56079a535688, info=info@entry=0x14a4d41bfc80, sink=sink@entry=0x0) at /test/10.8_opt/sql/sql_insert.cc:2156
 
#14 0x000056079812a058 in mysql_insert (thd=thd@entry=0x14a450000c58, table_list=<optimized out>, fields=@0x14a450005d60: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x14a450011240, last = 0x14a450011240, elements = 1}, <No data fields>}, values_list=@0x14a450005da8: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x14a450011090, last = 0x14a450011090, elements = 1}, <No data fields>}, update_fields=@0x14a450005d90: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x5607992d94b0 <end_of_list>, last = 0x14a450005d90, elements = 0}, <No data fields>}, update_values=@0x14a450005d78: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x5607992d94b0 <end_of_list>, last = 0x14a450005d78, elements = 0}, <No data fields>}, duplic=<optimized out>, ignore=<optimized out>, result=<optimized out>) at /test/10.8_opt/sql/sql_insert.cc:1127
 
#15 0x0000560798164e9f in mysql_execute_command (thd=0x14a450000c58, is_called_from_prepared_stmt=<optimized out>) at /test/10.8_opt/sql/sql_parse.cc:4562
 
#16 0x00005607981546f6 in mysql_parse (thd=0x14a450000c58, rawbuf=<optimized out>, length=<optimized out>, parser_state=<optimized out>) at /test/10.8_opt/sql/sql_parse.cc:8027
 
#17 0x00005607981608a5 in dispatch_command (command=COM_QUERY, thd=0x14a450000c58, packet=<optimized out>, packet_length=<optimized out>, blocking=<optimized out>) at /test/10.8_opt/sql/sql_class.h:1362
 
#18 0x0000560798162a97 in do_command (thd=0x14a450000c58, blocking=blocking@entry=true) at /test/10.8_opt/sql/sql_parse.cc:1402
 
#19 0x0000560798282247 in do_handle_one_connection (connect=<optimized out>, put_in_cache=true) at /test/10.8_opt/sql/sql_connect.cc:1418
 
#20 0x000056079828258d in handle_one_connection (arg=arg@entry=0x56079a5122a8) at /test/10.8_opt/sql/sql_connect.cc:1312
 
#21 0x00005607985f65b8 in pfs_spawn_thread (arg=0x56079a4c98d8) at /test/10.8_opt/storage/perfschema/pfs.cc:2201
 
#22 0x000014a4ea559609 in start_thread (arg=<optimized out>) at pthread_create.c:477
 
#23 0x000014a4ea147293 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95

10.8.1 0c5d1342ae6b5ab3256848be7a83e5c3b1f21566 (Debug)

 
2022-02-04 19:16:52 0 [Note] /test/MD290122-mariadb-10.8.1-linux-x86_64-dbg/bin/mysqld: ready for connections.
 
Version: '10.8.1-MariaDB-debug'  socket: '/test/MD290122-mariadb-10.8.1-linux-x86_64-dbg/socket.sock'  port: 38578  MariaDB Server
 
2022-02-04 19:16:56 4 [ERROR] InnoDB: Record in index `g` of table `test`.`t` was not found on update: TUPLE (info_bits=0, 2 fields): {NULL,[0](0x)} at: COMPACT RECORD(info_bits=0, 1 fields): {[8]infimum (0x696E66696D756D00)}
 
mysqld: /test/10.8_dbg/storage/innobase/row/row0upd.cc:2050: dberr_t row_upd_sec_index_entry(upd_node_t*, que_thr_t*): Assertion `0' failed.

10.8.1 0c5d1342ae6b5ab3256848be7a83e5c3b1f21566 (Debug)

 
Core was generated by `/test/MD290122-mariadb-10.8.1-linux-x86_64-dbg/bin/mysqld --no-defaults --core-'.
 
Program terminated with signal SIGABRT, Aborted.
 
#0  __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:50
 
[Current thread is 1 (Thread 0x14a6cc14c700 (LWP 2318883))]
 
(gdb) bt
 
#0  __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:50
 
#1  0x000014a6d4faf859 in __GI_abort () at abort.c:79
 
#2  0x000014a6d4faf729 in __assert_fail_base (fmt=0x14a6d5145588 "%s%s%s:%u: %s%sAssertion `%s' failed.\n%n", assertion=0x55c003cf64f7 "0", file=0x55c003de5138 "/test/10.8_dbg/storage/innobase/row/row0upd.cc", line=2050, function=<optimized out>) at assert.c:92
 
#3  0x000014a6d4fc0f36 in __GI___assert_fail (assertion=assertion@entry=0x55c003cf64f7 "0", file=file@entry=0x55c003de5138 "/test/10.8_dbg/storage/innobase/row/row0upd.cc", line=line@entry=2050, function=function@entry=0x55c003de6510 "dberr_t row_upd_sec_index_entry(upd_node_t*, que_thr_t*)") at assert.c:101
 
#4  0x000055c00371361b in row_upd_sec_index_entry (node=node@entry=0x14a654020c48, thr=thr@entry=0x14a654020fa0) at /test/10.8_dbg/storage/innobase/row/row0upd.cc:2050
 
#5  0x000055c0037143f3 in row_upd_sec_step (thr=0x14a654020fa0, node=0x14a654020c48) at /test/10.8_dbg/storage/innobase/row/row0upd.cc:2199
 
#6  row_upd (thr=0x14a654020fa0, node=0x14a654020c48) at /test/10.8_dbg/storage/innobase/row/row0upd.cc:2935
 
#7  row_upd_step (thr=thr@entry=0x14a654020fa0) at /test/10.8_dbg/storage/innobase/row/row0upd.cc:3050
 
#8  0x000055c0036bb079 in row_update_for_mysql (prebuilt=0x14a654020488) at /test/10.8_dbg/storage/innobase/row/row0mysql.cc:1700
 
#9  0x000055c003525a66 in ha_innobase::delete_row (this=0x14a65402d070, record=0x14a654072d28 <incomplete sequence \375>) at /test/10.8_dbg/storage/innobase/handler/ha_innodb.cc:8650
 
#10 0x000055c0031843da in handler::ha_delete_row (this=0x14a65402d070, buf=0x14a654072d28 <incomplete sequence \375>) at /test/10.8_dbg/sql/handler.cc:7651
 
#11 0x000055c002e0d59d in TABLE::delete_row (this=0x14a6540728d8) at /test/10.8_dbg/sql/sql_delete.cc:281
 
#12 0x000055c002e0b58b in mysql_delete (thd=thd@entry=0x14a654000db8, table_list=0x14a654013ea0, conds=<optimized out>, order_list=order_list@entry=0x14a654005ca8, limit=18446744073709551615, options=<optimized out>, result=<optimized out>) at /test/10.8_dbg/sql/sql_delete.cc:833
 
#13 0x000055c002e6df09 in mysql_execute_command (thd=thd@entry=0x14a654000db8, is_called_from_prepared_stmt=is_called_from_prepared_stmt@entry=false) at /test/10.8_dbg/sql/sql_limit.h:85
 
#14 0x000055c002e57db7 in mysql_parse (thd=thd@entry=0x14a654000db8, rawbuf=<optimized out>, length=<optimized out>, parser_state=parser_state@entry=0x14a6cc14b400) at /test/10.8_dbg/sql/sql_parse.cc:8027
 
#15 0x000055c002e66a53 in dispatch_command (command=command@entry=COM_QUERY, thd=thd@entry=0x14a654000db8, packet=packet@entry=0x14a65400b889 "DELETE FROM t", packet_length=packet_length@entry=13, blocking=blocking@entry=true) at /test/10.8_dbg/sql/sql_class.h:1362
 
#16 0x000055c002e69e9a in do_command (thd=0x14a654000db8, blocking=blocking@entry=true) at /test/10.8_dbg/sql/sql_parse.cc:1402
 
#17 0x000055c002fe5110 in do_handle_one_connection (connect=<optimized out>, connect@entry=0x55c0067051a8, put_in_cache=put_in_cache@entry=true) at /test/10.8_dbg/sql/sql_connect.cc:1418
 
#18 0x000055c002fe5715 in handle_one_connection (arg=arg@entry=0x55c0067051a8) at /test/10.8_dbg/sql/sql_connect.cc:1312
 
#19 0x000055c00346ec8e in pfs_spawn_thread (arg=0x55c006619328) at /test/10.8_dbg/storage/perfschema/pfs.cc:2201
 
#20 0x000014a6d54be609 in start_thread (arg=<optimized out>) at pthread_create.c:477
 
#21 0x000014a6d50ac293 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95

Bug confirmed present in:
MariaDB: 10.3.33 (dbg), 10.3.33 (opt), 10.4.23 (dbg), 10.4.23 (opt), 10.5.14 (dbg), 10.5.14 (opt), 10.6.6 (dbg), 10.6.6 (opt), 10.7.2 (dbg), 10.7.2 (opt), 10.8.1 (dbg), 10.8.1 (opt)

Bug (or feature/syntax) confirmed not present in:
MariaDB: 10.2.42 (dbg), 10.2.42 (opt)
MySQL: 5.5.62 (dbg), 5.5.62 (opt), 5.6.51 (dbg), 5.6.51 (opt), 5.7.36 (dbg), 5.7.36 (opt), 8.0.27 (dbg), 8.0.27 (opt)

 Comments   
Comment by Marko Mäkelä [ 2022-02-04 ]

bar, I am assigning this to you as the author of the Oracle compatibility mode.

As far as I understand, in the Oracle mode, the empty string and NULL are equivalent, while by default those are distinguished.

The problem appears to be that the Oracle mode is not part of the indexed virtual column specification.

If I understood the test case correctly, it is timing-sensitive. It depends on the fact that the delete-marked record has not been purged before the INSERT attempts to replace the purgeable delete-marked index record with a new one. To make the test fully deterministic, you should start a new connection before the DELETE statement, and execute START TRANSACTION WITH CONSISTENT SNAPSHOT in it, so that purge cannot remove the history of the DELETE. That new connection must remain open until after the final INSERT.

Comment by Roel Van de Paar [ 2022-02-14 ]

Got a secondary testcase

SET sql_mode='';
 
CREATE TABLE t (a INT(1),d INT(1),b VARCHAR(1),c CHAR(1),vadc INT(1) GENERATED ALWAYS AS ( (a + length (d))) STORED,vbc CHAR(1) GENERATED ALWAYS AS (SUBSTR(b,0,0)) VIRTUAL,vbidxc CHAR(1) GENERATED ALWAYS AS (SUBSTR(b,0,0)) VIRTUAL,PRIMARY KEY(b (1),a,d),KEY d (d),KEY a (a),KEY c_renamed (c (1),b (1)),KEY b (b (1),c (1),a),KEY vbidxc (vbidxc),KEY a_2 (a,vbidxc),KEY vbidxc_2 (vbidxc,d)) DEFAULT CHARSET=latin1 ENGINE=InnoDB;
 
INSERT INTO t VALUES (0,0,1,0,1,0,1,0,0);
 
SET SESSION sql_mode='ORACLE';
 
INSERT INTO t SET c=REPEAT (1,0);
 
ALTER TABLE t CHANGE COLUMN a b CHAR(1);
 
DELETE FROM t;
 
SET SESSION sql_mode='DEFAULT';

Leads to:

10.9.0 b5852ffbeebc3000982988383daeefb0549e058a (Debug)

 
mysqld: /test/10.9_dbg/storage/innobase/row/row0upd.cc:2050: dberr_t row_upd_sec_index_entry(upd_node_t*, que_thr_t*): Assertion `0' failed.

10.9.0 b5852ffbeebc3000982988383daeefb0549e058a (Debug)

 
Core was generated by `/test/MD140222-mariadb-10.9.0-linux-x86_64-dbg/bin/mysqld --no-defaults --core-'.
 
Program terminated with signal SIGABRT, Aborted.
 
#0  __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:50
 
[Current thread is 1 (Thread 0x14c22c0c1700 (LWP 1470380))]
 
(gdb) bt
 
#0  __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:50
 
#1  0x000014c22fc7e859 in __GI_abort () at abort.c:79
 
#2  0x000014c22fc7e729 in __assert_fail_base (fmt=0x14c22fe14588 "%s%s%s:%u: %s%sAssertion `%s' failed.\n%n", assertion=0x557c45e931e7 "0", file=0x557c45f81138 "/test/10.9_dbg/storage/innobase/row/row0upd.cc", line=2050, function=<optimized out>) at assert.c:92
 
#3  0x000014c22fc8ff36 in __GI___assert_fail (assertion=assertion@entry=0x557c45e931e7 "0", file=file@entry=0x557c45f81138 "/test/10.9_dbg/storage/innobase/row/row0upd.cc", line=line@entry=2050, function=function@entry=0x557c45f82510 "dberr_t row_upd_sec_index_entry(upd_node_t*, que_thr_t*)") at assert.c:101
 
#4  0x0000557c458a3fe1 in row_upd_sec_index_entry (node=node@entry=0x14c19c0252a8, thr=thr@entry=0x14c19c025690) at /test/10.9_dbg/storage/innobase/row/row0upd.cc:2050
 
#5  0x0000557c458a4db9 in row_upd_sec_step (thr=0x14c19c025690, node=0x14c19c0252a8) at /test/10.9_dbg/storage/innobase/row/row0upd.cc:2199
 
#6  row_upd (thr=0x14c19c025690, node=0x14c19c0252a8) at /test/10.9_dbg/storage/innobase/row/row0upd.cc:2935
 
#7  row_upd_step (thr=thr@entry=0x14c19c025690) at /test/10.9_dbg/storage/innobase/row/row0upd.cc:3050
 
#8  0x0000557c4584ba39 in row_update_for_mysql (prebuilt=0x14c19c024a18) at /test/10.9_dbg/storage/innobase/row/row0mysql.cc:1700
 
#9  0x0000557c456b5c20 in ha_innobase::delete_row (this=0x14c19c01f170, record=0x14c19c07ac38 <incomplete sequence \374>) at /test/10.9_dbg/storage/innobase/handler/ha_innodb.cc:8706
 
#10 0x0000557c4531fede in handler::ha_delete_row (this=0x14c19c01f170, buf=0x14c19c07ac38 <incomplete sequence \374>) at /test/10.9_dbg/sql/handler.cc:7654
 
#11 0x0000557c44fa79db in TABLE::delete_row (this=0x14c19c07a5e8) at /test/10.9_dbg/sql/sql_delete.cc:281
 
#12 0x0000557c44fa59c9 in mysql_delete (thd=thd@entry=0x14c19c000db8, table_list=0x14c19c013ea0, conds=<optimized out>, order_list=order_list@entry=0x14c19c005ca8, limit=18446744073709551615, options=<optimized out>, result=<optimized out>) at /test/10.9_dbg/sql/sql_delete.cc:833
 
#13 0x0000557c45008467 in mysql_execute_command (thd=thd@entry=0x14c19c000db8, is_called_from_prepared_stmt=is_called_from_prepared_stmt@entry=false) at /test/10.9_dbg/sql/sql_limit.h:85
 
#14 0x0000557c44ff2315 in mysql_parse (thd=thd@entry=0x14c19c000db8, rawbuf=<optimized out>, length=<optimized out>, parser_state=parser_state@entry=0x14c22c0c0400) at /test/10.9_dbg/sql/sql_parse.cc:8027
 
#15 0x0000557c45000fb1 in dispatch_command (command=command@entry=COM_QUERY, thd=thd@entry=0x14c19c000db8, packet=packet@entry=0x14c19c00b889 "DELETE FROM t", packet_length=packet_length@entry=13, blocking=blocking@entry=true) at /test/10.9_dbg/sql/sql_class.h:1362
 
#16 0x0000557c450043f8 in do_command (thd=0x14c19c000db8, blocking=blocking@entry=true) at /test/10.9_dbg/sql/sql_parse.cc:1402
 
#17 0x0000557c4517efc4 in do_handle_one_connection (connect=<optimized out>, connect@entry=0x557c48b3aa58, put_in_cache=put_in_cache@entry=true) at /test/10.9_dbg/sql/sql_connect.cc:1418
 
#18 0x0000557c4517f5c9 in handle_one_connection (arg=arg@entry=0x557c48b3aa58) at /test/10.9_dbg/sql/sql_connect.cc:1312
 
#19 0x0000557c45605d67 in pfs_spawn_thread (arg=0x557c48a4dc78) at /test/10.9_dbg/storage/perfschema/pfs.cc:2201
 
#20 0x000014c23018d609 in start_thread (arg=<optimized out>) at pthread_create.c:477
 
#21 0x000014c22fd7b293 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95

Bug confirmed present in:
MariaDB: 10.3.33 (dbg), 10.4.23 (dbg), 10.5.14 (dbg), 10.6.6 (dbg), 10.7.2 (dbg), 10.8.1 (dbg), 10.9.0 (dbg)

Bug (or feature/syntax) confirmed not present in:
MariaDB: 10.2.42 (dbg), 10.2.42 (opt), 10.3.33 (opt), 10.4.23 (opt), 10.5.14 (opt), 10.6.6 (opt), 10.7.2 (opt), 10.8.1 (opt), 10.9.0 (opt)
MySQL: 5.5.62 (dbg), 5.5.62 (opt), 5.6.51 (dbg), 5.6.51 (opt), 5.7.36 (dbg), 5.7.36 (opt), 8.0.27 (dbg), 8.0.27 (opt)

Comment by Roel Van de Paar [ 2022-03-30 ]

Another testcase:

SET sql_mode='';
 
CREATE TABLE t (a INT(1),d INT(1),b VARCHAR(1),c CHAR(1),vadc INT(1) GENERATED ALWAYS AS ( (a + length (d))) STORED,vbc CHAR(1) GENERATED ALWAYS AS (SUBSTR(b,0,0)) VIRTUAL,vbidxc CHAR(1) GENERATED ALWAYS AS (SUBSTR(b,0,0)) VIRTUAL,PRIMARY KEY(b (1),a,d),KEY d (d),KEY a (a),KEY c_renamed (c (1),b (1)),KEY b (b (1),c (1),a),KEY vbidxc (vbidxc),KEY a_2 (a,vbidxc),KEY vbidxc_2 (vbidxc,d)) DEFAULT CHARSET=latin1 ENGINE=InnoDB;
 
INSERT INTO t VALUES (0,0,1,0,1,0,1,0,0);
 
SET SESSION sql_mode=ORACLE;
 
INSERT INTO t SET c=REPEAT (1,0);
 
ALTER TABLE t CHANGE COLUMN a b CHAR(1);
 
DELETE FROM t;
 
SET SESSION sql_mode=DEFAULT;
 
SET GLOBAL table_open_cache=DEFAULT;
 
INSERT INTO t SET c=CONCAT (REPEAT (1,0),1,1);

Will produce, on 10.8.1 optimized:

10.8.3 9f5a3e568913e0810109554608c56c93f3ec24f8 (Optimized)

 
10.8.3-opt>INSERT INTO t SET c=CONCAT (REPEAT (1,0),1,1);
 
ERROR 1364 (HY000): Field 'a' doesn't have a default value

10.8.3 9f5a3e568913e0810109554608c56c93f3ec24f8 (Optimized)

 
2022-03-30 21:31:10 0 [Note] /test/MD160322-mariadb-10.8.3-linux-x86_64-opt/bin/mysqld: ready for connections.
 
Version: '10.8.3-MariaDB'  socket: '/test/MD160322-mariadb-10.8.3-linux-x86_64-opt/socket.sock'  port: 10725  MariaDB Server
 
2022-03-30 21:31:10 0 [Note] InnoDB: Buffer pool(s) load completed at 220330 21:31:10
 
2022-03-30 21:31:12 4 [ERROR] InnoDB: Record in index `vbidxc` of table `test`.`t` was not found on update: TUPLE (info_bits=0, 4 fields): {NULL,[0](0x),[4]    (0x80000000),[4]    (0x80000000)} at: COMPACT RECORD(info_bits=0, 1 fields): {[8]infimum (0x696E66696D756D00)}
 
2022-03-30 21:31:12 4 [ERROR] InnoDB: Record in index `a_2` of table `test`.`t` was not found on update: TUPLE (info_bits=0, 4 fields): {[4]    (0x80000000),NULL,[0](0x),[4]    (0x80000000)} at: COMPACT RECORD(info_bits=0, 1 fields): {[8]infimum (0x696E66696D756D00)}
 
2022-03-30 21:31:12 4 [ERROR] InnoDB: Record in index `vbidxc_2` of table `test`.`t` was not found on update: TUPLE (info_bits=0, 4 fields): {NULL,[4]    (0x80000000),[0](0x),[4]    (0x80000000)} at: COMPACT RECORD(info_bits=0, 1 fields): {[8]infimum (0x696E66696D756D00)}

On debug versions it will crash earlier:

10.9.0 5be92887c2caacb45af87b1131db952ce627e83a (Debug)

 
mysqld: /test/10.9_dbg/storage/innobase/row/row0upd.cc:2050: dberr_t row_upd_sec_index_entry(upd_node_t*, que_thr_t*): Assertion `0' failed.

10.9.0 5be92887c2caacb45af87b1131db952ce627e83a (Debug)

 
Core was generated by `/test/MD160322-mariadb-10.9.0-linux-x86_64-dbg/bin/mysqld --no-defaults --core-'.
 
Program terminated with signal SIGABRT, Aborted.
 
#0  __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:50
 
[Current thread is 1 (Thread 0x15213c0d5700 (LWP 2702076))]
 
(gdb) bt
 
#0  __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:50
 
#1  0x0000152145535859 in __GI_abort () at abort.c:79
 
#2  0x0000152145535729 in __assert_fail_base (fmt=0x1521456cb588 "%s%s%s:%u: %s%sAssertion `%s' failed.\n%n", assertion=0x55f2420291e7 "0", file=0x55f242117380 "/test/10.9_dbg/storage/innobase/row/row0upd.cc", line=2050, function=<optimized out>) at assert.c:92
 
#3  0x0000152145547006 in __GI___assert_fail (assertion=assertion@entry=0x55f2420291e7 "0", file=file@entry=0x55f242117380 "/test/10.9_dbg/storage/innobase/row/row0upd.cc", line=line@entry=2050, function=function@entry=0x55f242118768 "dberr_t row_upd_sec_index_entry(upd_node_t*, que_thr_t*)") at assert.c:101
 
#4  0x000055f241a38866 in row_upd_sec_index_entry (node=node@entry=0x152098025208, thr=thr@entry=0x1520980255f0) at /test/10.9_dbg/storage/innobase/row/row0upd.cc:2050
 
#5  0x000055f241a3963e in row_upd_sec_step (thr=0x1520980255f0, node=0x152098025208) at /test/10.9_dbg/storage/innobase/row/row0upd.cc:2199
 
#6  row_upd (thr=0x1520980255f0, node=0x152098025208) at /test/10.9_dbg/storage/innobase/row/row0upd.cc:2936
 
#7  row_upd_step (thr=thr@entry=0x1520980255f0) at /test/10.9_dbg/storage/innobase/row/row0upd.cc:3051
 
#8  0x000055f2419e0359 in row_update_for_mysql (prebuilt=0x152098024978) at /test/10.9_dbg/storage/innobase/row/row0mysql.cc:1700
 
#9  0x000055f241848944 in ha_innobase::delete_row (this=0x15209801f0d0, record=0x15209807aba8 <incomplete sequence \374>) at /test/10.9_dbg/storage/innobase/handler/ha_innodb.cc:8673
 
#10 0x000055f2414b3176 in handler::ha_delete_row (this=0x15209801f0d0, buf=0x15209807aba8 <incomplete sequence \374>) at /test/10.9_dbg/sql/handler.cc:7659
 
#11 0x000055f24113aafd in TABLE::delete_row (this=0x15209807a558) at /test/10.9_dbg/sql/sql_delete.cc:281
 
#12 0x000055f241138a6d in mysql_delete (thd=thd@entry=0x152098000db8, table_list=0x152098013ea0, conds=<optimized out>, order_list=order_list@entry=0x152098005ca8, limit=18446744073709551615, options=<optimized out>, result=<optimized out>) at /test/10.9_dbg/sql/sql_delete.cc:834
 
#13 0x000055f24119b589 in mysql_execute_command (thd=thd@entry=0x152098000db8, is_called_from_prepared_stmt=is_called_from_prepared_stmt@entry=false) at /test/10.9_dbg/sql/sql_limit.h:85
 
#14 0x000055f241185437 in mysql_parse (thd=thd@entry=0x152098000db8, rawbuf=<optimized out>, length=<optimized out>, parser_state=parser_state@entry=0x15213c0d4400) at /test/10.9_dbg/sql/sql_parse.cc:8027
 
#15 0x000055f2411940d3 in dispatch_command (command=command@entry=COM_QUERY, thd=thd@entry=0x152098000db8, packet=packet@entry=0x15209800b889 "DELETE FROM t", packet_length=packet_length@entry=13, blocking=blocking@entry=true) at /test/10.9_dbg/sql/sql_class.h:1362
 
#16 0x000055f24119751a in do_command (thd=0x152098000db8, blocking=blocking@entry=true) at /test/10.9_dbg/sql/sql_parse.cc:1402
 
#17 0x000055f241312228 in do_handle_one_connection (connect=<optimized out>, connect@entry=0x55f2448f43e8, put_in_cache=put_in_cache@entry=true) at /test/10.9_dbg/sql/sql_connect.cc:1418
 
#18 0x000055f24131282d in handle_one_connection (arg=arg@entry=0x55f2448f43e8) at /test/10.9_dbg/sql/sql_connect.cc:1312
 
#19 0x000055f241798f23 in pfs_spawn_thread (arg=0x55f244808588) at /test/10.9_dbg/storage/perfschema/pfs.cc:2201
 
#20 0x0000152145a46609 in start_thread (arg=<optimized out>) at pthread_create.c:477
 
#21 0x0000152145632163 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95

Crash is present in:
MariaDB: 10.3.35 (dbg), 10.4.25 (dbg), 10.5.16 (dbg), 10.6.8 (dbg), 10.7.4 (dbg), 10.8.3 (dbg), 10.9.0 (dbg)
And optimized output as per above.

Comment by Alexander Barkov [ 2022-03-30 ]

The first script is also repeatable with this variant:

DROP TABLE IF EXISTS t1;
 
SET sql_mode='';
 
CREATE TABLE t1 (d INT,b VARCHAR(1),c CHAR(1),g CHAR(1) GENERATED ALWAYS AS (SUBSTR(b,0,0)) VIRTUAL,PRIMARY KEY(b),KEY g(g)) ENGINE=InnoDB;
 
INSERT INTO t1 VALUES (0);
 
SET sql_mode='ORACLE';
 
INSERT INTO t1 SET c=REPEAT (1,0);
 
ALTER TABLE t1 CHANGE COLUMN a b INT;
 
DELETE FROM t1;
 
SET sql_mode='';
 
FLUSH TABLES;
 
INSERT INTO t1 SET c='0';

Notice

FLUSH TABLES;

vs

SET GLOBAL table_open_cache=DEFAULT;

Comment by Alexander Barkov [ 2022-04-07 ]

serg, please review a patch:

https://github.com/MariaDB/server/commit/96b40a5e2f5c823df2b4b8f383566065ba2b110f

Thanks.

Comment by Alexander Barkov [ 2022-04-22 ]

serg,

Another patch version:
https://github.com/MariaDB/server/commit/d67c3f88883b616a9adf3abba43938c3a07a5eee

Now the "view_body_utf8=" field in a view FRM file is written with forced qualifiers.

It's used for I_S.VIEW.VIEW_DEFINITION output as is, so must be created in an universal way which looks unambiguously in any sql_mode.

The "query=" field in a view FRM file is written using the old notation:

  • MariaDB functions as is: substr(1,2,3)
  • Oracle function with a suffyx: substr_oracle(1,2,3)

This is needed for downgrade compatibility, as discussed on slack.

Comment by Alexander Barkov [ 2023-05-04 ]

Hello serg, please find a new version here:

https://github.com/MariaDB/server/commits/bb-10.4-bar-MDEV-27744-v2

and my reply to review comments by email.

There are three patches in this branch:
MDEV-31184
MDEV-31187
MDEV-27744

Thanks.

Comment by Alexander Barkov [ 2023-10-31 ]

Hello Sergei,

Here's a new patch version:

https://github.com/MariaDB/server/commit/dd92459b0b16fdd431b216d2787e24d57e1b2b58

Please also see my comments to developers@lists.mariadb.org

Comment by Sergei Golubchik [ 2023-11-03 ]

dd92459b0b16fdd431b216d2787e24d57e1b2b58 is ok to push, thanks!

I still suggest to fix the test case to use FLUSH TABLES, as mentioned in the email.

Generated at Thu Feb 08 09:55:15 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.