| 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: | |
| 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 |
[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
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.