The root cause of the problem is however in the server, which allows to use in indexed virtual columns unsafe expressions depending on the session varianbles. For details see:
The following scripts demonstrate the crash in InnoDB on @@time_zone change. However, a similar crash can also happen on @@div_precision_increment and @@max_allowed_packet. This needs to be checked.
This script demonstrates the crash:
SET time_zone='+00:00';
CREATEORREPLACETABLE t1 (
a DATETIME,
v TIMESTAMP GENERATED ALWAYS AS (a),
KEY(v)
);
INSERTINTO t1 (a) VALUES ('2001-01-01 10:20:30');
SET time_zone='+10:00';
UPDATE t1 SET a='2000-01-01 10:20:30';
crashes with the following stack trace:
#3 0x00007ffff76eba26 in __assert_fail () from /lib64/libc.so.6
#4 0x0000000000f9eede in row_upd_sec_index_entry (node=0x7fff68070158,
thr=0x7fff68073798)
at /home/bar/maria-git/server.10.4.frac2/storage/innobase/row/row0upd.cc:2425
#5 0x0000000000f9f6c9 in row_upd_sec_step (node=0x7fff68070158,
thr=0x7fff68073798)
at /home/bar/maria-git/server.10.4.frac2/storage/innobase/row/row0upd.cc:2539
#6 0x0000000000fa256e in row_upd (node=0x7fff68070158, thr=0x7fff68073798)
at /home/bar/maria-git/server.10.4.frac2/storage/innobase/row/row0upd.cc:3315
#7 0x0000000000fa29d5 in row_upd_step (thr=0x7fff68073798)
at /home/bar/maria-git/server.10.4.frac2/storage/innobase/row/row0upd.cc:3430
#8 0x0000000000f4134b in row_update_for_mysql (prebuilt=0x7fff6806f678)
at /home/bar/maria-git/server.10.4.frac2/storage/innobase/row/row0mysql.cc:1889
#9 0x0000000000da6a72 in ha_innobase::update_row (this=0x7fff6806dd88,
at /home/bar/maria-git/server.10.4.frac2/sql/sql_update.cc:1047
This happens because DATETIME -> TIMESTAMP conversion depends in the @@time_zone system variable.
The same problem is repeatable with TIMESTAMP->DATETIME conversion:
SET time_zone='+00:00';
CREATEORREPLACETABLE t1 (
a TIMESTAMP,
v DATETIME GENERATED ALWAYS AS (a),
KEY(v)
);
INSERTINTO t1 (a) VALUES ('2001-01-01 10:20:30');
SET time_zone='+10:00';
UPDATE t1 SET a='2000-01-01 10:20:30';
Attachments
Issue Links
relates to
MDEV-18153Assertion `0' or Assertion `btr_validate_index(index, 0)' failed in row_upd_sec_index_entry or error code 126: Index is corrupted upon UPDATE with TIME_ROUND_FRACTIONAL
Closed
MDEV-18156Assertion `0' failed or `btr_validate_index(index, 0, false)' in row_upd_sec_index_entry or error code 126: Index is corrupted upon DELETE with PAD_CHAR_TO_FULL_LENGTH
Closed
MDEV-20423Assertion `0' failed or `btr_validate_index(index, 0, false)' in row_upd_sec_index_entry or error code 126: Index is corrupted upon DELETE with TIME_ROUND_FRACTIONAL
Closed
MDEV-34025Virtual columns do not check assignment cast validity
Closed
MDEV-20618Assertion `btr_validate_index(index, 0, false)' failed in row_upd_sec_index_entry
Closed
MDEV-20661Virtual fields are not recalculated on system fields value assignment
Closed
MDEV-20763Table corruption or Assertion `btr_validate_index(index, 0, false)' failed in row_upd_sec_index_entry with virtual column and EMPTY_STRING_IS_NULL SQL mode
Closed
MDEV-22061InnoDB: Assertion of missing row in sec index row_start upon REPLACE on a system-versioned table
Closed
MDEV-26405Assertion `0' failed in row_upd_sec_index_entry
Closed
MDEV-34037DATETIME <-> TIMESTAMP conversion in a virtual column corrups the table on @@time_zone change
In Review
MDEV-34038A division in an indexed virtual column corrupts the table on @@dev_precision_increment change
Open
MDEV-34039Some string functions in a virtual column lead the table corruption on @@max_allowed_packet change
2021-08-26 12:54:22 4 [ERROR] InnoDB: Record in index `v` of table `test`.`t1` was not found on update: TUPLE (info_bits=0, 2 fields): {[4]:O N(0x3A4FCD4E),[6] (0x000000000200)} at: COMPACT RECORD(info_bits=0, 1 fields): {[8]infimum (0x696E66696D756D00)}
The assertion is there so that we could catch index corruption more prominently in debug instrumented builds. Theoretically, we could apply the following patch to make the assertion failure go away:
+ ut_ad(index->has_virtual()); // tolerate some known bugs
#endif /* UNIV_DEBUG */
break;
case ROW_FOUND:
But wouldn’t that then lead to more bugs of this kind to be ignored?
Marko Mäkelä
added a comment - The assertion is there so that we could catch index corruption more prominently in debug instrumented builds. Theoretically, we could apply the following patch to make the assertion failure go away:
diff --git a/storage/innobase/row/row0upd.cc b/storage/innobase/row/row0upd.cc
index 4e2a9770aff..e0acd3df135 100644
--- a/storage/innobase/row/row0upd.cc
+++ b/storage/innobase/row/row0upd.cc
@@ -2112,7 +2112,7 @@ row_upd_sec_index_entry(
mtr_commit(&mtr);
mtr_start(&mtr);
ut_ad(btr_validate_index(index, 0) == DB_SUCCESS);
- ut_ad(0);
+ ut_ad(index->has_virtual()); // tolerate some known bugs
#endif /* UNIV_DEBUG */
break;
case ROW_FOUND:
But wouldn’t that then lead to more bugs of this kind to be ignored?
People
Marko Mäkelä
Alexander Barkov
Votes:
0Vote for this issue
Watchers:
5Start watching this issue
Dates
Created:
Updated:
Git Integration
Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.
{"report":{"fcp":1149,"ttfb":282.8999999910593,"pageVisibility":"visible","entityId":79006,"key":"jira.project.issue.view-issue","isInitial":true,"threshold":1000,"elementTimings":{},"userDeviceMemory":8,"userDeviceProcessors":32,"apdex":0.5,"journeyId":"b63fc8fe-3d75-4d4b-b0a4-4ff130ba994d","navigationType":0,"readyForUser":1233.7999999970198,"redirectCount":0,"resourceLoadedEnd":928.5,"resourceLoadedStart":288.3999999910593,"resourceTiming":[{"duration":345.90000000596046,"initiatorType":"link","name":"https://jira.mariadb.org/s/2c21342762a6a02add1c328bed317ffd-CDN/lu2cib/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/css/_super/batch.css","startTime":288.3999999910593,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":288.3999999910593,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":634.2999999970198,"responseStart":0,"secureConnectionStart":0},{"duration":346.20000000298023,"initiatorType":"link","name":"https://jira.mariadb.org/s/7ebd35e77e471bc30ff0eba799ebc151-CDN/lu2cib/820016/12ta74/2bf333562ca6724060a9d5f1535471f6/_/download/contextbatch/css/jira.browse.project,project.issue.navigator,jira.view.issue,jira.general,jira.global,atl.general,-_super/batch.css?agile_global_admin_condition=true&jag=true&jira.create.linked.issue=true&slack-enabled=true","startTime":288.59999999403954,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":288.59999999403954,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":634.7999999970198,"responseStart":0,"secureConnectionStart":0},{"duration":407.59999999403954,"initiatorType":"script","name":"https://jira.mariadb.org/s/0917945aaa57108d00c5076fea35e069-CDN/lu2cib/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/js/_super/batch.js?locale=en","startTime":288.79999999701977,"connectEnd":288.79999999701977,"connectStart":288.79999999701977,"domainLookupEnd":288.79999999701977,"domainLookupStart":288.79999999701977,"fetchStart":288.79999999701977,"redirectEnd":0,"redirectStart":0,"requestStart":639,"responseEnd":696.3999999910593,"responseStart":657.5999999940395,"secureConnectionStart":288.79999999701977},{"duration":415.5,"initiatorType":"script","name":"https://jira.mariadb.org/s/2d8175ec2fa4c816e8023260bd8c1786-CDN/lu2cib/820016/12ta74/2bf333562ca6724060a9d5f1535471f6/_/download/contextbatch/js/jira.browse.project,project.issue.navigator,jira.view.issue,jira.general,jira.global,atl.general,-_super/batch.js?agile_global_admin_condition=true&jag=true&jira.create.linked.issue=true&locale=en&slack-enabled=true","startTime":288.8999999910593,"connectEnd":288.8999999910593,"connectStart":288.8999999910593,"domainLookupEnd":288.8999999910593,"domainLookupStart":288.8999999910593,"fetchStart":288.8999999910593,"redirectEnd":0,"redirectStart":0,"requestStart":639,"responseEnd":704.3999999910593,"responseStart":656.2000000029802,"secureConnectionStart":288.8999999910593},{"duration":365.79999999701977,"initiatorType":"script","name":"https://jira.mariadb.org/s/a9324d6758d385eb45c462685ad88f1d-CDN/lu2cib/820016/12ta74/c92c0caa9a024ae85b0ebdbed7fb4bd7/_/download/contextbatch/js/atl.global,-_super/batch.js?locale=en","startTime":289.09999999403954,"connectEnd":289.09999999403954,"connectStart":289.09999999403954,"domainLookupEnd":289.09999999403954,"domainLookupStart":289.09999999403954,"fetchStart":289.09999999403954,"redirectEnd":0,"redirectStart":0,"requestStart":639.5,"responseEnd":654.8999999910593,"responseStart":654.2000000029802,"secureConnectionStart":289.09999999403954},{"duration":366.3999999910593,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2cib/820016/12ta74/1.0/_/download/batch/jira.webresources:calendar-en/jira.webresources:calendar-en.js","startTime":289.20000000298023,"connectEnd":289.20000000298023,"connectStart":289.20000000298023,"domainLookupEnd":289.20000000298023,"domainLookupStart":289.20000000298023,"fetchStart":289.20000000298023,"redirectEnd":0,"redirectStart":0,"requestStart":640.7000000029802,"responseEnd":655.5999999940395,"responseStart":654.8999999910593,"secureConnectionStart":289.20000000298023},{"duration":366.80000001192093,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2cib/820016/12ta74/1.0/_/download/batch/jira.webresources:calendar-localisation-moment/jira.webresources:calendar-localisation-moment.js","startTime":289.3999999910593,"connectEnd":289.3999999910593,"connectStart":289.3999999910593,"domainLookupEnd":289.3999999910593,"domainLookupStart":289.3999999910593,"fetchStart":289.3999999910593,"redirectEnd":0,"redirectStart":0,"requestStart":641.5999999940395,"responseEnd":656.2000000029802,"responseStart":655.7000000029802,"secureConnectionStart":289.3999999910593},{"duration":352,"initiatorType":"link","name":"https://jira.mariadb.org/s/b04b06a02d1959df322d9cded3aeecc1-CDN/lu2cib/820016/12ta74/a2ff6aa845ffc9a1d22fe23d9ee791fc/_/download/contextbatch/css/jira.global.look-and-feel,-_super/batch.css","startTime":289.5,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":289.5,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":641.5,"responseStart":0,"secureConnectionStart":0},{"duration":394.59999999403954,"initiatorType":"script","name":"https://jira.mariadb.org/rest/api/1.0/shortcuts/820016/47140b6e0a9bc2e4913da06536125810/shortcuts.js?context=issuenavigation&context=issueaction","startTime":289.70000000298023,"connectEnd":289.70000000298023,"connectStart":289.70000000298023,"domainLookupEnd":289.70000000298023,"domainLookupStart":289.70000000298023,"fetchStart":289.70000000298023,"redirectEnd":0,"redirectStart":0,"requestStart":647.5999999940395,"responseEnd":684.2999999970198,"responseStart":683.3999999910593,"secureConnectionStart":289.70000000298023},{"duration":357.30000001192093,"initiatorType":"link","name":"https://jira.mariadb.org/s/3ac36323ba5e4eb0af2aa7ac7211b4bb-CDN/lu2cib/820016/12ta74/d176f0986478cc64f24226b3d20c140d/_/download/contextbatch/css/com.atlassian.jira.projects.sidebar.init,-_super,-project.issue.navigator,-jira.view.issue/batch.css?jira.create.linked.issue=true","startTime":289.8999999910593,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":289.8999999910593,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":647.2000000029802,"responseStart":0,"secureConnectionStart":0},{"duration":383.79999999701977,"initiatorType":"script","name":"https://jira.mariadb.org/s/5d5e8fe91fbc506585e83ea3b62ccc4b-CDN/lu2cib/820016/12ta74/d176f0986478cc64f24226b3d20c140d/_/download/contextbatch/js/com.atlassian.jira.projects.sidebar.init,-_super,-project.issue.navigator,-jira.view.issue/batch.js?jira.create.linked.issue=true&locale=en","startTime":290,"connectEnd":653.8999999910593,"connectStart":653.8999999910593,"domainLookupEnd":653.8999999910593,"domainLookupStart":653.8999999910593,"fetchStart":290,"redirectEnd":0,"redirectStart":0,"requestStart":657.2999999970198,"responseEnd":673.7999999970198,"responseStart":669.5999999940395,"secureConnectionStart":653.8999999910593},{"duration":490.5,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2cib/820016/12ta74/1.0/_/download/batch/jira.webresources:bigpipe-js/jira.webresources:bigpipe-js.js","startTime":307.09999999403954,"connectEnd":307.09999999403954,"connectStart":307.09999999403954,"domainLookupEnd":307.09999999403954,"domainLookupStart":307.09999999403954,"fetchStart":307.09999999403954,"redirectEnd":0,"redirectStart":0,"requestStart":781.0999999940395,"responseEnd":797.5999999940395,"responseStart":796.7000000029802,"secureConnectionStart":307.09999999403954},{"duration":615.9000000059605,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2cib/820016/12ta74/1.0/_/download/batch/jira.webresources:bigpipe-init/jira.webresources:bigpipe-init.js","startTime":312.59999999403954,"connectEnd":312.59999999403954,"connectStart":312.59999999403954,"domainLookupEnd":312.59999999403954,"domainLookupStart":312.59999999403954,"fetchStart":312.59999999403954,"redirectEnd":0,"redirectStart":0,"requestStart":917.3999999910593,"responseEnd":928.5,"responseStart":927.7000000029802,"secureConnectionStart":312.59999999403954},{"duration":223.3999999910593,"initiatorType":"xmlhttprequest","name":"https://jira.mariadb.org/rest/webResources/1.0/resources","startTime":899.7000000029802,"connectEnd":899.7000000029802,"connectStart":899.7000000029802,"domainLookupEnd":899.7000000029802,"domainLookupStart":899.7000000029802,"fetchStart":899.7000000029802,"redirectEnd":0,"redirectStart":0,"requestStart":1087.3999999910593,"responseEnd":1123.0999999940395,"responseStart":1122,"secureConnectionStart":899.7000000029802}],"fetchStart":0,"domainLookupStart":32,"domainLookupEnd":66,"connectStart":67,"connectEnd":90,"secureConnectionStart":75,"requestStart":90,"responseStart":283,"responseEnd":313,"domLoading":286,"domInteractive":1304,"domContentLoadedEventStart":1304,"domContentLoadedEventEnd":1344,"domComplete":1742,"loadEventStart":1742,"loadEventEnd":1742,"userAgent":"Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; ClaudeBot/1.0; +claudebot@anthropic.com)","marks":[{"name":"bigPipe.sidebar-id.start","time":1280.8999999910593},{"name":"bigPipe.sidebar-id.end","time":1281.7000000029802},{"name":"bigPipe.activity-panel-pipe-id.start","time":1281.8999999910593},{"name":"bigPipe.activity-panel-pipe-id.end","time":1284.2000000029802},{"name":"activityTabFullyLoaded","time":1366.5}],"measures":[],"correlationId":"d032ae102e4d4a","effectiveType":"4g","downlink":9.6,"rtt":0,"serverDuration":119,"dbReadsTimeInMs":21,"dbConnsTimeInMs":30,"applicationHash":"9d11dbea5f4be3d4cc21f03a88dd11d8c8687422","experiments":[]}}
also on 10.6:
2021-08-26 12:54:22 4 [ERROR] InnoDB: Record in index `v` of table `test`.`t1` was not found on update: TUPLE (info_bits=0, 2 fields): {[4]:O N(0x3A4FCD4E),[6] (0x000000000200)} at: COMPACT RECORD(info_bits=0, 1 fields): {[8]infimum (0x696E66696D756D00)}
mariadbd: /10.6/src/storage/innobase/row/row0upd.cc:2051: dberr_t row_upd_sec_index_entry(upd_node_t*, que_thr_t*): Assertion `0' failed.
210826 12:54:22 [ERROR] mysqld got signal 6 ;
Server version: 10.6.5-MariaDB-debug-log
linux/raise.c:51(__GI_raise)[0x7f7c31eb218b]
stdlib/abort.c:81(__GI_abort)[0x7f7c31e91859]
intl/loadmsgcat.c:509(get_sysdep_segment_value)[0x7f7c31e91729]
:0(__GI___assert_fail)[0x7f7c31ea2f36]
row/row0upd.cc:2055(row_upd_sec_index_entry(upd_node_t*, que_thr_t*))[0x55fc88ed6a25]
row/row0upd.cc:2200(row_upd_sec_step(upd_node_t*, que_thr_t*))[0x55fc88ed7a30]
row/row0upd.cc:2936(row_upd(upd_node_t*, que_thr_t*))[0x55fc88edccbc]
row/row0upd.cc:3051(row_upd_step(que_thr_t*))[0x55fc88edd7c1]
row/row0mysql.cc:1723(row_update_for_mysql(row_prebuilt_t*))[0x55fc88e329f1]
handler/ha_innodb.cc:8554(ha_innobase::update_row(unsigned char const*, unsigned char const*))[0x55fc88a8b2a0]
sql/handler.cc:7567(handler::ha_update_row(unsigned char const*, unsigned char const*))[0x55fc87fa981f]
sql/sql_update.cc:1081(mysql_update(THD*, TABLE_LIST*, List<Item>&, List<Item>&, Item*, unsigned int, st_order*, unsigned long long, bool, unsigned long long*, unsigned long long*))[0x55fc87a32dcd]
sql/sql_parse.cc:4408(mysql_execute_command(THD*, bool))[0x55fc8770ea4a]
sql/sql_parse.cc:8030(mysql_parse(THD*, char*, unsigned int, Parser_state*))[0x55fc8772841a]
sql/sql_parse.cc:1898(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool))[0x55fc876fe4c8]
sql/sql_parse.cc:1404(do_command(THD*, bool))[0x55fc876fb1ec]
sql/sql_connect.cc:1418(do_handle_one_connection(CONNECT*, bool))[0x55fc87b611fb]
sql/sql_connect.cc:1314(handle_one_connection)[0x55fc87b60a87]
perfschema/pfs.cc:2203(pfs_spawn_thread)[0x55fc8887f697]
nptl/pthread_create.c:478(start_thread)[0x7f7c323b9609]
x86_64/clone.S:97(__GI___clone)[0x7f7c31f8e293]
Query (0x62b0000a82a8): UPDATE t1 SET a='2000-01-01 10:20:30'