Data too long for column at row 1 mysql 5.6

Question

I am using MySql 5.6 in production for my application. Now what happens is there is one field on the form in application which has data type char(2) table and its maximum length for input type is 25. Now when user types in characters more than 2 and hit save it prompts that

So I searched and found here that if I need to make this without error I need to switch MySQL mode to not use STRICT and hence I set sql_mode in my.cnf file and tried. But still I am getting same error. Here is my.cnf looks like:

[mysqld]
#
# * Basic Settings
#
user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
lc-messages-dir = /usr/share/mysql
sql_mode=NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

I am using MySql 5.6 I am not getting what I am missing here. I want that error to disappear.

EDIT:

I did SET GLOBAL sql_mode = '' and then did show variables; Here is output: But still getting same error.

My be your data has more length than the defined length of column — safin chacko, Feb 25 '16 at 07:18

score 1 · Answer 1 · answered Feb 29 '16 at 06:26

I found solution to this issue, which is given below:

You can solve error by adding property jdbcCompliantTruncation=false in JBoss datasource like:

jdbc:mysql://localhost:3306/dbName?zeroDateTimeBehavior=convertToNull&amp;jdbcCompliantTruncation=false

Note: When you have more than one properties to set as shown above you can separate them using &

Dylan Su · Answer 2 · 2016-02-25T07:45:32.593

0

You can set the system variable sql_mode with sql:

SET GLOBAL sql_mode = '';

If it is a INSERT statement, the error will persist even with non-STRICT sql mode.

In this case, you need to modify ur column data type definition to allow longer data input, or modify your query.

You can modify ur query like this to truncate the data by yourself, since MySQL is not handling it automatically.

INSERT ... VALUES( ..., LEFT('...',2));

edited Feb 25 '16 at 07:45

answered Feb 25 '16 at 07:19

Dylan Su

5,975
1
16
25

Hey thanks for quick response. I tried above query and still same error – mahendra kawde Feb 25 '16 at 07:24
If it is a select, the error will become a warning. If it is a select, it will fail with ERROR. what is ur query type? can u give a sample? – Dylan Su Feb 25 '16 at 07:26
I am submitting form and hence it is INSERT query – mahendra kawde Feb 25 '16 at 07:27
then no way to remove the error. You need to modify the table structure or modify ur query. – Dylan Su Feb 25 '16 at 07:41
Updated the post with more info. – Dylan Su Feb 25 '16 at 08:07

Data too long for column at row 1 mysql 5.6

2 Answers2