MySQL Bugs: #18601: doesn't recognize DROP VIEW syntax generated by Administrator backup

Bug #18601	doesn't recognize DROP VIEW syntax generated by Administrator backup
Submitted:	29 Mar 2006 3:25	Modified:	4 Dec 2006 4:45
Reporter:	Jim Michaels	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Query Browser	Severity:	S3 (Non-critical)
Version:	1.1.20	OS:	Windows (Windows XP Pro MP)
Assigned to:		CPU Architecture:	Any
Tags:	Source Editors

Description:
DROP VIEW IF EXISTS `workhours`;
causes
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ';
--
-- View structure for view `jmichae3`.`workhourstot`
--

DROP VIEW' at line 1

How to repeat:
DROP VIEW IF EXISTS `workhours`;

Suggested fix:
fix the query browser, the administrator backup, the server, and manual, or something. something's wrong here.

Thank you for a problem report. Sorry, but

DROP VIEW IF EXISTS `workhours`;

alone gives me just expected info in the message bar, at the bottom:

Unknown table 'test.workhours';

when entered in the SQL Query Area of QB 1.1.20. So, please, provide a more detailed description of your actions.

Well, that's the error messages I got.
I loaded the script into the query browser using "file" "Open Script..." and pressed the bug play button.  I didn't use the query window. that works. (now why is that?)  QB and server both somehow goes haywire after a time on this script Administrator Backup generated. I can ftp the file to you if you can provide an upload area.  it's 7MB (1.3MB compressed).  broke my server and the query browser until next reboot.  I eventually used QB's Script Load to get the rest of my data back in.
it's at http://mmediap3.home.comcast.net/z/sql.zip

I forgot to add: Administrator restore doesn't do a perfect job restoring this.  I got 2 error messages at the very end saying it basically didn't recognize the CREATE VIEW commands it generated by its own backup.  Time for another bug report.

Is this "drop view" error message a symptom of a blown parser or engine somewhere?

Thank you for the additional information. So, if you open a file (created by Administrator) using "File" > "Open Script..." and then try to run the script (containing DROP TABLE IF EXISTS and/or DROP VIEW IF EXISTS), you'll get messages like described in this report.  

But is seems to me that DATA in that INSERTs are a problem (they contain \ / and other similar symbols).

Verified with your all.sql script.

Administrator seems to have generated spurious characters in columns names too. like with msg_id these characters are not in the db.
the \ characters are perfectly legal syntax because they properly escape the next character (which is usually a \ ' " ).  from what I rememeber, / is legal normal char and does not need to be escaped with a preceding \.

Jim, could you please send the raw binary file for the "messages" table in the geoff-blog schema (send it to mikel@mysql.com)? Don't dump it, just pack the messages.frm file and send it to me. Hmm, I see it's an InnoDB table. Could you first change that to MyISAM so there is no data in the innodb data file? What I want to check is why MA dumps the table with that spurious character (0x1F) in the msg_id column name. Thank you.

the database has gone through a whole new engine upgrade since that time due to working on another [install] bug.  so I am not sure if you will have the same results.  I also edited out the problem since I needed to get the code working. sorry.  but I will send the data nonetheless.

Jim, thank you. I just wanted to look if I can see where the spurious character in the message table comes from. 

Anyway, I give back this report to Valeriy and if he cannot reproduce it (anymore) then we should close it.

Thanks again for your cooperation, Jim.

Jim,

Please, check with the latest versions of Adminitrator/QB (from GUI Tools v5.0r6) and, in case of the same bug, send the beckup you can not load.

did a backup with the MA 50r6.  no spurious characters.  
I suspect the corruption may have been due to a memory shortage.  I pushed my machine to the max.  With XP you never 

really know when it runs out of memory.  the only guess you have is that dialogs stop showing or buttons go missing.

drop view also appears to have worked without a hitch.  the entire 121MB script took almost 4 hours to execute, and 

with no errors - surprising, since there are numerous tables dependent on each other.  Nothing shows up in the resultset.  Are the errors and results turned off?  I didn't even get statistics on the script query.  nothing.  just worked I guess.

the 121MB file file took 38 minutes to load and was 3.8 million lines long and consumed all 4GB virtual memory.
my little ascii text editor (The Semware Editor) on the other hand, could open the file in 1-2 minutes (6 seconds if cached).  Notepad takes 

forever to load and chews up lots of memory.

So, the bug is not repeatable with latest version of MySQL Administrator (or it was a kind of out-of-memory issue).

appears to be solved.  good job.