My Oracle Support Banner

Query Errors After Upgrading to MySQL 5.6 From MySQL 5.5 or Earlier or Adding the SQL Mode STRICT_TRANS_TABLES or STRICT_ALL_TABLES (Doc ID 2091447.1)

Last updated on MARCH 24, 2020

Applies to:

MySQL Server - Version 5.6 and later
Information in this document applies to any platform.


When trying to insert into a table or updating an existing record with a value that does not fit into the column, the INSERT, UPDATE, or LOAD DATA INFILE fails with the an error like:

The rest of the document will use the above example as a base for the discussion.


The issue will start after enabling the STRICT_TRANS_TABLES SQL mode for transactional storage engines (InnoDB) and STRICT_ALL_TABLES for non-transactional storage engines.

The STRICT_TRANS_TABLES SQL mode can be enabled if you have upgraded to MySQL 5.6 in a way that executed mysql_install_db (i.e. starting with a fresh install). See also the Cause section below.


To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

In this Document

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.