Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

i am getting below error:

Last_SQL_Errno: 1292

    Last_SQL_Error: Error 'Incorrect datetime value: '2013-03-10 02:00:00' for column 'last_login' at row 1' on query. Default database: 'platform'. Query: 'INSERT INTO app_player (creation_date, last_login, removed_date, app_id, user_id) VALUES ('2013-03-10 02:00:00', '2013-03-10 02:00:00', null, 400, 77870956)'
1 row in set (0.00 sec)

Table structure:

mysql> show create table app_player\G
*************************** 1. row ***************************
       Table: app_player
Create Table: CREATE TABLE `app_player` (
  `app_id` int(10) unsigned NOT NULL,
  `user_id` bigint(20) unsigned NOT NULL,
  `creation_date` datetime NOT NULL,
  `removed_date` datetime DEFAULT NULL,
  `last_login` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`app_id`,`user_id`),
  KEY `idx_creation_date_app_id` (`creation_date`,`app_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql veriosn is : 5.1.52

mysql> show global variables like '%date%';
+-----------------------------------------+-------------------+
| Variable_name                           | Value             |
+-----------------------------------------+-------------------+
| binlog_direct_non_transactional_updates | OFF               |
| date_format                             | %Y-%m-%d          |
| datetime_format                         | %Y-%m-%d %H:%i:%s |
| innodb_stats_auto_update                | 1                 |
| innodb_stats_update_need_lock           | 1                 |
| log_slave_updates                       | OFF               |
| low_priority_updates                    | OFF               |
| query_cache_wlock_invalidate            | OFF               |
| sql_log_update                          | ON                |
| sql_low_priority_updates                | OFF               |
| sql_safe_updates                        | OFF               |
+-----------------------------------------+-------------------+
share|improve this question
5  
That's the start if DST in some timezones (US EDT for instance) - that date/time doesn't really exist there. – Mat Mar 10 '13 at 7:48

Description: The sql_mode changes behaviour from 5.6 to 5.7. I notice a create table statement that replicates fine on a 5.6 master breaks on the 5.7 downstream slave.

Last_SQL_Error: Error 'Invalid default value for 'last_change'' on query. Default database: 'db1'. Query: 'CREATE TABLE mytable ( id int(10) unsigned NOT NULL, field2 tinyint(3) unsigned NOT NULL, last_change timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', ....

sql_mode on the 5.6 master is: sql_mode = NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES sql_mode on the 5.7 slave is: sql_mode = NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES (both the config in /etc/my.cnf)

Given the sql_mode change the value and this is being replicated the 5.7 stricter behaviour is being applied on the 5.7 slave by the SQL thread when in fact the 5.6 behaviour should be applied.

How to repeat: see above.

Suggested fix: Make the SQL thread aware of the master version being 5.6 and treat the sql_mode as if it were running on a 5.6 server to maintain the same behaviour.

https://bugs.mysql.com/bug.php?id=75439

share|improve this answer
3  
The MySQL version given by the OP is 5.1 not 5.7 or 5.6. – Erik Oct 28 '15 at 15:24

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.