0

we are using Attunity tool to insert data from mySQL Database into other mySQL Database and there is a problem with '0000-00-00 00:00:00' values in columns defined as a timestamp in source database.

Attunity task doesn`t return any error message, only run forever.

There is this sentence in a manual: 'If the DATETIME and TIMESTAMP data types are specified with a “zero” value (i.e. 0000-00-00), you need to make sure that the target database in the replication task supports "zero" values for the DATETIME and TIMESTAMP data types. If they are not supported, you can use a transformation to specify a supported value (e.g. 1970.) Otherwise, they will be recorded as null on the target.'

Neverthelessif I try to convert value using expression builder, by test expression function works, but by running the job same behavior, no error message just run doesnt finish and doesnt insert any values.

Tried following functions working in expression builder correctly: replace($column_name, '0000-00-00 00:00:00','1000-01-01 00:00:00') Expression builder supports SQLite functions.

CASE WHEN $column_name = '0000-00-00 00:00:00' THEN '1000-01-01 00:00:00' ELSE $column_name END

It seems attunity tool first fail to insert data, just then performs the operation, and its too late afterwards.

Converting data type within attunity to string doesn`t help neither.

I run out of ideas what else to try.

Could you help?

Thank you

bennes
  • 71
  • 1
  • 3
  • 10
  • Your database is MySql, why did you tag SQLite? – forpas Jul 16 '21 at 07:36
  • Hi, because expression builder within attunity which based on manual should be used for transformation uses sqlite functions. But fair enough, tagged also mySQL – bennes Jul 16 '21 at 07:47
  • Did you try to turn off the NO_ZERO_IN_DATE mode: https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html#sqlmode_no_zero_in_date in the target database? – forpas Jul 16 '21 at 08:08

0 Answers0