field type timestamp default '0000-00-00 00:00:00' parse to sql can't import to mysql server

[zxb4221]

field type timestamp default '0000-00-00 00:00:00', when field value is '0000-00-00 00:00:00', python-mysql-replication parse the field value to '1970-01-01 08:00:00' which can't import to mysql server with a error: 1292, "Incorrect datetime value: '1970-01-01 08:00:00' for column 'promotion_begintime' at row 1"

source code:

elif column.type == FIELD_TYPE.TIMESTAMP2: values[name] = self.__add_fsp_to_time( datetime.datetime.fromtimestamp( self.packet.read_int_be_by_size(4)), column)

datetime.datetime.fromtimestamp(0) will return '1970-01-01 08:00:00'

[rspadim]

0000-00-00 is an invalid date

change the destination server with proper sql_mode variable, or change it to 0000-01-01 (month 0 and day 0 does not exists)

[telemmaite]

0000-00-00 dates exist for a convenience reason in MySQL and indeed there are modes to forbid them but also there are connectors which handle/translate them to NULL, why not do the same here?

The special “zero” date '0000-00-00' can be stored and retrieved as '0000-00-00'. When a '0000-00-00' date is used through Connector/ODBC, it is automatically converted to NULL because ODBC cannot handle that kind of date.

Month 0 and day 0 do exist and the doc provides example for them.

MySQL permits you to store dates where the day or month and day are zero. This is convenient if you want to store a birthdate in a DATE column and you know only part of the date. To disallow zero month or day parts in dates, enable the NO_ZERO_IN_DATE mode.

Yes, there is an option to force legal dates:

To have MySQL check all dates and accept only legal dates (unless overridden by IGNORE), set the sql_mode system variable to "NO_ZERO_IN_DATE,NO_ZERO_DATE".

Though..how feasible is that on 10 year old production setup just because 0000 dates are not handled here?

[rspadim]

https://dev.mysql.com/doc/refman/8.0/en/datetime.html

Invalid DATE, DATETIME, or TIMESTAMP values are converted to the “zero” value of the appropriate type ('0000-00-00' or '0000-00-00 00:00:00'), if the SQL mode permits this conversion. The precise behavior depends on which if any of strict SQL mode and the NO_ZERO_DATE SQL mode are enabled; see Section 5.1.11, “Server SQL Modes”.

• server requires that month and day values be valid, and not merely in the range 1 to 12 and 1 to 31, respectively. With strict mode disabled, invalid dates such as '2004-04-31' are converted to '0000-00-00' and a warning is generated. With strict mode enabled, invalid dates generate an error. To permit such dates, enable ALLOW_INVALID_DATES. See Section 5.1.11, “Server SQL Modes”, for more information.

[rspadim]

datetime.datetime.fromtimestamp(0) will return '1970-01-01 08:00:00'

This “from timestamp” isn’t considering the server timezone or maybe the field timezone

[rspadim]

https://github.com/noplay/python-mysql-replication/blob/511b42c8ac2c1682a6e2fd4d6691658245b57987/pymysqlreplication/row_event.py#L279