MySQL Bugs: #13134: Length of VARCHAR() utf8 column is increasing when table is recreated with PS/SP

Bug #13134	Length of VARCHAR() utf8 column is increasing when table is recreated with PS/SP
Submitted:	13 Sep 2005 10:45	Modified:	4 Mar 2006 8:00
Reporter:	Anders Karlsson	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S2 (Serious)
Version:	4.1.16, 5.0.12, 5.0.17-bk	OS:	Windows (Windows/Linux)
Assigned to:	Konstantin Osipov	CPU Architecture:	Any

Description:
Recreating a temporary table in a stroed procedures by a DROP IF EXISTS... CREATE TABLE fails after a certain number of attempts. It seems to depend on the rowsize of the created table, and different errors are returned, and sometimes you just get a warning.
The errors exposed are: 1118, 1074 and Warning 1246.
This is seen on and easily reproduced on Linux with 5.0.12 and Windows with 5.0.11. The warning only seems to appear on this windows version. In 5.0.12 on Linux you get a warning at the first invocation of the procedure, indicating that the table to be dropped didn't exist.
To expose the error, the procedure needs to be run several times, typically 5 or 6.

How to repeat:
On Linux:
$ mysql test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14 to server version: 5.0.12-beta

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

mysql> delimiter //
mysql> CREATE PROCEDURE testtmp()
    -> BEGIN
    -> DROP TEMPORARY TABLE IF EXISTS tmptab;
    -> CREATE TEMPORARY TABLE tmptab(col1 VARCHAR(250),
    ->   col2 VARCHAR(100));
    -> END
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> call testtmp();
Query OK, 0 rows affected, 1 warning (0.04 sec)

mysql> call testtmp();
Query OK, 0 rows affected (0.05 sec)

mysql> call testtmp();
Query OK, 0 rows affected (0.04 sec)

mysql> call testtmp();
Query OK, 0 rows affected (0.04 sec)

mysql> call testtmp();
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs
mysql> call testtmp();
ERROR 1074 (42000): Column length too big for column 'col1' (max = 21845); use BLOB or TEXT instead
mysql>

And on windows:
C:\Documents and Settings\karlsson>mysql test -u karlsson
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 95 to server version: 5.0.11-beta-nt

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

mysql> delimiter //
mysql> CREATE PROCEDURE testtmp()
    -> BEGIN
    ->  DROP TEMPORARY TABLE IF EXISTS tmptab;
    ->  CREATE TEMPORARY TABLE tmptab(col1 VARCHAR(250),
    ->    col2 VARCHAR(100));
    -> END
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> call testtmp();
Query OK, 0 rows affected (0.09 sec)

mysql> call testtmp();
Query OK, 0 rows affected (0.21 sec)

mysql> call testtmp();
Query OK, 0 rows affected (0.22 sec)

mysql> call testtmp();
Query OK, 0 rows affected (0.12 sec)

mysql> call testtmp();
ERROR 1118 (42000): Row size too large. The maximum row size for the used table
type, not counting BLOBs, is 65535. You have to change some columns to TEXT or B
LOBs
mysql> call testtmp();
Query OK, 0 rows affected, 3 warnings (0.13 sec)

mysql> show warnings;
+-------+------+-----------------------------------------------+
| Level | Code | Message                                       |
+-------+------+-----------------------------------------------+
| Note  | 1246 | Converting column 'col1' from VARCHAR to TEXT |
| Note  | 1246 | Converting column 'col2' from VARCHAR to TEXT |
+-------+------+-----------------------------------------------+
2 rows in set (0.00 sec)

mysql>

I was not able to repeat it on 5.0.12-beta-nt at least (will try on todays 5.0.13-BK on Linux later):

mysql> select version();
+----------------+
| version()      |
+----------------+
| 5.0.12-beta-nt |
+----------------+
1 row in set (0.00 sec)

mysql> delimiter //
mysql> CREATE PROCEDURE testtmp()
    -> BEGIN
    ->   DROP TEMPORARY TABLE IF EXISTS tmptab;
    ->   CREATE TEMPORARY TABLE tmptab(col1 VARCHAR(250), col2 VARCHAR(100));
    -> END
    -> //
Query OK, 0 rows affected (0.27 sec)

mysql> delimiter ;
mysql> call testtmp();
Query OK, 0 rows affected, 1 warning (1.76 sec)

mysql> show warnings;
+-------+------+------------------------+
| Level | Code | Message                |
+-------+------+------------------------+
| Note  | 1051 | Unknown table 'tmptab' |
+-------+------+------------------------+
1 row in set (0.00 sec)

mysql> call testtmp();
Query OK, 0 rows affected (0.33 sec)

mysql> call testtmp();
Query OK, 0 rows affected (0.08 sec)

mysql> call testtmp();
Query OK, 0 rows affected (0.08 sec)

mysql> call testtmp();
Query OK, 0 rows affected (0.08 sec)

mysql> call testtmp();
Query OK, 0 rows affected (0.08 sec)

mysql> call testtmp();
Query OK, 0 rows affected (0.08 sec)

...

And so on. No failures.

I should have said that this database, on both WIndows and Linux, are utf8 databases. This might have something to do with this. I did try to create the temp-tables with default charset set to latin-1 (for that particular table that is), and got the same error. I'll test with 5.0.12 on Windows (XP) also.

- Installed MySQL 5.0.12 on Windows XP.
- Set latin1 as default character set.
- Create procedure.
- Run proceure several times, without errors.
- Edit my.ini, set
default-character-set=utf8
instead of latin1.
- Restart MySQL service.
- Reconnect and run procedure again, several times (note that the procedure was NOT recreated after changing the character set).
- After about 6 or 7 tries, failure like before.

Conclusion: This seems to be UTF-8 related, after all.

Verified on 5.0.12-beta-nt just as you described in the previous message. 

Key change is to set default-character-set=utf8 for mysqld in the my.ini file after creation of the procedure and then restart the server (I am not sure it is OK, though). Then:

mysql> call testtmp();
Query OK, 0 rows affected, 1 warning (0.05 sec)

mysql> show warnings;
+-------+------+------------------------+
| Level | Code | Message                |
+-------+------+------------------------+
| Note  | 1051 | Unknown table 'tmptab' |
+-------+------+------------------------+
1 row in set (0.00 sec)

mysql> call testtmp();
Query OK, 0 rows affected (0.08 sec)

mysql> call testtmp();
Query OK, 0 rows affected (0.08 sec)

mysql> call testtmp();
Query OK, 0 rows affected (0.08 sec)

mysql> call testtmp();
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs
mysql> call testtmp();
Query OK, 0 rows affected, 3 warnings (0.15 sec)

mysql> show warnings;
+-------+------+-----------------------------------------------+
| Level | Code | Message                                       |
+-------+------+-----------------------------------------------+
| Note  | 1051 | Unknown table 'tmptab'                        |
| Note  | 1246 | Converting column 'col1' from VARCHAR to TEXT |
| Note  | 1246 | Converting column 'col2' from VARCHAR to TEXT |
+-------+------+-----------------------------------------------+
3 rows in set (0.00 sec)

mysql> select version();
+----------------+
| version()      |
+----------------+
| 5.0.12-beta-nt |
+----------------+
1 row in set (0.00 sec)

Hi!

This problem is not specific to temporary tables and SP. It is also repeatable with permanent tables and prepared statements in 4.1. Here is the test case which proves it:

drop table if exists t1;
prepare stmt1 from 'create table t1 (a varchar(100) character set utf8)';
execute stmt1;
show create table t1;
#+-------+---------------------------------------------------------------------------------------------------------------+
#| Table | Create Table                                                                                                  |
#+-------+---------------------------------------------------------------------------------------------------------------+
#| t1    | CREATE TABLE `t1` (
#  `a` varchar(100) character set utf8 default NULL
#) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
#+-------+---------------------------------------------------------------------------------------------------------------+

drop table t1;
execute stmt1;
show create table t1;
#+-------+---------------------------------------------------------------------------------------------------------------+
#| Table | Create Table                                                                                                  |
#+-------+---------------------------------------------------------------------------------------------------------------+
#| t1    | CREATE TABLE `t1` (
#  `a` varchar(300) character set utf8 default NULL
#) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
#+-------+---------------------------------------------------------------------------------------------------------------+

I have updated synopsis to reflect this.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/2988

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/3083

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/3085

Fixed and pushed into 4.1.18, 5.0.19, 5.1.8 (bk tags)

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Documented bugfix in 4.1.18, 5.0.19, 5.1.8 changelogs. Closed.