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

The value of "GENERATED COLUMN" is not updated

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.2.6, 10.2.7, 10.2.8, 10.2.9, 10.2.10, 10.2.11
    • 10.2.12
    • None
    • CentOS7

    Description

      In versions up to 10.2.11, the following procedure does not update the "GENERATED COLUMN" value of some records.

      CREATE TABLE `t1` (
        `col1` bigint(20) NOT NULL AUTO_INCREMENT,
        `col2` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
        `col3` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
        `col4` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
        `col5` varchar(100) GENERATED ALWAYS AS (concat_ws('\n',`col3`,`col4`)) STORED,
        PRIMARY KEY (`col1`),
        KEY i1 (col2)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
      

      #!/bin/bash
      for i in {1..500}; do
          mysql -u root test -e "INSERT INTO t1 (col1, col2, col3, col4) VALUES (${i}, 'AAA', '', ''); UPDATE t1 SET col3 = 'BBB', col4 = 'CCC' WHERE col1 = ${i} AND col2 = 'AAA';"
      done
      

      $ mysql -u root test -e "select count(*) from t1 where col5 not like 'BBB%'"
      +----------+
      | count(*) |
      +----------+
      |       31 |
      +----------+
      

      $ mysql -u root test -e "select * from t1 where col1 = 499\G"
      *************************** 1. row ***************************
      col1: 499
      col2: AAA
      col3: BBB
      col4: CCC
      col5:
       
      $
      

      If you delete the index of "i1" or delete the condition of col2 from the "UPDATE Query" where condition, it will not be reproduced, so I think this is the effect of the secondary index.

      Attachments

        Activity

          alice Alice Sherepa added a comment - - edited

          Thanks for the report. Reproducible on versions <=10.2.11, not repeatable on >=10.2.12
          Please upgrade to the recent version of the server.

          MariaDB [test]> CREATE or replace TABLE `t1` (
              ->   `col1` bigint(20) NOT NULL AUTO_INCREMENT,
              ->   `col2` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
              ->   `col3` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
              ->   `col4` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
              ->   `col5` varchar(100) GENERATED ALWAYS AS (concat_ws('\n',`col3`,`col4`)) STORED,
              ->   PRIMARY KEY (`col1`),
              ->   KEY i1 (col2)
              -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
          Query OK, 0 rows affected (0.02 sec)
           
          MariaDB [test]> DELIMITER $$
          MariaDB [test]> CREATE PROCEDURE prepare_data()
              -> BEGIN
              ->   DECLARE i INT DEFAULT 1;
              -> 
              ->   WHILE i < 500 DO
              ->   INSERT INTO t1 (col1, col2, col3, col4) VALUES (i, 'AAA', '', ''); 
              ->   UPDATE t1 SET col3 = 'BBB', col4 = 'CCC' WHERE col1 = i AND col2 = 'AAA';
              ->     SET i = i + 1;
              ->   END WHILE;
              -> END$$
          Query OK, 0 rows affected (0.00 sec)
           
          MariaDB [test]> DELIMITER ;
          MariaDB [test]> CALL prepare_data();
          Query OK, 1 row affected (5.03 sec)
           
          MariaDB [test]> select * from t1 where col1 = 499\G
          *************************** 1. row ***************************
          col1: 499
          col2: AAA
          col3: BBB
          col4: CCC
          col5: BBB
          CCC
          1 row in set (0.00 sec)
           
          MariaDB [test]> select version();
          +-----------------+
          | version()       |
          +-----------------+
          | 10.2.12-MariaDB |
          +-----------------+
          1 row in set (0.00 sec)
          

          alice Alice Sherepa added a comment - - edited Thanks for the report. Reproducible on versions <=10.2.11, not repeatable on >=10.2.12 Please upgrade to the recent version of the server. MariaDB [test]> CREATE or replace TABLE `t1` ( -> `col1` bigint(20) NOT NULL AUTO_INCREMENT, -> `col2` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL, -> `col3` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL, -> `col4` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL, -> `col5` varchar(100) GENERATED ALWAYS AS (concat_ws('\n',`col3`,`col4`)) STORED, -> PRIMARY KEY (`col1`), -> KEY i1 (col2) -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; Query OK, 0 rows affected (0.02 sec)   MariaDB [test]> DELIMITER $$ MariaDB [test]> CREATE PROCEDURE prepare_data() -> BEGIN -> DECLARE i INT DEFAULT 1; -> -> WHILE i < 500 DO -> INSERT INTO t1 (col1, col2, col3, col4) VALUES (i, 'AAA', '', ''); -> UPDATE t1 SET col3 = 'BBB', col4 = 'CCC' WHERE col1 = i AND col2 = 'AAA'; -> SET i = i + 1; -> END WHILE; -> END$$ Query OK, 0 rows affected (0.00 sec)   MariaDB [test]> DELIMITER ; MariaDB [test]> CALL prepare_data(); Query OK, 1 row affected (5.03 sec)   MariaDB [test]> select * from t1 where col1 = 499\G *************************** 1. row *************************** col1: 499 col2: AAA col3: BBB col4: CCC col5: BBB CCC 1 row in set (0.00 sec)   MariaDB [test]> select version(); +-----------------+ | version() | +-----------------+ | 10.2.12-MariaDB | +-----------------+ 1 row in set (0.00 sec)

          What fixes did this improve with version 10.2.12?
          I thought the secondary index was a problem, but looking at the Change Log, I couldn't figure out which fix helped.

          Fukumoto Makoto Fukumoto added a comment - What fixes did this improve with version 10.2.12? I thought the secondary index was a problem, but looking at the Change Log, I couldn't figure out which fix helped.

          People

            Unassigned Unassigned
            Fukumoto Makoto Fukumoto
            Votes:
            0 Vote for this issue
            Watchers:
            3 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.