MariaDB

  1. Bug #1006160
  2. Comment #5

Comment 5 for bug 1006160

Revision history for this message
Elena Stepanova (elenst) wrote : Re: 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'

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;