Re: master/slave replication - errors!!
bruce wrote: hi.. doing a simple test of master/slave replication, using mysql. i have two test systems: master - foo (192.168.10.13) slave - cat (192.168.20.20) on both machines, i created a testmasterdb. on the master, i populated the tbl within the db with some test data. there are no tbls in the slave, only the create database... for the master, the my.cnf is: [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=1 log-bin=/var/log/mysql/mysql-bin.log binlog-do-db=testmasterdb server-id=11 [mysql.server] user=mysql basedir=/var/lib [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid for the slave, the my.cnf is: [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=1 #replication - slave server server_id=2 master_host=mfgtest3.stratalight.com master_user=slave master_password=slave master_connect_retry=60 replicate-do-db=testmasterdb Use CHANGE MASTER TO instead. It avoids a lot of potential problems. Settings in the my.cnf is deprecated. [mysql.server] user=mysql basedir=/var/lib [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid on the master, (logged in as root) i granted replication privileges to my test user (slave/slave). on the slave mysql, i then tried to do a load data from master Don't use LOAD DATA FROM MASTER either. It only ever worked for certain cases and is also deprecated. You should do a dump-and-load or file copy to initialize the slave. and got the following error: Error running query on master: Access denied; you need the RELOAD privilege for this operation do i need to run the load data cmd from the slave, when i'm logged in as user slave, i would think that being root would allow me to issue the cmd? do i have to have the physical ipaddress or the master in the my.cnf file? (i would think i could have the fqdn, given that ips change -dhcp) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
master/slave replication - errors!!
hi.. doing a simple test of master/slave replication, using mysql. i have two test systems: master - foo (192.168.10.13) slave - cat (192.168.20.20) on both machines, i created a testmasterdb. on the master, i populated the tbl within the db with some test data. there are no tbls in the slave, only the create database... for the master, the my.cnf is: [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=1 log-bin=/var/log/mysql/mysql-bin.log binlog-do-db=testmasterdb server-id=11 [mysql.server] user=mysql basedir=/var/lib [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid for the slave, the my.cnf is: [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=1 #replication - slave server server_id=2 master_host=mfgtest3.stratalight.com master_user=slave master_password=slave master_connect_retry=60 replicate-do-db=testmasterdb [mysql.server] user=mysql basedir=/var/lib [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid on the master, (logged in as root) i granted replication privileges to my test user (slave/slave). on the slave mysql, i then tried to do a load data from master and got the following error: Error running query on master: Access denied; you need the RELOAD privilege for this operation do i need to run the load data cmd from the slave, when i'm logged in as user slave, i would think that being root would allow me to issue the cmd? do i have to have the physical ipaddress or the master in the my.cnf file? (i would think i could have the fqdn, given that ips change -dhcp) any thoughts/ideas/comments... thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ~Replication errors~
MySQL's pretty good when it describes an error. (if the error message is vague you might be screwed, but MySQL is pretty good). Which means: 1) You didn't properly change the max_allowed_packet on the master. what does show variables like max_allowed_packet; show you? 2) The slave is actually running fine and you're looking at an old error. What does SHOW SLAVE STATUS; show you on the master? 3) Did you change max_allowed_packet on the slave? I think it would need to be changed on the slave as well. -Sheeri On 5/10/06, Mohammed Abdul Azeem [EMAIL PROTECTED] wrote: Hi, Iam getting the following error on my Mysql Slave server. This happened when my disk space got full and there was no space left on the device. I managed to free up some space and then ran mysql STOP SLAVE mysql CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000110', MASTER_LOG_POS=850202232; mysql START SLAVE I could find that replication started but with the following error. Can anyone help me out in fixing the issue ? I tried increasing the max_allowed_packet on master server but with no luck. 060510 0:56:22 [Note] Slave I/O thread: connected to master '[EMAIL PROTECTED]:3306', replication started in log 'mysql-bin.000110' at position 850202232 060510 0:56:22 [ERROR] Error reading packet from server: log event entry exceeded max_allowed_packet; Increase max_allowed_packet on master ( server_errno=1236) 060510 0:56:22 [ERROR] Got fatal error 1236: 'log event entry exceeded max_allowed_packet; Increase max_allowed_packet on master' from master when readingdata from binary log 060510 0:56:22 [Note] Slave I/O thread exiting, read up to log 'mysql- bin.000110', position 850202232 Thanks in advance, Abdul. This email has been Scanned for Viruses! www.newbreak.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 errors~
Hi, Iam getting the following error on my Mysql Slave server. This happened when my disk space got full and there was no space left on the device. I managed to free up some space and then ran mysql STOP SLAVE mysql CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000110', MASTER_LOG_POS=850202232; mysql START SLAVE I could find that replication started but with the following error. Can anyone help me out in fixing the issue ? I tried increasing the max_allowed_packet on master server but with no luck. 060510 0:56:22 [Note] Slave I/O thread: connected to master '[EMAIL PROTECTED]:3306', replication started in log 'mysql-bin.000110' at position 850202232 060510 0:56:22 [ERROR] Error reading packet from server: log event entry exceeded max_allowed_packet; Increase max_allowed_packet on master ( server_errno=1236) 060510 0:56:22 [ERROR] Got fatal error 1236: 'log event entry exceeded max_allowed_packet; Increase max_allowed_packet on master' from master when readingdata from binary log 060510 0:56:22 [Note] Slave I/O thread exiting, read up to log 'mysql- bin.000110', position 850202232 Thanks in advance, Abdul. This email has been Scanned for Viruses! www.newbreak.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
replication errors
Lo again,
Master and Slave server, both running 5.0.2-LOG (Exactly the same versions).
On the master, everything is working fine. Data that gets inserted /
updated / deleted etc goes into the bin log, and gets replicated to the
slave.
HOWEVER
On the slave, I am getting syntax errors from the queries received from the
master?!?!?!?! HOW can I query NOT be correct when it gets to the slave, but
yet on the master, the query was issued successfully???
The latest example:
mysql SHOW SLAVE STATUS\G
*** 1. row ***
Slave_IO_State: Waiting for master to send event
Master_Host: 198.19.0.35
Master_User: replicator
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.01
Read_Master_Log_Pos: 8916111
Relay_Log_File: netsphere-relay-bin.09
Relay_Log_Pos: 956043
Relay_Master_Log_File: mysql-bin.01
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: 1064
Last_Error: Error 'You have an error in your SQL syntax;
check the manual that corresponds to your MySQL server version for the right
syntax to use near ''64.7.0/B' at line 1' on query. Default database: 'DB'.
Query: 'INSERT INTO GlobalCountryPrefix (CountryID, Network, Prefix) VALUES
('230', '64.7.0/B'
Skip_Counter: 0
Exec_Master_Log_Pos: 5911231
Relay_Log_Space: 3963045
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: 79761
1 row in set (0.00 sec)
Sure, there is a syntax error in the query - yes. But why does the master
not put the complete query in the bin log? This is the second time
in 3 days that it has happened now. Whilst we are on this, how do I tell
the slave to skip the query and proceed with the rest of the binlog...
Thanks to this now, we already have a MASSIVE backlog that needs to be
processed.
--
Chris.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: replication errors
Chris Knipe wrote: [..] Sure, there is a syntax error in the query - yes. But why does the master not put the complete query in the bin log? This is the second time in 3 days that it has happened now. Whilst we are on this, how do I tell the slave to skip the query and proceed with the rest of the binlog... Thanks to this now, we already have a MASSIVE backlog that needs to be processed. [..] I do not know if it is a best solution but that's shure that it's an easiest. Just add slave-skip-errors = all to the config... or just with errors that you want to skip, refer to the manual for an extensive description. -- Irek Slonina -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: replication errors
Chris Knipe wrote: [..] Sure, there is a syntax error in the query - yes. But why does the master not put the complete query in the bin log? This is the second time in 3 days that it has happened now. Whilst we are on this, how do I tell the slave to skip the query and proceed with the rest of the binlog... Thanks to this now, we already have a MASSIVE backlog that needs to be processed. [..] I do not know if it is a best solution but that's shure that it's an easiest. Just add slave-skip-errors = all to the config... or just with errors that you want to skip, refer to the manual for an extensive description. Ok, that's fair enough, and it seems to be working again for now. But just how realiable is MySQL's replication to begin with? Skipping the errors does not resolve my problem. Skiping the errors mearly means that my slave server will not have a accurate representation of the data on the master. Surely, there must be a reason why the Master server is not logging queries properly into the binary log??? Either it's not being logged right, or the Slave is not reading it correctly. If it was different versions of MySQL, I could have perhaps let this fly with a incompatibility of sorts, but they are exactly the same versions?? And this is not the first time it's happening either... It really makes me doubt whether MySQL is the right approach to take to this whole replication vs data redundancy scenario. -- Chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: replication errors
Chris Knipe wrote: [..] Sure, there is a syntax error in the query - yes. But why does the master not put the complete query in the bin log? This is the second time in 3 days that it has happened now. Whilst we are on this, how do I tell the slave to skip the query and proceed with the rest of the binlog... Thanks to this now, we already have a MASSIVE backlog that needs to be processed. [..] I do not know if it is a best solution but that's shure that it's an easiest. Just add slave-skip-errors = all to the config... or just with errors that you want to skip, refer to the manual for an extensive description. Ok, that's fair enough, and it seems to be working again for now. But just how realiable is MySQL's replication to begin with? Skipping the errors does not resolve my problem. Skiping the errors mearly means that my slave server will not have a accurate representation of the data on the master. Surely, there must be a reason why the Master server is not logging queries properly into the binary log??? Either it's not being logged right, or the Slave is not reading it correctly. If it was different versions of MySQL, I could have perhaps let this fly with a incompatibility of sorts, but they are exactly the same versions?? And this is not the first time it's happening either... It really makes me doubt whether MySQL is the right approach to take to this whole replication vs data redundancy scenario. -- Chris. Now, I've been running for not even 1 hour with the skip-errors enabled A quick check, on ONE table... [EMAIL PROTECTED]:~# mysql --host=mysqldb01 --database=DB -p Enter password: Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A elcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 159 to server version: 5.0.2-alpha-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql SELECT COUNT(RadAcctId) FROM RadiusAccounting; +--+ | COUNT(RadAcctId) | +--+ |1144320 | +--+ 1 row in set (1.69 sec) mysql quit Bye [EMAIL PROTECTED]:~# mysql --host=mysqldb02 --database=DB -p Enter password: Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 105 to server version: 5.0.2-alpha-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql SELECT COUNT(RadAcctId) FROM RadiusAccounting; +--+ | COUNT(RadAcctId) | +--+ |1144218 | +--+ 1 row in set (0.05 sec) mysql So there's already data missing on the slave *shrugs* -- Chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: replication errors
Chris Knipe wrote: Now, I've been running for not even 1 hour with the skip-errors enabled A quick check, on ONE table... [EMAIL PROTECTED]:~# mysql --host=mysqldb01 --database=DB -p Enter password: Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A elcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 159 to server version: 5.0.2-alpha-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql SELECT COUNT(RadAcctId) FROM RadiusAccounting; +--+ | COUNT(RadAcctId) | +--+ |1144320 | +--+ 1 row in set (1.69 sec) mysql quit Bye [EMAIL PROTECTED]:~# mysql --host=mysqldb02 --database=DB -p Enter password: Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 105 to server version: 5.0.2-alpha-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql SELECT COUNT(RadAcctId) FROM RadiusAccounting; +--+ | COUNT(RadAcctId) | +--+ |1144218 | +--+ 1 row in set (0.05 sec) mysql So there's already data missing on the slave *shrugs* -- Chris. I was thinking that master is executing the same queries that slave does - if an error exists on slave then it first comes up on the master and master is not executing it either. You prove that I was wrong... now I must check the integrity of the data on my slaves, which all are running with slave skip errors turned on :/. -- Irek Slonina -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: replication errors
mysql SELECT COUNT(RadAcctId) FROM RadiusAccounting; +--+ | COUNT(RadAcctId) | +--+ |1144320 | +--+ 1 row in set (1.69 sec) [EMAIL PROTECTED]:~# mysql --host=mysqldb02 --database=DB -p mysql SELECT COUNT(RadAcctId) FROM RadiusAccounting; +--+ | COUNT(RadAcctId) | +--+ |1144218 | +--+ 1 row in set (0.05 sec) So there's already data missing on the slave *shrugs* Or there is data still pending in the binlog between master and slave. Replication is not instantaneous. If you shut down the Master and wait, does all the data make it to the slave? I'm not promising it's not broken: I'm just promising that your analysis is too brief and incomplete to be conclusive. :-) -- Like Music? http://l-i-e.com/artists.htm -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: replication errors
- Original Message - From: Richard Lynch [EMAIL PROTECTED] To: Chris Knipe [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Friday, March 11, 2005 12:08 AM Subject: Re: replication errors mysql SELECT COUNT(RadAcctId) FROM RadiusAccounting; +--+ | COUNT(RadAcctId) | +--+ |1144320 | +--+ 1 row in set (1.69 sec) [EMAIL PROTECTED]:~# mysql --host=mysqldb02 --database=DB -p mysql SELECT COUNT(RadAcctId) FROM RadiusAccounting; +--+ | COUNT(RadAcctId) | +--+ |1144218 | +--+ 1 row in set (0.05 sec) So there's already data missing on the slave *shrugs* Or there is data still pending in the binlog between master and slave. Replication is not instantaneous. If you shut down the Master and wait, does all the data make it to the slave? I'm not promising it's not broken: I'm just promising that your analysis is too brief and incomplete to be conclusive. :-) I thought someone would bring this up. I should have mentioned in my original post, I did check, the slave was up to date... Quick test on the slave: [EMAIL PROTECTED]:~# mysql --host=mysqldb02 --database=DB -p Enter password: Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1806 to server version: 5.0.2-alpha-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql SHOW SLAVE STATUS\G *** 1. row *** Slave_IO_State: Waiting for master to send event Master_Host: 198.19.0.35 Master_User: replicator Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.06 Read_Master_Log_Pos: 7634 Relay_Log_File: netsphere-relay-bin.29 Relay_Log_Pos: 7768 Relay_Master_Log_File: mysql-bin.06 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 7634 Relay_Log_Space: 7768 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: 9 1 row in set (0.00 sec) mysql SELECT COUNT(RadAcctID) FROM RadiusAccounting; +--+ | COUNT(RadAcctID) | +--+ |1144374 | +--+ 1 row in set (0.05 sec) mysql On the master... [EMAIL PROTECTED]:~# mysql --host=mysqldb01 --database=DB -p Enter password: Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 225 to server version: 5.0.2-alpha-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql SHOW MASTER STATUS; +--+--+--+--+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +--+--+--+--+ | mysql-bin.06 | 7634 | | | +--+--+--+--+ 1 row in set (0.04 sec) mysql SELECT COUNT(RadAcctID) FROM RadiusAccounting; +--+ | COUNT(RadAcctID) | +--+ |1144475 | +--+ 1 row in set (0.12 sec) mysql -- Chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: replication errors
Hi Chris, OK, I can't take this anymore. :) Now, I've been running for not even 1 hour with the skip-errors enabled A quick check, on ONE table... snip So there's already data missing on the slave *shrugs* Yep, you told it to SKIP ERRORS. That means that if any query generates an error, for any reason, it's just going to skip over it and move on. So data was missing on your slave the moment you restarted replication -- it skipped the UPDATE it had a problem with. From that moment on, your replication was out of sync. And this is not the first time it's happening either... It really makes me doubt whether MySQL is the right approach to take to this whole replication vs data redundancy scenario. You do realize that MySQL 5.x is ALPHA, right? Alpha doesn't mean works beautifully on production systems, it means probably, very likely, broken in some way. Why are you using an ALPHA version and expecting it to work perfectly, and when it doesn't, doubting MySQL and replication? If you want it to be stable, use a STABLE version. Regards, Jeremy -- Jeremy Cole Technical Yahoo - MySQL (Database) Geek -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: replication errors
So there's already data missing on the slave *shrugs* Yep, you told it to SKIP ERRORS. That means that if any query generates an error, for any reason, it's just going to skip over it and move on. So data was missing on your slave the moment you restarted replication -- it skipped the UPDATE it had a problem with. From that moment on, your replication was out of sync. Correct yes. As I said, my problem is not the data that is missing, my problem is the fact that a update / insery query executes successfully on the master, but generates a error on the slave because the bin logs aren't read / written properly... That's the problem here, not the missing data... For that I have backups. -- Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: replication errors
Irek Sonina wrote: I was thinking that master is executing the same queries that slave does - if an error exists on slave then it first comes up on the master and master is not executing it either. You prove that I was wrong... now I must check the integrity of the data on my slaves, which all are running with slave skip errors turned on :/. Anyone thinking of using slave-skip-errors should read the documentation about it, which should be enough to scare them away from that option (especially all): | You can (but should not) also use the very non-recommended | value of _all_ which ignores all error messages and keeps | barging along regardless of what happens. Needless to say, if | you use it, we make no promises regarding your data integrity. | Please do not complain if your data on the slave is not | anywhere close to what it is on the master in this case. You | have been warned. http://dev.mysql.com/doc/mysql/en/replication-options.html -- Keith Ivey [EMAIL PROTECTED] Smokefree DC http://www.smokefreedc.org Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Replication errors...
Thanks! I was hoping it was something that had already been found. I'll upgrade as soon as I can. Thanks, Brian -Original Message- From: Sasha Pachev [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 24, 2004 10:47 PM To: Stanton, Brian Cc: '[EMAIL PROTECTED]' Subject: Re: Replication errors... Stanton, Brian wrote: Shortly after the MySQL 4.0 line went to production, I upgraded to mysql 4.0.12. Since then my slave has been getting corrupted tables 2 to 3 times every month. I've also seen this problem in mysql 4.0.13. When I run a check table on the table in question it gives the following results: +-+---+--+-- ---+ | Table | Op| Msg_type | Msg_text | +-+---+--+-- ---+ | database.table | check | warning | Table is marked as crashed | | database.table | check | warning | 2 clients is using or hasn't closed the table properly | | database.table | check | warning | Not used space is supposed to be: 526688 but is: 522768 | | database.table | check | error| record delete-link-chain corrupted | | database.table | check | error| Corrupt | +-+---+--+-- ---+ 5 rows in set (0.01 sec) It repairs just fine and then replication continues, but I never ran into this issue in the 3.23.xx line. Has anyone else been seeing this problem? Has it been fixed in a later 4.0.x version? ERROR: 1030 Got error 127 from table handler 040207 3:44:03 Slave: error 'Got error 127 from table handler' on query ... 040207 3:44:03 Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with SLAVE START. We stopped at log ... I've seen it on my systems. The problem is a bug in DELETE in 4.0 that was fixed in 4.0.18. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication errors...
Stanton, Brian wrote: Shortly after the MySQL 4.0 line went to production, I upgraded to mysql 4.0.12. Since then my slave has been getting corrupted tables 2 to 3 times every month. I've also seen this problem in mysql 4.0.13. When I run a check table on the table in question it gives the following results: +-+---+--+-- ---+ | Table | Op| Msg_type | Msg_text | +-+---+--+-- ---+ | database.table | check | warning | Table is marked as crashed | | database.table | check | warning | 2 clients is using or hasn't closed the table properly | | database.table | check | warning | Not used space is supposed to be: 526688 but is: 522768 | | database.table | check | error| record delete-link-chain corrupted | | database.table | check | error| Corrupt | +-+---+--+-- ---+ 5 rows in set (0.01 sec) It repairs just fine and then replication continues, but I never ran into this issue in the 3.23.xx line. Has anyone else been seeing this problem? Has it been fixed in a later 4.0.x version? ERROR: 1030 Got error 127 from table handler 040207 3:44:03 Slave: error 'Got error 127 from table handler' on query ... 040207 3:44:03 Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with SLAVE START. We stopped at log ... I've seen it on my systems. The problem is a bug in DELETE in 4.0 that was fixed in 4.0.18. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication errors...
Shortly after the MySQL 4.0 line went to production, I upgraded to mysql 4.0.12. Since then my slave has been getting corrupted tables 2 to 3 times every month. I've also seen this problem in mysql 4.0.13. When I run a check table on the table in question it gives the following results: +-+---+--+-- ---+ | Table | Op| Msg_type | Msg_text | +-+---+--+-- ---+ | database.table | check | warning | Table is marked as crashed | | database.table | check | warning | 2 clients is using or hasn't closed the table properly | | database.table | check | warning | Not used space is supposed to be: 526688 but is: 522768 | | database.table | check | error| record delete-link-chain corrupted | | database.table | check | error| Corrupt | +-+---+--+-- ---+ 5 rows in set (0.01 sec) It repairs just fine and then replication continues, but I never ran into this issue in the 3.23.xx line. Has anyone else been seeing this problem? Has it been fixed in a later 4.0.x version? ERROR: 1030 Got error 127 from table handler 040207 3:44:03 Slave: error 'Got error 127 from table handler' on query ... 040207 3:44:03 Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with SLAVE START. We stopped at log ... Thanks, Brian I don't need any of that SQL stuff -- I just want a database!
Replication errors
Hi I've got a master and a slave both at 3.23.49. I've been running replication for the last 3 months and all of a sudden I've got queries failing on the slave with duplicate entries (error 1062). From the documentation all I can figure out is that there's a possibility I might be using different character sets on databases. How do I check what the default character set is? Failing that... any other areas that might cause a similar problem. I've already tried set slave skip Rgds Mike E-mail: [EMAIL PROTECTED] -- Live Life in Broadband www.telewest.co.uk The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Statements and opinions expressed in this e-mail may not represent those of the company. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender immediately and delete the material from any computer. == -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication Errors *NEWEST*
NEWEST** I am now getting this error while trying to Replicate to a second MySQL server. 021125 0:41:01 Slave: Failed reading log event, reconnecting to retry, log 'bsd1-bin.001' position 73 021125 0:41:01 Slave: reconnected to master '[EMAIL PROTECTED]:3306',replication resumed in log 'bsd1-bin.001' at position 73 021125 0:41:17 Error reading packet from server: Could not open log file (server_errno=2) 021125 0:41:17 Slave: Failed reading log event, reconnecting to retry, log 'bsd1-bin.002' position 86 021125 0:41:17 Slave: reconnected to master '[EMAIL PROTECTED]:3306',replication resumed in log 'bsd1-bin.002' at position 86 021125 0:41:17 Error reading packet from server: Could not find first log (server_errno=65535) 021125 0:41:37 Slave thread killed while waiting to reconnect after a failed read 021125 0:41:37 Slave thread exiting, replication stopped in log 'bsd1-bin.002' at position 86 021125 0:41:37 Slave: connected to master '[EMAIL PROTECTED]:3306', replication started in log 'FIRST' at position 4 I have added the following to the master: [mysqld] socket=/tmp/mysql.sock server-id=1 log-bin [safe_mysqld] socket=/tmp/mysql.sock server-id=1 log-bin And added this to the slave: [mysqld] server-id=2 master-host=192.168.0.16 master-user=replicate master-password=mypasshere Any ideas on this? Thanks. Lewis - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Replication Errors
Any suggestions? This is a live DB ... 021118 10:43:47 InnoDB: Started /usr/libexec/mysqld: ready for connections 1254 021118 10:43:49 Slave: connected to master '[EMAIL PROTECTED]:3303', replication started in log 'FIRST' at position 31804350 ' at line 1 ERROR: 1064 You have an error in your SQL syntax near '¥fÓ=áëm 021118 10:43:49 Slave: error running query 'replace into relay ( ip_add 021118 10:43:49 Error running query, slave aborted. Fix the problem, and re-start the slave thread with mysqladmin start-slave. We stopped at log 'FIRST' position 31804350 021118 10:43:49 Slave thread exiting, replication stopped in log 'FIRST' at position 31804350 -- Michael T. Babcock C.T.O., FibreSpeed Ltd. http://www.fibrespeed.net/~mbabcock - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Replication Errors
On Mon, Nov 18, 2002 at 11:47:48AM -0500, Michael T. Babcock wrote: Any suggestions? This is a live DB ... 021118 10:43:47 InnoDB: Started /usr/libexec/mysqld: ready for connections 1254 021118 10:43:49 Slave: connected to master '[EMAIL PROTECTED]:3303', replication started in log 'FIRST' at position 31804350 ' at line 1 ERROR: 1064 You have an error in your SQL syntax near '¥fÓ=áëm 021118 10:43:49 Slave: error running query 'replace into relay ( ip_add 021118 10:43:49 Error running query, slave aborted. Fix the problem, and re-start the slave thread with mysqladmin start-slave. We stopped at log 'FIRST' position 31804350 021118 10:43:49 Slave thread exiting, replication stopped in log 'FIRST' at position 31804350 I hate to ask this, bug did you change anything? :-) What MySQL versions are involved? Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 3.23.51: up 104 days, processed 2,259,373,066 queries (250/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Replication Errors
Jeremy Zawodny wrote: I hate to ask this, bug did you change anything? :-) In the data? Heck ya :-) ... the versions of the software? No -- although either of them has restarted / flushed logs from time to time as necessary (I don't have a log rotation program yet for the binary logs mind you). What MySQL versions are involved? 3.23.53 on both sides. -- Michael T. Babcock C.T.O., FibreSpeed Ltd. http://www.fibrespeed.net/~mbabcock - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Replication errors
I have replication running successfully between two Linux boxes running 3.23.51. However, I'm getting the following error very frequently (every 30 - 60 seconds) in my slave error log: - 020703 15:41:09 Error reading packet from server: (server_errno=1159) 020703 15:42:09 Slave: Failed reading log event, reconnecting to retry, log 'db1-bin.009' position 25448 020703 15:42:09 Slave: reconnected to master '[EMAIL PROTECTED]:3306',replication resumed in log 'db1-bin.009' at position 25448 020703 15:42:39 Error reading packet from server: (server_errno=1159) 020703 15:43:39 Slave: Failed reading log event, reconnecting to retry, log 'db1-bin.009' position 25448 020703 15:43:39 Slave: reconnected to master '[EMAIL PROTECTED]:3306',replication resumed in log 'db1-bin.009' at position 25448 - Is this just a normal timeout error, because there's currently very little activity on these machines? Or is there some other problem? The slave_net_timeout value is at it's default of 3600, so I would expect to see this type of error every hour -- but maybe I misunderstand that variable. If this is just a normal timeout, is there any way to up the value or prevent these errors from being logged? Thanks, --jeff sql,query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Replication errors
Pada Wed, 3 Jul 2002 12:56:30 -0700 Jeff Kilbride [EMAIL PROTECTED] menulis : Is this just a normal timeout error, because there's currently very little activity on these machines? Or is there some other problem? The slave_net_timeout value is at it's default of 3600, so I would expect to see this type of error every hour -- but maybe I misunderstand that variable. If this is just a normal timeout, is there any way to up the value or prevent these errors from being logged? change this value master-connect-retry = 3600 sql,query -- Let's call it an accidental feature. -- Larry Wall - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Replication errors
Anyone have any ideas as to how I should handle a case of the following error? 020102 13:27:28 Slave: Failed reading log event, reconnecting to retry, log 'db1-bin.008' position 1064155638 020102 13:27:28 Slave: connected to master '[EMAIL PROTECTED]:3306',replication resumed in log 'db1-bin.008' at position 1064155638 020102 13:27:28 Error reading packet from server: binlog truncated in the middle of event (read_errno 0,server_errno=65535) Replication seems to have stopped at that point and the slave keeps trying to connect and dies at with that error. // database,sql,query,table Thanks, Michael -- Michael Brunson 504.473.6643 [EMAIL PROTECTED] ICQ: 83163789 --- Intercosmos Media Group, Inc. --- www.intercosmos.comwww.directnic.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
replication errors (broken binlog-do-db?)
Description: I have two DB servers that we will simply call MASTER and SLAVE. MASTER is started with --binlog-do-db=DB1 so that only DB1 will be replicated to SLAVE. When SLAVE is initially started everything seems normal, in that all update/delete/insert/etc. queries are propogated to SLAVE. However, if I issue a query similar to the one below, the SLAVE dies: Example Query: INSERT INTO DB2.sometable (field1,field2) SELECT field1,field2 FROM DB1.sometable WHERE (some condition); Query Explanation: Basically it moves all data from one table in DB1 to a table in DB2 Error: mysql SHOW SLAVE STATUS\G *** 1. row *** Master_Host: master.host Master_User: replication Master_Port: 3306 Connect_retry: 60 Log_File: master-bin.001 Pos: 31191383 Slave_Running: No Replicate_do_db: Replicate_ignore_db: Last_errno: 1146 Last_error: error 'Table 'DB2.sometable' doesn't exist' on query 'INSERT INTO DB2.sometable (field1,field2) SELECT field1,field2 FROM DB1.sometable WHERE (some condition);' Skip_counter: 0 1 row in set (0.00 sec) How-To-Repeat: I can reproduce this error with the above configuration at any time. All servers are binary distribution of 3.23.44 for linux (glibc). MySQLd startup options for each server are as follows: MASTER -- --set-variable back_log=100 --set-variable long_query_time=5 --set-variable delayed_insert_timeout=60 --set-variable delayed_insert_limit=30 --set-variable max_connections=256 --set-variable max_connect_errors=1 --set-variable table_cache=256 --set-variable wait_timeout=60 --set-variable thread_cache_size=10 --set-variable key_buffer_size=64M --log-bin --server-id=1 --binlog-do-db=DB1 --user=mysql SLAVE - --set-variable back_log=100 --set-variable long_query_time=5 --set-variable max_connections=256 --set-variable max_connect_errors=1 --set-variable table_cache=256 --set-variable wait_timeout=60 --master-host=master.host --master-user=someuser --master-password=XXX --server-id=2 --user=mysql Fix: Well I can rewrite my query, but that doesn't seem like a permanant solution. I'm open to suggestions. The query IMHO shouldn't be getting put into the binary log since the DB that is effected is not the one specified with --binlod-do-db flag. I suspect whatever --binlog-do-db does it at fault. This behavior may be by design, I don't know. Any response would be appreciated, I am not on the mysql list so please respond via personal email if possible. Even if this behavior is intentional I would appreciate knowing, so I can determine what needs redesigned in my system. Submitter-Id: submitter ID Originator:wrath Organization: MySQL support: none Synopsis: Replication errors with INSERT INTO..SELECT FROM Severity: serious Priority: medium Category: mysql Class: sw-bug Release: mysql-3.23.44 (Official MySQL binary) Environment: System: Linux XX 2.2.19 #2 Fri Jun 8 04:23:06 UTC 2001 i686 unknown Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-slackware-linux/egcs-2.91.66/specs gcc version egcs-2.91.66 19990314/Linux (egcs-1.1.2 release) Compilation info: CC='gcc' CFLAGS='-O3 -mpentium ' CXX='gcc' CXXFLAGS='-O3 -mpentium -felide-constructors' LDFLAGS='-static' LIBC: lrwxrwxrwx 1 root root 13 Apr 19 2001 /lib/libc.so.6 - libc-2.1.3.so -rwxr-xr-x 1 root root 1013224 Mar 21 2000 /lib/libc-2.1.3.so -rw-r--r-- 1 root root 20266642 Mar 20 2000 /usr/lib/libc.a -rw-r--r-- 1 root root 178 Mar 20 2000 /usr/lib/libc.so Configure command: ./configure --prefix=/usr/local/mysql '--with-comment=Official MySQL binary' --with-extra-charsets=complex --with-server-suffix= --enable-assembler --with-mysqld-ldflags=-all-static --with-client-ldflags=-all-static --disable-shared - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php
replication errors
I have a one-way replication setup where the master is out of state, and the slave is local. The connection between the machines is high-speed, but not 100% reliable. Every once in a while, my slave thread stops because of a duplicate key entry. I'm puzzled by this for several reasons: 1. Insertions and updates are made on the master, and nowhere else. 2. I know that if a slave loses connection, it will attempt to restart - so network disruptions shouldn't be a big factor. I am running: mysql Ver 11.6 Distrib 3.23.28-gamma, for sun-solaris2.7 (sparc) on both machines. Does anyone know where I might be able to start finding clues to solving this mystery? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
