| Bug #47313 | assert in check_key_in_view during CALL procedure | ||
|---|---|---|---|
| Submitted: | 14 Sep 2009 15:30 | Modified: | 7 Mar 2010 1:43 |
| Reporter: | Matthias Leich | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Stored Routines | Severity: | S3 (Non-critical) |
| Version: | 5.4 | OS: | Any |
| Assigned to: | Jon Olav Hauglid | CPU Architecture: | Any |
[14 Sep 2009 15:30]
Matthias Leich
[25 Sep 2009 13:13]
Tor Didriksen
An even simpler test case: delimiter |; CREATE PROCEDURE p1 () BEGIN UPDATE t1 SET f1 = 9 LIMIT 1; END| delimiter ;| CREATE VIEW t1 AS SELECT 10 as f1; --error ER_NON_UPDATABLE_TABLE CALL p1; CREATE TEMPORARY TABLE t1 (f1 int); CALL p1;
[25 Sep 2009 13:28]
Tor Didriksen
delimiter |; CREATE PROCEDURE p1 () BEGIN UPDATE t1 SET f1 = 9 LIMIT 1; END| delimiter ;| CREATE VIEW t1 AS SELECT 10 as f1; --error ER_NON_UPDATABLE_TABLE CALL p1; CREATE TEMPORARY TABLE t1 (f1 int); --error ER_NON_UPDATABLE_TABLE # MySQL 5.4.3-beta-debug returns error here # MySQL 6.0.14-alpha-debug dumps core CALL p1; drop procedure p1; drop view t1;
[14 Oct 2009 11:29]
Matthias Leich
Some attempts on other source trees I have assertion tree --------------------------- no mysql-trunk-signal 2009-09-23 no mysql-5.1-bugteam 2009-10-06
[22 Oct 2009 14:29]
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/commits/87781 3661 Jon Olav Hauglid 2009-10-22 Bug #47313 assert in check_key_in_view during CALL procedure View definitions are inlined in a stored procedure when the procedure is fist called. This means that if a temporary table is later added with the same name as the view, the stored procedure will still use the view. This happens even if temporary tables normally shadow base tables/views. The reason for the assert was that even if the stored procedure referenced the view, open_table() still tried to open the temporary table. This "half view/half temporary table" state caused the assert. The bug was not present in 5.1 as open_table() is not called for the view there. This code was changed with the introduction of MDL in order to properly lock the view and any objects it refers to. This patch restores the 5.1 behavior by instructing open_table() to open base tables/views (using OT_BASE_ONLY) when reopening tables/views used by stored procedures. Test case added to sp.test. The test case also demonstrates the effect of sp cache invalidation between CALLs. The test case has been verified on the 5.1 and next-mr codebases.
[24 Oct 2009 13:29]
Konstantin Osipov
Sent a review by email.
[26 Oct 2009 16:17]
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/commits/88203 3673 Jon Olav Hauglid 2009-10-26 Bug #47313 assert in check_key_in_view during CALL procedure View definitions are inlined in a stored procedure when the procedure is fist called. This means that if a temporary table is later added with the same name as the view, the stored procedure will still use the view. This happens even if temporary tables normally shadow base tables/views. The reason for the assert was that even if the stored procedure referenced the view, open_table() still tried to open the temporary table. This "half view/half temporary table" state caused the assert. The bug was not present in 5.1 as open_table() is not called for the view there. This code was changed with the introduction of MDL in order to properly lock the view and any objects it refers to. This patch fixes the problem by instructing open_table() to open base tables/views (using OT_BASE_ONLY) when reopening tables/views used by stored procedures. This also means that a prepared statement is no longer invalidated if a temporary table is created with the same name as a view used in the prepared statement. Test case added to sp.test. The test case also demonstrates the effect of sp cache invalidation between CALLs. @ mysql-test/t/ps_ddl.test Extended the VIEW->TEMPORARY TABLE transition test to cover not only merged views, but now also materialized views and views containing a reference to an information schema table. Test also updated to reflect the change to prepared statement invalidatation.
[26 Oct 2009 16:19]
Jon Olav Hauglid
Pushed to mysql-6.0-codebase (version 6.0.14-alpha). Will be backported to mysql-next (version 5.5.0-beta).
[31 Oct 2009 8:19]
Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20091031081410-qkxmjsdzjmj840aq) (version source revid:jon.hauglid@sun.com-20091026161648-s5ygxe9fn9ks8qov) (merge vers: 6.0.14-alpha) (pib:13)
[17 Nov 2009 23:16]
Paul DuBois
Noted in 6.0.14 changelog. If a temporary table was created with the same name as a view referenced in a stored routine, routine execution could raise an assertion. Setting report to NDI pending push into 5.5.x.
[10 Dec 2009 12:38]
Jon Olav Hauglid
Pushed in mysql-next-4284 (5.6.0-beta).
[16 Feb 2010 16:47]
Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100216101445-2ofzkh48aq2e0e8o) (version source revid:kostja@sun.com-20091211154405-c9yhiewr9o5d20rq) (merge vers: 6.0.14-alpha) (pib:16)
[16 Feb 2010 16:57]
Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100216101208-33qkfwdr0tep3pf2) (version source revid:jon.hauglid@sun.com-20091210123718-t7858zpdq2daojno) (pib:16)
[17 Feb 2010 1:14]
Paul DuBois
Setting report to Need Merge pending push of Celosia into release tree.
[6 Mar 2010 10:57]
Bugs System
Pushed into 5.5.3-m3 (revid:alik@sun.com-20100306103849-hha31z2enhh7jwt3) (version source revid:vvaintroub@mysql.com-20100216221947-luyhph0txl2c5tc8) (merge vers: 5.5.99-m3) (pib:16)
[7 Mar 2010 1:43]
Paul DuBois
Noted in 5.5.3 changelog.
