Re: Replication problem
On 8/29/2014 7:40 PM, Suresh Kuna wrote:
You can paste the show slave status\G here for us to review and on Master,
show global variables like 'log-bin%'; show master status ( 3 to 4 times
continuously )
Master:
+--+--+--+--+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--+--+--+--+
| mysql-bin.001225 | 107 | | |
+--+--+--+--+
1 row in set (0.00 sec)
Slave
* 1. row ***
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.10.101
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.001225
Read_Master_Log_Pos: 107
Relay_Log_File: ubuntu2-relay-bin.02
Relay_Log_Pos: 16426
Relay_Master_Log_File: mysql-bin.001139
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 '18699'
for key 'PRIMARY'' on query. Default database:
'Information_server'. Query: 'insert into audit (audit_type,
subtype, user_Id, ptNum, data) values ('login', 'login', 'WPD',
0, 'Login: WPD from location: Suite B')'
Skip_Counter: 0
Exec_Master_Log_Pos: 90012430
Relay_Log_Space: 29727610
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
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1062
Last_SQL_Error: Error 'Duplicate entry '18699'
for key 'PRIMARY'' on query. Default database:
'Information_server'. Query: 'insert into audit (audit_type,
subtype, user_Id, ptNum, data) values ('login', 'login', 'WPD',
0, 'Login: WPD from location: Suite B')'
Replicate_Ignore_Server_Ids:
Master_Server_Id: 3
1 row in set (0.00 sec)
1 transaction entered:
Master:
+--+--+--+--+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--+--+--+--+
| mysql-bin.001225 | 1837 | | |
+--+--+--+--+
1 row in set (0.00 sec)
SLave
*** 1. row ***
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.10.101
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.001225
Read_Master_Log_Pos: 1837
Relay_Log_File: ubuntu2-relay-bin.02
Relay_Log_Pos: 16426
Relay_Master_Log_File: mysql-bin.001139
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 '18699'
for key 'PRIMARY'' on query. Default database:
'Information_server'. Query: 'insert into audit (audit_type,
subtype, user_Id, ptNum, data) values ('login', 'login', 'WPD',
0, 'Login: WPD from location: Suite B')'
Skip_Counter: 0
Exec_Master_Log_Pos: 90012430
Relay_Log_Space: 29729340
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
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1062
Last_SQL_Error: Error 'Duplicate entry '18699'
for key 'PRIMARY'' on query. Default database:
'Information_server'. Query: 'insert into audit (audit_type,
subtype, user_Id, ptNum, data) values ('login', 'login', 'WPD',
0, 'Login: WPD from location: Suite B')'
Replicate_Ignore_Server_Ids:
Master_Server_Id: 3
1 row in set (0.00 sec)
Re: Replication problem
On 8/29/2014 5:11 PM, wagnerbianchi.com wrote: Hello guys, some points to check here: 1-) Is the master server configured with sync_binlog=1 ? It was not, I reconfigured and restarted mysql and... 2-) About the SHOW SLAVE STATUS output, when slave seems to be just reading events from master, is the Exec_Master_Log_Pos incrementing or not? Not - see prior post 3-) Why are you reconfiguring all the replication just because the link went down? AFAIK, I am no reconfiguring, after the link comes back up, the slave does not start replicating - sometimes. Happens quite infrequently. I intend to be just restoring the database and restarting replication Cheers, Thanks, I need the cheers. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Replication problem
On 8/29/2014 7:40 PM, Suresh Kuna wrote:
You can paste the show slave status\G here for us to review and on Master,
show global variables like 'log-bin%'; show master status ( 3 to 4 times
continuously )
after a more complex transaction;
+--+--+--+--+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--+--+--+--+
| mysql-bin.001225 | 5952 | | |
+--+--+--+--+
*** 1. row ***
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.10.101
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.001225
Read_Master_Log_Pos: 5952
Relay_Log_File: ubuntu2-relay-bin.02
Relay_Log_Pos: 16426
Relay_Master_Log_File: mysql-bin.001139
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 '18699'
for key 'PRIMARY'' on query. Default database:
'Information_server'. Query: 'insert into audit (audit_type,
subtype, user_Id, ptNum, data) values ('login', 'login', 'WPD',
0, 'Login: WPD from location: Suite B')'
Skip_Counter: 0
Exec_Master_Log_Pos: 90012430
Relay_Log_Space: 29733455
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
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1062
Last_SQL_Error: Error 'Duplicate entry '18699'
for key 'PRIMARY'' on query. Default database:
'Information_server'. Query: 'insert into audit (audit_type,
subtype, user_Id, ptNum, data) values ('login', 'login', 'WPD',
0, 'Login: WPD from location: Suite B')'
Replicate_Ignore_Server_Ids:
Master_Server_Id: 3
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: Replication problem
There's a duplicate key on the audit table, 18699. Delete it and restart
slave (start slave). Check slave status again, might be more rows in there
duplicated.
You might want to compare the row to master to ensure it's a duplicate
before deleting from slave.
On Aug 30, 2014 7:52 AM, william drescher will...@techservsys.com
wrote:
On 8/29/2014 7:40 PM, Suresh Kuna wrote:
You can paste the show slave status\G here for us to review and on
Master,
show global variables like 'log-bin%'; show master status ( 3 to 4 times
continuously )
after a more complex transaction;
+--+--+--+--+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--+--+--+--+
| mysql-bin.001225 | 5952 | | |
+--+--+--+--+
*** 1. row ***
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.10.101
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.001225
Read_Master_Log_Pos: 5952
Relay_Log_File: ubuntu2-relay-bin.02
Relay_Log_Pos: 16426
Relay_Master_Log_File: mysql-bin.001139
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 '18699' for key
'PRIMARY'' on query. Default database: 'Information_server'. Query: 'insert
into audit (audit_type, subtype, user_Id, ptNum, data) values ('login',
'login', 'WPD', 0, 'Login: WPD from location: Suite B')'
Skip_Counter: 0
Exec_Master_Log_Pos: 90012430
Relay_Log_Space: 29733455
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
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1062
Last_SQL_Error: Error 'Duplicate entry '18699' for key
'PRIMARY'' on query. Default database: 'Information_server'. Query: 'insert
into audit (audit_type, subtype, user_Id, ptNum, data) values ('login',
'login', 'WPD', 0, 'Login: WPD from location: Suite B')'
Replicate_Ignore_Server_Ids:
Master_Server_Id: 3
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: Replication problem
On 8/29/2014 7:40 PM, Suresh Kuna wrote:
You can paste the show slave status\G here for us to review and on Master,
show global variables like 'log-bin%'; show master status ( 3 to 4 times
continuously ) global var, below
+--+--+--+--+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--+--+--+--+
| mysql-bin.001225 | 8919 | | |
+--+--+--+--+
*** 1. row ***
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.10.101
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.001225
Read_Master_Log_Pos: 8919
Relay_Log_File: ubuntu2-relay-bin.02
Relay_Log_Pos: 16426
Relay_Master_Log_File: mysql-bin.001139
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 '18699'
for key 'PRIMARY'' on query. Default database:
'Information_server'. Query: 'insert into audit (audit_type,
subtype, user_Id, ptNum, data) values ('login', 'login', 'WPD',
0, 'Login: WPD from location: Suite B')'
Skip_Counter: 0
Exec_Master_Log_Pos: 90012430
Relay_Log_Space: 29736422
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
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1062
Last_SQL_Error: Error 'Duplicate entry '18699'
for key 'PRIMARY'' on query. Default database:
'Information_server'. Query: 'insert into audit (audit_type,
subtype, user_Id, ptNum, data) values ('login', 'login', 'WPD',
0, 'Login: WPD from location: Suite B')'
Replicate_Ignore_Server_Ids:
Master_Server_Id: 3
mysql show global variables like 'log-bin%';
Empty set (0.01 sec)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: Replication problem
Based on the SHOW SLAVE STATUS output you've sent us, I'd suggest that you
check what the application is doing, understand *why* the application is
violating the PK of the table information_server.audit, repair the possible
application problem and the, reconfigure the replication.
It seems that there's something not really good happening on the
application side since the error 1062 is being threw out by a INSERT sent
to the database ... by the application.
*PS.: looking at the purpose of information_server.audit, I wonder if this
is not a *design* problem!*
1-) Fix the application if you can, investigating the problem the
replication is showing up on SHOW SLAVE STATUS - remember that the
column Last_SQL_Error
is one of SHOW SLAVE STATUS best friends in this context;
2-) After to fix the problem, if design or application, recreate the slave
and then, start replication again;
It's possible to use another things to make the replication to bypass this
kind of problem, but, it's not that cool to have it configured. due to
that, I'd like to omit it at this point.
Let's keep in touch, happy mysql'ing!!
--
*Wagner Bianchi*
2014-08-30 9:54 GMT-03:00 Johnny Withers joh...@pixelated.net:
There's a duplicate key on the audit table, 18699. Delete it and restart
slave (start slave). Check slave status again, might be more rows in there
duplicated.
You might want to compare the row to master to ensure it's a duplicate
before deleting from slave.
On Aug 30, 2014 7:52 AM, william drescher will...@techservsys.com
wrote:
On 8/29/2014 7:40 PM, Suresh Kuna wrote:
You can paste the show slave status\G here for us to review and on
Master,
show global variables like 'log-bin%'; show master status ( 3 to 4 times
continuously )
after a more complex transaction;
+--+--+--+--+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--+--+--+--+
| mysql-bin.001225 | 5952 | | |
+--+--+--+--+
*** 1. row ***
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.10.101
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.001225
Read_Master_Log_Pos: 5952
Relay_Log_File: ubuntu2-relay-bin.02
Relay_Log_Pos: 16426
Relay_Master_Log_File: mysql-bin.001139
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 '18699' for key
'PRIMARY'' on query. Default database: 'Information_server'. Query:
'insert
into audit (audit_type, subtype, user_Id, ptNum, data) values ('login',
'login', 'WPD', 0, 'Login: WPD from location: Suite B')'
Skip_Counter: 0
Exec_Master_Log_Pos: 90012430
Relay_Log_Space: 29733455
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
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1062
Last_SQL_Error: Error 'Duplicate entry '18699' for key
'PRIMARY'' on query. Default database: 'Information_server'. Query:
'insert
into audit (audit_type, subtype, user_Id, ptNum, data) values ('login',
'login', 'WPD', 0, 'Login: WPD from location: Suite B')'
Replicate_Ignore_Server_Ids:
Master_Server_Id: 3
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: Replication problem
On 30/08/14 09:39, william drescher wrote:
On 8/29/2014 7:40 PM, Suresh Kuna wrote:
You can paste the show slave status\G here for us to review and on
Master,
show global variables like 'log-bin%'; show master status ( 3 to 4 times
continuously )
Master:
+--+--+--+--+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--+--+--+--+
| mysql-bin.001225 | 107 | | |
+--+--+--+--+
1 row in set (0.00 sec)
Slave
* 1. row ***
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.10.101
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.001225
Read_Master_Log_Pos: 107
Relay_Log_File: ubuntu2-relay-bin.02
Relay_Log_Pos: 16426
Relay_Master_Log_File: mysql-bin.001139
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 '18699' for key 'PRIMARY'' on query.
Default database: 'Information_server'. Query: 'insert into audit
(audit_type, subtype, user_Id, ptNum, data) values ('login', 'login',
'WPD', 0, 'Login: WPD from location: Suite B')'
Skip_Counter: 0
Exec_Master_Log_Pos: 90012430
Relay_Log_Space: 29727610
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
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1062
Last_SQL_Error: Error 'Duplicate entry '18699' for key 'PRIMARY'' on
query. Default database: 'Information_server'. Query: 'insert into audit
(audit_type, subtype, user_Id, ptNum, data) values ('login', 'login',
'WPD', 0, 'Login: WPD from location: Suite B')'
Replicate_Ignore_Server_Ids:
Master_Server_Id: 3
1 row in set (0.00 sec)
1 transaction entered:
Master:
+--+--+--+--+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--+--+--+--+
| mysql-bin.001225 | 1837 | | |
+--+--+--+--+
1 row in set (0.00 sec)
SLave
*** 1. row ***
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.10.101
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.001225
Read_Master_Log_Pos: 1837
Relay_Log_File: ubuntu2-relay-bin.02
Relay_Log_Pos: 16426
Relay_Master_Log_File: mysql-bin.001139
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 '18699' for key 'PRIMARY'' on query.
Default database: 'Information_server'. Query: 'insert into audit
(audit_type, subtype, user_Id, ptNum, data) values ('login', 'login',
'WPD', 0, 'Login: WPD from location: Suite B')'
Skip_Counter: 0
Exec_Master_Log_Pos: 90012430
Relay_Log_Space: 29729340
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
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1062
Last_SQL_Error: Error 'Duplicate entry '18699' for key 'PRIMARY'' on
query. Default database: 'Information_server'. Query: 'insert into audit
(audit_type, subtype, user_Id, ptNum, data) values ('login', 'login',
'WPD', 0, 'Login: WPD from location: Suite B')'
Replicate_Ignore_Server_Ids:
Master_Server_Id: 3
1 row in set (0.00 sec)
Well , it seems that you have a 'Duplicate entry ' issue on slave, so
the Slave_SQL_Running threads is locked :) , there's something wrong
here with the initial slave status, maybe you have to rebuild the slave
from scratch(backup or a dump from master), and be sure that you don't
change anything on slave( phpmyadmin? )
Bye
Julian
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: Replication problem
On 8/29/2014 7:40 PM, Suresh Kuna wrote: You can paste the show slave status\G here for us to review and on Master, show global variables like 'log-bin%'; show master status ( 3 to 4 times continuously ) mysql show global variables like 'log_bin%'; +-+---+ | Variable_name | Value | +-+---+ | log_bin | ON| | log_bin_trust_function_creators | OFF | +-+---+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Replication problem -solved
Thanks for pointing out the importance of the last error I resynced the slave to the master, reset the master position, and restarted the slave. Now all works fine and I am much better equipped next time to debug the loss of the link. When is the Last Error data deleted from the show slave data ? --bill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Replication problem -solved
On 30/08/14 12:56, william drescher wrote: Thanks for pointing out the importance of the last error I resynced the slave to the master, reset the master position, and restarted the slave. Now all works fine and I am much better equipped next time to debug the loss of the link. When is the Last Error data deleted from the show slave data ? --bill Just after you resync the slave, last error should be empty. Bye Julian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Replication problem -solved
On 8/30/2014 12:53 PM, Jose Julian Buda wrote: On 30/08/14 12:56, william drescher wrote: Thanks for pointing out the importance of the last error I resynced the slave to the master, reset the master position, and restarted the slave. Now all works fine and I am much better equipped next time to debug the loss of the link. When is the Last Error data deleted from the show slave data ? --bill Just after you resync the slave, last error should be empty. Bye Julian Thanks Julian, it is. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Replication problem
Replication novice I have a master server at the office and a replication server at home. This setup has been working for a couple of years. Occasionally the replication server gets out of sync (usually following a internet problem and the vpn going down.) I just stop the slave, make sure there is nothing going to the master (when the office is closed), copy the database, transfer the file, load the backup, and start the slave and all is well. This time there was not a communications problem of which I am aware. The slave status said the slave_IO_state was Waiting for master to send event but it was not replicating. I did the usual now it is not updating the replication database (transactions made on the master do not show on the slave - using phpMyAdmin on both servers) BUT show master status shows the correct log file and the position is incrementing AND show slave status shows the same master log file and the same position as the master. So, looking at the status info it seems to be running fine, but the transactions do not appear to appear on the slave. I seek suggestions how to 1) find out what goes wrong when the vpn goes down, and 2) (much more important now) how to find out whether or not the slave is actually replicating or not. --bill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Replication problem
Whilst there are a few possibilities, check on the master that your binary logs are being written to. Another possible reason could be filtering. On 29 Aug 2014 21:36, william drescher will...@techservsys.com wrote: Replication novice I have a master server at the office and a replication server at home. This setup has been working for a couple of years. Occasionally the replication server gets out of sync (usually following a internet problem and the vpn going down.) I just stop the slave, make sure there is nothing going to the master (when the office is closed), copy the database, transfer the file, load the backup, and start the slave and all is well. This time there was not a communications problem of which I am aware. The slave status said the slave_IO_state was Waiting for master to send event but it was not replicating. I did the usual now it is not updating the replication database (transactions made on the master do not show on the slave - using phpMyAdmin on both servers) BUT show master status shows the correct log file and the position is incrementing AND show slave status shows the same master log file and the same position as the master. So, looking at the status info it seems to be running fine, but the transactions do not appear to appear on the slave. I seek suggestions how to 1) find out what goes wrong when the vpn goes down, and 2) (much more important now) how to find out whether or not the slave is actually replicating or not. --bill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Replication problem
Hello guys, some points to check here: 1-) Is the master server configured with sync_binlog=1 ? 2-) About the SHOW SLAVE STATUS output, when slave seems to be just reading events from master, is the Exec_Master_Log_Pos incrementing or not? 3-) Why are you reconfiguring all the replication just because the link went down? Cheers, -- *WB* 2014-08-29 17:46 GMT-03:00 Andrew Moore eroomy...@gmail.com: Whilst there are a few possibilities, check on the master that your binary logs are being written to. Another possible reason could be filtering. On 29 Aug 2014 21:36, william drescher will...@techservsys.com wrote: Replication novice I have a master server at the office and a replication server at home. This setup has been working for a couple of years. Occasionally the replication server gets out of sync (usually following a internet problem and the vpn going down.) I just stop the slave, make sure there is nothing going to the master (when the office is closed), copy the database, transfer the file, load the backup, and start the slave and all is well. This time there was not a communications problem of which I am aware. The slave status said the slave_IO_state was Waiting for master to send event but it was not replicating. I did the usual now it is not updating the replication database (transactions made on the master do not show on the slave - using phpMyAdmin on both servers) BUT show master status shows the correct log file and the position is incrementing AND show slave status shows the same master log file and the same position as the master. So, looking at the status info it seems to be running fine, but the transactions do not appear to appear on the slave. I seek suggestions how to 1) find out what goes wrong when the vpn goes down, and 2) (much more important now) how to find out whether or not the slave is actually replicating or not. --bill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Replication problem
You can paste the show slave status\G here for us to review and on Master, show global variables like 'log-bin%'; show master status ( 3 to 4 times continuously ) On Fri, Aug 29, 2014 at 5:11 PM, wagnerbianchi.com m...@wagnerbianchi.com wrote: Hello guys, some points to check here: 1-) Is the master server configured with sync_binlog=1 ? 2-) About the SHOW SLAVE STATUS output, when slave seems to be just reading events from master, is the Exec_Master_Log_Pos incrementing or not? 3-) Why are you reconfiguring all the replication just because the link went down? Cheers, -- *WB* 2014-08-29 17:46 GMT-03:00 Andrew Moore eroomy...@gmail.com: Whilst there are a few possibilities, check on the master that your binary logs are being written to. Another possible reason could be filtering. On 29 Aug 2014 21:36, william drescher will...@techservsys.com wrote: Replication novice I have a master server at the office and a replication server at home. This setup has been working for a couple of years. Occasionally the replication server gets out of sync (usually following a internet problem and the vpn going down.) I just stop the slave, make sure there is nothing going to the master (when the office is closed), copy the database, transfer the file, load the backup, and start the slave and all is well. This time there was not a communications problem of which I am aware. The slave status said the slave_IO_state was Waiting for master to send event but it was not replicating. I did the usual now it is not updating the replication database (transactions made on the master do not show on the slave - using phpMyAdmin on both servers) BUT show master status shows the correct log file and the position is incrementing AND show slave status shows the same master log file and the same position as the master. So, looking at the status info it seems to be running fine, but the transactions do not appear to appear on the slave. I seek suggestions how to 1) find out what goes wrong when the vpn goes down, and 2) (much more important now) how to find out whether or not the slave is actually replicating or not. --bill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- -- Thanks Suresh Kuna MySQL Database Consutant MongoDB DBA Hadoop Admin
Re: replication problem
On 12/3/2012 7:24 AM, Trimurthy wrote: hi list, i am trying to set up replication but i couldn't complete because of the following error Could not find first log file name in binary log index file can any one please help me. ... snipped ... This one should have been easy for the list to advise you about. Let me step in to keep things on track. For each instance of MySQL that creates binary logs there will be an 'index' file that keeps track of which files this instance is managing. For example, if the binary logs are called mysql-bin.00, then the index file will be called mysql-bin.index What has happened is that the contents of the index file no longer matches the inventory of actual binary logs present on disk. They have either been moved, erased, or had their permissions changed so that the system user that the mysqld daemon executes as can no longer see them. The solution is to manually update the .index file with the current listing of binary log files. The longer term solution is to stop manually removing binary log files and allow the system to perform that for you with a PURGE BINARY LOGS command. When you have MySQL do the purging, the .index file will be automatically updated. For more details about the binary log, please read: http://dev.mysql.com/doc/refman/5.5/en/binary-log.html -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: replication problem
HI share detail Slave_IO_Running: Yes/No Slave_SQL_Running: Yes/No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: On Tue, Dec 4, 2012 at 5:59 AM, Rick James rja...@yahoo-inc.com wrote: SHOW SLAVE STATUS\G SHOW MASTER STATUS; What directory are the binlogs in? -Original Message- From: Michael Dykman [mailto:mdyk...@gmail.com] Sent: Monday, December 03, 2012 8:53 AM To: trimur...@tulassi.com Cc: mysql@lists.mysql.com Subject: Re: replication problem Trimurthy, you will have to describe the method you are using to setup replication. The error message seems plain but an observer could not reasonably guess what caused it without more information. - michael dykman On Mon, Dec 3, 2012 at 7:24 AM, Trimurthy trimur...@tulassi.com wrote: hi list, i am trying to set up replication but i couldn't complete because of the following error Could not find first log file name in binary log index file can any one please help me. Normal 0 false false false EN-US X-NONE AR-SA Thanks Kind Regards, TRIMURTHY -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: replication problem
Trimurthy, you will have to describe the method you are using to setup replication. The error message seems plain but an observer could not reasonably guess what caused it without more information. - michael dykman On Mon, Dec 3, 2012 at 7:24 AM, Trimurthy trimur...@tulassi.com wrote: hi list, i am trying to set up replication but i couldn't complete because of the following error Could not find first log file name in binary log index file can any one please help me. Normal 0 false false false EN-US X-NONE AR-SA Thanks Kind Regards, TRIMURTHY -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: replication problem
SHOW SLAVE STATUS\G SHOW MASTER STATUS; What directory are the binlogs in? -Original Message- From: Michael Dykman [mailto:mdyk...@gmail.com] Sent: Monday, December 03, 2012 8:53 AM To: trimur...@tulassi.com Cc: mysql@lists.mysql.com Subject: Re: replication problem Trimurthy, you will have to describe the method you are using to setup replication. The error message seems plain but an observer could not reasonably guess what caused it without more information. - michael dykman On Mon, Dec 3, 2012 at 7:24 AM, Trimurthy trimur...@tulassi.com wrote: hi list, i am trying to set up replication but i couldn't complete because of the following error Could not find first log file name in binary log index file can any one please help me. Normal 0 false false false EN-US X-NONE AR-SA Thanks Kind Regards, TRIMURTHY -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Crazy replication problem
Here is steps take to get to the problem. Version is 5.1.35 To be on the safe side, I did this prior to executing the scripts. I deleted the mysql directory and the mysql-log directory, recreated them, chowned them, ran mysql_install_db, chowned them again. So, I know at least I'm working with a clean setup. I did this on both servers. So, I have a script, which is actually a combination about 200 other scripts, cat'ed into a single script. The first half of the script creates a database and several tables. After all of the tables are created, a trigger is created on one of the tables. Following the trigger, about 150 stored procedures. This loads fine on the master server. All looks well. I then set the slave to slave against the master and it chokes on the creation of the trigger saying it can't find the table. quickly glancing at the tables, there are none. The database creation was replicated but the tables were not. Here is what I have in the my.cnf file for replication (we had this setup for master/master earlier, thus the replicate-do-db, but we will not be doing that in this round): log-slave-updates sync_binlog = 1 replicate-same-server-id= 0 log_bin_trust_function_creators = 1 log-bin = /exports/mysql-log/log-repl binlog-do-db= weblog binlog-do-db= webarchive replicate-do-db = weblog replicate-do-db = webarchive relay-log = relay-bin relay-log-index = relay-index relay-log-info-file = relay-info relay-log-purge = 1 Everything is INNODB. Any ideas as to why the tables didn't replicate? Also, I know that triggers shouldn't be replicated. Is there any was to prevent trigger replication? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Crazy replication problem
One quick follow up note: In the top of the big script, I'm doing: CREATE DATABASE x; CREATE TABLE x.whatever (...); Reading into the various bug reports, do I need to issue a USE x; ? I'm assuming that MySql isn't picking up the replica because there is no current database specified, even though the tables are qualified. Can anyone confirm this? Gary From: Gary Smith [g...@primeexalia.com] Sent: Friday, July 10, 2009 5:12 PM To: mysql@lists.mysql.com Subject: Crazy replication problem Here is steps take to get to the problem. Version is 5.1.35 To be on the safe side, I did this prior to executing the scripts. I deleted the mysql directory and the mysql-log directory, recreated them, chowned them, ran mysql_install_db, chowned them again. So, I know at least I'm working with a clean setup. I did this on both servers. So, I have a script, which is actually a combination about 200 other scripts, cat'ed into a single script. The first half of the script creates a database and several tables. After all of the tables are created, a trigger is created on one of the tables. Following the trigger, about 150 stored procedures. This loads fine on the master server. All looks well. I then set the slave to slave against the master and it chokes on the creation of the trigger saying it can't find the table. quickly glancing at the tables, there are none. The database creation was replicated but the tables were not. Here is what I have in the my.cnf file for replication (we had this setup for master/master earlier, thus the replicate-do-db, but we will not be doing that in this round): log-slave-updates sync_binlog = 1 replicate-same-server-id= 0 log_bin_trust_function_creators = 1 log-bin = /exports/mysql-log/log-repl binlog-do-db= weblog binlog-do-db= webarchive replicate-do-db = weblog replicate-do-db = webarchive relay-log = relay-bin relay-log-index = relay-index relay-log-info-file = relay-info relay-log-purge = 1 Everything is INNODB. Any ideas as to why the tables didn't replicate? Also, I know that triggers shouldn't be replicated. Is there any was to prevent trigger replication? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=g...@primeexalia.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Crazy replication problem
-Original Message- From: Gary Smith Sent: Friday, July 10, 2009 5:26 PM To: Gary Smith; mysql@lists.mysql.com Subject: RE: Crazy replication problem One quick follow up note: In the top of the big script, I'm doing: CREATE DATABASE x; CREATE TABLE x.whatever (...); Reading into the various bug reports, do I need to issue a USE x; ? I'm assuming that MySql isn't picking up the replica because there is no current database specified, even though the tables are qualified. Can anyone confirm this? Gary So I found that adding a USE database to the tables allowed it to replicate. I vaguely remember something like that a couple years back. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: replication problem
Hi. Can you give me a flow of your master/slave? On Wed, Jun 24, 2009 at 7:45 PM, 赵琦 tyzha...@gmail.com wrote: hi all: I have three mysql database,tow run as master and the other one runs as slave. Some tables in the database have an autoincreament field named as 'rowid'. These tables have 100 records on master, but some of these tables on the slave only have thousands of record. The tables on the slave are not the same as those on master. When i use 'show slave status', i find that Last_Errno equals to 0. How to solve this problem. -- David Yeung, MySQL Senior Support Engineer, Sun Gold Partner. My Blog:http://yueliangdao0608.cublog.cn
replication problem
hi all: I have three mysql database,tow run as master and the other one runs as slave. Some tables in the database have an autoincreament field named as 'rowid'. These tables have 100 records on master, but some of these tables on the slave only have thousands of record. The tables on the slave are not the same as those on master. When i use 'show slave status', i find that Last_Errno equals to 0. How to solve this problem.
Master-Master Replication Problem
I have configured 2 MySQL server to do master-master replication. Below is my config: Node A server-id = 1 log_bin = mysql-bin expire_logs_days= 10 max_binlog_size = 100M binlog_do_db= clustertest binlog_do_db= gateway binlog_do_db= reporting binlog_do_db= traffic binlog_ignore_db= mysql binlog_ignore_db= test replicate-same-server-id = 0 log-slave-updates auto-increment-increment = 10 auto-increment-offset= 1 master-host = 10.1.20.103 master-user = replicator master-password = username master-port = 3306 replicate-do-db = clustertest replicate-do-db = gateway replicate-do-db = reporting replicate-do-db = traffic Node B server-id = 2 log_bin = mysql-bin expire_logs_days= 10 max_binlog_size = 100M binlog_do_db= clustertest binlog_do_db= gateway binlog_do_db= reporting binlog_do_db= traffic binlog_ignore_db= mysql binlog_ignore_db= test replicate-same-server-id = 0 log-slave-updates auto-increment-increment = 10 auto-increment-offset= 2 master-host = 10.1.20.102 master-user = replicator master-password = username master-port = 3306 replicate-do-db = clustertest replicate-do-db = gateway replicate-do-db = reporting replicate-do-db = traffic I restart MySQL daemon, and go to mysql shell and execute this on both server: Node A flush tables with read lock; show master status \G Node b stop slave CHANGE MASTER TO MASTER_HOST='10.1.20.102', MASTER_USER='replicator', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.01', MASTER_LOG_POS=98; start slave I went back to Node A and execute unlock tables; Node B flush tables with read lock; show master status \G Node A stop slave CHANGE MASTER TO MASTER_HOST='10.1.20.103', MASTER_USER='replicator', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.01', MASTER_LOG_POS=98; start slave I went back to Node B and execute unlock tables; After that I try to insert new record from Node A and I see Node B is synchronized. But when I try to insert new record from Node B. I see nothing change at Node A. What I missed here? Willy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Replication problem
We have a windows server running our main database and an IIS server. We also have a linux box with apache/php that uses the same database server. We have configured a replication from that host to another windows server. Then we verified that not all the queries were replicated properly. We find out that some queries that come from the linux box and have cr/lf on them are not being replicated. ie: sql = select * from table_1; I think that may be some issues about how linux manages cr/lf. Whan can I do? thank you all! _ Consigue el nuevo Windows Live Messenger http://get.live.com/messenger/overview
Re: Replication problem
Hi, Jorge Martínez wrote: We have a windows server running our main database and an IIS server. We also have a linux box with apache/php that uses the same database server. We have configured a replication from that host to another windows server. Then we verified that not all the queries were replicated properly. We find out that some queries that come from the linux box and have cr/lf on them are not being replicated. ie: sql = select * from table_1; SELECT queries are not replicated. Only queries that change data are replicated. I think that may be some issues about how linux manages cr/lf. Whan can I do? thank you all! _ Consigue el nuevo Windows Live Messenger http://get.live.com/messenger/overview -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Replication problem
I'm sorry. I have posted a wrong example. Here you have a better one: update table_1 \n set field_1 = 'xx', \n field_2 = 'yy'; \n Date: Tue, 21 Aug 2007 11:16:50 -0400 From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] CC: mysql@lists.mysql.com Subject: Re: Replication problem Hi, Jorge Martínez wrote: We have a windows server running our main database and an IIS server. We also have a linux box with apache/php that uses the same database server.We have configured a replication from that host to another windows server. Then we verified that not all the queries were replicated properly. We find out that some queries that come from the linux box and have cr/lf on them are not being replicated. ie: sql = select * from table_1; SELECT queries are not replicated. Only queries that change data are replicated. I think that may be some issues about how linux manages cr/lf. Whan can I do?thank you all! _ Consigue el nuevo Windows Live Messenger http://get.live.com/messenger/overview -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ Llama a tus amigos de PC a PC: ¡Es GRATIS! http://get.live.com/messenger/overview
Re: Replication Problem? - SOLVED
It turns out that I have a binlog-ignore-db option configured for
another database. If you have even one of these you must make all
updates in the default database if you want them to be replicated. This
is a change in behavior from 4.0 to 4.1. We upgraded from 4.0 to 4.1 to
5.0 the same day which explains why we did not see this behavior sooner.
I removed the option from the config file and just used
replicate-ignore-db instead and all is right in the world again. Hope
this helps someone else down the road. Thanks for all of your helpful
information and suggestions.
http://dev.mysql.com/doc/refman/5.0/en/binary-log.html
-Ed
Logan, David (SST - Adelaide) wrote:
Hi Ed,
You may like to look at this, especially the last statement. If you are
not using these options however, I would suspect a bug.
Regards
--replicate-do-db=db_name
Tell the slave to restrict replication to statements where the default
database (that is, the one selected by USE) is db_name. To specify more
than one database, use this option multiple times, once for each
database. Note that this does not replicate cross-database statements
such as UPDATE some_db.some_table SET foo='bar' while having selected a
different database or no database.
An example of what does not work as you might expect: If the slave is
started with --replicate-do-db=sales and you issue the following
statements on the master, the UPDATE statement is not replicated:
USE prices;
UPDATE sales.january SET amount=amount+1000;
The main reason for this just check the default database behavior is
that it is difficult from the statement alone to know whether it should
be replicated (for example, if you are using multiple-table DELETE
statements or multiple-table UPDATE statements that act across multiple
databases). It is also faster to check only the default database rather
than all databases if there is no need.
If you need cross-database updates to work, use
--replicate-wild-do-table=db_name.% instead. See Section 6.9, How
Servers Evaluate Replication Rules.
---
** _/ ** David Logan
*** _/ *** ITO Delivery Specialist - Database
*_/* Hewlett-Packard Australia Ltd
_/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED]
_/ _/ _/ _/ Desk: +61 8 8408 4273
_/ _/ _/_/_/ Mobile: +61 417 268 665
*_/ **
** _/ Postal: 148 Frome Street,
_/ ** Adelaide SA 5001
Australia
invent
---
-Original Message-
From: Atle Veka [mailto:[EMAIL PROTECTED]
Sent: Wednesday, 13 December 2006 10:55 AM
To: Ed Pauley II
Cc: mysql@lists.mysql.com
Subject: Re: Replication Problem?
Ed, this is unfortunately by design. Personally I don't get why this
choice was made...
Reference:
http://lists.mysql.com/mysql/190869
Regards,
Atle
FCI, Inc. - Unix Systems Administrator
On Tue, 12 Dec 2006, Ed Pauley II wrote:
We recently upgraded to MySQL 5.0. Since upgrading I have noticed that
queries of the form INSERT INTO test.test_table VALUES('test','1')
no
longer replicate. If you connect to or change to the test database and
then execute INSERT INTO test_table VALUES('test','1') the query
replicates. Is this normal behavior? Is there a configuration setting
that I can change to make replication accept explicit database naming
in
the query?
Any help would be greatly appreciated!
--
Ed Pauley II
[EMAIL PROTECTED]
--
Ed Pauley II
[EMAIL PROTECTED]
http://www.brisnet.com
http://www.brisbet.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication Problem? - SOLVED
It turns out that I have a binlog-ignore-db option configured for
another database. If you have even one of these you must make all
updates in the default database if you want them to be replicated. This
is a change in behavior from 4.0 to 4.1. We upgraded from 4.0 to 4.1 to
5.0 the same day which explains why we did not see this behavior sooner.
I removed the option from the config file and just used
replicate-ignore-db instead and all is right in the world again. Hope
this helps someone else down the road. Thanks for all of your helpful
information and suggestions.
http://dev.mysql.com/doc/refman/5.0/en/binary-log.html
-Ed
Logan, David (SST - Adelaide) wrote:
Hi Ed,
You may like to look at this, especially the last statement. If you are
not using these options however, I would suspect a bug.
Regards
--replicate-do-db=db_name
Tell the slave to restrict replication to statements where the default
database (that is, the one selected by USE) is db_name. To specify more
than one database, use this option multiple times, once for each
database. Note that this does not replicate cross-database statements
such as UPDATE some_db.some_table SET foo='bar' while having selected a
different database or no database.
An example of what does not work as you might expect: If the slave is
started with --replicate-do-db=sales and you issue the following
statements on the master, the UPDATE statement is not replicated:
USE prices;
UPDATE sales.january SET amount=amount+1000;
The main reason for this just check the default database behavior is
that it is difficult from the statement alone to know whether it should
be replicated (for example, if you are using multiple-table DELETE
statements or multiple-table UPDATE statements that act across multiple
databases). It is also faster to check only the default database rather
than all databases if there is no need.
If you need cross-database updates to work, use
--replicate-wild-do-table=db_name.% instead. See Section 6.9, How
Servers Evaluate Replication Rules.
---
** _/ ** David Logan
*** _/ *** ITO Delivery Specialist - Database
*_/* Hewlett-Packard Australia Ltd
_/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED]
_/ _/ _/ _/ Desk: +61 8 8408 4273
_/ _/ _/_/_/ Mobile: +61 417 268 665
*_/ **
** _/ Postal: 148 Frome Street,
_/ ** Adelaide SA 5001
Australia
invent
---
-Original Message-
From: Atle Veka [mailto:[EMAIL PROTECTED]
Sent: Wednesday, 13 December 2006 10:55 AM
To: Ed Pauley II
Cc: mysql@lists.mysql.com
Subject: Re: Replication Problem?
Ed, this is unfortunately by design. Personally I don't get why this
choice was made...
Reference:
http://lists.mysql.com/mysql/190869
Regards,
Atle
FCI, Inc. - Unix Systems Administrator
On Tue, 12 Dec 2006, Ed Pauley II wrote:
We recently upgraded to MySQL 5.0. Since upgrading I have noticed that
queries of the form INSERT INTO test.test_table VALUES('test','1')
no
longer replicate. If you connect to or change to the test database and
then execute INSERT INTO test_table VALUES('test','1') the query
replicates. Is this normal behavior? Is there a configuration setting
that I can change to make replication accept explicit database naming
in
the query?
Any help would be greatly appreciated!
--
Ed Pauley II
[EMAIL PROTECTED]
--
Ed Pauley II
Internet Software Developer
Bloodstock Research Information Services
859.223.
800.354.9206 ext. 297
[EMAIL PROTECTED]
http://www.brisnet.com
http://www.brisbet.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication Problem?
We recently upgraded to MySQL 5.0. Since upgrading I have noticed that
queries of the form INSERT INTO test.test_table VALUES('test','1') no
longer replicate. If you connect to or change to the test database and
then execute INSERT INTO test_table VALUES('test','1') the query
replicates. Is this normal behavior? Is there a configuration setting
that I can change to make replication accept explicit database naming in
the query?
Any help would be greatly appreciated!
--
Ed Pauley II
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication Problem?
Ed, this is unfortunately by design. Personally I don't get why this
choice was made...
Reference:
http://lists.mysql.com/mysql/190869
Regards,
Atle
FCI, Inc. - Unix Systems Administrator
On Tue, 12 Dec 2006, Ed Pauley II wrote:
We recently upgraded to MySQL 5.0. Since upgrading I have noticed that
queries of the form INSERT INTO test.test_table VALUES('test','1') no
longer replicate. If you connect to or change to the test database and
then execute INSERT INTO test_table VALUES('test','1') the query
replicates. Is this normal behavior? Is there a configuration setting
that I can change to make replication accept explicit database naming in
the query?
Any help would be greatly appreciated!
--
Ed Pauley II
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Replication Problem?
Hi Ed,
You may like to look at this, especially the last statement. If you are
not using these options however, I would suspect a bug.
Regards
--replicate-do-db=db_name
Tell the slave to restrict replication to statements where the default
database (that is, the one selected by USE) is db_name. To specify more
than one database, use this option multiple times, once for each
database. Note that this does not replicate cross-database statements
such as UPDATE some_db.some_table SET foo='bar' while having selected a
different database or no database.
An example of what does not work as you might expect: If the slave is
started with --replicate-do-db=sales and you issue the following
statements on the master, the UPDATE statement is not replicated:
USE prices;
UPDATE sales.january SET amount=amount+1000;
The main reason for this just check the default database behavior is
that it is difficult from the statement alone to know whether it should
be replicated (for example, if you are using multiple-table DELETE
statements or multiple-table UPDATE statements that act across multiple
databases). It is also faster to check only the default database rather
than all databases if there is no need.
If you need cross-database updates to work, use
--replicate-wild-do-table=db_name.% instead. See Section 6.9, How
Servers Evaluate Replication Rules.
---
** _/ ** David Logan
*** _/ *** ITO Delivery Specialist - Database
*_/* Hewlett-Packard Australia Ltd
_/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED]
_/ _/ _/ _/ Desk: +61 8 8408 4273
_/ _/ _/_/_/ Mobile: +61 417 268 665
*_/ **
** _/ Postal: 148 Frome Street,
_/ ** Adelaide SA 5001
Australia
invent
---
-Original Message-
From: Atle Veka [mailto:[EMAIL PROTECTED]
Sent: Wednesday, 13 December 2006 10:55 AM
To: Ed Pauley II
Cc: mysql@lists.mysql.com
Subject: Re: Replication Problem?
Ed, this is unfortunately by design. Personally I don't get why this
choice was made...
Reference:
http://lists.mysql.com/mysql/190869
Regards,
Atle
FCI, Inc. - Unix Systems Administrator
On Tue, 12 Dec 2006, Ed Pauley II wrote:
We recently upgraded to MySQL 5.0. Since upgrading I have noticed that
queries of the form INSERT INTO test.test_table VALUES('test','1')
no
longer replicate. If you connect to or change to the test database and
then execute INSERT INTO test_table VALUES('test','1') the query
replicates. Is this normal behavior? Is there a configuration setting
that I can change to make replication accept explicit database naming
in
the query?
Any help would be greatly appreciated!
--
Ed Pauley II
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
replication problem if network delay
Hi All, I need small help from you. I am using Replication, i am using this technique to give live support to Support team. But my Server and Slave are not in LAN. So some times i am getting network delay. Can anybody tell me a good suggestion to over come this. regards, balaraju
RE: Replication Problem
Dave, I tried reset slave as you mentioned - no change. Nothing updating. Same error. Brett Aah, sorry, I didn't explain myself very well 8-( At the mysql prompt RESET SLAVE; This tells the slave to reset itself back, and forget the current settings (eg. the log position etc., not the master or such details) to where the master started and begin replication from there. There is also a complementary RESET MASTER which tells the master to get rid of all binary logs and start again. RESET SLAVE is documented here http://dev.mysql.com/doc/refman/5.0/en/reset-slave.html Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Brett Harvey [mailto:[EMAIL PROTECTED] Sent: Monday, 24 July 2006 1:49 PM To: mysql@lists.mysql.com Subject: RE: Replication Problem Hello David, Reset how? I did the CHANGE MASTER TO and set it as what was specified from the Show Master command after I tar'd the files. Thanks! Brett Hi Brett, Did you reset the master and/or slave? Looking at the logs, it appears as though the slave is trying to read from a different position in the log than the master has reached. Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Brett Harvey [mailto:[EMAIL PROTECTED] Sent: Monday, 24 July 2006 1:36 PM To: mysql@lists.mysql.com Subject: Re: Replication Problem Hi, Thanks for the response. Yes - I have that in my config also. replicate-do-db=FMS Thanks Brett Hi, Did u mention what all databases has to be replicated in ur cnf file as (replicate-do-db=database-name). Thanks Regards Dilipkumar -- --- - /Brett C. Harvey; /Creative-Pages.Net, President; /Facility Management Systems, CTO (www.fmsystems.biz); /Lasso Partner Association Member ID #LPA135259 (www.omnipilot.com/www.lassopartner.com); --- - -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- /Brett C. Harvey; /Creative-Pages.Net, President; /Facility Management Systems, CTO (www.fmsystems.biz); /Lasso Partner Association Member ID #LPA135259 (www.omnipilot.com/www.lassopartner.com); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- /Brett C. Harvey; /Creative-Pages.Net, President; /Facility Management Systems, CTO (www.fmsystems.biz); /Lasso Partner Association Member ID #LPA135259 (www.omnipilot.com/www.lassopartner.com); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Replication Problem
Hi Brett, Can you please do the following : SHOW MASTER STATUS; (on the master) and SHOW SLAVE STATUS; (on the slave) Thanks P.S I will be going to bed soon so don't worry if you don't hear anything, I'll look at it first thing in the morning. --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Brett Harvey [mailto:[EMAIL PROTECTED] Sent: Monday, 24 July 2006 9:28 PM To: mysql@lists.mysql.com Subject: RE: Replication Problem Dave, I tried reset slave as you mentioned - no change. Nothing updating. Same error. Brett Aah, sorry, I didn't explain myself very well 8-( At the mysql prompt RESET SLAVE; This tells the slave to reset itself back, and forget the current settings (eg. the log position etc., not the master or such details) to where the master started and begin replication from there. There is also a complementary RESET MASTER which tells the master to get rid of all binary logs and start again. RESET SLAVE is documented here http://dev.mysql.com/doc/refman/5.0/en/reset-slave.html Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Brett Harvey [mailto:[EMAIL PROTECTED] Sent: Monday, 24 July 2006 1:49 PM To: mysql@lists.mysql.com Subject: RE: Replication Problem Hello David, Reset how? I did the CHANGE MASTER TO and set it as what was specified from the Show Master command after I tar'd the files. Thanks! Brett Hi Brett, Did you reset the master and/or slave? Looking at the logs, it appears as though the slave is trying to read from a different position in the log than the master has reached. Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Brett Harvey [mailto:[EMAIL PROTECTED] Sent: Monday, 24 July 2006 1:36 PM To: mysql@lists.mysql.com Subject: Re: Replication Problem Hi, Thanks for the response. Yes - I have that in my config also. replicate-do-db=FMS Thanks Brett Hi, Did u mention what all databases has to be replicated in ur cnf file as (replicate-do-db=database-name). Thanks Regards Dilipkumar -- -- - - /Brett C. Harvey; /Creative-Pages.Net, President; /Facility Management Systems, CTO (www.fmsystems.biz); /Lasso Partner Association Member ID #LPA135259 (www.omnipilot.com/www.lassopartner.com); -- - - -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- --- - /Brett C. Harvey; /Creative-Pages.Net, President; /Facility Management Systems, CTO (www.fmsystems.biz); /Lasso Partner Association Member ID #LPA135259 (www.omnipilot.com/www.lassopartner.com); --- - -- MySQL General Mailing List For list archives: http
RE: Replication Problem
David, mysql show master status\g +---+--+--+--+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +---+--+--+--+ | FMSweb-bin.01 | 903763 | | | +---+--+--+--+ 1 row in set (0.00 sec) mysql show slave status\G *** 1. row *** Slave_IO_State: Waiting for master to send event Master_Host: xxx.xxx.xxx.xxx Master_User: FMSReplicate Master_Port: 3306 Connect_Retry: 60 Master_Log_File: FMSweb-bin.01 Read_Master_Log_Pos: 903763 Relay_Log_File: FMSbuilding-relay-bin.04 Relay_Log_Pos: 901275 Relay_Master_Log_File: FMSweb-bin.01 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: FMS 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: 903763 Relay_Log_Space: 901275 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) Gnite to you! lol I just got up... Thanks! Brett Hi Brett, Can you please do the following : SHOW MASTER STATUS; (on the master) and SHOW SLAVE STATUS; (on the slave) Thanks P.S I will be going to bed soon so don't worry if you don't hear anything, I'll look at it first thing in the morning. -- /Brett C. Harvey; /Creative-Pages.Net, President; /Facility Management Systems, CTO (www.fmsystems.biz); /Lasso Partner Association Member ID #LPA135259 (www.omnipilot.com/www.lassopartner.com);
RE: Replication Problem
Hi Brett, Just about to go when your email rocked up 8-) Like you, I can't see any reason why it is not replicating according to the logs. Have you tried to create a database on the master and see if it appears on the slave? What commands are being replicated at this time? You can use the mysqlbinlog command to check the binary log to see what it is trying to replicate. There are certain commands that are not replicated and we want to eliminate those from the equation. Try the create database command eg. On master CREATE DATABASE THING; On Slave SHOW DATABASES; You should see the database thing on your slave. You can then drop the database on the master and it should disappear. Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Brett Harvey [mailto:[EMAIL PROTECTED] Sent: Monday, 24 July 2006 9:51 PM To: mysql@lists.mysql.com Subject: RE: Replication Problem David, mysql show master status\g +---+--+--+--+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +---+--+--+--+ | FMSweb-bin.01 | 903763 | | | +---+--+--+--+ 1 row in set (0.00 sec) mysql show slave status\G *** 1. row *** Slave_IO_State: Waiting for master to send event Master_Host: xxx.xxx.xxx.xxx Master_User: FMSReplicate Master_Port: 3306 Connect_Retry: 60 Master_Log_File: FMSweb-bin.01 Read_Master_Log_Pos: 903763 Relay_Log_File: FMSbuilding-relay-bin.04 Relay_Log_Pos: 901275 Relay_Master_Log_File: FMSweb-bin.01 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: FMS 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: 903763 Relay_Log_Space: 901275 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) Gnite to you! lol I just got up... Thanks! Brett Hi Brett, Can you please do the following : SHOW MASTER STATUS; (on the master) and SHOW SLAVE STATUS; (on the slave) Thanks P.S I will be going to bed soon so don't worry if you don't hear anything, I'll look at it first thing in the morning. -- /Brett C. Harvey; /Creative-Pages.Net, President; /Facility Management Systems, CTO (www.fmsystems.biz); /Lasso Partner Association Member ID #LPA135259 (www.omnipilot.com/www.lassopartner.com); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Replication Problem
David, I could try and create a table. But I've got it set just for the one database FMS. The error log, last night, was showing this (I think I had it in my first post) 060723 21:01:11 [ERROR] Error reading packet from server: Lost connection to MySQL server during query ( server_errno=2013) 060723 21:01:11 [Note] Slave I/O thread killed while reading event 060723 21:01:11 [Note] Slave I/O thread exiting, read up to log 'FMSweb-bin.04', position 349828 060723 21:01:11 [Note] Error reading relay log event: slave SQL thread was killed The two are on the same network, same switch in fact. I can do commands from one to the other without problem. The logs seem to update in that sense but when looking at the data - no go. I'll check binlogs on the slave. Brett Just about to go when your email rocked up 8-) Like you, I can't see any reason why it is not replicating according to the logs. Have you tried to create a database on the master and see if it appears on the slave? What commands are being replicated at this time? You can use the mysqlbinlog command to check the binary log to see what it is trying to replicate. There are certain commands that are not replicated and we want to eliminate those from the equation. Try the create database command eg. On master CREATE DATABASE THING; On Slave SHOW DATABASES; You should see the database thing on your slave. You can then drop the database on the master and it should disappear. Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Brett Harvey [mailto:[EMAIL PROTECTED] Sent: Monday, 24 July 2006 9:51 PM To: mysql@lists.mysql.com Subject: RE: Replication Problem David, mysql show master status\g +---+--+--+--+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +---+--+--+--+ | FMSweb-bin.01 | 903763 | | | +---+--+--+--+ 1 row in set (0.00 sec) mysql show slave status\G *** 1. row *** Slave_IO_State: Waiting for master to send event Master_Host: xxx.xxx.xxx.xxx Master_User: FMSReplicate Master_Port: 3306 Connect_Retry: 60 Master_Log_File: FMSweb-bin.01 Read_Master_Log_Pos: 903763 Relay_Log_File: FMSbuilding-relay-bin.04 Relay_Log_Pos: 901275 Relay_Master_Log_File: FMSweb-bin.01 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: FMS 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: 903763 Relay_Log_Space: 901275 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) Gnite to you! lol I just got up... Thanks! Brett Hi Brett, Can you please do the following : SHOW MASTER STATUS; (on the master) and SHOW SLAVE STATUS; (on the slave) Thanks P.S I will be going to bed soon so don't worry if you don't hear anything, I'll look at it first thing in the morning. -- /Brett C. Harvey; /Creative-Pages.Net, President; /Facility Management Systems, CTO (www.fmsystems.biz); /Lasso Partner Association Member ID #LPA135259 (www.omnipilot.com/www.lassopartner.com); -- /Brett C. Harvey; /Creative-Pages.Net, President; /Facility Management Systems, CTO (www.fmsystems.biz); /Lasso Partner Association Member ID #LPA135259 (www.omnipilot.com/www.lassopartner.com); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Replication Problem
Hi Brett, I am a little bit lost as to why this is happening. The only suggestion I have at this point is to go through the replication troubleshooting in this chapter and see if something there is causing the problem. Looking at what you have done and the docs, I don't think this will help. This may be a bug. Sorry I can't be of more assistance. http://dev.mysql.com/doc/refman/5.0/en/replication.html I did find this in the bug system, you may be able to either re-open it or log a new one http://bugs.mysql.com/bug.php?id=10157 Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Brett Harvey [mailto:[EMAIL PROTECTED] Sent: Monday, 24 July 2006 11:06 PM To: mysql@lists.mysql.com Subject: RE: Replication Problem David, I could try and create a table. But I've got it set just for the one database FMS. The error log, last night, was showing this (I think I had it in my first post) 060723 21:01:11 [ERROR] Error reading packet from server: Lost connection to MySQL server during query ( server_errno=2013) 060723 21:01:11 [Note] Slave I/O thread killed while reading event 060723 21:01:11 [Note] Slave I/O thread exiting, read up to log 'FMSweb-bin.04', position 349828 060723 21:01:11 [Note] Error reading relay log event: slave SQL thread was killed The two are on the same network, same switch in fact. I can do commands from one to the other without problem. The logs seem to update in that sense but when looking at the data - no go. I'll check binlogs on the slave. Brett Just about to go when your email rocked up 8-) Like you, I can't see any reason why it is not replicating according to the logs. Have you tried to create a database on the master and see if it appears on the slave? What commands are being replicated at this time? You can use the mysqlbinlog command to check the binary log to see what it is trying to replicate. There are certain commands that are not replicated and we want to eliminate those from the equation. Try the create database command eg. On master CREATE DATABASE THING; On Slave SHOW DATABASES; You should see the database thing on your slave. You can then drop the database on the master and it should disappear. Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Brett Harvey [mailto:[EMAIL PROTECTED] Sent: Monday, 24 July 2006 9:51 PM To: mysql@lists.mysql.com Subject: RE: Replication Problem David, mysql show master status\g +---+--+--+--+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +---+--+--+--+ | FMSweb-bin.01 | 903763 | | | +---+--+--+--+ 1 row in set (0.00 sec) mysql show slave status\G *** 1. row *** Slave_IO_State: Waiting for master to send event Master_Host: xxx.xxx.xxx.xxx Master_User: FMSReplicate Master_Port: 3306 Connect_Retry: 60 Master_Log_File: FMSweb-bin.01 Read_Master_Log_Pos: 903763 Relay_Log_File: FMSbuilding-relay-bin.04 Relay_Log_Pos: 901275 Relay_Master_Log_File: FMSweb-bin.01 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: FMS 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: 903763 Relay_Log_Space: 901275 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
RE: Replication Problem
David, Thanks for attempting to help. When I look at my show slave status, its updating. When I look at the FMSbuilding-relay-bin, its updating! It has the information that's taken place on the master server. I can see information in the FMSbuilding-relay-bin on the slave server that I did on the master. But, the databases/tables/data on the slave are not being updated. Brett I am a little bit lost as to why this is happening. The only suggestion I have at this point is to go through the replication troubleshooting in this chapter and see if something there is causing the problem. Looking at what you have done and the docs, I don't think this will help. This may be a bug. Sorry I can't be of more assistance. http://dev.mysql.com/doc/refman/5.0/en/replication.html I did find this in the bug system, you may be able to either re-open it or log a new one http://bugs.mysql.com/bug.php?id=10157 -- /Brett C. Harvey; /Creative-Pages.Net, President; /Facility Management Systems, CTO (www.fmsystems.biz); /Lasso Partner Association Member ID #LPA135259 (www.omnipilot.com/www.lassopartner.com); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Replication Problem
Hi Brett, I am absolutely baffled as to how the binlog/relaylog can be updated but the dbs/tables etc. are not being touched. The only other thing is to check the datadir on your server. Is it actually pointing to where you think it is? You can do a SHOW VARIABLES LIKE 'datadir'; and check it is pointing to where you think it should be. Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Brett Harvey [mailto:[EMAIL PROTECTED] Sent: Tuesday, 25 July 2006 9:19 AM To: mysql@lists.mysql.com Subject: RE: Replication Problem David, Thanks for attempting to help. When I look at my show slave status, its updating. When I look at the FMSbuilding-relay-bin, its updating! It has the information that's taken place on the master server. I can see information in the FMSbuilding-relay-bin on the slave server that I did on the master. But, the databases/tables/data on the slave are not being updated. Brett I am a little bit lost as to why this is happening. The only suggestion I have at this point is to go through the replication troubleshooting in this chapter and see if something there is causing the problem. Looking at what you have done and the docs, I don't think this will help. This may be a bug. Sorry I can't be of more assistance. http://dev.mysql.com/doc/refman/5.0/en/replication.html I did find this in the bug system, you may be able to either re-open it or log a new one http://bugs.mysql.com/bug.php?id=10157 -- /Brett C. Harvey; /Creative-Pages.Net, President; /Facility Management Systems, CTO (www.fmsystems.biz); /Lasso Partner Association Member ID #LPA135259 (www.omnipilot.com/www.lassopartner.com); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Replication Problem
David, It is pointing correctly. Plus, I can use a mysql browser and 3rd party tools on the slave server and read the databases/tables properly. I've tried stopping and restarting. I've rebooted... I'm baffled too, obviously. lol Thanks! Brett I am absolutely baffled as to how the binlog/relaylog can be updated but the dbs/tables etc. are not being touched. The only other thing is to check the datadir on your server. Is it actually pointing to where you think it is? You can do a SHOW VARIABLES LIKE 'datadir'; and check it is pointing to where you think it should be. -- /Brett C. Harvey; /Creative-Pages.Net, President; /Facility Management Systems, CTO (www.fmsystems.biz); /Lasso Partner Association Member ID #LPA135259 (www.omnipilot.com/www.lassopartner.com); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Replication Problem
Another suggestion, look for other files on the system ending in .MYD or put a query through that you know has updated a particular table/database and do a find looking for a file that has been updated in the last 5 minutes. See which ones have. Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Brett Harvey [mailto:[EMAIL PROTECTED] Sent: Tuesday, 25 July 2006 10:18 AM To: mysql@lists.mysql.com Subject: RE: Replication Problem David, It is pointing correctly. Plus, I can use a mysql browser and 3rd party tools on the slave server and read the databases/tables properly. I've tried stopping and restarting. I've rebooted... I'm baffled too, obviously. lol Thanks! Brett I am absolutely baffled as to how the binlog/relaylog can be updated but the dbs/tables etc. are not being touched. The only other thing is to check the datadir on your server. Is it actually pointing to where you think it is? You can do a SHOW VARIABLES LIKE 'datadir'; and check it is pointing to where you think it should be. -- /Brett C. Harvey; /Creative-Pages.Net, President; /Facility Management Systems, CTO (www.fmsystems.biz); /Lasso Partner Association Member ID #LPA135259 (www.omnipilot.com/www.lassopartner.com); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Replication Problem
David, that error is only when doing a stop/shutdown. So it doesnt make a lot of sense that would be the problem. Here's the log where I did a stop and start. 060724 20:54:25 [Note] C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld-nt: Normal shutdown 060724 20:54:25 [ERROR] Error reading packet from server: Lost connection to MySQL server during query ( server_errno=2013) 060724 20:54:25 [Note] Slave I/O thread killed while reading event 060724 20:54:25 [Note] Slave I/O thread exiting, read up to log 'FMSweb-bin.01', position 5582202 060724 20:54:25 [Note] Error reading relay log event: slave SQL thread was killed 060724 20:54:28 [Warning] C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld-nt: Forcing close of thread 6 user: 'bharvey' 060724 20:54:28 [Warning] C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld-nt: Forcing close of thread 5 user: 'bharvey' 060724 20:54:28 InnoDB: Starting shutdown... 060724 20:54:29 InnoDB: Shutdown completed; log sequence number 0 44044 060724 20:54:29 [Note] C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld-nt: Shutdown complete 060724 20:55:10 InnoDB: Started; log sequence number 0 44044 060724 20:55:10 [Note] C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld-nt: ready for connections. Version: '5.0.22-community-nt-log' socket: '' port: 3306 MySQL Community Edition (GPL) 060724 20:55:10 [Note] Slave SQL thread initialized, starting replication in log 'FMSweb-bin.01' at position 5582202, relay log '.\FMSbuilding-relay-bin.05' position: 17820 060724 20:55:10 [Note] Slave I/O thread: connected to master '[EMAIL PROTECTED]:3306', replication started in log 'FMSweb-bin.01' at position 5582202 I did some manual inserts into the database on the slave, just to make sure there was no problem - those worked fine and were searchable. Brett Are there any other messages apart from the 2013 error? Are there any options on the filesystem that are perhaps stopping any writing by the mysql user? At this point, I have to say I'm just about out of ideas. Regards -- /Brett C. Harvey; /Creative-Pages.Net, President; /Facility Management Systems, CTO (www.fmsystems.biz); /Lasso Partner Association Member ID #LPA135259 (www.omnipilot.com/www.lassopartner.com); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication Problem
Well, I successfully setup replication for one server/slave. Now I'm trying to do the same for another set. I've tried both the tar method and load data from master and can't seem to get either to work. Nothing replicates to the slave. I'm running Mysql 5.022. === My error log is showing this - note the error reading packet from server. 060723 21:01:01 InnoDB: Started; log sequence number 0 44044 060723 21:01:01 [Note] Slave SQL thread initialized, starting replication in log 'FMSweb-bin.04' at position 349828, relay log '.\FMSbuilding-relay-bin.06' position: 236 060723 21:01:01 [Note] Slave I/O thread: connected to master '[EMAIL PROTECTED]:3306', replication started in log 'FMSweb-bin.04' at position 349828 060723 21:01:01 [Note] C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld-nt: ready for connections. Version: '5.0.21-community-nt-log' socket: '' port: 3306 MySQL Community Edition (GPL) 060723 21:01:11 [ERROR] Error reading packet from server: Lost connection to MySQL server during query ( server_errno=2013) 060723 21:01:11 [Note] Slave I/O thread killed while reading event 060723 21:01:11 [Note] Slave I/O thread exiting, read up to log 'FMSweb-bin.04', position 349828 060723 21:01:11 [Note] Error reading relay log event: slave SQL thread was killed 060723 21:01:41 [Note] Slave SQL thread initialized, starting replication in log 'FMSweb-bin.04' at position 98, relay log '.\FMSbuilding-relay-bin.01' position: 4 060723 21:01:41 [Note] Slave I/O thread: connected to master '[EMAIL PROTECTED]:3306', replication started in log 'FMSweb-bin.04' at position 98 After taring my Master Status was this: mysql SHOW MASTER STATUS; +---+--+--+--+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +---+--+--+--+ | FMSweb-bin.04 | 98 | | | +---+--+--+--+ 1 row in set (0.00 sec) Upon doing a Start Slave I had his for Slave Status show slave status\G *** 1. row *** Slave_IO_State: Waiting for master to send event Master_Host: 206.103.218.36 Master_User: FMSReplicate Master_Port: 3306 Connect_Retry: 60 Master_Log_File: FMSweb-bin.04 Read_Master_Log_Pos: 349828 Relay_Log_File: FMSbuilding-relay-bin.02 Relay_Log_Pos: 349966 Relay_Master_Log_File: FMSweb-bin.04 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: FMS 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: 349828 Relay_Log_Space: 349966 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) My Master Status is mysql show master status\g +---+--+--+--+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +---+--+--+--+ | FMSweb-bin.04 | 349828 | | | +---+--+--+--+ 1 row in set (0.00 sec) === My Master Config is server-id=1 My Salve Config is server-id=2 replicate-do-db=FMS master-host=xxx.xxx.xxx.xxx master-user=FMSReplicate master-password=password relay-log=FMSbuilding-relay-bin log-bin=FMSbuilding-bin I did: CHANGE MASTER TO - MASTER_HOST='xxx.xxx.xxx.xxx', - MASTER_USER='TenenzReplicate', - MASTER_PASSWORD='password', - MASTER_LOG_FILE='FMSweb-bin.04', - MASTER_LOG_POS=98; any ideas? how do I get it to replicate properly? Thanks! -- /Brett C. Harvey; /Creative-Pages.Net, President; /Facility Management Systems, CTO (www.fmsystems.biz); /Lasso Partner Association Member ID #LPA135259 (www.omnipilot.com/www.lassopartner.com);
Re: Replication Problem
Hi, Did u mention what all databases has to be replicated in ur cnf file as (replicate-do-db=database-name). Thanks Regards Dilipkumar - Original Message - From: Brett Harvey [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, July 24, 2006 8:49 AM Subject: Replication Problem Well, I successfully setup replication for one server/slave. Now I'm trying to do the same for another set. I've tried both the tar method and load data from master and can't seem to get either to work. Nothing replicates to the slave. I'm running Mysql 5.022. === My error log is showing this - note the error reading packet from server. 060723 21:01:01 InnoDB: Started; log sequence number 0 44044 060723 21:01:01 [Note] Slave SQL thread initialized, starting replication in log 'FMSweb-bin.04' at position 349828, relay log '.\FMSbuilding-relay-bin.06' position: 236 060723 21:01:01 [Note] Slave I/O thread: connected to master '[EMAIL PROTECTED]:3306', replication started in log 'FMSweb-bin.04' at position 349828 060723 21:01:01 [Note] C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld-nt: ready for connections. Version: '5.0.21-community-nt-log' socket: '' port: 3306 MySQL Community Edition (GPL) 060723 21:01:11 [ERROR] Error reading packet from server: Lost connection to MySQL server during query ( server_errno=2013) 060723 21:01:11 [Note] Slave I/O thread killed while reading event 060723 21:01:11 [Note] Slave I/O thread exiting, read up to log 'FMSweb-bin.04', position 349828 060723 21:01:11 [Note] Error reading relay log event: slave SQL thread was killed 060723 21:01:41 [Note] Slave SQL thread initialized, starting replication in log 'FMSweb-bin.04' at position 98, relay log '.\FMSbuilding-relay-bin.01' position: 4 060723 21:01:41 [Note] Slave I/O thread: connected to master '[EMAIL PROTECTED]:3306', replication started in log 'FMSweb-bin.04' at position 98 After taring my Master Status was this: mysql SHOW MASTER STATUS; +---+--+--+--+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +---+--+--+--+ | FMSweb-bin.04 | 98 | | | +---+--+--+--+ 1 row in set (0.00 sec) Upon doing a Start Slave I had his for Slave Status show slave status\G *** 1. row *** Slave_IO_State: Waiting for master to send event Master_Host: 206.103.218.36 Master_User: FMSReplicate Master_Port: 3306 Connect_Retry: 60 Master_Log_File: FMSweb-bin.04 Read_Master_Log_Pos: 349828 Relay_Log_File: FMSbuilding-relay-bin.02 Relay_Log_Pos: 349966 Relay_Master_Log_File: FMSweb-bin.04 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: FMS 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: 349828 Relay_Log_Space: 349966 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) My Master Status is mysql show master status\g +---+--+--+--+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +---+--+--+--+ | FMSweb-bin.04 | 349828 | | | +---+--+--+--+ 1 row in set (0.00 sec) === My Master Config is server-id=1 My Salve Config is server-id=2 replicate-do-db=FMS master-host=xxx.xxx.xxx.xxx master-user=FMSReplicate master-password=password relay-log=FMSbuilding-relay-bin log-bin=FMSbuilding-bin I did: CHANGE MASTER TO - MASTER_HOST='xxx.xxx.xxx.xxx', - MASTER_USER='TenenzReplicate', - MASTER_PASSWORD='password', - MASTER_LOG_FILE='FMSweb-bin.04', - MASTER_LOG_POS=98; any ideas? how do I get it to replicate properly? Thanks! -- /Brett C. Harvey; /Creative-Pages.Net, President; /Facility Management Systems, CTO (www.fmsystems.biz); /Lasso Partner Association Member ID #LPA135259 (www.omnipilot.com/www.lassopartner.com); ** DISCLAIMER ** Information contained and transmitted by this E-MAIL is proprietary to Sify Limited and is intended for use only by the individual or entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under
Re: Replication Problem
Hi, Thanks for the response. Yes - I have that in my config also. replicate-do-db=FMS Thanks Brett Hi, Did u mention what all databases has to be replicated in ur cnf file as (replicate-do-db=database-name). Thanks Regards Dilipkumar -- /Brett C. Harvey; /Creative-Pages.Net, President; /Facility Management Systems, CTO (www.fmsystems.biz); /Lasso Partner Association Member ID #LPA135259 (www.omnipilot.com/www.lassopartner.com); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Replication Problem
Hi Brett, Did you reset the master and/or slave? Looking at the logs, it appears as though the slave is trying to read from a different position in the log than the master has reached. Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Brett Harvey [mailto:[EMAIL PROTECTED] Sent: Monday, 24 July 2006 1:36 PM To: mysql@lists.mysql.com Subject: Re: Replication Problem Hi, Thanks for the response. Yes - I have that in my config also. replicate-do-db=FMS Thanks Brett Hi, Did u mention what all databases has to be replicated in ur cnf file as (replicate-do-db=database-name). Thanks Regards Dilipkumar -- /Brett C. Harvey; /Creative-Pages.Net, President; /Facility Management Systems, CTO (www.fmsystems.biz); /Lasso Partner Association Member ID #LPA135259 (www.omnipilot.com/www.lassopartner.com); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication Problem
Hi, Check whether u have starting with the correct master position from slave. Thanks Regards Dilipkumar - Original Message - From: Brett Harvey [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, July 24, 2006 9:36 AM Subject: Re: Replication Problem Hi, Thanks for the response. Yes - I have that in my config also. replicate-do-db=FMS Thanks Brett Hi, Did u mention what all databases has to be replicated in ur cnf file as (replicate-do-db=database-name). Thanks Regards Dilipkumar -- /Brett C. Harvey; /Creative-Pages.Net, President; /Facility Management Systems, CTO (www.fmsystems.biz); /Lasso Partner Association Member ID #LPA135259 (www.omnipilot.com/www.lassopartner.com); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] ** DISCLAIMER ** Information contained and transmitted by this E-MAIL is proprietary to Sify Limited and is intended for use only by the individual or entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If this is a forwarded message, the content of this E-MAIL may not have been sent with the authority of the Company. If you are not the intended recipient, an agent of the intended recipient or a person responsible for delivering the information to the named recipient, you are notified that any use, distribution, transmission, printing, copying or dissemination of this information in any way or in any manner is strictly prohibited. If you have received this communication in error, please delete this mail notify us immediately at [EMAIL PROTECTED] Watch the latest updates on Mumbai, with video coverage of news, events, Bollywood, live darshan from Siddhivinayak temple and more, only on www.mumbailive.in Watch the hottest videos from Bollywood, Fashion, News and more only on www.sifymax.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Replication Problem
Hello David, Reset how? I did the CHANGE MASTER TO and set it as what was specified from the Show Master command after I tar'd the files. Thanks! Brett Hi Brett, Did you reset the master and/or slave? Looking at the logs, it appears as though the slave is trying to read from a different position in the log than the master has reached. Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Brett Harvey [mailto:[EMAIL PROTECTED] Sent: Monday, 24 July 2006 1:36 PM To: mysql@lists.mysql.com Subject: Re: Replication Problem Hi, Thanks for the response. Yes - I have that in my config also. replicate-do-db=FMS Thanks Brett Hi, Did u mention what all databases has to be replicated in ur cnf file as (replicate-do-db=database-name). Thanks Regards Dilipkumar -- /Brett C. Harvey; /Creative-Pages.Net, President; /Facility Management Systems, CTO (www.fmsystems.biz); /Lasso Partner Association Member ID #LPA135259 (www.omnipilot.com/www.lassopartner.com); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- /Brett C. Harvey; /Creative-Pages.Net, President; /Facility Management Systems, CTO (www.fmsystems.biz); /Lasso Partner Association Member ID #LPA135259 (www.omnipilot.com/www.lassopartner.com); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Replication Problem
Aah, sorry, I didn't explain myself very well 8-( At the mysql prompt RESET SLAVE; This tells the slave to reset itself back, and forget the current settings (eg. the log position etc., not the master or such details) to where the master started and begin replication from there. There is also a complementary RESET MASTER which tells the master to get rid of all binary logs and start again. RESET SLAVE is documented here http://dev.mysql.com/doc/refman/5.0/en/reset-slave.html Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Brett Harvey [mailto:[EMAIL PROTECTED] Sent: Monday, 24 July 2006 1:49 PM To: mysql@lists.mysql.com Subject: RE: Replication Problem Hello David, Reset how? I did the CHANGE MASTER TO and set it as what was specified from the Show Master command after I tar'd the files. Thanks! Brett Hi Brett, Did you reset the master and/or slave? Looking at the logs, it appears as though the slave is trying to read from a different position in the log than the master has reached. Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Brett Harvey [mailto:[EMAIL PROTECTED] Sent: Monday, 24 July 2006 1:36 PM To: mysql@lists.mysql.com Subject: Re: Replication Problem Hi, Thanks for the response. Yes - I have that in my config also. replicate-do-db=FMS Thanks Brett Hi, Did u mention what all databases has to be replicated in ur cnf file as (replicate-do-db=database-name). Thanks Regards Dilipkumar -- --- - /Brett C. Harvey; /Creative-Pages.Net, President; /Facility Management Systems, CTO (www.fmsystems.biz); /Lasso Partner Association Member ID #LPA135259 (www.omnipilot.com/www.lassopartner.com); --- - -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- /Brett C. Harvey; /Creative-Pages.Net, President; /Facility Management Systems, CTO (www.fmsystems.biz); /Lasso Partner Association Member ID #LPA135259 (www.omnipilot.com/www.lassopartner.com); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication problem
We are running 4.1.13 standard on Linux as a master, and 4.1.14 on NT4
as a slave.
Replication seems to work extremely well, except in the following
circumstance.
On the master server, running a query similar to:
insert into zmast.leagueinfo
(countieslist, defaultleaguecode, leaguename ...)
select
countieslist, 'DDLS2005', leaguename ..
from zmast.leagueinfo
where defaultleaguecode='ddls2004'.
using the same table as source and target, with the only changes being
the defaultleaguecode and the autoincrement ID field, without any problem.
However, this does not seem to replicate to the slave, and no error shows
until later when an attempt is made to insert a record into a table where
a relationship to this record is required ('Cannot update child
record'-type message).
The manual that I have does specify that currently you cannot insert into
a table and select from the same table in a subquery. Clearly this is not
actually the case, but for some reason, it will not replicate.
Anyone else had/got this problem?
Is there a sensible solution - the only one I can come up with is changing
the intitial 'insert ... select' into a 'create table temp select
from...'/'insert ... select from temp'/ 'drop table temp' set.
This insert ... select stuff is not done on a regular basis, and then only
by an administrator.
Ideas welcome
Cheers
Terry
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication problem
Hello.
However, this does not seem to replicate to the slave, and no error shows
Have you compared the slave's relay logs and master's binary logs? Does
this query present in both logs? See:
http://dev.mysql.com/doc/mysql/en/slave-logs.html
http://dev.mysql.com/doc/mysql/en/Binary_log.html
http://dev.mysql.com/doc/mysql/en/mysqlbinlog.html
Terry Riley [EMAIL PROTECTED] wrote:
We are running 4.1.13 standard on Linux as a master, and 4.1.14 on NT4
as a slave.
Replication seems to work extremely well, except in the following
circumstance.
On the master server, running a query similar to:
insert into zmast.leagueinfo
(countieslist, defaultleaguecode, leaguename ...)
select
countieslist, 'DDLS2005', leaguename ..
from zmast.leagueinfo
where defaultleaguecode='ddls2004'.
using the same table as source and target, with the only changes being
the defaultleaguecode and the autoincrement ID field, without any problem.
However, this does not seem to replicate to the slave, and no error shows
until later when an attempt is made to insert a record into a table where
a relationship to this record is required ('Cannot update child
record'-type message).
The manual that I have does specify that currently you cannot insert into
a table and select from the same table in a subquery. Clearly this is not
actually the case, but for some reason, it will not replicate.
Anyone else had/got this problem?
Is there a sensible solution - the only one I can come up with is changing
the intitial 'insert ... select' into a 'create table temp select
from...'/'insert ... select from temp'/ 'drop table temp' set.
This insert ... select stuff is not done on a regular basis, and then only
by an administrator.
Ideas welcome
Cheers
Terry
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication problem
- Original Message -
However, this does not seem to replicate to the slave, and no error
shows
Have you compared the slave's relay logs and master's binary logs? Does
this query present in both logs?
The hint was what I needed, Gleb. Spasibo. It turns out that the
administrator doing this is using an elderly version of SQLyog to run his
scripts, and either by accident or design, SQLyog doesn't make entries in
the master bin-log - so no replication is ever going to happen. The query
(actually a set of queries) don't appear in logs on either server.
This GUI has been in use for over a year, but we only switched on the
replication system a couple of weeks ago, and this is the first
'occasional' update - that was frequently used on the old master-only
system without problems.
Thanks again for the hint!
Terry
See:
http://dev.mysql.com/doc/mysql/en/slave-logs.html
http://dev.mysql.com/doc/mysql/en/Binary_log.html
http://dev.mysql.com/doc/mysql/en/mysqlbinlog.html
Terry Riley [EMAIL PROTECTED] wrote:
We are running 4.1.13 standard on Linux as a master, and 4.1.14 on
NT4 as a slave.
Replication seems to work extremely well, except in the following
circumstance.
On the master server, running a query similar to:
insert into zmast.leagueinfo
(countieslist, defaultleaguecode, leaguename ...)
select
countieslist, 'DDLS2005', leaguename ..
from zmast.leagueinfo
where defaultleaguecode='ddls2004'.
using the same table as source and target, with the only changes
being the defaultleaguecode and the autoincrement ID field, without
any problem.
However, this does not seem to replicate to the slave, and no error
shows until later when an attempt is made to insert a record into a
table where a relationship to this record is required ('Cannot update
child record'-type message).
The manual that I have does specify that currently you cannot insert
into a table and select from the same table in a subquery. Clearly
this is not actually the case, but for some reason, it will not
replicate.
Anyone else had/got this problem?
Is there a sensible solution - the only one I can come up with is
changing the intitial 'insert ... select' into a 'create table temp
select from...'/'insert ... select from temp'/ 'drop table temp' set.
This insert ... select stuff is not done on a regular basis, and then
only by an administrator.
Ideas welcome
Cheers
Terry
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [Replication] Problem connecting to master
If nothing helps, trace files could spill the light on your problem. many thanks for your help. With tracing I found the problem. Like almost every time it was just a typing error :-( Regards Jan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [Replication] Problem connecting to master
Hello. If nothing helps, trace files could spill the light on your problem. See: http://dev.mysql.com/doc/mysql/en/making-trace-files.html Jan Roehrich [EMAIL PROTECTED] wrote: --skip-networking can be specified as the command line option. Use: show variables like 'skip_networking'; to check if it is enabled. mysql show variables like 'skip_networking'; +-+---+ | Variable_name | Value | +-+---+ | skip_networking | OFF | +-+---+ -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [Replication] Problem connecting to master
Hello. Please send your configuration files for master and slave. Check that you don't have skip-networking in your slave's configuration file. Jan Roehrich [EMAIL PROTECTED] wrote: I have a strange problem setting up replication. I followed each step of setting up replication in the mysqld documentation (http://dev.mysql.com/doc/mysql/en/replication-howto.html). But there are some error messages on the salve: 050828 13:22:58 [ERROR] Slave I/O thread: error connecting to master '[EMAIL PROTECTED]:3306': Error: 'Can't connect to MySQL server on 'alturo.vlugnet.org' (13)' errno: 2003 retry-time: 60 retries: 86400 When I try to connect to the master using same hostname, same user, same password with the mysql client it works without problems. I tried to drag the problem with ethereal. It seems that the slave doesn't even try to connect to the master. There are not even connection attempts between master and slave on port 3306. Also have a look at http://forums.mysql.com/read.php?26,36766 it seems that he has the same problem. As slave I'm using mysql version 4.1.12 on Fedora Core 4. Master is 3.23.58 on Fedora Core 2. Any ideas? Regards Jan -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [Replication] Problem connecting to master
Please send your configuration files for master and slave. Check that you don't have skip-networking in your slave's configuration file. Master: -- [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock log-bin=bin-log server-id=1 [mysql.server] user=mysql basedir=/var/lib [safe_mysqld] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid --- Slave: --- [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock old_passwords=1 server-id=2 master-host=82.165.25.207 master-port=3306 master-user=replication master-password=hw4Gabfy [mysql.server] user=mysql basedir=/var/lib [mysqld_safe] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid -- Could not see skip-networking anywhere. Greetings Jan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [Replication] Problem connecting to master
Hello. Could not see skip-networking anywhere. --skip-networking can be specified as the command line option. Use: show variables like 'skip_networking'; to check if it is enabled. Jan Roehrich [EMAIL PROTECTED] wrote: Please send your configuration files for master and slave. Check that you don't have skip-networking in your slave's configuration file. Master: -- [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock log-bin=bin-log server-id=1 [mysql.server] user=mysql basedir=/var/lib [safe_mysqld] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid --- Slave: --- [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock old_passwords=1 server-id=2 master-host=82.165.25.207 master-port=3306 master-user=replication master-password=hw4Gabfy [mysql.server] user=mysql basedir=/var/lib [mysqld_safe] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid -- Could not see skip-networking anywhere. Greetings Jan -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [Replication] Problem connecting to master
--skip-networking can be specified as the command line option. Use: show variables like 'skip_networking'; to check if it is enabled. mysql show variables like 'skip_networking'; +-+---+ | Variable_name | Value | +-+---+ | skip_networking | OFF | +-+---+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [Replication] Problem connecting to master
Jan Roehrich wrote: --skip-networking can be specified as the command line option. Use: show variables like 'skip_networking'; to check if it is enabled. mysql show variables like 'skip_networking'; +-+---+ | Variable_name | Value | +-+---+ | skip_networking | OFF | +-+---+ It can be a firewall problem... Are you sure that the slave didn't try to connect to the master, and got no response, or got a connection refused? Check the bin-log of the slave for errors in the bin-log. -- Nuno Pereira -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[Replication] Problem connecting to master
I have a strange problem setting up replication. I followed each step of setting up replication in the mysqld documentation (http://dev.mysql.com/doc/mysql/en/replication-howto.html). But there are some error messages on the salve: 050828 13:22:58 [ERROR] Slave I/O thread: error connecting to master '[EMAIL PROTECTED]:3306': Error: 'Can't connect to MySQL server on 'alturo.vlugnet.org' (13)' errno: 2003 retry-time: 60 retries: 86400 When I try to connect to the master using same hostname, same user, same password with the mysql client it works without problems. I tried to drag the problem with ethereal. It seems that the slave doesn't even try to connect to the master. There are not even connection attempts between master and slave on port 3306. Also have a look at http://forums.mysql.com/read.php?26,36766 it seems that he has the same problem. As slave I'm using mysql version 4.1.12 on Fedora Core 4. Master is 3.23.58 on Fedora Core 2. Any ideas? Regards Jan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication problem
Hello.
I suggest you to check what query was written to the master binary log
and then report a bug. Please include all information you've found in the
report.
Weicheng Pan [EMAIL PROTECTED] wrote:
Dear Gleb:
The problem has occured again, and I grab some output.
This box run mysql 4.1.11 on FreeBSD 5.4 AMD64, with 8GB RAM
In this case,
The original query is INSERT INTO LOW_PRIORTY INTO `statistics`.`search`
(`userid`, `id`, `func`) VALUES('lty0105', '8', 2)
The strange query is 'INSERT LOW_PRIORITY INTO `statistics`.`search` (
`userid` , `id` , `func` ) VALUES ( 'lty0105', '8', 2p)
Slave server got a strange character 'p' after character '2'
Slave:
mysql show slave status\G;
*** 1. row ***
Slave_IO_State: Waiting for master to send event
Master_Host: db0
Master_User: repl
Master_Port: 3306
Connect_Retry: 5
Master_Log_File: db0-bin.56
Read_Master_Log_Pos: 755005566
Relay_Log_File: db10-relay-bin.60
Relay_Log_Pos: 572461705
Relay_Master_Log_File: db0-bin.56
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB: blog,album_database
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table: album_database.%,blog.%
Last_Errno: 1054
Last_Error: Error 'Unknown column '2p' in 'field list'' on
query. Default database: ''. Query: 'INSERT LOW_PRIORITY INTO
`statistics`.`search` ( `userid` , `id` , `func` ) VALUES ( 'lty0105', '8',
2p)'
Skip_Counter: 0
Exec_Master_Log_Pos: 692584947
Relay_Log_Space: 634882324
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)
Master:
mysql show master status\G;
*** 1. row ***
File: db0-bin.56
Position: 761127837
Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)
Finally I dump the slave log:
[EMAIL PROTECTED] [ /home/mysql ] mysqlbinlog db10-relay-bin.60 | grep
(
'lty0105', '8', 2p)
INSERT LOW_PRIORITY INTO `statistics`.`search` ( `userid` , `id` , `func` )
VALUES ( 'lty0105', '8', 2p);
[EMAIL PROTECTED] [ /home/mysql ]
With best regards,
Weicheng.
- Original Message ---
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication problem
Dear Gleb: I forgot to give the master's bin log dump data, The data writen in master log is correct: [EMAIL PROTECTED] [ /home/mysql ] mysqlbinlog db0-bin.56 | grep ( 'lty0105', '8', 2p) INSERT LOW_PRIORITY INTO `statistics`.`search` ( `userid` , `id` , `func` ) VALUES ( 'lty0105', '8', 2 ); And the slave log: [EMAIL PROTECTED] [ /home/mysql ] mysqlbinlog db10-relay-bin.60 | grep ( 'lty0105', '8', 2p) INSERT LOW_PRIORITY INTO `statistics`.`search` ( `userid` , `id` , `func` ) VALUES ( 'lty0105', '8', 2p); I took your suggestion and have sent a report. And thank you very much. With best regards, Weicheng Pan Jun 3, 2005. - Original Message - From: Gleb Paharenko [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, June 02, 2005 4:51 PM Subject: Re: Replication problem Hello. I suggest you to check what query was written to the master binary log and then report a bug. Please include all information you've found in the report. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication problem
Dear Gleb:
The problem has occured again, and I grab some output.
This box run mysql 4.1.11 on FreeBSD 5.4 AMD64, with 8GB RAM
In this case,
The original query is INSERT INTO LOW_PRIORTY INTO `statistics`.`search`
(`userid`, `id`, `func`) VALUES('lty0105', '8', 2)
The strange query is 'INSERT LOW_PRIORITY INTO `statistics`.`search` (
`userid` , `id` , `func` ) VALUES ( 'lty0105', '8', 2p)
Slave server got a strange character 'p' after character '2'
Slave:
mysql show slave status\G;
*** 1. row ***
Slave_IO_State: Waiting for master to send event
Master_Host: db0
Master_User: repl
Master_Port: 3306
Connect_Retry: 5
Master_Log_File: db0-bin.56
Read_Master_Log_Pos: 755005566
Relay_Log_File: db10-relay-bin.60
Relay_Log_Pos: 572461705
Relay_Master_Log_File: db0-bin.56
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB: blog,album_database
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table: album_database.%,blog.%
Last_Errno: 1054
Last_Error: Error 'Unknown column '2p' in 'field list'' on
query. Default database: ''. Query: 'INSERT LOW_PRIORITY INTO
`statistics`.`search` ( `userid` , `id` , `func` ) VALUES ( 'lty0105', '8',
2p)'
Skip_Counter: 0
Exec_Master_Log_Pos: 692584947
Relay_Log_Space: 634882324
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)
Master:
mysql show master status\G;
*** 1. row ***
File: db0-bin.56
Position: 761127837
Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)
Finally I dump the slave log:
[EMAIL PROTECTED] [ /home/mysql ] mysqlbinlog db10-relay-bin.60 | grep (
'lty0105', '8', 2p)
INSERT LOW_PRIORITY INTO `statistics`.`search` ( `userid` , `id` , `func` )
VALUES ( 'lty0105', '8', 2p);
[EMAIL PROTECTED] [ /home/mysql ]
With best regards,
Weicheng.
- Original Message -
From: Gleb Paharenko [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Monday, May 30, 2005 5:20 PM
Subject: Re: Replication problem
Hello.
Not enough information to make a conclusion. What version of
MySQL do you use? Usually debugging of replication problems
begins from researching of binary logs with mysqlbinlog. See:
http://dev.mysql.com/doc/mysql/en/mysqlbinlog.html
http://dev.mysql.com/doc/mysql/en/slave-logs.html
http://dev.mysql.com/doc/mysql/en/binary-log.html
Please, next time include the output of SHOW SLAVE STATUS
(executed on the slave) and SHOW MASTER STATUS (executed on
the master) as well.
Weicheng Pan [EMAIL PROTECTED] wrote:
Hi all:
I have replication problem on my FreeBSD 5.4 AMD64 box.
Sometimes replication will stop and throw an error code 1054,
but the query is not the original one in the master.
A query like UPDATE table_a SET col_a = 'abc' where col_b = 234
the right most character will become a strange character, and cannot
print
out in error log.
I have twelve machines doing the replication work, but only got problem
on
this AMD64 machine.
Anyone has encounter this type problem? please give me some advise.
Thanks and have a nice day.
Regards,
Weicheng.
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication problem
Hello. Not enough information to make a conclusion. What version of MySQL do you use? Usually debugging of replication problems begins from researching of binary logs with mysqlbinlog. See: http://dev.mysql.com/doc/mysql/en/mysqlbinlog.html http://dev.mysql.com/doc/mysql/en/slave-logs.html http://dev.mysql.com/doc/mysql/en/binary-log.html Please, next time include the output of SHOW SLAVE STATUS (executed on the slave) and SHOW MASTER STATUS (executed on the master) as well. Weicheng Pan [EMAIL PROTECTED] wrote: Hi all: I have replication problem on my FreeBSD 5.4 AMD64 box. Sometimes replication will stop and throw an error code 1054, but the query is not the original one in the master. A query like UPDATE table_a SET col_a = 'abc' where col_b = 234 the right most character will become a strange character, and cannot print out in error log. I have twelve machines doing the replication work, but only got problem on this AMD64 machine. Anyone has encounter this type problem? please give me some advise. Thanks and have a nice day. Regards, Weicheng. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication problem
Hi all: I have replication problem on my FreeBSD 5.4 AMD64 box. Sometimes replication will stop and throw an error code 1054, but the query is not the original one in the master. A query like UPDATE table_a SET col_a = 'abc' where col_b = 234 the right most character will become a strange character, and cannot print out in error log. I have twelve machines doing the replication work, but only got problem on this AMD64 machine. Anyone has encounter this type problem? please give me some advise. Thanks and have a nice day. Regards, Weicheng. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication problem
Hello. Error: 'Unknown MySQL server host 'server5.domain.com' (1)' errno: 2005 Does the problem remain if you remove the following line from the my.cnf on the server8: skip-networking Chris Mason [EMAIL PROTECTED] wrote: I have two servers, server5.mydomain.com and server8.mydomain.com. I = want to replicate one database on server5 to server 8. I did the whole proceedure as recommended in http://dev.mysql.com/doc/mysql/en/replication-howto.html - 6.4. How to = Set Up Replication I setup the GRANT statement on server5 for the slave. mysql SHOW GRANTS FOR [EMAIL PROTECTED]; += --- ---+ | Grants for [EMAIL PROTECTED] | += --- ---+ | GRANT RELOAD, SUPER, REPLICATION SLAVE ON *.* TO = 'server8'@'MyServer8IP' IDENTIFIED BY PASSWORD 'xxx' | += --- ---+ 1 row in set (0.00 sec) But when I stop and start the slave, I get: 050316 8:56:42 [Note] Slave SQL thread initialized, starting = replication in log 'mysql-bin.04' at position 79, relay log './server8-relay-bin.01' position: 4 050316 8:56:42 [ERROR] Slave I/O thread: error connecting to master '[EMAIL PROTECTED]:3306': Error: 'Unknown MySQL server host 'server5.domain.com' (1)' errno: 2005 retry-time: 60 retries: 86400 When I try from the command line, I get it to work without problem: [EMAIL PROTECTED] mysql]# mysql -h server5.domain.com -u server8 -p Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 30 to server version: 4.1.10-standard-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql=20 Both /etc/my.cnf files are listed below [EMAIL PROTECTED] mysql]# cat /etc/my.cnf=20 [mysqld] datadir=3D/var/lib/mysql socket=3D/var/lib/mysql/mysql.sock old_passwords skip-locking key_buffer =3D 256M max_allowed_packet =3D 1M table_cache =3D 256 sort_buffer_size =3D 1M read_buffer_size =3D 1M read_rnd_buffer_size =3D 4M myisam_sort_buffer_size =3D 64M thread_cache =3D 8 query_cache_size=3D 16M skip-networking sort_buffer=3D2M log-bin server-id=3D2 log-slave-updates=20 log-warnings=20 replicate-ignore-db=3Dmysql replicate-do-db=3Danguillaguide [isamchk] key_buffer =3D 128M sort_buffer_size =3D 128M read_buffer =3D 2M write_buffer =3D 2M [myisamchk] key_buffer =3D 128M sort_buffer_size =3D 128M read_buffer =3D 2M write_buffer =3D 2M [mysqlhotcopy] interactive-timeout =20 [mysql.server] user=3Dmysql basedir=3D/var/lib [safe_mysqld] err-log=3D/var/log/mysqld.log pid-file=3D/var/run/mysqld/mysqld.pid [EMAIL PROTECTED] mysql]# cat /etc/my.cnf=20 [mysqld] old-passwords max_connections =3D 500 key_buffer =3D 16M myisam_sort_buffer_size =3D 64M join_buffer_size =3D 2M read_buffer_size =3D 2M sort_buffer_size =3D 3M table_cache =3D 1500 thread_cache_size =3D 128 wait_timeout =3D 14400 connect_timeout =3D 10 max_allowed_packet =3D 16M max_connect_errors =3D 10 query_cache_limit =3D 1M query_cache_size =3D 32M query_cache_type =3D 1 skip-innodb log-bin=3Dmysql-bin server-id=3D1 [mysqld_safe] open_files_limit =3D 8192 [mysqldump] quick max_allowed_packet =3D 16M [myisamchk] key_buffer =3D 64M sort_buffer =3D 64M read_buffer =3D 16M write_buffer =3D 16M Chris Mason Anguilla =20 --=20 No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.7.4 - Release Date: 3/18/2005 =20 -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication problem
I have two servers, server5.mydomain.com and server8.mydomain.com. I want to replicate one database on server5 to server 8. I did the whole proceedure as recommended in http://dev.mysql.com/doc/mysql/en/replication-howto.html - 6.4. How to Set Up Replication I setup the GRANT statement on server5 for the slave. mysql SHOW GRANTS FOR [EMAIL PROTECTED]; +--- ---+ | Grants for [EMAIL PROTECTED] | +--- ---+ | GRANT RELOAD, SUPER, REPLICATION SLAVE ON *.* TO 'server8'@'MyServer8IP' IDENTIFIED BY PASSWORD 'xxx' | +--- ---+ 1 row in set (0.00 sec) But when I stop and start the slave, I get: 050316 8:56:42 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.04' at position 79, relay log './server8-relay-bin.01' position: 4 050316 8:56:42 [ERROR] Slave I/O thread: error connecting to master '[EMAIL PROTECTED]:3306': Error: 'Unknown MySQL server host 'server5.domain.com' (1)' errno: 2005 retry-time: 60 retries: 86400 When I try from the command line, I get it to work without problem: [EMAIL PROTECTED] mysql]# mysql -h server5.domain.com -u server8 -p Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 30 to server version: 4.1.10-standard-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql Both /etc/my.cnf files are listed below [EMAIL PROTECTED] mysql]# cat /etc/my.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock old_passwords skip-locking key_buffer = 256M max_allowed_packet = 1M table_cache = 256 sort_buffer_size = 1M read_buffer_size = 1M read_rnd_buffer_size = 4M myisam_sort_buffer_size = 64M thread_cache = 8 query_cache_size= 16M skip-networking sort_buffer=2M log-bin server-id=2 log-slave-updates log-warnings replicate-ignore-db=mysql replicate-do-db=anguillaguide [isamchk] key_buffer = 128M sort_buffer_size = 128M read_buffer = 2M write_buffer = 2M [myisamchk] key_buffer = 128M sort_buffer_size = 128M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout [mysql.server] user=mysql basedir=/var/lib [safe_mysqld] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid [EMAIL PROTECTED] mysql]# cat /etc/my.cnf [mysqld] old-passwords max_connections = 500 key_buffer = 16M myisam_sort_buffer_size = 64M join_buffer_size = 2M read_buffer_size = 2M sort_buffer_size = 3M table_cache = 1500 thread_cache_size = 128 wait_timeout = 14400 connect_timeout = 10 max_allowed_packet = 16M max_connect_errors = 10 query_cache_limit = 1M query_cache_size = 32M query_cache_type = 1 skip-innodb log-bin=mysql-bin server-id=1 [mysqld_safe] open_files_limit = 8192 [mysqldump] quick max_allowed_packet = 16M [myisamchk] key_buffer = 64M sort_buffer = 64M read_buffer = 16M write_buffer = 16M Chris Mason Anguilla -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.7.4 - Release Date: 3/18/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication Problem: I/O Thread exits every night randomly
Hi all We have the following setup: - Master mySQL Server 4.0.11-gamma (sun-solaris2.9-sparc-64bit) - Slave mySQL Server 4.1.7 (sun-solaris2.9-sparc-64bit) Everything worked fine with both servers having the same Version (4.0.11-gamma). As a first step in updating to Rel. 4.1 we migrated the replika. Since then, we encounter the following problem: Every night replication stops. To be more precise: the I/O thread on Slave stops: mysql SHOW SLAVE STATUS\G *** 1. row *** Slave_IO_State: Master_Host: spas.mysql.prod.glue.ch Master_User: replica Master_Port: 26001 Connect_Retry: 60 Master_Log_File: appl02-bin.628 Read_Master_Log_Pos: 30621 Relay_Log_File: appl01-relay-bin.24 Relay_Log_Pos: 412019 Relay_Master_Log_File: appl02-bin.628 Slave_IO_Running: No 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: 30621 Relay_Log_Space: 412019 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: 18563 1 row in set (0.00 sec) The error log on replika says: 041103 3:37:50 [ERROR] While trying to obtain the list of slaves from the master 'spas.mysql.prod.glue.ch:26001', user 'replica' got the following error: 'Lost connection to MySQL server during query' 041103 3:37:50 [ERROR] Slave I/O thread exiting, read up to log 'appl02-bin.628', position 30621 Doing a mysqlbinlog on both relay log and master log do not show any problems. After a START SLAVE the slave does not recover operation completely. It looks, like the relay log file is filled, but the statements are not executed and the Seconds_Behind_Master are not decreased. Only after a complete restart of mysql (replica) the replica recovers. Any idea? Thanks for any help! Stephan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB backup + replication problem?
Hi! Guilhem has now fixed this bug to 4.0.22. Best regards, Heikki Innobase Oy InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php Order MySQL support from http://www.mysql.com/support/index.html .. From: Don MacAskill ([EMAIL PROTECTED]) Subject: InnoDB backup + replication problem? This is the only article in this thread View: Original Format Newsgroups: mailing.database.myodbc Date: 2004-09-29 09:58:02 PST I've got an interesting (well, I think so anyway) problem with my replication. The slave chugs along just fine, then spits out: Query caused different errors on master and slave. Error on master: 'Can't execute the query because you have a conflicting read lock' (1223), Error on slave: 'no error' (0). Default database: 'mysql'. Query: 'BEGIN' I check the master binlog position, and discover this: /*!40019 SET @@session.max_insert_delayed_threads=0*/; # at 35294588 #040929 2:25:51 server id 1 log_pos 35294588 Query thread_id=7830089 exec_time=0 error_code=1223 use mysql; SET TIMESTAMP=1096449951; BEGIN; # at 35294629 #040929 2:25:44 server id 1 log_pos 35282293 Query thread_id=7830089 exec_time=0 error_code=0 SET TIMESTAMP=1096449944; INSERT INTO ibbackup_binlog_marker VALUES (1); # at 35294710 #040929 2:25:51 server id 1 log_pos 35294710 Query thread_id=7830089 exec_time=0 error_code=1223 SET TIMESTAMP=1096449951; COMMIT; I didn't see this prior to 4.0.21 (I was on 4.0.20), but it may or may not be related. This has happened a few times now, and always around the time that I finish an InnoDB backup. Anyone else seen this? Any ideas? Thanks, Don -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication problem with a cross database query
On 10/06/2004 6:23 PM, Bill Thomason [EMAIL PROTECTED] wrote: I originally posted a query about a problem entitled Table doesn't exist on query replication problem... The original title might be a little misleading. The slave replication is halting on a transaction that contains a query that spans two databases - one that is being replicated and the other is not. Could anyone provide me with some general rules of thumb about breaking down such a query? This may sound like a vague or possibly stupid question since this predicament is probably very specific to the situation. I didn't write the original query but I am establishing the master/slave relationships and uncovered the bug in doing so. Other than altering your database schema or replicating the reporting database, your options appear to be very limited. You can't use 'SQL_SLAVE_SKIP_COUNTER = 1' unless your slave is not running, and bookending the troublesome query with 'SQL_LOG_BIN = 0' and 'SQL_LOG_BIN = 1' only works from within the mysql client. Paul -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Table doesn't exist on query replication problem...
Hi, I have a mysql 4.0.21 master and a 4.0.18 slave. I am trying to maintain a replication of a DB named masterdb. My goal is to have multiple slaves with a copy of masterdb. On the master I specified in my.cnf binlog-do-db=masterdb. On the slave I specified replicate-do-db=masterdb in my.cnf. I took a snap shot of masterdb as per the user documentation. I loaded this on the slave server and set up the slave to point to the master using the changer master to ... following the proper syntax. The slave halts with the following messages in the .err file: 041006 12:06:06 Slave I/O thread: connected to master '[EMAIL PROTECTED]:3306', replication started in log 'aaa-bin.008' at position 79 ERROR: 1146 Table 'reporting.linkdata' doesn't exist 041006 12:06:37 Slave: Error 'Table 'reporting.linkdata' doesn't exist' on query 'replace into linkfilters (ip,sstatus,cstatus,dusers) select ip, sum(spam), sum(total), sum(dusers) from reporting.linkdata as a left join reporting.users as u on u.eid=a.eid where unix_timestamp(u.lastupdate) = 0 group by ip having sum(a.total)=15 and sum(a.dusers)=6'. Default database: 'masterdb', Error_code: 1146 041006 12:06:37 Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with SLAVE START. We stopped at log 'aaa-bin.007' position 73927 On the master server there is a database named reporting that does not exist on the slave and should not exist because it is used by the master server only. From my reading of the MySQL documentation on the binary log files works, (correct me if I am wrong. PLEASE!) the master binary log file records all transactions needed to rebuild the database(s) in the event of a catastrophe. In the context of replication, (from a high level view) the slave server reaches into the master and executes transactions to update itself (something like 'mysqlbinlog aaa-bin.007 |mysql -u rep -h slave'), correct? If this assumption about replication is correct then the query: 'replace into linkfilters (ip,sstatus,cstatus,dusers) select ip, sum(spam), sum(total), sum(dusers) from reporting.linkdata as a left join reporting.users as u on u.eid=a.eid where unix_timestamp(u.lastupdate) = 0 group by ip having sum(a.total)=15 and sum(a.dusers)=6' holds the key to my problem because reporting.linkdata and reporting.users do not exist on the slave since it has no concept of the database named reporting. Is there a way to rework this query so that either there are no references to the reporting database or to configure my.cnf on the master and slave to handle this query? Thanks in advance, -Bill Thomason -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table doesn't exist on query replication problem...
Hello, I suggest to you have the same version of mysql in both servers. regards El Miércoles, 6 de Octubre de 2004 13:07, Bill Thomason escribió: Hi, I have a mysql 4.0.21 master and a 4.0.18 slave. I am trying to maintain a replication of a DB named masterdb. My goal is to have multiple slaves with a copy of masterdb. On the master I specified in my.cnf binlog-do-db=masterdb. On the slave I specified replicate-do-db=masterdb in my.cnf. I took a snap shot of masterdb as per the user documentation. I loaded this on the slave server and set up the slave to point to the master using the changer master to ... following the proper syntax. The slave halts with the following messages in the .err file: 041006 12:06:06 Slave I/O thread: connected to master '[EMAIL PROTECTED]:3306', replication started in log 'aaa-bin.008' at position 79 ERROR: 1146 Table 'reporting.linkdata' doesn't exist 041006 12:06:37 Slave: Error 'Table 'reporting.linkdata' doesn't exist' on query 'replace into linkfilters (ip,sstatus,cstatus,dusers) select ip, sum(spam), sum(total), sum(dusers) from reporting.linkdata as a left join reporting.users as u on u.eid=a.eid where unix_timestamp(u.lastupdate) = 0 group by ip having sum(a.total)=15 and sum(a.dusers)=6'. Default database: 'masterdb', Error_code: 1146 041006 12:06:37 Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with SLAVE START. We stopped at log 'aaa-bin.007' position 73927 On the master server there is a database named reporting that does not exist on the slave and should not exist because it is used by the master server only. From my reading of the MySQL documentation on the binary log files works, (correct me if I am wrong. PLEASE!) the master binary log file records all transactions needed to rebuild the database(s) in the event of a catastrophe. In the context of replication, (from a high level view) the slave server reaches into the master and executes transactions to update itself (something like 'mysqlbinlog aaa-bin.007 |mysql -u rep -h slave'), correct? If this assumption about replication is correct then the query: 'replace into linkfilters (ip,sstatus,cstatus,dusers) select ip, sum(spam), sum(total), sum(dusers) from reporting.linkdata as a left join reporting.users as u on u.eid=a.eid where unix_timestamp(u.lastupdate) = 0 group by ip having sum(a.total)=15 and sum(a.dusers)=6' holds the key to my problem because reporting.linkdata and reporting.users do not exist on the slave since it has no concept of the database named reporting. Is there a way to rework this query so that either there are no references to the reporting database or to configure my.cnf on the master and slave to handle this query? Thanks in advance, -Bill Thomason -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication problem with a cross database query
I originally posted a query about a problem entitled Table doesn't exist on query replication problem... The original title might be a little misleading. The slave replication is halting on a transaction that contains a query that spans two databases - one that is being replicated and the other is not. Could anyone provide me with some general rules of thumb about breaking down such a query? This may sound like a vague or possibly stupid question since this predicament is probably very specific to the situation. I didn't write the original query but I am establishing the master/slave relationships and uncovered the bug in doing so. Thanks in advance, -Bill Thomason -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
InnoDB backup + replication problem?
I've got an interesting (well, I think so anyway) problem with my replication. The slave chugs along just fine, then spits out: Query caused different errors on master and slave. Error on master: 'Can't execute the query because you have a conflicting read lock' (1223), Error on slave: 'no error' (0). Default database: 'mysql'. Query: 'BEGIN' I check the master binlog position, and discover this: /*!40019 SET @@session.max_insert_delayed_threads=0*/; # at 35294588 #040929 2:25:51 server id 1 log_pos 35294588 Query thread_id=7830089 exec_time=0 error_code=1223 use mysql; SET TIMESTAMP=1096449951; BEGIN; # at 35294629 #040929 2:25:44 server id 1 log_pos 35282293 Query thread_id=7830089 exec_time=0 error_code=0 SET TIMESTAMP=1096449944; INSERT INTO ibbackup_binlog_marker VALUES (1); # at 35294710 #040929 2:25:51 server id 1 log_pos 35294710 Query thread_id=7830089 exec_time=0 error_code=1223 SET TIMESTAMP=1096449951; COMMIT; I didn't see this prior to 4.0.21 (I was on 4.0.20), but it may or may not be related. This has happened a few times now, and always around the time that I finish an InnoDB backup. Anyone else seen this? Any ideas? Thanks, Don -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Slave replication problem
did you get an answer to your problem yet? If not I may be able to help.. Andrew From: Jeff McKeon [mailto:[EMAIL PROTECTED] Sent: Fri 03/09/2004 15:29 To: [EMAIL PROTECTED] Subject: Slave replication problem Hello all, We had a power outage this morning and before we could shut down our main MySQL server, power to it was lost. Bad UPS battery. Long story. We replicate this server to two others. On one I get the following error... Jeff McKeon IT Manager*** 1. row *** Master_Host: 10.32.1.10 Master_User: repl Master_Port: 3306 Connect_retry: 60 Master_Log_File: DB01TC07927-bin.022 Read_Master_Log_Pos: 2223919 Relay_Log_File: DB02TC07927-relay-bin.286 Relay_Log_Pos: 16361931 Relay_Master_Log_File: DB01TC07927-bin.021 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_do_db: Replicate_ignore_db: Last_errno: 0 Last_error: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave. Skip_counter: 0 Exec_master_log_pos: 85068331 Relay_log_space: 18604700 1 row in set (0.00 sec) On the other I'm getting... *** 1. row *** Master_Host: 10.32.1.10 Master_User: repl Master_Port: 3306 Connect_retry: 60 Master_Log_File: DB01TC07927-bin.021 Read_Master_Log_Pos: 85079027 Relay_Log_File: mis02tc07927-relay-bin.106 Relay_Log_Pos: 4 Relay_Master_Log_File: DB01TC07927-bin.021 Slave_IO_Running: No Slave_SQL_Running: Yes Replicate_do_db: Replicate_ignore_db: Last_errno: 0 Last_error: Skip_counter: 0 Exec_master_log_pos: 85079027 Relay_log_space: 4 1 row in set (0.00 sec) From the server.err log I have: 040903 14:00:01 Slave I/O thread: connected to master '[EMAIL PROTECTED]:3306', replication started in log 'DB01TC07927-bin.021' at position 85079027 040903 14:00:01 Error reading packet from server: Client requested master to start replication from impossible position (server_errno=1236) 040903 14:00:01 Got fatal error 1236: 'Client requested master to start replication from impossible position' from master when reading data from binary log 040903 14:00:01 Slave I/O thread exiting, read up to log 'DB01TC07927-bin.021', position 85079027 How do I fix these problems? Thanks, Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Slave replication problem
Hello all, We had a power outage this morning and before we could shut down our main MySQL server, power to it was lost. Bad UPS battery. Long story. We replicate this server to two others. On one I get the following error... Jeff McKeon IT Manager*** 1. row *** Master_Host: 10.32.1.10 Master_User: repl Master_Port: 3306 Connect_retry: 60 Master_Log_File: DB01TC07927-bin.022 Read_Master_Log_Pos: 2223919 Relay_Log_File: DB02TC07927-relay-bin.286 Relay_Log_Pos: 16361931 Relay_Master_Log_File: DB01TC07927-bin.021 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_do_db: Replicate_ignore_db: Last_errno: 0 Last_error: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave. Skip_counter: 0 Exec_master_log_pos: 85068331 Relay_log_space: 18604700 1 row in set (0.00 sec) On the other I'm getting... *** 1. row *** Master_Host: 10.32.1.10 Master_User: repl Master_Port: 3306 Connect_retry: 60 Master_Log_File: DB01TC07927-bin.021 Read_Master_Log_Pos: 85079027 Relay_Log_File: mis02tc07927-relay-bin.106 Relay_Log_Pos: 4 Relay_Master_Log_File: DB01TC07927-bin.021 Slave_IO_Running: No Slave_SQL_Running: Yes Replicate_do_db: Replicate_ignore_db: Last_errno: 0 Last_error: Skip_counter: 0 Exec_master_log_pos: 85079027 Relay_log_space: 4 1 row in set (0.00 sec) From the server.err log I have: 040903 14:00:01 Slave I/O thread: connected to master '[EMAIL PROTECTED]:3306', replication started in log 'DB01TC07927-bin.021' at position 85079027 040903 14:00:01 Error reading packet from server: Client requested master to start replication from impossible position (server_errno=1236) 040903 14:00:01 Got fatal error 1236: 'Client requested master to start replication from impossible position' from master when reading data from binary log 040903 14:00:01 Slave I/O thread exiting, read up to log 'DB01TC07927-bin.021', position 85079027 How do I fix these problems? Thanks, Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication Problem
Hi, All Is replication of database in MySQL is possible. Actually i want to replicate the changes to the database to different servers located at different locations. Please tell me if any suggestions, or available resources for this Replication Topic. From: Smitesh S. Damdoo (Mob. No.) 9223256591
Re: Replication Problem
On Friday 20 August 2004 07:08, [EMAIL PROTECTED] might have typed: Is replication of database in MySQL is possible. Actually i want to replicate the changes to the database to different servers located at different locations. A tiny bit of legwork would have answered it for you. http://dev.mysql.com/doc/mysql/en/Replication.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
replication problem
Hi Friends: We are using mysql version 4.0.17 on Linux with a master and a single slave both running on the same node. We have encountered a problem in replication in the following scenario: First the slave got abnormally terminated while there were some active connections to the master. The master was also terminated abnormally in quick succession. After both master and slave was brought up, we noticed that the last transaction in the master just before the abnormal termination is getting replicated twice in the slave. Thanks in advance. Please pardon me if there is a confidentiality note that gets automatically attached with this mail and do ignore it. Regards, Ravi Confidentiality Notice The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain confidential or privileged information. If you are not the intended recipient, please notify the sender at Wipro or [EMAIL PROTECTED] immediately and destroy all copies of this message and any attachments.
Re: replication problem
On Tue, Aug 03, 2004 at 12:44:05PM +0530, [EMAIL PROTECTED] wrote: Hi Friends: We are using mysql version 4.0.17 on Linux with a master and a single slave both running on the same node. We have encountered a problem in replication in the following scenario: First the slave got abnormally terminated while there were some active connections to the master. The master was also terminated abnormally in quick succession. After both master and slave was brought up, we noticed that the last transaction in the master just before the abnormal termination is getting replicated twice in the slave. Thanks in advance. Please pardon me if there is a confidentiality note that gets automatically attached with this mail and do ignore it. I'm going to make an educated guess. There's a race condition and you lost. The slave copied that query from the master's binlog but was shutdown before it could update the master.info file to reflect it's new position in reading the master's binlog. When you started it back up, it fetched it a second time, not knowing it had done so previously. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication problem on 3.23.57 3.23.58
We have suddenly run into a replication problem between our master and our slave databases. It had been running without a problem for atleast several months since our last upgrade, and for several years before that. I've tried a number of debugging techniques, and now I'm hoping somebody here can make sense of it. On the slave I get error messages like: 040630 2:43:52 Slave: reconnected to master '[EMAIL PROTECTED]:3306',replication resumed in log 'mysql-bin.163' at position 37919441 It does that several times between 2:20am and 4:30am. and every few nights the slave gives up: 040630 4:15:34 Slave thread exiting, replication stopped in log 'mysql-bin.163' at position 99512496 No logs are recorded on the master. This 2am to 5am time coincides with a lot of mysql update/insert traffic on the master, in parallel with alot of different connections. Master and slave are running linux. Master is running 3.23.57 binary and the slave is running 3.23.58 binary. I checked the changelong on mysql's site, and I don't see why the version difference would be a problem. (but I am open to upgrading the master!) Both master and slave have max packet set at 16M. So my initial thought was hardware (since everything was great before). So I feel like I've almost eliminated it by doing the following: Changing ethernet boards on both master and slave (to totally different brand of hardware even) Changed cables Used different switch ports Turned off auto negotiate on the switch port Then I: swapped the slave hardware for completely different hardware (removed raid drives, stuffed into similar server) Upgraded the kernels on both machines to 2.4.26 I also setup a ping over night to both the master and slave servers looking for packet loss, none. I wrote a TCP based ping between both of those servers and our administrative server. It kept passing data back and forth and recorded the total time. No slow down, or Net errors during the time periods Mysql has communications problems. So then I setup a second slave. This box runs debian (the other slave ran redhat). This machine is running the same exact binary mysql that was running on the other slave. This slave ran fine for 3 days (which led to make some false assumptions that it was working fine). But now it's slave breaks too. Interesting though. Both slaves will break at *approximately* the same time (about a minute apart.. Both machines are ntp synced to the second, so it's not *EXACTLY* the same time), and both will be pretty *close* in the binlog, but not exact. I have looked in the biglog at the various queries that it's crapped out on, and they're nothing interesting. I've looked at about 10 of them, and some are very simple updates, some are long complex inserts. A few nights I have SLAVE STOP;ed the slave, and waited until the morning to run the slave log forward. When I run the slave log in the morning, I get the same exact slave disconnects. This is the first time I've seen the slave disconnect outside the 2:30-4am window. So clearly this seems to be a mysql problem... Something in the binlog that's causing it to disconnect, but why do the two slaves not disconnect in the same *EXACT* place? So, before putting the axe through the mysql server, I busted out my packet sniffer. I sniffed the packets from both sides of the connection until it failed. From my reading, it looks like the master is tearing down the connection. It basically looks like this: Master sends lots of data Master sends a PSH ACK packet Master sends lots of data Master sends a PSH ACK packet Master sends lots of data Master sends a PSH ACK FIN packet Slave sends a ACK FIN packet (with slave ACKs in there too) S, it looks like the master is tearing down the connection. Anybody have any thoughts? I can upgrade the master to 3.23.58, but I don't see anything in the mysql change log that implies that will help. (Bringing down the master server requires much dancing around and appeasing the customers due to the outage). My other thought is going to mysql 4.0.x. But again, I don't generally like doing things just because they might help. We have had a plan to upgrade to 4.0.x for some time (we certified our software on it), but we don't have an urgent need to budget the resources required to do it. Is it possible there is some sort of race in the mysql-biglog writing? One of the reasons why this might have only started cropping up now is the 2:30-4am slot has been getting progressively busier and busier, with a huge number of parallel insert/updates (4cpu box). The rest of the day the traffic isn't even close to that time period, and replication works rockstar. Settings on the master: set-variable= key_buffer=1024M set-variable= tmp_table_size=1024M set-variable= max_allowed_packet=16M set-variable= thread_stack=128K set-variable= max_connections=2000 set-variable= max_connect_errors=9 set-variable
Re: Replication problem on 3.23.57 3.23.58
somebody here can make sense of it. On the slave I get error messages like: 040630 2:43:52 Slave: reconnected to master '[EMAIL PROTECTED]:3306',replication resumed in log 'mysql-bin.163' at position 37919441 It does that several times between 2:20am and 4:30am. and every few Ah.. right.. I'm not-so-smart. I didn't actually post the entire error log message: 040701 14:56:05 Error reading packet from server: Lost connection to MySQL server during query (server_errno=2013) 040701 14:56:05 Slave: Failed reading log event, reconnecting to retry, log 'mysql-bin.165' position 14792980 040701 14:56:05 Slave: reconnected to master '[EMAIL PROTECTED]:3306',replication resumed in log 'mysql-bin.165' at position 14792980 040701 15:02:13 Error reading packet from server: Lost connection to MySQL server during query (server_errno=2013) 040701 15:02:13 Slave: Failed reading log event, reconnecting to retry, log 'mysql-bin.165' position 25852745 040701 15:02:13 Slave: reconnected to master '[EMAIL PROTECTED]:3306',replication resumed in log 'mysql-bin.165' at position 25852745 040701 15:09:14 Error reading packet from server: Lost connection to MySQL server during query (server_errno=2013) 040701 15:09:14 Slave: Failed reading log event, reconnecting to retry, log 'mysql-bin.165' position 37090598 040701 15:09:14 Slave: reconnected to master '[EMAIL PROTECTED]:3306',replication resumed in log 'mysql-bin.165' at position 37090598 040701 15:15:28 Error reading packet from server: Lost connection to MySQL server during query (server_errno=2013) 040701 15:15:28 Slave: Failed reading log event, reconnecting to retry, log 'mysql-bin.165' position 47700336 040701 15:15:28 Slave: reconnected to master '[EMAIL PROTECTED]:3306',replication resumed in log 'mysql-bin.165' at position 47700336 are the messages that come out of the slave. I'm currently running a test where I back up the entire slave, run it forward through the logs, and restore the slave back exactly to it's previous point, and run it through the logs again. See if it dies in the same places or if it's somewhat random. -Joe -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
replication problem, empty Binlog_do_db ?
Running 4.0.20-standard-log on Fedora Core 1 on two boxes. Running with a largely untouched my-huge.cnf - innodb configs uncommented. Followed http://dev.mysql.com/doc/mysql/en/Replication_HOWTO.html to setup replication. On the master I see: mysql show master status; +-+--+--+--+ | File| Position | Binlog_do_db | Binlog_ignore_db | +-+--+--+--+ | db2-bin.005 | 185 | | | +-+--+--+--+ The HOWTO seems to suggest that Binlog_do_db should have dbs I want to replicate. The slave seems forever stuck in: *** 2. row *** Id: 2 User: system user Host: db: NULL Command: Connect Time: 797 State: Waiting for master to send event Info: NULL Since I have InnoDB tables and don't have the money for InnoDB Hot Backup, I did a 'flush tables with read lock; show master status' on the master, shutdown the master and rsync'd /var/lib/mysql to the slave. I started the master back up, started the slave, ran the 'change master to' query and started the slave. Log shows: 040527 23:03:49 Slave SQL thread initialized, starting replication in log 'db2-bin.004' at position 211, relay log './db3-relay-bin.001' position: 4 040527 23:03:49 Slave I/O thread: connected to master '[EMAIL PROTECTED]:3306', replication started in log 'db2-bin.004' at position 211 and no other errors. I have test.mrztest and can insert rows on the master but they never appear on the slave. Did I miss a step? Is Binlog_do_db supposed to be empty? -- matthew zeier - Nothing in life is to be feared. It is only to be understood. - Marie Curie -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication problem
Hi, On Wed, Apr 21, 2004 at 06:35:53PM +0200, Mechain Marc wrote: Add this in your my.cnf slave file: slave-skip-errors = 1062 Ahh, that's what i was looking for. Seems as if i had not read chapter 6 of the documentation thoroughly enough... Thanks, Georg -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication problem
Hi, i'm new to this list, but i use mysql for years an are very happy with it. However, today i ran into a problem that i couldn't find a solution for: I set up database replication with a master and one slave, and it works fine so far. I rewrote my application (web based written in php) so that it executes all queries that insert, delete or update rows are executed on the master, and all other queries on the slave. Fine. But what, if the master fails? I want users to be able to continue working on the slave, and this works fine for webpages that just do select statements. I thought that, in case of the master being down, i could execute all data-modifying queries on the slave, and also store these queries in a special table or file, and re-execute them later on the master if the master becomes available again. The problem is, that i then may get Duplicate entry ... for key ... errors on the slave, if a record was already inserted into a table with unique keys, and that the sql-thread on the slave then exits. Is it possible to make the slave ignore such errors (i found no option for this in the docs) and just stupidly continue replication, or does anyone have a better idea how to set up such a scenario? Bye, Georg -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Replication problem
Add this in your my.cnf slave file: slave-skip-errors = 1062 Marc. -Message d'origine- De : Georg Horn [mailto:[EMAIL PROTECTED] Envoyé : mercredi 21 avril 2004 17:31 À : [EMAIL PROTECTED] Objet : Replication problem Hi, i'm new to this list, but i use mysql for years an are very happy with it. However, today i ran into a problem that i couldn't find a solution for: I set up database replication with a master and one slave, and it works fine so far. I rewrote my application (web based written in php) so that it executes all queries that insert, delete or update rows are executed on the master, and all other queries on the slave. Fine. But what, if the master fails? I want users to be able to continue working on the slave, and this works fine for webpages that just do select statements. I thought that, in case of the master being down, i could execute all data-modifying queries on the slave, and also store these queries in a special table or file, and re-execute them later on the master if the master becomes available again. The problem is, that i then may get Duplicate entry ... for key ... errors on the slave, if a record was already inserted into a table with unique keys, and that the sql-thread on the slave then exits. Is it possible to make the slave ignore such errors (i found no option for this in the docs) and just stupidly continue replication, or does anyone have a better idea how to set up such a scenario? Bye, Georg -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: weird replication problem with master.info being created empty
You need to shutdown mysql, delete the master.info and relay-log.info files and then start mysql again. The reason for this is because if the info files are present then mysql uses them as is and only recreates them from my.ini or my.cnf if absent. This should get your replication going again. regards Jurgen Have a problem, running a master/slave setup which worked until tonight and mysteriously broke with no changes to the setup at all. My server starts up fine, but the master.info and relay-log.info that are created are 0 bytes and empty. In the logfile, I get this: 040128 23:00:29 Error reading slave log configuration 040128 23:00:29 Failed to initialize the master info structure and when I run slave start I get this: ERROR 1201: Could not initialize master info structure, check permisions on master.info The blank files in question have okay permissions (mysql:mysql) as does the directory they're in, and they ARE getting written to, just nothing is being written. The my.cnf file also has appropriate permissions and is readable by mysql (so it's not a case of the server not knowing what to write in those files). Moreover, nothing has changed on the server config at all. I've tried resetting the master and slave but it doesn't help. I was running version 4.0.12-0 but just upgraded to 4.0.17-0 which hasn't helped at all, and the only thing I can find in the archives are similar problems with the same error number that either turn out to be legit permissions errors (which isn't the case here), and this: http://lists.mysql.com/bugs/15135 which I don't really follow as far as what the final solution was. __ http://www.webmail.co.za/dialup Webmail ISP - Cool Connection, Cool Price -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: weird replication problem with master.info being created empty
Tried that, but no luck, they get recreated, but they still have 0 bytes in them. This is really driving me crazy because although it's come up before on lists, noone has ever really had an answer for it that makes sense. The url I mentioned earlier does say something about the hostname of the server changing, but that isn't the case here, the hostname is the same as always (should have added that in the first place). thanks, Andrew From: Lightware Software [EMAIL PROTECTED] Date: Thu, 29 Jan 2004 08:53:48 +0200 To: [EMAIL PROTECTED] Subject: Re: weird replication problem with master.info being created empty You need to shutdown mysql, delete the master.info and relay-log.info files and then start mysql again. The reason for this is because if the info files are present then mysql uses them as is and only recreates them from my.ini or my.cnf if absent. This should get your replication going again. regards Jurgen Have a problem, running a master/slave setup which worked until tonight and mysteriously broke with no changes to the setup at all. My server starts up fine, but the master.info and relay-log.info that are created are 0 bytes and empty. In the logfile, I get this: 040128 23:00:29 Error reading slave log configuration 040128 23:00:29 Failed to initialize the master info structure and when I run slave start I get this: ERROR 1201: Could not initialize master info structure, check permisions on master.info The blank files in question have okay permissions (mysql:mysql) as does the directory they're in, and they ARE getting written to, just nothing is being written. The my.cnf file also has appropriate permissions and is readable by mysql (so it's not a case of the server not knowing what to write in those files). Moreover, nothing has changed on the server config at all. I've tried resetting the master and slave but it doesn't help. I was running version 4.0.12-0 but just upgraded to 4.0.17-0 which hasn't helped at all, and the only thing I can find in the archives are similar problems with the same error number that either turn out to be legit permissions errors (which isn't the case here), and this: http://lists.mysql.com/bugs/15135 which I don't really follow as far as what the final solution was. __ http://www.webmail.co.za/dialup Webmail ISP - Cool Connection, Cool Price -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: weird replication problem with master.info being created empty
Maybe you should check if the ip address resolved for the master's url is correct ? Also try using CHANGE MASTER TO MASTER_HOST='master host name', MASTER_USER='replication user name', MASTER_PASSWORD='replication password'; and then START SLAVE; and see what happens. regards Jurgen Tried that, but no luck, they get recreated, but they still have 0 bytes in them. This is really driving me crazy because although it's come up before on lists, noone has ever really had an answer for it that makes sense. The url I mentioned earlier does say something about the hostname of the server changing, but that isn't the case here, the hostname is the same as always (should have added that in the first place). You need to shutdown mysql, delete the master.info and relay-log.info files and then start mysql again. The reason for this is because if the info files are present then mysql uses them as is and only recreates them from my.ini or my.cnf if absent. This should get your replication going again. regards Jurgen Have a problem, running a master/slave setup which worked until tonight and mysteriously broke with no changes to the setup at all. My server starts up fine, but the master.info and relay-log.info that are created are 0 bytes and empty. In the logfile, I get this: 040128 23:00:29 Error reading slave log configuration 040128 23:00:29 Failed to initialize the master info structure and when I run slave start I get this: ERROR 1201: Could not initialize master info structure, check permisions on master.info The blank files in question have okay permissions (mysql:mysql) as does the directory they're in, and they ARE getting written to, just nothing is being written. The my.cnf file also has appropriate permissions and is readable by mysql (so it's not a case of the server not knowing what to write in those files). Moreover, nothing has changed on the server config at all. I've tried resetting the master and slave but it doesn't help. I was running version 4.0.12-0 but just upgraded to 4.0.17-0 which hasn't helped at all, and the only thing I can find in the archives are similar problems with the same error number that either turn out to be legit permissions errors (which isn't the case here), and this: http://lists.mysql.com/bugs/15135 which I don't really follow as far as what the final solution was. __ http://www.webmail.co.za/dialup Webmail ISP - Cool Connection, Cool Price -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: weird replication problem with master.info being created empty
Thanks, but actually I just found the answer a second ago, and I'm including it here so hopefully it'll be archived around the internet and whatnot so the next poor sap like me with the same problem who tries to find it can: What happened was that somehow the servername-relay-bin.009 file got corrupted, and that caused the whole mess. I noticed that I had a list of files like so: servername-relay-bin.009 servername-relay-bin.010 servername-relay-bin.011 servername-relay-bin.012 servername-relay-bin.013 servername-relay-bin.014 servername-relay-bin.015 servername-relay-bin.016 etc.. and they were all 4 bytes except the 009 one which was 1 byte. They were confusing me because I had run reset master and reset slave a few times while trying to figure this problem out, but those still were sitting there. So, all I did was copy one of the good servername-relay-bin.010 files over the bad servername-relay-bin.009 file, and that solved it. I don't know if this would be a good idea on a server where that might mess up the syncing (or where the broken relay-bin file was different from the non-broken one), but in my case I could do that and now I just need to copy the current database over to that server and start fresh. I hope I have explained that somewhat clearly, it's late here, but yeah, essentially the whole problem can be summed up as this: A corrupted servername-relay-bin file will cause the master.info and relay-log.info to be created but be empty, and when you try slave start it'll tell you: ERROR 1201: Could not initialize master info structure, check permisions on master.info Hope that helps someone down the line! Andrew From: Lightware Software [EMAIL PROTECTED] Date: Thu, 29 Jan 2004 10:25:24 +0200 To: [EMAIL PROTECTED] Subject: Re: weird replication problem with master.info being created empty Maybe you should check if the ip address resolved for the master's url is correct ? Also try using CHANGE MASTER TO MASTER_HOST='master host name', MASTER_USER='replication user name', MASTER_PASSWORD='replication password'; and then START SLAVE; and see what happens. regards Jurgen Tried that, but no luck, they get recreated, but they still have 0 bytes in them. This is really driving me crazy because although it's come up before on lists, noone has ever really had an answer for it that makes sense. The url I mentioned earlier does say something about the hostname of the server changing, but that isn't the case here, the hostname is the same as always (should have added that in the first place). You need to shutdown mysql, delete the master.info and relay-log.info files and then start mysql again. The reason for this is because if the info files are present then mysql uses them as is and only recreates them from my.ini or my.cnf if absent. This should get your replication going again. regards Jurgen Have a problem, running a master/slave setup which worked until tonight and mysteriously broke with no changes to the setup at all. My server starts up fine, but the master.info and relay-log.info that are created are 0 bytes and empty. In the logfile, I get this: 040128 23:00:29 Error reading slave log configuration 040128 23:00:29 Failed to initialize the master info structure and when I run slave start I get this: ERROR 1201: Could not initialize master info structure, check permisions on master.info The blank files in question have okay permissions (mysql:mysql) as does the directory they're in, and they ARE getting written to, just nothing is being written. The my.cnf file also has appropriate permissions and is readable by mysql (so it's not a case of the server not knowing what to write in those files). Moreover, nothing has changed on the server config at all. I've tried resetting the master and slave but it doesn't help. I was running version 4.0.12-0 but just upgraded to 4.0.17-0 which hasn't helped at all, and the only thing I can find in the archives are similar problems with the same error number that either turn out to be legit permissions errors (which isn't the case here), and this: http://lists.mysql.com/bugs/15135 which I don't really follow as far as what the final solution was. __ http://www.webmail.co.za/dialup Webmail ISP - Cool Connection, Cool Price -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
weird replication problem with master.info being created empty
Hi all, Have a problem, running a master/slave setup which worked until tonight and mysteriously broke with no changes to the setup at all. My server starts up fine, but the master.info and relay-log.info that are created are 0 bytes and empty. In the logfile, I get this: 040128 23:00:29 Error reading slave log configuration 040128 23:00:29 Failed to initialize the master info structure and when I run slave start I get this: ERROR 1201: Could not initialize master info structure, check permisions on master.info The blank files in question have okay permissions (mysql:mysql) as does the directory they're in, and they ARE getting written to, just nothing is being written. The my.cnf file also has appropriate permissions and is readable by mysql (so it's not a case of the server not knowing what to write in those files). Moreover, nothing has changed on the server config at all. I've tried resetting the master and slave but it doesn't help. I was running version 4.0.12-0 but just upgraded to 4.0.17-0 which hasn't helped at all, and the only thing I can find in the archives are similar problems with the same error number that either turn out to be legit permissions errors (which isn't the case here), and this: http://lists.mysql.com/bugs/15135 which I don't really follow as far as what the final solution was. Anyone have any ideas? This is confusing! thanks, Andrew -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication Problem
Hi I understand the concept of master n slave relationship but have certain queries regarding this... 1) In case the master is down i do i configure my system so that a present active slave becomes the master or any other way to tackle this situation.How does the clients know about this new server being set as the master . 2)The master is run as a myslq demon . Now the slaves though being servers ..are run as clients of the master server with a definate usernme who has been granted REPLICATION SLAVE PERMISSion set at the master server?? i.e do i run the slaves as mysql -h xxx -u -p ?? --mysql client ? 3)Is it necessary t take a snapshot of the datadir at the server , for the first time i set up replication /? Plz Help Me out Thnx Varun Yahoo! India Mobile: Download the latest polyphonic ringtones. Go to http://in.mobile.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Drop foreign key replication problem
Batara, thank you for the bug report. I have to fix this bug when I overhaul the foreign key constraint naming conventions. Yes, to fix the problem you can drop the foreign key constraint manually in the slave. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - hot backup tool for InnoDB which also backs up MyISAM tables Order MySQL technical support from https://order.mysql.com/ - Original Message - From: Batara Kesuma [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Wednesday, December 03, 2003 6:41 AM Subject: Drop foreign key replication problem Hi, I am running a master and a slave. When I created a foreign key in master, it looked like this: CONSTRAINT `0_373` FOREIGN KEY (`prefecture_id`) REFERENCES `prefecture` (`id`) But on the slave it was like: CONSTRAINT `0_451` FOREIGN KEY (`prefecture_id`) REFERENCES `prefecture` (`id`) The problem is, when I deleted this foreign key on master using command: ALTER TABLE input DROP FOREIGN KEY 0_373 The slave produced an error: ERROR: 1025 Error on rename of './new_atpress/input' to './new_atpress/#sql2-3a54-2' (errno: 140) 031203 13:27:30 Slave: Error 'Error on rename of './new_atpress/input' to './new_atpress/#sql2-3a54-2' (errno: 140)' on query 'alter table input drop foreign key 0_373'. Default database: 'new_atpress', Error_code: 1025 031203 13:27:30 Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with SLAVE START. We stopped at log 'borneo-bin.010' position 901130237 Versions: MySQL 4.0.15 Linux 2.4.18 I guess this is because the constraint name difference of the foreign keys. How do I fix this problem? Can I just manually drop the foreign key on slave and skip this alter table input drop foreign key 0_373? How to do it? Please help, and thank you very much. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Drop foreign key replication problem
Hi, I am running a master and a slave. When I created a foreign key in master, it looked like this: CONSTRAINT `0_373` FOREIGN KEY (`prefecture_id`) REFERENCES `prefecture` (`id`) But on the slave it was like: CONSTRAINT `0_451` FOREIGN KEY (`prefecture_id`) REFERENCES `prefecture` (`id`) The problem is, when I deleted this foreign key on master using command: ALTER TABLE input DROP FOREIGN KEY 0_373 The slave produced an error: ERROR: 1025 Error on rename of './new_atpress/input' to './new_atpress/#sql2-3a54-2' (errno: 140) 031203 13:27:30 Slave: Error 'Error on rename of './new_atpress/input' to './new_atpress/#sql2-3a54-2' (errno: 140)' on query 'alter table input drop foreign key 0_373'. Default database: 'new_atpress', Error_code: 1025 031203 13:27:30 Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with SLAVE START. We stopped at log 'borneo-bin.010' position 901130237 Versions: MySQL 4.0.15 Linux 2.4.18 I guess this is because the constraint name difference of the foreign keys. How do I fix this problem? Can I just manually drop the foreign key on slave and skip this alter table input drop foreign key 0_373? How to do it? Please help, and thank you very much. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Strange replication problem
I found the problem which I will outline here just in case anyone else runs across it in the future: It appears that a slave will not replicate data from it's own server-id. In my case, a large portion of the binary logs on my slave had originally come from the master, so when the master tried to re-replicate the data, it simply ignored entries from it's own server-id. This makes complete sense, however, I can't find anywhere in the MySQL documention that explains this behavior. The documention only says that the master and slaves should have unique server-ids. -Mike I have one master and one slave which I am upgrading to 4.0.14 from 4.0.12. To start the upgrade I stopped the slave, took a snapshot of it's data directory, cleared it's binary logs, and switched to the 4.0.14 binaries. I then restarted the slave thread to get it caught up with the master. The slave also runs with --log-slave-updates so that it has a copy of all of the data from the point of the snapshot. This afternoon at 2:10pm I switched our mysql.domain.com CNAME to point to the slave instead of the master (this is relevant). At this point, the slave is acting as the master and taking all of the updates. When I was sure all of the clients were using the slave I stopped it's slave thread and took down the master server to upgrade it as well. I rebuilt the master's data directory from the snapshot I'd taken previously on the slave. At this point I told the master to replicate the data off of the slave. Here's the strange part. The I/O thread seems to be grabbing the data off of the slave correctly. It writes relay logs just fine. However, the SQL thread doesn't update the database. SHOW SLAVE STATUS indicates that both parts are running normally. The I/O thread continues to write relay log files (deleting old ones as it goes as if it doesn't need them anymore). Then... at the point in logs for 2:10pm today when the CNAME was switched, all of the sudden the SQL thread decides to start updating the database. There isn't anything strange in the binary logs that I can see accept that the 'log_pos' value drops a fair amount at the same time it decides to start updating the database. I don't know what the means if anything. Why isn't it updating the database for all of the relay data? Considering that I've completely wiped the master's data directory except for the snapshot, cleared it's binary logs, and it's innodblogs... I'm completely at a loss for how it can know the exact time it stopped getting normal updates when it's CNAME changed. If you have any questions about my environment I'd be happy to answer them. Thanks, Mike -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Strange replication problem
I have one master and one slave which I am upgrading to 4.0.14 from 4.0.12. To start the upgrade I stopped the slave, took a snapshot of it's data directory, cleared it's binary logs, and switched to the 4.0.14 binaries. I then restarted the slave thread to get it caught up with the master. The slave also runs with --log-slave-updates so that it has a copy of all of the data from the point of the snapshot. This afternoon at 2:10pm I switched our mysql.domain.com CNAME to point to the slave instead of the master (this is relevant). At this point, the slave is acting as the master and taking all of the updates. When I was sure all of the clients were using the slave I stopped it's slave thread and took down the master server to upgrade it as well. I rebuilt the master's data directory from the snapshot I'd taken previously on the slave. At this point I told the master to replicate the data off of the slave. Here's the strange part. The I/O thread seems to be grabbing the data off of the slave correctly. It writes relay logs just fine. However, the SQL thread doesn't update the database. SHOW SLAVE STATUS indicates that both parts are running normally. The I/O thread continues to write relay log files (deleting old ones as it goes as if it doesn't need them anymore). Then... at the point in logs for 2:10pm today when the CNAME was switched, all of the sudden the SQL thread decides to start updating the database. There isn't anything strange in the binary logs that I can see accept that the 'log_pos' value drops a fair amount at the same time it decides to start updating the database. I don't know what the means if anything. Why isn't it updating the database for all of the relay data? Considering that I've completely wiped the master's data directory except for the snapshot, cleared it's binary logs, and it's innodblogs... I'm completely at a loss for how it can know the exact time it stopped getting normal updates when it's CNAME changed. If you have any questions about my environment I'd be happy to answer them. Thanks, Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fw: Replication Problem (XP+mysql4.0.14)
nobody have answer ? For me it is very urgent to kwow when the .15 version will be avalaible . Tks - Original Message - From: Massimo Petrini [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: I.P. [EMAIL PROTECTED] Sent: Friday, August 22, 2003 1:16 PM Subject: Re: Replication Problem (XP+mysql4.0.14) I have your same problem in my network where the master i 3.23.52 and then slave are 4.0.14; before, in all 4.0.13, it was ok, without this bug. It will be solved in 4.0.15 ? Best regards Massimo - Original Message - From: I.P. [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, August 22, 2003 11:56 AM Subject: Replication Problem (XP+mysql4.0.14) Hi, it's my story. I have two 4.0.14 mysql server on one machine with win XP Professional polish version. First acts as master: on port 3300 Second acts as slave: on port 3301 below my configuration: ### FOR MASTER # # This will be passed to all mysql clients [client] #password=my_password port=3300 #socket=MySQL # Here is entries for some specific programs # The following values assume you have at least 32M ram # The MySQL server [mysqld] basedir = c:/mysql4/ datadir = c:/mysql4/data/ port=3300 language=polish default-character-set=latin2 log-bin server-id=1 log-warnings set-variable = key_buffer=16K set-variable = max_allowed_packet=1M set-variable = thread_stack=64K set-variable = table_cache=4 set-variable = sort_buffer=64K set-variable = net_buffer_length=2K query_cache_size = 1024K # Uncomment the following if you are using Innobase tables innodb_data_file_path = ibdata1:50M innodb_data_home_dir = d:\\innodb\\mysql4\\master\\ibdata innodb_log_group_home_dir = d:\\innodb\\mysql4\\master\\iblogs innodb_log_arch_dir = d:\\innodb\\mysql4\\master\\iblogs set-variable = innodb_mirrored_log_groups=1 set-variable = innodb_log_files_in_group=3 set-variable = innodb_log_file_size=5M set-variable = innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=1 innodb_log_archive=0 set-variable = innodb_buffer_pool_size=16M set-variable = innodb_additional_mem_pool_size=2M set-variable = innodb_file_io_threads=4 set-variable = innodb_lock_wait_timeout=50 [mysqldump] quick set-variable = max_allowed_packet=16M [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates [isamchk] set-variable = key_buffer=8M set-variable = sort_buffer=8M [myisamchk] set-variable = key_buffer=8M set-variable = sort_buffer=8M [mysqlhotcopy] interactive-timeout ### ###FOR SLAVE # # This will be passed to all mysql clients [client] #password=my_password port=3301 #socket=MySQL # Here is entries for some specific programs # The following values assume you have at least 32M ram # The MySQL server [mysqld] basedir = c:/mysql4_slave/ datadir = c:/mysql4_slave/data/ port=3301 language=polish default-character-set=latin2 server-id=2 # log-bin # log-slave-updates master-host=127.0.0.1 master-user=irek master-password=XX master-port=3300 master-connect-retry=30 # log-update=log_updates.log log-warnings set-variable = key_buffer=16K set-variable = max_allowed_packet=1M set-variable = thread_stack=64K set-variable = table_cache=4 set-variable = sort_buffer=64K set-variable = net_buffer_length=2K query_cache_size = 1024K # Uncomment the following if you are using Innobase tables innodb_data_file_path = ibdata1:50M innodb_data_home_dir = d:\\innodb\\mysql4\\slave\\ibdata innodb_log_group_home_dir = d:\\innodb\\mysql4\\slave\\iblogs innodb_log_arch_dir = d:\\innodb\\mysql4\\slave\\iblogs set-variable = innodb_mirrored_log_groups=1 set-variable = innodb_log_files_in_group=3 set-variable = innodb_log_file_size=5M set-variable = innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=1 innodb_log_archive=0 set-variable = innodb_buffer_pool_size=16M set-variable = innodb_additional_mem_pool_size=2M set-variable = innodb_file_io_threads=4 set-variable = innodb_lock_wait_timeout=50 [mysqldump] quick set-variable = max_allowed_packet=16M [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates [isamchk] set-variable = key_buffer=8M set-variable = sort_buffer=8M [myisamchk] set-variable = key_buffer=8M set-variable = sort_buffer=8M ## END configuration So i have noticed this things. I start replication with master and slave: 1) Master is running ... --- Slave is running ... and at console can i see: 030804 22:55:36 InnoDB: Started 030804 22:55:36 Slave I/O thread: connected to master '[EMAIL PROTECTED]:3300', r eplication started in log 'FIRST' at position 4 030804 22:55:36 Slave SQL thread initialized, starting replication in log 'FIRS T' at position 0, relay log '.\hq-relay-bin.001' position: 4 mysqld-max-nt
