Bug #48554 Varchar Limt

• Submitted: 5 Nov 2009 7:28
• Modified: 5 Nov 2009 9:03
• Reporter: Rama Krishna G
• Status: Closed
• Category: MySQL Server: Data Types
• Severity: S1 (Critical)
• Version: 5.0.5
• OS: Windows
• CPU Architecture: Any
• Tags: limit, size, varchar

[5 Nov 2009 7:28] Rama Krishna G

Description:
I declared a column as varchar(750) in a table. 

1) It is accepting 512 chars only into table if am giving more than 512 and less than 750 chars data. 
2) Getting error if am giving more than 750 char data.

How to repeat:
Declare a column as varchar(750). Execute a java class which includes the data into table.

[5 Nov 2009 7:41] Valeriy Kravchuk

Thank you for the problem report. Pleae, send the results of SHOW CREATE TABLE for the table used. Your Java code would be nice to review also.

[5 Nov 2009 8:31] Rama Krishna G

CREATE TABLE `AccountDetail` (                           
                 `accountDetailId` bigint(20) NOT NULL auto_increment,
                 `clientDefinedString1` varchar(100) default NULL,      
                 `clientDefinedString2` varchar(100) default NULL,      
                 `clientDefinedString3` varchar(750) default NULL,      
                 `updatedPaymentAmountDate` datetime default NULL,      
                 `updatedPaymentDayDate` datetime default NULL,         
                 PRIMARY KEY  (`accountDetailId`)                       
               ) ENGINE=InnoDB DEFAULT CHARSET=latin1

[5 Nov 2009 8:55] Valeriy Kravchuk

Look at the following test:

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot -P3310 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.39-community MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE TABLE `AccountDetail` (
    ->                  `accountDetailId` bigint(20) NOT NULL auto_increment,
    ->                  `clientDefinedString1` varchar(100) default NULL,
    ->
    ->                  `clientDefinedString2` varchar(100) default NULL,
    ->
    ->                  `clientDefinedString3` varchar(750) default NULL,
    ->
    ->                  `updatedPaymentAmountDate` datetime default NULL,
    ->
    ->                  `updatedPaymentDayDate` datetime default NULL,
    ->
    ->                  PRIMARY KEY  (`accountDetailId`)
    ->
    ->                ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (1.42 sec)

mysql> insert into `AccountDetail` (`clientDefinedString3`) values (repeat('.',
600));
Query OK, 1 row affected (0.86 sec)

mysql> select length(`clientDefinedString3`) from `AccountDetail`;
+--------------------------------+
| length(`clientDefinedString3`) |
+--------------------------------+
|                            600 |
+--------------------------------+
1 row in set (1.09 sec)

mysql> insert into `AccountDetail` (`clientDefinedString3`) values (repeat('.',
800));
ERROR 1406 (22001): Data too long for column 'clientDefinedString3' at row 1
mysql> set session sql_mode='';
Query OK, 0 rows affected (0.08 sec)

mysql> insert into `AccountDetail` (`clientDefinedString3`) values (repeat('.',
800));
Query OK, 1 row affected, 1 warning (0.06 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1265
Message: Data truncated for column 'clientDefinedString3' at row 1
1 row in set (0.02 sec)

mysql> select length(`clientDefinedString3`) from `AccountDetail`;
+--------------------------------+
| length(`clientDefinedString3`) |
+--------------------------------+
|                            600 |
|                            750 |
+--------------------------------+
2 rows in set (0.00 sec)

This is normal server's behavior (note how sql_mode setting changed it). If you see something else in your java app, please, upload its code.

What version of MySQL server are you working with?

[5 Nov 2009 9:03] Rama Krishna G

The main problem is with SqlYog tool.