MySQL Bugs: #33140: When federated tables are not enabled, ENGINE=federated silently behaves wrongly

Bug #33140	When federated tables are not enabled, ENGINE=federated silently behaves wrongly
Submitted:	11 Dec 2007 13:41	Modified:	18 Dec 2007 12:23
Reporter:	River Tarnell	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Federated storage engine	Severity:	S3 (Non-critical)
Version:	5.0.51	OS:	Solaris (Solaris 10 11/06)
Assigned to:		CPU Architecture:	Any

Description:
We recently upgraded to a new MySQL server, and neglected to enable federated table engine in the new version.  Existing federates tables then stopped working; the error log contained this:

071211  0:33:52071211  0:33:52 [ERROR] Cannot find table toolserver_priv/wiki from the internal data dictionary
of InnoDB though the .frm file for the table exists. Maybe you
have deleted and recreated InnoDB data files but have forgotten
to delete the corresponding .frm files of InnoDB tables, or you
have moved .frm files to another database?
See http://dev.mysql.com/doc/refman/5.0/en/innodb-troubleshooting.html
how you can resolve the problem.

Apparently, the tables had reverted to InnoDB tables, but InnoDB did not know about them.  Nothing was logged suggesting that federated tables where missing.

Additionally, when creating new federated tables, the server would silently convert them to empty InnoDB tables, which was rather perplexing.

How to repeat:
Compile the server without the federated storage engine, and try to create a federated table.

Suggested fix:
Produce an error when trying to create federated tables without the engine.

Does SQL_MODE for that server has 'NO_ENGINE_SUBSTITUTION'?  I guess not!! MySQL per default creates table using the default engine. With other words default is SQL_MODE <> 'NO_ENGINE_SUBSTITUTION'.  I also think your default engine is InnoDB?

But definitely if InnoDB is 'silently' used, InnoDB should handle the table correctly!

(this is not an official reply .. I am no MySQL person!)

MySQL per default creates table using the default engine.

add:
.. if the specified engine is not available.

Please, send your my.cnf file content.

/etc/my.cnf

Attachment: my.cnf (, text), 4.39 KiB.

Thank you for the bug report. You have in your my.cnf file:
default_table_type = InnoDB and please see Peter's comments
about sql_mode regarding the engine.

I think that ENGINE SUBSTITUION should always be disabled for FEDERATED engine, as the CREATE TABLE statement for FEDERATED and other TABLES are not compatible!

@miguel: can you change to feature request or will I have to create a new report?

The problem is not only creating new tables (although I think this is dubious default behaviour, I see it's not a bug.)

The other problem is that _existing_ federated tables, when the server is running with federated tables disabled (which is easy to do by accident, because they aren't in the default build) are silently converted to non-functional InnoDB tables with absolutely no indication of what's wrong.

I think that if you want the MySQL people to read your posts you will have to change the status of this report!