#1030 – Got error 1 from storage engine

Posted on December 14, 2012

0


I was setting up a federated MySQL connection and had to fight through a lot of error messages from firewall settings to MySQL user synchronization. The last error I got before the setup was finally working was a real killer: “#1030 – Got error 1 from storage engine”. Try to search for this error and all you’ll find out is that this is a general database error and there can be a lot of matters that give up this message. Documentation is not helpful, either.

Federated setup means a distributed database system. You have two servers, lets call them local and remote. Create a normal table on remote server and create an exact copy of that table on local server, but define it with “ENGINE=FEDERATED” as explained in here (http://dev.mysql.com/doc/refman/5.1/en/federated-create-connection.html). What happens is that local table will be created as an empty file, and it will always stay empty, because it cheats the local database to use the normal table on remote server via empty federated table. From local point of view, you have a normal table there, but its data is actually read from the remote server.

Word of caution: this means local federated table is really remote. In production enviromnent you want to make a local, static copy from this using for example materialized view -technique (http://nuts-and-bolts-of-cakephp.com/2010/09/20/speeding-things-up-with-materialized-views-and-mysql/), or something similar. That is because the federated connections is somewhat slow, and it also has some real limitations (see http://dev.mysql.com/doc/refman/5.1/en/federated-usagenotes.html).

It is a bit of a hassle to make sure firewalls are configured right (tip: you can use “telnet [ip] [port]” to test for MySQL connections as well), user privileges are properly set on both ends and forget not to restart both MySQL servers with federated engine enabled. To permanently enable federated, edit /etc/my.cnf or where ever your configuration file is, and just add word federated after [mysqld] title.

Solution for me to #1030 – Got error 1 from storage engine message was to see if both MySQLs are using the same engine for data saving. This was nowhere mentioned in documentation, but setting ENGINE=FEDERATED is not setting up your engine as such, it will enable federated connection but the actual data storing engine is defined by the default setting in your MySQL installation. Some might have MyISAM as default, while others could have InnoDB, and probably more are available too. For me the problem was that I defined remote table as MyISAM and local default was InnoDB, so local federated table was not identical to remote table as their engines were different. Remember that federated table won’t save any data locally, so why it falls by this distinction remains a mystery.

Advertisements