mysql proxy r/w replication and temporary tables

I am doing master/slave replication on MySQL5.1 and r/w split with mysql proxy 0.8.x

It works fine except with temporary tables. MySQL throws an error, that the temporary table is not existent.

This is the query log for the master server:

        CREATE TEMPORARY TABLE IF NOT EXISTS sh ( ad_id MEDIUMINT( 8 ) UNSIGNED NOT NULL, score float , INDEX ( `ad_id` ), INDEX ( `score` )) ENGINE = MEMORY

INSERT INTO sh
                            SELECT  cl.ID, 1
                            FROM    classifieds cl
                            WHERE   cl.advertiser_id = '40179'

This is the query log for the slave:

CREATE TEMPORARY TABLE IF NOT EXISTS sh ( ad_id MEDIUMINT( 8 ) UNSIGNED NOT NULL, score float , INDEX ( `ad_id` ), INDEX ( `score` )) ENGINE = MEMORY

This is the mysql errror message:

Occured during executing INSERT INTO sh SELECT cl.ID, 1 FROM classifieds cl WHERE cl.advertiser_id = '40179' statement
Error: 1146 Table 'dbname.sh' doesn't exist

If I query the master directly (change php db connection to master instead to mysql-proxy), it works without problems.

I am using this mysql proxy config:

[mysql-proxy]
daemon = true
pid-file = /home/mysqladm/mysql-proxy.pid
log-file = /home/mysqladm/mysql-proxy.log
log-level = debug
proxy-address = 192.168.0.109:3307
proxy-backend-addresses = 192.168.0.108:3306
p开发者_Python百科roxy-read-only-backend-addresses = 192.168.0.109
proxy-lua-script = /usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua

Has anybody an idea on how to fix that? Thank you for any help!

// edit next day

I believe I know why this isn't working:

MySQL Proxy sends the create tmp and insert select statements to the master which replicates the commands correctly to the slave, then in the next step the select is sent to the slave. Unfortunatelly in MySQL the tmp table is only valid for the connection which issued it, therefore the tmp table created by the replication is not valid for the second connection issued by mysql proxy on slave.

I am now trying to solve this by changing my application and issuing connects with tmp tables directly to the master.

Please let me know if you believe that there is a better solution.

Yes, that's exactly the problem. This is one of the pitfalls of splitting read queries with MySQL Proxy instead of having the application layer make that determination for itself.

It sounds like what you're doing is putting that determination back into the application layer, but for these tables only. That's a fine workaround. If you find yourself making more exceptions that require pointing a dbh directly at a database, consider abstracting that code and giving your application a way to request a dbh for a particular functionality. In this case, you'd like your code to ask a library "give me a dbh that I can perform TEMPORARY TABLE queries on."

Another way would be to give all TEMPORARY TABLEs recognizable names (maybe make them all start with "tmp_") which would give Proxy a fighting chance to send SELECTs on them to the right place.