Bug #5153 Views: "Not updatable" condition won't turn off

• Submitted: 23 Aug 2004 1:18
• Modified: 26 May 2006 17:38
• Reporter: Peter Gulutzan
• Status: Can't repeat
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.0.2-alpha-debug
• OS: Linux (SuSE 8.2)
• Assigned to: Oleksandr Byelkin
• CPU Architecture: Any

[23 Aug 2004 1:18] Peter Gulutzan

Description:
After a view has been flagged "not updatable" 
the view doesn't become "updatable" again. If 
I drop the view and create it again, and the 
view is now updatable, I still get the message 
"ERROR 1288 (HY000): The target table v of the 
INSERT is not updatable". 

How to repeat:
mysql> create table t (s1 int); 
Query OK, 0 rows affected (0.31 sec) 
 
mysql> create view v as select 1 from t; 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> create procedure p () insert into v values (5); 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> call p(); 
ERROR 1288 (HY000): The target table v of the INSERT is not updatable 
mysql> drop view v; 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> create view v as select * from t; 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> call p(); 
ERROR 1288 (HY000): The target table v of the INSERT is not updatable

[7 Sep 2004 13:09] Oleksandr Byelkin

Thank you for bugreport! It is known problem for stored procedures and prepared 
statements and it can't be solved until new table cache will be created. 
 
With new cache we will have list of objects which are depended on this table/view. In case 
of dropping/changing table/view stored procedure(s) which use it will be removed from 
cache, and prepared statement will be marked for reparsing.

[14 May 2006 21:40] Hartmut Holzgraefe

This seems to be fixed in current 5.0?

[16 May 2006 5:34] Oleksandr Byelkin

Sorry, but 5.0 is freeze, we can't do such big changes in it.

[26 May 2006 17:38] MySQL Verification Team

I was unable to repeat with current source:

miguel@hegel:~/dbs/5.0> bin/mysql -uroot db1
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.0.23-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table t (s1 int); 
Query OK, 0 rows affected (0.01 sec)

mysql> create view v as select 1 from t; 
Query OK, 0 rows affected (0.00 sec)

mysql> create procedure p () insert into v values (5); 
Query OK, 0 rows affected (0.00 sec)

mysql> call p();
ERROR 1288 (HY000): The target table v of the INSERT is not updatable
mysql> drop view v;
Query OK, 0 rows affected (0.00 sec)

mysql> create view v as select * from t; 
Query OK, 0 rows affected (0.00 sec)

mysql> call p(); 
Query OK, 1 row affected (0.01 sec)

mysql>

[9 Feb 2009 7:28] siva v

is this will work in Mysql 6.0 version??