Alexander Barkov added a comment - 2019-02-18 06:09 - edited The problem happens because after changing sql_mode, the value of the column 'v' changes from CONCAT('bar',0x0000000000) to CONCAT('bar', 0x2020202020) . The engine then notices that the index value does not match to the column value and reports an index corruption. This script demonstrates the difference: SET SQL_MODE= '' ; CREATE OR REPLACE TABLE t1 (c CHAR (8), v BINARY (8) AS (c), KEY (v)); INSERT INTO t1 (c) VALUES ( 'foo' ); SELECT HEX( CAST (c AS BINARY (8))) AS nopad FROM t1; SET SQL_MODE= 'PAD_CHAR_TO_FULL_LENGTH' ; SELECT HEX( CAST (c AS BINARY (8))) AS pad FROM t1; +------------------+ | nopad | +------------------+ | 666F6F0000000000 | +------------------+ +------------------+ | pad | +------------------+ | 666F6F2020202020 | +------------------+ A possible solution would be to disallow CHAR columns inside virtual column expressions.

Elena Stepanova added a comment - 2019-05-25 23:03 This one now also causes btr_validate_index assertion (with InnoDB): 10.3 592dc59d 2019-05-26 2:01:41 9 [ERROR] InnoDB: Record in index `v` of table `test`.`t1` was not found on update: TUPLE (info_bits=0, 2 fields): {[8]foo (0x666F6F2020202020),[6] (0x000000000200)} at: COMPACT RECORD(info_bits=0, 2 fields): {[8]foo (0x666F6F0000000000),[6] (0x000000000200)} mysqld: /data/src/10.3/storage/innobase/row/row0upd.cc:2449: dberr_t row_upd_sec_index_entry(upd_node_t*, que_thr_t*): Assertion `btr_validate_index(index, 0, false)' failed. 190526 2:01:41 [ERROR] mysqld got signal 6 ;   #7 0x00007fc65ff3ef12 in __GI___assert_fail (assertion=0x55cb3b1e7aa0 "btr_validate_index(index, 0, false)", file=0x55cb3b1e7498 "/data/src/10.3/storage/innobase/row/row0upd.cc", line=2449, function=0x55cb3b1ea3a0 <row_upd_sec_index_entry(upd_node_t*, que_thr_t*)::__PRETTY_FUNCTION__> "dberr_t row_upd_sec_index_entry(upd_node_t*, que_thr_t*)") at assert.c:101 #8 0x000055cb3aaf5244 in row_upd_sec_index_entry (node=0x7fc6080a75e0, thr=0x7fc608181458) at /data/src/10.3/storage/innobase/row/row0upd.cc:2449 #9 0x000055cb3aaf5956 in row_upd_sec_step (node=0x7fc6080a75e0, thr=0x7fc608181458) at /data/src/10.3/storage/innobase/row/row0upd.cc:2563 #10 0x000055cb3aaf8127 in row_upd (node=0x7fc6080a75e0, thr=0x7fc608181458) at /data/src/10.3/storage/innobase/row/row0upd.cc:3328 #11 0x000055cb3aaf8495 in row_upd_step (thr=0x7fc608181458) at /data/src/10.3/storage/innobase/row/row0upd.cc:3443 #12 0x000055cb3aa9cbc4 in row_update_for_mysql (prebuilt=0x7fc6080a6af8) at /data/src/10.3/storage/innobase/row/row0mysql.cc:1887 #13 0x000055cb3a942e29 in ha_innobase::delete_row (this=0x7fc6080a5c68, record=0x7fc608008290 "\371foo foo ", '\245' <repeats 31 times>, "\330\202") at /data/src/10.3/storage/innobase/handler/ha_innodb.cc:8999 #14 0x000055cb3a73b6ac in handler::ha_delete_row (this=0x7fc6080a5c68, buf=0x7fc608008290 "\371foo foo ", '\245' <repeats 31 times>, "\330\202") at /data/src/10.3/sql/handler.cc:6508 #15 0x000055cb3a8cea89 in TABLE::delete_row (this=0x7fc60800a5e0) at /data/src/10.3/sql/sql_delete.cc:253 #16 0x000055cb3a8cbdec in mysql_delete (thd=0x7fc608000b00, table_list=0x7fc6080128f0, conds=0x7fc6080130f8, order_list=0x7fc6080053b8, limit=18446744073709551615, options=0, result=0x0) at /data/src/10.3/sql/sql_delete.cc:747 #17 0x000055cb3a43558d in mysql_execute_command (thd=0x7fc608000b00) at /data/src/10.3/sql/sql_parse.cc:4931 #18 0x000055cb3a43f8d8 in mysql_parse (thd=0x7fc608000b00, rawbuf=0x7fc608012808 "DELETE FROM t1 WHERE c <> 'bar'", length=31, parser_state=0x7fc6592dc5f0, is_com_multi=false, is_next_command=false) at /data/src/10.3/sql/sql_parse.cc:8098 #19 0x000055cb3a42c91a in dispatch_command (command=COM_QUERY, thd=0x7fc608000b00, packet=0x7fc60815fe71 "DELETE FROM t1 WHERE c <> 'bar'", packet_length=31, is_com_multi=false, is_next_command=false) at /data/src/10.3/sql/sql_parse.cc:1858 #20 0x000055cb3a42b33d in do_command (thd=0x7fc608000b00) at /data/src/10.3/sql/sql_parse.cc:1403 #21 0x000055cb3a59356b in do_handle_one_connection (connect=0x55cb3c9bd450) at /data/src/10.3/sql/sql_connect.cc:1402 #22 0x000055cb3a5932e2 in handle_one_connection (arg=0x55cb3c9bd450) at /data/src/10.3/sql/sql_connect.cc:1308 #23 0x000055cb3ae6aa8c in pfs_spawn_thread (arg=0x55cb3c900ab0) at /data/src/10.3/storage/perfschema/pfs.cc:1862 #24 0x00007fc661ab34a4 in start_thread (arg=0x7fc6592dd700) at pthread_create.c:456 #25 0x00007fc65fffbd0f in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:97

Alexander Barkov added a comment - 2019-08-23 18:49 - edited A similar crash happens with the NO_UNSIGNED_SUBTRACTION flag change: SET sql_mode=NO_UNSIGNED_SUBTRACTION; CREATE OR REPLACE TABLE t1 ( a INT UNSIGNED, b INT UNSIGNED, c INT GENERATED ALWAYS AS (a-b) VIRTUAL, KEY (c) ); INSERT IGNORE INTO t1 (a,b) VALUES (1,2); SELECT * FROM t1; SET sql_mode= '' ; FLUSH TABLES; SELECT * FROM t1; UPDATE t1 SET a=3;

Alexander Barkov added a comment - 2019-08-23 18:56 - edited A similar crash happens with TIME_ROUND_FRACTIONAL. See MDEV-20423 .

Alexander Barkov added a comment - 2019-08-26 11:34 Please review a patch: https://github.com/MariaDB/server/commit/5f1cd8fe7a5bb3bba7e00791943c3ebf8e3c341b

Alexander Barkov added a comment - 2019-08-28 15:52 - edited serg , please review a new version: https://github.com/MariaDB/server/commit/e4415549e5374f6fcb5d317a7078ead610adfa6c

Sergei Golubchik added a comment - 2019-08-28 18:00 This patch is good, basically ok to push. But it makes old tables unreadable (an error when a table is opened). We need some reasonable user-friendly behavior on upgrades. In a separate patch, yes. But both have to be pushed together, otherwise we risk that the first patch will be released alone and the second patch won't make it.

Alexander Barkov added a comment - 2019-08-29 08:47 serg , please find an additional patch here: https://github.com/MariaDB/server/commit/899c0b3ec6e609e34b217e1f35e3c07a901e4a92 It allows to open old tables for most purposes, only creation of "bad" virtual fields is now disallowed. Note, automatic fixing, like adding RTRIM()/RPAD(), is not implemented yet. Thanks.

Sergei Golubchik added a comment - 2019-08-30 16:34 ok to push

Elena Stepanova added a comment - 2020-08-11 23:24 For the record, despite having a long history, I think it ended up only fixed in 10.5, as the fix wasn't acceptable for post-GA versions. But there were several commits in all branches, so I'm not sure what is the correct value for fixVersion.