Databases
StackOverflow2013
Postsdbo
POmysql proxy r/w replication and temporary tables
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POmysql proxy r/w replication and temporary tables
    primarykey
    data
    text
    <p>I am doing master/slave replication on MySQL5.1 and r/w split with mysql proxy 0.8.x</p> <p>It works fine except with temporary tables. MySQL throws an error, that the temporary table is not existent.</p> <p>This is the query log for the master server:</p> <pre><code> 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' </code></pre> <p>This is the query log for the slave:</p> <pre><code>CREATE TEMPORARY TABLE IF NOT EXISTS sh ( ad_id MEDIUMINT( 8 ) UNSIGNED NOT NULL, score float , INDEX ( `ad_id` ), INDEX ( `score` )) ENGINE = MEMORY </code></pre> <p>This is the mysql errror message:</p> <pre><code>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 </code></pre> <p>If I query the master directly (change php db connection to master instead to mysql-proxy), it works without problems. </p> <p>I am using this mysql proxy config:</p> <pre><code>[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 proxy-read-only-backend-addresses = 192.168.0.109 proxy-lua-script = /usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua </code></pre> <p>Has anybody an idea on how to fix that? Thank you for any help!</p> <p>// edit next day</p> <p>I believe I know why this isn't working:</p> <p>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.</p> <p>I am now trying to solve this by changing my application and issuing connects with tmp tables directly to the master. </p> <p>Please let me know if you believe that there is a better solution. </p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USOMG Ponies
    1. USmerlin
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POmysql proxy r/w replication and temporary tables
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POmysql proxy r/w replication and temporary tables
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POmysql proxy r/w replication and temporary tables
      1. USaurora
      1. VTFavorite
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload