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 JUNE 21, 2017

Applies to:

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

Symptoms

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.

Changes

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.

Cause

Sign In with your My Oracle Support account

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

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms