[ssaamg]

Hi Ritesh,

I found that changing the datatype of one of the fields in the primary key the syncro is done fine.

My current primary key is made with four fields three of them are varchar and one is date.

Changing the date to varchar the sycro is made without problems.

[ssaamg]

yes, Roy, my question is about why the table is not being syncro. If the design of the table is not allowing the syncro then please let me know. I haven't found a reason on why SQLyog is not copying all the table. 6467 vs 6178 records. There is no error message.

[ssaamg]

for multiple pk I mean a pk with multiple fields. (not many pk in one table). I'm far, far, far, of being an DB expert, but I think the use of multiple field on a PK is a valid one. MySQL allows it and its use is documented(see below). As far as I know PK with multiple fileds is used in other DBs too.

On the other hand I agree with you that including the date to the PK is not the best way and will improve that. Thanks Peter.

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name

[(] LIKE old_tbl_name [)];

create_definition:

column_definition

| [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,…)

| KEY [index_name] [index_type] (index_col_name,…)

| INDEX [index_name] [index_type] (index_col_name,…)

[ssaamg]

Roy and Peter, Thanks for the reply,

The MySQL version is 4.0.2 (missed the 0,sorry)

On this specific table I am using the datetime format below (on others I use the timestamp(14) format)

I agree that there could be more than one record with the same data, but these is a multiple Primary key with more than one field making it impossible to repeat.

requestedDate datetime PRI 0000-00-00 00:00:00 select,insert,update,references

brand varchar(16) PRI select,insert,update,references

firstName varchar(16) PRI select,insert,update,references

lastName varchar(16) PRI select,insert,update,references

[ssaamg]

Hi,

Ritesh, I am using the las SQLyog version Enterprise 4 Registered .

I tested with the Windows version and also with the Linux Agent and got the same result.

Also created a backup database in the same machine and same result.

Find attached one of the tables.

note that on this one I didnt test on the dates (that has customer information and cant send) but the one I am attaching gives this result:

Sync started at Fri Feb 11 16:52:55 2005

Table SrcRows TgtRows Inserted Updated Deleted

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

`itinerary_daysinfo` 6467 6178 0 0 0

Total time taken – 33 sec(s)

The description of the table is as follows:

Field Type Null Key Default Extra Privileges

---

---

---

---

---

---

---

REGDATE timestamp(14) YES (NULL) select,insert,update,references

TOURCODE varchar(8) PRI select,insert,update,references

VERSIONDATE date PRI 0000-00-00 select,insert,update,references

BRAND varchar(32) PRI select,insert,update,references

STATUS varchar(8) PRI select,insert,update,references

DAYTITLE varchar(255) YES (NULL) select,insert,update,references

DAYDESCRIPTION text YES MUL (NULL) select,insert,update,references

SORTID int(2) PRI 0 select,insert,update,references

UNIQUEID int(11) MUL (NULL) auto_increment select,insert,update,references

/*Index Information For – lontours.itinerary_daysinfo*/

---

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

---

---

---

---

---

---

---

---

---

---

---

---

itinerary_daysinfo 0 PRIMARY 1 TOURCODE A (NULL) (NULL) (NULL) BTREE

itinerary_daysinfo 0 PRIMARY 2 VERSIONDATE A (NULL) (NULL) (NULL) BTREE

itinerary_daysinfo 0 PRIMARY 3 BRAND A (NULL) (NULL) (NULL) BTREE

itinerary_daysinfo 0 PRIMARY 4 STATUS A (NULL) (NULL) (NULL) BTREE

itinerary_daysinfo 0 PRIMARY 5 SORTID A 6467 (NULL) (NULL) BTREE

itinerary_daysinfo 1 UNIQUEID 1 UNIQUEID A (NULL) (NULL) (NULL) BTREE

itinerary_daysinfo 1 DAYDESCRIPTION 1 DAYDESCRIPTION A 6467 (NULL) (NULL) YES FULLTEXT

itinerary_daysinfo 1 DAYDESCRIPTION 2 DAYTITLE A 6467 (NULL) (NULL) YES FULLTEXT

/*DDL Information For – lontours.itinerary_daysinfo*/

---

Table Create Table

---

---

itinerary_daysinfo CREATE TABLE `itinerary_daysinfo` (

`REGDATE` timestamp(14) NOT NULL,

`TOURCODE` varchar(8) NOT NULL default '',

`VERSIONDATE` date NOT NULL default '0000-00-00',

`BRAND` varchar(32) NOT NULL default '',

`STATUS` varchar(8) NOT NULL default '',

`DAYTITLE` varchar(255) default NULL,

`DAYDESCRIPTION` text,

`SORTID` int(2) NOT NULL default '0',

`UNIQUEID` int(11) NOT NULL auto_increment,

PRIMARY KEY (`TOURCODE`,`VERSIONDATE`,`BRAND`,`STATUS`,`SORTID`),

KEY `UNIQUEID` (`UNIQUEID`),

FULLTEXT KEY `DAYDESCRIPTION` (`DAYDESCRIPTION`,`DAYTITLE`)

) TYPE=MyISAM

[ssaamg]

Thanks for your reply royvarley.

Neither, colums nor where clause were used.

I use timestamp extensively as par of primary keys.

After syncronizing one table, some records were not copied. I looked at both tables and the only record that were not copied were of an specific date (the 31st of January).

To test I made two new records both with a 31st of Janury date, those again where not syncronized.

Then I changed one of the dates to the 30th and the other remained on the 31st. The result was that the record with the 30th was copied but the one with the 31st was not syncro.

I have been following the syncros day by day and the rule is not the 31st for some reason other records aren't syncro too

I always make sure to have the structures sycronized before doing the data syncro. The tables are in a same on the same hardware, same MySQL(4.2) version and same Red Hat version.

Thanks for the help

[Author]

Posts