Sync Problem With Mysql 5.0rc

[katla]

There seems to be a problem with sqlYog with regard to NOT NULL colums in tables on a MySQL 5.0RC server.

To reproduce:

When I copy a database from a 4.1.12 server to a 5.0RC server using sqlYog DB/Copy … it goes well.

When I later check structure between the to – sqlYog synch structure tells me that the NOT NULL on my 4.1.12 is NULL on the 5.0RC.

When I however check with Mysql Admin – they are NOT null on the 5.0RC also.

This also leads to failure of later db sync – since the table structures are “seemingly” differing..

(my servers are running on windows).

My SQLYog Enterprise is brand new – downloaded today.

---

Its a Great tool by the way! Would be even greater if you could find a way to do a table sync between a MS Sql server 2005 express and a MySQL server..

[peterlaursen]

I cananot reproduce this. See attached picture.

Upper database is a MySQL 4.1.14.

Lower is a 5.0.13

Data have been copied from 4.1.14 to 5.0.13 and you see that everything is “NOT NULL” as it should be.

We will need create statements for the tables identical to yours. A a litte bit of samle data would be nice too …

[peterlaursen]

I found this question in the FAQ sytem:

“I just bought sqlYog enterprise. 1) I copy a db from a 4.1.12 server to a 5.0RC server using sqlYog db/copy db.. 2) I compare using structure synch. Structure sync somehow does not seem to recognise columnd with NOT NULL set on the 5.0RC server. (they are however . I have checkd with Mysql ADmin). First I thought it was SQLYog that didnt copy it correct – but it seems to be something wring in the way SQLYog fetched this info from 5.0RC. Is this a bug or are I trying to do somethinsg that SQLYog are not ready to do yet? This also leads to that the database sync fails since the structures are recognised as different.”

I think this belongs in the Forums! I'll delete the entry from the FAQ – just for your information!

[katla]

Quote:

I think this belongs in the Forums!  I'll delete the entry from the FAQ – just for your information!

No problem – I first posted it in the faq – but there were some error messages on the top of the screen – so I was not sure whether is was stored or not..

—

Here is how it looks like in sql compare

First how structure compare looks:

This is how source looks like in SQLyog

And this is how Target looks like.

a) The 4.1.12 is intalled on a windows 2003 server and the 5.0.rc is on a w2K server

😎 i run the sqlYog enterprise on my windows XP. logon is root for both servers and i use normal server connection (no tunnelling)

weird..

[peterlaursen]

I am sorry … but you will have to guide me a little bit more!

To me it all looks identical.

But did you read this discussion:

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

… and did you try the SQLyog 4.2 beta5 ?

Do you also have the NULL statements in the jobfile?

When you are sync'ing is then between identical MySQL-versions?

But I'd wait for Ritesh to reply too.

He knows exactly what they did between beta4 and beta5.

[peterlaursen]

I think I may have found out something by now!

The “betcode”-column has NULL undefined in source and NO in target.

That seems to be one more difference (or a bug?) of MySQL version 4.1 and 5.0.

Null-definition is NO for autoincrement columns with 5.0 and undefined with 4.1. It is not the SQLyog copy too that causes this, I belive. It occurs like that everywhere with the two MySQL versions.l

I would not mind to work more into it, but let us build an “plan of attack” together with Ritesh …

[peterlaursen]

Maybe this is a little bit easier to overview:

Two similar databases (a MySQL 4.1 and a 5.0 database) as shown in SQLyog Object Pane.

For the 4.1 database observe:

It the listing at the top the NUL- setting is not explicitly “NO” for column “ID”, The “create table” statement however has “NOT NUL in the definition”. And it is correct that MySQL adminstrator shows the column as NOT NUL (the adm.jpg is a shot of the 4.1 database)

For the 5.0 database the setting in the listing at the top IS explicitly “NO” for column “ID”.

…. hmmmm

[Ritesh]

Problem understood <_< SQLyog checks whether there is any string under NULL column or not. MySQL versions < 5.0 used to have YES and BLANK STRINGS for NULL and NOT NULL value respectively.

MySQL versions > 5.0 seems to have NO explicitly to define columns having NOT NULL property and thus the errors.

This issue affects multiple features of SQLyog. I have asked my developer to look into the issue.

Will be fixed in one of the future BETAS of v4.2.

[Ritesh]

katla wrote on Oct 5 2005, 09:39 PM:

Its a Great tool by the way!  Would be even greater if you could find a way to do a table sync between a MS Sql server 2005 express and a MySQL server..

[post=”7437″]<{POST_SNAPBACK}>[/post]

You can indeed do it using the ODBC Import Tool (Migration Toolkit in v4.2 BETAs).

[peterlaursen]

@Ritesh

didn't you forget a link? 😀

http://www.databasejournal.com/features/my…cle.php/3550146

[katla]

Ritesh wrote on Oct 6 2005, 01:29 PM:

You can indeed do it using the ODBC Import Tool (Migration Toolkit in v4.2 BETAs).

[post=”7460″]<{POST_SNAPBACK}>[/post]

Yes! I understand that I can do an Import. But I would like to do an automatic sync every 15 minutes or so so i can do this for a kind of insurance if the primary server goes down.

Ideally I would have liked to have replication which is very good with MS SQL servers – but the cost of 2 complete servers are prohibitive. But MS is making a sql server 2005 Express which is free – but it only takes replication TO it – not FROM it. And it does not support what was great with ms sql 2000 – DTS (its not called that now).

I know I can do quite a lot with MySQL and I am now doing all my development work for inhouse solutions for this DB – but since I have heard rumours about “instability” from friends when databases grow (on Windows that is) – I would have preferred to use MS SQL server since it is fantastic when it comes to stability (and a lot ofther things as well). But MySQL is so far quite nice too – but there are too many bugs leading to lots of work. And up to now the lack of Stored procedures, trigger and views have been a limitation.

A bug example for Mysql (4.1.12 at least): a ' in a varchar field lead to that the backup in 4.1.12 is impossible to use since the mysql outputs ,', for it (while it would output ,'A', if the field had contained an A. I spent several hours last mondays with this simple error which lead to that the restore did not work.

(and the need for the backup was that I tested sqlYog and accidentally destroyed my production database… thats how newbies with sqlyog can “misuse it”..)

[Ritesh]

Quote:

Yes! I understand that I can do an Import. But I would like to do an automatic sync every 15 minutes or so so i can do this for a kind of insurance if the primary server goes down.

You can schedule SJA to run between any time intervals.

Did you try out the TRIGGER option in SQLyog ODBC Import Tool? You can use it in conjunction with WHERE clause to incorporate SYNC feature between MS SQL and MySQL.

You should read the article as posted by Peter Laursen. BTW, Peter is our in house data sync and ODBC Import expert. He will be more then happy to help you out with your problems.

Quote:

(and the need for the backup was that I tested sqlYog and accidentally destroyed my production database… thats how newbies with sqlyog can “misuse it”..)

I am not sure if I understood the problem. Can you explain it a little more?

[peterlaursen]

Quote:

But I would like to do an automatic sync every 15 minutes

You can do that!

You add a datetime colum to each table for the MS-SQL database.

Then you use the trigger-facility of the SQLyog Migration Tool to update the datetime, every time data are written to MySQL. Also create an “automatic timestamp” with the MS SQL server (that is updated each time some change is done with a row).

Then use the SQLyog Migration -option to only transfer rows having datetimes or timestamps bigger than time of last sync. Note that the trigger-sql and the where-sql must be MS SQL syntax!

This is “incremental import”.

Read the article. There is also a simple “incremental import” example in the SQLyog helpfile!

[peterlaursen]

My God —- I've become an expert.

That should be in organic beers and Highland Malts 🙂

[peterlaursen]

BTW:

you will also need some way to “clean up” orphaned entries in the MySQL-database (entries that don't exist in the MS SQL database anymore).

…. hmmmm

Any ideas ????

[katla]

Ritesh wrote on Oct 6 2005, 02:12 PM:

I am not sure if I understood the problem. Can you explain it a little more?

The problem was that I did something which I should not do… I actually messed around with connections so that when I thought I worked on the replica – I actually worked on the original and it effectively did what it was told to do – and destroyed my original database. But since I were unsure about how sqlYog worked – I did take an mysql backup before I started messing about.. and that was when i found the bug in mysql backup…

(there are lots of other annoying things in mysql – text colums can for instance not be used in recordsets when I am updating (like I am used to from ms sql server) – the ODBC driver is not as good as it should be.. But I guess that is what is the price one have to pay for a software that does not drive one into bankrupcy..)

[peterlaursen]

Quote:

and that was when i found the bug in mysql backup

which bug?

Are you talking about the one we are discussing in this thread, with the the failure to sync, or something else ?

[peterlaursen]

Quote:

text colums can for instance not be used in recordsets when I am updating

You can consider to use VARCHAR with MySQL version 5. With MySQL 5 VARCHARS can be about 65000 charcters (as of 255 with previous versions). VARCHARs are more effective with MySQL than TEXTs, due to the different ways that they are stored.

BTW: I don't know the term “recordset” in MS SQL – terminology!

There really has been a lot of issues with the ODBC drivers for MySQL after 3.51.6 (that was real good). however 3.51.6 does not support MySQL greater than version 4.0.

There is a 3.51.12 in beta and a 5.0 in alpha. The 5.0 is recommended for MySQL version 5 (better datatype compability). But it is obvious thht MySQL have big problems with ODBC! And the 5.0 alpha won't install on either of my systems!

However their JDBC driver is excellent. But that won't help you if have applications in Visual Basic or Delphi …

the is a good FTP-mirror with MySQL stuff here:

ftp://sunsite.dk/mirrors/mysql/Downloads/

[peterlaursen]

thinking about this problems with “orphaned” entries in the MySQL database.

I think it would take another option here (see picture) to choose “delete from target when row is not in source”.

Will probably take a lot of coding to implement. 🙁

[Ritesh]

peterlaursen wrote on Oct 6 2005, 03:24 PM:

thinking about this problems with “orphaned” entries in the MySQL database.

I think it would take another option here (see picture) to choose “delete from target when row is not in source”.

Will probably take a lot of coding to implement.  🙁

[post=”7474″]<{POST_SNAPBACK}>[/post]

I have added this in the TO-DO list. Maybe we will implement in v4.3. This is more of a 2-way sync issue!

The only issue that I can think of – how a SELECT-WHERE clause with BINARY data works in ODBC? This wont be a problem if a PRIMARY key is defined on the table but then my experience says that many people do have tables without PRIMARY KEYS!

Actually, thinking more on the above issue, it will be a problem if somebody has a PRIMARY KEY on binary data column?

[Ritesh]

Quote:

I did take an mysql backup before I started messing about.

Its always good to have backups of your data 😀

[peterlaursen]

Quote:

This is more of a 2-way sync issue!

I thnik it is a one-way sync issue!.

You have a production DB (not MySQL) that you a intervals copy to a MySQL DB.

Now image that a row is deleted at the source. There is no way for the Migration Tool to delete the corresponding row in MYSQL, except to delete the table from the start.

[Author]

Posts