MySQL Bugs: #56136: Could not able assign InnoDB storage engine for an table

Bug #56136	Could not able assign InnoDB storage engine for an table
Submitted:	20 Aug 2010 7:53	Modified:	20 Aug 2010 9:10
Reporter:	Anil Alpati	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:		OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Could not able assign InnoDB storage engine for an table

Below is log which I have used -

mysql> alter table hi engine='InnoDB';
Query OK, 0 rows affected, 1 warning (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table hi;
+-------+-------------------------------------------------------------------------------------+
| Table | Create Table                                                                        |
+-------+-------------------------------------------------------------------------------------+
| hi    | CREATE TABLE `hi` (
  `i` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

How to repeat:
same as above

Please check SHOW WARNINGS to see what is the warning returned.

InnoDB is likely either not enabled, or failed to start.
Check output of 'SHOW ENGINES' and mysql error log for details about this case.

Below is error log...  Yes you are right could not start InnoDB Settings 

100820 12:48:13 mysqld_safe mysqld from pid file /usr/local/mysql/data/anil-desktop.pid ended
100820 12:52:00 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data
Warning: World-writable config file '/usr/local/mysql/my.cnf' is ignored
100820 12:52:00 [Note] Plugin 'FEDERATED' is disabled.
InnoDB: Error: log file ./ib_logfile0 is of different size 0 268435456 bytes
InnoDB: than specified in the .cnf file 0 5242880 bytes!
100820 12:52:00 [ERROR] Plugin 'InnoDB' init function returned error.
100820 12:52:00 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
100820 12:52:00 [Warning] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=anil-desktop-relay-bin' to avoid this problem.
100820 12:52:00 [ERROR] Failed to open the relay log './stageserver-relay-bin.000001' (relay_log_pos 4)
100820 12:52:00 [ERROR] Could not find target log during relay log initialization
100820 12:52:00 [ERROR] Failed to initialize the master info structure
100820 12:52:00 [Note] Event Scheduler: Loaded 0 events
100820 12:52:00 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: '5.1.45-enterprise-gpl-advanced'  socket: '/tmp/mysql.sock'  port: 3306  MySQL Enterprise Server - Advanced Edition (GPL)

Below Show status - 

mysql> show engines;
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine     | Support | Comment                                                        | Transactions | XA   | Savepoints |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance         | NO           | NO   | NO         |
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| BLACKHOLE  | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV        | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| FEDERATED  | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| ARCHIVE    | YES     | Archive storage engine                                         | NO           | NO   | NO         |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
7 rows in set (0.00 sec)

mysql> show warnings
    -> ;
Empty set (0.00 sec)

mysql>

so, this is definitely not a bug.  please fix your my.cnf with regards to innodb_log_file_size and be sure innodb starts.

I have fixed the issue by stopping the server and deleting old idblogs files which could not initialized the pool. 

After started the mysql server and InnoDB Configuration started sucessfully