SEO

Hi

I am running 4.1.20 as this is the stock version in RHEL4 u5 and i have
setup a master/slave combination. Updates at first appeared to work as
creating and inserting into the master created the db's on the slave
etc. I have noticed however that data in the master is not making its
way to the slave.

I am now seeing issues like this

mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.12.225
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 2008085
Relay_Log_File: devsw19nagios02-relay-bin.000001
Relay_Log_Pos: 2909
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1062
Last_Error: Error 'Duplicate entry '754' for key 1' on
query. Default database: 'nagiosql'. Query: 'INSERT INTO tbl_logbook SET
user='Admin',time=NOW(), entry='Passwort successfully modified''
Skip_Counter: 0
Exec_Master_Log_Pos: 2941
Relay_Log_Space: 2008053
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
1 row in set (0.00 sec)

and in the mysql log

080516 9:23:01 [Note] Slave SQL thread initialized, starting
replication in log 'mysql-bin.000003' at position 79, relay log
'./devpctnagios02-relay-bin.000001' position: 4
080516 9:23:01 [Note] Slave I/O thread: connected to master
'repl@192.168.12.225:3306', replication started in log
'mysql-bin.000003' at position 79
080516 10:06:58 [ERROR] Slave: Error 'Duplicate entry '754' for key 1'
on query. Default database: 'nagiosql'. Query: 'INSERT INTO tbl_logbook
SET user='Admin',time=NOW(), entry='Passwort successfully modified'',
Error_code: 1062
080516 10:06:58 [ERROR] Error running query, slave SQL thread aborted.
Fix the problem, and restart the slave SQL thread with "SLAVE START". We
stopped at log 'mysql-bin.000003' position 2941

Any clues as to how i can fix this?

thanks

Hi Tom,

The error 1062 you could skipped in the slave modifying your my.cnf ( slave
side) using :

slave-skip-errors=1062

This error on duplicated records, normally is problem in binarylogs transfer
data. But, anyway, please confer that the data exist in the slave.

Regards,
Juan



On Fri, May 16, 2008 at 8:40 AM, Tom Brown <tom@ng23.net> wrote:

> Hi
>
> I am running 4.1.20 as this is the stock version in RHEL4 u5 and i have
> setup a master/slave combination. Updates at first appeared to work as
> creating and inserting into the master created the db's on the slave etc. I
> have noticed however that data in the master is not making its way to the
> slave.
>
> I am now seeing issues like this
>
> mysql> show slave status\G
> *************************** 1. row ***************************
> Slave_IO_State: Waiting for master to send event
> Master_Host: 192.168.12.225
> Master_User: repl
> Master_Port: 3306
> Connect_Retry: 60
> Master_Log_File: mysql-bin.000003
> Read_Master_Log_Pos: 2008085
> Relay_Log_File: devsw19nagios02-relay-bin.000001
> Relay_Log_Pos: 2909
> Relay_Master_Log_File: mysql-bin.000003
> Slave_IO_Running: Yes
> Slave_SQL_Running: No
> Replicate_Do_DB:
> Replicate_Ignore_DB:
> Replicate_Do_Table:
> Replicate_Ignore_Table:
> Replicate_Wild_Do_Table:
> Replicate_Wild_Ignore_Table:
> Last_Errno: 1062
> Last_Error: Error 'Duplicate entry '754' for key 1' on
> query. Default database: 'nagiosql'. Query: 'INSERT INTO tbl_logbook SET
> user='Admin',time=NOW(), entry='Passwort successfully modified''
> Skip_Counter: 0
> Exec_Master_Log_Pos: 2941
> Relay_Log_Space: 2008053
> Until_Condition: None
> Until_Log_File:
> Until_Log_Pos: 0
> Master_SSL_Allowed: No
> Master_SSL_CA_File:
> Master_SSL_CA_Path:
> Master_SSL_Cert:
> Master_SSL_Cipher:
> Master_SSL_Key:
> Seconds_Behind_Master: NULL
> 1 row in set (0.00 sec)
>
> and in the mysql log
>
> 080516 9:23:01 [Note] Slave SQL thread initialized, starting replication
> in log 'mysql-bin.000003' at position 79, relay log
> './devpctnagios02-relay-bin.000001' position: 4
> 080516 9:23:01 [Note] Slave I/O thread: connected to master '
> repl@192.168.12.225:3306', replication started in log 'mysql-bin.000003'
> at position 79
> 080516 10:06:58 [ERROR] Slave: Error 'Duplicate entry '754' for key 1' on
> query. Default database: 'nagiosql'. Query: 'INSERT INTO tbl_logbook SET
> user='Admin',time=NOW(), entry='Passwort successfully modified'',
> Error_code: 1062
> 080516 10:06:58 [ERROR] Error running query, slave SQL thread aborted. Fix
> the problem, and restart the slave SQL thread with "SLAVE START". We stopped
> at log 'mysql-bin.000003' position 2941
>
> Any clues as to how i can fix this?
>
> thanks
>
>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=j...domc@gmail.com
>
>

>
> The error 1062 you could skipped in the slave modifying your my.cnf ( slave
> side) using :
>
> slave-skip-errors=1062
>
> This error on duplicated records, normally is problem in binarylogs transfer
> data. But, anyway, please confer that the data exist in the slave.
>
>

Yes thanks - i have done that and restarted the slave and _some_ tables
now appear to update but others dont

eg

master:
mysql> select count(*) from tbl_checkcommand;
+----------+
| count(*) |
+----------+
| 30 |
+----------+
1 row in set (0.00 sec)

slave:
mysql> select count(*) from tbl_checkcommand;
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)

all seems ok in the log

080516 12:21:55 [Note] Slave SQL thread initialized, starting
replication in log 'mysql-bin.000003' at position 2941, relay log
'./devsw19nagios02-relay-bin.000001' position: 2909
/usr/libexec/mysqld: ready for connections.
Version: '4.1.20' socket: '/var/lib/mysql/mysql.sock' port: 3306
Source distribution
080516 12:21:55 [Note] Slave I/O thread: connected to master
'repl@192.168.12.225:3306', replication started in log
'mysql-bin.000003' at position 4608829

any ideas as to why they are not all updating ?

thanks

>
> Yes thanks - i have done that and restarted the slave and _some_
> tables now appear to update but others dont
>
> eg
>
> master:
> mysql> select count(*) from tbl_checkcommand;
> +----------+
> | count(*) |
> +----------+
> | 30 |
> +----------+
> 1 row in set (0.00 sec)
>
> slave:
> mysql> select count(*) from tbl_checkcommand;
> +----------+
> | count(*) |
> +----------+
> | 0 |
> +----------+
> 1 row in set (0.00 sec)
>
> all seems ok in the log
>
> 080516 12:21:55 [Note] Slave SQL thread initialized, starting
> replication in log 'mysql-bin.000003' at position 2941, relay log
> './devsw19nagios02-relay-bin.000001' position: 2909
> /usr/libexec/mysqld: ready for connections.
> Version: '4.1.20' socket: '/var/lib/mysql/mysql.sock' port: 3306
> Source distribution
> 080516 12:21:55 [Note] Slave I/O thread: connected to master
> 'repl@192.168.12.225:3306', replication started in log
> 'mysql-bin.000003' at position 4608829
>
> any ideas as to why they are not all updating ?
>
>

i should probabbly also mention that the slave appears to think its
happy, i believe,

mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.12.225
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 6412734
Relay_Log_File: devsw19nagios02-relay-bin.000002
Relay_Log_Pos: 1803952
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 6412734
Relay_Log_Space: 1803952
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
1 row in set (0.00 sec)

Hi Tom,

Your master binary log start with mysql-bin.000003, then, your insert ( or
update) in the master could be in mysql-bin.000002 or 1.

Please, reload your binary logs again from the beginning ( put
mysql-bin.000002 or 1, ). Your insert or update could be in one of this
files.

Regards,
Juan

On Fri, May 16, 2008 at 10:09 AM, Tom Brown <tom@ng23.net> wrote:

>
>
>> Yes thanks - i have done that and restarted the slave and _some_ tables
>> now appear to update but others dont
>>
>> eg
>>
>> master:
>> mysql> select count(*) from tbl_checkcommand;
>> +----------+
>> | count(*) |
>> +----------+
>> | 30 |
>> +----------+
>> 1 row in set (0.00 sec)
>>
>> slave:
>> mysql> select count(*) from tbl_checkcommand;
>> +----------+
>> | count(*) |
>> +----------+
>> | 0 |
>> +----------+
>> 1 row in set (0.00 sec)
>>
>> all seems ok in the log
>>
>> 080516 12:21:55 [Note] Slave SQL thread initialized, starting replication
>> in log 'mysql-bin.000003' at position 2941, relay log
>> './devsw19nagios02-relay-bin.000001' position: 2909
>> /usr/libexec/mysqld: ready for connections.
>> Version: '4.1.20' socket: '/var/lib/mysql/mysql.sock' port: 3306 Source
>> distribution
>> 080516 12:21:55 [Note] Slave I/O thread: connected to master '
>> repl@192.168.12.225:3306', replication started in log 'mysql-bin.000003'
>> at position 4608829
>>
>> any ideas as to why they are not all updating ?
>>
>>
>>
> i should probabbly also mention that the slave appears to think its happy,
> i believe,
>
> mysql> show slave status\G
> *************************** 1. row ***************************
> Slave_IO_State: Waiting for master to send event
> Master_Host: 192.168.12.225
> Master_User: repl
> Master_Port: 3306
> Connect_Retry: 60
> Master_Log_File: mysql-bin.000003
> Read_Master_Log_Pos: 6412734
> Relay_Log_File: devsw19nagios02-relay-bin.000002
> Relay_Log_Pos: 1803952
> Relay_Master_Log_File: mysql-bin.000003
> Slave_IO_Running: Yes
> Slave_SQL_Running: Yes
> Replicate_Do_DB:
> Replicate_Ignore_DB:
> Replicate_Do_Table:
> Replicate_Ignore_Table:
> Replicate_Wild_Do_Table:
> Replicate_Wild_Ignore_Table:
> Last_Errno: 0
> Last_Error:
> Skip_Counter: 0
> Exec_Master_Log_Pos: 6412734
> Relay_Log_Space: 1803952
> Until_Condition: None
> Until_Log_File:
> Until_Log_Pos: 0
> Master_SSL_Allowed: No
> Master_SSL_CA_File:
> Master_SSL_CA_Path:
> Master_SSL_Cert:
> Master_SSL_Cipher:
> Master_SSL_Key:
> Seconds_Behind_Master: 0
>
> 1 row in set (0.00 sec)
>
>
>