如何在 Google Cloud SQL 中创建联合表

2022-01-06 00:00:00 sql php mysql google-cloud-sql joomla

我有一个 Joomla (PHP) 网站,其中包含一个现有的托管 MySQL 数据库.我有一个包含一些统计数据的 Google Cloud SQL 实例.

I have a Joomla (PHP) website with an existing hosted MySQL database. I have a Google Cloud SQL Instance with some statistical data in.

我需要跨两个数据库查询数据,并希望查询在 Google Cloud SQL 实例上运行.

I need to query the data across both databases and would like the query to run on the Google Cloud SQL instance.

到目前为止,我的研究使我相信最好的方法是在 Google Cloud SQL 数据库中创建一个联合表,但在尝试这样做时,我没有得到我期望的结果(我也没有得到错误?!)

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 表:

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;

谷歌云 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';

哪里

*uid*, *pwd*, *joomla_server_ip* and *database_name* 

都是有效值.

这两个语句都执行得很好,没有错误,但是在将数据插入到 Joomla 上的 *test_table* 后,我无法在 Google Cloud SQL 上的 *federated_table* 中看到任何数据.

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.

我已尝试使用命令行工具 (Windows) 和 SQuirrel SQL JDBC 客户端创建联合表.

I have tried the federated table creation using both the command line tool (Windows) and using the SQuirrel SQL JDBC client.

因为我没有看到任何错误,所以我不确定问题出在 Joomla 数据库端还是 Google Cloud SQL 数据库端.所以任何帮助将不胜感激.我假设问题出在两个数据库之间的连接上,但我愿意尝试您可能向我提出的任何其他理论.

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.

我现在使用不同的客户端进行连接 (MySQL Workbench) 并且在尝试执行相同操作时报告错误

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'

推荐答案

在提出这个问题后不久,Google 将 MySQL Wire Protocol 添加到 Google Cloud SQL.

Shortly after asking this question Google added the MySQL Wire Protocol to Google Cloud SQL.

http://googlecloudplatform.blogspot.co.uk/2013/10/google-cloud-sql-now-accessible-from-any-application-anywhere.html

现在可以以正常方式创建联合表.

It is now possible to create Federated tables in the normal way.

相关文章