Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-7367

Updating a virtual column corrupts table which crashes server

    XMLWordPrintable

Details

    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

        1. grad_degree_build_bug.sql
          6 kB
          Elena Stepanova
        2. gso_grad_supr.csv
          1 kB
          Elena Stepanova
        3. mdev7367.test
          9 kB
          Elena Stepanova

        Issue Links

          Activity

            People

              jplindst Jan Lindström (Inactive)
              m3griffi Marlon Griffith
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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