I had updated from MariaDB 10.1.38 to MariaDB 10.3.13 via WHM. In the process it updated to MariaDB 10.2.23 first. After the process ends, a single application has bugged. After dig a lot I discover what happen, but not why.
Run this code:
# SETUP
CREATETABLEIFNOT EXISTS `test` (
`id` INT(10) UNSIGNED NOTNULL,
PRIMARYKEY (`id`)
)
ENGINE=InnoDB;
# CRASHER CODE
SELECT test.id
FROM test
LEFTJOIN test AS join2
LEFTJOIN test AS join3
LEFTJOIN test AS join4
ONNULLAND
join4.id
ONFALSE
ONFALSE
LEFTJOIN test AS join1
ONFALSE;
# CLEAN
DROPTABLE `test`;
The original query is very long, so I tried to simplificate this test case to the best that as I could.
If you run this code the server will crash with the following error:
SQL Error (2013): Lost connection to MySQL server during query
I just run the server with: mysqld without any additional parameters.
I did it based on my original code that crashes, so please consider the following changes (I did it to simplificate, but crashes happen anyway):
The original code have five tables and is used at ON clauses to make sense. But set all ON to FALSE still will crashes;
The original code have only a single AS because the join1 and join2 tables are the same. All other tables are not aliases because are differents tables;
I did a lot of tests based on this simplified code that solves the crash, but will change the behaviour of query:
Dropping the PRIMARY_KEY from id will solve;
Dropping any JOIN or unwrapper it will solve;
Dropping NULL AND will solve;
Replacing NULL AND with NULL OR will solve;
Replacing join4.id to join3.id will solve;
Replacing NULL with any other value will solve;
Moving join1 over join2 will solve;
Moving join1 to inside of join2, join3 or join4 will solve;
Moving join2, join3 or join4 to inside of join1 will solve;
Using other engines than not InnoDB or MRG_MyISAM will solve;
Adding parentheses to JOIN will not solve;
Adding PRIMARY_KEY to id will not solve;
Swapping join4.id with NULL will not solve;
Replacing NULL with NOT NULL will not solve;
I test some previous versions, and the problems starts in MariaDB 10.3.11 and still affects MariaDB 10.3.12 and MariaDB 10.3.13 (current).
Actually, the problem seems to be related with the `NULL AND`. For some reason it crashes the server.
David Rodrigues
added a comment - - edited Maybe related to MDEV-18467 , and possibly fixed with https://github.com/MariaDB/server/commit/8f4de38f65ba89c6273c15c9adb50ab762d03f59 . Someone can confirm it for me?
Query (0x62b000000288): SELECT test.id FROM test LEFT JOIN test AS join2 LEFT JOIN test AS join3 LEFT JOIN test AS join4 ON NULL AND join4.id ON FALSE ON FALSE LEFT JOIN test AS join1 ON FALSE
Alice Sherepa
added a comment - Thanks a lot for the report and test case.
Reproducible on current 10.3-10.4, with InnoDB, not with MyIsam
10.3 7b42d892de6acc04490f5c
Version: '10.3.14-MariaDB-debug-log'
mysqld: /10.3/sql/sql_select.cc:8022: bool greedy_search(JOIN*, table_map, uint, uint, uint): Assertion `join->best_read < double(1.79769313486231570815e+308L)' failed.
190402 11:53:57 [ERROR] mysqld got signal 6 ;
linux/raise.c:54(__GI_raise)[0x7fc79fae802a]
stdlib/abort.c:91(__GI_abort)[0x7fc79fadebd7]
assert/assert.c:92(__assert_fail_base)[0x7fc79fadec82]
/10.3/sql/mysqld(+0x108d876)[0x555a8c8a8876]
/10.3/sql/mysqld(_Z11choose_planP4JOINy+0x532)[0x555a8c8a6b3f]
/10.3/sql/mysqld(+0x107a55b)[0x555a8c89555b]
sql/sql_select.cc:8024(greedy_search(JOIN*, unsigned long long, unsigned int, unsigned int, unsigned int))[0x555a8c877ad0]
sql/sql_select.cc:7592(choose_plan(JOIN*, unsigned long long))[0x555a8c873838]
sql/sql_select.cc:5085(make_join_statistics(JOIN*, List<TABLE_LIST>&, st_dynamic_array*))[0x555a8c88dae9]
sql/sql_select.cc:1888(JOIN::optimize_inner())[0x555a8c86907f]
sql/sql_select.cc:1451(JOIN::optimize())[0x555a8c7efdba]
sql/sql_select.cc:4225(mysql_select(THD*, TABLE_LIST*, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*))[0x555a8c7de913]
sql/sql_select.cc:385(handle_select(THD*, LEX*, select_result*, unsigned long))[0x555a8c7f873a]
sql/sql_parse.cc:6548(execute_sqlcom_select(THD*, TABLE_LIST*))[0x555a8c7d325b]
sql/sql_parse.cc:3821(mysql_execute_command(THD*))[0x555a8c7d03c1]
sql/sql_parse.cc:8091(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x555a8cb255ec]
sql/sql_parse.cc:1859(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x555a8cb24fc9]
sql/sql_parse.cc:1403(do_command(THD*))[0x555a8de58161]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x76ba)[0x7fc7a07236ba]
x86_64/clone.S:111(clone)[0x7fc79fbb841d]
Query (0x62b000000288): SELECT test.id FROM test LEFT JOIN test AS join2 LEFT JOIN test AS join3 LEFT JOIN test AS join4 ON NULL AND join4.id ON FALSE ON FALSE LEFT JOIN test AS join1 ON FALSE
People
Unassigned
David Rodrigues
Votes:
1Vote for this issue
Watchers:
6Start 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":908.7000000476837,"ttfb":219.90000009536743,"pageVisibility":"visible","entityId":74152,"key":"jira.project.issue.view-issue","isInitial":true,"threshold":1000,"elementTimings":{},"userDeviceMemory":8,"userDeviceProcessors":64,"apdex":0.5,"journeyId":"a6eadd94-d695-4037-ba9c-59f57cc2888b","navigationType":0,"readyForUser":1003.9000000953674,"redirectCount":0,"resourceLoadedEnd":599.1000001430511,"resourceLoadedStart":227.29999995231628,"resourceTiming":[{"duration":48,"initiatorType":"link","name":"https://jira.mariadb.org/s/2c21342762a6a02add1c328bed317ffd-CDN/lu2bsh/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/css/_super/batch.css","startTime":227.29999995231628,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":227.29999995231628,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":275.2999999523163,"responseStart":0,"secureConnectionStart":0},{"duration":48.59999990463257,"initiatorType":"link","name":"https://jira.mariadb.org/s/7ebd35e77e471bc30ff0eba799ebc151-CDN/lu2bsh/820016/12ta74/eb142f92e4bd16bd1ef8b08c1b9d5d56/_/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":227.60000014305115,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":227.60000014305115,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":276.2000000476837,"responseStart":0,"secureConnectionStart":0},{"duration":250.30000019073486,"initiatorType":"script","name":"https://jira.mariadb.org/s/b09d0d077992e4331b5f9ec0d3ec448c-CDN/lu2bsh/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/js/_super/batch.js?locale=en","startTime":227.79999995231628,"connectEnd":278.2999999523163,"connectStart":278.2999999523163,"domainLookupEnd":278.2999999523163,"domainLookupStart":278.2999999523163,"fetchStart":227.79999995231628,"redirectEnd":0,"redirectStart":0,"requestStart":278.7999999523163,"responseEnd":478.10000014305115,"responseStart":293.10000014305115,"secureConnectionStart":278.2999999523163},{"duration":371.30000019073486,"initiatorType":"script","name":"https://jira.mariadb.org/s/b47ab3df14096803b180217eb8482517-CDN/lu2bsh/820016/12ta74/eb142f92e4bd16bd1ef8b08c1b9d5d56/_/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":227.79999995231628,"connectEnd":227.79999995231628,"connectStart":227.79999995231628,"domainLookupEnd":227.79999995231628,"domainLookupStart":227.79999995231628,"fetchStart":227.79999995231628,"redirectEnd":0,"redirectStart":0,"requestStart":279.2999999523163,"responseEnd":599.1000001430511,"responseStart":298.90000009536743,"secureConnectionStart":227.79999995231628},{"duration":68.90000009536743,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2bsh/820016/12ta74/1.0/_/download/batch/jira.webresources:calendar-en/jira.webresources:calendar-en.js","startTime":228,"connectEnd":228,"connectStart":228,"domainLookupEnd":228,"domainLookupStart":228,"fetchStart":228,"redirectEnd":0,"redirectStart":0,"requestStart":281.5,"responseEnd":296.90000009536743,"responseStart":295.7999999523163,"secureConnectionStart":228},{"duration":78.20000004768372,"initiatorType":"script","name":"https://jira.mariadb.org/s/6c569cbf8087ab04e40d0bef98627457-CDN/lu2bsh/820016/12ta74/c92c0caa9a024ae85b0ebdbed7fb4bd7/_/download/contextbatch/js/atl.global,-_super/batch.js?locale=en","startTime":228,"connectEnd":228,"connectStart":228,"domainLookupEnd":228,"domainLookupStart":228,"fetchStart":228,"redirectEnd":0,"redirectStart":0,"requestStart":281,"responseEnd":306.2000000476837,"responseStart":305.2000000476837,"secureConnectionStart":228},{"duration":79,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2bsh/820016/12ta74/1.0/_/download/batch/jira.webresources:calendar-localisation-moment/jira.webresources:calendar-localisation-moment.js","startTime":228.20000004768372,"connectEnd":228.20000004768372,"connectStart":228.20000004768372,"domainLookupEnd":228.20000004768372,"domainLookupStart":228.20000004768372,"fetchStart":228.20000004768372,"redirectEnd":0,"redirectStart":0,"requestStart":282.40000009536743,"responseEnd":307.2000000476837,"responseStart":306.40000009536743,"secureConnectionStart":228.20000004768372},{"duration":53.60000014305115,"initiatorType":"link","name":"https://jira.mariadb.org/s/b04b06a02d1959df322d9cded3aeecc1-CDN/lu2bsh/820016/12ta74/a2ff6aa845ffc9a1d22fe23d9ee791fc/_/download/contextbatch/css/jira.global.look-and-feel,-_super/batch.css","startTime":228.29999995231628,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":228.29999995231628,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":281.90000009536743,"responseStart":0,"secureConnectionStart":0},{"duration":84.09999990463257,"initiatorType":"script","name":"https://jira.mariadb.org/rest/api/1.0/shortcuts/820016/47140b6e0a9bc2e4913da06536125810/shortcuts.js?context=issuenavigation&context=issueaction","startTime":228.40000009536743,"connectEnd":228.40000009536743,"connectStart":228.40000009536743,"domainLookupEnd":228.40000009536743,"domainLookupStart":228.40000009536743,"fetchStart":228.40000009536743,"redirectEnd":0,"redirectStart":0,"requestStart":286.2999999523163,"responseEnd":312.5,"responseStart":311,"secureConnectionStart":228.40000009536743},{"duration":55.10000014305115,"initiatorType":"link","name":"https://jira.mariadb.org/s/3ac36323ba5e4eb0af2aa7ac7211b4bb-CDN/lu2bsh/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":228.5,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":228.5,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":283.60000014305115,"responseStart":0,"secureConnectionStart":0},{"duration":85.5,"initiatorType":"script","name":"https://jira.mariadb.org/s/81b5d7c27af3ebc078cc4a36383678ba-CDN/lu2bsh/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":228.60000014305115,"connectEnd":228.60000014305115,"connectStart":228.60000014305115,"domainLookupEnd":228.60000014305115,"domainLookupStart":228.60000014305115,"fetchStart":228.60000014305115,"redirectEnd":0,"redirectStart":0,"requestStart":286.40000009536743,"responseEnd":314.10000014305115,"responseStart":312.90000009536743,"secureConnectionStart":228.60000014305115},{"duration":338,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2bsh/820016/12ta74/1.0/_/download/batch/jira.webresources:bigpipe-init/jira.webresources:bigpipe-init.js","startTime":232.60000014305115,"connectEnd":232.60000014305115,"connectStart":232.60000014305115,"domainLookupEnd":232.60000014305115,"domainLookupStart":232.60000014305115,"fetchStart":232.60000014305115,"redirectEnd":0,"redirectStart":0,"requestStart":349.60000014305115,"responseEnd":570.6000001430511,"responseStart":566.2999999523163,"secureConnectionStart":232.60000014305115},{"duration":338.2999999523163,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2bsh/820016/12ta74/1.0/_/download/batch/jira.webresources:bigpipe-js/jira.webresources:bigpipe-js.js","startTime":232.60000014305115,"connectEnd":232.60000014305115,"connectStart":232.60000014305115,"domainLookupEnd":232.60000014305115,"domainLookupStart":232.60000014305115,"fetchStart":232.60000014305115,"redirectEnd":0,"redirectStart":0,"requestStart":351.90000009536743,"responseEnd":570.9000000953674,"responseStart":566.7999999523163,"secureConnectionStart":232.60000014305115},{"duration":50.799999952316284,"initiatorType":"xmlhttprequest","name":"https://jira.mariadb.org/rest/webResources/1.0/resources","startTime":604.1000001430511,"connectEnd":604.1000001430511,"connectStart":604.1000001430511,"domainLookupEnd":604.1000001430511,"domainLookupStart":604.1000001430511,"fetchStart":604.1000001430511,"redirectEnd":0,"redirectStart":0,"requestStart":625.6000001430511,"responseEnd":654.9000000953674,"responseStart":654.4000000953674,"secureConnectionStart":604.1000001430511}],"fetchStart":0,"domainLookupStart":0,"domainLookupEnd":0,"connectStart":0,"connectEnd":0,"requestStart":59,"responseStart":220,"responseEnd":231,"domLoading":223,"domInteractive":1071,"domContentLoadedEventStart":1071,"domContentLoadedEventEnd":1122,"domComplete":1486,"loadEventStart":1486,"loadEventEnd":1486,"userAgent":"Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; ClaudeBot/1.0; +claudebot@anthropic.com)","marks":[{"name":"bigPipe.sidebar-id.start","time":1047.2000000476837},{"name":"bigPipe.sidebar-id.end","time":1048},{"name":"bigPipe.activity-panel-pipe-id.start","time":1048.1000001430511},{"name":"bigPipe.activity-panel-pipe-id.end","time":1050},{"name":"activityTabFullyLoaded","time":1142.2000000476837}],"measures":[],"correlationId":"8965d1b00d6946","effectiveType":"4g","downlink":9.1,"rtt":0,"serverDuration":96,"dbReadsTimeInMs":28,"dbConnsTimeInMs":35,"applicationHash":"9d11dbea5f4be3d4cc21f03a88dd11d8c8687422","experiments":[]}}
Bug
Major
Maybe related to
MDEV-18467, and possibly fixed with https://github.com/MariaDB/server/commit/8f4de38f65ba89c6273c15c9adb50ab762d03f59. Someone can confirm it for me?