Interrupting a query inside InnoDB causes an unrelated warning to be raised

Bug #1115158 reported by Vlad Lesin
12
This bug affects 2 people
Affects Status Importance Assigned to Milestone
MySQL Server
Unknown
Unknown
Percona Server moved to https://jira.percona.com/projects/PS
Fix Released
Low
Vlad Lesin
5.1
Won't Fix
Undecided
Unassigned
5.5
Won't Fix
Low
Unassigned
5.6
Fix Released
Low
Vlad Lesin

Bug Description

Upstream bug 64556 (the description can be seen here: http://bugs.mysql.com/bug.php?id=64556 ).

Related branches

Vlad Lesin (vlad-lesin)
Changed in percona-server:
assignee: nobody → Vlad Lesin (vlad-lesin)
Revision history for this message
monty solomon (monty+launchpad) wrote :

The problem also occurs with queries that fail.

I ran an ALTER TABLE statement that failed due to a zero datetime column (sql_mode contains NO_ZERO_DATE,NO_ZERO_IN_DATE)

ALTER TABLE `Test`
 DROP PRIMARY KEY,
 ADD PRIMARY KEY (`id`, `date_time`),
 ENGINE=InnoDB ROW_FORMAT=DYNAMIC
 PARTITION BY RANGE COLUMNS(date_time) (
  PARTITION p201206 VALUES LESS THAN ('2012-07-01 00:00:00'),
  PARTITION p201412 VALUES LESS THAN ('2015-01-01 00:00:00'),
  PARTITION p2015 VALUES LESS THAN ('2016-01-01 00:00:00'),
  PARTITION p9999 VALUES LESS THAN MAXVALUE
);

ERROR 1292 (22007): Incorrect datetime value: '0000-00-00 00:00:00' for column 'date_time' at row 1

mysql> show warnings;
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------+
| Error | 1292 | Incorrect datetime value: '0000-00-00 00:00:00' for column 'date_time' at row 1 |
| Error | 1317 | Query execution was interrupted |
| Error | 152 | InnoDB: Cannot delete/update rows with cascading foreign key constraints that exceed max depth of 20. Please drop extra constraints and try again |
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Error
   Code: 1292
Message: Incorrect datetime value: '0000-00-00 00:00:00' for column 'date_time' at row 1
*************************** 2. row ***************************
  Level: Error
   Code: 1317
Message: Query execution was interrupted
*************************** 3. row ***************************
  Level: Error
   Code: 152
Message: InnoDB: Cannot delete/update rows with cascading foreign key constraints that exceed max depth of 20. Please drop extra constraints and try again
3 rows in set (0.00 sec)

mysql> select * from Test\G
*************************** 1. row ***************************
       id: 336823180
date_time: 0000-00-00 00:00:00
1 row in set (0.00 sec)

mysql> show create table Test\G
*************************** 1. row ***************************
       Table: Test
Create Table: CREATE TABLE `Test` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `date_time` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=336823181 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

Server version: 5.5.32-31.0-log Percona Server (GPL), Release rel31.0, Revision 549

Revision history for this message
Shahriyar Rzayev (rzayev-sehriyar) wrote :

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-1967

To post a comment you must log in.
This report contains Public information  
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.