Error 1064 When Syncing With Sja

[bernie]

Hi,

I am using SJA to synchornize the changes from on server onto another. Both are running MySQL 5.018 and I have tried SJA 5.02 on Windows, SJA 5.0 on Linux, and SJA 5.1 BETA 6 on Windows. I have about 15 tables, and most of them work without a problem. However, a few of them generate this error:

There was an error while execute a query. The query and the error has been logged in the log file.

And the log file shows this:

Error No. 1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'group by 1 order by 2' at line 1

Are there limitations to SJA that might be appearing here? What could be generating this error?

Thanks,

-bernie

[peterlaursen]

Yeah .. and sometimes my car won't start … could you please tell me the reason? I am using Texaco gasoline 😀 .. Don't you think that you would need a little bit of additional information to help me here?

And so with your problem! Maybe Ritesh could use this “'group by 1 order by 2'” (but I doubt, because the error in the internal SQL is probably just before that) .. but we simply need a description of the DATA. From what sort of system do the data come? Are there any 'weird' binary data or encoded data?

Could you post 'Create staement for the tables' and preferably a small set of data from source and target to reproduce the error. And the job(XML)file. If it is data you don't want to have exposed here, then create a ticket in the webbased support system.

As you are able to use SJA for Linux, I must suppose that you use direct connection,

There is of course no limitation with 16 tables or the like!

The only thing we can tell from the information that you give is that SJA internally in your situation (with your data and server setup's) generates some SQL that is invalid in the situation.

[Ritesh]

We had fixed couple of bugs similar to this in one of the earlier versions of SJA. I will require the CREATE TABLE statement and couple of rows of data to find the issue.

[bernie]

Thanks for the response. Here is the table information:

Field Type Collation Null Key Default Extra Privileges Comment

---

---

---

---

---

---

---

---

---

submittime datetime (NULL) NO PRI 0000-00-00 00:00:00 select,insert,update

clientname varchar(20) latin1_swedish_ci NO PRI select,insert,update

jobname varchar(40) latin1_swedish_ci NO PRI select,insert,update

notetime datetime (NULL) NO PRI 0000-00-00 00:00:00 select,insert,update

username varchar(20) latin1_swedish_ci NO select,insert,update

note varchar(200) latin1_swedish_ci NO select,insert,update

modtime datetime (NULL) NO 0000-00-00 00:00:00 select,insert,update

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

---

---

---

---

---

---

---

---

---

---

---

---

jobnoteslog 0 PRIMARY 1 submittime A 2 (NULL) (NULL) BTREE

jobnoteslog 0 PRIMARY 2 clientname A 2 (NULL) (NULL) BTREE

jobnoteslog 0 PRIMARY 3 jobname A 2 (NULL) (NULL) BTREE

jobnoteslog 0 PRIMARY 4 notetime A 2 (NULL) (NULL) BTREE

Table Create Table

---

---

jobnoteslog CREATE TABLE `jobnoteslog` (

`submittime` datetime NOT NULL default '0000-00-00 00:00:00',

`clientname` varchar(20) NOT NULL,

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

`notetime` datetime NOT NULL default '0000-00-00 00:00:00',

`username` varchar(20) NOT NULL default '',

`note` varchar(200) NOT NULL default '',

`modtime` datetime NOT NULL default '0000-00-00 00:00:00',

PRIMARY KEY (`submittime`,`clientname`,`jobname`,`notetime`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

An example of a few rows from the source and the target that generate this error looks like this:

Source:

insert into `jobnoteslog` (`submittime`,`clientname`,`jobname`,`notetime`,`username`,`note`,`modtime`) values ('2006-04-05 16:21:52','company a','job 1','2006-04-05 17:13:24','bob','note','2006-04-05 17:13:24');

insert into `jobnoteslog` (`submittime`,`clientname`,`jobname`,`notetime`,`username`,`note`,`modtime`) values ('2006-04-09 23:00:04','company i','job 1','2006-04-10 17:43:06','bob','note','2006-04-10 17:43:06');

insert into `jobnoteslog` (`submittime`,`clientname`,`jobname`,`notetime`,`username`,`note`,`modtime`) values ('2006-04-10 08:00:04','company n','job 1','2006-04-10 17:40:06','bob','note','2006-04-10 17:40:06');

insert into `jobnoteslog` (`submittime`,`clientname`,`jobname`,`notetime`,`username`,`note`,`modtime`) values ('2006-04-10 10:30:13','company p','job 1','2006-04-10 17:41:50','bob','note','2006-04-10 17:41:50');

insert into `jobnoteslog` (`submittime`,`clientname`,`jobname`,`notetime`,`username`,`note`,`modtime`) values ('2006-04-10 14:23:48','test case','job 1','2006-04-10 17:39:59','bob','note','2006-04-10 17:39:59');

insert into `jobnoteslog` (`submittime`,`clientname`,`jobname`,`notetime`,`username`,`note`,`modtime`) values ('2006-04-10 15:00:03','test case','job 2','2006-04-10 17:39:05','bob','note','2006-04-10 17:39:05');

insert into `jobnoteslog` (`submittime`,`clientname`,`jobname`,`notetime`,`username`,`note`,`modtime`) values ('2006-04-10 15:00:03','test cast','job 2','2006-04-10 17:39:48','bob','note','2006-04-10 17:39:48');

insert into `jobnoteslog` (`submittime`,`clientname`,`jobname`,`notetime`,`username`,`note`,`modtime`) values ('2006-04-10 17:29:41','company s','job 3','2006-04-10 17:44:13','bob','note','2006-04-10 17:44:13');

insert into `jobnoteslog` (`submittime`,`clientname`,`jobname`,`notetime`,`username`,`note`,`modtime`) values ('2006-04-10 17:29:41','company s','job 3','2006-04-10 17:44:18','bob','note','2006-04-10 17:44:18');

insert into `jobnoteslog` (`submittime`,`clientname`,`jobname`,`notetime`,`username`,`note`,`modtime`) values ('2006-04-10 17:29:41','company s','job 3','2006-04-10 17:44:23','bob','note','2006-04-10 17:44:23');

insert into `jobnoteslog` (`submittime`,`clientname`,`jobname`,`notetime`,`username`,`note`,`modtime`) values ('2006-04-10 17:29:41','company s','job 3','2006-04-10 17:50:50','bob','note','2006-04-10 17:50:50');

Target:

insert into `jobnoteslog` (`submittime`,`clientname`,`jobname`,`notetime`,`username`,`note`,`modtime`) values ('2006-04-05 16:21:52','company a','job 1','2006-04-05 17:13:24','bob','note','2006-04-05 17:13:24');

insert into `jobnoteslog` (`submittime`,`clientname`,`jobname`,`notetime`,`username`,`note`,`modtime`) values ('2006-04-09 23:00:04','company i','job 1','2006-04-10 17:43:06','bob','note','2006-04-10 17:43:06');

insert into `jobnoteslog` (`submittime`,`clientname`,`jobname`,`notetime`,`username`,`note`,`modtime`) values ('2006-04-10 08:00:04','company n','job 1','2006-04-10 17:40:06','bob','note','2006-04-10 17:40:06');

insert into `jobnoteslog` (`submittime`,`clientname`,`jobname`,`notetime`,`username`,`note`,`modtime`) values ('2006-04-10 10:30:13','company p','job 1','2006-04-10 17:41:50','bob','note','2006-04-10 17:41:50');

insert into `jobnoteslog` (`submittime`,`clientname`,`jobname`,`notetime`,`username`,`note`,`modtime`) values ('2006-04-10 14:23:48','test case','job 1','2006-04-10 17:39:59','bob','note','2006-04-10 17:39:59');

insert into `jobnoteslog` (`submittime`,`clientname`,`jobname`,`notetime`,`username`,`note`,`modtime`) values ('2006-04-10 15:00:03','test case','job 2','2006-04-10 17:39:05','bob','note','2006-04-10 17:39:05');

Thanks for looking into this!

[peterlaursen]

This is confirmed. Also with SQLyog 5.1 beta 6.

You have a PK build on 4 columns of which 2 are DATETIMES. If I create a new PK with only the two other columns there is no error when syncing.

I also note that there is only seconds between the records. If I change them so that there are months or more between them, there is no error. We fixed a similar issue with TIMESTAMPs and DATETIMES in the PK recently.

@Ritesh: has this fix accidently been deactivated?

[bernie]

Ritesh, any update with this?

I have one other table that has similar characteristics, with one datetime in the PK that has times only seconds apart. However, I also have another table that doesn't have a datetime in the PK (though it still has a datetime in the table) that also generates this same error. Is there a version with the fix you described that I can try out?

[Ritesh]

I will work on the DATETIME issue today. In the meantime, if you can send the other table structure and sample data to [email protected]. We plan to release RC by today evening and it will have the big fixed.

[Ritesh]

This is happening due to an issue with MySQL:

http://bugs.mysql.com/bug.php?id=19098

As soon as they reply and I know what is the actual reason, we will fix it up.

[bernie]

Thanks for looking into this, Ritesh.

I went back to look at my tables, and realized that there are actually only two tables with this problem (the third was a duplicate of one of them). It all seems to be related to the same bug, since they have datetimes in the PK with very close times. I was mistaken in thinking there was another table with different characteristics that also had problems.

[Ritesh]

As you can see at http://bugs.mysql.com/bug.php?id=19098, this has been confirmed as a bug by MySQL. Is it possible to use some other datatype as the primary key for the table?

[peterlaursen]

FAQ write-up:

http://www.webyog.com/faq/35_104_en.html

[bernie]

Unfortunately, we need to use some sort of date/time value in the PK to differentiate the rows. The database has already been laid out and has been in use for some time, so I also don't want to change whats already being used.

Is there no way to work around this issue? Like truncating the extra .000000 that gets returned?

[peterlaursen]

Is it possible to use/convert to a TIMESTAMP and not a DATETIME? I understood this bug does not affect TIMESTAMPs. Actually a bug in SQLyog affecting TIMESTAMP-based PK's were fixed early in the 5.1 BETA tree and 5.1 works fine with recent MySQL 4.1 5.0 and 5.1.

I think that we cannot mess around with the code, before we have some indication from MySQL what they intend to do for the future. Is this a plain bug? Or is there some reason that 'SELECT datatime_variable+0' must return a FLOAT and not a BIGINT?

But TRUNCATING to get rid of the Zero's is no big deal I think. But we need to be sure that that does not raise new problems!

[bernie]

With the same dataset, I changed the DATETIME values to TIMESTAMP just to see if it would work (I'm not sure if there would be any other side-effects so I'm not sure we could change it over in our production database).

I ended up getting the same error “There was an error while execute a query. The query and the error has been logged in the log file.” The tool also deleted a row out of the target dataset the first time I ran it.

The linux version, which I think is an older version, didnt return any error, but also didnt perform any actions. It gave this output:

Table SrcRows TgtRows Inserted Updated Deleted

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

`jobnoteslog` 11 5 0 0 0

Ritesh, can you confirm this on your side with the same data I posted earlier?

-bernie

[Ritesh]

I have asked my developer to work on it. If its a bug, will be fixed in the next release of SQLyog.

[peterlaursen]

MySQL docs now say:

http://dev.mysql.com/doc/refman/5.0/en/upg…g-from-4-1.html

Incompatible change: Before MySQL 4.1.13/5.0.8, conversion of DATETIME values to numeric form by adding zero produced a result in YYYYMMDDHHMMSS format. The result of DATETIME+0 is now in YYYYMMDDHHMMSS.000000 format.

So I think SJA must do TRUNCATE(datetime + 0) to fit all versions 😮

[Ritesh]

Yup.

It will require change for SJA. It will effect the people who use DATETIME as part of PK.

I have put this change as top priority for v5.2.

select TRUNCATE(datetime_column + 0, 0) does the trick!

[peterlaursen]

Fixed in 5.16 beta12

[Author]

Posts