Sample Header Ad - 728x90

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