MySQL Bugs: #60645: MySQL Workbench Export views and routines fails

Bug #60645	MySQL Workbench Export views and routines fails
Submitted:	25 Mar 2011 17:23	Modified:	14 May 2012 11:26
Reporter:	Kim Upper	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Workbench: Administration	Severity:	S2 (Serious)
Version:	5.2.33 CD rev 7508	OS:	Windows (Windows 7)
Assigned to:		CPU Architecture:	Any
Tags:	export, Views

Description:
I don't know if this bug is in mysqldump or in the interface to dump used by the Workbench.

We've been making some major changes in our development database. I have a stored procedure failing because View xyz references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them.

I am attempting to get back to the code for this view. I don't care if that code is broken or not, I just need the original code so I can figure out what's wrong with that view and fix it. Alter view returns nothing. Send create statement to SQL editor sends me an empty statement -- delimiter $$ [cr][cr]$$.

I then went to my database backups. The routines.sql file is missing and evidently has been missing for some months. I have been creating the backups for our development database using the dump feature from mysql workbench with dump views and dump routines checked.

On discovering I coudln't find the routines.sql, I attempted database dumps today, both as a single file and as one file per table. The routines.sql was never written. It appears that an error on my broken view is causing the dump of routines.sql to abort. Therefore, NONE of my views and NONE of my stored procedures get backed up. AND, I CANNOT get back to my original code to figure out what's wrong with my view.

We need to be able to obtain backups when broken views and routines exist. In addition, it would be really nice to be able to get back to the code for broken views and routines so they can be fixed. Other database management systems I have used allow this. I say the failure to backup all routines and views when one is broken is critical or near critical. The inability to return to the code for a broken view is merely highly annoying.

How to repeat:
please see above

Suggested fix:
Short term/immediate fix, allow other views and stored procedures to be dumped even though the broken views cannot be exported.

Long term, allow everything to be dumped and backed up. A backup of broken code is usually far better than having no concept what the broken is supposed to do.

Hello Kim.
Thanks a lot for your input.
I have 2 favors to ask you:
1.- Can you please go to "workbench -> help -> system info" and paste the data that you get here?
2.- Can you please try to retrieve a create statement of this broken view using the cmdline tool (Start -> all programs -> MySQL -> MySQL Server 5.x -> MySQL Command Line Client).
We are looking at your report.
Thanks a lot in advance.

Here is the workbench post as requested:

MySQL Workbench CE for Windows version 5.2.33

Configuration Directory: C:\Users\Kim Upper\AppData\Roaming\MySQL\Workbench

Data Directory: C:\Program Files (x86)\MySQL\MySQL Workbench 5.2 CE

Cairo Version: 1.8.8

Rendering Mode: OpenGL is available on this system, so OpenGL is used for rendering.

OpenGL Driver Version: 3.2.0

OS: Microsoft Windows 7 Home Premium Edition (build 7600), 64-bit

CPU: 4x Intel(R) Core(TM) i5 CPU         650  @ 3.20GHz, 7.9 GiB RAM

Active video adapter: NVIDIA GeForce GTS 250

Installed video RAM: 1024 MB

Current video mode: 1680 x 1050 x 4294967296 colors

Used bit depth: 32

Driver version: 8.17.11.9745

Installed display drivers: nvd3dumx.dll,nvwgf2umx.dll,nvwgf2umx.dll,nvd3dum,nvwgf2um,nvwgf2um

Current user language: English (United States)

Regarding the request to retrieve the create statement of the view via the command line tool, the view is long since repaired. However, I believe this to be a SQL engine bug since it was broken in both workbench and in a third party backup tool.

So, looks like the problem was not a result of any bug in MySQL Workbench, wasn't it?

I don't know how the various pieces fit together well enough to understand where the issue really is. It makes sense to me that it's more a MySQL bug than a workbench bug. If this is true, then I mis-categorized the bug, but it doesn't change the severity. What would be the correct categorization?

If you suspect a server bug here, we need to know exact server version, 5.x.y. Entire error log of the server may be also useful. Please, upload it (compressed) to this report.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

I am not sure if my problem is related to this or not.
I use the mysql workbench facility for creating a dump, I check export stored procedures, but none of my stored procedures get backed up

Michelle,

Please, make sure this problem still happens in your environment with a recent version of Workbench, 5.2.39, and if it does, please, create a new bug report about this.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".