Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Major
-
Resolution: Fixed
-
Affects Version/s: 10.1, 10.0
-
Fix Version/s: 10.0.18
-
Labels:
-
Environment:Prod: a freebsd server running 10.0.14
To eliminate OS influence, tested: mac 10.9.5 laptop with 8 GB ram running 10.0.15 installed with homebrew
Description
This worked on mariadb 5.5.x.
The table is recreated at the beginning of a data import and when data is inserted into the empty table the virtual column works. When the next set of data is updated into the table, the table is corrupted and the server crashes - stops working. When the corrupted table is deleted, the server can be restart.
The table must be updated several times for a successful data import.
The work around was to replace the virtual column by a varchar and recalculate the values in the update query.
The table:
DROP TABLE IF EXISTS grad_degree;
CREATE TABLE IF NOT EXISTS grad_degree (
student_id int(8) UNSIGNED NOT NULL,
plan varchar(10) NOT NULL,
admit_term char(4) NOT NULL,
faculty varchar(3) NOT NULL DEFAULT '',
org_unit varchar(4) NOT NULL DEFAULT '',
registered char(1) NOT NULL DEFAULT '',
ft_pt enum('F', 'P') NOT NULL,
level char(1) NOT NULL DEFAULT '',
enrl_type varchar(4) NOT NULL DEFAULT '',
curr_term decimal(3,1) UNSIGNED NOT NULL DEFAULT '0.0',
cum_term decimal(3,1) UNSIGNED NOT NULL DEFAULT '0.0',
wdraw_code varchar(3) NOT NULL DEFAULT '',
wdraw_rsn varchar(4) NOT NULL DEFAULT '',
wdraw_date date NOT NULL DEFAULT '0000-00-00',
term_gpa decimal(5,2) UNSIGNED NOT NULL DEFAULT '0.00',
cum_gpa decimal(5,2) UNSIGNED NOT NULL DEFAULT '0.00',
thesis_title varchar(250) NOT NULL DEFAULT '',
ofis_deg_status varchar(15) AS (
CASE
WHEN wdraw_rsn = '' THEN 'In progress'
WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed'
ELSE 'Not Completed'
END) VIRTUAL,
deg_transferred enum('Y', 'N') NOT NULL DEFAULT 'N' COMMENT 'Guessed by the same admit term with a different plan for a student',
deg_start_term char(4) NOT NULL DEFAULT '' COMMENT 'Educated guess at the beginning of the data',
deg_as_of_term char(4) NOT NULL COMMENT 'In most cases also end term',
CONSTRAINT grad_degree_stu_plan_admit_pky PRIMARY KEY (student_id, plan, admit_term)
) ENGINE = InnoDB DEFAULT CHARSET = utf8
;
CREATE INDEX grad_degree_wdraw_rsn_ndx ON grad_degree (wdraw_rsn); CREATE INDEX grad_degree_admit_term_ndx ON grad_degree (admit_term); CREATE INDEX grad_degree_as_of_term_ndx ON grad_degree (deg_as_of_term);
The update query:
UPDATE grad_degree AS gd INNER JOIN gso_grad_supr AS ggs ON ( gd.student_id = ggs.uw_id AND gd.plan = ggs.plan AND gd.admit_term = ggs.admit_term ) SET gd.faculty = ggs.faculty, gd.org_unit = ggs.org_unit, gd.registered = ggs.registered, gd.ft_pt = ggs.ft_pt, gd.level = ggs.level, gd.enrl_type = ggs.enrl_type, gd.curr_term = ggs.curr_term, gd.cum_term = ggs.cum_term, gd.wdraw_code = ggs.wdraw_code, gd.wdraw_rsn = ggs.wdraw_rsn, gd.wdraw_date = ggs.wdraw_date, gd.term_gpa = ggs.term_gpa, gd.cum_gpa = ggs.cum_gpa, gd.deg_as_of_term = 1031 WHERE gd.wdraw_rsn NOT IN ('DCMP', 'TRDC') AND ggs.term = 1031 ;
Marlon 519-885-1211 x38146
Gliffy Diagrams
Attachments
Issue Links
- includes
-
-
- Closed
-
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Closing for now as incomplete; if you have information as requested above, please comment to re-open the report.
Hello,
I was finally able to test if mariadb 10.0.16 had fixed the issue that I reported. Sergei sent me the email indicating that it had been fixed. Unfortunately, the server is still hanging.
I also noticed that the csv and debug sql script that I emailed to this address is not in this thread.
Did you get the sample csv and debug sq script that I sent from m3griffi@uwaterloo.ca or m3griffi@engmail.uwaterloo.ca?
Marlon 519-885-1211 x38146
Copied attachments from MDEV-7851
Marlon Griffith wrote:
Here are my observations:
- after eliminating most columns, I have tracked the bug to these combination of columns:
- the ofis_deg_status and deg_as_of_term being updated in the same query in combination with an index on deg_as_of_term
- the deg_as_of_term's index is corrupted and thus crashing the database server
I have included two files:
- grad_degree_build_bug.sql: manually follow the instructions to duplicate the bug
- gso_grad_supr.csv: a sanitized version of, only, the relevant data to duplicate the bug
Thanks for the test case, reproducible as described. On a debug build, it's an InnoDB assertion failure:
InnoDB: Assertion failure in thread 139837203834624 in file rem0rec.ic line 1040 InnoDB: Failing assertion: n < rec_offs_n_fields(offsets)
On a release build, it's a SIGSEGV.
I put together and attached an MTR-like test case (mdev7367.test). It is the same exact scenario as in Marlon's scripts, unabridged; the only differences are:
- comments converted to MTR style;
- LOAD DATA replaced by INSERT (of the same data);
- include/have_innodb.inc added.
Below is a somewhat shortened test case which also causes the same problem. It's better to check both on the fixed version, just in case.
Shorter test case
--source include/have_innodb.inc CREATE TABLE IF NOT EXISTS gso_grad_supr ( term char(4) NOT NULL DEFAULT '', uw_id int(8) UNSIGNED NOT NULL DEFAULT 0, plan varchar(10) NOT NULL DEFAULT '', wdraw_rsn varchar(4) NOT NULL DEFAULT '', admit_term char(4) NOT NULL DEFAULT '', CONSTRAINT gso_grad_supr_pky PRIMARY KEY (uw_id, term) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `gso_grad_supr` VALUES ('1031',2,'CSM','','1009'); INSERT INTO `gso_grad_supr` VALUES ('1035',2,'CSM','ACAD','1009'); CREATE TABLE IF NOT EXISTS grad_degree ( student_id int(8) UNSIGNED NOT NULL, plan varchar(10) NOT NULL, admit_term char(4) NOT NULL, wdraw_rsn varchar(4) NOT NULL DEFAULT '', ofis_deg_status varchar(15) AS ( CASE WHEN wdraw_rsn = '' THEN 'In progress' WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed' ELSE 'Not Completed' END) VIRTUAL, deg_start_term char(4) NOT NULL DEFAULT '' COMMENT 'Educated guess at the beginning of the data', deg_as_of_term char(4) NOT NULL COMMENT 'In most cases also end term', CONSTRAINT grad_degree_stu_plan_admit_pky PRIMARY KEY (student_id, plan, admit_term) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE INDEX grad_degree_wdraw_rsn_ndx ON grad_degree (wdraw_rsn); CREATE INDEX grad_degree_as_of_term_ndx ON grad_degree (deg_as_of_term); INSERT IGNORE grad_degree ( student_id, plan, admit_term, wdraw_rsn, deg_start_term, deg_as_of_term ) SELECT ggs.uw_id AS c_student_id, ggs.plan, ggs.admit_term, ggs.wdraw_rsn, IF( (SELECT COUNT(*) FROM grad_degree AS gd WHERE gd.student_id = ggs.uw_id AND gd.admit_term = ggs.admit_term) > 0, ggs.term, ggs.admit_term ) AS c_deg_start_term, ggs.term AS c_as_of_term FROM gso_grad_supr AS ggs LEFT OUTER JOIN grad_degree AS gd ON ( gd.student_id = ggs.uw_id AND gd.plan = ggs.plan AND gd.admit_term = ggs.admit_term ) WHERE ggs.term = 1031 AND gd.student_id IS NULL ; UPDATE grad_degree AS gd INNER JOIN gso_grad_supr AS ggs ON ( gd.student_id = ggs.uw_id AND gd.plan = ggs.plan AND gd.admit_term = ggs.admit_term ) SET gd.wdraw_rsn = ggs.wdraw_rsn, gd.deg_as_of_term = 1035 WHERE gd.wdraw_rsn NOT IN ('DCMP', 'TRDC') AND ggs.term = 1035 ;
Stack trace from debug build of 10.0 commit 184f718fef0101a7559364cb97e22ee568e64c12
2015-03-27 19:13:25 7f2e62dc6700 InnoDB: Assertion failure in thread 139837203834624 in file rem0rec.ic line 1040 InnoDB: Failing assertion: n < rec_offs_n_fields(offsets) #5 0x00007f2e60c0d3e0 in *__GI_abort () at abort.c:92 #6 0x00007f2e590abd98 in rec_get_nth_field_offs (offsets=0x7f2e4ddae488, n=18446744073709551615, len=0x7f2e62dc30d8) at 10.0/storage/innobase/include/rem0rec.ic:1040 #7 0x00007f2e590ae3f3 in trx_undo_page_report_modify (undo_page=0x7f2e58924000 "", trx=0x7f2e4dd54c78, index=0x7f2e4de18378, rec=0x7f2e588f40ae "", offsets=0x7f2e4ddae488, update=0x7f2e4df1fd70, cmpl_info=0, mtr=0x7f2e62dc31d0) at 10.0/storage/innobase/trx/trx0rec.cc:788 #8 0x00007f2e590af482 in trx_undo_report_row_operation (flags=10, op_type=2, thr=0x7f2e4df1ffb8, index=0x7f2e4de18378, clust_entry=0x0, update=0x7f2e4df1fd70, cmpl_info=0, rec=0x7f2e588f40ae "", offsets=0x7f2e4ddae488, roll_ptr=0x7f2e62dc37e0) at 10.0/storage/innobase/trx/trx0rec.cc:1314 #9 0x00007f2e58e77ec8 in btr_cur_upd_lock_and_undo (flags=10, cursor=0x7f2e4dc14c78, offsets=0x7f2e4ddae488, update=0x7f2e4df1fd70, cmpl_info=0, thr=0x7f2e4df1ffb8, mtr=0x7f2e62dc3c90, roll_ptr=0x7f2e62dc37e0) at 10.0/storage/innobase/btr/btr0cur.cc:1707 #10 0x00007f2e58e79972 in btr_cur_pessimistic_update (flags=10, cursor=0x7f2e4dc14c78, offsets=0x7f2e62dc38d8, offsets_heap=0x7f2e62dc4168, entry_heap=0x7f2e4de5e400, big_rec=0x7f2e62dc38f0, update=0x7f2e4df1fd70, cmpl_info=0, thr=0x7f2e4df1ffb8, trx_id=1325, mtr=0x7f2e62dc3c90) at 10.0/storage/innobase/btr/btr0cur.cc:2501 #11 0x00007f2e5907ec6f in row_upd_clust_rec (node=0x7f2e4df1fc68, index=0x7f2e4de18378, offsets=0x7f2e4ddae488, offsets_heap=0x7f2e62dc4168, thr=0x7f2e4df1ffb8, mtr=0x7f2e62dc3c90) at 10.0/storage/innobase/row/row0upd.cc:2176 #12 0x00007f2e5907f577 in row_upd_clust_step (node=0x7f2e4df1fc68, thr=0x7f2e4df1ffb8) at 10.0/storage/innobase/row/row0upd.cc:2466 #13 0x00007f2e5907f6c4 in row_upd (node=0x7f2e4df1fc68, thr=0x7f2e4df1ffb8) at 10.0/storage/innobase/row/row0upd.cc:2521 #14 0x00007f2e5907fb34 in row_upd_step (thr=0x7f2e4df1ffb8) at 10.0/storage/innobase/row/row0upd.cc:2673 #15 0x00007f2e5903d76f in row_update_for_mysql (mysql_rec=0x7f2e4de61210 "\377\002", prebuilt=0x7f2e4df1f078) at 10.0/storage/innobase/row/row0mysql.cc:1799 #16 0x00007f2e58f4de3f in ha_innodb::update_row (this=0x7f2e4dc82088, old_row=0x7f2e4de61210 "\377\002", new_row=0x7f2e4de61188 "\377\002") at 10.0/storage/innobase/handler/ha_innodb.cc:7492 #17 0x00000000008773bb in handler::ha_update_row (this=0x7f2e4dc82088, old_data=0x7f2e4de61210 "\377\002", new_data=0x7f2e4de61188 "\377\002") at 10.0/sql/handler.cc:5996 #18 0x0000000000748e6e in multi_update::send_data (this=0x7f2e4df26f20, not_used_values=...) at 10.0/sql/sql_update.cc:2090 #19 0x00000000006de6b0 in end_send (join=0x7f2e4df26fe0, join_tab=0x7f2e4df15d08, end_of_records=false) at 10.0/sql/sql_select.cc:18918 #20 0x00000000006dc49b in evaluate_join_record (join=0x7f2e4df26fe0, join_tab=0x7f2e4df159e0, error=0) at 10.0/sql/sql_select.cc:18026 #21 0x00000000006dbf26 in sub_select (join=0x7f2e4df26fe0, join_tab=0x7f2e4df159e0, end_of_records=false) at 10.0/sql/sql_select.cc:17843 #22 0x00000000006dc49b in evaluate_join_record (join=0x7f2e4df26fe0, join_tab=0x7f2e4df156b8, error=0) at 10.0/sql/sql_select.cc:18026 #23 0x00000000006dbf26 in sub_select (join=0x7f2e4df26fe0, join_tab=0x7f2e4df156b8, end_of_records=false) at 10.0/sql/sql_select.cc:17843 #24 0x00000000006db5f9 in do_select (join=0x7f2e4df26fe0, fields=0x7f2e62dc4b10, table=0x0, procedure=0x0) at 10.0/sql/sql_select.cc:17466 #25 0x00000000006b8a73 in JOIN::exec_inner (this=0x7f2e4df26fe0) at 10.0/sql/sql_select.cc:3081 #26 0x00000000006b5f88 in JOIN::exec (this=0x7f2e4df26fe0) at 10.0/sql/sql_select.cc:2370 #27 0x00000000006b931d in mysql_select (thd=0x7f2e5a5f8070, rref_pointer_array=0x7f2e5a5fc6f0, tables=0x7f2e4df45378, wild_num=0, fields=..., conds=0x7f2e4df26dc0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=1342177408, result=0x7f2e4df26f20, unit=0x7f2e5a5fbd88, select_lex=0x7f2e5a5fc478) at 10.0/sql/sql_select.cc:3310 #28 0x00000000007472ac in mysql_multi_update (thd=0x7f2e5a5f8070, table_list=0x7f2e4df45378, fields=0x7f2e5a5fc590, values=0x7f2e5a5fca30, conds=0x7f2e4df26dc0, options=0, handle_duplicates=DUP_ERROR, ignore=false, unit=0x7f2e5a5fbd88, select_lex=0x7f2e5a5fc478, result=0x7f2e62dc5248) at 10.0/sql/sql_update.cc:1567 #29 0x000000000067e396 in mysql_execute_command (thd=0x7f2e5a5f8070) at 10.0/sql/sql_parse.cc:3366 #30 0x0000000000686c05 in mysql_parse (thd=0x7f2e5a5f8070, rawbuf=0x7f2e4df45088 "UPDATE grad_degree AS gd \nINNER JOIN\ngso_grad_supr AS ggs \nON ( gd.student_id = ggs.uw_id AND gd.plan = ggs.plan AND gd.admit_term = ggs.admit_term )\nSET \ngd.wdraw_rsn = ggs.wdraw_rsn,\ngd.deg_as_of_te"..., length=274, parser_state=0x7f2e62dc5600) at 10.0/sql/sql_parse.cc:6517 #31 0x0000000000679631 in dispatch_command (command=COM_QUERY, thd=0x7f2e5a5f8070, packet=0x7f2e552b9071 "UPDATE grad_degree AS gd \nINNER JOIN\ngso_grad_supr AS ggs \nON ( gd.student_id = ggs.uw_id AND gd.plan = ggs.plan AND gd.admit_term = ggs.admit_term )\nSET \ngd.wdraw_rsn = ggs.wdraw_rsn,\ngd.deg_as_of_te"..., packet_length=276) at 10.0/sql/sql_parse.cc:1300 #32 0x00000000006789c7 in do_command (thd=0x7f2e5a5f8070) at 10.0/sql/sql_parse.cc:996 #33 0x00000000007961d7 in do_handle_one_connection (thd_arg=0x7f2e5a5f8070) at 10.0/sql/sql_connect.cc:1375 #34 0x0000000000795f2a in handle_one_connection (arg=0x7f2e5a5f8070) at 10.0/sql/sql_connect.cc:1289 #35 0x0000000000cd240a in pfs_spawn_thread (arg=0x7f2e552b1370) at 10.0/storage/perfschema/pfs.cc:1860 #36 0x00007f2e629feb50 in start_thread (arg=<optimized out>) at pthread_create.c:304 #37 0x00007f2e60cb470d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:112
Something goes wrong already when indexes are build
Version: '10.0.17-MariaDB-debug-log' socket: '/run/shm/tmp/mysqld.1.sock' port: 16000 Source distribution 150330 12:06:19 [ERROR] Found index grad_degree_as_of_term_ndx whose column info does not match that of MySQL. 150330 12:06:19 [ERROR] Build InnoDB index translation table for Table ./test/grad_degree failed 150330 12:06:19 [ERROR] Found index grad_degree_as_of_term_ndx whose column info does not match that of MySQL. 150330 12:06:19 [ERROR] Build InnoDB index translation table for Table ./test/grad_degree failed 150330 12:06:19 [ERROR] Found index grad_degree_as_of_term_ndx whose column info does not match that of MySQL. 150330 12:06:19 [ERROR] Build InnoDB index translation table for Table ./test/grad_degree failed 2015-03-30 12:06:19 7f57a84fc700 InnoDB: Assertion failure in thread 140014462682880 in file rem0rec.ic line 1040 InnoDB: Failing assertion: n < rec_offs_n_fields(offsets)
Assertion is result from the fact that column is not found from clustered index.
commit b53bcd438f171fcbc3a4ad5051434e1b87c844fe
Author: Jan Lindström <jan.lindstrom@mariadb.com>
Date: Mon Mar 30 18:53:10 2015 +0300
MDEV-7367: Updating a virtual column corrupts table which crashes server
Analysis: MySQL table definition contains also virtual columns. Similarly,
index fielnr references MySQL table fields. However, InnoDB table definition
does not contain virtual columns. Therefore, when matching MySQL key fieldnr
we need to use actual column name to find out referenced InnoDB dictionary
column name.
Fix: Add new function to match MySQL index key columns to InnoDB dictionary.
Hi,
Could you please specify more clearly what needs to be inserted into the table initially, and what does it mean "the table must be updated several times for a successful data import"?
Better still, could you maybe provide the exact sequence of INSERTs / UPDATEs that causes the crash?
Also, your UPDATE statement uses the 2nd table which you didn't specify, please provide information about it as well – SHOW CREATE TABLE output and either a data dump or at least the information about how many rows matches the join.
Finally, what does the error log say, is there any kind of stack trace?
Thanks.