List:General Discussion« Previous MessageNext Message »
From:Bill MacAllister Date:August 17 2002 8:50pm
Subject:Replication and tmp tables
View as plain text  
Hello,

I am continuing to have what seems to be intermittent problems with 
replication.  I have attempted to exclude temporary tables from replication 
by using ignore wild specifications in my.cnf on both master and slave. 
For some unknown reason there are times that MySQL still tries to replicate 
a temporary table.

On the slave:

nb3% mysql --version
mysql  Ver 11.16 Distrib 3.23.49, for redhat-linux-gnu (i386)
nb3% cat /etc/my.cnf
[mysqld]
datadir=/usr/local/mysql/var
socket=/usr/local/mysql/tmp/mysql.sock
set-variable    = key_buffer=256M
set-variable    = max_allowed_packet=1M
set-variable    = table_cache=256
set-variable    = sort_buffer=1M
set-variable    = record_buffer=1M
set-variable    = myisam_sort_buffer_size=64M
set-variable    = thread_cache=8
master-host=mysql-master.prideindustries.com
master-user=slaveuser
master-password=slavepassword
master-port=3306
server-id=200
replicate-ignore-table=base_housing.u
replicate-ignore-table=base_housing.wo
replicate-wild-ignore-table=%.tmp%
replicate-wild-ignore-table=%.%tmp
replicate-wild-ignore-table=%.temp%
replicate-wild-ignore-table=%.%temp

[mysqldump]
quick
set-variable    = max_allowed_packet=16M

[client]
socket=/usr/local/mysql/tmp/mysql.sock

nb3% mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 36 to server version: 3.23.51-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show slave status \G
*************************** 1. row ***************************
        Master_Host: mysql-master.prideindustries.com
        Master_User: copydb
        Master_Port: 3306
      Connect_retry: 60
           Log_File: db1-bin.907
                Pos: 14160409
      Slave_Running: No
    Replicate_do_db:
Replicate_ignore_db:
         Last_errno: 1146
         Last_error: error 'Table 'm2mreferral.tmp_travis_househits' 
doesn't exist' on query 'insert into househits_travis select * from 
tmp_travis_househits'
       Skip_counter: 0
1 row in set (0.00 sec)

mysql>

I don't think it should be trying to replicate this table.  Any suggestions 
would be greatly appreciated.

Thanks,

Bill

+-------------------------------------------------------------
| Bill MacAllister, Senior Programmer
| PRIDE Industries
| Phone: 916-788-2402

Thread
Replication and tmp tablesBill MacAllister18 Aug