MySQL Bugs: #24879: Prepared Statements: CREATE TABLE (UTF8 KEY) produces a growing key length

Bug #24879	Prepared Statements: CREATE TABLE (UTF8 KEY) produces a growing key length
Submitted:	7 Dec 2006 11:11	Modified:	4 Jun 2007 18:13
Reporter:	Konstantin Osipov (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Prepared statements	Severity:	S3 (Non-critical)
Version:	4.1/5.0/5.1BK	OS:	Linux (Linux)
Assigned to:	Konstantin Osipov	CPU Architecture:	Any
Tags:	alter, prepared statement

Description:
CREATE TABLE with a UTF8 key produces a bigger key length on subsequent execution.

mysql> drop table if exists t1;
Query OK, 0 rows affected (0.00 sec)

mysql> prepare stmt from "create table t1 (c char(100) character set utf8, key (c(10)))";
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> execute stmt;
Query OK, 0 rows affected (0.02 sec)

mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `c` char(100) character set utf8 default NULL,
  KEY `c` (`c`(10))
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> drop table t1;
Query OK, 0 rows affected (0.01 sec)

mysql> execute stmt;
Query OK, 0 rows affected (0.00 sec)

mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `c` char(100) character set utf8 default NULL,
  KEY `c` (`c`(30))
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

## Notice that the key length is growing (10 -> 30)

How to repeat:
drop table if exists t1;
prepare stmt from "create table t1 (c char(100) character set utf8, key (c(10)))";
execute stmt;
show create table t1\G
drop table t1;
execute stmt;
show create table t1\G

Thank you for the bug report verified as described.

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/27449

ChangeSet@1.2515, 2007-05-28 15:30:01+04:00, kostja@vajra.(none) +17 -0
  5.1 version of a fix and test cases for bugs:
  Bug#4968 ""Stored procedure crash if cursor opened on altered table"
  Bug#6895 "Prepared Statements: ALTER TABLE DROP COLUMN does nothing"
  Bug#19182 "CREATE TABLE bar (m INT) SELECT n FROM foo; doesn't work from 
  stored procedure."
  Bug#19733 "Repeated alter, or repeated create/drop, fails"
  Bug#22060 "ALTER TABLE x AUTO_INCREMENT=y in SP crashes server"
  Bug#24879 "Prepared Statements: CREATE TABLE (UTF8 KEY) produces a 
  growing key length" (this bug is not fixed in 5.0)
  
  Re-execution of CREATE DATABASE, CREATE TABLE and ALTER TABLE 
  statements in stored routines or as prepared statements caused
  incorrect results (and crashes in versions prior to 5.0.25).
  
  In 5.1 the problem occured only for CREATE DATABASE, CREATE TABLE
  SELECT and CREATE TABLE with INDEX/DATA DIRECTOY options).
    
  The problem of bugs 4968, 19733, 19282 and 6895 was that functions
  mysql_prepare_table, mysql_create_table and mysql_alter_table are not
  re-execution friendly: during their operation they modify contents
  of LEX (members create_info, alter_info, key_list, create_list),
  thus making the LEX unusable for the next execution.
  In particular, these functions removed processed columns and keys from
  create_list, key_list and drop_list. Search the code in sql_table.cc 
  for drop_it.remove() and similar patterns to find evidence.
    
  The fix is to supply to these functions a usable copy of each of the
  above structures at every re-execution of an SQL statement. 
    
  To simplify memory management, LEX::key_list and LEX::create_list
  were added to LEX::alter_info, a fresh copy of which is created for
  every execution.
    
  The problem of crashing bug 22060 stemmed from the fact that the above 
  metnioned functions were not only modifying HA_CREATE_INFO structure 
  in LEX, but also were changing it to point to areas in volatile memory
  of the execution memory root.
     
  The patch solves this problem by creating and using an on-stack
  copy of HA_CREATE_INFO in mysql_execute_command.
  
  Additionally, this patch splits the part of mysql_alter_table
  that analizes and rewrites information from the parser into
  a separate function - mysql_prepare_alter_table, in analogy with
  mysql_prepare_table, which is renamed to mysql_prepare_create_table.

Queued in 5.1-runtime

Pushed into 5.1.20-beta

Noted in 5.1.20 changelog.