MySQL upgrade issues with SQL statements

The following errors encountered duting the validation of new MySQL versions including MySQL 5.5 and 5.6.

#1) As of MySQL5.1 there is no problem with "TRUNCATE TABLE" statement while truncating a parent table if its child table doesn't have any matching rows or if the child table is created using constraints with cascade delete or cascade update.

Since MySQL 5.5 "TRUNCATE TABLE" statement results with the following error while truncating a table, even though its child table doesn't have any matching rows or if the child table is created using constraints with cascade delete or cascade update.

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`)).


Although TRUNCATE TABLE is similar to DELETE, it is classified as a DDL statement rather than a DML statement. It differs from DELETE in the following ways since MySQL 5.5 onwards :

Truncate operations drop and re-create the table, which is much faster than deleting rows one by one.

TRUNCATE TABLE does not cause ON DELETE triggers to fire, and it cannot be performed for InnoDB tables with parent-child foreign key relationships.

Any AUTO_INCREMENT value is reset to its start value. This is true even for MyISAM and InnoDB, which normally do not reuse sequence values.

SUMMARY : Please make sure that you are not using anywhere TRUNCATE TABLE as part of your code, and going forward use DELETE statement instead of TRUNCATE TABLE.

For more information please go through http://dev.mysql.com/doc/refman/5.6/en/truncate-table.html



#2) MySQL 5.1 is not strict to the data types and the associated values.

The following statement works fine in MySQL 5.1.

insert into testdatatype2 select * from testdatatype where status1=1;

The same statement results in the following error in MySQL 5.5 and 5.6.
ERROR 1292 (22007): Truncated incorrect DOUBLE value: 'OFF'

Before start our discussion first lets go through tables structures used.

mysql> show create table testdatatype\G
*************************** 1. row ***************************
       Table: testdatatype
Create Table: CREATE TABLE `testdatatype` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `status1` varchar(5) DEFAULT NULL,
  `status2` int(5) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> show create table testdatatype2\G
*************************** 1. row ***************************
       Table: testdatatype2
Create Table: CREATE TABLE `testdatatype2` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `status1` varchar(5) DEFAULT NULL,
  `status2` int(5) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> select * from testdatatype;
+------+-----------+---------+---------+
| id   | name      | status1 | status2 |
+------+-----------+---------+---------+
|    1 | trimurthy | OFF     |       2 |
|    2 | anandh    | 1       |       2 |
+------+-----------+---------+---------+
2 rows in set (0.00 sec)

mysql> select * from testdatatype2;
Empty set (0.00 sec)

Both tables structure is same, only table names are different, if we observe the table structure status1 column has been defined as varchar(5).
And the table is having both string and number data in status1 column.

In MySQL 5.1 version the following statement worked with out any errors and warnings.

mysql> insert into testdatatype2 select * from testdatatype where status1=1;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from testdatatype2;
+------+-----------+---------+---------+
| id   | name      | status1 | status2 |
+------+-----------+---------+---------+
|    1 | trimurthy | OFF     |       2 |
|    2 | anandh    | 1       |       2 |
+------+-----------+---------+---------+
2 rows in set (0.00 sec)

But when it comes to MySQL 5.5 and 5.6 the same insert statement results in the following error.

mysql> insert into testdatatype2 select * from testdatatype where status1=1;
ERROR 1292 (22007): Truncated incorrect DOUBLE value: 'OFF'

This is because since MySQL 5.5 onwards server is strict to the data types.

To resolve the above mentioned error use quotations in the where clause as follows, which is a SQL standard.

insert into testdatatype2 select * from testdatatype where status1='1';


#3 As of MySQL 5.1 and 5.5 there is no problem with index creation on large varchar data type columns, incase of 5.1 and 5.5 it will create index only on first 255 characters of a given column and it will result in warning stating that "Specified key was too long; max key length is 767 bytes", when it comes to MySQL 5.6 it will result with an error "ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes".

The following is an example :

MySQL 5.1 :

mysql> create table compatibility_check ( id int, name varchar(50), description varchar(500), key key_desc (description));

Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> show warnings;

+---------+------+---------------------------------------------------------+
| Level   | Code | Message                                                 |
+---------+------+---------------------------------------------------------+
| Warning | 1071 | Specified key was too long; max key length is 767 bytes |
+---------+------+---------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show create table compatibility_check;

CREATE TABLE `compatibility_check` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(50) DEFAULT NULL,
  `description` varchar(500) DEFAULT NULL,
  KEY `key_desc` (`description`(255))
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


MySQL 5.5 :

mysql> create table compatibility_check ( id int, name varchar(50), description varchar(500), key key_desc (description));

Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql> show warnings;

+---------+------+---------------------------------------------------------+
| Level   | Code | Message                                                 |
+---------+------+---------------------------------------------------------+
| Warning | 1071 | Specified key was too long; max key length is 767 bytes |
+---------+------+---------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show create table compatibility_check;

CREATE TABLE `compatibility_check` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(50) DEFAULT NULL,
  `description` varchar(500) DEFAULT NULL,
  KEY `key_desc` (`description`(255))
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


MySQL 5.6 :

mysql> create table compatibility_check ( id int, name varchar(50), description varchar(500), key key_desc (description));

ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

mysql> create table compatibility_check ( id int, name varchar(50), description varchar(500), key key_desc (description(100)));

Query OK, 0 rows affected (0.03 sec)


SUMMARY : Make sure that you are not using anywhere creation of index on big varchar columns as part of your code, and going forward use "KEY key_desc (description(255)" instead of "key key_desc (description)", if you have to create and index on varchar columns larger than 255 characters.



MySQL Internals, MySQL HighAvailability, MySQL Performance Tuning, MySQL Query Optimization, MySQL performance, MySQL Fabric, MySQL HA, MySQL Installation, MySQL Upgrade, InnoDB Performance Tuning, InnoDB Buffer Pool Size, MySQL Performance Tuning, MySQL ClusterMySQL Latest News, News and Events, MySQL Customers