Bug #15730 Can't change Storage Engine with 'alter table'
Submitted: 14 Dec 2005 7:36 Modified: 28 Dec 2005 15:26
Reporter: takuya koide Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.1.3 OS:Linux (Red Hat Enterprise Linux AS3)
Assigned to: Aleksey Kishkin CPU Architecture:Any

[14 Dec 2005 7:36] takuya koide
Description:
when use 'alter table table_name engine=innodb' on 5.1.3, cause warnings 'Using storage engine MyISAM for table table_name' and don't change 'Storage Engine'.

How to repeat:
MySQL 5.0.16 (OK)
  > use test
  Database changed

  > create table t1 (c1 int) ENGINE=MyISAM;
  Query OK, 0 rows affected (0.01 sec)
  
  > alter table t1 ENGINE=InnoDB;
  Query OK, 0 rows affected (0.01 sec)
  Records: 0  Duplicates: 0  Warnings: 0
  
  > show create table t1;
  +-------+---------------------------------------------------+
  | Table | Create Table                                      |
  +-------+---------------------------------------------------+
  | t1    | CREATE TABLE `t1` (
    `c1` int(11) default NULL
  ) ENGINE=InnoDB DEFAULT CHARSET=ujis |
  +-------+---------------------------------------------------+
  1 row in set (0.00 sec)

MySQL 5.1.3 (NG)
  > use test
  Database changed

  > create table t1 (c1 int) ENGINE=MyISAM;
  Query OK, 0 rows affected (0.03 sec)
  
  > alter table t1 ENGINE=InnoDB;
  Query OK, 0 rows affected, 1 warning (0.00 sec)
  Records: 0  Duplicates: 0  Warnings: 0
  
  > show warnings;
  +---------+------+--------------------------------------------+
  | Level   | Code | Message                                    |
  +---------+------+--------------------------------------------+
  | Warning | 1266 | Using storage engine MyISAM for table 't1' |
  +---------+------+--------------------------------------------+
  1 row in set (0.00 sec)
  
  > show create table t1;
  +-------+-----------------------------------------------------+
  | Table | Create Table                                        |
  +-------+-----------------------------------------------------+
  | t1    | CREATE TABLE `t1` (
    `c1` int(11) default NULL
  ) ENGINE=MyISAM DEFAULT CHARSET=ujis |
  +-------+-----------------------------------------------------+
  1 row in set (0.01 sec)

Suggested fix:
no idea.
[19 Dec 2005 14:24] Aleksey Kishkin
Hi! cannot reproduce it. 

takuya, could you provide here output of 
show engines;
?

mysql> create table t1 (c1 int) ENGINE=MyISAM;
Query OK, 0 rows affected (0.02 sec)

mysql> alter table t1 ENGINE=InnoDB;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table t1;
+-------+-----------------------------------------------------------------------                                                                           -----------------+
| Table | Create Table                                                                                                                                                      |
+-------+-----------------------------------------------------------------------                                                                           -----------------+
| t1    | CREATE TABLE `t1` (
  `c1` int(11) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-----------------------------------------------------------------------                                                                           -----------------+
1 row in set (0.00 sec)

mysql> select version();
+-------------------+
| version()         |
+-------------------+
| 5.1.4-alpha-debug |
+-------------------+
1 row in set (0.00 sec)
[20 Dec 2005 2:03] takuya koide
I see. 

I get mysql-5.1.3-alpha.tar.tar from http://dev.mysql.com/downloads/mysql/5.1.html but this tar archive file does not include InnoDB.

$ ls
md5.txt  mysql-5.1.3-alpha.tar.tar

(md5.txt is md5 strings in http://dev.mysql.com/downloads/mysql/5.1.html)

$ cat md5.txt
43f4d55b472124ce0d9a76185722c1e7 
$ md5sum mysql-5.1.3-alpha.tar.tar 
43f4d55b472124ce0d9a76185722c1e7  mysql-5.1.3-alpha.tar.tar
$ tar xfz mysql-5.1.3-alpha-retry.tar.tar 
$ ls mysql-5.1.3-alpha
BUILD/              SSL/             dbug/        missing*       sql-bench/
COPYING             aclocal.m4       depcomp*     mysql-test/    sql-common/
ChangeLog           client/          extra/       mysys/         storage/
Docs/               cmd-line-utils/  include/     netware/       strings/
EXCEPTIONS-CLIENT   config/          install-sh*  os2/           support-files/
INSTALL-SOURCE      config.guess*    libmysql/    pstack/        tests/
INSTALL-WIN-SOURCE  config.h.in      libmysql_r/  regex/         tools/
Makefile.am         config.sub*      libmysqld/   scripts/       vio/
Makefile.in         configure*       ltmain.sh    server-tools/  zlib/
README              configure.in     man/         sql/

Not found innobase dir !
[27 Dec 2005 13:21] Aleksey Kishkin
it must be in the 'storage' directory.
I want to be sure that your binary was compiled without innodb support, and behaviuor you noted isn't result of some bug. Could you please write here output of 'show engines;' command?
[28 Dec 2005 7:48] takuya koide
I see 'Not Bug' entirely!

I'm verry sorry. I cannot notice 'storage' directory and following 'configure option'.

  --with-innodb
    enable innobase storage engine (default is no)

I get the idea that innodb is 'default yes' because earlier 5.0 is so.

working of compile was following...

$ ./configure --prefix=/usr/local/mysql
$ make
$ make install

output of 'show engines' is following...
+----------+-------+------------+--+----------+
|Engine    |Support|Transactions|XA|Savepoints|
+----------+-------+------------+--+----------+
|MEMORY    |    YES|          NO|NO|        NO|
|MyISAM    |DEFAULT|          NO|NO|        NO|
|MRG_MYISAM|    YES|          NO|NO|        NO|
+----------+-------+------------+--+----------+
(Comment is omitted)

I ought to have done following...

$ ./configure --prefix=/usr/local/mysql --with-innodb
$ make
$ make install