The problem is still occurring. As I said in my earlier message, it
tends to (but is not limited to) the beginning of a binary log file.
Here is an example:
mysql> show slave status;
+--------------+-------------+-------------+---------------+---------------+-----+---------------+-----------------+---------------------+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+
| Master_Host | Master_User | Master_Port | Connect_retry |
Log_File | Pos | Slave_Running | Replicate_do_db |
Replicate_ignore_db | Last_errno |
Last_error
| Skip_counter |
+--------------+-------------+-------------+---------------+---------------+-----+---------------+-----------------+---------------------+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+
| db1.tias.com | slave | 3306 | 60 |
spode-bin.009 | 73 | No |
| | 1146 | error 'Table 'curioscape.avstemp'
doesn't exist' on query '# /etc/httpd/cgi-bin/search.curioscape.fcgi
(19278)
INSERT IGNORE INTO avstemp SET siteid='9241', payrate=0,
relevance='0.007758'
' | 0 |
+--------------+-------------+-------------+---------------+---------------+-----+---------------+-----------------+---------------------+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+
1 row in set (0.01 sec)
Note that avstemp is a temporary table (CREATE TEMPORARY TABLE) and that
the failures are happening on temporary tables.
So I do a:
mysql> SET SQL_SLAVE_SKIP_COUNTER=1; SLAVE START;
and get:
mysql> show slave status;
+--------------+-------------+-------------+---------------+---------------+-----+---------------+-----------------+---------------------+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+
| Master_Host | Master_User | Master_Port | Connect_retry |
Log_File | Pos | Slave_Running | Replicate_do_db |
Replicate_ignore_db | Last_errno |
Last_error
| Skip_counter |
+--------------+-------------+-------------+---------------+---------------+-----+---------------+-----------------+---------------------+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+
| db1.tias.com | slave | 3306 | 60 |
spode-bin.009 | 238 | No |
| | 1146 | error 'Table 'curioscape.avstemp'
doesn't exist' on query '# /etc/httpd/cgi-bin/search.curioscape.fcgi
(19278)
INSERT IGNORE INTO avstemp SET siteid='59588', payrate=0,
relevance='0.007758'
' | 0 |
+--------------+-------------+-------------+---------------+---------------+-----+---------------+-----------------+---------------------+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+
1 row in set (0.00 sec)
I've been untangling it by skipping over the errors. Since they are
temporary tables and no client will ever seem them (because the client
that created them was talking to a different server), the contents are
really irrelevant.
What can I do to stop this from happening?
Is there more information I can provide to diagnose this? I could
provide the error logs. Or even the binlogs (although they are 1GB
each).
Mike Wexler wrote:
>
> Jeremy Zawodny wrote:
> >
> > On Sun, Sep 02, 2001 at 11:40:08AM -0700, Mike Wexler wrote:
> > > I have a slave server and a master server both running 3.23.38.
> > >
> > > Every once in a while the slave stops updating:
> > >
> > > | db1.tias.com | slave | 3306 | 60 |
> > > spode-bin.005 | 912992502 | No |
> > > | | 1158 | Slave: query 'drop table
> > > tias.avtemp' partially completed on the master and was aborted. There is
> > > a chance that your master is inconsistent at this point. If you are sure
> > > that your master is ok, run this query manually on the slave and then
> > > restart the slave with SET SQL_SLAVE_SKIP_COUNTER=1; SLAVE START; |
> > > 0 |
> >
> > [snip]
> >
> > > Why is this happening and what can be done to prevent it from happening.
> >
> > Hard to say. Was there actually a problem on the master?
>
> Not that I know of.
>
> > Did the
> > slave work fine once you set the skip counter?
>
> In this case yes. At least for a while but inevitably this either
> happens again. Or I get an error that insert or drop operations are
> being applied to a (temporary) table that doesn't exist. The latter
> error usuallly occurs at the beginning of a binlog and usually occurs
> multiple times.
>
> >
> > Jeremy
> > --
> > Jeremy D. Zawodny, <[EMAIL PROTECTED]>
> > Technical Yahoo - Yahoo Finance
> > Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936
> >
> > MySQL 3.23.41-max: up 3 days, processed 33,533,446 queries (109/sec. avg)
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
