5

I have two tables named user and user_posts.These two tables are in different amazone rds instance. I wants to join these two tables. Is it possible to write mysql join using laravel framework? Thanks in advance!

reshma
  • 672
  • 8
  • 24

1 Answers1

6

This isn't possible with RDS.

MySQL has a FEDERATED storage engine that allows one server to access tables on another server, but it is disabled in RDS for MySQL.

The Federated Storage Engine is currently not supported by Amazon RDS for MySQL.

— http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_MySQL.html

Clarificarion:

When using the FEDERATED storage engine to allow tables with data on one server to appear to exist on another server -- thus allowing the data to be queried from a server that does not actually have a persistent copy of the data -- only the server that does not have the actual data needs to actually support the FEDERATED engine.

This means that while an RDS instance can't access the data from another server using FEDERATED, a non-RDS MySQL server can access data on an RDS instance using a FEDERATED table configured on the non-RDS server, with the table configured to retrieve data from RDS.

This is because -- from the perspective of the server with the actual data -- the connection from the server using FEDERATED looks like an ordinary client connection. The data is retrieved using normal queries, so FEDERATED support does not need to be available on that side of the link.

This means that a non-RDS server running MySQL can access the data on one or more RDS servers, using FEDERATED tables.

I use this routinely for generating reports that join tables on two (and in one case, three) different RDS instances.

FEDERATED tables do have limitations -- they appear to the server that is fetching the remote data as being very similar to MyISAM tables, in the sense that they do not support transacrions and any query that would result in a full table scan will actually fetch the entire remote table for each query, which can obvioisly get out of hand... so they have to be used with diligence and discretion.

This may not be useful for the scenario described, since it would require the addition of a third server, but it is a solution that is not completely ruled out when RDS makes up a portion of the database infrastructure. The information above is also true when using RDS/MariaDB and RDS/Aurora for MySQL.

Michael - sqlbot
  • 169,571
  • 25
  • 353
  • 427
  • Thanks lot for your reply – reshma Mar 20 '17 at 09:38
  • @RPillai my pleasure. I've extended the answer to clarify that while `FEDERATED` won't help you with just 2 RDS instances, it's not *entirely* incompatible with RDS. – Michael - sqlbot Mar 20 '17 at 10:26
  • i have one more doubt. Is it possible to connect to different rds instances from one laravel application? – reshma Mar 28 '17 at 06:29
  • i just tried editing variables in my .env file. DB_HOST_1=rds_url_1 DB_DATABASE_1=db1 DB_HOST_2=rds_url_2 DB_DATABASE_2=db2 And it got connected . My problem sloved :)Thanks – reshma Mar 29 '17 at 10:41
  • @Michael-sqlbot so that mean its imposible to have two rds mysql servers and connect them with each other with DB link or anything else, only way is to have mysql server which sits somewhere but not on AWS? – Andrey Sep 19 '18 at 22:18
  • @Andrey the server could be on EC2, or outside AWS, but not RDS. – Michael - sqlbot Sep 20 '18 at 00:21
  • @Michael-sqlbot thanx, I guess i need some different solution – Andrey Sep 20 '18 at 00:28
  • @Michael-sqlbot : Actually I want to connect 2 mysql RDS from a mediator EC2 via federated engine table but getting error like "ERROR 1429 (HY000): Unable to connect to foreign data source: Can't connect to MySQL server on 'user-data-cluster.cluster-cgqf", while trying to fetch data remotely from same EC2 machine normally with same user/host/port then getting data successfully. Can you please help here as you are already doing it. Where I am doing mistake. Note: the user is fetching data in federated table have only SELECT permissions. – Zafar Malik Dec 15 '22 at 10:58
  • @ZafarMalik it's difficult to speculate what the problem is, because this error message appears to be truncated. Error 1429 is a generic federation error code. You might try turning on all possible logging on the RDS side and see if you find any evidence of the connection attempt. On the EC2 side, you might find wireshark to be helpful in capturing something about the interaction. – Michael - sqlbot Dec 15 '22 at 21:04
  • @Michael-sqlbot When I am trying to fetch data via following remote connection then it is running successfully and providing output. : mysql -h customer-data-cluster.cluster-dfdpdf3dfddad.us-east-2.rds.amazonaws.com -P 3307 -u federated_admin -pMy$password customer -e"select * from employee limit 3" – Zafar Malik Dec 16 '22 at 15:18
  • when I am creating federated table as per following then also no issue : CREATE TABLE testdb.test_table ( `ID` int(11) NOT NULL, `BiologicalSex` varchar(45) NOT NULL, `SortOrder` int(11) NOT NULL DEFAULT '1', `IsActive` tinyint(1) NOT NULL DEFAULT '1', PRIMARY KEY (`ID`) ) ENGINE=FEDERATED CONNECTION='mysql://federated_admin:My$password@customer-data-cluster.cluster-dfdpdf3dfddad.us-east-2.rds.amazonaws.com:3307/customer/employee'; – Zafar Malik Dec 16 '22 at 15:19
  • But when I am trying to fetch data from federated table then getting this connection error- mysql> select * from testdb.test_table; ERROR 1429 (HY000): Unable to connect to foreign data source: Can't connect to MySQL server on 'customer-data-cluster.cluster-dfdp mysql> – Zafar Malik Dec 16 '22 at 15:20
  • Also when I am trying to fetch data via federated table from EC2 source then it is working fine but not working for RDS. Now I want to know if there is any setting/parameter in RDS which can block the connection from federated table. – Zafar Malik Dec 16 '22 at 15:20