Only First "alter Table" Sql Command Takes Effect

[GreenAlien]

I've just been playing with the “Structure Synchronisation Tool” (SST) but it has not been working as expected.

I have a test database on my local system and a copy of the database on my production server. I use the SST tool and it reports all the tables need updating (i.e. they all appear in blue). I thought they were identical but I play along and select the “Sync (clipboard)” option on the local database, which results in several “alter table” sql commands being placed on the clipboard, one for each table.

I paste this into a text editor to take a nose and it seems that my local database is using InnoDB while the production database is using MyISAM, and therefore the SQL update info included instructions for changing all the table types to InnoDB. I don't know what the difference is between InnoDB and MyISAM, but I suspect when I originally copied over the database (a couple of weeks ago before I discovered SQLyog) and I excluded the table type from the structure information when doing that, and so each mysql database apparently defaulted to different table types.

Anyway, I paste this SQL code into the SQL box in SQLyog and hit F5. It appears everything when fine, so I confirm by logging onto the production server (a typical LAMP web host), fire up phpMyAdmin, and bring up the info about all the tables. Now, instead of all the tables now showing as InnoDB like I expect, only the FIRST table is showing as InnoDB. The rest of the tables still appear to be MyISAM.

So for a sanity check I repeat the process. I rerun SQLyog's SST tool. This time all the tables show in blue except for the first table, which shows as black. This at least confirms what phpMyAdmin is reporting. I click “Sync (clipboard)” and it puts the update sql code on the clipboard. Again I take a nose at this in my text editor and observe the “alter table” commands are there as expected – only this time for four tables instead of five tables. I run the sql statements in SQLyog, and switch back to phpMyAdmin on the production server to see what effect all this had. This time, the first TWO tables are showing as InnoDB, the rest remain as MyISAM.

It appears that each time I apply the sql statements to update the structure, only the first “alter table” statement takes effect. The rest are ignored!

As a final check, I rerun the SST, which shows the remaining three tables in blue, as expected. I hit the “Sync (clipboard)” button again for the local database. Again, the sql statement includes several “alter table mydatabase.mytable type=InnoDB;” commands. This time, instead of running those through SQLyog, I run it through phpMyAdmin's “Run SQL query on database” tool instead. This time all the tables show as InnoDB according to the structure info in phpMyAdmin. I confirm this by rerunning SQLyog's SST tool – this time all the tables show in black as expected, indicating the two databases are now synchronised.

So in summary, it appears that if I try to do several “alter table” sql commands via the sql box in SQLyog, only the first actually gets applied! Is this a bug?

Cheers, Ant

[peterlaursen]

I never heard about it before. And I think it is a simple issue

Quote:

I paste this SQL code into the SQL box in SQLyog and hit F5

try hiiting the “double green arow” icon with the mouse instead! F5 executes only ONE query (limited by the ;-character).

Quote:

don't know what the difference is between InnoDB and MyISAM

very briefly the INNODB supports Foreing Keys and transactions what MyISAM does not. The MyISAM storage engine is written as optimized to MySQL code. Optimization of INNODB has also taken place with recent MySQL-versions.

MyISAM is originally written for MYSQL, INNDB started as a “foreign” project to MySQL. More to study here http://www.innodb.com/index.php

My experience with “older” MySQL versions (that is up to 4.0.x) is that MyISAM is about 50% faster than INNODB. With the INNODB-code of 4.1 and 5.0 the difference is not that big.

[peterlaursen]

@ Ritesh

Since Sqlyog version 4.05 there has been no difference in F5 and F8.

Wouldn't it be an idea to change functionality of F8 to “execute all queries”

At the same time let Ctrl+Enter be the same as F5 as has been requested before by quite a lot of users!

[GreenAlien]

Thanks again Peter for your quick reply. I nipped back on here to confess that I had overlooked the “Execute All Queries” option to find you had already answered my question!

I'd like to propose the following to improve the user friendlyness of SQLyog (or just to accommodate clueless developers like myself!):

When someone presses F5 (or hits the single green arrow), and several queries exist, show a dialog with a note along the lines of “You have entered several queries. You can run all these queries by pressing F8 or by clicking the double arrowed button. Run currently selected query anyway?”. With yes/cancel buttons, and a “Do not show this again” tickbox option.

If someone enters or pastes in several queries, then I suspect in the majority of cases they expected all of them to be executed.

Thanks, Ant

ps: thanks for the overview of InnoDB and MyISAM. I suspect that the foreign key feature allows you to link tables together. I'm still in the learning process so I'll be sure to read up on that.

[peterlaursen]

Quote:

When someone presses F5 (or hits the single green arrow), and several queries exist, show a dialog with a note along the lines of “You have entered several queries. You can run all these queries by pressing F8 or by clicking the double arrowed button. Run currently selected query anyway?”. With yes/cancel buttons, and a “Do not show this again” tickbox option.

If someone enters or pastes in several queries, then I suspect in the majority of cases they expected all of them to be executed.

I don't think I agree. On the opposite I hate alle these dialogue boxes that M$ has trained us to think is a natural thing.

The SQL-pane easily gets filled up with hundreds of individual querries! Some will cause errors – others might do undesired things. I would not risk to execute all queries when I only need to do one

Since there is a sinnge-arow and a double-arrow available I think that is is it should be. If F8 is change to have the double-arrow function thne that is Ok with me too.

You can also “mark up” a section of code in SQL-pane with the mouse and press F5. Then queries inside that section will execute

A configurabale hotkey-manager is on the TO DO list. But other issues have been considered more important until now

[peterlaursen]

@ritesh

and BTW – don't forget to update this screen.

F8 is not “execute for update” anymore

[GreenAlien]

peterlaursen wrote on Aug 19 2005, 02:02 AM:

I don't think I agree.  On the opposite I hate alle these dialogue boxes that M$ has trained us to think is a natural thing.

Think we'll have to agree to disagree on that one then. I bet there's quite alot of people that install SQLyog, paste in a couple of lines of SQL (eg as a result of using SQLyog's sync tool), and haven't realised about the “execute all queries” option and expect F5 to do the job.

This consumed about 20 mins of my time before I noticed the database wasn't as expected and the reason why. Ticking a checkbox and dismissing a dialog takes all of 3 seconds, and only needs to be done once after installation of SQLyog.

Nothing wrong with a little hand holding, especially as there'll be plenty people migrating from query tools that only have the single execute option.

Rgds, Ant

[Author]

Posts