CODEWITHSUNDEEP
mariadbfederated-table
Martin Dirichs
Martin Dirichs

Reputation: 113

How to create a federated table in MariaDB without specifying columns

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?

Upvotes: 3

Views: 4605

Answers (1)

Martin Dirichs
Martin Dirichs

Reputation: 113

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 |

Upvotes: 3

Related Questions