Can't create federated table in MariaDB using CREATE SERVER - 'Foreign data src error'
1
vote
1
answer
2466
views
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?
Asked by alex8275
(11 rep)
Jul 6, 2018, 02:26 AM
Last activity: Jul 8, 2018, 03:36 AM
Last activity: Jul 8, 2018, 03:36 AM