MySQL Bugs: #7509: Wrong column type for a table column created from SP variable

Bug #7509	Wrong column type for a table column created from SP variable
Submitted:	23 Dec 2004 15:45	Modified:	21 Nov 2005 8:53
Reporter:	Anindhya Sharma	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S1 (Critical)
Version:	5.0.13	OS:	Any (any)
Assigned to:	Alexander Nozdrin	CPU Architecture:	Any

Description:
I have a main stored procedure that calls other stored procedures. On trying to execute the main procedure twice in successsion gives me the error :

java.sql.SQLException: Specified key was too long; max key length is 1024 bytes
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2849)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1534)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1625)
	at com.mysql.jdbc.Connection.execSQL(Connection.java:2286)
	at com.mysql.jdbc.Connection.execSQL(Connection.java:2211)
	at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1812)
	at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1657)

The stored procedures are responsible for dropping tables, recreating them, copying data from some hold tables and then creating the indexes on them. The first time I run in in "mysql" command prompt it works fine, the second consecutive run gives me the error shown above. 

In trying to come up with a small repeat case, I created a small example but run into another problem with stored procedures,

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 

If I add creating index on the table, I get,

ERROR 1005 (HY000): Can't create table '.\testdb1\#sql-2d8_7.frm' (errno: 139)

So as you can see, I am trying to use stored procedures but keep getting different errors.

How to repeat:
1) Run this from a script file or separately:

create database testdb1;

use testdb1

delimiter :

create procedure gentable()
begin
	create table proctbl (
		num int primary key,
		name varchar(128));
end;
:

create procedure genindex()
begin
	create index proctbl_ind1 on proctbl(name);
end;
:

create procedure droptable()
begin
	drop table if exists proctbl;
end;
:

create procedure genall()
begin
	call droptable();
	call gentable();
	call genindex();
end;
:

delimiter ;

2) First problem. Only create and drop table.

call gentable();
call droptable();

After a few calls (maybe 10 tries) you should see the error.

3) Second problem. Create the index also now.

call gentable();     // comment, only once to create the table
call genall();         // comment, multiple times to see another problem

4) In doing  all of  this my original problem still exists and that is the "java.sql.SQLException: Specified key was too long; max key length is 1024 bytes". 
Note that this works once but fails the next time. Please help !!

5) Note that I ran into my original problem as mentioned in step 4 when calling the main stored procedure using Java Connector version "mysql-connector-java-3.1.5 gamma-bin.jar". When that failed I tried the "mysql prompt" but had the same problem as mentioned in step 4.

I was able reproduce it only if default-character-set is multi-byte, like utf8, sjis.

I am using utf8 as the default-character-set.

The original problem is no longer repeatable.
Instead an instance of the general problem of DDL in SPs.
(Lowering priority to the same as the other bugs of this type, since it's not a crashing bug.)

Hi Per-Erick,

Did you have the character set as multi-byte in your tests when you were trying to reproduce the original problem? Please note that the problem is only seen in that case.

Regards,
Anindhya

I wasn't able repeat initial problem using 5.0.13, but I found another problem.

If table is created via SP column 'name' is created as mediumtext, but in the SP body it's defined as varchar(128).

mysql> call gentable();
Query OK, 0 rows affected (0.02 sec)

mysql> show create table proctbl;
+---------+-------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                        |
+---------+-------------------------------------------------------------------------------------------------------------------------------------+
| proctbl | CREATE TABLE `proctbl` (
  `num` int(11) NOT NULL,
  `name` mediumtext,
  PRIMARY KEY  (`num`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+---------+-------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> delimiter |
mysql> CREATE PROCEDURE p1()
    -> BEGIN
    ->   CREATE TABLE t1(col1 INT PRIMARY KEY, col2 VARCHAR(128));
    -> END|
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> call p1();
Query OK, 0 rows affected (0.00 sec)

mysql> show create table t1;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                      |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `col1` int(11) NOT NULL,
  `col2` varchar(128) default NULL,
  PRIMARY KEY  (`col1`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select version();
+-------------------------------+
| version()                     |
+-------------------------------+
| 5.0.17-valgrind-max-debug-log |
+-------------------------------+
1 row in set (0.00 sec)