0

I am trying to include just one table from another MariaDB server (lets call it A) into the database on another (even physical) server (lets call it B). And I came up with the conclusion that SPIDER engine might be a good option. But I can't make it work. Here is what I did so far:

on server A

  • I installed the SPIDER engine even though I don't think it is necessary
  • Port 3306 is visible from outside
  • MariaDB is configured to allow connections from everywhere
  • I've created a user test_user with host set to %
  • I've created the following table in the test database:

    CREATE TABLE sss ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50) ) ENGINE=InnoDB;

on server B

  • I installed the SPIDER engine
  • I've created the following SERVER:

    CREATE SERVER dataNode1 FOREIGN DATA WRAPPER mysql OPTIONS ( HOST 'IP-server-A', DATABASE 'test', USER 'test_user', PASSWORD 'passwd', PORT 3306);

  • Then I created the table as follows:

    CREATE TABLE sss ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50) ) ENGINE=Spider COMMENT='wrapper "mysql", srv "dataNode1", table "sss"';

I manage to connect with test_user from server B to server A:

mysql -h IP-server-A -u test_user -ppasswd

But when I log with root to MariaDB on server B and I try to access the table sss, I get:

#1227 - Access denied; you need (at least one of) the SUPER privilege(s) for this operation

What have I missed? Thank you.

  • Which MariaDB versions are you using on server A and B? I've just tested this with two MariaDB instances running on the same server, and had no problems making it work. Instead of using the wrapper/server, you can also try specifying the connection details directly in the local table (i.e. on server B) with COMMENT 'host "IP-server-A", database "test", user "test_user", password "passwd", port "3306"'; – dbdemon Jul 21 at 20:40
  • What are the grants for test_user on server A? You can connect as this user to server A, but can you also see the table contents? – dbdemon Jul 21 at 20:55
  • @dbdemon The MariaDB on server A is 10.3.15 and the one on server B is 10.2.8. I've also tried putting the connection params in the COMMENT of the table but still same problem.The grants of test_user on server A are ALL PRIVILEGES for the test database. I am managing to do SELECT with this user on the sss table. So, I still have no clue what is going on. – Martin Dimitrov Jul 23 at 6:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.