.::: Create sample multiple dblink MariaDB MySQL to run multiple query Inner Join, Normal Join :::.

1. correlation dblink

https://teguhth.blogspot.com/2024/01/create-link-or-dblink-to-remote-server.html

2. check normal query from remoteserver

select b.NAMA_BARANG,s.NAMA_SUPLIER,p.TANGGAL_PASOK,p.JUMLAH_PASOK from barang b,suplier s,pasok p
where b.KODE_BARANG=p.KODE_BARANG and s.KODE_SUPLIER=p.KODE_SUPLIER;

select @@hostname,b.NAMA_BARANG,s.NAMA_SUPLIER,p.TANGGAL_PASOK,p.JUMLAH_PASOK from barang b,suplier s,pasok p
where b.KODE_BARANG=p.KODE_BARANG and s.KODE_SUPLIER=p.KODE_SUPLIER;
 

3. create dblink for other table in local source

CREATE TABLE dblink_teguh.barang_dblink ENGINE = federated TABLE_TYPE=mariadb CONNECTION 'dblink_maria/barang';
CREATE TABLE dblink_teguh.customer_dblink ENGINE = federated TABLE_TYPE=mariadb CONNECTION 'dblink_maria/customer';
CREATE TABLE dblink_teguh.suplier_dblink ENGINE = federated TABLE_TYPE=mariadb CONNECTION 'dblink_maria/suplier';
CREATE TABLE dblink_teguh.pasok_dblink ENGINE = federated TABLE_TYPE=mariadb CONNECTION 'dblink_maria/pasok';
CREATE TABLE dblink_teguh.pembelian_dblink ENGINE = federated TABLE_TYPE=mariadb CONNECTION 'dblink_maria/pembelian';
 

4. adjust query and run in local source

---

select b.NAMA_BARANG,s.NAMA_SUPLIER,p.TANGGAL_PASOK,p.JUMLAH_PASOK from barang_dblink b,suplier_dblink s,pasok_dblink p
where b.KODE_BARANG=p.KODE_BARANG and s.KODE_SUPLIER=p.KODE_SUPLIER;

select @@hostname,b.NAMA_BARANG,s.NAMA_SUPLIER,p.TANGGAL_PASOK,p.JUMLAH_PASOK from barang_dblink b,suplier_dblink s,pasok_dblink p
where b.KODE_BARANG=p.KODE_BARANG and s.KODE_SUPLIER=p.KODE_SUPLIER;

5. testing delete for from remoteserver

 delete from pasok
 where KODE_BARANG='ELK-01' and KODE_SUPLIER='EJ-01';

select @@hostname,b.NAMA_BARANG,s.NAMA_SUPLIER,p.TANGGAL_PASOK,p.JUMLAH_PASOK from barang b,suplier s,pasok p
where b.KODE_BARANG=p.KODE_BARANG and s.KODE_SUPLIER=p.KODE_SUPLIER;
 

6. check again in local source

select @@hostname,b.NAMA_BARANG,s.NAMA_SUPLIER,p.TANGGAL_PASOK,p.JUMLAH_PASOK from barang_dblink b,suplier_dblink s,pasok_dblink p
where b.KODE_BARANG=p.KODE_BARANG and s.KODE_SUPLIER=p.KODE_SUPLIER;
 

7. try from local server

 delete from pasok_dblink
 where KODE_BARANG='ELK-01' and KODE_SUPLIER='EJ-02';
 
 MariaDB [dblink_teguh]>  delete from pasok_dblink
    ->  where KODE_BARANG='ELK-01' and KODE_SUPLIER='EJ-02';
ERROR 1296 (HY000): Got error 10000 'Error on remote system: 1142: DELETE command denied to user 'admin'@'10.10.10.8' for table `teguhth_maria_dwh`.`pasok`' from FEDERATED
MariaDB [dblink_teguh]>