[MDEV-7367] Updating a virtual column corrupts table which crashes server - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.0(EOL), 10.1(EOL)
Fix Version/s: 10.0.18
Component/s: Data Manipulation - Update, Storage Engine - InnoDB, Storage Engine - XtraDB, Virtual Columns
Labels:
- verified
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

Attachments

- Sort By Name
- Sort By Date
- Ascending
- Descending
- Thumbnails
- List
- Download All

grad_degree_build_bug.sql
6 kB
2015-03-27 16:49
gso_grad_supr.csv
1 kB
2015-03-27 16:49
mdev7367.test
9 kB
2015-03-27 18:13

Issue Links

includes

MDEV-7851 More info about MDEV-7367: Updating a virtual column corrupts table which crashes server

Closed

relates to

MDEV-13838 Wrong result after altering a partitioned table

Closed

Activity

Ascending order - Click to sort in descending order

Marlon Griffith created issue - 2014-12-23 21:15

Elena Stepanova made changes - 2014-12-24 16:56

Field	Original Value	New Value
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	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: {code:sql} 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 ; {code} {code:sql} 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); {code} The update query: {code:sql} 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 ; {code} Marlon 519-885-1211 x38146

Elena Stepanova added a comment - 2014-12-24 17:15

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.

Elena Stepanova added a comment - 2014-12-24 17:15 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.

Elena Stepanova made changes - 2014-12-24 17:15

Due Date

2015-01-21

Sergei Golubchik made changes - 2015-01-06 00:00

Fix Version/s

10.0.16 [ 17900 ]

Sergei Golubchik made changes - 2015-01-06 00:00

Priority

Blocker [ 1 ]

Major [ 3 ]

Sergei Golubchik made changes - 2015-01-28 16:50

Fix Version/s		10.0 [ 16000 ]
Fix Version/s	10.0.16 [ 17900 ]

Elena Stepanova added a comment - 2015-02-08 13:40

Closing for now as incomplete; if you have information as requested above, please comment to re-open the report.

Elena Stepanova added a comment - 2015-02-08 13:40 Closing for now as incomplete; if you have information as requested above, please comment to re-open the report.

Elena Stepanova made changes - 2015-02-08 13:40

Fix Version/s		N/A [ 14700 ]
Fix Version/s	10.0 [ 16000 ]
Resolution		Incomplete [ 4 ]
Status	Open [ 1 ]	Closed [ 6 ]

Marlon Griffith added a comment - 2015-02-26 22:40

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

Marlon Griffith added a comment - 2015-02-26 22:40 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

Marlon Griffith made changes - 2015-03-26 22:23

Link

This issue includes ~~MDEV-7851~~ [ ~~MDEV-7851~~ ]

Elena Stepanova made changes - 2015-03-27 16:47

Resolution	Incomplete [ 4 ]
Status	Closed [ 6 ]	Stalled [ 10000 ]

Elena Stepanova added a comment - 2015-03-27 16:49 - edited

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

Elena Stepanova added a comment - 2015-03-27 16:49 - edited 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

Elena Stepanova made changes - 2015-03-27 16:49

Attachment		gso_grad_supr.csv [ 37505 ]
Attachment		grad_degree_build_bug.sql [ 37506 ]

Elena Stepanova made changes - 2015-03-27 17:28

Assignee

Elena Stepanova [ elenst ]

Elena Stepanova made changes - 2015-03-27 17:28

Status

Stalled [ 10000 ]

In Progress [ 3 ]

Elena Stepanova added a comment - 2015-03-27 18:21

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

Elena Stepanova added a comment - 2015-03-27 18:21 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

Elena Stepanova made changes - 2015-03-27 18:21

Attachment		mdev7367.test [ 37507 ]
Component/s		Storage Engine - InnoDB [ 10129 ]
Component/s		Virtual Columns [ 10803 ]
Fix Version/s		10.1 [ 16100 ]
Fix Version/s		10.0 [ 16000 ]
Fix Version/s	N/A [ 14700 ]
Affects Version/s		10.1 [ 16100 ]
Affects Version/s		10.0 [ 16000 ]
Affects Version/s	10.0.14 [ 17101 ]
Assignee	Elena Stepanova [ elenst ]	Jan Lindström [ jplindst ]
Due Date	2015-01-21
Labels		verified

Jan Lindström (Inactive) added a comment - 2015-03-30 12:17

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.

Jan Lindström (Inactive) added a comment - 2015-03-30 12:17 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.

Jan Lindström (Inactive) added a comment - 2015-03-31 09:18

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.

Jan Lindström (Inactive) added a comment - 2015-03-31 09:18 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.

Jan Lindström (Inactive) made changes - 2015-03-31 09:18

Component/s		Storage Engine - XtraDB [ 10135 ]
Fix Version/s		10.0.18 [ 18702 ]
Fix Version/s	10.0 [ 16000 ]
Fix Version/s	10.1 [ 16100 ]
Resolution		Fixed [ 1 ]
Status	In Progress [ 3 ]	Closed [ 6 ]

Rasmus Johansson (Inactive) made changes - 2015-05-18 17:50

Workflow

MariaDB v2 [ 59064 ]

MariaDB v3 [ 62359 ]

Marko Mäkelä made changes - 2017-10-09 06:27

Link

This issue relates to ~~MDEV-13838~~ [ ~~MDEV-13838~~ ]

Sergei Golubchik made changes - 2021-12-06 21:40

Workflow

MariaDB v3 [ 62359 ]

MariaDB v4 [ 148652 ]

People

Assignee:: Jan Lindström (Inactive)

Reporter:: Marlon Griffith

Votes:: 0 Vote for this issue

Watchers:: 4 Start watching this issue

Dates

Created:: 2014-12-23 21:15

Updated:: 2017-10-10 14:03

Resolved:: 2015-03-31 09:18

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Attachments

Issue Links

Activity

People

Dates

Git Integration