Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.0(EOL), 10.1(EOL)
-
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
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