Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.3.17, 10.3.23, 10.5.4, 10.2(EOL), 10.3(EOL), 10.4(EOL), 10.5
-
None
-
CPanel on linux, Windows x64
Description
I'm getting a server crash when I issue a very simple DELETE statement "DELETE FROM companies WHERE CompanyID=749".
I believe it might have something to do with an "ON DELETE SET NULL" foreign key relationship and some indexes of virtual columns in the child table... but unfortunately I was unable to reproduce the problem as a simple testcase; it seems to depend somehow on the data in my existing DB.
I can however reliably repeat the crash if I issue the exact same DELETE statement.
Since I was unable to determine the specific cause, I can't tell if this is related to an already-reported bug or not.
The tables are all InnoDB, and running mysql_upgrade did not affect the issue.
The log contained the following:
190821 4:08:56 [ERROR] mysqld got signal 11 ;
|
This could be because you hit a bug. It is also possible that this binary
|
or one of the libraries it was linked against is corrupt, improperly built,
|
or misconfigured. This error can also be caused by malfunctioning hardware.
|
|
|
To report this bug, see https://mariadb.com/kb/en/reporting-bugs
|
|
|
We will try our best to scrape up some info that will hopefully help
|
diagnose the problem, but since we have already crashed,
|
something is definitely wrong and this may fail.
|
|
|
Server version: 10.3.17-MariaDB
|
key_buffer_size=134217728
|
read_buffer_size=131072
|
max_used_connections=2
|
max_threads=153
|
thread_count=8
|
It is possible that mysqld could use up to
|
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 467422 K bytes of memory
|
Hope that's ok; if not, decrease some variables in the equation.
|
|
|
Thread pointer: 0x55a1b0836658
|
Attempting backtrace. You can use the following information to find out
|
where mysqld died. If you see no messages after this, something went
|
terribly wrong...
|
stack_bottom = 0x7f17c45a8d30 thread_stack 0x49000
|
/usr/sbin/mysqld(my_print_stacktrace+0x2e)[0x55a1adf3f93e]
|
/usr/sbin/mysqld(handle_fatal_signal+0x30f)[0x55a1ad9df2af]sigaction.c:0(__restore_rt)[0x7f17d4b355d0]
|
/usr/sbin/mysqld(+0xa174fd)[0x55a1adc6d4fd]
|
/usr/sbin/mysqld(+0xa17ddc)[0x55a1adc6dddc]
|
/usr/sbin/mysqld(+0xa18ff4)[0x55a1adc6eff4]
|
/usr/sbin/mysqld(+0x9ea141)[0x55a1adc40141]
|
/usr/sbin/mysqld(+0x9d35c2)[0x55a1adc295c2]
|
/usr/sbin/mysqld(+0xa12a78)[0x55a1adc68a78]
|
/usr/sbin/mysqld(+0xa18809)[0x55a1adc6e809]
|
/usr/sbin/mysqld(+0xa18ff4)[0x55a1adc6eff4]
|
/usr/sbin/mysqld(+0x9e9c0b)[0x55a1adc3fc0b]
|
/usr/sbin/mysqld(+0x926c1c)[0x55a1adb7cc1c]
|
/usr/sbin/mysqld(_ZN7handler13ha_delete_rowEPKh+0x19f)[0x55a1ad9eb01f]
|
/usr/sbin/mysqld(_Z12mysql_deleteP3THDP10TABLE_LISTP4ItemP10SQL_I_ListI8st_orderEyyP13select_result+0x14e8)[0x55a1adb1fa68]
|
/usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x25d1)[0x55a1ad803611]
|
/usr/sbin/mysqld(_Z11mysql_parseP3THDPcjP12Parser_statebb+0x22b)[0x55a1ad809c2b]
|
/usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcjbb+0x1c9f)[0x55a1ad80c80f]
|
/usr/sbin/mysqld(_Z10do_commandP3THD+0x13e)[0x55a1ad80d74e]
|
/usr/sbin/mysqld(_Z24do_handle_one_connectionP7CONNECT+0x221)[0x55a1ad8e0f61]
|
/usr/sbin/mysqld(handle_one_connection+0x3d)[0x55a1ad8e102d]
|
pthread_create.c:0(start_thread)[0x7f17d4b2ddd5]
|
/lib64/libc.so.6(clone+0x6d)[0x7f17d2ed002d]
|
|
|
Trying to get some variables.
|
Some pointers may be invalid and cause the dump to abort.
|
Query (0x55a1b08440d0): DELETE FROM companies WHERE CompanyID=749
|
Connection ID (thread ID): 40
|
Status: NOT_KILLED
|
|
|
Optimizer switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on
|
|
|
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
|
information that should help you find out what is causing the crash.
|
Writing a core file...
|
Working directory at /var/lib/mysql
|
Resource Limits:Limit Soft Limit Hard Limit Units
|
Max cpu time unlimited unlimited seconds
|
Max file size unlimited unlimited bytes
|
Max data size unlimited unlimited bytes
|
Max stack size 8388608 unlimited bytes
|
Max core file size 0 unlimited bytes
|
Max resident set unlimited unlimited bytes
|
Max processes 7263 7263 processes
|
Max open files 50000 50000 files
|
Max locked memory 65536 65536 bytes
|
Max address space unlimited unlimited bytes
|
Max file locks unlimited unlimited locks
|
Max pending signals 7263 7263 signals
|
Max msgqueue size 819200 819200 bytes
|
Max nice priority 0 0
|
Max realtime priority 0 0
|
Max realtime timeout unlimited unlimited us
|
Core pattern: core
|
Attachments
Issue Links
- relates to
-
-
- Closed
-
-
-
- Closed
-
Activity
| Field | Original Value | New Value |
|---|---|---|
| Labels | need_feedback |
| Description |
I'm getting a server crash when I issue a very simple DELETE statement "DELETE FROM companies WHERE CompanyID=749".
I believe it might have something to do with an "ON DELETE SET NULL" foreign key relationship and some indexes of virtual columns in the child table... but unfortunately I was unable to reproduce the problem as a simple testcase; it seems to depend somehow on the data in my existing DB. I can however reliably repeat the crash if I issue the exact same DELETE statement. Since I was unable to determine the specific cause, I can't tell if this is related to an already-reported bug or not. The tables are all InnoDB, and running mysql_upgrade did not affect the issue. The log contained the following: 190821 4:08:56 [ERROR] mysqld got signal 11 ; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. To report this bug, see https://mariadb.com/kb/en/reporting-bugs We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. Server version: 10.3.17-MariaDB key_buffer_size=134217728 read_buffer_size=131072 max_used_connections=2 max_threads=153 thread_count=8 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 467422 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. Thread pointer: 0x55a1b0836658 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... stack_bottom = 0x7f17c45a8d30 thread_stack 0x49000 /usr/sbin/mysqld(my_print_stacktrace+0x2e)[0x55a1adf3f93e] /usr/sbin/mysqld(handle_fatal_signal+0x30f)[0x55a1ad9df2af]sigaction.c:0(__restore_rt)[0x7f17d4b355d0] /usr/sbin/mysqld(+0xa174fd)[0x55a1adc6d4fd] /usr/sbin/mysqld(+0xa17ddc)[0x55a1adc6dddc] /usr/sbin/mysqld(+0xa18ff4)[0x55a1adc6eff4] /usr/sbin/mysqld(+0x9ea141)[0x55a1adc40141] /usr/sbin/mysqld(+0x9d35c2)[0x55a1adc295c2] /usr/sbin/mysqld(+0xa12a78)[0x55a1adc68a78] /usr/sbin/mysqld(+0xa18809)[0x55a1adc6e809] /usr/sbin/mysqld(+0xa18ff4)[0x55a1adc6eff4] /usr/sbin/mysqld(+0x9e9c0b)[0x55a1adc3fc0b] /usr/sbin/mysqld(+0x926c1c)[0x55a1adb7cc1c] /usr/sbin/mysqld(_ZN7handler13ha_delete_rowEPKh+0x19f)[0x55a1ad9eb01f] /usr/sbin/mysqld(_Z12mysql_deleteP3THDP10TABLE_LISTP4ItemP10SQL_I_ListI8st_orderEyyP13select_result+0x14e8)[0x55a1adb1fa68] /usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x25d1)[0x55a1ad803611] /usr/sbin/mysqld(_Z11mysql_parseP3THDPcjP12Parser_statebb+0x22b)[0x55a1ad809c2b] /usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcjbb+0x1c9f)[0x55a1ad80c80f] /usr/sbin/mysqld(_Z10do_commandP3THD+0x13e)[0x55a1ad80d74e] /usr/sbin/mysqld(_Z24do_handle_one_connectionP7CONNECT+0x221)[0x55a1ad8e0f61] /usr/sbin/mysqld(handle_one_connection+0x3d)[0x55a1ad8e102d] pthread_create.c:0(start_thread)[0x7f17d4b2ddd5] /lib64/libc.so.6(clone+0x6d)[0x7f17d2ed002d] Trying to get some variables. Some pointers may be invalid and cause the dump to abort. Query (0x55a1b08440d0): DELETE FROM companies WHERE CompanyID=749 Connection ID (thread ID): 40 Status: NOT_KILLED Optimizer switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains information that should help you find out what is causing the crash. Writing a core file... Working directory at /var/lib/mysql Resource Limits:Limit Soft Limit Hard Limit Units Max cpu time unlimited unlimited seconds Max file size unlimited unlimited bytes Max data size unlimited unlimited bytes Max stack size 8388608 unlimited bytes Max core file size 0 unlimited bytes Max resident set unlimited unlimited bytes Max processes 7263 7263 processes Max open files 50000 50000 files Max locked memory 65536 65536 bytes Max address space unlimited unlimited bytes Max file locks unlimited unlimited locks Max pending signals 7263 7263 signals Max msgqueue size 819200 819200 bytes Max nice priority 0 0 Max realtime priority 0 0 Max realtime timeout unlimited unlimited us Core pattern: core |
I'm getting a server crash when I issue a very simple DELETE statement "DELETE FROM companies WHERE CompanyID=749".
I believe it might have something to do with an "ON DELETE SET NULL" foreign key relationship and some indexes of virtual columns in the child table... but unfortunately I was unable to reproduce the problem as a simple testcase; it seems to depend somehow on the data in my existing DB. I can however reliably repeat the crash if I issue the exact same DELETE statement. Since I was unable to determine the specific cause, I can't tell if this is related to an already-reported bug or not. The tables are all InnoDB, and running mysql_upgrade did not affect the issue. The log contained the following: {noformat} 190821 4:08:56 [ERROR] mysqld got signal 11 ; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. To report this bug, see https://mariadb.com/kb/en/reporting-bugs We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. Server version: 10.3.17-MariaDB key_buffer_size=134217728 read_buffer_size=131072 max_used_connections=2 max_threads=153 thread_count=8 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 467422 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. Thread pointer: 0x55a1b0836658 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... stack_bottom = 0x7f17c45a8d30 thread_stack 0x49000 /usr/sbin/mysqld(my_print_stacktrace+0x2e)[0x55a1adf3f93e] /usr/sbin/mysqld(handle_fatal_signal+0x30f)[0x55a1ad9df2af]sigaction.c:0(__restore_rt)[0x7f17d4b355d0] /usr/sbin/mysqld(+0xa174fd)[0x55a1adc6d4fd] /usr/sbin/mysqld(+0xa17ddc)[0x55a1adc6dddc] /usr/sbin/mysqld(+0xa18ff4)[0x55a1adc6eff4] /usr/sbin/mysqld(+0x9ea141)[0x55a1adc40141] /usr/sbin/mysqld(+0x9d35c2)[0x55a1adc295c2] /usr/sbin/mysqld(+0xa12a78)[0x55a1adc68a78] /usr/sbin/mysqld(+0xa18809)[0x55a1adc6e809] /usr/sbin/mysqld(+0xa18ff4)[0x55a1adc6eff4] /usr/sbin/mysqld(+0x9e9c0b)[0x55a1adc3fc0b] /usr/sbin/mysqld(+0x926c1c)[0x55a1adb7cc1c] /usr/sbin/mysqld(_ZN7handler13ha_delete_rowEPKh+0x19f)[0x55a1ad9eb01f] /usr/sbin/mysqld(_Z12mysql_deleteP3THDP10TABLE_LISTP4ItemP10SQL_I_ListI8st_orderEyyP13select_result+0x14e8)[0x55a1adb1fa68] /usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x25d1)[0x55a1ad803611] /usr/sbin/mysqld(_Z11mysql_parseP3THDPcjP12Parser_statebb+0x22b)[0x55a1ad809c2b] /usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcjbb+0x1c9f)[0x55a1ad80c80f] /usr/sbin/mysqld(_Z10do_commandP3THD+0x13e)[0x55a1ad80d74e] /usr/sbin/mysqld(_Z24do_handle_one_connectionP7CONNECT+0x221)[0x55a1ad8e0f61] /usr/sbin/mysqld(handle_one_connection+0x3d)[0x55a1ad8e102d] pthread_create.c:0(start_thread)[0x7f17d4b2ddd5] /lib64/libc.so.6(clone+0x6d)[0x7f17d2ed002d] Trying to get some variables. Some pointers may be invalid and cause the dump to abort. Query (0x55a1b08440d0): DELETE FROM companies WHERE CompanyID=749 Connection ID (thread ID): 40 Status: NOT_KILLED Optimizer switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains information that should help you find out what is causing the crash. Writing a core file... Working directory at /var/lib/mysql Resource Limits:Limit Soft Limit Hard Limit Units Max cpu time unlimited unlimited seconds Max file size unlimited unlimited bytes Max data size unlimited unlimited bytes Max stack size 8388608 unlimited bytes Max core file size 0 unlimited bytes Max resident set unlimited unlimited bytes Max processes 7263 7263 processes Max open files 50000 50000 files Max locked memory 65536 65536 bytes Max address space unlimited unlimited bytes Max file locks unlimited unlimited locks Max pending signals 7263 7263 signals Max msgqueue size 819200 819200 bytes Max nice priority 0 0 Max realtime priority 0 0 Max realtime timeout unlimited unlimited us Core pattern: core {noformat} |
Here is the structure of the two relevant tables. `companies` is the parent table.
Since these are the real table structures and not a simplified test case, much of it should be irrelevant to the bug:
----------------------------------------------------------------------------------
[EDIT: Removed now that there's a simplified test case]
There's something else that I forgot to mention, that's quite peculiar.
Initially when this occurred, it happened for a specific `companies` row that had a single child-row in `requests`.
I was initially able to delete the `companies` row (what I'll call the "company") by first setting the CompanyID foreign key in the `requests` table row (the "request") to NULL manually first. (This is what should happen anyway on delete, due to the "ON DELETE SET NULL" property of constraint `requests_ibfk_5`).
Later, investigating the issue, I was able to reproduce the crash (every time) by issuing the following SQL:
INSERT INTO companies (ShortName, <other fields without defaults>) VALUES ('bugtest', <'' or 0 values>) ; /* create a new, "blank" company */
SELECT @cID:=CompanyID FROM companies WHERE ShortName='bugtest';
UPDATE requests SET CompanyID=@cID WHERE RequestID=124213; /* attach it to the previous company's single child request */
DELETE FROM companies WHERE CompanyID=@cID; /* also crashes if I insert the CompanyID as a literal rather than using @cID */
The DELETE statement would always crash MariaDB.
(Importantly, this tells me that no other child tables referencing `companies` were involved, since the new, "blank" company I created doesn't
have any such child records. `requests` does have child-tables of its own, but they are all keyed to `RequestID` and should not be affected by the "ON DELETE SET NULL" setting CompanyID to NULL.)
Here's the peculiar thing:
After MariaDB automatically restarts, I can confirm (via a couple of SELECTS) that the new company I created ('bugtest') still exists, and that request #124213 is still linked to it by foreign key. But now, issuing the same DELETE statement doesn't cause a crash. i.e. immediately after the server restarts, the bug does not manifest, even though nothing about the two tables is apparently different.
Of course, if I then repeat the procedure (re-create the company, re-link it to the request, and attempt to delete it) the crash happens again...
I can't imagine why that would be!
(NOTE: I'm not sure if it's relevant or not, but this test procedure was carried out using cPanel's phpMyAdmin interface, rather than MariaDB's console interface, because the database exists on a hosted server managed via cPanel/WHM. BTW after I first realised MariaDB was crashing, I reinstalled it via the WHM interface just in case it was corrupted somehow, and rebooted the server... but obviously that didn't fix the problem).
| Labels | need_feedback |
I am experiencing the exact same crash, but on 10.4.7 and with a "ON DELETE CASCADE" constraint. Also, the automatic reboot does not fix the crashing for me.
However, I think I have tracked the issue somewhat down:
https://github.com/MariaDB/server/blob/c1a2778e689030fcbb6cdc93d8c1f1d85024ed28/storage/innobase/row/row0upd.cc#L2121
This call to innobase_allocate_row_for_vcol fails and returns true. Hence record points to NULL and this is responsible for the crash.
The reason innobase_allocate_row_for_vcol fails, seems to be because innodb_find_table_for_vc (https://github.com/MariaDB/server/blob/c1a2778e689030fcbb6cdc93d8c1f1d85024ed28/storage/innobase/handler/ha_innodb.cc#L20471) isn't finding the table.
After some further investigation removing the virtual column from my child table definition resolved the crash. So this seems to be something related to virtual columns.
| Component/s | Storage Engine - InnoDB [ 10129 ] | |
| Component/s | Virtual Columns [ 10803 ] | |
| Fix Version/s | 10.3 [ 22126 ] | |
| Assignee | Marko Mäkelä [ marko ] |
| Assignee | Marko Mäkelä [ marko ] | Nikita Malyavin [ nikitamalyavin ] |
| Status | Open [ 1 ] | In Progress [ 3 ] |
| Attachment | virt.test [ 53079 ] |
Hi Dean T! I have managed to create the tables you pasted (after few fixes), but unfortunately can't see how to reproduce the issue yet 
I tried to write a mysqltest script to reproduce it, and attached it. Can you tell if I am missing something?
Also, could you send me a core dump of mariadb server? This can be quite helpful
| Comment | [ [~Dean T] hi! What's your case? Did you find out the straight way to reproduce it? ] |
galli-leo hi! What's your case? Did you find out the straight way to reproduce it?
Hi Nikita.
Sorry, as I said initially the bug appears to depend on the existing data I have in my tables, not just the created/inserted/deleted rows.
i.e. it probably has something to do with the execution plan chosen based on index statistics; some execution plans trigger this bug, others do not.
I can't post the actual data as there's private information in it (plus it's very large), but I've been meaning to try to craft a proper, small test case that triggers the bug, using made-up or random data.
I'll see if I can get that done for you in the next few days.
| Status | In Progress [ 3 ] | Stalled [ 10000 ] |
Dean T I am glad you are still interested in it! Please, try to provide core dump at least. it can be gathered with coredumpctl quite easily on modern linux platforms with systemd
| Attachment | testcase.zip [ 53092 ] |
| Affects Version/s | 10.5.4 [ 24264 ] | |
| Affects Version/s | 10.3.23 [ 24222 ] |
| Environment | CPanel on linux | CPanel on linux, Windows x64 |
Hi Nikita.
I've created a simplified test case using only those columns necessary to reproduce the bug, and attached it as testcase.zip (containing the file testcase.sql).
Please create a new database, then run testcase.sql on it to populate the two tables.
Then execute the following SQL *twice*:
INSERT INTO parent (ID) VALUES (1000);
UPDATE child SET ParentID=1000 WHERE ID=124213;
DELETE FROM parent WHERE ID=1000;
On the second execution, the DELETE will fail, and will crash MariaDB. I've confirmed this occurs on Windows x64, for both versions 10.3.23 and 10.5.4.
(The linux+cPanel version I originally discovered the bug on is running a live website: The owners wouldn't be happy if I crashed it repeatedly for testing purposes! So, I tested on my own Windows x64 PC.)
Please let me know whether or not you can reproduce this crash or not.
Also, once you fix it, you'd better confirm that it fixes Leonardo Galli's issue as well: He said it was "the exact same crash", and he's probably right, but it wouldn't do to discount the possibility that his was a different (albeit similar) bug altogether.
| Attachment | error log.txt [ 53098 ] |
I've also attached MariaDB v10.5.4's error log for when it crashed, as "error log.txt".
Nikita, if you still need me to attach a core dump, can you tell me where to find it on Windows? Thanks.
Dean T thank you! I will check your test case soon. There'll be no need for a core dump (btw don't know whether windows can even do it) in case of successful reproduction
Thanks a lot! I repeated with the provided test case on 10.2-10.5:
|
10.2 c710c450e3a5654244 |
Version: '10.2.34-MariaDB-debug-log'
|
2020-08-31 8:21:18 140502294345472 [ERROR] InnoDB: Record in index `ParentID` of table `test`.`child` was not found on update: TUPLE (info_bits=0, 3 fields): {NULL,[1] (0x01),[4] 5(0x0001E535)} at: COMPACT RECORD(info_bits=0, 3 fields): {NULL,[1] (0x01),[4] ~(0x0001E47E)}
|
mysqld: /d1/git/10.2/storage/innobase/row/row0upd.cc:2437: dberr_t row_upd_sec_index_entry(upd_node_t*, que_thr_t*): Assertion `0' failed.
|
200831 8:21:18 [ERROR] mysqld got signal 6 ;
|
Server version: 10.2.34-MariaDB-debug-log
|
|
|
linux/raise.c:51(__GI_raise)[0x7fc94316f7bb]
|
stdlib/abort.c:81(__GI_abort)[0x7fc94315a535]
|
intl/loadmsgcat.c:1177(_nl_load_domain)[0x7fc94315a40f]
|
/lib/x86_64-linux-gnu/libc.so.6(+0x30102)[0x7fc943168102]
|
row/row0upd.cc:2441(row_upd_sec_index_entry(upd_node_t*, que_thr_t*))[0x55d102934bcc]
|
row/row0upd.cc:2560(row_upd_sec_step(upd_node_t*, que_thr_t*))[0x55d1029353f3]
|
row/row0upd.cc:3316(row_upd(upd_node_t*, que_thr_t*))[0x55d102937646]
|
row/row0upd.cc:3433(row_upd_step(que_thr_t*))[0x55d10293799a]
|
row/row0mysql.cc:1827(row_update_for_mysql(row_prebuilt_t*))[0x55d1028e2086]
|
handler/ha_innodb.cc:8942(ha_innobase::update_row(unsigned char const*, unsigned char*))[0x55d1027a6c47]
|
sql/handler.cc:6140(handler::ha_update_row(unsigned char const*, unsigned char*))[0x55d1025b579a]
|
sql/sql_update.cc:817(mysql_update(THD*, TABLE_LIST*, List<Item>&, List<Item>&, Item*, unsigned int, st_order*, unsigned long long, enum_duplicates, bool, unsigned long long*, unsigned long long*))[0x55d10242051b]
|
sql/sql_parse.cc:3998(mysql_execute_command(THD*))[0x55d102330f6c]
|
sql/sql_parse.cc:7733(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x55d10233cd25]
|
sql/sql_parse.cc:1826(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x55d10232b13f]
|
sql/sql_parse.cc:1377(do_command(THD*))[0x55d102329bc4]
|
sql/sql_connect.cc:1336(do_handle_one_connection(CONNECT*))[0x55d10247e8c4]
|
sql/sql_connect.cc:1242(handle_one_connection)[0x55d10247e62f]
|
perfschema/pfs.cc:1871(pfs_spawn_thread)[0x55d102c800ca]
|
nptl/pthread_create.c:487(start_thread)[0x7fc9438adfa3]
|
x86_64/clone.S:97(clone)[0x7fc9432314cf]
|
|
|
Trying to get some variables.
|
Some pointers may be invalid and cause the dump to abort.
|
Query (0x7fc8ec0127b8): UPDATE child SET ParentID=1000 WHERE ID=124213
|
|
10.3 6a042281bdbfe91cc39e1f6e02 |
Version: '10.3.25-MariaDB-debug-log'
|
2020-08-31 08:27:03 0x7f94572a2700 InnoDB: Assertion failure in file /d1/git/10.3/storage/innobase/row/row0ins.cc line 221
|
InnoDB: Failing assertion: !cursor->index->is_committed()
|
InnoDB: We intentionally generate a memory trap.
|
InnoDB: Submit a detailed bug report to https://jira.mariadb.org/
|
InnoDB: If you get repeated assertion failures or crashes, even
|
InnoDB: immediately after the mysqld startup, there may be
|
InnoDB: corruption in the InnoDB tablespace. Please refer to
|
InnoDB: https://mariadb.com/kb/en/library/innodb-recovery-modes/
|
InnoDB: about forcing recovery.
|
200831 8:27:03 [ERROR] mysqld got signal 6 ;
|
|
|
linux/raise.c:51(__GI_raise)[0x7f946213c7bb]
|
stdlib/abort.c:81(__GI_abort)[0x7f9462127535]
|
/d1/git/10.3/sql/mysqld(+0xeba398)[0x56510a5f4398]
|
ut/ut0new.cc:95(ut_new_boot())[0x56510a4da4c2]
|
row/row0ins.cc:222(row_ins_sec_index_entry_by_modify(unsigned long, unsigned long, btr_cur_t*, unsigned short**, mem_block_info_t*, mem_block_info_t*, dtuple_t const*, que_thr_t*, mtr_t*))[0x56510a4e28e6]
|
row/row0ins.cc:3079(row_ins_sec_index_entry_low(unsigned long, unsigned long, dict_index_t*, mem_block_info_t*, mem_block_info_t*, dtuple_t*, unsigned long, que_thr_t*))[0x56510a4e31f9]
|
row/row0ins.cc:3276(row_ins_sec_index_entry(dict_index_t*, dtuple_t*, que_thr_t*, bool))[0x56510a574ffd]
|
row/row0upd.cc:2536(row_upd_sec_index_entry(upd_node_t*, que_thr_t*))[0x56510a57526e]
|
row/row0upd.cc:2563(row_upd_sec_step(upd_node_t*, que_thr_t*))[0x56510a577ff7]
|
row/row0upd.cc:3332(row_upd(upd_node_t*, que_thr_t*))[0x56510a578444]
|
row/row0upd.cc:3447(row_upd_step(que_thr_t*))[0x56510a50d2e8]
|
row/row0mysql.cc:2223(row_update_cascade_for_mysql(que_thr_t*, upd_node_t*, dict_table_t*))[0x56510a4ddba2]
|
row/row0ins.cc:1396(row_ins_foreign_check_on_constraint(que_thr_t*, dict_foreign_t*, btr_pcur_t*, dtuple_t*, mtr_t*))[0x56510a4dec67]
|
row/row0ins.cc:1819(row_ins_check_foreign_constraint(unsigned long, dict_foreign_t*, dict_table_t*, dtuple_t*, que_thr_t*))[0x56510a56edb6]
|
row/row0upd.cc:295(row_upd_check_references_constraints(upd_node_t*, btr_pcur_t*, dict_table_t*, dict_index_t*, unsigned short*, que_thr_t*, mtr_t*))[0x56510a576e5b]
|
row/row0upd.cc:3004(row_upd_del_mark_clust_rec(upd_node_t*, dict_index_t*, unsigned short*, que_thr_t*, unsigned long, bool, mtr_t*))[0x56510a57789d]
|
row/row0upd.cc:3174(row_upd_clust_step(upd_node_t*, que_thr_t*))[0x56510a577e76]
|
row/row0upd.cc:3303(row_upd(upd_node_t*, que_thr_t*))[0x56510a578444]
|
row/row0upd.cc:3447(row_upd_step(que_thr_t*))[0x56510a50c3e6]
|
row/row0mysql.cc:1849(row_update_for_mysql(row_prebuilt_t*))[0x56510a37800b]
|
handler/ha_innodb.cc:8907(ha_innobase::delete_row(unsigned char const*))[0x56510a15849e]
|
sql/handler.cc:6554(handler::ha_delete_row(unsigned char const*))[0x56510a2fd86d]
|
sql/sql_delete.cc:245(TABLE::delete_row())[0x56510a2fa98d]
|
sql/sql_delete.cc:720(mysql_delete(THD*, TABLE_LIST*, Item*, SQL_I_List<st_order>*, unsigned long long, unsigned long long, select_result*))[0x565109e30d75]
|
sql/sql_parse.cc:4649(mysql_execute_command(THD*))[0x565109e3ba88]
|
sql/sql_parse.cc:7810(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x565109e283ca]
|
sql/sql_parse.cc:1850(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x565109e26ced]
|
sql/sql_parse.cc:1393(do_command(THD*))[0x565109f9ec48]
|
sql/sql_connect.cc:1403(do_handle_one_connection(CONNECT*))[0x565109f9e9aa]
|
sql/sql_connect.cc:1309(handle_one_connection)[0x56510a93aa05]
|
nptl/pthread_create.c:487(start_thread)[0x7f946287afa3]
|
x86_64/clone.S:97(clone)[0x7f94621fe4cf]
|
|
|
Query (0x7f940c012a78): DELETE FROM parent WHERE ID=1000
|
| Affects Version/s | 10.2 [ 14601 ] | |
| Affects Version/s | 10.3 [ 22126 ] | |
| Affects Version/s | 10.4 [ 22408 ] | |
| Affects Version/s | 10.5 [ 23123 ] |
| Fix Version/s | 10.2 [ 14601 ] | |
| Fix Version/s | 10.4 [ 22408 ] | |
| Fix Version/s | 10.5 [ 23123 ] |
| Link |
This issue relates to |
| Link |
This issue relates to |
Till the last release set, we were tracking the virtual column variation of this assertion failure under MDEV-19338. Apparently it hasn't been fully fixed.
| Status | Stalled [ 10000 ] | In Progress [ 3 ] |
Analysis report
First of all I was successful at reducing the test case to ~25 lines. It turned out that one record for each table is enough:
--source include/have_innodb.inc
|
|
|
set default_storage_engine=innodb; |
|
|
CREATE TABLE parent |
(
|
ID int unsigned NOT NULL, |
PRIMARY KEY (ID) |
);
|
|
|
CREATE TABLE child |
(
|
ID int unsigned NOT NULL, |
ParentID int unsigned NULL, |
Value int unsigned NOT NULL DEFAULT 0xbababebe, |
Flag int unsigned AS (Value) VIRTUAL, |
PRIMARY KEY (ID), |
KEY (ParentID,Flag), |
FOREIGN KEY (ParentID) REFERENCES parent (ID) ON DELETE SET NULL ON UPDATE CASCADE |
);
|
|
|
INSERT INTO parent (ID) VALUES (100); |
INSERT INTO child (ID,ParentID,Value) VALUES (123123,100,1); |
#UPDATE child set ParentID= NULL WHERE ParentID = 100; |
DELETE FROM parent WHERE ID=100; |
select * from child; |
INSERT INTO parent (ID) VALUES (100); |
UPDATE child SET ParentID=100 WHERE ID=123123; |
This is the minimal test case to fail.
To analyze the problem I used good/bad strategy. First I tried to compare the behaviors without the virtual column, by substituting Flag type with INT UNSIGNED DEFAULT (123).
Tracing the data changes has only shown that the value returned from the b-tree index contains Flag=NULL in BAD case (so the match didn't happen, and therefore the assertion has failed) and Flag=123 in GOOD case.
Second approach was to compare the cascade SET NULL update behavior with UPDATE child SET Parent=NULL and to see what the difference is there.
Hopefully, both functions make an update with row_upd call.
I have placed the breakpoints in some key places when data transitions happen. We could expect that both cases would behave identically during update, and that gets confirmed by stack traces.
GOOD:
#0 rec_copy (buf=0x611000071188, rec=0x7f5db859007e "", offsets=0x7f5da5d45d40) at ../storage/innobase/include/rem0rec.ic:1563
|
#1 0x0000559ba07ef1cd in row_build_low (type=1, index=0x617000043708, rec=0x7f5db859007e "", offsets=0x7f5da5d45d40, col_table=0x0, add_cols=0x0, add_v=0x0, col_map=0x0, ext=0x6210000a5e50, heap=0x611000071100) at ../storage/innobase/row/row0row.cc:426
|
#2 0x0000559ba07eeb8a in row_build (type=1, index=0x617000043708, rec=0x7f5db859007e "", offsets=0x7f5da5d45d40, col_table=0x0, add_cols=0x0, col_map=0x0, ext=0x6210000a5e50, heap=0x611000071100) at ../storage/innobase/row/row0row.cc:602
|
#3 0x0000559ba088b457 in row_upd_store_row (node=0x6210000a5d70, thd=0x62a0000ba208, mysql_table=0x61e00003cc88) at ../storage/innobase/row/row0upd.cc:2236
|
#4 0x0000559ba0887478 in row_upd_clust_step (node=0x6210000a5d70, thr=0x6250000f0188) at ../storage/innobase/row/row0upd.cc:3190
|
#5 0x0000559ba0881b65 in row_upd (node=0x6210000a5d70, thr=0x6250000f0188) at ../storage/innobase/row/row0upd.cc:3286
|
#6 0x0000559ba0880eb1 in row_upd_step (thr=0x6250000f0188) at ../storage/innobase/row/row0upd.cc:3432
|
#7 0x0000559ba0780c01 in row_update_for_mysql (prebuilt=0x6210000a5188) at ../storage/innobase/row/row0mysql.cc:1825
|
#8 0x0000559ba03a53bc in ha_innobase::update_row (this=0x61c0000638a8, old_row=0x6190000da2d0 "\371\363\340\001", new_row=0x6190000da2b8 "\373\363\340\001") at ../storage/innobase/handler/ha_innodb.cc:8943
|
#9 0x0000559b9fcb023c in handler::ha_update_row (this=0x61c0000638a8, old_data=0x6190000da2d0 "\371\363\340\001", new_data=0x6190000da2b8 "\373\363\340\001") at ../sql/handler.cc:6140
|
#10 0x0000559b9f74a156 in mysql_update (thd=0x62a0000ba208, table_list=0x62b000000370, fields=..., values=..., conds=0x62b000000d60, order_num=0, order=0x0, limit=18446744073709551615, handle_duplicates=DUP_ERROR, ignore=false, found_return=0x7f5da5d4e5a0, updated_return=0x7f5da5d4e5c0) at ../sql/sql_update.cc:817
|
#11 0x0000559b9f3c0e4b in mysql_execute_command (thd=0x62a0000ba208) at ../sql/sql_parse.cc:3998
|
#12 0x0000559b9f3afce6 in mysql_parse (thd=0x62a0000ba208, rawbuf=0x62b000000228 "UPDATE child set ParentID= NULL WHERE ParentID = 100", length=52, parser_state=0x7f5da5d50b20, is_com_multi=false, is_next_command=false) at ../sql/sql_parse.cc:7733
|
BAD:
#0 rec_copy (buf=0x61600005b410, rec=0x7fd555c7407e "", offsets=0x7fd543430b80) at ../storage/innobase/include/rem0rec.ic:1563
|
#1 0x000055e3292981cd in row_build_low (type=1, index=0x617000043708, rec=0x7fd555c7407e "", offsets=0x7fd543430b80, col_table=0x0, add_cols=0x0, add_v=0x0, col_map=0x0, ext=0x61600005b0e8, heap=0x611000071380) at ../storage/innobase/row/row0row.cc:426
|
#2 0x000055e329297b8a in row_build (type=1, index=0x617000043708, rec=0x7fd555c7407e "", offsets=0x7fd543430b80, col_table=0x0, add_cols=0x0, col_map=0x0, ext=0x61600005b0e8, heap=0x611000071380) at ../storage/innobase/row/row0row.cc:602
|
#3 0x000055e329334457 in row_upd_store_row (node=0x61600005b008, thd=0x62a0000ba208, mysql_table=0x0) at ../storage/innobase/row/row0upd.cc:2236
|
#4 0x000055e329330478 in row_upd_clust_step (node=0x61600005b008, thr=0x6240001dc188) at ../storage/innobase/row/row0upd.cc:3190
|
#5 0x000055e32932ab65 in row_upd (node=0x61600005b008, thr=0x6240001dc188) at ../storage/innobase/row/row0upd.cc:3286
|
#6 0x000055e329329eb1 in row_upd_step (thr=0x6240001dc188) at ../storage/innobase/row/row0upd.cc:3432
|
#7 0x000055e32922d564 in row_update_cascade_for_mysql (thr=0x6240001dc188, node=0x61600005b008, table=0x618000046908) at ../storage/innobase/row/row0mysql.cc:2109
|
#8 0x000055e3291bce29 in row_ins_foreign_check_on_constraint (thr=0x6240001dc188, foreign=0x616000058f08, pcur=0x7fd543432f80, entry=0x61600005ad28, mtr=0x7fd5434330c0) at ../storage/innobase/row/row0ins.cc:1404
|
#9 0x000055e3291b7aa9 in row_ins_check_foreign_constraint (check_ref=0, foreign=0x616000058f08, table=0x618000045d08, entry=0x61600005ad28, thr=0x6240001dc188) at ../storage/innobase/row/row0ins.cc:1793
|
#10 0x000055e3293378ee in row_upd_check_references_constraints (node=0x620000010bf8, pcur=0x611000071100, table=0x618000045d08, index=0x617000042c88, offsets=0x7fd543434e30, thr=0x6240001dc188, mtr=0x7fd5434348c0) at ../storage/innobase/row/row0upd.cc:325
|
#11 0x000055e3293321f1 in row_upd_del_mark_clust_rec (node=0x620000010bf8, index=0x617000042c88, offsets=0x7fd543434e30, thr=0x6240001dc188, referenced=1, foreign=false, mtr=0x7fd5434348c0) at ../storage/innobase/row/row0upd.cc:2996
|
#12 0x000055e32933009d in row_upd_clust_step (node=0x620000010bf8, thr=0x6240001dc188) at ../storage/innobase/row/row0upd.cc:3157
|
#13 0x000055e32932ab65 in row_upd (node=0x620000010bf8, thr=0x6240001dc188) at ../storage/innobase/row/row0upd.cc:3286
|
#14 0x000055e329329eb1 in row_upd_step (thr=0x6240001dc188) at ../storage/innobase/row/row0upd.cc:3432
|
#15 0x000055e329229c01 in row_update_for_mysql (prebuilt=0x620000010108) at ../storage/innobase/row/row0mysql.cc:1825
|
#16 0x000055e328e52d64 in ha_innobase::delete_row (this=0x61c0000628a8, record=0x6190000d93b8 "\377d") at ../storage/innobase/handler/ha_innodb.cc:9059
|
#17 0x000055e328759cba in handler::ha_delete_row (this=0x61c0000628a8, buf=0x6190000d93b8 "\377d") at ../sql/handler.cc:6168
|
#18 0x000055e328ca6f07 in mysql_delete (thd=0x62a0000ba208, table_list=0x62b000000340, conds=0x62b000000b30, order_list=0x62a0000be7a0, limit=18446744073709551615, options=0, result=0x0) at ../sql/sql_delete.cc:583
|
#19 0x000055e327e6e16b in mysql_execute_command (thd=0x62a0000ba208) at ../sql/sql_parse.cc:4362
|
#20 0x000055e327e58ce6 in mysql_parse (thd=0x62a0000ba208, rawbuf=0x62b000000228 "DELETE FROM parent WHERE ID=100", length=31, parser_state=0x7fd54343eb20, is_com_multi=false, is_next_command=false) at ../sql/sql_parse.cc:7733
|
We can see here, that top 5 frames are identical, and that is expected to be persistent during the whole functinon execution (considering it is designed well).
I was expecting the difference in row_upd_sec_index_entry, since it is the function where the secondary index update happens. The first difference I found was in rec_convert_dtuple_to_rec_comp record-tuple comparison function.
GOOD:
#0 rec_convert_dtuple_to_rec_comp (rec=0x6190000dc10e "\276\276\276\276"..., index=0x617000044188, fields=0x6190000dbc40, n_fields=3, status=0, temp=false) at ../storage/innobase/rem/rem0rec.cc:1262
|
#1 0x0000559ba06b76be in rec_convert_dtuple_to_rec_new (buf=0x6190000dc108 "\001\276", index=0x617000044188, dtuple=0x6190000dbc08) at ../storage/innobase/rem/rem0rec.cc:1338
|
#2 0x0000559ba06b73ce in rec_convert_dtuple_to_rec (buf=0x6190000dc108 "\001\276", index=0x617000044188, dtuple=0x6190000dbc08, n_ext=0) at ../storage/innobase/rem/rem0rec.cc:1370
|
#3 0x0000559ba0a71140 in page_cur_tuple_insert (cursor=0x7f5da5d44328, tuple=0x6190000dbc08, index=0x617000044188, offsets=0x7f5da5d44c30, heap=0x7f5da5d442c0, n_ext=0, mtr=0x7f5da5d44400, use_cache=false) at ../storage/innobase/include/page0cur.ic:280
|
#4 0x0000559ba0a6ebd2 in btr_cur_optimistic_insert (flags=0, cursor=0x7f5da5d44320, offsets=0x7f5da5d44c30, heap=0x7f5da5d442c0, entry=0x6190000dbc08, rec=0x7f5da5d45420, big_rec=0x7f5da5d45440, n_ext=0, thr=0x6250000f0188, mtr=0x7f5da5d44400) at ../storage/innobase/btr/btr0cur.cc:3237
|
#5 0x0000559ba071c25f in row_ins_sec_index_entry_low (flags=0, mode=2, index=0x617000044188, offsets_heap=0x6190000dc080, heap=0x6190000dc580, entry=0x6190000dbc08, trx_id=0, thr=0x6250000f0188) at ../storage/innobase/row/row0ins.cc:3029
|
#6 0x0000559ba0720496 in row_ins_sec_index_entry (index=0x617000044188, entry=0x6190000dbc08, thr=0x6250000f0188) at ../storage/innobase/row/row0ins.cc:3203
|
#7 0x0000559ba0895093 in row_upd_sec_index_entry (node=0x6210000a5d70, thr=0x6250000f0188) at ../storage/innobase/row/row0upd.cc:2526
|
#8 0x0000559ba0887c94 in row_upd_sec_step (node=0x6210000a5d70, thr=0x6250000f0188) at ../storage/innobase/row/row0upd.cc:2553
|
#9 0x0000559ba088220f in row_upd (node=0x6210000a5d70, thr=0x6250000f0188) at ../storage/innobase/row/row0upd.cc:3315
|
#10 0x0000559ba0880eb1 in row_upd_step (thr=0x6250000f0188) at ../storage/innobase/row/row0upd.cc:3432
|
#11 0x0000559ba0780c01 in row_update_for_mysql (prebuilt=0x6210000a5188) at ../storage/innobase/row/row0mysql.cc:1825
|
#12 0x0000559ba03a53bc in ha_innobase::update_row (this=0x61c0000638a8, old_row=0x6190000da2d0 "\371\363\340\001", new_row=0x6190000da2b8 "\373\363\340\001") at ../storage/innobase/handler/ha_innodb.cc:8943
|
#13 0x0000559b9fcb023c in handler::ha_update_row (this=0x61c0000638a8, old_data=0x6190000da2d0 "\371\363\340\001", new_data=0x6190000da2b8 "\373\363\340\001") at ../sql/handler.cc:6140
|
#14 0x0000559b9f74a156 in mysql_update (thd=0x62a0000ba208, table_list=0x62b000000370, fields=..., values=..., conds=0x62b000000d60, order_num=0, order=0x0, limit=18446744073709551615, handle_duplicates=DUP_ERROR, ignore=false, found_return=0x7f5da5d4e5a0, updated_return=0x7f5da5d4e5c0) at ../sql/sql_update.cc:817
|
#15 0x0000559b9f3c0e4b in mysql_execute_command (thd=0x62a0000ba208) at ../sql/sql_parse.cc:3998
|
#16 0x0000559b9f3afce6 in mysql_parse (thd=0x62a0000ba208, rawbuf=0x62b000000228 "UPDATE child set ParentID= NULL WHERE ParentID = 100", length=52, parser_state=0x7f5da5d50b20, is_com_multi=false, is_next_command=false) at ../sql/sql_parse.cc:7733
|
if (dfield_is_null(field)) entry is bypassed
BAD
#0 rec_convert_dtuple_to_rec_comp (rec=0x6190000dc10e "\276\276\276"..., index=0x617000044188, fields=0x6190000dbc40, n_fields=3, status=0, temp=false) at ../storage/innobase/rem/rem0rec.cc:1257
|
#1 0x000055e3291606be in rec_convert_dtuple_to_rec_new (buf=0x6190000dc108 "\001\276", index=0x617000044188, dtuple=0x6190000dbc08) at ../storage/innobase/rem/rem0rec.cc:1338
|
#2 0x000055e3291603ce in rec_convert_dtuple_to_rec (buf=0x6190000dc108 "\001\276", index=0x617000044188, dtuple=0x6190000dbc08, n_ext=0) at ../storage/innobase/rem/rem0rec.cc:1370
|
#3 0x000055e32951a140 in page_cur_tuple_insert (cursor=0x7fd54342f168, tuple=0x6190000dbc08, index=0x617000044188, offsets=0x7fd54342fa70, heap=0x7fd54342f100, n_ext=0, mtr=0x7fd54342f240, use_cache=false) at ../storage/innobase/include/page0cur.ic:280
|
#4 0x000055e329517bd2 in btr_cur_optimistic_insert (flags=0, cursor=0x7fd54342f160, offsets=0x7fd54342fa70, heap=0x7fd54342f100, entry=0x6190000dbc08, rec=0x7fd543430260, big_rec=0x7fd543430280, n_ext=0, thr=0x6240001dc188, mtr=0x7fd54342f240) at ../storage/innobase/btr/btr0cur.cc:3237
|
#5 0x000055e3291c525f in row_ins_sec_index_entry_low (flags=0, mode=2, index=0x617000044188, offsets_heap=0x6190000dc080, heap=0x6190000dc580, entry=0x6190000dbc08, trx_id=0, thr=0x6240001dc188) at ../storage/innobase/row/row0ins.cc:3029
|
#6 0x000055e3291c9496 in row_ins_sec_index_entry (index=0x617000044188, entry=0x6190000dbc08, thr=0x6240001dc188) at ../storage/innobase/row/row0ins.cc:3203
|
#7 0x000055e32933e093 in row_upd_sec_index_entry (node=0x61600005b008, thr=0x6240001dc188) at ../storage/innobase/row/row0upd.cc:2526
|
#8 0x000055e329330c94 in row_upd_sec_step (node=0x61600005b008, thr=0x6240001dc188) at ../storage/innobase/row/row0upd.cc:2553
|
#9 0x000055e32932b20f in row_upd (node=0x61600005b008, thr=0x6240001dc188) at ../storage/innobase/row/row0upd.cc:3315
|
#10 0x000055e329329eb1 in row_upd_step (thr=0x6240001dc188) at ../storage/innobase/row/row0upd.cc:3432
|
#11 0x000055e32922d564 in row_update_cascade_for_mysql (thr=0x6240001dc188, node=0x61600005b008, table=0x618000046908) at ../storage/innobase/row/row0mysql.cc:2109
|
#12 0x000055e3291bce29 in row_ins_foreign_check_on_constraint (thr=0x6240001dc188, foreign=0x616000058f08, pcur=0x7fd543432f80, entry=0x61600005ad28, mtr=0x7fd5434330c0) at ../storage/innobase/row/row0ins.cc:1404
|
#13 0x000055e3291b7aa9 in row_ins_check_foreign_constraint (check_ref=0, foreign=0x616000058f08, table=0x618000045d08, entry=0x61600005ad28, thr=0x6240001dc188) at ../storage/innobase/row/row0ins.cc:1793
|
#14 0x000055e3293378ee in row_upd_check_references_constraints (node=0x620000010bf8, pcur=0x611000071100, table=0x618000045d08, index=0x617000042c88, offsets=0x7fd543434e30, thr=0x6240001dc188, mtr=0x7fd5434348c0) at ../storage/innobase/row/row0upd.cc:325
|
#15 0x000055e3293321f1 in row_upd_del_mark_clust_rec (node=0x620000010bf8, index=0x617000042c88, offsets=0x7fd543434e30, thr=0x6240001dc188, referenced=1, foreign=false, mtr=0x7fd5434348c0) at ../storage/innobase/row/row0upd.cc:2996
|
#16 0x000055e32933009d in row_upd_clust_step (node=0x620000010bf8, thr=0x6240001dc188) at ../storage/innobase/row/row0upd.cc:3157
|
#17 0x000055e32932ab65 in row_upd (node=0x620000010bf8, thr=0x6240001dc188) at ../storage/innobase/row/row0upd.cc:3286
|
#18 0x000055e329329eb1 in row_upd_step (thr=0x6240001dc188) at ../storage/innobase/row/row0upd.cc:3432
|
#19 0x000055e329229c01 in row_update_for_mysql (prebuilt=0x620000010108) at ../storage/innobase/row/row0mysql.cc:1825
|
#20 0x000055e328e52d64 in ha_innobase::delete_row (this=0x61c0000628a8, record=0x6190000d93b8 "\377d") at ../storage/innobase/handler/ha_innodb.cc:9059
|
#21 0x000055e328759cba in handler::ha_delete_row (this=0x61c0000628a8, buf=0x6190000d93b8 "\377d") at ../sql/handler.cc:6168
|
#22 0x000055e328ca6f07 in mysql_delete (thd=0x62a0000ba208, table_list=0x62b000000340, conds=0x62b000000b30, order_list=0x62a0000be7a0, limit=18446744073709551615, options=0, result=0x0) at ../sql/sql_delete.cc:583
|
#23 0x000055e327e6e16b in mysql_execute_command (thd=0x62a0000ba208) at ../sql/sql_parse.cc:4362
|
#24 0x000055e327e58ce6 in mysql_parse (thd=0x62a0000ba208, rawbuf=0x62b000000228 "DELETE FROM parent WHERE ID=100", length=31, parser_state=0x7fd54343eb20, is_com_multi=false, is_next_command=false) at ../sql/sql_parse.cc:7733
|
#25 0x000055e327e4a6c6 in dispatch_command (command=COM_QUERY, thd=0x62a0000ba208, packet=0x629000136209 "DELETE FROM parent WHERE ID=100", packet_length=31, is_com_multi=false, is_next_command=false) at ../sql/sql_parse.cc:1823
|
#26 0x000055e327e53e39 in do_command (thd=0x62a0000ba208) at ../sql/sql_parse.cc:1377
|
#27 0x000055e328352495 in do_handle_one_connection (connect=0x6080000014a8) at ../sql/sql_connect.cc:1336
|
#28 0x000055e328351b44 in handle_one_connection (arg=0x6080000014a8) at ../sql/sql_connect.cc:1241
|
#29 0x000055e329eb4e92 in pfs_spawn_thread (arg=0x615000007888) at ../storage/perfschema/pfs.cc:1869
|
#30 0x00007fd55f2d93e9 in start_thread () from /usr/lib/libpthread.so.0
|
#31 0x00007fd55e93c293 in clone () from /usr/lib/libc.so.6
|
if is entered.
It turned out that this happens due to different values stored in update->n_fields, and the behavior was different even much earlier, in row_upd_store_v_row called from row_upd_clust_step
GOOD
Thread 2 hit Breakpoint 23, row_upd_store_v_row (node=0x6210000a5d70, update=0x6210000a5e90, thd=0x62a0000ba208, mysql_table=0x61e00003cc88) at ../storage/innobase/row/row0upd.cc:2147
|
2147 if (i >= n_upd) {
|
(gdb) bt
|
#0 row_upd_store_v_row (node=0x6210000a5d70, update=0x6210000a5e90, thd=0x62a0000ba208, mysql_table=0x61e00003cc88) at ../storage/innobase/row/row0upd.cc:2147
|
#1 0x0000559ba088b628 in row_upd_store_row (node=0x6210000a5d70, thd=0x62a0000ba208, mysql_table=0x61e00003cc88) at ../storage/innobase/row/row0upd.cc:2240
|
#2 0x0000559ba0887478 in row_upd_clust_step (node=0x6210000a5d70, thr=0x6250000f0188) at ../storage/innobase/row/row0upd.cc:3190
|
#3 0x0000559ba0881b65 in row_upd (node=0x6210000a5d70, thr=0x6250000f0188) at ../storage/innobase/row/row0upd.cc:3286
|
#4 0x0000559ba0880eb1 in row_upd_step (thr=0x6250000f0188) at ../storage/innobase/row/row0upd.cc:3432
|
#5 0x0000559ba0780c01 in row_update_for_mysql (prebuilt=0x6210000a5188) at ../storage/innobase/row/row0mysql.cc:1825
|
#6 0x0000559ba03a53bc in ha_innobase::update_row (this=0x61c0000638a8, old_row=0x6190000da2d0 "\371\363\340\001", new_row=0x6190000da2b8 "\373\363\340\001") at ../storage/innobase/handler/ha_innodb.cc:8943
|
#7 0x0000559b9fcb023c in handler::ha_update_row (this=0x61c0000638a8, old_data=0x6190000da2d0 "\371\363\340\001", new_data=0x6190000da2b8 "\373\363\340\001") at ../sql/handler.cc:6140
|
#8 0x0000559b9f74a156 in mysql_update (thd=0x62a0000ba208, table_list=0x62b000000370, fields=..., values=..., conds=0x62b000000d60, order_num=0, order=0x0, limit=18446744073709551615, handle_duplicates=DUP_ERROR, ignore=false, found_return=0x7f5da5d4e5a0, updated_return=0x7f5da5d4e5c0) at ../sql/sql_update.cc:817
|
#9 0x0000559b9f3c0e4b in mysql_execute_command (thd=0x62a0000ba208) at ../sql/sql_parse.cc:3998
|
#10 0x0000559b9f3afce6 in mysql_parse (thd=0x62a0000ba208, rawbuf=0x62b000000228 "UPDATE child set ParentID= NULL WHERE ParentID = 100", length=52, parser_state=0x7f5da5d50b20, is_com_multi=false, is_next_command=false) at ../sql/sql_parse.cc:7733
|
#11 0x0000559b9f3a16c6 in dispatch_command (command=COM_QUERY, thd=0x62a0000ba208, packet=0x629000136209 "UPDATE child set ParentID= NULL WHERE ParentID = 100", packet_length=52, is_com_multi=false, is_next_command=false) at ../sql/sql_parse.cc:1823
|
#12 0x0000559b9f3aae39 in do_command (thd=0x62a0000ba208) at ../sql/sql_parse.cc:1377
|
#13 0x0000559b9f8a9495 in do_handle_one_connection (connect=0x6080000014a8) at ../sql/sql_connect.cc:1336
|
#14 0x0000559b9f8a8b44 in handle_one_connection (arg=0x6080000014a8) at ../sql/sql_connect.cc:1241
|
#15 0x0000559ba140be92 in pfs_spawn_thread (arg=0x615000007888) at ../storage/perfschema/pfs.cc:1869
|
#16 0x00007f5dc1beb3e9 in start_thread () from /usr/lib/libpthread.so.0
|
#17 0x00007f5dc124e293 in clone () from /usr/lib/libc.so.6
|
(gdb) p n_upd
|
$1 = 1
|
n_upd equals 1 (only one field is updated)
BAD:
Thread 2 hit Breakpoint 23, row_upd_store_v_row (node=0x61600005b008, update=0x61a00002d108, thd=0x62a0000ba208, mysql_table=0x0) at ../storage/innobase/row/row0upd.cc:2147
|
2147 if (i >= n_upd) {
|
(gdb) bt
|
#0 row_upd_store_v_row (node=0x61600005b008, update=0x61a00002d108, thd=0x62a0000ba208, mysql_table=0x0) at ../storage/innobase/row/row0upd.cc:2147
|
#1 0x000055e329334628 in row_upd_store_row (node=0x61600005b008, thd=0x62a0000ba208, mysql_table=0x0) at ../storage/innobase/row/row0upd.cc:2240
|
#2 0x000055e329330478 in row_upd_clust_step (node=0x61600005b008, thr=0x6240001dc188) at ../storage/innobase/row/row0upd.cc:3190
|
#3 0x000055e32932ab65 in row_upd (node=0x61600005b008, thr=0x6240001dc188) at ../storage/innobase/row/row0upd.cc:3286
|
#4 0x000055e329329eb1 in row_upd_step (thr=0x6240001dc188) at ../storage/innobase/row/row0upd.cc:3432
|
#5 0x000055e32922d564 in row_update_cascade_for_mysql (thr=0x6240001dc188, node=0x61600005b008, table=0x618000046908) at ../storage/innobase/row/row0mysql.cc:2109
|
#6 0x000055e3291bce29 in row_ins_foreign_check_on_constraint (thr=0x6240001dc188, foreign=0x616000058f08, pcur=0x7fd543432f80, entry=0x61600005ad28, mtr=0x7fd5434330c0) at ../storage/innobase/row/row0ins.cc:1404
|
#7 0x000055e3291b7aa9 in row_ins_check_foreign_constraint (check_ref=0, foreign=0x616000058f08, table=0x618000045d08, entry=0x61600005ad28, thr=0x6240001dc188) at ../storage/innobase/row/row0ins.cc:1793
|
#8 0x000055e3293378ee in row_upd_check_references_constraints (node=0x620000010bf8, pcur=0x611000071100, table=0x618000045d08, index=0x617000042c88, offsets=0x7fd543434e30, thr=0x6240001dc188, mtr=0x7fd5434348c0) at ../storage/innobase/row/row0upd.cc:325
|
#9 0x000055e3293321f1 in row_upd_del_mark_clust_rec (node=0x620000010bf8, index=0x617000042c88, offsets=0x7fd543434e30, thr=0x6240001dc188, referenced=1, foreign=false, mtr=0x7fd5434348c0) at ../storage/innobase/row/row0upd.cc:2996
|
#10 0x000055e32933009d in row_upd_clust_step (node=0x620000010bf8, thr=0x6240001dc188) at ../storage/innobase/row/row0upd.cc:3157
|
#11 0x000055e32932ab65 in row_upd (node=0x620000010bf8, thr=0x6240001dc188) at ../storage/innobase/row/row0upd.cc:3286
|
#12 0x000055e329329eb1 in row_upd_step (thr=0x6240001dc188) at ../storage/innobase/row/row0upd.cc:3432
|
#13 0x000055e329229c01 in row_update_for_mysql (prebuilt=0x620000010108) at ../storage/innobase/row/row0mysql.cc:1825
|
#14 0x000055e328e52d64 in ha_innobase::delete_row (this=0x61c0000628a8, record=0x6190000d93b8 "\377d") at ../storage/innobase/handler/ha_innodb.cc:9059
|
(gdb) p n_upd
|
$1 = 2
|
n_upd equals 2 (two fields are updated)
This is right, we only update ParentID, and Flag should remain unchanged, but in ON DELETE SET NULL case it is also chaned (to NULL). I could have guessed earlier when I have seen NULL returning from b-tree, but it became clear only at the following point, wehere update->n_fields is set up last time, in row_ins_foreign_fill_virtual which fills an update vector for cascade cases:
BAD:
Thread 2 hit Breakpoint 25, row_ins_foreign_fill_virtual (cascade=0x61600005b008, rec=0x7fd555c7407e "", index=0x617000043708, node=0x620000010bf8, foreign=0x616000058f08) at ../storage/innobase/row/row0ins.cc:948
|
948 update->n_fields += n_v_fld;
|
(gdb) bt
|
#0 row_ins_foreign_fill_virtual (cascade=0x61600005b008, rec=0x7fd555c7407e "", index=0x617000043708, node=0x620000010bf8, foreign=0x616000058f08) at ../storage/innobase/row/row0ins.cc:948
|
#1 0x000055e3291bc2b9 in row_ins_foreign_check_on_constraint (thr=0x6240001dc188, foreign=0x616000058f08, pcur=0x7fd543432f80, entry=0x61600005ad28, mtr=0x7fd5434330c0) at ../storage/innobase/row/row0ins.cc:1300
|
#2 0x000055e3291b7aa9 in row_ins_check_foreign_constraint (check_ref=0, foreign=0x616000058f08, table=0x618000045d08, entry=0x61600005ad28, thr=0x6240001dc188) at ../storage/innobase/row/row0ins.cc:1793
|
#3 0x000055e3293378ee in row_upd_check_references_constraints (node=0x620000010bf8, pcur=0x611000071100, table=0x618000045d08, index=0x617000042c88, offsets=0x7fd543434e30, thr=0x6240001dc188, mtr=0x7fd5434348c0) at ../storage/innobase/row/row0upd.cc:325
|
#4 0x000055e3293321f1 in row_upd_del_mark_clust_rec (node=0x620000010bf8, index=0x617000042c88, offsets=0x7fd543434e30, thr=0x6240001dc188, referenced=1, foreign=false, mtr=0x7fd5434348c0) at ../storage/innobase/row/row0upd.cc:2996
|
#5 0x000055e32933009d in row_upd_clust_step (node=0x620000010bf8, thr=0x6240001dc188) at ../storage/innobase/row/row0upd.cc:3157
|
#6 0x000055e32932ab65 in row_upd (node=0x620000010bf8, thr=0x6240001dc188) at ../storage/innobase/row/row0upd.cc:3286
|
#7 0x000055e329329eb1 in row_upd_step (thr=0x6240001dc188) at ../storage/innobase/row/row0upd.cc:3432
|
#8 0x000055e329229c01 in row_update_for_mysql (prebuilt=0x620000010108) at ../storage/innobase/row/row0mysql.cc:1825
|
#9 0x000055e328e52d64 in ha_innobase::delete_row (this=0x61c0000628a8, record=0x6190000d93b8 "\377d") at ../storage/innobase/handler/ha_innodb.cc:9059
|
#10 0x000055e328759cba in handler::ha_delete_row (this=0x61c0000628a8, buf=0x6190000d93b8 "\377d") at ../sql/handler.cc:6168
|
#11 0x000055e328ca6f07 in mysql_delete (thd=0x62a0000ba208, table_list=0x62b000000340, conds=0x62b000000b30, order_list=0x62a0000be7a0, limit=18446744073709551615, options=0, result=0x0) at ../sql/sql_delete.cc:583
|
#12 0x000055e327e6e16b in mysql_execute_command (thd=0x62a0000ba208) at ../sql/sql_parse.cc:4362
|
#13 0x000055e327e58ce6 in mysql_parse (thd=0x62a0000ba208, rawbuf=0x62b000000228 "DELETE FROM parent WHERE ID=100", length=31, parser_state=0x7fd54343eb20, is_com_multi=false, is_next_command=false) at ../sql/sql_parse.cc:7733
|
#14 0x000055e327e4a6c6 in dispatch_command (command=COM_QUERY, thd=0x62a0000ba208, packet=0x629000136209 "DELETE FROM parent WHERE ID=100", packet_length=31, is_com_multi=false, is_next_command=false) at ../sql/sql_parse.cc:1823
|
(gdb) p n_v_fld
|
$2 = 1
|
update->n_fields, the number of fields changed, gets incremented bu a total number of virtual fields in table. Then it can be seen, again in row_ins_foreign_fill_virtual, that all the virtual fields sharing the index of the FOREIGN KEY are set to NULL, which is incorrect.
Actually, the total number of indexed virtual fields in the table could be more than a number of virtual columns in FK index, and that can be another bug.
| Summary | MariaDB server crash | Server crashes after DELETE with SEL NULL Foreign key and a virtual column in index |
The fix was pushed to bb-10.2-nikita.
Buildbot: https://buildbot.askmonty.org/buildbot/grid?category=main&branch=bb-10.2-nikita
| Assignee | Nikita Malyavin [ nikitamalyavin ] | Thirunarayanan Balathandayuthapani [ thiru ] |
Note: the initial support for CASCADE/SET NULL was added here: https://github.com/mysql/mysql-server/commit/5ed18d823c4fad30b75ab4b68c7327c1b95821d3
| Status | In Progress [ 3 ] | Stalled [ 10000 ] |
| Assignee | Thirunarayanan Balathandayuthapani [ thiru ] | Nikita Malyavin [ nikitamalyavin ] |
| Assignee | Nikita Malyavin [ nikitamalyavin ] | Thirunarayanan Balathandayuthapani [ thiru ] |
| Status | Stalled [ 10000 ] | In Review [ 10002 ] |
| Fix Version/s | 10.2.34 [ 24505 ] | |
| Fix Version/s | 10.3.25 [ 24506 ] | |
| Fix Version/s | 10.4.15 [ 24507 ] | |
| Fix Version/s | 10.5.6 [ 24508 ] | |
| Fix Version/s | 10.2 [ 14601 ] | |
| Fix Version/s | 10.3 [ 22126 ] | |
| Fix Version/s | 10.4 [ 22408 ] | |
| Fix Version/s | 10.5 [ 23123 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | In Review [ 10002 ] | Closed [ 6 ] |
| Summary | Server crashes after DELETE with SEL NULL Foreign key and a virtual column in index | Server crashes after DELETE with SEЕ NULL Foreign key and a virtual column in index |
| Summary | Server crashes after DELETE with SEЕ NULL Foreign key and a virtual column in index | Server crashes after DELETE with SET NULL Foreign key and a virtual column in index |
| Fix Version/s | 10.5.7 [ 25019 ] |
| Fix Version/s | 10.5.6 [ 24508 ] |
| Fix Version/s | 10.4.16 [ 25020 ] |
| Fix Version/s | 10.4.15 [ 24507 ] |
| Fix Version/s | 10.3.26 [ 25021 ] |
| Fix Version/s | 10.3.25 [ 24506 ] |
| Fix Version/s | 10.2.35 [ 25022 ] |
| Fix Version/s | 10.2.34 [ 24505 ] |
| Workflow | MariaDB v3 [ 99062 ] | MariaDB v4 [ 156658 ] |
Could you please paste the output of SHOW CREATE TABLE for this table and the related one.