Bug #4865 Master Updates not Executing
Submitted: 3 Aug 2004 10:24 Modified: 4 Aug 2004 4:47
Reporter: cameron green Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Replication Severity:S2 (Serious)
Version:4.0.20 OS:Linux (Redhat Linux SE 3)
Assigned to: CPU Architecture:Any

[3 Aug 2004 10:24] cameron green
Description:
I've set up replication a couple of times on Solaris and Knoppix before and it worked straight away.  Trying is on Redhat SE 3 has taken me over a day and a half and getting nowhere.

Replication appears to be working technically, queries run on the master are sent to the slave and appear in the slaves relay log.   

eg..

master> insert into timmy values (1);

end of slave relay log :
# at 96
#040803 18:14:35 server id 2  log_pos 257       Query   thread_id=2     exec_time=0     error_code=0
SET TIMESTAMP=1091520875;
insert into timmy values (1);

slave> select * from timmy;
Empty set (0.00 sec)

The relay log position appears to increment past the query, but the query is never run on the master.  

show slave status before insert query :

          Master_Host: telos.soe.uq.edu.au
          Master_User: slave_skaro
          Master_Port: 3306
        Connect_retry: 60
      Master_Log_File: telos-bin.002
  Read_Master_Log_Pos: 205
       Relay_Log_File: skaro-relay-bin.005
        Relay_Log_Pos: 44
Relay_Master_Log_File: telos-bin.001
     Slave_IO_Running: Yes
    Slave_SQL_Running: Yes
      Replicate_do_db:
  Replicate_ignore_db:
           Last_errno: 0
           Last_error:
         Skip_counter: 0
  Exec_master_log_pos: 205
      Relay_log_space: 44

Post insert query :
          Master_Host: telos.soe.uq.edu.au
          Master_User: slave_skaro
          Master_Port: 3306
        Connect_retry: 60
      Master_Log_File: telos-bin.002
  Read_Master_Log_Pos: 320
       Relay_Log_File: skaro-relay-bin.005
        Relay_Log_Pos: 159
Relay_Master_Log_File: telos-bin.001
     Slave_IO_Running: Yes
    Slave_SQL_Running: Yes
      Replicate_do_db:
  Replicate_ignore_db:
           Last_errno: 0
           Last_error:
         Skip_counter: 0
  Exec_master_log_pos: 320
      Relay_log_space: 159

slave> show processlist;
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------+------------------+
| Id | User        | Host      | db   | Command | Time | State       | Info             |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------+------------------+
|  1 | system user |           | NULL | Connect | 475  | Waiting for master to send event       | NULL             |
|  2 | system user |           | NULL | Connect | 475  | Has read all relay log; waiting for the I/O slave thread to update it | NULL             |
|  3 | root        | localhost | test | Query   | 0    | NULL       | show processlist |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------+------------------+

last couple of lines from error log on slave

040803 18:11:18  Slave SQL thread initialized, starting replication in log 'telos-bin.001' at position 205, relay log './skaro-relay-bin.004' position: 107
/usr/sbin/mysqld: ready for connections.
Version: '4.0.20-standard-log'  socket: '/var/lib/mysql/mysql.sock'  port: 3306
040803 18:11:18  Slave I/O thread: connected to master 'slave_skaro@telos.soe.uq.edu.au:3306',  replication started in log 'telos-bin.002' at position 205

Really mystified, have searched everywhere for an answer.   Was going to try removing the servers and reinstalling them from rpm and trying again if you guys can't work out what is going wrong.

How to repeat:
No idea.

Suggested fix:
No idea.
[3 Aug 2004 10:26] cameron green
"The relay log position appears to increment past the query, but the query is
never run on the master. "

Meant slave.

Should be : The relay log position appears to increment past the query, but the query is
never run on the slave.
[3 Aug 2004 13:46] Guilhem Bichot
Hello,

Are you sure the master's and slave's server ids are different?
Do 
SELECT @@server_id;
on master and slave to check that.
In MySQL 4.1 I have added an automatic check of that everytime replication starts :)

Regards,
Guilhem
[4 Aug 2004 4:04] cameron green
You are exactly right.  Sorry for wasting your time.

Don't know if this is a redhat specific thing or not but the example conf comes with two server id sections.  One in the top of the file, one lower down in the slave conf stuff.

root@skaro# grep server-id  /usr/share/mysql/my-medium.cnf
server-id       = 1
#server-id       = 2

I uncommented all the fields in the slave section, but not realising there were two server-ids in the file didn't update both with the new values.  Anyway obviously my silliness, but would prevent future mishaps if you only have one of each variable in the example config files.

Cheerio,

Cameron