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]
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]
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
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
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
