[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

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

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

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

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

[Author]

Posts