MySQL launches + replication with multiple databases

Question

I am running a couple of databases on MySQL 5.0.45 and trying to synchronize my legacy database with a revised schema, so I can work side by side. I do this by adding triggers to the new database, but I'm having replication issues. My setup is as follows.

Server "master"

• The legacydb database is replicated to the slave server.
• The newdb database has triggers that update legacydb and are not replicated.

Slave Server

• Database "legacydb"

My updates to "newdb" run fine and trigger my triggers. They update "legacydb" on the "master" server. However, the changes are not replicated to subordinates. MySQL docs say that for simplicity, replication considers the current database context (for example, "SELECT DATABASE();" ) when deciding which queries to replicate rather than look at the query product. My trigger is fired from the context of the "newdb" database, so replication ignores updates.

I tried moving the update statement to a stored procedure in "legacydb". This works fine (i.e., data is replicated to subordinates) when I connect to "master" and start manually "USE newdb; CALL legacydb.do_update('Foobar', 1, 2, 3, 4);" . However, when this procedure is called from a trigger, it is not replicated.

So far, my reflection on how to fix this has been one of the following.

• Force the trigger to install the new current database. That would be easiest, but I don't think it is possible. This is what I was hoping to achieve with a stored procedure.

• Replication of both databases and the presence of triggers in both the master and slave. That would be possible, but a pain in tuning.

• Make replication select all the changes in "legacydb", regardless of the current database context.

• If replication is performed at a very high level, it will never even see any updates triggered by my trigger, in which case no number of hackers will achieve what I want.

Any help on how to achieve this would be greatly appreciated.

Answer 1

This may have something to do with this:

A stored function receives table locks before execution to avoid inconsistencies in the binary log due to a mismatch in the order in which statements are executed and when they appear in the log. The entries calling the function are written, not the statements executed inside the function. Therefore, stored functions that update the same base tables are not executed in parallel.

In contrast, stored procedures do not receive table-level locks. All statements executed in stored procedures are written to the binary log.

In addition, there is a list of trigger issues: http://dev.mysql.com/doc/refman/5.0/en/routine-restrictions.html