Percona Server with XtraDB

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

Reported by Vlad Lesin on 2013-02-04
12
This bug affects 2 people
Affects Status Importance Assigned to Milestone
MySQL Server
Unknown
Unknown
Percona Server
Status tracked in 5.6
5.1
Undecided
Unassigned
5.5
Low
Unassigned
5.6
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) on 2013-02-04
Changed in percona-server:
assignee: nobody → Vlad Lesin (vlad-lesin)
monty (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

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

Other bug subscribers

Remote bug watches

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