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,

> "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?

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'
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.

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'
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.

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'
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 MariaDB server throws SQL error 1356: View 'test.view' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

While

UPDATE `v` SET `Column 2`=15 WHERE `id`=3 LIMIT 1;

produces the error, doing a

SELECT * FROM view

is working fine:

/* Affected rows: 0 Found rows: 3 Warnings: 0 Duration for 1 query: 0,016 sec. */

Can you confirm?

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;

1. FLUSH TABLES; # workaround

UPDATE t2 SET id=15 WHERE id=4;

Launchpad bug id: 1006160

wil be fixed in 10.0 when we merge WL#4179 "Stored programs: validation of stored program statements" from 5.6