List:General Discussion« Previous MessageNext Message »
From:David Nedved Date:January 13 2003 7:16pm
Subject:replication - queries out of order or dropped?
View as plain text  
Hi All,

Trying my best to maintain some production servers running
mysql-max-3.23.53a-pc-linux-gnu-i686 with one master and one slave.

We keep having issues where we create a new table on the master, and
a short while later the slave will crash because it starts trying to
perform queries which insert into the new table, but it looks as if the
query to create the table hasn't been executed yet (ie the table doesn't
yet exist on the slave).

I've got three pairs of master/slave servers, and this is happenning
on all of them.  My experience is that I can brute-force something such
as setting the skip count to some positive integer, etc. etc., but that's
only a hack, and doesn't get around the big problem here which is that
somehow the queries are seemingly getting either skipped or out-of-order.

Any help here?  My management is getting quite upset about this.  We're
not using the slaves for anything at present, but they are meant to be
a disaster recovery option.  Right now they're worthless...

Thanks in advance,

David Nedved

here is the output of "show slave status" on a currently broken slave:
(sure enough the table exists on the master, but not on the slave)

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 47773 to server version: 3.23.53a-max-log

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

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 |
+-------------+-------------+-------------+---------------+--------------+-----------+---------------+-----------------+---------------------+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+
| 66.83.39.9  | repl        | 3306        | 60            | customer.024 | 146873601 | No 
          |                 | mysql               | 1          | error 'Can't create/write
to file './www_heliumandballoons_com_wa/ACCESS.frm' (Errcode: 2)' on query 'CREATE TABLE
ACCESS (
  ID int(1) NOT NULL default '0',
  CATALOG tinyint(1) default NULL,
  DISPLAY_CATALOG tinyint(1) default NULL,
  ECOMMERCE tinyint(1) default NULL,
  DISPLAY_ECOMMERCE tinyint(1) default NULL,
  RESIDENTIAL tinyint(1) default NULL,
  PRIMARY KEY (ID)
) TYPE=MyISAM' | 0            |
+-------------+-------------+-------------+---------------+--------------+-----------+---------------+-----------------+---------------------+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+
1 row in set (0.00 sec)

mysql> 
Thread
replication - queries out of order or dropped?David Nedved13 Jan
  • Html entities and MySql, this is a confusing one to me.Scott Haneda13 Jan
    • Re: Html entities and MySql, this is a confusing one to me.Paul DuBois13 Jan
      • Re: Html entities and MySql, this is a confusing one to me.Scott Haneda13 Jan
        • Re: Html entities and MySql, this is a confusing one to me.Scott Haneda14 Jan
  • Re: replication - queries out of order or dropped?Jeremy Zawodny14 Jan
    • Re: replication - queries out of order or dropped?David Nedved14 Jan
      • Re: replication - queries out of order or dropped?Jeremy Zawodny15 Jan
        • Re: replication - queries out of order or dropped?Martin Waite15 Jan
          • Re: replication - queries out of order or dropped?David Nedved15 Jan