Update queries on Federated Tables
Hi,
I have a following setup
Server A <-->Server B (Circular or master-master replication)
Server B -->Server C (Master Slave Replication)
Server C is a slave and also contains some tables using Federated Engine and connects to a remote server (Server D) which has the same tables and structure as the replicating servers.
This setup is for all our servers at different data centers. The purpose is to assimilate all the data at one server (D) from all the data centers.
On the Federated tables the insert queries work fine, however the update queries are not updating the remote server D.
I tried running an update query from Server C on the federated table, and get this response -
Query OK, 1 row affected (0.12 sec)
Rows matched: 1 Changed: 1 Warnings: 0
however the rows are not updated.
The table structure on which query is being run is
On Server C(Federated Table)
`entity_option` (
`entity_id` mediumint(8) unsigned NOT NULL,
`option_id` smallint(5) unsigned NOT NULL,
`value` text NOT NULL,
`oldValue` text,
`cluster_id` smallint(5) unsigned default NULL,
`date_created` datetime default NULL,
`date_modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
KEY `table_index` (`entity_id`,`option_id`),
KEY `option_id_index` (`option_id`)
) ENGINE=FEDERATED DEFAULT CHARSET=latin1 CONNECTION='mysql://admin:password@remoteserver.com:3306/remote_db/entity_option'
and on the remote server the structure is -
`entity_option` (
`entity_id` mediumint(8) unsigned NOT NULL,
`option_id` smallint(5) unsigned NOT NULL,
`value` text NOT NULL,
`oldValue` text,
`cluster_id` smallint(5) unsigned default NULL,
`date_created` datetime default NULL,
`date_modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
KEY `table_index` (`entity_id`,`option_id`),
KEY `option_id_index` (`option_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
Appreciate if anybody can point me where I am going wrong.
Thanks