I am replicating a MySQL database into MS SQL Server 2008, which has a native
date type (without the time portion). In my xml schema, the date fields are
defined as type="DATE", but in SQL Server, they are coming through as
datetime.

In version 2.5.13, while the types did not match, the data still replicated
and the times just were set to 00:00:00.

In version 3.0.9, I'm getting this error when sending a batch with a date
column:

java.lang.RuntimeException: java.lang.IllegalArgumentException: Timestamp
format must be yyyy-mm-dd hh:mm:ss

Is it possible to translate these date fields as date's for SQL Server 2008?

Thanks,

Ryan

Yeah, we can fix that. The scenario is datetime on mysql and data on
sqlserver. Replicate from mysql to sqlserver. Correct?

Great, thanks. I'm trying to replicate a date on MySQL to a date on SQL
Server. When I export the MySQL schema to xml, the fields are listed as
date's, but when I dbimport them into SQL Server, they come out as datetime's.
SQL Server 2008 and higher has a native date type, so I was hoping they could
come through as date's. Here is an example of the field in the xml file:

This same behavior occurs in both 2.5.13 and 3.0.9, but in 2.5.13, the data
still replicated and the times just were set to 00:00:00, but 3.0.9 gives the
above error. Is there a way that the fields could come through as date's when
I dbimport them into SQL Server 2008? Thanks.

This is fixed in 3.0.10 which will be release today or tomorrow.

http://www.symmetricds.org/issues/view.php?id=722

Yeah. If I get time, I'll see if I can that fixed as well.

http://www.symmetricds.org/issues/view.php?id=723

Thanks for the quick fixes. I tried out 3.0.10 today and the above problems
seem to be fixed, except now I'm getting errors on certain batches with errors
such as:

java.lang.RuntimeException: java.text.ParseException: Unable to parse the
date: 1001

In this case, the 1001 is data stored in an integer field in my table. I had
another error where the data was referring to a varchar field in my table. Do
you know why it's trying to parse them as dates?

No ideas. If you post a scenario where we can reproduce the issue, we'll take
a look.

Thanks - I know this doesn't completely explain it, but from my initial tests,
the issue resolves itself if I edit row_data slightly.

It's very intermittent and not for every row, but as an example, if my failed
row_data was:

"123","abc","2012-07-25 10:06:02","def"

and I add an extra space to the end of the string:

"123","abc ","2012-07-25 10:06:02","def"

if I then restart both server and client, the batch processes successfully.

On a side note, a full reload worked successfully but I'm guessing that worked
since individual rows aren't inserted into row_data.

I didn't run into this problem with 2.5.13 - just upgraded to 3.0.10 today. I
will continue to figure out how to best reproduce it.

Not sure if this will help, but the above fix only works if I change the
row_data before the date field.

For example, with the failed row_data as:

"123","abc","2012-07-25 10:06:02","def"

If I edit row_data to be:

"123","abc ","2012-07-25 10:06:02","def"

the batch works (added space after abc)

If I edit it instead to be:

"123","abc","2012-07-25 10:06:02","def "

the batch still fails (added space after def).

Here is a little more information - please let me know what other information
I can give you to help investigate it. Thanks again for your help.

I am replicating from MySQL 5.5 to SQL Server 2008 using 3.0.10. It definitely
is an intermittent problem and doesn't happen for all rows. I haven't narrowed
down the pattern yet. The issue did not occur on 2.5.13.

My table is defined as:

• field1 int
  
• field2 text
  
• field3 datetime
  
• field4 varchar
  

The row_data in sym_data is:

"123","abc","2012-07-25 10:06:02","def"

When the row_data is above, I get the error and backtrace pasted below.

If I instead change the "abc" to be "abc " with an extra space, like:

"123","abc ","2012-07-25 10:06:02","def"

and restart the server and client, then it works.

The error and backtrace on the first row_data is:

2012-07-25 18:37:00,522 ERROR Could not convert a value of abc for column
field3 of type TIMESTAMP

2012-07-25 18:37:00,523 ERROR java.text.ParseException: Unable to parse the
date: abc

java.lang.RuntimeException: java.text.ParseException: Unable to parse the
date: abc

at org.jumpmind.db.platform.AbstractDatabasePlatform.parseDate(AbstractDatabas
ePlatform.java:534)

at org.jumpmind.db.platform.AbstractDatabasePlatform.getObjectValues(AbstractD
atabasePlatform.java:330)

at org.jumpmind.db.platform.AbstractDatabasePlatform.getObjectValues(AbstractD
atabasePlatform.java:308)

at org.jumpmind.symmetric.io.data.writer.DatabaseWriter.execute(DatabaseWriter
.java:843)

at org.jumpmind.symmetric.io.data.writer.DatabaseWriter.update(DatabaseWriter.
java:620)

at org.jumpmind.symmetric.io.data.writer.DatabaseWriter.write(DatabaseWriter.j
ava:133)

at org.jumpmind.symmetric.io.data.writer.TransformWriter.write(TransformWriter
.java:181)

at org.jumpmind.symmetric.io.data.DataProcessor.forEachDataInTable(DataProcess
or.java:163)

at org.jumpmind.symmetric.io.data.DataProcessor.forEachTableInBatch(DataProces
sor.java:135)

at org.jumpmind.symmetric.io.data.DataProcessor.process(DataProcessor.java:86)

at org.jumpmind.symmetric.service.impl.DataLoaderService$LoadIntoDatabaseOnArr
ivalListener.end(DataLoaderService.java:651)

at org.jumpmind.symmetric.io.data.writer.StagingDataWriter.notifyEndBatch(Stag
ingDataWriter.java:43)

at org.jumpmind.symmetric.io.data.writer.AbstractProtocolDataWriter.end(Abstra
ctProtocolDataWriter.java:163)

at org.jumpmind.symmetric.io.data.DataProcessor.process(DataProcessor.java:96)

at org.jumpmind.symmetric.service.impl.DataLoaderService.loadDataFromTransport
(DataLoaderService.java:318)

at org.jumpmind.symmetric.service.impl.DataLoaderService.loadDataFromPull(Data
LoaderService.java:204)

at
org.jumpmind.symmetric.service.impl.PullService.execute(PullService.java:125)

at org.jumpmind.symmetric.service.impl.NodeCommunicationService$2.run(NodeComm
unicationService.java:231)

at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(Unknown Source)

at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)

at java.lang.Thread.run(Unknown Source)

Caused by: java.text.ParseException: Unable to parse the date: abc

at org.apache.commons.lang.time.DateUtils.parseDate(DateUtils.java:271)

at org.jumpmind.db.platform.AbstractDatabasePlatform.parseDate(AbstractDatabas
ePlatform.java:525)

... 20 more

Could there be a hidden special character causing issues that disappears when
you edit the data?

Are there differences in the way the table is defined in mysql vs sql server?

It might also be interesting if you set stream.to.file.threshold.bytes=0 and
take a look at the raw csv file generated.

Thanks for your help. The table is identical between MySQL and SQL Server, and
a full reload runs successfully. There are dates in my table which you fixed
for 3.0.10 to work with SQL Server 2008 - not sure if that is related since my
dates are coming through as "2012-07-01" rather than "2012-07-01 00:00:00". I
thought about a hidden character being removed, but I tried your suggestion of
setting stream.to.file.threshold.bytes=0, and when I look at the before and
after .ready files, they are 1 byte different with the extra space being the
only difference.

From what I can tell, it's not just an extra space that fixes it - removing
characters from row_data work too. I also tried a test where I didn't edit
row_data, but I instead deleted the .ready files and restarted the server and
client to force the .ready files to regenerate, but that causes the same
error. I need to edit row_data in some form to get the data to replicate. It
seems that the client isn't parsing the incoming csv file correctly and
looking for data in the wrong columns at times, but again, it's not on every
row, just some.

Do you have any other thoughts on what I can try to debug this? I switched my
main replication back to using version 2.5.13 last Wednesday and it's been
running perfect with no errors since. I'm only seeing this issue in version
3.0.10. Thanks.

Does this only happen with the one table? Do the triggers regenerate when you
move between one version and the other? If so, can you email or post the
trigger text for 2.5 and 3.0.10?

I finally figured out a reproduce script tonight - it seems to be related to
using text fields and updating multiple rows at a time.

Using 3.0.10 replicating MySQL 5.5 to SQL Server 2008:

1) On the MySQL side, run:

CREATE TABLE t1 (

field1 int(11) NOT NULL,

field2 text,

field3 datetime DEFAULT NULL,

PRIMARY KEY (field1)

) ENGINE=InnoDB;

2) Add table to sym_trigger and sym_trigger_router. Run sync triggers.

3) Run send-schema on the MySQL side to send the schema for table t1 to the
SQL Server.

4) Run the following SQL on the MySQL side:

insert into t1 values (1, null, '2001-01-01');

insert into t1 values (2, 'abc', '2001-01-01');

insert into t1 values (3, null, '2001-01-01');

This data replicates successfully to SQL Server.

5) Run the following SQL on the MySQL side:

update t1 set field3 = '2002-01-01';

This batch fails with an error:

• MsSqlDatabasePlatform - Could not convert a value of 2 for column field3 of type TIMESTAMP
  
• MsSqlDatabasePlatform - java.text.ParseException: Unable to parse the date: 2
  

Thanks. I could duplicate the issue. Will work on a fix.

The fix is in this snapshot. 3.1 will probably be released late this week or
early next week. Your help is very much appreciated!

http://snapshots.repository.codehaus.org/org/jumpmind/symmetric/symmetric-
assemble/3.1.0-SNAPSHOT/symmetric-assemble-3.1.0-20120730.121339-27-server.zip

Thanks for the fix - all seems great - ran all day today with no sync issues.