Tables not syncronizing

[ssaamg]

Hi,

I have been using the Syncronizing tool for a few weeks. It usually works fine but some times the new records are not updated.

Table SrcRows TgtRows Inserted Updated Deleted

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

`itinerary_daysinfo` 6467 6178 0 0 0

Total time taken – 58 sec(s)

As you can see the syncro tool does recognize that we have different rows but they are not inserted.

Any idea?

Thanks

---

[royvarley]

Do you specify any columns or use a SQL Where clause?

[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

[Ritesh]

Which version of SJA are you using? Are you using Linux version or Windows version?

Can you send me the table structure and some sample datas to reproduce the error?

[peterlaursen]

Are you sure that the mysql version is 4.2 ??

Never heard about anything higher then 4.1.9 in en 4.x build …..

Here is a quote from the mysql manual

Quote:

ou can specify DATETIME, DATE, and TIMESTAMP values using any of a common set of formats:

As a string in either 'YYYY-MM-DD HH:MM:SS' or 'YY-MM-DD HH:MM:SS' format. A “relaxed'' syntax is allowed: Any punctuation character may be used as the delimiter between date parts or time parts. For example, '98-12-31 11:30:45', '98.12.31 11+30+45', '98/12/31 11*30*45', and '98@12@31 11^30^45' are equivalent.

As a string in either 'YYYY-MM-DD' or 'YY-MM-DD' format. A “relaxed'' syntax is allowed here, too. For example, '98-12-31', '98.12.31', '98/12/31', and '98@12@31' are equivalent.

As a string with no delimiters in either 'YYYYMMDDHHMMSS' or 'YYMMDDHHMMSS' format, provided that the string makes sense as a date. For example, '19970523091528' and '970523091528' are interpreted as '1997-05-23 09:15:28', but '971122129015' is illegal (it has a nonsensical minute part) and becomes '0000-00-00 00:00:00'.

As a string with no delimiters in either 'YYYYMMDD' or 'YYMMDD' format, provided that the string makes sense as a date. For example, '19970523' and '970523' are interpreted as '1997-05-23', but '971332' is illegal (it has nonsensical month and day parts) and becomes '0000-00-00'.

As a number in either YYYYMMDDHHMMSS or YYMMDDHHMMSS format, provided that the number makes sense as a date. For example, 19830905132800 and 830905132800 are interpreted as '1983-09-05 13:28:00'.

As a number in either YYYYMMDD or YYMMDD format, provided that the number makes sense as a date. For example, 19830905 and 830905 are interpreted as '1983-09-05'.

As the result of a function that returns a value that is acceptable in a DATETIME, DATE, or TIMESTAMP context, such as NOW() or CURRENT_DATE.

Which timestamp format do you use: text or number ?

You are sure that the timestamp isn't identical for those records that don't copy ?

(if the chosen timestamp format is too short!)

Further the timestamp format has changed from different wysql-versions. So using timestamp as a PK if not the mysql versions are totalaly identical is not wise.

Also note that in mysql the “resolution” of the timestamp is only seconds and not milliseconds ore smaller “timeslices”.

So on a big corporate database it's likely that there are records having identical timestamps. Since it takes almost a minute to syncronize in your case it must be a big database ….

[royvarley]

Hmmm… looks like a job for the folks at webyog!

However, even though you say the mysql versions are the same, I agree with Peter's comment about the wisdom of using TIMESTAMPS as part of the pk. I'd re-visit that part of the design if possible. Maybe use a simple long integer as the pk and then construct alternate keys using either TIMESTAMP or simple strings based on date/time down to the resolution you need.

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

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

[peterlaursen]

Quote:

but these is a multiple Primary key

That's in my opinion no good.

“multiple primary key” – what's that ?

I've seen that before but I believe it's a very unhealthy construction

(Actually my own photography database uses the same construction. The program that I use is ThumbpsPlus http://www.cerious.com and the program unfortunately won't work if I change it)

You kan create “unique indexes” like that, but a primary key must be one field.

Actually I think it's a minor flaw in MySQL that it allows for it.

What you get is not a “multiple primary key” but multiple “primary key”s – and that's makes no sence in my opinion

– only ONE key can be “primary” just like there can only be ONE winner in a game!

Correct me if I'm wrong!

[royvarley]

Well, strictly speaking, a key can be one, or a combination of more than one, field in a table. A primary key just needs to be unique and it's valid to have more than one field to achieve that.

In my experience, I just find life a lot easier if a primary key is a generated unique number. Either using an autonumber field or having a special routine to generate a unique number. You can then create indexes on other fields where it makes sense for common queries into the data. Doing this makes using a pk as a foreign key easy. In your case, for example, for a record in another table to refer uniquely to a record in the example table you've given us, requires holding (and managing in the software) four fields.

Then again, I don't know your application or the design trauma you've had to go through to get to where you are at. If it makes sense for you, don't let me sway you into changing things for the sake of it. And that's not why you're here asking for help, is it! 🙄

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

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.

[Ritesh]

Thanks for the attachment.

I will run the sync and let you know the result tomorrow.

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

[Ritesh]

We are proud to announce the release of SQLyog v4.03 BETA 1.

This version has the following new features:

– Proxy server support for HTTP Tunneling.

– Credential authentication support for HTTP Tunneling.

This release also fixes the bug Data Sync bug as reported above. Now you dont need to change your datatype.

Please download v4.03 BETA 1 from the following links:

Enterprise Edition: http://www.webyog.com/sqlyog/SQLyog403Ent.exe

FREE Edition: http://www.webyog.com/sqlyog/SQLyog403.exe

[dschenk]

I got this far and downloaded the beta. The old version and the beta give me the same result. Changes made to my target database locally are consistently overridden by the values in the source database, even though I am making those edits days after the source entries. New records added to the target database will be uploaded to the source, but target edits are not. My source is running on SME linux and is Version 12.22 Distribution 4.0.23. My source is on win2k and is mysql 4.1.8 NT. Is the source difference significant, or should I be looking for some other problem?

[peterlaursen]

pardon me but

Quote:

Changes made to my target database locally are consistently overridden by the values in the source database

Yes that's the way it works – and should work!

In the syncronization proces data from the source will overwrite data in the target if the values of the PK are identical.

to me it sounds like you are turning source/target around … if you have made changes to one database and want to syncronize the one with the newly made changes is sorce in the proces …

did I get something totally wrong ??

[dschenk]

What is PK? I think if a sync is what I understand that it is. target and source terms are a bit arbitrary here. I want changes to be made independently on my linux server copy and on my roaming laptop copy and then to be able to sync and save the most recent changes/entries in each and have that new merged version be replicated on both the server and the laptop. That's what I hope will happen. In fact, I always get the server copy, even when doing a two-way sync. What am I missing?

[peterlaursen]

What you miss is to understand what a PK (primary Key) is, I'm afraid !

The PK is THE most important index to a database. SQLyog depends totally on it not only when syncronizing, but also when updating, deleting etc. Without the PK the records in the base can't be identified by sqlyog and if the PK in two records is identical in two databases with the same structure the syncronization tool overwrites the target row with data from the source. That's not an error – that's how it is meant to work. Because: If two rows has the same value of the PK there must be a rule for the software to decide which record/data are “the right one”. And the rule that sqlyog uses is that in that situation the “sorce” data are the “right one”s. So source and target are not arbitrary names but are names constituating a rule for the program to work!

A very simpel way to create a PK is to use an autoincremented integer as PK, but if you add records/rows (as you do) to both copies of the base between syncronizations you will risk to have different records/rows with the same value of the PK. In that case you will have to build a primary key thats has different values depending on which machine it has been made. Example: create a table more with only one row in it in the DB, put one text feld in it: on the laptop value could be “laptop” – on the server value could be “server”. Create a PK using that text field AND a value that's unique on each machine.

Note too that there is NO sort of time-control in the sqlyog syncronization tool.

[dschenk]

Okay, thank you. I do appreciate the advice. I do in fact have an autoincrement primary key, and I think I understand what you are saying about the trouble I may run into with that just being the number by itself and so coupling it with a fixed value in either database. Great for adding new records, but please go back to what I said I want to be able to do. As I understand your answer there is no way I will be able to alter only particular fields in existing records of each of the versions of the databases and to have the changes preserved. Is that correct? I hope I am misunderstanding that since the whole thing will be of little value to me otherwise.

[peterlaursen]

You can also create a column in each table with those values. (and to save space you can use single characters or “0” and “1” or whatever … ). Then you set the default value “laptop” for that column on your laptop, and “server” on the server. Further add an autoincrement integer in each table. Let the PK consist of of both.

Now when syncronizing PK values

Quote:

' 17654; “laptop” '

AND

Quote:

' 17654; “server” '

are NOT identical and no new data will be overwriten in syncronization.

With this solution you won't need to write code to fetch data from another tabel. I think that was the workaround that i would try in your situation ….

You are not the first person in this forum with this request/expectation/wish/need ….

But basically I think it's not a Sqlyog issue – it's a Mysql issue.

To me it seems like you expect MySQL to be a distributed database system, and it is not!

[peterlaursen]

I think that if you work on both databases at the same time, you will need to write code to solve the problem …

But I am far from an expert in this, so maybe someone else comes up with some “smart” solution …

[Ritesh]

SQLyog will always overwrite the data from source to target. There is no mechanism to check which data was updated last.

You need to select the correct source and target depending upon your requirement.

[Anonymous]

for dschenk

I think exists solution of your problem. You will need keep new sql queries on both servers. For the tables on the server database you will need create own id keys with odd numbers. For the tables on the local database you will need create own id keys with even numbers. Also you don't should use autoincrement function for this id's. Then you can write own scripts which will be sync data between both servers. I think it'll be working without any problems.

[Author]

Posts