MySQL Bugs: #28605: SHOW CREATE VIEW with views using stored

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

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.

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.

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.

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.

test case

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

Thank you for the feedback.

The error was verified. All versions are affected.

Error occured since 5.0.41

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.

Pushed into 5.1.20-beta

Pushed into 5.0.44

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.

Bug #30057 was marked as duplicate of this one

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?

Thank you for the feedback.

I am afraid there is no other workaround than recreate views after upgrade.

Can this at least be noted in the upgrade documentation? Even with full backups this could seriously ruin people's days.

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.

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.