Copy Database To Different Host – Problem

[nicmar]

(I posted somthing similar in BETA-discussion too, but since the problam actually is in 5.02 i have to post this in bugs)

I have problem with 5.02 version of SqlYog when trying to synchronize tables localhost->ISP.

Not all table data came along to the ISP. This is a VERY serious problem, since I need to be sure that my data gets transfered correctly.

I have about 40 tables in my DB and as far as I can see, only on one table the table data isn't transfered. The structure is ok, but no data.

When I export that table using “export as SQL-statements”, and then execute it on the other host, there's no problem.

MySQL-versions:

Localhost: 4.0.26-nt

ISP: 4.0.26-standard (Using HTTP-tunneling)

Thanks in advance.

[peterlaursen]

well .. there is a problem with one table.

Is it correctly understood that NO DATA at all come through for that table?

Any error messages in the log?

Are you PERFECTLY sure that the structure on the two hoses are 110 % identical ?

Could you post the 'crate statement' for that table on both host?

It simply looks as if a error is raised when trying to sync that table. Now, if you chose not to 'exit on error' the sync tool proceeds to next table.

[nicmar]

Yep, no data coming through. I do like this:

1. Right click the database and choose “Copy database to different host/db” (So i'm not using the structure synchronisation tool)

2. I make sure “Drop table if exists in target”

3. Select Structure and data

4. Press copy

No error messages, nothing in the log. It proceeds to the other tables. And there is no “exit on error”-option.

If i do it manually, this is the data from localhost:

[edited]

There are no quotes around the first field, which is the primary key.

Does this make it easier to find the problem? 🙂

[peterlaursen]

There were quite a lot of quoting errors in what you posted.

However after correcting this and importing I get this:

Error Code : 1064

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 ''Om avbokning sker efter avtalad tid uteˆ[ˆ]™ÚY�9H

LÜ‹‰Ë

(0 ms taken)

It is the same issue as here:

http://www.webyog.com/forums/index.php?showtopic=1915

And that explains that copy fails too, I believe. I have no problems with direct connection.

I know that this is very high priority right now. Ritesh is working intensely on that problem.

Can you tell what is the PHP-version(s) involved in this copy?

[nicmar]

Ok, somehow i never got that error this time, but I got it before, and reported it as a bug somewhere else I think.

I hope ritesh can fix it!

I'll have to try to live through my day until then.. 🙂

thanks for your time!

[Ritesh]

This is very strange. The error should occur everytime. One thing that I have noticed is that it happens with MySQL 4.0.x and not with v4.1.x. I will research on it even more.

BTW, if the data is not confidential, is there any chance of getting a dump?

[peterlaursen]

Quote:

One thing that I have noticed is that it happens with MySQL 4.0.x and not with v4.1.x.

It might as well be PHP versions that make the difference. I happens on MySQL 5.0/PHP 5.1.2 as well (but not exactly the same way as with MySQL 4.0/PHP 4.4.2). I also don't think MySQL 4.1/PHP 4.3.11 is 'free' of this. But it is hard to generate this sort of error (but not totally impossible either, I think) with MySQL 4.1/PHP 4.3.9. But it is hard to remember details! And when I first researched into this there was no less than 4 different issues at the same time. That made it rather confusing. Now there is only this one left. So a systematic approach should be much easier now.

@ritesh: you can have access to my local server with almost any MySQL version and PHP version that you like. It is all installed. I can switch between them in seconds – just need to move a few comment characters in the config files and restart Apache and MySQL!

Quote:

The error should occur everytime

Well, yes. But where will the error be reported if it occurs with 'copy database to other host'. Not in 'messages' tab, I think. In sqlyog.err? Nowhere ?? It looks to me like 'copy database to other host' 'by-passes' the common SQLyog message queue.

I think @nicmar gets this error with the first row of this table. And the tool skips that table completely then. But as there is no 'abort on error' user option here I can't tell for sure. You (@ritesh) know the code. Will the job abort or proceed to next table with this error?

[nicmar]

I'll mail you a dump of the entire db, ritesh.

It's not confidential, but don't post anything on the forums thanks, there are some personal details, but mostly test data.

Btw, i'm using PHP 4.4.1 and Mysql 4.0.26 both on my localhost and my ISP .

I've sent the file to ritesh via mail.

[Ritesh]

nicmar wrote on Mar 18 2006, 08:45 AM:

I'll mail you a dump of the entire db, ritesh.

It's not confidential, but don't post anything on the forums thanks, there are some personal details, but mostly test data.

Btw, i'm using PHP 4.4.1 and Mysql 4.0.26 both on my localhost and my ISP .

I've sent the file to ritesh via mail.

Thanks, I have received the dump. I will work on it now. Dont worry, no details will be published anywhere.

[peterlaursen]

@nicmar

I thinks SQLyog 5.1 BETA 4 solves this.

Can you confirm?

[Ritesh]

Yes, indeed. This version fixes this issue.

I imported your data without any problem. This process was earlier giving errors.

[peterlaursen]

Quote:

Yes, indeed. This version fixes this issue.

@ritesh .. you got mail!

The BETA 4 solution has the side-effect that non-ASCII characters in unicode (utf8,ucs2) databases won't work with SQLyog anymore. Not even with direct connection … 🙁

Still some 'nice work' to be done ….

[nicmar]

Shall I still try it? Where is the Beta 4 download? I forgot.. 😕

[peterlaursen]

Link to beta4 from here: http://www.webyog.com/forums/index.php?sho…view=getnewpost

It should solve your problem. However fixes only one-byte charsets.

[nicmar]

I still have problems syncing databases, and as usual when it's a rush..

First i tried the SJA, and it didnt get very far, just stopped on something like “an error has occured”, and yet i told it not to stop on errors…?

Second try:

Then I exported all to SQL-file, worked fine.

When i execute SQL-statements from a file, it runs for 1500/4900kb, then says this error:

I x out everything but the structure, and a swedish “ä” somewhere in the data.

Query:

insert into `boka_anstallda` values

('7030','17','','xxxx','xxxxx','xxxxx','[email protected]','','xxxxxxx','xxxxxxx','xäxxx','','','','xxxxxx','xxxxxxx','1','0','0','1143704527','0','','0','0','','','1')

DROP TABLE IF EXISTS `boka_badlogin`

Error occured at:2006-03-30 10:52:07

Line no.:13936

Error Code: 1064 – 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 'DROP TABLE IF EXISTS `boka_badlogin`' at line 3

So my third try is to use the SJA again:

Well.. now it worked, thank god 🙂

It feels kinda dangerous to be using a BETA-version on a Live important database, is there any risk of data loss?

It's always easy to transport small databases, but the kind at 5Mb, which not really is HUGE, always feels more risky.

-niclas

[peterlaursen]

Quote:

It feels kinda dangerous to be using a BETA-version on a Live important database, is there any risk of data loss?

There ALWAYS is. That is why you need to have a backup plan. Read the standard terms of any software contract. No liability for 'consequental damage' etc … But betas are of course more risky.

Could you explain more in detail (we must get to the very bottom of all this!):

1) With what program versions (SQLyog and MySQL) did you have errors and with which versions did you not ?

2) Which character set is used for the database having Swedish data? (did you notice the utf-8 encoding of backups with SQLyog version 5.1 beta5?)

3) Do you use tunneling for some of the operations?

[nicmar]

Like i said in the last post, i got it to work second time i tried sja.

That was from localhost (direct) -> Mysql 4.0 (Tunnel)

Now I try localhost (direct) -> Mysql 4.1 (Tunnel) with the same custom application, which means that the table structure is the same as in the above transfer.

There might be some conflict with the building of tag files, i will now try again.

Nope same error, here's the error log:

---

Sync started at Thu Mar 30 11:55:16 2006

Error No. 1064

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 'DEFAULT CHARSET=latin1' at line 5

Error No. 1146

Table db_name.table_name' doesn't exist

Error No. 1064

---

On and on for each table.

I guess this is the same problem as in phpmyadmin when trying to export 4.1 table, import into 4.0. In phpmyadmin you can set “use mysql4.0 compatibility”, and it doesnt type the “default charset..” and the ENGINE=MyISAM is changed to TYPE=MyISAM.

Is it possible to add this to sqlyog, or even better, detect what databas version it is on both servers, and use the correct syntax?

So what I'm left to do is enter the phpmyadmin on the 4.1 host and do a 4.0-export there. It kind of sucks 🙁

-nicmar

[peterlaursen]

Now .. it is still not clear to me! :huh:

1) What MySQL version is your localhost?

2) You also don't answer with which SQLyog version you get this result. SQLyog 5.02 or SQLyog 5.1 beta 5? Or both?

3) You get error with ''DEFAULT CHARSET=latin1' when importing to MySQL 4.1 and not with 4.0. That is the opposite world. Are you sure? Your remark on the use of phpmyadmin (we'll soon ban that word! 😎 ) is the 'other way around'.

4) Are you sync'ing into an existing table or is the table (attempted) created by the SJA at sync time? If the latter is the case, what happens if you create the table before the sync with the STRUCTURE SYNC tool (or by hand)?

And when you get this error:

Quote:

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 'DEFAULT CHARSET=latin1' at line 5

.. then the error actually can be just befre that! MySQL is not very 'smart' in neither localising or describing errors! But most likely it is the table definition that is the problem. And if the does not understand the table definition then no table is created, and you get the next error

Note that the SQLyog 5.1 (from BETA 5) export tool encodes as UTF-8 with an ordinary export from MySQL 4.1 ++. With the Backup 'powertool' you can choose not to. MySQL 4.0 does not support the utf8 character set. I don't know how this will be with SJA sync in 5.1 FINAL but I don't think there is any change yet here (and the tunneler of BETA 5 is also identical to that of BETA 4, I think)

You can always edit an SQL-file in an editor! Even 'search and replace' UTF-8 encoded special characters with single byte representation.

And the TAG files have nothing to do with neither SJA SYNC nor IMPORT. They are only used by 'autocomplete'.

[nicmar]

1) 4.0.23-nt on the localhost and 4.1.10 on the external

2) I only use the latest beta (5), currently on my laptop cause the other one got stolen. It's hard to try different versions cause there are different tunnel-php-files. Didnt try 5.02 in a while.

3) Oh, I did the arrow things wrong. I only sync in one direction and that's from 4.1 (external w/ tunnel) -> 4.0 (Localhost). In the first host both the external and localhost are 4.0.

The issue here is a Live system running critical data, which I download to my db to make sure i got the lateest data when developing.

4) I tried the struct. sync tool which made an SQL in clipboard which I ran on the empty DB on localhost and it created all tables, no errors. Right now the sync is ongoing, but it takes a while. Maybe a slow host.

5) I don't really know what the UTF-problem is, but I know that I had trouble while using 😆 to sync, and all swedish chars got messed up in the localhost copy. I don't know if I'll get that problem now.

6) “You can always edit an SQL-file in an editor! Even 'search and replace' UTF-8 encoded special characters with single byte representation”

Do you mean that the sja creates an SQL-file and stores somewhere during the operation? I still havent read the sja-guide yet that you wrote.. it's kinda long.. 🙄

7) Another thing that worries me right now is that I'm on a WLAN. What happens if it looses the connection in the middle of a sync? I got a better connection now cause I moved the computer a bit, but i guess it could get broken, or if my ISP has some kind of trouble, during a sync..

8) The sync is done after 628 sec, but this result worries me:

`table_name1` 2 0 2 0 0

`table_name2` 2952 0 952 0 0

`table_name3` 9148 0 149 0 0

All tables with more than 1000 rows only shows wrong number of rows inserted. The thousand-digit seems to be missing and in some cases +1, 149 instead of 9148.. WEIRD!

Let me check the actual data.

All rows are there, but there are actually 9149 rows in both the source and the target!

So something is wrong with the display, all rows got copied.

It looks like I give you headache here 🙂

-nicmar

[peterlaursen]

1)+2) OK .. al understood. But this ” It's hard to try different versions cause there are different tunnel-php-files.” You may rename them and have more 'variants' if you can edit the PHP-code to specific needs or need versions for different SJA/SQLyog versions! I always rename the 'SQLtogTunnelBETA.php' with BETA versions, so I won't have to ovrwrite the one belonging to latest stable release. But let us stay wiht version 5.1 now.

3)+4) Now I think that we agree that when running a DATA SYNC from 4.1 > 4.0 the table structure must be created in advance, as 4.0 does not understand the 'create table' statement from 4.1.

5) “and all swedish chars got messed up in the localhost copy” Did the Swedish characters become two-byte sequences? If yes It very much looks like data have been UTF-8 encoded when reading from 4.1. What is the default character set for the server and the database? (SQLyog will show you from tools .. show .. variables). It looks like database (and server) has utf8 as default, but tables are created explicitly as latin1 with the create statement. SJA queries the character set for the database and use this for 'set names'. You may simply hardcode the tunnelling file to 'set name= latin1;' if this is the case then if all characters can be represented in latin1! (Swedish can and as I said you can have more tunnelling files with different names)

6) I was only talking about 'ordinary' export and the Backup 'powertool'. SJA datasync does not create a SQL-file.

7) “What happens if it looses the connection in the middle of a sync?” Then the sync stops 'half done' , and you'll have to start all over again! simply! But will of course be faster 2nd time as less work needs to be done.

8) This is quite a serious bug with the display in my opinion then!

[nicmar]

1+2) Actually i rename the SQLyogTunnel.php to a random name, like SQLyogTunnel739.php to increase security so that “hackers” don't try to access via this.

The reason that ISP's don't allow direct connection with the db is security, so by putting a SQLyogTunnel.php in the root, we remove that security 🙂

3+4) I can live with that, didnt take much time!

5) That was with phpmyadmin, with sqlyog all chars were fine!

6-8) Ok, got ya, i guess you're working on the bugs then..

[Author]

Posts