ERROR - Function 'innodb' already exists

Question

I'm looking to start converting some MyISAM tables to INNODB. Running Ubuntu 14.04 Server with MySQL 5.6 ( apt-get upgraded from 5.5 ), 32G ram on 4 core/ 8 thread cpu. Initially had trouble with...

could not open single-table tablespace file .\mysql\innodb_index_stats.ibd after restart of MySQL

and

Error: Table "mysql"."innodb_table_stats" not found.

I followed Rolando's posted advice and the DB isn't crashing or creating a huge error.log anymore. ( the above issues are gone, just describing leading events )

Now I'm concerned about the following errors before I convert anything to INNODB. Federated can be ignored from what I read, the second line below ( also the subject of this post ) concerns me

2014-05-19 01:50:57 30950 [Note] Plugin 'FEDERATED' is disabled.
2014-05-19 01:50:57 30950 [ERROR] Function 'innodb' already exists
2014-05-19 01:50:57 30950 [Warning] Couldn't load plugin named 'innodb' with soname 'ha_innodb.so'.
2014-05-19 01:50:57 30950 [ERROR] Function 'federated' already exists
2014-05-19 01:50:57 30950 [Warning] Couldn't load plugin named 'federated' with soname 'ha_federated.so'.
2014-05-19 01:50:57 30950 [ERROR] Function 'blackhole' already exists
2014-05-19 01:50:57 30950 [Warning] Couldn't load plugin named 'blackhole' with soname 'ha_blackhole.so'.
2014-05-19 01:50:57 30950 [ERROR] Function 'archive' already exists
2014-05-19 01:50:57 30950 [Warning] Couldn't load plugin named 'archive' with soname 'ha_archive.so'.

added the following to my.cnf from Rolando's posted advice

innodb_buffer_pool_size=4G
innodb_buffer_pool_instances=2
innodb_read_io_threads=12
innodb_write_io_threads=12
innodb_io_capacity=300
innodb_log_file_size=128M

Wasn't sure if there should be anything else INNODB related. Based on what I could find also added

innodb_flush_method            = O_DIRECT
innodb_log_files_in_group      = 2
innodb_flush_log_at_trx_commit = 1
innodb_file_per_table          = 1

That didn't help. Not that I expected it to eliminate an "existing function"...lol

Answer 1

The plugins throwing errors in the error.log were not specifically specified in my.cnf. So I ran...

select * from mysql.plugin

for MySQL 5.6.17 I deleted the following rows in mysql.plugin

innodb ha_innodb.so
federated ha_federated.so
blackhole ha_blackhole.so
archive ha_archive.so

Do not delete anything you are not sure about. The above worked for me as...

• I do not use Federated ( accesses data in tables of remote databases )
• INNODB is built into 5.6, no plugin required
• I do not use Blackhole ( accepts data but throws it away and does not store it )
• I do not use Archive ( storing large amounts of data without indexes in a very small footprint )

Answer 2

PROBLEM

mysql.innodb_table_stats is part of the MySQL 5.6 install. mysql_upgrade does not generate them. Why ?

• When you install MySQL 5.6 from scratch, that file is generated.
• The file innodb_table_stats.ibd does not exists in the mysql schema in MySQL 5.5
• MySQL 5.5's mysql_upgrade is not aware of any innodb system tables.
• In fact, there are five(5) .ibd files in MySQL 5.6 in the mysql schema.

SOLUTION

Please look at these posts I wrote last year on how to make them

• Dec 07, 2013 : InnoDB: Error: Table "mysql"."innodb_table_stats" not found after upgrade to mysql 5.6
• Aug 15, 2013 : Cannot open table mysql/innodb_index_stats

Give it a Try !!!

Answer 3

I got these kind of similar errors in PCLinuxOs. What I have done is just renamed the file /etc/my.cnf to /etc/my.cnf.bak and I was able to start the mysql server. I don't know the exact reason but it worked for me. The settings in that file may be conflicting.