[MDEV-13209] Cross-database operation with virtual columns fails Created: 2017-06-29  Updated: 2017-07-05  Resolved: 2017-07-05

Status: Closed
Project: MariaDB Server
Component/s: Virtual Columns
Affects Version/s: 10.2.6, 10.2
Fix Version/s: 10.2.7

Type: Bug Priority: Critical
Reporter: sysdljr Assignee: Sergei Golubchik
Resolution: Fixed Votes: 0
Labels: None
Environment:

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.



 Comments   
Comment by Elena Stepanova [ 2017-06-29 ]

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.

Comment by sysdljr [ 2017-06-30 ]

ok, Thank you rapid confirm and reply.

Generated at Thu Feb 08 08:03:46 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.