MySQL Bugs: #15884: Message "Column length too big for column" while inserting into temporary table

Bug #15884	Message "Column length too big for column" while inserting into temporary table
Submitted:	20 Dec 2005 10:03	Modified:	5 Jun 2006 17:57
Reporter:	Andrey Kazachkov	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.0.18-nt	OS:	Windows (windows/Linux suse)
Assigned to:	Alexey Botchkov	CPU Architecture:	Any

Description:
After running for 6 times stored procedure proc_3 (described below) error "Column length too big for column 'wstrName' (max = 21845); use BLOB or TEXT instead" occures.

So, we have following stored procedures 

CREATE PROCEDURE proc_1(
	OUT	__nCount		int)
BEGIN
	SELECT COUNT(*) INTO __nCount FROM __tmp_xx;
END

CREATE PROCEDURE proc_2(
	OUT	__nCount		int)
BEGIN
	DROP TEMPORARY TABLE IF EXISTS __tmp_xx;
	CREATE TEMPORARY TABLE __tmp_xx(
		nId int AUTO_INCREMENT PRIMARY KEY,
		wstrName nvarchar(256) NOT NULL
	);
	INSERT INTO __tmp_xx (wstrName) VALUES ('xx');
	CALL proc_1(__nCount);
	DROP TEMPORARY TABLE IF EXISTS __tmp_xx;
END

CREATE PROCEDURE proc_3()
BEGIN
	DECLARE __nCount int;
	CALL proc_2(__nCount);
	SELECT __nCount;
END

Now run "CALL proc_3()" after 6-th run error "Column length too big for column 'wstrName' (max = 21845); use BLOB or TEXT instead" occures.

How to repeat:
Run "CALL proc_3()" for 6 times and you'll probably get the desribed error.
Codepage UTF8 for the database is used.
______________________________
Database	Create Database
mytest	CREATE DATABASE `mytest` /*!40100 DEFAULT CHARACTER SET utf8 */

I was unable to repeat, could you please provide your my.ini file/

Thanks in advance.

C:\mysql\bin>mysql -uroot --default-character-set=utf8 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 5.0.17-nt

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

mysql> delimiter //
mysql> CREATE PROCEDURE proc_1(OUT __nCount int)
    -> BEGIN
    ->  SELECT COUNT(*) INTO __nCount FROM __tmp_xx;
    -> END//
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> CREATE PROCEDURE proc_2(OUT __nCount int)
    -> BEGIN
    -> DROP TEMPORARY TABLE IF EXISTS __tmp_xx;
    -> CREATE TEMPORARY TABLE __tmp_xx(
    -> nId int AUTO_INCREMENT PRIMARY KEY,
    -> wstrName nvarchar(256) NOT NULL  );
    -> INSERT INTO __tmp_xx (wstrName) VALUES ('xx');
    -> CALL proc_1(__nCount);
    -> DROP TEMPORARY TABLE IF EXISTS __tmp_xx;
    -> END//
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> CREATE PROCEDURE proc_3()
    -> BEGIN
    -> DECLARE __nCount int;
    -> CALL proc_2(__nCount);
    -> SELECT __nCount;
    -> END//
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> delimiter ;
mysql>
mysql> CALL proc_3();
+----------+
| __nCount |
+----------+
|        1 |
+----------+
1 row in set (0.09 sec)

Query OK, 0 rows affected, 1 warning (0.09 sec)

mysql> CALL proc_3();
+----------+
| __nCount |
+----------+
|        1 |
+----------+
1 row in set (0.09 sec)

Query OK, 0 rows affected, 1 warning (0.09 sec)

mysql> CALL proc_3();
+----------+
| __nCount |
+----------+
|        1 |
+----------+
1 row in set (0.09 sec)

Query OK, 0 rows affected, 1 warning (0.09 sec)

mysql> CALL proc_3();
+----------+
| __nCount |
+----------+
|        1 |
+----------+
1 row in set (0.13 sec)

Query OK, 0 rows affected, 1 warning (0.13 sec)

mysql> CALL proc_3();
+----------+
| __nCount |
+----------+
|        1 |
+----------+
1 row in set (0.08 sec)

Query OK, 0 rows affected, 1 warning (0.08 sec)

mysql> CALL proc_3();
+----------+
| __nCount |
+----------+
|        1 |
+----------+
1 row in set (0.09 sec)

Query OK, 0 rows affected, 2 warnings (0.09 sec)

mysql> CALL proc_3();
+----------+
| __nCount |
+----------+
|        1 |
+----------+
1 row in set (0.09 sec)

Query OK, 0 rows affected, 1 warning (0.09 sec)

mysql> CALL proc_3();
+----------+
| __nCount |
+----------+
|        1 |
+----------+
1 row in set (0.08 sec)

Query OK, 0 rows affected, 1 warning (0.08 sec)

mysql> CALL proc_3();
+----------+
| __nCount |
+----------+
|        1 |
+----------+
1 row in set (0.09 sec)

Query OK, 0 rows affected, 1 warning (0.09 sec)

mysql> CALL proc_3();
+----------+
| __nCount |
+----------+
|        1 |
+----------+
1 row in set (0.09 sec)

Query OK, 0 rows affected, 1 warning (0.09 sec)

mysql> CALL proc_3();
+----------+
| __nCount |
+----------+
|        1 |
+----------+
1 row in set (0.09 sec)

Query OK, 0 rows affected, 1 warning (0.09 sec)

required my.ini file

Attachment: my.ini (application/octet-stream, text), 9.03 KiB.

script that caused the error.

Attachment: script.txt (text/plain), 827 bytes.

Required file my.ini was attached. I also attached full sql script used to reproduce behaviour.

Thank you for the bug report and feedback.
I just modified the script in the line use database mytest to use mytest.

+----------+
| __nCount |
+----------+
|        1 |
+----------+
1 row in set (0.03 sec)

Query OK, 0 rows affected, 1 warning (0.03 sec)

ERROR 1074 (42000): Column length too big for column 'wstrName' (max = 21845); use BLOB or TEXT instead
5.0.18>delimiter ;
5.0.18>select version(); 
+------------------+
| version()        |
+------------------+
| 5.0.18-debug-log |
+------------------+
1 row in set (0.00 sec)

The bug can be reproduced in a more simple way. It's enough to create a temporary table within a stored procedure to reproduce the bug. The table must containt varchar(n) column. 

Script to reproduce the behaviour: 

----------------------------------------------------------------------
delimiter GO

DROP DATABASE IF EXISTS MYTEST 
GO

CREATE DATABASE MYTEST DEFAULT CHARACTER SET utf8
GO

USE MYTEST
GO

DROP PROCEDURE IF EXISTS proc_3
GO

CREATE PROCEDURE proc_3()
BEGIN
	CREATE TEMPORARY TABLE IF NOT EXISTS __tmp_xx(
		strName varchar(256) 
	);
END
GO

CALL proc_3();
CALL proc_3();
CALL proc_3();
CALL proc_3();
CALL proc_3();
CALL proc_3();

delimiter ;

----------------------------------------------------------------------

Unfortunately I can't see any workaround for it :-(.

Alexey,

I was unable to repeat also, then I assume that was already fixed
with current release server 5.0.22.

It seems to be fixed