I am trying to use the Federated engine of MariaDB 10.1.12 to create tables that are based on tables in a remote database. Following the MariaDB instructions about how to use the FederatedX implementation, in database db1
I create a table as
CREATE TABLE test_table (
id int(20) NOT NULL auto_increment,
name varchar(32) NOT NULL default '',
other int(20) NOT NULL default '0',
PRIMARY KEY (id),
KEY name (name),
KEY other_key (other))
DEFAULT CHARSET=latin1;
Now when I want to see this table in a second database db2
using the Federated engine, I can issue
CREATE TABLE test_table (
id int(20) NOT NULL auto_increment,
name varchar(32) NOT NULL default '',
other int(20) NOT NULL default '0',
PRIMARY KEY (id),
KEY name (name),
KEY other_key (other)
) ENGINE=FEDERATED
DEFAULT CHARSET=latin1
CONNECTION='mysql://user_x:pass_y@localhost/db1/test_table';
All this is copied from the MariaDB documentation and works well. However, if I try to create the table without explicitly duplicating the definition of the table structure - an example given in the same documentation
CREATE TABLE test_table ENGINE=FEDERATED DEFAULT CHARSET=latin1
CONNECTION='mysql://user_x:pass_y@localhost/db1/test_table';
MariaDB responds with an error
ERROR 1113 (42000): A table must have at least 1 column
Am I missing something or is it not possible to use federated tables without specifying the individual columns?
It seems that with the standard installation of MariaDB on Ubuntu 14.04, the old Federated engine is active, not the new FederatedX variant. Only the latter supports auto-discovery of columns. In order to correct this, I took the following steps:
uninstall soname 'ha_federated';
to remove the federated plugin. After a server restart, the correct one is loaded with
install plugin federated soname 'ha_federatedx';
This loads the new FederatedX implementation supporting auto-discovery of columns. To view the installed engines, one can list them with
show engines;
If all is correct, there should be this line in the output:
| FEDERATED | YES | FederatedX pluggable storage engine |