Bug #28605 SHOW CREATE VIEW with views using stored_procedures no longer showing SP names.
Submitted: 22 May 2007 21:57 Modified: 28 Oct 2009 14:39
Reporter: Rolf Martin-Hoster Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S2 (Serious)
Version:5.0.41, 5.1 OS:Linux
Assigned to: Paul Dubois CPU Architecture:Any
Tags: regression
Triage: Needs Triage: D3 (Medium)

[22 May 2007 21:57] Rolf Martin-Hoster
Description:
We migrated our server to 5.0.41 this AM and everything has been running smooth. However this afternoon I noticed that the mysql dumps we use to propagate our testing enviroment were broken. It turns out that views using stored procedures are not showing the SP function name in SHOW CREATE VIEW. INFORMATION_SCHEMA.VIEWS does, however, show the correct information. Replacing the views fixes the problem.

How to repeat:
I've included dumps of show create view and i_s.view in private comments.
[23 May 2007 6:33] Sveta Smirnova
Thank you for the report.

I can not repeat described behaviour with sample data.

Please provide output of SHOW CREATE TABLE and SHOW CREATE FUNCTION  for all underlying tables and stored functions.
[23 May 2007 14:41] Rolf Martin-Hoster
I have recreated all of the functions under one of my other views and recreated the view it self and now I receive an error. On show create view or select :
mysql> show create view account_transfer_view;
ERROR 1241 (21000): Operand should contain 1 column(s)
mysql> select * from account_transfer_view;
ERROR 1241 (21000): Operand should contain 1 column(s)

This is the same error I would get when executing a dumped sql file.
[23 May 2007 15:09] Rolf Martin-Hoster
Things have gotten signifigantly worse. I attempted to recreated the functions and views on the live server and am getting operand errors. I have rolled back to 5.0.33 and able to drop/replace views and they are functioning again.

This is not limited to one view, any view with SP.
[23 May 2007 19:48] Sveta Smirnova
test case

Attachment: bug28605.test (application/octet-stream, text), 4.08 KiB.

[23 May 2007 19:50] Sveta Smirnova
Thank you for the feedback.

The error was verified. All versions are affected.

Error occured since 5.0.41
[29 May 2007 11:48] 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/27562

ChangeSet@1.2504, 2007-05-29 14:45:30+03:00, gkodinov@magare.gmz +3 -0
  Bug #28605: SHOW CREATE VIEW with views using stored_procedures no 
   longer showing SP names.
  SHOW CREATE VIEW uses Item::print() methods to reconstruct the 
  statement text from the parse tree.
  The print() method for stored procedure calls needs allocate 
  space to print the function's quoted name.
  It was incorrectly calculating the length of the buffer needed 
  (was too short).
  Fixed to reflect the actual space needed.
[4 Jun 2007 21:21] Bugs System
Pushed into 5.1.20-beta
[4 Jun 2007 21:23] Bugs System
Pushed into 5.0.44
[10 Jun 2007 18:55] Jon Stephens
Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Documented bugfix in 5.0.44 and 5.1.20 changelogs.
[26 Jul 2007 6:29] Sveta Smirnova
Bug #30057 was marked as duplicate of this one
[3 Sep 2009 17:56] Rolf Martin-Hoster
I had completely forgotten about this bug commit.

Today we upgraded our server from 5.0 to 5.1.37 and I am getting the same error.

Recreating the view seems to have corrected this issue however. Is there some other workaround for this?
[3 Sep 2009 18:32] Sveta Smirnova
Thank you for the feedback.

I am afraid there is no other workaround than recreate views after upgrade.
[3 Sep 2009 19:04] Rolf Martin-Hoster
Can this at least be noted in the upgrade documentation? Even with full backups this could seriously ruin people's days.
[21 Oct 2009 15:29] Valeriy Kravchuk
I think this manual page, http://dev.mysql.com/doc/refman/5.1/en/upgrading-from-previous-series.html, should be updated with the information from this bug report. I do not see this upgrade problem (views from 5.0.41-5.0.44 or older 5.1.x to current 5.1) documented anywhere.
[28 Oct 2009 14:39] Paul Dubois
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

Updated the page referenced in the previous comment:

Known issue: The fix for Bug#23491 introduced a problem with SHOW CREATE VIEW, which is used by mysqldump. This causes an incompatibility when upgrading from versions affected by that bug fix (MySQL 5.0.40 through 5.0.43, MySQL 5.1.18 through 5.1.19): If you use mysqldump before upgrading from an affected version and reload the data after upgrading to a higher version, you must drop and recreate your views.