1

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?

1 Answer 1

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.