[peterlaursen]

And of course ..

After dropping the PK in the above example and creating a new one using a auto-increment column everything works perfectly.

Also this table definition

Code:

CREATE TABLE `p1a` (                    
         `ts` timestamp(14) NOT NULL,          
         `id` bigint(20) NOT NULL default '0',  
         `t` varchar(20) NOT NULL default '',  
         PRIMARY KEY  (`id`,`t`)                
       ) TYPE=MyISAM

sync's perfectly. So it is somehow related to the use of a TIMESTAMP with the PK

[peterlaursen]

Quote:

if the data can be added to first table (in first server), why SJA cant add the same data to the second table (in the second server ) which was actually copied by SQLYOG GUI in the first place is puzzling me.

Right .. that is the question. And it should. Maybe my idea of a server/client conflict with a PK using a TIMESTAMP is part of the answer. I just tested with 4.0.26 and the automatic server rule 'ON UPDATE' also applies here. But I dno't know if it is a silly idea. But then a MySQL concat_ws() issue ??? Or the SJA checksums algorithm?? RITESH 😮

You will have to wait for Ritesh. He knows exactly what SQL the SJA is writing. But a small 'reproduceable test case' would speed up things I believe. So this is what I can offer:

I get:

Code:

Table                      SrcRows  TgtRows  Inserted  Updated  Deleted
=========================  =======  =======  ========  =======  =======
`p1a`                           18       14         2        0        0  

with this table

Code:

CREATE TABLE `p1a` (                        
         `ts` timestamp(14) NOT NULL,              
         `id` bigint(20) NOT NULL auto_increment,  
         `t` varchar(20) default NULL,            
         PRIMARY KEY  (`ts`,`id`)                  
       ) TYPE=MyISAM                              

with these source data:

Code:

insert into `p1a` (`ts`,`id`,`t`) values

(20060207153413,29,'peberbøsse'),
(20060207153413,26,'østerssauce'),
(20060207153413,21,'øressund'),
(20060207153413,20,'tøsedreng'),
(20060207153413,28,'griseøre'),
(20060207153413,27,'ønskedrøm'),
(20060207153413,25,'ødeland'),
(20060207153413,30,'ølhund'),
(20060207153413,31,'øllebrød'),
(20060207153413,32,'ø'),
(20060207153413,33,'øøø'),
(20060207153413,34,'øø'),
(20060207153413,35,'øøøøøø'),
(20060207153700,1,'bøllehat'),
(20000101000000,1,'slut'),
(00000000000000,1,'hakkebøf'),
(20011111111111,1,'bøvl'),
(20011111111111,2,'griseøre');

And these target data

Code:

insert into `p1a` (`ts`,`id`,`t`) values

(20060207153413,29,'peberbøsse'),
(20060207153413,26,'østerssauce'),
(20060207153413,21,'øressund'),
(20060207153413,20,'tøsedreng'),
(20060207153413,28,'griseøre'),
(20060207153413,27,'ønskedrøm'),
(20060207153413,25,'ødeland'),
(20060207153413,30,'ølhund'),
(20060207153413,31,'øllebrød'),
(20060207153413,32,'ø'),
(20060207153413,33,'øøø'),
(20060207153413,34,'øø'),
(20060207153413,35,'øøøøøø'),
(20060207153700,1,'bøllehat');

After the job has run data at target are:

Code:

insert into `p1a` (`ts`,`id`,`t`) values

(20060207153413,29,'peberbøsse'),
(20060207153413,26,'østerssauce'),
(20060207153413,21,'øressund'),
(20060207153413,20,'tøsedreng'),
(20060207153413,28,'griseøre'),
(20060207153413,27,'ønskedrøm'),
(20060207153413,25,'ødeland'),
(20060207153413,30,'ølhund'),
(20060207153413,31,'øllebrød'),
(20060207153413,32,'ø'),
(20060207153413,33,'øøø'),
(20060207153413,34,'øø'),
(20060207153413,35,'øøøøøø'),
(20060207153700,1,'bøllehat'),
(00000000000000,1,'hakkebøf'),
(20000101000000,1,'slut');

The ones that are not imported are:

Code:

(20011111111111,1,'bøvl'),
(20011111111111,2,'griseøre');

note that they have an identical timestamp.

And @sathyaw: you are supposed to create such test case yourself!

Don't forget that you are asking for support on a free program!

On Webyog support read:

http://www.webyog.com/faq/1_26_en.html

@sathyaw: one last thing:

can you confirm that the rows skipped in your case also have identical timestamps?

[peterlaursen]

@sathyaw

Any news here? Did you try my proposal? Anything else you tried? Do you have any additional information?

Also if you could create a small 'test case' with only a few rows of data, that would be very useful. Let's say if you can reproduce the sync failure with about 10-20 rows in source and 5-10 rows in target, I think Ritesh would like to have a copy of such small data-set.

Also: what does a 'repair table …' or 'check table ..' return? A corrupted key could be part of the problem.

[peterlaursen]

But maybe it was better to find the reason for that corruption and eliminate that reason? Such corruption probably is corruption of indexes. Corrupted indexes could lead to corruption of data themselves.

Appropriate use of LOCK's, FLUSH's etc. with the scripts could be the way out …

And you could insert a Check table tablename in the scripts and 'pick up' the server response (variable 'Msg_text'). If it is not 'OK' then you found the place where things start to go wrong.

[peterlaursen]

You can use a SJA 'Notifications Service' Job for this.

Just execute a statement like

Code:

repair  table `test`.`t1`;

with that 'Notifications Job' and schedule that job with CRON.

Further you can repair manually from SQLyog .. tools .. table diagnostics. When it is done like this you can copy the SQL to use with SJA from the HISTORY pane.

[peterlaursen]

I think it takes three parameters … don't forget the jobfile! 😉

like:

sja jobfile.xml -llog.txt -ssession.xml

The -s and -l parameters for specifying the session file and the log file are optional. A complete file path can be specified. That is useful if you don’t have write privilege to the installation folder. If not specified default values logfile.txt and session.xml are used and files are placed in the directory from where SJA runs.

The order of the three parameters is unimportant.

[peterlaursen]

well .. yes it is definitely a Primary Key! 😀

I also would like to know the the MySQL version(s). That is because that with the more recent MySQL versions there are certain rules on how the the first timestamp in the table is handled by the server. It is treated like: “timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP”. Now SJA tries to insert certain values (the same as on the source). However the server tries to insert CURRENT_TIMESTAMP. It looks like it generates some conflict with the result that nothing is done.

The MySQL docs http://dev.mysql.com/doc/refman/5.0/en/datetime.html say

Quote:

The TIMESTAMP data type has varying properties, depending on the MySQL version and the SQL mode the server is running in.

and more here: http://dev.mysql.com/doc/refman/5.0/en/timestamp-4-1.html

Ritsh must be able to tell tomorrow it the order of INSERTS generated by the SJA here will conflict with the server rule in some way. When syncing against an empty table all column are inserted in one insert statement and thus no conflict here.

My proposal – something you could test with the data:

==================================

Would it not be possibly to use the uniqueid -field as a PK alone? Is it unique as the name indicates

Simply execute this on both servers

Code:

alter table `vobbnj22cpy`.`cdrs` drop primary key,  add primary key (`uniqueid` )

and try sync again.

EDIT: Sorry .. I did not write your db_name correctly!

Also .. I consider this to be a bug with the SJA. The rows are uniquely identified by the PK and should be sync'ed – no matter the data types and values! As the SQLyog GUI will handle TIMESTAMPS perfectly from version 5.1, SJA should too. Provided that I am right in my assumption of the cause for this.

[peterlaursen]

we need more info to be able to know what is happening.

This

Quote:

This table has two primery keys

can't be true. There can only be one Primary Key! But do you mean a PK made from two columns?

Could we see the 'create statement for the table' please? What does this

Code:

show create table tab1;

return?

first of all I want to be sure that is is a PRIMARY KEY and not two UNIQUE INDEXES that you have!

This article:http://www.webyog.com/articles/Using_SQLyog_Enterprise_to_Effectively_Synchronize_MySQL_Databases.pdf

.. will show you the difference of how the SJA sync tools works with a Pk and without.

[peterlaursen]

I can add that one of the problems reported here – the wrong display of certain national characters in the editor – seems to be related to a corrupted SQLyog.ini -file. With a fresh install to an empty directory – and without copying the SQLyog.ini from the old installation – that problem is solved here.

But this does not solve the HTTP-tunnel write of certain character squences with national characters.

I still believe that is two non-related issues!

[peterlaursen]

Still I would try to re-install/re-compile the MySQL server. I think recompiling using a generic RPM-source would solve any problems with incompatible updates.

[peterlaursen]

I believe there are statically linked as well as a dynamically linked version available of the MySQL server, and versions that use the glic library and some that don't. Further if you have C-compiler there are sources available as a RPM-package.

With the YaST installer on SuSE it is a 5 minutes show to change the server. Even to compile it from source.

Something to choose from here

(5.0 -versions): ftp://sunsite.dk/mirrors/mysql/Downloads/MySQL-5.0/

(4.1 -versions): ftp://sunsite.dk/mirrors/mysql/Downloads/MySQL-4.1/

64-bit versions 4.1.18 compiled on SuSE 9 (non glibc-versions):

ftp://sunsite.dk/mirrors/mysql/Downloads/….sles9.ia64.rpm (Intel)

ftp://sunsite.dk/mirrors/mysql/Downloads/…les9.x86_64.rpm (AMD).

Glibc versions:

ftp://sunsite.dk/mirrors/mysql/Downloads/…libc23.ia64.rpm

ftp://sunsite.dk/mirrors/mysql/Downloads/…bc23.x86_64.rpm

this is a statically linked 32 bit version (??):

ftp://sunsite.dk/mirrors/mysql/Downloads/…1.18-0.i386.rpm (this is what I would use on my system, since there is no build for SuSE 10 yet. Every version from 4.1.10 to 5.0.18 has installed fine! But a 64 bit version like this does not seem to be available yet!)

And a source for Suse: ftp://sunsite.dk/mirrors/mysql/Downloads/…0.sles9.src.rpm. It compiles with a single mouse-click if you have the compiler installed.

(those are standard server binaries only, you will probably need the clients as well and the max-binaries. And of course you may check what is available from SuSE online-update).

I would consider reinstalling MySQL. Then you will get it updated too. But only of course if you have safe backup routines, in case something goes wrong.

[peterlaursen]

hmmm…

that is a 64 bit system. I have a 32 bit SuSE 10. But I don't think it will make much use to test with it.

However SuSE 64 bit systems should be pretty safe with 32 bit code – unlike Debian64 for instance.

@ritesh: on what system is it compiled ?

[peterlaursen]

YEP .. that is one of the basic concepts that you can save and re-use the jobfile.

[peterlaursen]

Quote:

i would prefer to use doubles, but using the import wizard it chooses to use decimal.

well, then override the proposal of that silly ODBC-driver! Simply press the 'map' button in the Migration Wizard for the column(s) and change the name and type as you like (as long as it is legal with the data).

In this respect that you can override the driver, the SQLyog Migration Tool is unique!

[peterlaursen]

hmmmm ..

there is a FAQ to write here on DECIMALs and SQLyog..

… done http://webyog.com/faq/29_90_en.html <_<

[Author]

Posts