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

Cross-database operation with virtual columns fails

Details

    • Bug
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Fixed
    • 10.2.6, 10.2(EOL)
    • 10.2.7
    • Virtual Columns
    • None
    • CentOS 6.7

    Description

      Hi, all
      we encountered a problem in 10.2.6 slave.
      we could not confirm ,it is bug or other, please experts help to confirm, thanks.

      our product server consist of a master and two slaves:
      master: MariaDB server 10.1.22
      slave1: MariaDB server 10.1.22
      slave2: MariaDB server 10.2.6 (delay slave)

      use table structure:

      CREATE TABLE dba.event_time(
      		 
        `id` INT(11) NOT NULL AUTO_INCREMENT,
      		 
        `dbname` VARCHAR(50) COLLATE utf8_bin DEFAULT NULL  ,
      		 
        `eventname` VARCHAR(50) COLLATE utf8_bin DEFAULT NULL  ,
      		 
        `startime` DATETIME DEFAULT NULL  ,
      		 
        `endtime` DATETIME DEFAULT NULL  ,
      		 
        `recdate` DATE DEFAULT NULL  ,
      		 
        `duration` INT(11) AS (timestampdiff(SECOND,startime,endtime)) PERSISTENT ,
      		 
        PRIMARY KEY (`id`),
      		 
        UNIQUE KEY `dbname` (`dbname`,`eventname`,`recdate`)
      		 
      ) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

      on master, after execute alter statement , slave1 could normal run.

      ALTER TABLE `dba`.`event_time`  ADD INDEX (`startime`);

      but ,on slave2, slave stop run ,and have some error in error log:

      2017-06-28 23:59:50 140041235142400 [ERROR] Slave SQL: Error 'Unknown column 'dba.event_time.startime' in 'GENERATED ALWAYS AS'' on query. Default database: ''. Query: 'ALTER TABLE `dba`.`event_time`  ADD INDEX (`startime`)', Gtid 0-5187-24322365258, Internal MariaDB error code: 1054
      		 
      2017-06-28 23:59:50 140041235142400 [Warning] Slave: Unknown column 'dba.event_time.startime' in 'GENERATED ALWAYS AS' Error_code: 1054
      		 
      2017-06-28 23:59:50 140041235142400 [Warning] Slave: Got an error evaluating stored expression timestampdiff(SECOND,`dba`.`event_time`.`startime`,`dba`.`event_time`.`endtime`) Error_code: 4027
      		 
      2017-06-28 23:59:50 140041235142400 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.008350' position 693638501; GTID position '0-5187-24322365257'

      if manual execute ALTER TABLE `dba`.`event_time` ADD INDEX (`startime`) on slave2, it could normal run.

      Attachments

        Activity

          Ascending order - Click to sort in descending order
          elenst Elena Stepanova added a comment - - edited

          Thanks for the report. It is indeed a bug.

          The problem triggered by default database being empty. In a more general case, it can be a cross-database operation.

          USE test;
          		 
          CREATE DATABASE db;
          		 
           
          		 
          CREATE TABLE db.t1 (i INT, j INT AS (i) PERSISTENT);
          		 
          SHOW CREATE TABLE db.t1;
          		 
           
          		 
          ALTER TABLE db.t1 ADD INDEX (i);
          		 
           
          		 
          # Cleanup
          		 
          DROP DATABASE db;

          Results

          		 
          MariaDB [test]> SHOW CREATE TABLE db.t1;
          		 
          +-------+
          		 
          | Table | Create Table                                                                                                                                    +-------+
          		 
          | t1    | CREATE TABLE `t1` (
          		 
            `i` int(11) DEFAULT NULL,
          		 
            `j` int(11) GENERATED ALWAYS AS (`db`.`t1`.`i`) STORED
          		 
          ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
          		 
          +-------+
          		 
          1 row in set (0.01 sec)
          		 
           
          		 
          MariaDB [test]> 
          MariaDB [test]> ALTER TABLE db.t1 ADD INDEX (i);
          		 
          ERROR 1054 (42S22): Unknown column 'db.t1.i' in 'GENERATED ALWAYS AS'

          10.1 works okay.

          elenst Elena Stepanova added a comment - - edited Thanks for the report. It is indeed a bug. The problem triggered by default database being empty. In a more general case, it can be a cross-database operation. USE test; CREATE DATABASE db;   CREATE TABLE db.t1 (i INT , j INT AS (i) PERSISTENT); SHOW CREATE TABLE db.t1;   ALTER TABLE db.t1 ADD INDEX (i);   # Cleanup DROP DATABASE db; Results MariaDB [test]> SHOW CREATE TABLE db.t1; + -------+ | Table | Create Table + -------+ | t1 | CREATE TABLE `t1` ( `i` int (11) DEFAULT NULL , `j` int (11) GENERATED ALWAYS AS (`db`.`t1`.`i`) STORED ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | + -------+ 1 row in set (0.01 sec)   MariaDB [test]> MariaDB [test]> ALTER TABLE db.t1 ADD INDEX (i); ERROR 1054 (42S22): Unknown column 'db.t1.i' in 'GENERATED ALWAYS AS' 10.1 works okay.
          sysdljr sysdljr added a comment -

          ok, Thank you rapid confirm and reply.

          sysdljr sysdljr added a comment - ok, Thank you rapid confirm and reply.

          People

            serg Sergei Golubchik
            sysdljr sysdljr
            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.