I have a Joomla (PHP) website with an existing hosted MySQL database. I have a Google Cloud SQL Instance with some statistical data in.
I need to query the data across both databases and would like the query to run on the Google Cloud SQL instance.
My research so far has lead me to belive that the best way to do this is to create a federated table inside the Google Cloud SQL database but in attempting to do this I am not getting the results I expect (neither am I getting an error?!)
Joomla MySQL table:
CREATE TABLE test_table (
id INT(20) NOT NULL AUTO_INCREMENT,
name VARCHAR(32) NOT NULL DEFAULT '',
other INT(20) NOT NULL DEFAULT '0',
PRIMARY KEY (id),
INDEX name (name),
INDEX other_key (other)
)
ENGINE=MyISAM
DEFAULT CHARSET=latin1;
Google Cloud SQL:
CREATE TABLE federated_table (
id INT(20) NOT NULL AUTO_INCREMENT,
name VARCHAR(32) NOT NULL DEFAULT '',
other INT(20) NOT NULL DEFAULT '0',
PRIMARY KEY (id),
INDEX name (name),
INDEX other_key (other)
)
ENGINE=FEDERATED
DEFAULT CHARSET=latin1
CONNECTION='mysql://*uid*:*pwd*@*joomla_server_ip*:3306/*database_name*/test_table';
Where
*uid*, *pwd*, *joomla_server_ip* and *database_name*
Are all valid values.
Both statements execute fine with no errors, but after inserting data to *test_table* on Joomla I am unable to see any data in *federated_table* on Google Cloud SQL.
I have tried the federated table creation using both the command line tool (Windows) and using the SQuirrel SQL JDBC client.
Because I am seeing no errors what so ever I'm not sure if the problem is at the Joomla database end or the Google Cloud SQL database end. So any help will be greatly appreciated. I am assuming the problem is with the connection between the two databases, but am open to trying any other theroies that you may throw at me.
EDIT:
I'm now using a different client to connect (MySQL Workbench) and this reports an error when trying to do the same thing
1286 Unknown storage engine 'FEDERATED' 1266 Using storage engine InnoDB for table 'federated_table'
