Tables Dont Sync, No Log Either

[sathyaw]

Hi there,

I am using SJA linux ersion to keep my backup database uptodate with the production. Backup database is on a seperate server and I run SJA as a cron job. I have a problem with one particular table. This table has two primery keys. If I drop all the rows on my backup table and run SJA it will copy everything without a problem, but updates will not take place.

here is the output when I run SJA;

[root@localhost sja]# ./sja sync_vobbus22.xml

SQLyog Job Agent Version 5.0

Copyright © Webyog Softworks Pvt. Ltd.. All Rights Reserved.

Sync started at Mon Feb 6 13:09:35 2006

Table SrcRows TgtRows Inserted Updated Deleted

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

`tab1` 11629 11255 0 0 0

`tab2` 541 541 0 2 0

`tab3` 541 541 0 0 0

`tab4` 508 508 0 0 0

`tab5` 1333 1333 0 0 0

Total time taken – 2 sec(s)

eventhough target rows are less it is not writing.

now if I empty the table using sqlyog and run the same SJA

[root@localhost sja]# ./sja sync_vobbus22.xml

SQLyog Job Agent Version 5.0

Copyright © Webyog Softworks Pvt. Ltd.. All Rights Reserved.

Sync started at Mon Feb 6 13:10:39 2006

Table SrcRows TgtRows Inserted Updated Deleted

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

`tab1` 11629 0 11629 0 0

`tab2` 541 541 0 0 0

`tab3` 541 541 0 0 0

`tab4` 508 508 0 0 0

`tab5` 1333 1333 0 0 0

Can someone point me to find why this is happening and any pointers to fix it, greatly appreciated.

Cheers

Sathya

[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.

[sathyaw]

here is how the table is made;

/*Column Information For – vobbnj22cpy.tab1*/

---

Field Type Null Key Default Extra Privileges

---

---

---

---

---

---

---

ctime timestamp(14) YES PRI (NULL) select,insert,update

c_num varchar(40) YES (NULL) select,insert,update

did varchar(40) YES (NULL) select,insert,update

disposition varchar(20) YES (NULL) select,insert,update

billseconds int(11) YES (NULL) select,insert,update

rate int(11) YES (NULL) select,insert,update

billcost int(11) YES (NULL) select,insert,update

uniqueid varchar(40) PRI select,insert,update

/*Index Information For – vobbnj22cpy.tab1*/

---

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment

---

---

---

---

---

---

---

---

---

---

---

---

cdrs 0 PRIMARY 1 ctime A (NULL) (NULL) (NULL) BTREE

cdrs 0 PRIMARY 2 uniqueid A 11629 (NULL) (NULL) BTREE

/*DDL Information For – vobbnj22cpy.cdrs*/

---

Table Create Table

---

---

cdrs CREATE TABLE `cdrs` (

`ctime` timestamp(14) NOT NULL,

`c_num` varchar(40) default NULL,

`did` varchar(40) default NULL,

`disposition` varchar(20) default NULL,

`billseconds` int(11) default NULL,

`rate` int(11) default NULL,

`billcost` int(11) default NULL,

`uniqueid` varchar(40) NOT NULL default '',

PRIMARY KEY (`ctime`,`uniqueid`)

) TYPE=MyISAM

[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]

@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.

[sathyaw]

actually unequeid is not uneque as it says, hence cant make it the only PK. SQL versions are both on 4.0.18.

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.

I have the option that I can do anything with the second table, is there anything that I can try only on the second table to get over with this problem ?

sorry, I thought I posted this last night, but was only previewing 😮

[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]

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

[sathyaw]

oh man, you are great, really appreciate your help.

what we see here is that so called lines with duplicate time stamps being not updated. thing is that in my case target tabel is not getting updated at all.

Table Op Msg_type Msg_text

---

---

---

---

vobbnj22cpy.cards check status OK

Table Op Msg_type Msg_text

---

---

---

---

vobbnj22cpy.cards analyze status Table is already up to date

I will definetly get some definitive data. I am travelling at a customer location hence the slow response but will get it by the end of this week for sure.

again thanks a lot

[Ritesh]

This is not related to checksum generation problem. Somehow the timestamps are not getting updated correctly between the source and target. Is it due to SJA or due to some restrictions in MySQL, will let you know by evening.

I will now try with Peters test case and see if I can reproduce the bug.

[peterlaursen]

@ritesh

I don't think that I will have to remind you that:

1) The fix should be verified on all major MySQL versions – since there are quite a lot code changes with various MySQL's regarding TIMESTAMP handling.

2) It should be checked if it is only a TIMESTAMP that does this. A TIME, a DATE -variable maybe to ??

3) Also if a TIMESTAMP (or a TIME) is used alone as a PK, does that behave the same way?

You may ask me to check some of this. However it would be waste if you did allready … 😉

BTW: does SJA do anything like GROUP BY ?? If yes then MySQL builds temporary table(s) and that could be part of it.

[Ritesh]

peterlaursen wrote on Feb 8 2006, 11:57 AM:

BTW: does SJA do anything like GROUP BY ??  If yes then MySQL builds temporary table(s) and that could be part of it.

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

Hmmm. We indeed do that. Anyway, my engg. will be working on this issue in another couple of hours. Will revert back soon.

[peterlaursen]

1)

I came across a bug report at bugs.mysql.com. there was an issue with GROUP by . Now a TINYINT is not a true boolean. The temp table stores as an integer. This results in 'type loss'. Similar a TIMESTAMP is not a true TIME-variable – it is a char(14) as of 4.0.x. So what hapapens when it is store in a temp table … ?

Just an idea!

2)

I could not help reasearching a little bit into it myself!

Code:

Test 1+2+3:
*********

Source data:

ts                       id  t      
——————-  ——  ——–
2006-02-07 15:34:13      33  Ã¸Ã¸Ã¸    
2006-02-07 15:34:13      34  Ã¸Ã¸      
2006-02-07 15:34:13      35  Ã¸Ã¸Ã¸Ã¸Ã¸Ã¸  
2006-02-07 15:37:00       1  bøllehat
2000-01-01 00:00:00       1  slut    
0000-00-00 00:00:00       1  hakkebøf
2001-11-11 11:11:11       1  bøvl    
2001-11-11 11:11:11       2  griseøre

target data:

ts                       id  t      
——————-  ——  ——–
2006-02-07 15:34:13      33  Ã¸Ã¸Ã¸    
2006-02-07 15:34:13      34  Ã¸Ã¸      
2006-02-07 15:34:13      35  Ã¸Ã¸Ã¸Ã¸Ã¸Ã¸  
2006-02-07 15:37:00       1  bøllehat

(this formatting is as MySQL 4.0.x displays a DATETIME)

Test one:
*********

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

Table                      SrcRows  TgtRows  Inserted  Updated  Deleted
=========================  =======  =======  ========  =======  =======
`p1a`                            8        4         2        0        0  

Test two:
*********

CREATE TABLE `p1a` (                    
         `ts` datetime NOT NULL default '0000-00-00 00:00:00',      
         `id` bigint(20) NOT NULL default '0',  
         `t` varchar(20) default NULL,          
         PRIMARY KEY  (`ts`,`id`)              
       ) TYPE=MyISAM

Table                      SrcRows  TgtRows  Inserted  Updated  Deleted
=========================  =======  =======  ========  =======  =======
`p1a`                            8        4         4        0        0  

test three:
***********

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

Table                      SrcRows  TgtRows  Inserted  Updated  Deleted
=========================  =======  =======  ========  =======  =======
`p1a`                            8        4         2        0        0  

test4:
*******

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

(data must now be changed so the TIMESTAMP is unique)

Source data:

           ts      id  t      
————–  ——  ——–
20060207153413      33  Ã¸Ã¸Ã¸    
20060207153414      34  Ã¸Ã¸      
20060207153415      35  Ã¸Ã¸Ã¸Ã¸Ã¸Ã¸  
20060207153700       1  bøllehat
20000101000000       1  slut    
00000000000000       1  hakkebøf
20011111111111       1  bøvl    
20011111111112       2  griseøre

Target data:

           ts      id  t      
————–  ——  ——–
20060207153413      33  Ã¸Ã¸Ã¸    
20060207153414      34  Ã¸Ã¸      
20060207153415      35  Ã¸Ã¸Ã¸Ã¸Ã¸Ã¸  
20060207153700       1  bøllehat

(this formatting is as MySQL 4.0.x displays a TIMESTAMP)

Table                      SrcRows  TgtRows  Inserted  Updated  Deleted
=========================  =======  =======  ========  =======  =======
`p1a`                            8        4         2        0        0  

Conclusion(s):
**************
1: The issue is not reproduceable with a DATETIME – only a TIMESTAMP.
2: TIMESTAMPs need not be identical to reproduce the problem.

In test4 the data not INSERTED on target are
20011111111111       1  bøvl    
20011111111112       2  griseøre

In test1+2+3 they are
2001-11-11 11:11:11       1  bøvl    
2001-11-11 11:11:11       2  griseøre

All tested on MySQL 4.0.26

[peterlaursen]

And on MySQL 5.1.5

Code:

Table                      SrcRows  TgtRows  Inserted  Updated  Deleted
=========================  =======  =======  ========  =======  =======
`p1a`                            8        4         2        0        0  

same!

Added: also same on MySQL 3.23.58

[peterlaursen]

@sathyaw

I saw your face at the Forums, looking like 😡

No need to be angry anymore! The sync-with-timestamp-PK issue should be fixed with 5.1 BETA (works OK here). Can you confirm?

[sathyaw]

so where can I download the 5.1 BETA ?

😛

[peterlaursen]

You have a PM!

[sathyaw]

appreciate if It is possible to know when this fix can be incorporated to a alpha or beta code, i mean the linux version.

[Author]

Posts