CODEWITHSUNDEEP
mysqlsqlsql-mode
mahendra kawde
mahendra kawde

Reputation: 847

Data too long for column at row 1 mysql 5.6

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

enter image description here

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: enter image description here But still getting same error.

Upvotes: 1

Views: 3689

Answers (2)

mahendra kawde
mahendra kawde

Reputation: 847

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&jdbcCompliantTruncation=false

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

Upvotes: 1

Dylan Su
Dylan Su

Reputation: 6065

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));

Upvotes: 0

Related Questions