Definition Mismatch?

[chuck]

just the other day I started getting an error when I sync. ANY of my databases. I have been doing the sync. the same way for almost a year and have never had any problems.

The error I get is:

NULL definition mismatch for 'ID' column in '`amuriaregister`' table

I get this on every table in every database.

I created a blank 'test' database on my localhost to test the sync. Synchronized the server database(source) whth my empty localhost test database and everything is fine.

but, when I do the sync. a second time (server to test) I get the error above.

I'm not sure what is causing this problem and it is starting to conflict with some of the online reporting because the data is not being updated.

Does anyone know what started causing this problem and how to resolve it?

Thanks in advance

-Chuck

[peterlaursen]

What are the MySQL server versions and the SQLyog version ?

Anything being updated since it last worked? MySQL server at Server/ISP maybe?

(you can execute 'select version()' to get server versions)

I think that I can remember that 'NULL definition mismatch' occured with earlier SQLyog versions when trying to sync incompatible MySQL versions.

Did I get you right that BOTH syncs were server >> local? And it succeeds first time but not second time? And the DB was empty both times ?

And you have checked that the NULL definitions are identical in the 'create statement for the table' ?

I guess that the column that causes trouble is both first column and Primary Key?

Also please paste in a simple 'create statement' with this problem to reproduce the problem.

[peterlaursen]

You may also read this:

http://www.webyog.com/faq/10_68_en.html

But SQLyog before (I think) 4.1 did not abort in this situation, and thus the error could occur.

And did you ever read this one?

http://www.webyog.com/articles/Using_SQLyo…L_Databases.pdf

[chuck]

Localhost and server have the same version of MySql and SqlYog. I've been using them together for almost a year.

Did I get you right that BOTH syncs were server >> local? And it succeeds first time but not second time? And the DB was empty both times ?

Yes, but on the local I started with an empty database. I synched two tables, one empty and one full. All the data came over the first time with no errors but, the second time, I got the errors on both tables with no data. Some fields were added recently and I took them out but no changes.

Yes, the column causing the problems is the first field and the pk

One thing I do notice in the create statement on the localhost is at the bottom it says CHARSET=latin1. It does not say that on the server. What I find weard about this is that sja created this table when it synched.

create statement(server):

CREATE TABLE `amuriaregister` (

`ID` int(100) NOT NULL default '0',

`DistTBNum` varchar(50) default NULL,

`Name` varchar(50) default NULL,

`Contact` varchar(50) default NULL,

`Sex` varchar(50) default NULL,

`Age` decimal(3,0) default NULL,

`TreatStartDate` date default NULL,

`Regimen` varchar(50) default NULL,

`DiseaseClass` varchar(50) default NULL,

`PatientCat` varchar(50) default NULL,

`TransferIn` varchar(50) default NULL,

`TransferReason` varchar(50) default NULL,

`TreatStopDate` varchar(50) default NULL,

`TreatComplete` varchar(50) default NULL,

`TreatNotComplete` varchar(50) default NULL,

`Remarks` varchar(50) default NULL,

`RegisterDate` varchar(50) default NULL,

`DiagUnit` varchar(50) default NULL,

`DiagUnitNumber` varchar(50) default NULL,

`TreatUnit` varchar(50) default NULL,

`TreatUnitNumber` varchar(50) default NULL,

`HIVStatus` varchar(50) default NULL,

`DisTrans` varchar(50) default NULL,

`PTDate` varchar(50) default NULL,

`PTResults` varchar(50) default NULL,

`TmoDate` varchar(50) default NULL,

`TmoResults` varchar(50) default NULL,

`FmoDate` varchar(50) default NULL,

`FmoResults` varchar(50) default NULL,

`EmoDate` varchar(50) default NULL,

`EmoResults` varchar(50) default NULL,

`GEmoDate` varchar(50) default NULL,

`GEmoResults` varchar(50) default NULL,

`TreRemarks` varchar(50) default NULL,

`Surname` varchar(50) default NULL,

`RCat` varchar(50) default NULL,

`THmoDate` varchar(50) default NULL,

`THmoResults` varchar(50) default NULL,

`DistNam` varchar(50) default NULL,

[chuck]

Sorry about that. It did not post everything I wrote. I guess it was to long

end of create statement(server)

PRIMARY KEY (`ID`)

) TYPE=MyISAM

create statentet(localhost)

CREATE TABLE `amuriaregister` (

`ID` int(100) NOT NULL default '0',

`DistTBNum` varchar(50) default NULL,

`Name` varchar(50) default NULL,

`Contact` varchar(50) default NULL,

`Sex` varchar(50) default NULL,

`Age` decimal(3,0) default NULL,

`TreatStartDate` date default NULL,

`Regimen` varchar(50) default NULL,

`DiseaseClass` varchar(50) default NULL,

`PatientCat` varchar(50) default NULL,

`TransferIn` varchar(50) default NULL,

`TransferReason` varchar(50) default NULL,

`TreatStopDate` varchar(50) default NULL,

`TreatComplete` varchar(50) default NULL,

`TreatNotComplete` varchar(50) default NULL,

`Remarks` varchar(50) default NULL,

`RegisterDate` varchar(50) default NULL,

`DiagUnit` varchar(50) default NULL,

`DiagUnitNumber` varchar(50) default NULL,

`TreatUnit` varchar(50) default NULL,

`TreatUnitNumber` varchar(50) default NULL,

`HIVStatus` varchar(50) default NULL,

`DisTrans` varchar(50) default NULL,

`PTDate` varchar(50) default NULL,

`PTResults` varchar(50) default NULL,

`TmoDate` varchar(50) default NULL,

`TmoResults` varchar(50) default NULL,

`FmoDate` varchar(50) default NULL,

`FmoResults` varchar(50) default NULL,

`EmoDate` varchar(50) default NULL,

`EmoResults` varchar(50) default NULL,

`GEmoDate` varchar(50) default NULL,

`GEmoResults` varchar(50) default NULL,

`TreRemarks` varchar(50) default NULL,

`Surname` varchar(50) default NULL,

`RCat` varchar(50) default NULL,

`THmoDate` varchar(50) default NULL,

`THmoResults` varchar(50) default NULL,

[chuck]

happened again…damn 🙂

FmoDate` varchar(50) default NULL,

`FmoResults` varchar(50) default NULL,

`EmoDate` varchar(50) default NULL,

`EmoResults` varchar(50) default NULL,

`GEmoDate` varchar(50) default NULL,

`GEmoResults` varchar(50) default NULL,

`TreRemarks` varchar(50) default NULL,

`Surname` varchar(50) default NULL,

`RCat` varchar(50) default NULL,

`THmoDate` varchar(50) default NULL,

`THmoResults` varchar(50) default NULL,

`DistNam` varchar(50) default NULL,

PRIMARY KEY (`ID`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1

[peterlaursen]

Pretty strange.

But please answer:

What are the MySQL version(s) ?

And the SQLyog version?

Server running *nix and local Windows ?

I shall try to reproduce it then.

This

Quote:

One thing I do notice in the create statement on the localhost is at the bottom it says CHARSET=latin1. It does not say that on the server. What I find weard about this is that sja created this table when it synched.

….hmmm

[peterlaursen]

But why

Code:

`ID` int(100) NOT NULL default '0',

and not

Code:

`ID` int(100) NOT NULL auto_increment,

Also an int(100) is a strange thing! A Int is standard 10 characters(4 bytes)

ranging from -2147483648 to 2147483647 (signed) or from 0 to 4294967295 (unsigned). You just get a lot of (90 to be exact!) zerofills the way you do.

read about numerical types: http://dev.mysql.com/doc/refman/4.1/en/numeric-types.html

I don't know if this could be part of the problem. I don't think so. The existence of the ' DEFAULT CHARSET=latin1' indicates that local MySQL version is 4.1 and higher , server version 4.0 or lower.

I can get this error with old SQLyog versions and when syncing from MySQL 5.0 to 4.0.

Would you please check your versions EXACTLY! ALL of them! Don't GUESS/THINK but CHECK it 😉

[chuck]

On local I'm running Mysql 5.0 and SqlYog 5.0.16 (I just noticed in SqlYog in the help>about it says 4.04 but select version() returns 5.0.16), windows xp

Server is running Mysql 4.0 and SqlYog 4.0.13, windows 2000

The ID field is generated @ runtime. It's always number of rows +1. Kind of a pain to try and explain why I had to do this.

I made these tables in SqlYog so I'm not sure why it made int(100). I just named the column ID with the datatype of int…Yog did the rest.

The oddest thing about this is nothing has changed. no upgrades. same system for over a year with out any problems.

Now, I know what your going to say about no upgrades “you have the latest version of SqlYog”, true, but the person that brought this error to my attention is runing the same version of MySql and SqlYog as the server and is unable to sync. His computer is the important one. It is collecting data remotely(offline) and synchronizing when a connection is available.

I don't if this helps with the debuging but I tryed to copy from server – local and it worked fine. when I copy from Local – Server I get the Mysql syntax error saying that ENGINE=MyISAM DEFAULT CHARSET=latin1 is the wrong syntax

Do you think I should try upgrading all the systems?

…also thanks for all the help

-Chuck

[peterlaursen]

Quote:

On local I'm running Mysql 5.0 and SqlYog 5.0.16 (I just noticed in SqlYog in the help>about it says 4.04 but select version() returns 5.0.16), windows xp.

Server is running Mysql 4.0 and SqlYog 4.0.13, windows 2000

Mismatch! There is no SQLyog 5.016 or 4.013! And servers are not running SQLyog at all! SQLyog is a MySQL client.

'Select version()' returns the MySQL server version. It is then 5.0.16 on local and 4.0.13 on remote server. SQLyog version is then 4.04 – that is what the help menu from SQLyog shows.

It is exactly as I guessed it. You are trying to sync a MySQL server 5.0 (local) to a MySQL 4.0 (remote). It is not possible! The first link to the Webyog FAQ that I posted explains why! The most recent versions of SQLyog (from 4.1 as far as i remember) abort in this situation – older versions as yours attempt to do the sync and then the 'NULL mismatch' error occurs.

MySQL 5.0.16 is very recent (it was uploaded to MySQL FTP-mirrors 2005-12-06) so you must have updated your local MySQL since then! That was what I asked you in the first question.

To sync with a remote 4.x server you must run a local 4.x. And you should consider to upgrade SQLyog. It is free if you have a legal/registered copy of SQLyog. But it still won't sync MySQL 4.0 with 5.0.

[peterlaursen]

Quote:

same system for over a year with out any problems

No! It is impossible! You must have installed MySQL 5.0.16 on local within the last 2 weeks!

[chuck]

I installed the latest versions on MY local to test the error that had been reproted to me. The remote computer(as far as I know)has not had any upgrades and has been working fine… til now.

The versions I wrote about were between me(local) and the server. Not the computer that does the synching. And we're both having the same problems.

At first I did not think that the versions were causing the problems so I downloaded the latest version onto my computer to test the sync to the server.

I'll check the versions on the remote computer. I Should probly upgrade everything.

Thanks

[peterlaursen]

😀

The SQLyog sync tool won't sync across 4.x and 5.x. So you will have to upgrade the remote server or 'downgrade' the local.

[Author]

Posts