Bug #71452 Error on legal MEMORY Engine table deletion in safe update mode

• Submitted: 22 Jan 2014 3:07
• Modified: 22 Jan 2014 20:36
• Reporter: Stephen Richards
• Status: Verified
• Category: MySQL Server: Storage Engines
• Severity: S2 (Serious)
• Version: 5.5.27/5.6
• OS: Windows (7)
• CPU Architecture: Any
• Tags: memory engine safe update

[22 Jan 2014 3:07] Stephen Richards

Description:
In safe update mode, doing the same deletion, the operation works on InnoDb table and fails on a MEMORY table.

How to repeat:
#create the table

CREATE TABLE  memorytest (
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `STRINGDATA` varchar(45) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDb AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;

#insert some data
insert into memorytest (id, stringdata) values (1, 'a'), (2, 'b');

#turn on safe update mode
set sql_safe_updates = 1;

#delete everything, using the index so you get no error
delete from memorytest where id>0;
#works fine, no error, 3 rows affected

#insert some data again
insert into memorytest (id, stringdata) values (1, 'a'), (2, 'b');

#change the engine to MEMORY
alter table memorytest engine=MEMORY;

#do the same deletion
delete from memorytest where id>0;

Error message received: You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column

[22 Jan 2014 3:08] Stephen Richards

"2 rows affected" not 3, in my example.  That is not the problem.  I did 3 the first time I tested this.

[22 Jan 2014 20:36] MySQL Verification Team

Thank you for the bug report.

Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.15 MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> USE TEST
Database changed
mysql> CREATE TABLE  memorytest (
    ->   `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
    ->   `STRINGDATA` varchar(45) NOT NULL,
    ->   PRIMARY KEY (`ID`)
    -> ) ENGINE=InnoDb AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.33 sec)

mysql> insert into memorytest (id, stringdata) values (1, 'a'), (2, 'b');
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> set sql_safe_updates = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from memorytest where id>0;
Query OK, 2 rows affected (0.03 sec)

mysql> insert into memorytest (id, stringdata) values (1, 'a'), (2, 'b');
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> alter table memorytest engine=MEMORY;
Query OK, 2 rows affected (0.31 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> delete from memorytest where id>0;
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column
mysql> SHOW CREATE TABLE memorytest\G
*************************** 1. row ***************************
       Table: memorytest
Create Table: CREATE TABLE `memorytest` (
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `STRINGDATA` varchar(45) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=MEMORY AUTO_INCREMENT=7 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)