All,

I'm testing out dump and restore stuff at the moment. With MySQL 4.1.9 on RHEL 3 I do the following to get a dump of a InnoDB database.

mysqldump -u root -pXXXXXX --opt --databases some_db --single-transaction --flush-logs > /database/somedir/databaseID/dumpfile.sql

I can then restore this on MySQL 4.1.9 on Windows XP Pro with the following :

mysql -u root –pYYYYYYYY < /path/tofile/dumpfile.sql

All works.

Now I try a Export Database as SQL dump in SQLyog v4.05 (connected to the same RHEL 3 box) and it creates the sql file. If I then try and use SQLyog connected to the Windows XP Pro PC and do an Import from SQL dump I get the famous :

Error Code: 1005 - Can't create table '.\some_db\sometable_name.frm' (errno: 150)

Under the dump that I created I checked :

Include "USE dbname-" statement
Include "DROP TABLE" statement
Add Create database
Lock All Tables For Read
Flush Logs Before Dump
Add Lock Around Insert Statements
Set FOREIGN_KEY_CHECKS=0
and
Create Bulk Insert Statements

I've looked at the two dump files and can only see minor differences (some commands in upper case in one and lower case in the other etc.)

Is this just Unix/Linux Windows nastiness or am I doing something fundamentally wrong?

Apologies if this is in the wrong subject, I put it here since I'm just starting to use SQLyog.

Regards,

Matt

That's strange in my opinion. When using Sqlyog are you then logged on as ROOT too ?
And is the windows machine where Sqlyog runs the same machine where the Windows Mysql server runs too ?

Can you use the .sql-file created by sqlyog from MySQL command Line under Windows ?
You can also try restoring the .sql using "MySQL administrator" and see if the problem is the same.

two more things that might have something to do with it:
1) Character_set settings on the two servers. Does the "create table"-statement specify a character set and collation ? Any particular language setting on the two machines ?
2) has he Mysql Server(s) (now running 4.1.9 been) updated from earlier versions (4.0.x or earlier) , and it that case, did you remember to update the GRANT-tables ?


The MySQL-documentation
http://dev.mysql.com/doc/mysql/en/news-4-1-10.html writes in it's changelog for ver. 4.1.10

although it is not exactly your problem it might be a related problem.

Is there a chance that you could update the win installation to 4.1.11 (latest release in the 4.1 -series) ? With the new installer it's only two minutes. Just don't run the configuration wizard not to overwrite your my.ini! And you could create a "Systems Restore Point" in Win XP to undo (I works that way!) . But there is quite a lot of bugs fixed from 4.1.9 to 4.1.11 (and more in 4.1.12 (not released yet)) so maybe that's worth a try ?

Peter,

Yes logged on as the root user for MySQL on both machines.
Yes, SQLyog and the MySQL server are both running on my PC - Windows XP Pro

When trying to inject the raw sql file I get :
C:\>mysql -u root -p < V010MT01_SQLyog2.sql
Enter password: ********
ERROR 1153 (08S01) at line 209: Got a packet bigger than 'max_allowed_packet' bytes

I launched the mysql client and injected the V010MT01_SQLyog2.sql file I got some errors :

ERROR 1153 (08S01) at line 209: Got a packet bigger than 'max_allowed_packet' bytes
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 13
Current database: reel_db
...
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails
ERROR 1050 (42S01): Table 'libelle_civilite' already exists
Query OK, 0 rows affected (0.00 sec)
...
ERROR 1062 (23000): Duplicate entry '1' for key 1 - saw this one twice
ERROR 1050 (42S01): Table 'offre' already exists - for three different tables
ERROR 1216 (23000): Cannot add or update a child row: a foreign key constraint fails

The base and tables 'seem' fine though after examining them for 2 minutes ;-).

Charset is latin1 for both SQL dump scripts. The 'raw' mysqldump script has :
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
The SQLyog generated script doesn't mention collation anywhere.

Under Server variables for the Windows XP machine I have for character_set :
client, connection, database, results and server as latin1. Under character_set_system I have utf8
Under the RHEL machine its the same.

Both are brand new installs - no upgrade. The RHEL had a compiled install, the Windows machine used the mysql-4.1.9-win32.zip from the MySQL website.

The reason that I put 4.1.9 on the XP machine was that I wanted to use the same version as on RHEL. If I upgrade on the XP box, my tests won't really be valid.

I have the feeling that this is an SQLyog thing. The 'raw' mysqldump script has settings for collation, unique checks while the SQLyog script doesn't. We're not going to use SQLyog in production, but it would have been nice to be able to export/import through SQLyog.

Oh well, I'll have to learn to use MySQL Administrator to export/import.

Thanks for your help and advice, if there are any more questions I'll try and answer them.

Matt

P.S. Just looked at the lower_case_table_names parameter on both machines : they're different, the XP machine has a value of 1 and the RHEL machine has 0.

oh that's it .. once more!

max_allowed_packet is a setting in your MySQL configuration file (my.ini(windows)/my.cnf(unix-Linux))
I think that after installation it is as low as 2M and that's not much. Try raising the value.
On my local server I'm using "max_allowed_packet = 100 M" and MySQL does not take significant resources anyway.

After editing the ini-file you must stop and restart the MySQL server for the changes to take effekt.

But also try from Sqlyog in the settings i the "export data" dialogue box to uncheck "create bulk insert statements" before generating the .sql

Both methods should work ....
Strange though that the error occurs with Sqlyog and not command-line tool.

But maybe the insert statement for the table i.e.
insert into `mybase`.`mytable`
(column1, column2, etc)
values
( x1,y1 ,z1; x2,y2,z2; etc)

that shouldn't have been sent until it was finished!

continue;

..... are longer with Sqlyog then with command-line tool ??

I unchecked "create bulk insert statements", redid the dump and then imported - worked. Table names have been changed to lower case but what should I expect on a Windows machine. The import did take a while though : 5M file and about 30 mins (the file was already on the PC running Windows XP and MySQL).

I'll edit the my.ini for the packet sizes.

Thanks for your help Peter.

Matt

unchecking "create bulk insert statements" will cause the server and the client to renegotiate the connection with each statement, I think. Probabaly that's why it is that slow! But with Sqlyog as of now it is the only way you can't decide the packet size.

Thats very strange.

It should not take so much time.

If the data is not confidential then can you send me a dump of your database?

I just tested on my system (Athlon xp+ 1700, 512 MB RAM)

With a dump of about 12 MB, it takses 20-25 seconds to import with "create bulk insert statements" unchecked. With "create bulk insert statements" checked it's less than 5 seconds.

Also I can't reproduce that Capital Letters are "decapitalized" ( that was what you meant ? ). But I have only Windows' machines, so if it is some charset mismatch with MySQL across platforms I don't have any chance to find out ...

The problem is related to the fact, that most my.cnf and my.ini files contain special rules for MySql's command line utilities - such as an increased max_allowed_packet setting.

Charsets and collations are an issue with SQLyog, they are not really taken into consideration when an operation gets performed by SQLyog.

I have to settings of max_allowed_packet i my my.ini
- one in the main server section [mysqld] and one in the [mysqldump] section
i.e.:


[mysqld]
..
..
max_allowed_packet=100M

[mysqldump]
quick
max_allowed_packet = 16M


I have changed the first one from 2 to 100 M. But besides that and besides that I changed the datadir location I just use one of the templates coming with MySQL.

I've not touched the latter. Is this the one you are talking about Shadow ?

Exactly!

@Shadow: Great to see you back in the forums!

Thanks! I have managed to get a development task once again, so I'll be using MySql and SQLyog rather frequently.