We have a production database (MySQL 8.0) that has a federated table in it. The remote server had an IP address update, and whenever a client opens this DB, it takes forever (~5 minutes) since table information can not be retrieved... Our fix is to remove the table as it is no longer needed. Unfortunately all the following have failed with forever running queries.
Looking at the process list they are in a state of "*Waiting for table metadata lock*":

1. DROP TABLE db.myfedtable;
2. ALTER TABLE db.myfedtable CONNECTION = 'valid connection string for updated IP';
3. ALTER TABLE db.myfedtable ENGINE = InnoDB;

We have also verified no processes or queries are trying to run against this table. We have tried restarting MySQL since the server connection has been lost. Our next steps is to try and setup a firewall to re-route traffic to the new server IP just so we can run a drop statement. Any ideas would be helpful here.

I have no expirence with FEDERATED tables in MySQL. I have database with FEDERATED tables defined and I'm wonder if this tables are used in current env (I have no access to application/developers to ask/check). The mysql.servers table (https://mariadb.com/kb/en/mysqlservers-table/) is empty. I wonder if connection info stored there is ephemeral or persitent after first connection to remote server/s. My question is: if mysql.servers table is empty can I "safely" assume that this tables are not used by apps?

Scenario: I need to sync a table in a remote server with the table in my production server. The problem being that the table structures are not same. I have looked into the concepts of federated table only to find out that the two tables are exactly same, structure wise, which is not the case here. So basically, is there any way I can sync only few columns,the matching columns,of those tables?

On IBM Db2 v11.5.8.0 on Linux x86_64 I have two local databases DB01 and DB02. Both databases individually are configured to use LDAP authentication. Now I am required to setup a federated access from DB01 to DB02. Looking at CREATE SERVER and CREATE USER MAPPING there is always definition of userid and password. I don't know the userid and password for our users (at least I don't know passwords). Is it possible to define federated access without specifying userid/password at CREATE SERVER and/or CREATE USER MAPPING? What I would like is that database: - passes authentication (userid/password) from DB01 to DB02 or - database does not perform authentication for federated users, because authentication was already made when user connected to DB01 or - something similar, but without me required to define userid/password for every user in CREATE SERVER or CREATE USER MAPPING command. Regards

For testing, I've set up a XAMPP portable 8.1.12 with 10.4.27-MariaDB and enabled the FederatedX engine using this trick . When I now set up a SERVER that is not existing and a federated table that is "using" that server, running a SELECT against that table fails as it should, but INSERT behaves as if it has worked. Why is that? Example: CREATE SERVER nonexistentserver FOREIGN DATA WRAPPER nonexistentserverwrapper OPTIONS ( HOST '127.0.0.1', DATABASE 'thereIsNoServerSoThereIsNoDatabase', USER 'andNoUser', PASSWORD 'AndNoPassword!', PORT 12345 ); CREATE TABLE mytable ( theLine INT NOT NULL AUTO_INCREMENT PRIMARY KEY, theContent VARCHAR(255) NOT NULL ) ENGINE=FEDERATED CONNECTION='nonexistentserver/mytable'; SELECT behaves as I would expect: MariaDB [testdb]> SELECT * FROM mytable; ERROR 1296 (HY000): Got error 10000 'Error on remote system: 0: ' from FEDERATED But INSERT does not: MariaDB [testdb]> INSERT INTO mytable VALUES (NULL, "Hey!"); Query OK, 1 row affected (0.005 sec) Is that a bug or is it supposed to do that? ---------- **Additum 1:** When I do not use install plugin federated soname 'ha_federatedx'; but remove the x first (inspiration from this answer plus comment ), thus use *the old federated* but federatedX, the INSERT gives the expected error.

I believe this is a firewall rule problem. Therefore, I believe that the issue is finding out what the public IP address of the RDS is. I don't see that anywhere on the RDS interface. I have two AWS RDS MariaDB databases. I can connect to both without a problem. From the command line, I type: mysql -u me -p db1.cet4jwf1l8xu.us-east-2.rds.amazonaws.com I connect no problem. I can also connect to db2 without a problem. In db2, I want to remotely access a table from db1. I have done this with both Federated and Connect. I get the same error: Can't connect to server, timed out. The syntax for each is pretty much the same: create table testtable engine=federated connection='mysql://me:test@db1.cet4jwf1l8xu.us-east-2.rds.amazonaws.com/mydb/demo'; It sits for a while and then comes up "can't connect." I can switch to engine=connect and get the same timeout error. In the incoming security rules for db1, I have port 3306 open to everywhere. So, I assume it shouldn't be blocking it. Inside db1, user me is set to host '%', which I believe means 'anywhere'. So, I don't think that is the problem either. I'm not sure what else could keep it from connecting when I can connect from a basic CLI on multiple other servers.

I am looking for a solution to the problem described below. There are two DB2 instances with two authorization IDs on the same server and can be accessed using the same user User1. **Example:** To access Inst1DB, I use the following commands: [User1 ~]$ . /db2/authID1db2/home/authID1db2/sqllib/db2profile [User1 ~]$ db2 connect to Inst1DB To access Inst2DB, run the following commands: [User1 ~]$ . /db2/auth2db2/home/auth2db2/sqllib/db2profile [User1 ~]$ db2 connect to Inst2DB Note that one needs to terminate the current DB2 session before connecting to another DB instance. To terminate DB session, run the following command: [User1 ~]$ db2 terminate **Issue:** I want to join two tables, one of which belongs to INST1DB and the other to INST2DB, while connected to INST1DB. [User1 ~]$ . /db2/authID1db2/home/authID1db2/sqllib/db2profile [User1 ~]$ db2 connect to Inst1DB [User1 ~]$ db2 "Select * from Inst1DB.schema1.table1 T1 , Inst2DB.schema2.table2 T2 where T1.column1 = T2.column1. Curerntly I am getting below error: >SQL0204N "Inst2DB.schema2.table2" is an undefined name. SQLSTATE=42704. **Query:** Can anyone suggest to me how to connect the two instances and perform a join on these tables?

i have 10 mysql servers and one central mysql server that's keeping track of some numerical indexes. Idea is that each time one of those 10 servers request and id, that id is marked as used and no other server can use it. For that reason i have implemented a Federated table system which works almost perfectly for me - each server is writing changes into local table and it's automatically reflected on the table of the main server. Like that each of those 10 servers can see what's the next available id without even knowing about each other. Now, the problem is that on the central server, to Federated, "target" table i have added one additional column. It turns out i have to replicate that same change on all those 10 servers manually! My expectation was that once i add new column on "federated target" table, all dependent tables on those 10 servers will get updated automatically to match their "parent" structure like they did once created for the first time. Is there a way to enforce this in MySql? Basically i wanna have 1 table and structural as well as data changes of that table to be reflected automatically on all those 10 servers.

We are planning to create a standalone MySQL server specifically for Federated tables that points to 4 DB instances. Sample table: federated_db1_users
federated_db1_transactions
federated_db2_users
federated_db2_transactions
Is this kind of setup feasible as long term solution? What we are aiming to do is to centralize all of our data set into 1 data source for our reporting tools.

For a home air quality monitoring project I am building, I am collecting sensor readings and storing them on my Synology server, which includes a MariaDB instance. The Synology is great for managing things like RAID arrays and automated backups, but it doen't have a particularly impressive CPU. I also use it for all sorts of other purposes, so I don't want to burden the CPU too much. I am also building a web server (actually a Shiny Server ) on an Ubuntu machine to serve a web page of some analytics on top of the data. I want part of these analytics to be calculated on demand by a stored proc. However, I want the stored proc to use the CPU resources of the Ubuntu machine. But I want the data to be managed and backup up by the Synology. So my plan is to have a DB in the Synology for storing the data, and have another DB with federated tables in the Ubuntu. The stored proc runs on the Ubuntu. The stored proc only reads data from the Synology, transforms it, and returns it to the caller. It doesn't write anything back to the Synology. Does this seems like a good idea? How would you do it?

When i test select * from myFederatedTable (for test there is 50k rows) it take 0.3732 seconds now I have two federated tables with data < 20000 in both tables, and have query like : SELECT i.id_news, i.id_news_type, i.id_agency, i.add_date, i.update_date, j.title, k.image_name_latin, m.folder_path FROM tbl i INNER JOIN tbl_languages j on i.id_news = j.id_news and j.lang = 'en' INNER JOIN federated_Table1 k on i.id_image_groups = k.id_image_groups INNER JOIN federated_table2 m on k.id_folder_groups = m.id_folder_groups ORDER BY i.update_date DESC LIMIT 14 It takes 16 seconds Without joins to federated tables SELECT i.id_news, i.id_news_type, i.id_agency, i.add_date, i.update_date, j.title FROM tbl i INNER JOIN tbl_languages j on i.id_news = j.id_news and j.lang = 'en' ORDER BY i.update_date DESC LIMIT 14 0.0496 seconds. I have few questions - 1.Is there any way to optimize it ? - 2.when i select from federated table (really i select from main database?) or when i join to federated table I really join to main database? - 3.Task is copy data from one database tables to another database tables. Is there best way to replicate this 2 tables for quick selection ?

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?

Let say I have DB1, a database in PostgreSQL, and DB2, a database in Oracle. How can I make a query joining these databases? Is this even possible?

I have two MySQL(5.6) databases D1 and D2 on two different machines. There is a table T1 on D1 and there are two tables T2A and T2B on D2. I want to update T2B whenever T1 is updated. T2A is a federated table and T1 is base table of it. There is an "After Update" trigger on D2 which updates T2B whenever T2A is updated. When there is an update on T2A, T2B table is updated by trigger. When there is an update on T1, T2A table is updated by federation **but the trigger to update T2B as well is not triggered**. So when there is an update on T1, T2B table is not updated automatically, only T2A is. How to solve this?

I have a client that really (really, REALLY!) wants to use a standard database server through a specific hosting company. That company offers vanilla installs of PostgreSQl and MySQL only. The client has several running instances of web application software. They want those applications to be able to share a certain portion of their data. That data is kept in a small handful of tables on each instance's database. I immediately thought of some type of data replication or synchronization. Master-Slave or Master-Master replication would work fine. MySQL federation would probably work too. But the problem comes back again to the hosting company. *We won't have superuser access on the OS or the DB server, nor will we be able to modify the DB server's configuration files.* **Question:** Without superuser access or access to the DB server's configuration files, is it possible to set up some type of data synchronization? Everything I've read so far suggests that this is impossible. But, perhaps there's a method I've overlooked? **Note:** If it helps, each app's database will be hosted on the same DB server.

For the first time ever could not find a solution on internet! The problem is when I'm trying to (on local server): CREATE TABLE DB_1.testView ENGINE=FEDERATED CONNECTION='remote_server'; I'm getting an error: >\#1939 - Engine FEDERATED failed to discover table \DB_1\.\testView\ with 'CREATE ALGORITHM=UNDEFINED DEFINER=\root\@\localhost\ SQL SECURITY DEFINER VIEW \testView\ AS select 'test_line' AS \test_line\ CONNECTION='remote_server'' Obviously you can see that VIEW is very simple for testing purposes (executed on remote server): CREATE VIEW DB_1.testView AS SELECT "test_line" The user in remote_server has all privileges. View was tested with different options and content. Federated tables work fine. Server version on both machines: 10.0.29-MariaDB-0+deb8u1 Am I missing something?

I want to achieve federation using following commands through JDBC. connect to SIGCAP_M user db2sigcp using db2sigcp ; drop wrapper drda; create wrapper drda ; UNCATALOG NODE XYZ; CATALOG TCPIP NODE XYZ REMOTE 10.XXX.XX.XX SERVER 446 ostype OS400; UNCATALOG DB as4xyz; CATALOG DCS DATABASE as4xyz as as4xyz; CATALOG DB as4xyz as as4xyz AT NODE xyz authentication DCS ; attach to xyz user "kasparov" using "kasparov123"; drop server xyz; create server xyz type DB2/400 version 7 wrapper DRDA authorization db2ngicp PASSWORD db2ngicp options( add dbname 'S845F85V', DB2_TWO_PHASE_COMMIT 'Y' ) ; CREATE USER MAPPING FOR db2sigcp SERVER EQUATION OPTIONS ( ADD REMOTE_AUTHID 'db2ngicp',ADD REMOTE_PASSWORD 'db2ngicp') ; CREATE nickname E_NEPF FOR as4xyz.KFILLV.NEPF; As per IBM documentation - https://www.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.sql.rtn.doc/doc/r0012547.html SYSPROC.ADMIN_CMD doesn't support any of the above commands. How can I make this work?

This is the scenario. Have two MySQL servers (S1, S2) on different machines, with a database on each (DB1, DB2). I have a table (T2) on DB2 that needs to "fetch" rows from another table (T1) on DB1. I have created a temporary table (base on DB1 and federated on DB2), so when I insert a row on T1 which complies with some requirements I copy to my DB1 temporary table. That's OK. With the federated table on DB2 I get the row I need for T2. I presumed that with a trigger on the federated temporary table of DB2 it's possible to insert that row to T2, but the trigger never fires. Any suggestions?