MySQL Bugs: #56255: ERROR 1071 (42000): not raisen if innodb not configured

Bug #56255	ERROR 1071 (42000): not raisen if innodb not configured
Submitted:	25 Aug 2010 14:43	Modified:	25 Sep 2010 16:08
Reporter:	rudi pfeilsticker	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Errors	Severity:	S2 (Serious)
Version:	5.0.51a-24	OS:	Linux (5.0.51a-24+lenny4 (Debian))
Assigned to:		CPU Architecture:	Any

Description:
If none of the innodb specific option are given in my.cnf, the user may create indexes on innodb tables which exceed the maximum key length of 1000.

If the options are given "ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes" is correctly raised and the table creation correctly aborted.

How to repeat:

cat /proc/version
Linux version 2.6.32-trunk-amd64 (Debian 2.6.32-3) (ben@decadent.org.uk) (gcc version 4.3.4 (Debian 4.3.4-6) ) #1 SMP Sat Dec 26 17:13:29 UTC 2009
nurago96:/data/operations# 
nurago96:/data/operations# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 52
Server version: 5.0.51a-24+lenny4 (Debian)

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

mysql> select version();
+-------------------+
| version()         |
+-------------------+
| 5.0.51a-24+lenny4 | 
+-------------------+
1 row in set (0.00 sec)

mysql> exit
Bye
nurago96:/data/operations# 
nurago96:/data/operations# grep innodb /etc/mysql/my.cnf 
#skip-innodb

nurago96:/data/operations# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 53
Server version: 5.0.51a-24+lenny4 (Debian)

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

mysql> CREATE DATABASE test /*!40100 DEFAULT CHARACTER SET utf8 */;
Query OK, 1 row affected (0.00 sec)

mysql> use test;
Database changed
mysql> 
mysql> DROP TABLE IF EXISTS `test`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE `test` (
    ->   a varchar(100) NOT NULL,
    ->   b varchar(100) NOT NULL default 'default-b',
    ->   c varchar(100) NOT NULL,
    ->   d varchar(100) NOT NULL,
    ->   `name` varchar(100) NOT NULL,
    ->   `description` longtext NOT NULL,
    ->   `default_allowOrDeny` enum('allow','deny') NOT NULL default 'deny',
    ->   PRIMARY KEY  (c,d,a,b)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
Query OK, 0 rows affected (0.00 sec)

mysql> show create table test;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                                                    |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test  | CREATE TABLE `test` (
  `a` varchar(100) NOT NULL,
  `b` varchar(100) NOT NULL default 'default-b',
  `c` varchar(100) NOT NULL,
  `d` varchar(100) NOT NULL,
  `name` varchar(100) NOT NULL,
  `description` longtext NOT NULL,
  `default_allowOrDeny` enum('allow','deny') NOT NULL default 'deny',
  PRIMARY KEY  (`c`,`d`,`a`,`b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC | 
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> exit
Bye

nurago96:/data/operations# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 54
Server version: 5.0.51a-24+lenny4 (Debian)

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

mysql> drop database test;
Query OK, 1 row affected (0.01 sec)

mysql> exit
Bye
nurago96:/data/operations# 
nurago96:/data/operations# vi "/etc/mysql/my.cnf" 

nurago96:/data/operations# /etc/mysql/init.d/mysql restart
Stopping MySQL database server: mysqld.
Starting MySQL database server: mysqld.
Checking for corrupt, not cleanly closed and upgrade needing tables..
nurago96:/data/operations# grep innodb /etc/mysql/my.cnf
#skip-innodb
innodb_file_per_table
innodb_data_home_dir = /data/mysql/
innodb_data_file_path = ibdata1:500M;ibdata2:10M:autoextend
innodb_log_group_home_dir = /data/mysql/
innodb_log_files_in_group=2
innodb_log_arch_dir = /data/mysql/
innodb_buffer_pool_size = 4096M
innodb_additional_mem_pool_size = 64M
innodb_log_file_size = 1024M
innodb_log_buffer_size = 256M
innodb_flush_log_at_trx_commit = 2
innodb_lock_wait_timeout = 200
innodb_autoextend_increment = 256
nurago96:/data/operations# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 28
Server version: 5.0.51a-24+lenny4 (Debian)

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

mysql> CREATE DATABASE test /*!40100 DEFAULT CHARACTER SET utf8 */;
Query OK, 1 row affected (0.00 sec)

mysql> use test;
Database changed
mysql> 
mysql> DROP TABLE IF EXISTS `test`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE `test` (
    ->   a varchar(100) NOT NULL,
    ->   b varchar(100) NOT NULL default 'default-b',
    ->   c varchar(100) NOT NULL,
    ->   d varchar(100) NOT NULL,
    ->   `name` varchar(100) NOT NULL,
    ->   `description` longtext NOT NULL,
    ->   `default_allowOrDeny` enum('allow','deny') NOT NULL default 'deny',
    ->   PRIMARY KEY  (c,d,a,b)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes
mysql> show create table test;
ERROR 1146 (42S02): Table 'test.test' doesn't exist
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> drop database test;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye
nurago96:/data/operations#

Thank you for the report.

But version 5.0.51 is old and many bugs were fixed since. Please upgrade to current version 5.0.91, try with it and inform us if problem still exists.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".