Bug #36790 Inappropriate 1406 error generated by stored procedure on INSERT

• Submitted: 18 May 2008 23:26
• Modified: 19 May 2008 3:34
• Reporter: David Wyllie
• Status: Not a Bug
• Category: MySQL Server: Stored Routines
• Severity: S2 (Serious)
• Version: 5.1.24
• OS: Windows
• CPU Architecture: Any

[18 May 2008 23:26] David Wyllie

Description:
1. A table is created with one, or more than one, field of type CHAR length 16 (or any other number).
2. Insert into this issued by MySQL query browser is successful.
3. However when these commands are used in a stored procedure, a 1406 error is generated if the string length is more than 1.

This error was previously described associated with character encoding issues
http://forums.mysql.com/read.php?103,51906,176803#msg-176803
however it is supposed to be fixed.

Here, something about the context of use of the insert within the stored procedure causes the insert to fail.

The various remedies described above did not resolve the problem
 

How to repeat:
# architecture: winXP (has not been tried on linux)
# MySql 5.1.24-rc-community via TCP/IP on localhost, compiled by MySQL
# installation script generated by wizard as developer machine.
# Client version MySQL client version 5.1.11

# code to generate problem follows

# adding a table to schema test over which user has priviledges
USE test;

DROP TABLE IF EXISTS `test`.`testtable`;
CREATE TABLE  `test`.`testtable` (
  `testfield` char(16) NOT NULL DEFAULT "default_value"
) ENGINE=InnoDB;

# changing to MyIsam does not resolve the problem

DELIMITER $$

# write log.  just writes three variables to a tables using INSERT
DROP PROCEDURE IF EXISTS `test`.`testproc` $$

CREATE DEFINER=`dwyllie`@`%` PROCEDURE `testproc`(IN var1 CHAR)
BEGIN

# insert variables into the testtable table.

INSERT INTO testtable (testfield) VALUES (var1);

END $$

DELIMITER ;

INSERT INTO test.testtable (testfield) VALUES ("1");
# succeeds

call testproc("1");
# succeeds. reports "No dataset returned", which is correct.

call testproc("11");
# fails, results in Data too long for column "var1" at row 1 error 1406.

INSERT INTO test.testtable (testfield) VALUES ("11");
# succeeds

# have tried to set character sets as described variously here http://forums.mysql.com/read.php?103,51906,176803#msg-176803
# however, problem persists

Suggested fix:
None known

[19 May 2008 3:34] Valeriy Kravchuk

Thank you for a problem report. This is not a bug though. CHAR in MySQL's SP declaration is interpreted as CHAR(1). If you declare procedure with var1 CHAR(16), it will just work:

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot test -P3308
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.60-enterprise-gpl-nt-log MySQL Enterprise Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> exit
Bye

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot test -P3310
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: Y
ES)

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

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> DROP TABLE IF EXISTS `test`.`testtable`;
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> CREATE TABLE  `test`.`testtable` (
    ->   `testfield` char(16) NOT NULL DEFAULT "default_value"
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.22 sec)

mysql> delimiter //
mysql> create PROCEDURE `testproc`(IN var1 CHAR)
    -> BEGIN
    ->
    -> # insert variables into the testtable table.
    ->
    -> INSERT INTO testtable (testfield) VALUES (var1);
    ->
    -> END//
Query OK, 0 rows affected (0.41 sec)

mysql> delimiter ;
mysql> INSERT INTO test.testtable (testfield) VALUES ("1");
Query OK, 1 row affected (0.09 sec)

mysql> call testproc("1");
Query OK, 1 row affected (0.08 sec)

mysql> call testproc("11");
ERROR 1406 (22001): Data too long for column 'var1' at row 1
mysql> DROP PROCEDURE IF EXISTS `test`.`testproc`;
Query OK, 0 rows affected (0.41 sec)

mysql> delimiter //
mysql> create PROCEDURE `testproc`(IN var1 CHAR(16))
    -> BEGIN
    -> # insert variables into the testtable table.
    -> INSERT INTO testtable (testfield) VALUES (var1);
    -> END//
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> call testproc("11");
Query OK, 1 row affected (0.02 sec)