| Bug #6120 | Stored procedure unaffected by ALTER VIEW | ||
|---|---|---|---|
| Submitted: | 15 Oct 2004 19:25 | Modified: | 26 Jul 2005 20:21 |
| Reporter: | Peter Gulutzan | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 5.0.2-alpha-debug | OS: | Linux (SuSE 8.2) |
| Assigned to: | Igor Babaev | CPU Architecture: | Any |
[6 Jul 2005 16:00]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/internals/26727
[11 Jul 2005 16:44]
Oleksandr Byelkin
after adding invalidation to DROP VIEW, it is OK
[12 Jul 2005 15:35]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/internals/26949
[12 Jul 2005 16:44]
Igor Babaev
As Sanja noticed in his review a similar problem exists for the DROP VIEW / CREATE VIEW scenario.
ChangeSet
1.1898 05/07/06 09:00:17 igor@igor-inspiron.creware.com +3 -0
view.result, view.test:
Added a test case for bug #6120.
sql_view.cc:
Fixed bug #6120.
The SP cache must be invalidated when a view is altered.
ChangeSet
1.1915 05/07/12 08:34:57 igor@igor-inspiron.creware.com +3 -0
view.test, view.result:
Expanded the test case for bug #6120 to cover
DROP VIEW / CREATE VIEW scenario.
sql_view.cc:
Expanded the fix for bug #6120 to cover the case of
DROP VIEW / CREATE_VIEW.
The fix will appear in 5.0.10
[26 Jul 2005 20:21]
Paul DuBois
Noted in 5.0.10 changelog.
Description: I have a procedure that depends on a view. I alter the view. I call the procedure. It's using the old definition of the view, as it was before the alter. Only after I do a data-change operation on the view, does it get through to the stored procedure that the view is different. How to repeat: mysql> create table t (s1 int, s2 int)// Query OK, 0 rows affected (0.42 sec) mysql> insert into t values (1,2)// Query OK, 1 row affected (0.00 sec) mysql> create view v as select s2 as s1, s1 as s2 from t// Query OK, 0 rows affected (0.01 sec) mysql> select * from v// +------+------+ | s1 | s2 | +------+------+ | 2 | 1 | +------+------+ 1 row in set (0.00 sec) mysql> create procedure p () select * from v// Query OK, 0 rows affected (0.00 sec) mysql> call p()// +------+------+ | s1 | s2 | +------+------+ | 2 | 1 | +------+------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) mysql> alter view v as select s1 as s1, s2 as s2 from t// Query OK, 0 rows affected (0.00 sec) mysql> call p()// +------+------+ | s1 | s2 | +------+------+ | 2 | 1 | +------+------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) mysql> insert into v values (1,2); -> // Query OK, 1 row affected (0.00 sec) mysql> call p()// +------+------+ | s1 | s2 | +------+------+ | 1 | 2 | | 1 | 2 | +------+------+ 2 rows in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) Suggested fix: Don't support ALTER VIEW!