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

The value of "GENERATED COLUMN" is not updated

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.2.6, 10.2.7, 10.2.8, 10.2.9, 10.2.10, 10.2.11
    • Fix Version/s: 10.2.12
    • Labels:
      None
    • Environment:
      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

            People

            Assignee:
            Unassigned
            Reporter:
            Fukumoto Makoto Fukumoto
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: