Known Bug? Import Batch File In Wrong Db

[magge]

Hello!

I've just persuaded my boss to buy me an Enterprise license, and I'm having happy days using the yog.

However I had abit of a surprise here… I rightclicked on of my DBs and selected “Import batch file…”, it clearly indicated what DB I was working on in the next dialog so I selected my file and proceeded. However the target DB remained unchanged, and I suspect all the SQL statements were executed to another DB. It's a bit hard for me to find out what DB exactly so I'm not sure if the DB names are in any way similar or something.

Is this something thats is or has been an issue ?

Sorry I don't have more details at hand. I only know I imported the SQL file, and the target DB did not reflect the changes. Using the same file via the mysql commandline worked and produced what I expected. That is using:

mysql mydb -u bla -p < mydump.sql Previously, using the Free version, I noticed that when I didnt use SQLyog for a long while (so the connection was terminated) a DB was not selected when I did another query. I had to change back/forth for my DB in the select-box at the top in order to be in the same DB as before the connection was terminated. The issue described above might have been after a long time of inactivity, I'm not certain. I will post more details if I'm able to reproduce this problem any more in my daily work. 🙂 SQLyog Enterprise v.5.17 mySQL v.5.0.22

[peterlaursen]

Quote:

and I suspect all the SQL statements were executed to another DB.

Does the file contain a 'USE' statement? If so it will of course use what the statement says!

Now we have an issue with 'conditional comments' like /*!40101 comment_as_statement */ that are widely used by 'mysqldump' program for instance. SQLyog 'import' cannot handle it (it runs OK from the editor). This is a known bug that will be fixed in 5.2 tree and most likely in beta2 or beta3. So it is likely you simply had an error!

[magge]

peterlaursen wrote on Sep 6 2006, 10:56 AM:

Does the file contain a 'USE' statement? If so it will of course use what the statement says!

Now we have an issue with 'conditional comments' like /*!40101 comment_as_statement */ that are widely used by 'mysqldump' program for instance. SQLyog 'import' cannot handle it (it runs OK from the editor). This is a known bug that will be fixed in 5.2 tree and most likely in beta2 or beta3. So it is likely you simply had an error!

The SQL dump would be typically be produced by:

Code:

mysqldump –add-drop-tables –complete-insert mydb -u xx -p > mydump.sql

…and it would typically be from mysql 4.1.x / mysqldump 9.11 . I've looked into the file and there's noe /* style comments (guess theyre introduced with mysql 5 ?), and no USE statement.

I tried creating a new DB in yog now, and importing this file into it, see screenshots below…

[attachment=512:attachment] import batch file

[attachment=513:attachment] refreshing object browser

[attachment=514:attachment] confirming empty db in mysql commandline

Some probing in the log leads me to believe that somehow yog has gotten 2 connections to mysql and is mixing them up. You can see the connection id 1514 selecting the db I right clicked, and connection 1518 executing the statements from the file.

Code:

060906 11:37:20 1514 Query use `test_empty`
060906 11:37:22 1514 Query select database()
060906 11:37:30 1518 Connect root@localhost on gjensidige
1518 Query DROP TABLE IF EXISTS `QRTZ_BLOB_TRIGGERS`
1518 Query CREATE TABLE `QRTZ_BLOB_TRIGGERS` (
`TRIGGER_NAME` varchar(80) NOT NULL default '',
`TRIGGER_GROUP` varchar(80) NOT NULL default '',
`BLOB_DATA` blob,
PRIMARY KEY (`TRIGGER_NAME`,`TRIGGER_GROUP`)
) TYPE=MyISAM
1518 Query DROP TABLE IF EXISTS `QRTZ_CALENDARS`
1518 Query CREATE TABLE `QRTZ_CALENDARS` (
`CALENDAR_NAME` varchar(80) NOT NULL default '',
`CALENDAR` blob NOT NULL,
PRIMARY KEY (`CALENDAR_NAME`)
) TYPE=MyISAM

[peterlaursen]

The import tool creates a seperate connection to the import.

We need a small sample dump here! Just a small database with two tables and a few rows in each!

Please create such, zip it and attach it here!

It is a complete mystery what happens! Never experienced or heard about this before!

[magge]

Its quite a mystery to me too… 🙂

It's hard to reproduce – I restarted yog and tried to make it happen with simple 3 table dump, no luck so far.

I'll try to let it idle for a bit now, maybe thats where the evil seed lies.

…or maybe it's because I had been using that previous window for days and it got confused at some earlier point when I did something completly different.

Oh well! Back to my work. I'll let you know.

[magge]

Hello again!

So it might seem this is an issue that occurs when sqlyog has been running for a long time, and perhaps only when I do some certain actions (which I've not been able to observe). But I have another occurance of the problem to show you now. This time I've prepared a very simple SQL dump (see attachment) which should not be able to confuse sqlyog in any way.

[attachment=537:attachment] The DB “test_empty” is … empty. 🙂

[attachment=538:attachment] Importing test.sql with “Current database is – TEST_EMPTY”…

[attachment=539:attachment] …import successful.

[attachment=540:attachment] The DB “test_empty” is … still empty. :huh:

[attachment=541:attachment] As traced from log (see attachment) my tables have been created in the “itegra” DB.

I dont know what more info I can provide, here… I HAVE used the Itegra DB with this session of sqlyog, allthough I'm not doing anything fancy with sqlyog. No procedures, views or functions. Just plain SELECT/UPDATE/INSERTs from the query tab.

[peterlaursen]

“I HAVE used the Itegra DB with this session of sqlyog,…”

It very much then looks like the 'use test_empty' statement sent by SQLyog does not reach the server or is not exeucted or is assigned by the server to a wrong client thread.

I you ever experience that again please check the HISTORY for that use-statement!

[magge]

Ok – so it's more likely a server/communications issue? Maybe I should just upgrade. I'll try to look in the history tab if I experience the same.

Might be a good idea, though, if SQLyog did an extra check of which DB its actually in before starting to read the dump file. As a security measure to avoid overwriting and dropping tables in another DB than expected. Since it's somehow possible to get a version of mySQL, misconfigure it or have communications problems that cause what I've described. Even though its highly unlikely it would occur the surprise is unpleasant and it shouldn't be a big thing to implement?

Thanks for the help! 🙂

[peterlaursen]

I cannot be sure!

As long as an issue is not reproduced and fully understodd, every posibilty is open!

But HISTORY compared with SERVER logs ..

[magge]

Hello again!

I'm back again with my third occurance of this bizzare issue… Again this seems to occur when SQLYog has been idling for a long while.

I now loaded a SQL dump into a totally different DB than I expected. A restart of SQLyog and retry at the same dump, and it worked as expected.

[attachment=550:attachment] Screenshot of SQLyog log after issue confirmed.

Observe the log in the screenshot… I've now just emptied the “telehuset” DB (partially shown, the drop statements). Then I right click telehuset, import batch file (the select database() statements), and finally I hit F9 a couple of times to refresh the object/table browser (select `TABLE_NAME` (…) statements). DB is still empty.

Looking at the mySQL log, it still looks like SQLyog makes a new connection to another DB (dnb_eksterne):

Code:

(…end of empty database action…)

256 Query drop table `telehuset`.`travelvote`
256 Query drop table `telehuset`.`user`
256 Query drop table `telehuset`.`usergroup`
256 Query drop table `telehuset`.`vendorinfo`
256 Query drop table `telehuset`.`winnerbudget`
256 Query drop table `telehuset`.`winnerbudgetlap`
256 Query SET FOREIGN_KEY_CHECKS=1
256 Query show databases
256 Query select `TABLE_NAME` from `INFORMATION_SCHEMA`.`TABLES` where `TABLE_SCHEMA` = 'telehuset' and `TABLE_TYPE` = 'BASE TABLE'
060920 14:05:37 256 Query select database()
060920 14:05:42 258 Connect root@localhost on dnb_eksterne
258 Query DROP TABLE IF EXISTS `activity`
258 Query CREATE TABLE `activity` (
`activityid` int(11) NOT NULL auto_increment,

(…dump begins here…)

[peterlaursen]

MySQL 5.0.22 .. hmmm

OK .. we will certainly need to research into this.

Can you inform about time-out settings on the server?

For instance with a MySQL Administrator screenshot like this one.

But please upgrade SQLyog too.

[peterlaursen]

Now ..

sorry for the confusion.

As I have written before SQL-import always creates a new connection. That is as expected.

We never had a similar report and never experienced this ourselves. We have now had a couple of SQLyogs inactive for a while and still cannot reproduce it.

Is it possible for us to get tempoarary access to the server?

Also do you have more SQLyog copies installed or only one?

If you have more has it happened on one PC only or more?

Could it be a specific server behaviour that triggers this?

A sort of memory corruption on your system?

Do you have an option to work on another computer?

Other ideas?

[magge]

Hello again. What you write leads me to believe there must be something wrong with my system.

My mySQL network parameters:

[attachment=556:attachment]

Your questions:

Is it possible for us to get tempoarary access to the server?

I dare not with my workstation, sorry. For sure the network security ppl would throw me out the window.

Also do you have more SQLyog copies installed or only one?

Only one installation…

If you have more has it happened on one PC only or more?

I'm the only one using SQLyog here right now.

Could it be a specific server behaviour that triggers this?

Could be. Although my workstation setup is pretty basic and in fact its not long since I've reinstalled. One strange thing thats been bothering me is that I keep getting a “Insert disk” prompt (see screenshot) whenever I connect to mySQL. This goes for both SQLyog (both remote and local connections) and the mySQL commandline client. I think maybe windows might be somewhat confused about the mysql piped connection or something. I tried to get rid of everything pipe-related at some point, but I could not get rid of the “Insert disk” message. Perhaps that is related to the problem? I didn't think of it until now. Perhaps the fact that windows want a disk inserted upon making a new mysql connection is what makes the fuzz upon connecting during “Import batch file” ?

Otherwise this system has nothing fancy. No 3.party evil firewall (just windows fw). Some cygwin services are running, but should be totally unrelated.

[attachment=555:attachment]

A sort of memory corruption on your system?

Not likely to be physical, atleast. I've run almost 24hours x86 memtest with no errors. Nothing else is observed that looks like memory corruption (illegal operations/reads/writes, blue screens of death etc).

Do you have an option to work on another computer?

I can give it a go on my private comp. at some point, offcourse. What you write makes me think it's going to work there. 🙄

Other ideas?

My only idea is the “Insert floppy” case described above. Don't know WHY I didn't suspect that before… 😕

Also – I'll update my SQLyog today…

Thanks for great support, by the way…

[peterlaursen]

As I wrote I cannot tell for sue what is happenning.

But we never had any similar report.

It is a possibility that some Windows thing got messed up somehow on that PC of yours.

[magge]

Hello again.

For your information my “Insert disk”-issue is related to this “bug”:

http://bugs.mysql.com/bug.php?id=625

It is a Windows specific thing. If you, like me, have your OS partition on a drive thats not connected to one of the good old IDE controllers but instead a SATA controller etc – you might find that your OS is not on C: but on F: for example. In addition my comp has some card readers, this goes for any removable media I think (mysql bug is related to a zipdrive), that installed themselves on C:, D:.

Mysql tries to do something with C: whenever it recieves connections, thus Windows prompts you to insert something into that drive. In my case, perhaps because I've never used the card readers or because they're some kind of noname-product, I just get a message “Please insert disk into (blank)…”.

This is all fixable via the Drive Manager – change the drive letters of your removable drive(s) away from C:.

I think this might be the key to my problems. Yesterday (before fixing my drives) I noticed that I even got connected to the wrong DB from “New connection” in a SQLyog window I used for a previous connection. This all happens inbetween “Insert disk” prompts, so I'm pretty confident thats the problem. However I've not gotten the “Insert disk” prompt when running “Import batch file”.

I'll give an update on what this has fixed or not fixed later.

[peterlaursen]

Thank you very much for the info!

I'll keep an eye on that bug report!

Whenever you get convinced that this is the issue, I think you should add this info to the bug report at mysql.com!

[magge]

Being rid of the “Insert floppy” messages it seems I was wrong – I still import my sql batches in the wrong DB.

I will try to reproduce this on another machine soon.

Short testing procedure for anyone else who'd like to try: Start SQLyog, do some queries in database A, wait a long time (typical for me is ca 16 hours), right click DB B and choose “Import batch file”, import something, and for me that dump winds up in DB A instead of DB B. I'm pretty sure this should reproduce the (my :eek:) problem every time.

[Author]

Posts