View used via a trigger references old columns

[nbrnhardt]

MariaDB 5.5.23 on Windows 32 bits tiggers following message in Windows application log:

Slave SQL: Query caused different errors on master and slave. Error on master: message (format)='View '%-.192s.%-.192s' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them' error code=1356 ; Error on slave: actual message='no error', error code=0. Default database: 'dbweilandt2'. Query: 'UPDATE vorgangsliste SET gerätestatusid = 4,gerätestatusid=4,garantie=NULL,`timestamp`= NOW() WHERE vorgangsnr = 190655', Error_code: 0

Before that, a column name was changed (which usually triggers a "...references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights..." on views that use the old column name. At the time the above error was triggered, all views with reference to the changed column used the new column name. The only explanation is that somewhere in cache or Aria log file there was still the old name. "Vorgangsliste" is a table that triggers some views on update or on insert, but none of these triggers have references to the changed table.

Table format is Aria.

The only workaround to get the master working like it should was to stop and restart it.

The master has been working for some weeks. Is there any chance to reproduce the problem?

[Elena Stepanova (elenst)]

Hi,

> "Vorgangsliste" is a table that triggers some views on update or on insert,
> but none of these triggers have references to the changed table.

I'm not quite sure I understand what you mean by this.
Are you saying that "UPDATE Vorgangsliste" complains about invalid references in some view X, even although neither the UPDATE itself, nor any triggers on Vorgangsliste, nor any secondary triggers which might be activated by this statement use this view?

[nbrnhardt (nb-k)]

Table V (vorgangsliste) starts a trigger that updates table B, using a SELECT from a VIEW that references table A.

I changed a column name in table A and all corresponding VIEWS that use it.

The EXPECTED bahavior is that MariaDB should only use the new column name. Nontheless, on the master server it complains about invalid references. Maybe the VIEW was stored in memory/cache with the old column name and did not refresh when it got updated?

The error disappeared on the master server when I restarted it. One of the slave uses the same system (Windows 32bit) and configurration file as the master, but here no error occured.

I try to reproduce the problem and post it here if I am successful. You would need a couple of tables, JOIN them in a VIEW, create another table with a trigger ON UPDATE that SELECTs the VIEW, and save changes from that view in another tables. Reference is always an ID as PRIMARY KEY.

[nbrnhardt (nb-k)]

Example
=======

Table V is updated --> trigger is run ON UPDATE

Trigger does (simplified)
REPLACE INTO another_database.statistics
SELECT * FROM VIEW vw
WHERE NEW.id = vw.id

VIEW does a SELECT FROM a JOIN b USING(id) JOIN c USING(id)
whereas id is the PRIMARY KEY.

Column name in table B gets changed, as well as the respective name in VIEW.
A select from VIEW is OK, does what it should, but the trigger fails on master server, but not on slave.

[nbrnhardt (nb-k)]

Okay, I could reproduce it.

Run as SQL:
=== SQL BEGIN ===
CREATE DATABASE `test` /*!40100 CHARACTER SET utf8 COLLATE 'utf8_general_ci' */;

USE `test`;

CREATE TABLE `a` (
 `id` INT(10) NOT NULL,
 `something` VARCHAR(50) NULL,
 PRIMARY KEY (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=Aria;

CREATE TABLE `b` (
 `id` INT(10) NOT NULL,
 `number` INT(10) NULL,
 PRIMARY KEY (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=Aria;

CREATE TABLE `master` (
 `id` INT(10) NOT NULL AUTO_INCREMENT,
 `col2` INT(10) NULL DEFAULT '0',
 `col3` INT(10) NULL DEFAULT '0',
 `col4` VARCHAR(50) NULL DEFAULT '0',
 PRIMARY KEY (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=Aria;

CREATE TABLE `v` (
 `id` INT(10) NULL,
 `Column 2` INT(10) NULL,
 `Column 3` INT(10) NULL,
 `Column 4` INT(10) NULL,
 PRIMARY KEY (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=Aria;

CREATE ALGORITHM = MERGE DEFINER=`root`@`localhost` VIEW `view` AS SELECT id,col2,col3,col4,something,number,`Column 2`,`Column 4`
FROM master
LEFT JOIN a USING(id)
LEFT JOIN b USING(id)
LEFT JOIN v USING(id) ;

CREATE DATABASE `test2` /*!40100 CHARACTER SET utf8 COLLATE 'utf8_general_ci' */;

USE `test2`;

CREATE TABLE `stats` (
 `id` INT(10) NOT NULL DEFAULT '0',
 `fa` INT(10) NULL DEFAULT NULL,
 `fb` INT(10) NULL DEFAULT NULL,
 `fc` VARCHAR(50) NULL DEFAULT NULL,
 `fd` VARCHAR(50) NULL DEFAULT NULL,
 `fe` INT(10) NULL DEFAULT NULL,
 `ff` INT(10) NULL DEFAULT NULL,
 `fg` INT(10) NULL DEFAULT NULL
)
COLLATE='utf8_general_ci'
ENGINE=Aria;

USE test;
=== SQL END ===

Now, create the trigger:

CREATE TRIGGER `t_v` AFTER UPDATE ON `v` FOR EACH ROW BEGIN

 REPLACE INTO test2.stats (id,fa,fb,fc,fd,ff)
 SELECT id,col2,col4,something,`Column 2`,1
 FROM view WHERE NEW.id=id;

END;

And run more SQL:
=== SQL BEGIN ===
INSERT INTO `master` (`col2`, `col3`, `col4`) VALUES (1, 2, '3');
INSERT INTO `master` (`col2`, `col3`, `col4`) VALUES (3, 4, '6');
INSERT INTO `master` (`col2`, `col3`) VALUES (33, 2);

INSERT INTO `v` (`id`, `Column 2`, `Column 3`, `Column 4`) VALUES (1, 123, 234, 345);
INSERT INTO `v` (`id`, `Column 2`, `Column 3`, `Column 4`) VALUES (2, 123, 234, 345);
INSERT INTO `v` (`id`, `Column 2`, `Column 3`, `Column 4`) VALUES (3, 123, 234, 345);

INSERT INTO `a` (`id`, `something`) VALUES (1, 'It');
INSERT INTO `a` (`id`, `something`) VALUES (2, 'is');
INSERT INTO `a` (`id`, `something`) VALUES (3, 'OK');

INSERT INTO `b` (`id`, `number`) VALUES (1, 2);
INSERT INTO `b` (`id`, `number`) VALUES (2, 6);
INSERT INTO `b` (`id`, `number`) VALUES (3, 5);

/* Do some update to start TRIGGER */
UPDATE `v` SET `Column 2`=2 WHERE `id`=3 LIMIT 1;

/* ALTER COLUMN NAME IN TABLE */
ALTER TABLE `a`
 CHANGE COLUMN `something` `else` VARCHAR(50) NULL DEFAULT NULL AFTER `id`;

/* ALTER COLUMN NAME IN CORRESPONDING VIEW */
ALTER DEFINER=`root`@`localhost` VIEW `view` AS SELECT id,col2,col3,col4,`else`,number,`Column 2`,`Column 4`
FROM master
LEFT JOIN a USING(id)
LEFT JOIN b USING(id)
LEFT JOIN v USING(id) ;

/* Do some update to start TRIGGER again*/
UPDATE `v` SET `Column 2`=15 WHERE `id`=3 LIMIT 1;
=== SQL END ===

My Mar...

[Elena Stepanova (elenst)]

Hi,

>> Can you confirm?

Yes, I can confirm the usage of the old name when SELECT is invoked by a trigger. A shorter test case is below.
The problem exists in all of MySQL 5.1-5.6 and MariaDB 5.1-5.5. It might be a variation of bug http://bugs.mysql.com/bug.php?id=33000 or a related problem.
Running FLUSH TABLES after you updated your structures seems to be a viable workaround.

However, I could not reproduce the replication error. Maybe something that happened on the slave between changing the structures and using the trigger caused flushing tables, explicitly or implicitly.

Test case (for old column usage):

CREATE TABLE t1 (id INT, oldname VARCHAR(3));
CREATE TABLE t2 (id INT);
CREATE TABLE t3 LIKE t1;

CREATE VIEW v AS SELECT id, oldname FROM t1;

CREATE TRIGGER tr AFTER UPDATE ON t2 FOR EACH ROW
  INSERT INTO t3 SELECT * FROM v WHERE NEW.id = id;

INSERT INTO t2 VALUES (1),(2),(3);
INSERT INTO t1 VALUES (3,'It'),(4,'is'),(5,'OK');

UPDATE t2 SET id=4 WHERE id=3;

ALTER TABLE t1 CHANGE COLUMN oldname newname VARCHAR(3);

CREATE OR REPLACE VIEW v AS SELECT id, newname FROM t1;

# FLUSH TABLES; # workaround

UPDATE t2 SET id=15 WHERE id=4;