Can't create federated table in MariaDB using CREATE SERVER - 'Foreign data src error'

Question

I'm using MaraiaDB 5.5.44 on Centos 7.2.

I've used federated tables before across physical servers ... I found the 'CREATE SERVER' mechanism a bit flaky (I could only manage to link tables of the same name) but it worked. In this case I want to set up federated tables in database A that reference tables in database B where both databases on on the same host. I'd like the connection between the tables to work using the Unix socket rather than TCP/IP. The MariaDB server isn't bound to any network interface.

It all works using the CONNECTION string, creating a link here from table 'tmp_table' on the local database to the table of the same name in the 'tmp' database:

> create table tmp_table (t varchar(32)) engine=FEDERATED CONNECTION='mysql://testuser:testpassword@localhost/tmp/tmp_table';

That works perfectly. But when I set up a SERVER for the connection, using the CREATE SERVER statement, I get a 'Foreign data src error':

> create server test_server foreign data wrapper mysql options (user 'testuser', password 'testpassword', host 'localhost', database 'tmp');
> select * from mysql.servers;
+------------------+-----------+----------------------+----------------+------------------+------+--------+---------+-------+
| Server_name | Host | Db | Username | Password | Port | Socket | Wrapper | Owner |
+------------------+-----------+----------------------+----------------+------------------+------+--------+---------+-------+
| test_server | localhost | tmp | testuser | testpassword | 0 | | mysql | |
+------------------+-----------+----------------------+----------------+------------------+------+--------+---------+-------+
> create table tmp_table (t varchar(32)) engine=FEDERATED CONNECTION='test_server/tmp_table';
ERROR 1434 (HY000): Can't create federated table. Foreign data src error: database: 'tmp' username: 'testuser' hostname: 'localhost'

I've tried various permutations of the CONNECTION string - 'test_server/', 'test_server' - but always get the 'Foreign data src error'.

I have been able to get federated tables working before, using a TCP/IP connection to the source database on another host, so I'm wondering if I've made a mistake or if MariaDB simply can't use a SERVER for a connection between local databases over the Unix socket. Can anyone help me with this problem?

Answer 1

Nuts. I'd been looking at the documentation on the MySQL web site for version 5.5 of MySQL.

When I looked up the MariaDB's site and explored its knowledge base pages I discovered that:

a. They've abandoned the FEDERATED engined and have replaced it with a FederatedX engine, even though:

b. The syntax still uses the 'FEDERATED' key word, but also

c. They have added a 'Socket' option to the CREATE SERVER statement.

The 'Socket' column was in the listing of the mysql.server table but I assumed it should just be blank, and it wasn't mentioned in the MySQL documentation.

Anyway, adding a 'socket' option to CREATE SERVER to tell MariaDB to explicitly user the database server's socket has solved the problem.

Weird how the SOCKET wasn't necessary in the long-form CONNECT string though.