MySQL ODBC Destination error "default is invalid for column type TYPE_TIMESTAMP"

[Robert Bischoff]

When I'm trying to insert values into a MYSQL table using the Cozyroc ODBC Destination I'm getting the error 

[ODBC Destination [2]] Error: An error occurred with the following error message: "'0000-00-00 00:00:00' default is invalid for column type TYPE_TIMESTAMP".

The value I'm inserting is not '0000-00-00 00:00:00'. It is '2016-01-02 07:00:00'.

I can get around this by changing the MYSQL column data type from datetime NOT NULL to datetime NULL. It also works if I use the native SSIS ODBC destination, however, that is very slow.

I need to be able to insert into a not null column. Anyone have ideas?

[Ivan Peev]

Hi Robert,

The error below could happen if the default value you have setup in MySQL is incorrect. The default value will be used when you send NULL value as input and the column doesn't allow NULL values. That explains why you are not seeing this issue when you set the column to permit NULL values.

[Robert Bischoff]

Thanks, Ivan. But I'm not passing a null. I'm passing '2016-01-02 07:00:00'. When I set the column to allow null the value inserts correctly as '2016-01-02 07:00:00'. Since the same data is inserted correctly with the native SSIS ODBC destination, I can only assume this has something to do with the Cozyroc ODBC destination.

[Ivan Peev]

Robert,

There is no '0000-00-00 00:00:00' value setup anywhere in COZYROC's component. Did you check what is the default value setup in your MySQL table schema ?

[Robert Bischoff]

There is not default value. This is the column on the create script: `date_created` datetime NOT NULL,

[Ivan Peev]

Robert,

Setup a default value with correct timestamp specification. Specify for example for default: 0001-01-01 00:00:00

Did it work now ?

[ari...@gmail.com]

Ivan, where is it in MySQL we can set this default value? For a temporary solution, I have to set all my date columns to accept null, I don't feel right with this.

[Ivan Peev]

Hi,

You have to set up the default column value in your MySQL table configuration.

[ari...@gmail.com]

Oh yes, ok, I got it working as your recommendation, thanks.