I cannot convert myISAM to innodb

Question

I did a wipe and restore.

1. I backedup my current innodb tables. (mysqldump)
2. I loaded it into the database.
3. For some reason...the tables are now all myisam instead of innodb...weird!

4. I try to do:

   ALTER TABLE xxx ENGINE=innodb;

And it doesn't do anything to any table.

"Show table status" still is "MyISAM"

mysql> alter table auth_user_user_permissions engine=innodb;
Query OK, 0 rows affected, 1 warning (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0



+------------+----------+----------------------------------------------------------------+
| Engine     | Support  | Comment                                                        |
+------------+----------+----------------------------------------------------------------+
| MyISAM     | DEFAULT  | Default engine as of MySQL 3.23 with great performance         |
| MEMORY     | YES      | Hash based, stored in memory, useful for temporary tables      |
| InnoDB     | DISABLED | Supports transactions, row-level locking, and foreign keys     |
| BerkeleyDB | NO       | Supports transactions and page-level locking                   |
| BLACKHOLE  | YES      | /dev/null storage engine (anything you write to it disappears) |
| EXAMPLE    | NO       | Example storage engine                                         |
| ARCHIVE    | YES      | Archive storage engine                                         |
| CSV        | YES      | CSV storage engine                                             |
| ndbcluster | DISABLED | Clustered, fault-tolerant, memory-based tables                 |
| FEDERATED  | DISABLED | Federated MySQL storage engine                                 |
| MRG_MYISAM | YES      | Collection of identical MyISAM tables                          |
| ISAM       | NO       | Obsolete storage engine                                        |
+------------+----------+----------------------------------------------------------------+



# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
# You might want to disable InnoDB to shrink the mysqld process by circa 100MB.
#skip-innodb

innodb_buffer_pool_size = 10000M
innodb_additional_mem_pool_size = 20M
innodb_log_file_size=1024M
innodb_log_buffer_size=8M
innodb_flush_log_at_trx_commit = 0


mysql> alter table auth_group engine=innodb;
Query OK, 0 rows affected, 1 warning (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show warnings;
+---------+------+----------------------------------------------------+
| Level   | Code | Message                                            |
+---------+------+----------------------------------------------------+
| Warning | 1266 | Using storage engine MyISAM for table 'auth_group' |
+---------+------+----------------------------------------------------+
1 row in set (0.00 sec)

Imprecise

This is a longshot, but is it possible that your new install of MySQL doesn't include InnoDB support? To see the supported engines issue the following query: show engines;. What is the output? – Quieten

I see you've included an update showing that there is a warning. What is the output of the following query: show warnings;? – Quieten

Very old versions of MySQL do not have InnoDB enabled by default. What version of MySQL are you using? What is the output of the following query: select version();? – Quieten

Answer 1

I see in your show engines output that InnoDB is disabled in your MySQL install. You need to enable it in order to be able to convert your tables from MyISAM to InnoDB.

Quieten

@alex: What version of MySQL are you using? Check with select version();. – Quieten

@alex: Are there any entries in the MySQL error log (during startup, perhaps) that appear to be related to InnoDB support? – Quieten

I got it. Remove ib_logfile0 and ib_logfile1. – Imprecise