Description:
A function (updateVIEW) updates an updatable view (viewA), that's a view on a table (tableA) that has an update trigger that updates another table (journalA).
The function fails with "table journalA not found".
The same update of the view done outside of a function succeeds.
A similar function (UpdateTable) that updates tableA directly succeeds.
The same update of the view done in a procedure succeeds.
console log:
mysql> DELIMITER ^
mysql> drop database if exists bug ^
Query OK, 3 rows affected (0.00 sec)
mysql> create database bug ^
Query OK, 1 row affected (0.00 sec)
mysql> create table bug.tableA select 1 as keyA,'A' as fieldA ^
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> create view bug.viewA as select * from bug.tableA ^
Query OK, 0 rows affected (0.00 sec)
mysql> create table bug.journalA (keyA int, fieldA char(1)) ^
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE trigger bug.tableA_trigger_update AFTER update on bug.tableA
-> for each row insert into bug.journalA (keyA, fieldA) values (new.keyA, new.fieldA);
-> ^
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE function bug.updateTable() returns int
-> begin
-> update bug.tableA set fieldA='B' where keyA='1';
-> return row_count();
-> end
-> ^
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE function bug.updateView() returns int
-> begin
-> update bug.viewA set fieldA='B' where keyA='1';
-> return row_count();
-> end
-> ^
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE procedure bug.updateView() update bug.viewA set fieldA='B' where keyA='1';
-> ^
Query OK, 0 rows affected (0.00 sec)
mysql> /* update the view in procedure: succeeds */
mysql> call bug.updateView()
-> ^
Query OK, 1 row affected (0.00 sec)
mysql> select bug.updateTable(),version()
-> ^
+-------------------+-----------+
| bug.updateTable() | version() |
+-------------------+-----------+
| 0 | 5.1.73 |
+-------------------+-----------+
1 row in set (0.00 sec)
mysql> /* update the view directly: succeeds */
mysql> update bug.viewA set fieldA='B' where keyA='1';
-> ^
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0
mysql> /* update the view din function: FAILS with ERROR 1146 (42S02): Table 'bug.journalA' doesn't exist*/
mysql> select bug.updateView()
-> ^
ERROR 1146 (42S02): Table 'bug.journalA' doesn't exist
mysql> /* yes it does exist! */
mysql> show create table bug.journalA^
+----------+---------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------+---------------------------------------------------------------------------------------------------------------------------------+
| journalA | CREATE TABLE `journala` (
`keyA` int(11) DEFAULT NULL,
`fieldA` char(1) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+----------+---------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
How to repeat:
DELIMITER ^
drop database if exists bug ^
create database bug ^
create table bug.tableA select 1 as keyA,'A' as fieldA ^
create view bug.viewA as select * from bug.tableA ^
create table bug.journalA (keyA int, fieldA char(1)) ^
CREATE trigger bug.tableA_trigger_update AFTER update on bug.tableA
for each row insert into bug.journalA (keyA, fieldA) values (new.keyA, new.fieldA);
^
CREATE function bug.updateTable() returns int
begin
update bug.tableA set fieldA='B' where keyA='1';
return row_count();
end
^
CREATE function bug.updateView() returns int
begin
update bug.viewA set fieldA='B' where keyA='1';
return row_count();
end
^
CREATE procedure bug.updateView() update bug.viewA set fieldA='B' where keyA='1';
^
/* update the view in procedure: succeeds */
call bug.updateView()
^
select bug.updateTable(),version()
^
/* update the view directly: succeeds */
update bug.viewA set fieldA='B' where keyA='1';
^
/* update the view din function: FAILS with ERROR 1146 (42S02): Table 'bug.journalA' doesn't exist*/
select bug.updateView()
^
/* yes it does exist! */
show create table bug.journalA^
Suggested fix:
Find the table!
Description: A function (updateVIEW) updates an updatable view (viewA), that's a view on a table (tableA) that has an update trigger that updates another table (journalA). The function fails with "table journalA not found". The same update of the view done outside of a function succeeds. A similar function (UpdateTable) that updates tableA directly succeeds. The same update of the view done in a procedure succeeds. console log: mysql> DELIMITER ^ mysql> drop database if exists bug ^ Query OK, 3 rows affected (0.00 sec) mysql> create database bug ^ Query OK, 1 row affected (0.00 sec) mysql> create table bug.tableA select 1 as keyA,'A' as fieldA ^ Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> create view bug.viewA as select * from bug.tableA ^ Query OK, 0 rows affected (0.00 sec) mysql> create table bug.journalA (keyA int, fieldA char(1)) ^ Query OK, 0 rows affected (0.00 sec) mysql> CREATE trigger bug.tableA_trigger_update AFTER update on bug.tableA -> for each row insert into bug.journalA (keyA, fieldA) values (new.keyA, new.fieldA); -> ^ Query OK, 0 rows affected (0.00 sec) mysql> CREATE function bug.updateTable() returns int -> begin -> update bug.tableA set fieldA='B' where keyA='1'; -> return row_count(); -> end -> ^ Query OK, 0 rows affected (0.00 sec) mysql> CREATE function bug.updateView() returns int -> begin -> update bug.viewA set fieldA='B' where keyA='1'; -> return row_count(); -> end -> ^ Query OK, 0 rows affected (0.00 sec) mysql> CREATE procedure bug.updateView() update bug.viewA set fieldA='B' where keyA='1'; -> ^ Query OK, 0 rows affected (0.00 sec) mysql> /* update the view in procedure: succeeds */ mysql> call bug.updateView() -> ^ Query OK, 1 row affected (0.00 sec) mysql> select bug.updateTable(),version() -> ^ +-------------------+-----------+ | bug.updateTable() | version() | +-------------------+-----------+ | 0 | 5.1.73 | +-------------------+-----------+ 1 row in set (0.00 sec) mysql> /* update the view directly: succeeds */ mysql> update bug.viewA set fieldA='B' where keyA='1'; -> ^ Query OK, 0 rows affected (0.00 sec) Rows matched: 1 Changed: 0 Warnings: 0 mysql> /* update the view din function: FAILS with ERROR 1146 (42S02): Table 'bug.journalA' doesn't exist*/ mysql> select bug.updateView() -> ^ ERROR 1146 (42S02): Table 'bug.journalA' doesn't exist mysql> /* yes it does exist! */ mysql> show create table bug.journalA^ +----------+---------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +----------+---------------------------------------------------------------------------------------------------------------------------------+ | journalA | CREATE TABLE `journala` ( `keyA` int(11) DEFAULT NULL, `fieldA` char(1) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 | +----------+---------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> How to repeat: DELIMITER ^ drop database if exists bug ^ create database bug ^ create table bug.tableA select 1 as keyA,'A' as fieldA ^ create view bug.viewA as select * from bug.tableA ^ create table bug.journalA (keyA int, fieldA char(1)) ^ CREATE trigger bug.tableA_trigger_update AFTER update on bug.tableA for each row insert into bug.journalA (keyA, fieldA) values (new.keyA, new.fieldA); ^ CREATE function bug.updateTable() returns int begin update bug.tableA set fieldA='B' where keyA='1'; return row_count(); end ^ CREATE function bug.updateView() returns int begin update bug.viewA set fieldA='B' where keyA='1'; return row_count(); end ^ CREATE procedure bug.updateView() update bug.viewA set fieldA='B' where keyA='1'; ^ /* update the view in procedure: succeeds */ call bug.updateView() ^ select bug.updateTable(),version() ^ /* update the view directly: succeeds */ update bug.viewA set fieldA='B' where keyA='1'; ^ /* update the view din function: FAILS with ERROR 1146 (42S02): Table 'bug.journalA' doesn't exist*/ select bug.updateView() ^ /* yes it does exist! */ show create table bug.journalA^ Suggested fix: Find the table!