RE: Replication Issues
That appear to have been it, thanks to all for the assist and have a great weekend! Dirk Bremer - Senior Systems Engineer - Utility - AMS NISC Lake St. Louis MO - USA Central Time Zone 636-755-2652 fax 636-755-2502 dirk.bre...@nisc.coop www.nisc.coop -Original Message- From: Johnny Withers [mailto:joh...@pixelated.net] Sent: Thursday, March 26, 2009 18:00 To: mysql@lists.mysql.com Subject: Re: Replication Issues I notice in the startup messages there is an underscore in the file names, but there are dashes in the dir list you provided. ??? On 3/26/09, Dirk Bremer dirk.bre...@nisc.coop wrote: On the master in the directory specified in the my.ini on the master, there are seven log-files: D:\Logsdir mysql*.* Volume in drive D is Local Disk Volume Serial Number is A46F-D8E7 Directory of D:\Logs 2009-03-25 00:1738,366,715 mysql-bin.01 2009-03-25 00:17 198 mysql-bin.02 2009-03-25 00:17 672 mysql-bin.03 2009-03-26 00:1856,190,604 mysql-bin.04 2009-03-26 00:18 672 mysql-bin.05 2009-03-26 16:5121,984,192 mysql-bin.06 2009-03-26 17:00 414,487 mysql-bin.07 2009-03-26 16:51 175 mysql-bin.index The contents of the mysql-bin.index are: D:\Logs\mysql-bin.01 D:\Logs\mysql-bin.02 D:\Logs\mysql-bin.03 D:\Logs\mysql-bin.04 D:\Logs\mysql-bin.05 D:\Logs\mysql-bin.06 D:\Logs\mysql-bin.07 There is nothing in the master's error-log. I followed the instructions here: http://forums.mysql.com/read.php?26,9390,242387#msg-242387 With negative results. Dirk Bremer - Senior Systems Engineer - Utility - AMS NISC Lake St. Louis MO - USA Central Time Zone 636-755-2652 fax 636-755-2502 dirk.bre...@nisc.coop www.nisc.coop -Original Message- From: Claudio Nanni [mailto:claudio.na...@gmail.com] Sent: Thursday, March 26, 2009 16:18 To: Dirk Bremer Cc: mysql@lists.mysql.com Subject: Re: Replication Issues Hi Dirk, it seems that the information about binary files on the master contained in the mysql-bin.index does not match the actual files on the disk. The first two log messages are just info on the slave starting the two replication threads, the other two seem to be a message coming from the master which says I can't find the binary log file! check the content of the index file and check the binary log files actually present on the master. Cheers Claudio Dirk Bremer wrote: We recently completed a project where all of our production servers were replaced. As part of this project, one of the new servers acted as a replication-slave to one of the old servers, which was the replication-master. I had replication up and running just fine on multiple slaves for a couple of years. Last weekend I moved all functionality from the old server to the new server so that the new server is now setup at the replication-master. I am trying to setup replication-slaves on some of the other new servers and have run into problems doing so. In the my.ini for the master, there is: [mysqld] log-error=D:\Logs\mysql_error.log log-warnings server-id=1 lower_case_table_names=2 log-bin=D:\Logs\mysql-bin.log tmpdir=D:/Temp The master is creating the binary-log and index. There is a replication account setup on the master called replication. The slave is creating a relay-log and index, has what appears to be correct master.info and relay-log.info files. When I start the slave I get the following in the error log: 090325 9:03:30 [Note] Slave SQL thread initialized, starting replication in log 'mysql_bin.04' at position 10120038, relay log '.\AMSCD2-relay-bin.02' position: 4 090325 9:03:30 [Note] Slave I/O thread: connected to master 'replicat...@amstcop:3306', replication started in log 'mysql_bin.04' at position 10120038 090325 9:03:30 [ERROR] Error reading packet from server: Could not find first log file name in binary log index file (server_errno=1236) 090325 9:03:30 [ERROR] Got fatal error 1236: 'Could not find first log file name in binary log index file' from master when reading data from binary log I'm sure that I've overlooked something, well, stupid, but have struggled with it the last few days to no avail. All servers are using the same version of 4.1.22-community-nt. I feel a bit naked without the replication so your assistance in helping me getting it up and running will be very appreciated. Dirk Bremer - Senior Systems Engineer - Utility - AMS NISC Lake St. Louis MO - USA Central Time Zone 636-755-2652 fax 636-755-2502 dirk.bre...@nisc.coop www.nisc.coop -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=joh...@pixelated.net -- - Johnny Withers 601.209.4985 joh...@pixelated.net -- MySQL General
Replication Issues
We recently completed a project where all of our production servers were replaced. As part of this project, one of the new servers acted as a replication-slave to one of the old servers, which was the replication-master. I had replication up and running just fine on multiple slaves for a couple of years. Last weekend I moved all functionality from the old server to the new server so that the new server is now setup at the replication-master. I am trying to setup replication-slaves on some of the other new servers and have run into problems doing so. In the my.ini for the master, there is: [mysqld] log-error=D:\Logs\mysql_error.log log-warnings server-id=1 lower_case_table_names=2 log-bin=D:\Logs\mysql-bin.log tmpdir=D:/Temp The master is creating the binary-log and index. There is a replication account setup on the master called replication. The slave is creating a relay-log and index, has what appears to be correct master.info and relay-log.info files. When I start the slave I get the following in the error log: 090325 9:03:30 [Note] Slave SQL thread initialized, starting replication in log 'mysql_bin.04' at position 10120038, relay log '.\AMSCD2-relay-bin.02' position: 4 090325 9:03:30 [Note] Slave I/O thread: connected to master 'replicat...@amstcop:3306', replication started in log 'mysql_bin.04' at position 10120038 090325 9:03:30 [ERROR] Error reading packet from server: Could not find first log file name in binary log index file (server_errno=1236) 090325 9:03:30 [ERROR] Got fatal error 1236: 'Could not find first log file name in binary log index file' from master when reading data from binary log I'm sure that I've overlooked something, well, stupid, but have struggled with it the last few days to no avail. All servers are using the same version of 4.1.22-community-nt. I feel a bit naked without the replication so your assistance in helping me getting it up and running will be very appreciated. Dirk Bremer - Senior Systems Engineer - Utility - AMS NISC Lake St. Louis MO - USA Central Time Zone 636-755-2652 fax 636-755-2502 dirk.bre...@nisc.coop www.nisc.coop
Re: Replication Issues
Most likely something is wrong in your AMSCD2-relay-bin.index file. Check this out: http://forums.mysql.com/read.php?26,9390,242387#msg-242387 Walter Heck On Thu, Mar 26, 2009 at 10:02 PM, Dirk Bremer dirk.bre...@nisc.coop wrote: Could not find first log file name in binary log index file -- 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 Issues
Hi Dirk, it seems that the information about binary files on the master contained in the mysql-bin.index does not match the actual files on the disk. The first two log messages are just info on the slave starting the two replication threads, the other two seem to be a message coming from the master which says I can't find the binary log file! check the content of the index file and check the binary log files actually present on the master. Cheers Claudio Dirk Bremer wrote: We recently completed a project where all of our production servers were replaced. As part of this project, one of the new servers acted as a replication-slave to one of the old servers, which was the replication-master. I had replication up and running just fine on multiple slaves for a couple of years. Last weekend I moved all functionality from the old server to the new server so that the new server is now setup at the replication-master. I am trying to setup replication-slaves on some of the other new servers and have run into problems doing so. In the my.ini for the master, there is: [mysqld] log-error=D:\Logs\mysql_error.log log-warnings server-id=1 lower_case_table_names=2 log-bin=D:\Logs\mysql-bin.log tmpdir=D:/Temp The master is creating the binary-log and index. There is a replication account setup on the master called replication. The slave is creating a relay-log and index, has what appears to be correct master.info and relay-log.info files. When I start the slave I get the following in the error log: 090325 9:03:30 [Note] Slave SQL thread initialized, starting replication in log 'mysql_bin.04' at position 10120038, relay log '.\AMSCD2-relay-bin.02' position: 4 090325 9:03:30 [Note] Slave I/O thread: connected to master 'replicat...@amstcop:3306', replication started in log 'mysql_bin.04' at position 10120038 090325 9:03:30 [ERROR] Error reading packet from server: Could not find first log file name in binary log index file (server_errno=1236) 090325 9:03:30 [ERROR] Got fatal error 1236: 'Could not find first log file name in binary log index file' from master when reading data from binary log I'm sure that I've overlooked something, well, stupid, but have struggled with it the last few days to no avail. All servers are using the same version of 4.1.22-community-nt. I feel a bit naked without the replication so your assistance in helping me getting it up and running will be very appreciated. Dirk Bremer - Senior Systems Engineer - Utility - AMS NISC Lake St. Louis MO - USA Central Time Zone 636-755-2652 fax 636-755-2502 dirk.bre...@nisc.coop www.nisc.coop -- 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 Issues
On the master in the directory specified in the my.ini on the master, there are seven log-files: D:\Logsdir mysql*.* Volume in drive D is Local Disk Volume Serial Number is A46F-D8E7 Directory of D:\Logs 2009-03-25 00:1738,366,715 mysql-bin.01 2009-03-25 00:17 198 mysql-bin.02 2009-03-25 00:17 672 mysql-bin.03 2009-03-26 00:1856,190,604 mysql-bin.04 2009-03-26 00:18 672 mysql-bin.05 2009-03-26 16:5121,984,192 mysql-bin.06 2009-03-26 17:00 414,487 mysql-bin.07 2009-03-26 16:51 175 mysql-bin.index The contents of the mysql-bin.index are: D:\Logs\mysql-bin.01 D:\Logs\mysql-bin.02 D:\Logs\mysql-bin.03 D:\Logs\mysql-bin.04 D:\Logs\mysql-bin.05 D:\Logs\mysql-bin.06 D:\Logs\mysql-bin.07 There is nothing in the master's error-log. I followed the instructions here: http://forums.mysql.com/read.php?26,9390,242387#msg-242387 With negative results. Dirk Bremer - Senior Systems Engineer - Utility - AMS NISC Lake St. Louis MO - USA Central Time Zone 636-755-2652 fax 636-755-2502 dirk.bre...@nisc.coop www.nisc.coop -Original Message- From: Claudio Nanni [mailto:claudio.na...@gmail.com] Sent: Thursday, March 26, 2009 16:18 To: Dirk Bremer Cc: mysql@lists.mysql.com Subject: Re: Replication Issues Hi Dirk, it seems that the information about binary files on the master contained in the mysql-bin.index does not match the actual files on the disk. The first two log messages are just info on the slave starting the two replication threads, the other two seem to be a message coming from the master which says I can't find the binary log file! check the content of the index file and check the binary log files actually present on the master. Cheers Claudio Dirk Bremer wrote: We recently completed a project where all of our production servers were replaced. As part of this project, one of the new servers acted as a replication-slave to one of the old servers, which was the replication-master. I had replication up and running just fine on multiple slaves for a couple of years. Last weekend I moved all functionality from the old server to the new server so that the new server is now setup at the replication-master. I am trying to setup replication-slaves on some of the other new servers and have run into problems doing so. In the my.ini for the master, there is: [mysqld] log-error=D:\Logs\mysql_error.log log-warnings server-id=1 lower_case_table_names=2 log-bin=D:\Logs\mysql-bin.log tmpdir=D:/Temp The master is creating the binary-log and index. There is a replication account setup on the master called replication. The slave is creating a relay-log and index, has what appears to be correct master.info and relay-log.info files. When I start the slave I get the following in the error log: 090325 9:03:30 [Note] Slave SQL thread initialized, starting replication in log 'mysql_bin.04' at position 10120038, relay log '.\AMSCD2-relay-bin.02' position: 4 090325 9:03:30 [Note] Slave I/O thread: connected to master 'replicat...@amstcop:3306', replication started in log 'mysql_bin.04' at position 10120038 090325 9:03:30 [ERROR] Error reading packet from server: Could not find first log file name in binary log index file (server_errno=1236) 090325 9:03:30 [ERROR] Got fatal error 1236: 'Could not find first log file name in binary log index file' from master when reading data from binary log I'm sure that I've overlooked something, well, stupid, but have struggled with it the last few days to no avail. All servers are using the same version of 4.1.22-community-nt. I feel a bit naked without the replication so your assistance in helping me getting it up and running will be very appreciated. Dirk Bremer - Senior Systems Engineer - Utility - AMS NISC Lake St. Louis MO - USA Central Time Zone 636-755-2652 fax 636-755-2502 dirk.bre...@nisc.coop www.nisc.coop -- 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 Issues
I notice in the startup messages there is an underscore in the file names, but there are dashes in the dir list you provided. ??? On 3/26/09, Dirk Bremer dirk.bre...@nisc.coop wrote: On the master in the directory specified in the my.ini on the master, there are seven log-files: D:\Logsdir mysql*.* Volume in drive D is Local Disk Volume Serial Number is A46F-D8E7 Directory of D:\Logs 2009-03-25 00:1738,366,715 mysql-bin.01 2009-03-25 00:17 198 mysql-bin.02 2009-03-25 00:17 672 mysql-bin.03 2009-03-26 00:1856,190,604 mysql-bin.04 2009-03-26 00:18 672 mysql-bin.05 2009-03-26 16:5121,984,192 mysql-bin.06 2009-03-26 17:00 414,487 mysql-bin.07 2009-03-26 16:51 175 mysql-bin.index The contents of the mysql-bin.index are: D:\Logs\mysql-bin.01 D:\Logs\mysql-bin.02 D:\Logs\mysql-bin.03 D:\Logs\mysql-bin.04 D:\Logs\mysql-bin.05 D:\Logs\mysql-bin.06 D:\Logs\mysql-bin.07 There is nothing in the master's error-log. I followed the instructions here: http://forums.mysql.com/read.php?26,9390,242387#msg-242387 With negative results. Dirk Bremer - Senior Systems Engineer - Utility - AMS NISC Lake St. Louis MO - USA Central Time Zone 636-755-2652 fax 636-755-2502 dirk.bre...@nisc.coop www.nisc.coop -Original Message- From: Claudio Nanni [mailto:claudio.na...@gmail.com] Sent: Thursday, March 26, 2009 16:18 To: Dirk Bremer Cc: mysql@lists.mysql.com Subject: Re: Replication Issues Hi Dirk, it seems that the information about binary files on the master contained in the mysql-bin.index does not match the actual files on the disk. The first two log messages are just info on the slave starting the two replication threads, the other two seem to be a message coming from the master which says I can't find the binary log file! check the content of the index file and check the binary log files actually present on the master. Cheers Claudio Dirk Bremer wrote: We recently completed a project where all of our production servers were replaced. As part of this project, one of the new servers acted as a replication-slave to one of the old servers, which was the replication-master. I had replication up and running just fine on multiple slaves for a couple of years. Last weekend I moved all functionality from the old server to the new server so that the new server is now setup at the replication-master. I am trying to setup replication-slaves on some of the other new servers and have run into problems doing so. In the my.ini for the master, there is: [mysqld] log-error=D:\Logs\mysql_error.log log-warnings server-id=1 lower_case_table_names=2 log-bin=D:\Logs\mysql-bin.log tmpdir=D:/Temp The master is creating the binary-log and index. There is a replication account setup on the master called replication. The slave is creating a relay-log and index, has what appears to be correct master.info and relay-log.info files. When I start the slave I get the following in the error log: 090325 9:03:30 [Note] Slave SQL thread initialized, starting replication in log 'mysql_bin.04' at position 10120038, relay log '.\AMSCD2-relay-bin.02' position: 4 090325 9:03:30 [Note] Slave I/O thread: connected to master 'replicat...@amstcop:3306', replication started in log 'mysql_bin.04' at position 10120038 090325 9:03:30 [ERROR] Error reading packet from server: Could not find first log file name in binary log index file (server_errno=1236) 090325 9:03:30 [ERROR] Got fatal error 1236: 'Could not find first log file name in binary log index file' from master when reading data from binary log I'm sure that I've overlooked something, well, stupid, but have struggled with it the last few days to no avail. All servers are using the same version of 4.1.22-community-nt. I feel a bit naked without the replication so your assistance in helping me getting it up and running will be very appreciated. Dirk Bremer - Senior Systems Engineer - Utility - AMS NISC Lake St. Louis MO - USA Central Time Zone 636-755-2652 fax 636-755-2502 dirk.bre...@nisc.coop www.nisc.coop -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=joh...@pixelated.net -- - Johnny Withers 601.209.4985 joh...@pixelated.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Replication Issues
Hi All, innodb_file_per_table will create individual .ibd file for each table. If mysql master has single ibdata file and mysql slave (used 'innodb_file_per_table') has multiple .ibd files. Is there any other issues by having replication in this way. Thanks -- Krishna Chandra Prajapati MySQL DBA, Ed Ventures e-Learning Pvt.Ltd. 1-8-303/48/15, Sindhi Colony P.G.Road, Secunderabad. Pin Code: 53 Office Number: 040-66489771 Mob: 9912924044 URL: ed-ventures-online.com Email-id: [EMAIL PROTECTED]
Replication Issues
I posted this message on the Replication list, but have got no responses, so, I figured I'd try here. I just set up Replication recently with a Web Server 2003 server being the master and a Windows 2000 server machine being the slave. When I got done, I did a few tests, and all appeared to be fine. Sunday Morning, I checked the event logs on the slave machine, and there was an entry in there that said Slave SQL thread exiting, replication stopped in log 'mysql-bin.01' at position 190 this occurred almost exactly 2 hours after I had finished setting up the slave. I checked, and sure enough, replication no longer appeared to be working when I tried to change a value on the master, it did not seem to replicate to the slave (yes, I realize there can be some time delay, but the machines were not busy at all). I did a SHOW SLAVE STATUS on the slave, and it came back with Waiting for master to send event. But, it wasn't not working. So, I stopped the MySQL Server on the slave machine, and re-started it. It seemed to start alright, but immediately gave an error in the event log about a foreign key violation. I re-sync'd that database (I'm going to post a separate message about that), and continued on. I checked again this morning (Monday),and it appears to have stopped again, because some new records that were added to the master have not been added to the slave. My question is this; What caused the slave to just stop replicating? There was no explanation at all that I could tell. Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication Issues
Hi Jesse, Jesse wrote: I posted this message on the Replication list, but have got no responses, so, I figured I'd try here. I just set up Replication recently with a Web Server 2003 server being the master and a Windows 2000 server machine being the slave. When I got done, I did a few tests, and all appeared to be fine. Sunday Morning, I checked the event logs on the slave machine, and there was an entry in there that said Slave SQL thread exiting, replication stopped in log 'mysql-bin.01' at position 190 this occurred almost exactly 2 hours after I had finished setting up the slave. I checked, and sure enough, replication no longer appeared to be working when I tried to change a value on the master, it did not seem to replicate to the slave (yes, I realize there can be some time delay, but the machines were not busy at all). I did a SHOW SLAVE STATUS on the slave, and it came back with Waiting for master to send event. But, it wasn't not working. So, I stopped the MySQL Server on the slave machine, and re-started it. It seemed to start alright, but immediately gave an error in the event log about a foreign key violation. I re-sync'd that database (I'm going to post a separate message about that), and continued on. I checked again this morning (Monday),and it appears to have stopped again, because some new records that were added to the master have not been added to the slave. My question is this; What caused the slave to just stop replicating? There was no explanation at all that I could tell. I can't think of anything other than looking deeper into the MySQL log files. I'm not sure where those are on Windows, but I imagine it's not all going to the event log. Maybe there is something in c:\program files\mysql 5\ (or wherever you installed MySQL). Try looking for a file named mysql.err or mysqld.err. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication Issues
I can't think of anything other than looking deeper into the MySQL log files. I'm not sure where those are on Windows, but I imagine it's not all going to the event log. Maybe there is something in c:\program files\mysql 5\ (or wherever you installed MySQL). Try looking for a file named mysql.err or mysqld.err. I found an error log named dlgsrv.err. In it is a similar entry to the original error, I got: 070929 17:49:51 [Note] Slave SQL thread exiting, replication stopped in log 'mysql-bin.01' at position 192 It looks like that may be the point at which it just stopped. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication Issues
Jesse wrote: I can't think of anything other than looking deeper into the MySQL log files. I'm not sure where those are on Windows, but I imagine it's not all going to the event log. Maybe there is something in c:\program files\mysql 5\ (or wherever you installed MySQL). Try looking for a file named mysql.err or mysqld.err. I found an error log named dlgsrv.err. In it is a similar entry to the original error, I got: 070929 17:49:51 [Note] Slave SQL thread exiting, replication stopped in log 'mysql-bin.01' at position 192 It looks like that may be the point at which it just stopped. What is the event at that position in the binlog? Use the mysqlbinlog tool to see, or on the master you can run SHOW BINLOG EVENTS (check the manual for the full syntax). Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication Issues
What is the event at that position in the binlog? Use the mysqlbinlog tool to see, or on the master you can run SHOW BINLOG EVENTS (check the manual for the full syntax). If I'm reading this right (and I'm probably not), the log goes from position 98 to position 557 Position 98 has a DELETE FROM query on the mysql.db table. Position 557 has a FLUSH PRIVILEGES command. The position doesn't make any sense to me. Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication Issues
Jesse wrote: What is the event at that position in the binlog? Use the mysqlbinlog tool to see, or on the master you can run SHOW BINLOG EVENTS (check the manual for the full syntax). If I'm reading this right (and I'm probably not), the log goes from position 98 to position 557 Position 98 has a DELETE FROM query on the mysql.db table. Position 557 has a FLUSH PRIVILEGES command. The position doesn't make any sense to me. The position is a byte offset. Here's a bogus sample I just cooked up: # at 13634 #071001 14:07:40 server id 3 end_log_pos 13710 Query thread_id=6 exec_time=0 error_code=0 use test/*!*/; SET TIMESTAMP=1191262060/*!*/; drop table t1/*!*/; # at The # at num is the position of the event. The timestamp, server id, etc follow. The event shown goes from 13634 to 13710 and there's a lot of timestamp and other mumbo-jumbo in most binlog events. Hopefully that's enough info for you to decide whether you read it right. But it looks like your slave had some issue with that. Do you have a lot of privileges? One of the Google engineers told me he's sometimes seen FLUSH PRIVILEGES break replication with a lot of privileges. Whoever was doing that query should have been using GRANT and REVOKE instead -- or DROP USER. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication Issues
Hopefully that's enough info for you to decide whether you read it right. But it looks like your slave had some issue with that. Do you have a lot of privileges? One of the Google engineers told me he's sometimes seen FLUSH PRIVILEGES break replication with a lot of privileges. Whoever was doing that query should have been using GRANT and REVOKE instead -- or DROP USER. I'm the only one that gets in and does anything with the privileges. I always do it with the root user as well, and most of the time, I just use the MySQL Administrator to do that. However, because I was following directions on setting up replication from the help file, I was just using the MySQL Command line. But, I did not issue these commands, it may be that I went into MySQL Administrator to look around or something, and ended up changing something, but I'm not sure what it would have been. I don't recall doing a DROP USER either. In fact, all of the user issues that I did, I did on Friday night, and I haven't touched the users that I'm aware of. I wasn't even in the system at all Saturday Night (I don't think). Anyway. It's all a big mess and jumble. I think I'm going to attempt re-syncing things and re-starting things tonight and trying to start fresh again. I'll see what happens tomorrow. Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slave Replication issues
Hi,
This is because direct inserts are being pointed to one of the slave (or)
you have taken the dump from the master in which when you try to restore
it and start replication you will have to start from the master position
what u have noted. In such case you might get this error to ignore this
you can start your mysql with skip-slave-error=1062 in such case these
duplicates will not repeat.
Thanks Regards
Dilipkumar
- Original Message -
From: David Hillman [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Wednesday, July 26, 2006 12:34 AM
Subject: Re: Slave Replication issues
On Jul 25, 2006, at 12:47 PM, David Nance wrote:
Hi, we have been losing slaves due to errors in replication. See error
message below. Would appreciate if anyone could share if they have
seen same
issues. It seems something may be getting corrupted in the binary log.
Thanks.
The error reads:
Last_error: Error 'Duplicate entry '12312942' for key 1' on query
'INSERT
INTO permissions (user_id, journal_id, date_granted, start_date,
end_date,
type) VALUES ('1503443', '94', NOW(), '-00-00 00:00:00',
'-00-00
00:00:00', 'author')'. Default database: 'manuscript_central_1_1'
One of the indexes on your 'permissions' table in defined to be
'unique', and your application is trying to insert a row with a
duplicate value. This doesn't seem like a replication error, unless
there really isn't a unique index on permissions... but I bet there is.
--
David Hillman
LiveText, Inc
1.866.LiveText x235
** 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]
Slave Replication issues
Hi, we have been losing slaves due to errors in replication. See error
message below. Would appreciate if anyone could share if they have seen same
issues. It seems something may be getting corrupted in the binary log.
Thanks.
The error reads:
Last_error: Error 'Duplicate entry '12312942' for key 1' on query 'INSERT
INTO permissions (user_id, journal_id, date_granted, start_date, end_date,
type) VALUES ('1503443', '94', NOW(), '-00-00 00:00:00', '-00-00
00:00:00', 'author')'. Default database: 'manuscript_central_1_1'
Dave Nance
ScholarOne, Inc.
375 Greenbrier Dr. Suite 200
Charlottesville VA 22901-1618
P: (434) 817-2040 Ext 236
F: (434) 817-2020
[EMAIL PROTECTED]
www.scholarone.com http://www.scholarone.com/
Re: Slave Replication issues
On Jul 25, 2006, at 12:47 PM, David Nance wrote:
Hi, we have been losing slaves due to errors in replication. See error
message below. Would appreciate if anyone could share if they have
seen same
issues. It seems something may be getting corrupted in the binary log.
Thanks.
The error reads:
Last_error: Error 'Duplicate entry '12312942' for key 1' on query
'INSERT
INTO permissions (user_id, journal_id, date_granted, start_date,
end_date,
type) VALUES ('1503443', '94', NOW(), '-00-00 00:00:00',
'-00-00
00:00:00', 'author')'. Default database: 'manuscript_central_1_1'
One of the indexes on your 'permissions' table in defined to be
'unique', and your application is trying to insert a row with a
duplicate value. This doesn't seem like a replication error, unless
there really isn't a unique index on permissions... but I bet there is.
--
David Hillman
LiveText, Inc
1.866.LiveText x235
Re: Replication issues during Failover
On Wed, Feb 18, 2004 at 02:18:59PM -0800, Gowtham Jayaram wrote:
Hello all:
I am looking into the Replication issues that surface in a Failover
scenario (Master goes down and Slave is pressed into service).
I understand that it is critical for the Master and Slave databases
to mirror each other for Replication to work.
mirror each other implies a dual-master scenario. Is that what
you're planning? If so, are you aware of the problems in doing so,
such as the lack of conflict resolution?
I am trying to ensure that this integrity is maintained when the
Failover scenario occurs.
Reading the MySQL FAQ, I see the following recommendation.
1. On the Master, execute these commands:
mysql FLUSH TABLES WITH READ LOCK;
mysql SHOW MASTER STATUS;
Record the log name and the offset from the output
of the SHOW MASTER STATUS statement.
2. On the Slave, issue this command, where the
replication coordinates that are the arguments to the
MASTER_POS_WAIT() function are the values recorded in
the previous step:
mysql SELECT MASTER_POS_WAIT('log_name',
log_offset', [timeout]);
The SELECT statement will block until the Slave
reaches the specified log file and offset. At that
point, the Slave will be in sync with the Master and
the statement will return.
3. On the Master, issue the following statement to
allow the master to begin processing updates again:
mysql UNLOCK TABLES;
4. Also, I plan to add an extra (maybe redundant)
check to make sure that the SQL Thread is in 'Has read
all relay log' state.
QUESTIONS:
Q1. Will the above steps be __sufficient__ to synchronize the
databases?
Well, you really haven't said much about how the failover and recovery
are going to work. What you've described above is a partial method of
syncing the two servers. But it's not clean when and how you inted to
use it.
Q2. Are there any gotchas that I need to watch out for?
Yes. ;-)
I would also like to hear any recommendations of how to handle the
scenario in which the Master database crashes. Since I cannot
connect to the Master database, I cannot perform above Steps 1, 2
and 3.
Correct. If the master is dead, the slave has the data is has and
there's not much else you can do about it until the master comes back
up (assuming it does at all).
Q3. Will Step 4 work in this scenario (since it is file based)? If
it does work is it sufficient to ensure database mirroring?
Maybe.
Q4. If Step 4 does not work what is the recommendation for this
scenario.
Can you be more clear on the setup you have in mind? Does pressed
into service mean you want to slave to take updates too?
Jeremy
--
Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo!
[EMAIL PROTECTED] | http://jeremy.zawodny.com/
MySQL 4.0.15-Yahoo-SMP: up 158 days, processed 1,941,264,213 queries (141/sec. avg)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication issues during Failover
I left out the details because of the fear of putting
people to sleep. Here it goes
The requirement is to design High Availability for an
Application that is using a MySQL database. The
following is the configuration I have in mind.
CONFIGURATION:
- Two machines, Primary and Secondary. Each machine
has the __capability__ to run an instance of our
Application and an instance of MySQL Server.
- Bidirectional replication is setup between the
Primary and Secondary, i.e.; MySQL on both machines
are running with '--log-bin' and without
'--log-slave-updates'.
- A Heartbeat Manager runs on both boxes providing
the status of the Application (ACTIVE, STAND-BY or
DOWN).
- Also, the Application will be designed to ensure
that a database access or update is possible only if
the Application is ACTIVE.
INITIAL STATE:
- The Heartbeat Managers are running on both the
machines.
- To start off, Primary is acting as the Master.
The Application and the MySQL process on the Primary
are ACTIVE, performing database operations at any
given time.
- Secondary is the Slave. The Application and the
MySQL process on the Secondary are in STAND-BY mode,
i.e., no data access or update.
- All the data written to the Primary database is
being replicated onto the Secondary database via MySQL
Replication.
FAILOVER SCENARIO:
- Primary becomes unavailable. Primary is DOWN.
- The Heartbeat Mgr on the Secondary becomes aware
and makes the Secondary ACTIVE. This means
- The secondary will start taking requests and
making updates to the database.
- In short the secondary is the Master now.
Later
- Primary is brought back up again. It will be in
the STAND-BY state.
- Since bidirectional replication is setup, the
updates on the Secondary get replicated onto the
Primary.
Now to the questions;
mirror each other implies a dual-master scenario.
Is that what
you're planning? If so, are you aware of the
problems in doing so,
such as the lack of conflict resolution?
Yes. The Slave is expected to take over when the
Master goes down.
No, I am not aware of 'lack of conflict resolution'.
I am waiting for the 'High Performance MySQL' book to
explain this to me :-). Would be great if you explain
it or point me to some link.
Q2. Are there any gotchas that I need to watch out
for?
Yes. ;-)
Hopefully the above details will help you provide me
more information.
Also, on a more general tone:
- How do people using MySQL as the database provide
High availability solutions?
- What is the alternative to Replication?
- Is the 'shared storage device' solution preferred in
which you have the Primary and Secondary pointing to
the same storage area; when machine fails, the other
machine is brought online against the same data.
All feedback is most welcome.
Gowtham.
--- Jeremy Zawodny [EMAIL PROTECTED] wrote:
On Wed, Feb 18, 2004 at 02:18:59PM -0800, Gowtham
Jayaram wrote:
Hello all:
I am looking into the Replication issues that
surface in a Failover
scenario (Master goes down and Slave is pressed
into service).
I understand that it is critical for the Master
and Slave databases
to mirror each other for Replication to work.
mirror each other implies a dual-master scenario.
Is that what
you're planning? If so, are you aware of the
problems in doing so,
such as the lack of conflict resolution?
I am trying to ensure that this integrity is
maintained when the
Failover scenario occurs.
Reading the MySQL FAQ, I see the following
recommendation.
1. On the Master, execute these commands:
mysql FLUSH TABLES WITH READ LOCK;
mysql SHOW MASTER STATUS;
Record the log name and the offset from the
output
of the SHOW MASTER STATUS statement.
2. On the Slave, issue this command, where the
replication coordinates that are the arguments to
the
MASTER_POS_WAIT() function are the values recorded
in
the previous step:
mysql SELECT MASTER_POS_WAIT('log_name',
log_offset', [timeout]);
The SELECT statement will block until the Slave
reaches the specified log file and offset. At that
point, the Slave will be in sync with the Master
and
the statement will return.
3. On the Master, issue the following statement to
allow the master to begin processing updates
again:
mysql UNLOCK TABLES;
4. Also, I plan to add an extra (maybe redundant)
check to make sure that the SQL Thread is in 'Has
read
all relay log' state.
QUESTIONS:
Q1. Will the above steps be __sufficient__ to
synchronize the
databases?
Well, you really haven't said much about how the
failover and recovery
are going to work. What you've described above is a
partial method of
syncing the two servers. But it's not clean when
and how you inted to
use it.
Q2. Are there any gotchas that I need to watch out
for?
Yes. ;-)
I would also like to hear any recommendations of
how to handle the
scenario
Replication issues during Failover
Hello all:
I am looking into the Replication issues that surface
in a Failover scenario (Master goes down and Slave is
pressed into service).
I understand that it is critical for the Master and
Slave databases to mirror each other for Replication
to work. I am trying to ensure that this integrity is
maintained when the Failover scenario occurs.
Reading the MySQL FAQ, I see the following
recommendation.
1. On the Master, execute these commands:
mysql FLUSH TABLES WITH READ LOCK;
mysql SHOW MASTER STATUS;
Record the log name and the offset from the output
of the SHOW MASTER STATUS statement.
2. On the Slave, issue this command, where the
replication coordinates that are the arguments to the
MASTER_POS_WAIT() function are the values recorded in
the previous step:
mysql SELECT MASTER_POS_WAIT('log_name',
log_offset', [timeout]);
The SELECT statement will block until the Slave
reaches the specified log file and offset. At that
point, the Slave will be in sync with the Master and
the statement will return.
3. On the Master, issue the following statement to
allow the master to begin processing updates again:
mysql UNLOCK TABLES;
4. Also, I plan to add an extra (maybe redundant)
check to make sure that the SQL Thread is in 'Has read
all relay log' state.
QUESTIONS:
Q1. Will the above steps be __sufficient__ to
synchronize the databases?
Q2. Are there any gotchas that I need to watch out
for?
I would also like to hear any recommendations of how
to handle the scenario in which the Master database
crashes. Since I cannot connect to the Master
database, I cannot perform above Steps 1, 2 and 3.
Q3. Will Step 4 work in this scenario (since it is
file based)? If it does work is it sufficient to
ensure database mirroring?
Q4. If Step 4 does not work what is the recommendation
for this scenario.
Looking forward to all your feedback. Thank you.
Gowtham.
__
Do you Yahoo!?
Yahoo! Mail SpamGuard - Read only the mail you want.
http://antispam.yahoo.com/tools
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Insert Replication issues...
I have a setup of 3 servers, with MySQL replication in a round-robin fashion between all 3. So far, any writes to a particular table are done only on one machine at any time. The replication is used as a hot backup, not a load-balancing thing. I now have an instance where I'd like to do inserts on several of the machines at once. The inserts are just logged info, so there's no unique key, no auto-increment, etc... Is it safe to do this, in this configuration? If simultaneous updates hit 2 or more of the servers at the same time, will it still replicate all updates properly (even if they may chronologically occur at different times)? (I am currently using MySQL 3.23.49 from Debian packages...) -- . Trevor Phillips - http://jurai.murdoch.edu.au/ . : Web Technical Administrator - [EMAIL PROTECTED] : | IT Services- Murdoch University | | On nights such as this, evil deeds are done. And good deeds, of / | course. But mostly evil, on the whole. / \ -- (Terry Pratchett, Wyrd Sisters) / - 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: Insert Replication issues...
On Wed, Feb 12, 2003 at 10:59:00AM +0800, Trevor Phillips wrote: I have a setup of 3 servers, with MySQL replication in a round-robin fashion between all 3. So far, any writes to a particular table are done only on one machine at any time. The replication is used as a hot backup, not a load-balancing thing. I now have an instance where I'd like to do inserts on several of the machines at once. The inserts are just logged info, so there's no unique key, no auto-increment, etc... Is it safe to do this, in this configuration? Yes. If simultaneous updates hit 2 or more of the servers at the same time, will it still replicate all updates properly (even if they may chronologically occur at different times)? That's correct. -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.8: up 8 days, processed 282,721,681 queries (371/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
replication issues
Hey guys, I followed the mysql replication instructions in the mysql manual to the tee. http://www.mysql.com/doc/en/Replication_HOWTO.html However replication immediately fails. I tailed the logs and noticed that it failed on an insert query on key1, claiming duplicate entry. Now I've read online that duplicate entry problems are often caused by autoincrement fields. The query was writing to a table that had an autoincrement index, however it was not attempting to write to the index field. I also attempted to have it skip the entry with the SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; command. This command failed. It did not fail when I eliminated the GLOBAL word in the command like so: SET SQL_SLAVE_SKIP_COUNTER=1; However, still no luck with replication. It failed on the very next insert query. FYI: I am using Linux (Redhat 7.3), and the simplest possible replication scheme, with one master and one slave on separate machines, both using the latest stable version of mysql 3.23.54a. Any ideas? Is there a better how to on the web that any of you know of? Have any of you seen this issue. I really appreciate any help you have to offer. Thanks much. -Dan - 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 issues
Dan wrote: not attempting to write to the index field. I also attempted to have it skip the entry with the SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; command. This command failed. It did not fail when I eliminated the GLOBAL word in the command like so: SET SQL_SLAVE_SKIP_COUNTER=1; However, still no luck with This won't help your situation much, but the manual fails to mention on that page that the GLOBAL is a new feature for the 4.x series. Could the MySQL team take a hint from the php documentation team and put such version notices in the margins where appropriate? -- 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
Newbie Replication Issues
I have a few questions about Replication. I've already read the basic replication page here: http://www.mysql.com/doc/en/Replication_HOWTO.html I am running MySQL-MAX 3.23.53 for Linux on my master server, and the same version on Windows 2000 for the slave. #1 - Can I replicate from Linux to Windows 2k? I followed the instructions and put the appropriate things into the my.ini located in C:\MYSQL But... I can't tell if it actually read the my.ini The instructions also refer to an error log. I see C:\MYSQL\data\mysql.err - is this the file they are referring to? If so, the file makes no mention of replication. - RIck - 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: Newbie Replication Issues
Yes, you can replicate from Linux to Win2K ... but both servers have to be properly configured. Did you edit my.cnf on your Linux machine so it knows it is the Master (Win2K would be the Slave)? - Original Message - From: Rick Root [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, November 18, 2002 2:45 PM Subject: Newbie Replication Issues I have a few questions about Replication. I've already read the basic replication page here: http://www.mysql.com/doc/en/Replication_HOWTO.html I am running MySQL-MAX 3.23.53 for Linux on my master server, and the same version on Windows 2000 for the slave. #1 - Can I replicate from Linux to Windows 2k? I followed the instructions and put the appropriate things into the my.ini located in C:\MYSQL But... I can't tell if it actually read the my.ini The instructions also refer to an error log. I see C:\MYSQL\data\mysql.err - is this the file they are referring to? If so, the file makes no mention of replication. - RIck - 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 - 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: Newbie Replication Issues
At 15:45 -0500 11/18/02, Rick Root wrote: I have a few questions about Replication. I've already read the basic replication page here: http://www.mysql.com/doc/en/Replication_HOWTO.html I am running MySQL-MAX 3.23.53 for Linux on my master server, and the same version on Windows 2000 for the slave. #1 - Can I replicate from Linux to Windows 2k? I followed the instructions and put the appropriate things into the my.ini located in C:\MYSQL But... I can't tell if it actually read the my.ini Probably not, because C:\mysql\my.ini is not one of the standard option file locations. Where are the instructions that tell you to create a file with that pathname? The instructions also refer to an error log. I see C:\MYSQL\data\mysql.err - is this the file they are referring to? On Windows, yes. If so, the file makes no mention of replication. - RIck - 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 issues
Hello List, I'm using MySQL 4.0.3-beta on 2 RedHat 7.3 Boxes belonging to a 2 node HA Cluster. To allow the takeover of the database node I formed a replication ring. So node1 replicates to node2 and the other way round. Read/Write operations are only done on Node 2. The nodes are connected with 1 Gbit Nic's The replication ring seems to work but I have another problem. With v 3.x the whole replication seemed much more stable. At least it run several days without an error. It was also possible to resume replication without any errors when a node was rebootet. Now with 4.0.3 the whole thing isn't that stable anymore. Mostly it is this error: When I shut down the master the slave complains that the query log is truncated. | 021007 22:54:31 Error reading packet from server: binlog truncated in the middle of event (server_errno=1236) | 021007 22:54:31 Got fatal error 1236: 'binlog truncated in the middle of event' from master when reading data from binary | log Another strange thing is that the slave doesn't seem to try to reconnect top the master after a server restart. I always have to restart the slave. LOAD DATA FROM MASTER doesn't work the way I expected as it constantly complains about the missing SUPER priviliege. But I did set it. Here the my.cnf of one of the nodes: mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock [mysql.server] user=mysql basedir=/var/lib skip-locking set-variable= key_buffer=16M set-variable= max_allowed_packet=1M set-variable= table_cache=64 set-variable= sort_buffer=512K set-variable= net_buffer_length=8K set-variable= myisam_sort_buffer_size=8M set-variable= max_allowed_packet=16M [safe_mysqld] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid [mysqld] skip-innodb master-host=10.1.0.212 master-user=replicator master-password=secret server-id = 102 log-bin slave_net_timeout=120 I'm pretty sure that the problems are not directly associated with the replication itself but with my setup. Can anyone provide tips how to tune my setup? Please CC to me as I don't know if ezmlm allready added me to this list. Thanks in advance Daniel Khan - 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 issues - server_errno=2013
Below is the output from the error log I started slave replication, it replicated about 5 tables, then just after it created the next table, it stops. I am not sure of the problem (does anyone know what server_errno 2013 is?) 020513 10:01:07 Error reading packet from server: Lost connection to MySQL server during query (read_errno 0,server_errno=2013) 020513 10:02:07 Slave: Failed reading log event, reconnecting to retry, log 'QBSDB251-bin.001' position 15998 020513 10:02:07 Slave: reconnected to master 'replicant1@host_address:3306',replication resumed in log 'QBSDB251-bin.001' at position 15998 020513 10:02:38 Error reading packet from server: Lost connection to MySQL server during query (read_errno 0,server_errno=2013) 020513 10:03:05 Slave thread killed while waiting to reconnect after a failed read 020513 10:03:05 Slave thread exiting, replication stopped in log 'QBSDB251-bin.001' at position 15998 020513 10:28:58 Slave: connected to master 'replicant1@host_address:3306', replication started in log 'QBSDB251-bin.001' at position 15998 020513 10:29:28 Error reading packet from server: Lost connection to MySQL server during query (read_errno 0,server_errno=2013) 020513 10:29:28 Slave: Failed reading log event, reconnecting to retry, log 'QBSDB251-bin.001' position 15998 020513 10:29:31 Slave: reconnected to master 'replicant1@host_address:3306',replication resumed in log 'QBSDB251-bin.001' at position 15998 020513 10:30:01 Error reading packet from server: Lost connection to MySQL server during query (read_errno 0,server_errno=2013) 020513 10:31:01 Slave: Failed reading log event, reconnecting to retry, log 'QBSDB251-bin.001' position 15998 020513 10:31:01 Slave: reconnected to master 'replicant1@host_address:3306',replication resumed in log 'QBSDB251-bin.001' at position 15998 020513 10:31:31 Error reading packet from server: Lost connection to MySQL server during query (read_errno 0,server_errno=2013) 020513 10:32:31 Slave: Failed reading log event, reconnecting to retry, log 'QBSDB251-bin.001' position 15998 020513 10:32:32 Slave: reconnected to master 'replicant1@host_address:3306',replication resumed in log 'QBSDB251-bin.001' at position 15998 020513 10:33:02 Error reading packet from server: Lost connection to MySQL server during query (read_errno 0,server_errno=2013) 020513 10:34:02 Slave: Failed reading log event, reconnecting to retry, log 'QBSDB251-bin.001' position 15998 020513 10:34:02 Slave: reconnected to master 'replicant1@host_address:3306',replication resumed in log 'QBSDB251-bin.001' at position 15998 020513 10:34:32 Error reading packet from server: Lost connection to MySQL server during query (read_errno 0,server_errno=2013) 020513 10:35:32 Slave: Failed reading log event, reconnecting to retry, log 'QBSDB251-bin.001' position 15998 020513 10:35:32 Slave: reconnected to master 'replicant1@host_address:3306',replication resumed in log 'QBSDB251-bin.001' at position 15998 Luc Foisy - 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 Issues
Luc, Luc Foisy wrote: MySQL version 3.23.32 (please don't ask me to upgrade to the newest version of mysql as an answer) I'm very sorry but an upgrade seems to be necessary: if I recall this correctly we had to upgrade to 3.23.33 a year ago or so because the changelog pointed towards a replication bug introduced in 3.23.32! --cut Regards, Frank. -- Dr. Frank Ullrich, Netzwerkadministration Heise Zeitschriften Verlag GmbH Co KG, Helstorfer Str. 7, D-30625 Hannover E-Mail: [EMAIL PROTECTED] Phone: +49 511 5352 587; FAX: +49 511 5352 538 - 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 Issues
MySQL version 3.23.32 (please don't ask me to upgrade to the newest version of mysql as an answer) Everytime the replication server gets reset, the slave properties gets all messed up I am assuming the master.info gets trashed Is this a known issue for this version? Anyways, I can use the CHANGE MASTER TO to fix most of the settings ( lucky for me stuff like possition doesnt get changed, it just ends up in a different field, like the host name ) Except for the Connect_Retry which is set at 0, and I would like it at 60. How do I change that? I tried changing the master.info, but the settings don't take on the server Also, is there a fix for this version of mysql so my slave settings dont mess up? Luc Foisy - 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 Issues
Humph. Undocumented CHANGE MASTER TO MASTER_CONNECT_RETRY=60; Still asking the other question if there is a fix for the problem or not... -Original Message- From: Luc Foisy [mailto:[EMAIL PROTECTED]] Sent: Wednesday, April 03, 2002 11:37 AM To: MYSQL-List (E-mail) Subject: Replication Issues MySQL version 3.23.32 (please don't ask me to upgrade to the newest version of mysql as an answer) Everytime the replication server gets reset, the slave properties gets all messed up I am assuming the master.info gets trashed Is this a known issue for this version? Anyways, I can use the CHANGE MASTER TO to fix most of the settings ( lucky for me stuff like possition doesnt get changed, it just ends up in a different field, like the host name ) Except for the Connect_Retry which is set at 0, and I would like it at 60. How do I change that? I tried changing the master.info, but the settings don't take on the server Also, is there a fix for this version of mysql so my slave settings dont mess up? Luc Foisy - 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 - 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: Input Needed: Replication issues...
On Saturday 12 January 2002 20:53, Jeremy Zawodny wrote: On Fri, Jan 11, 2002 at 08:08:51AM -0800, Kyle Hayes wrote: We write our own replication system using our own checkpointing and code. We start with the update logs (we're not happy about the future removal of the update logs because of this). I've looked through the MySQL source to see if I can figure out enough of the format of the binary log to see if I can make some Perl code that will parse it. It is really easy to use the update log since all SQL end with a semicolon as the last character on a line. Since Perl tends to be line-oriented, it is easy to find this. [snip] Several questions: (1) Do you use transactions at all, or is this MyISAM only? (2) Have you considered publishing the code? (3) Do I understand correctly, that you have your own primary keys which have nothing to do with MySQL's unique IDs? 1) MyISAM only (we started on some of this when 3.23 was knee-high to a bullfrog :-) 2) Yes, but we probably won't. The code has a large number of assumptions about our set up. I don't know that management would be too thrilled with it. The system I'm working on now (to replace the old one) is much cleaner, but would still need a work to remove any Quicknet IP from it. I don't have time to do the clean up, but in general I'd like to give it back to the community. 3) We have sets of IDs for each row that are unique per server. They are made with something like a sequence table using LAST_INSERT_ID tricks. These are the IDs that tie everything together. The auto increment fields are for bulk record keeping and things like purging old data efficiently. Honestly, it is not that much code. I wrote the whole thing in about a week full time. I'll check with management to see if I can release it. Best, Kyle - 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: Input Needed: Replication issues...
Hi! Two-way replication is complex, and even more difficult is coping with broken communications. MySQL only supports one-way replication (master - slaves). Auto-inc column values are communicated in the binlog to the slaves. Some ideas: - Generate primary keys where you concatenate the id of the database site before the id number: Paris0234, NewYork0123 Writing SELECT queries will be difficult. - Write your own replication script like Kyle has done (maybe Kyle could use the 'query-log' of MySQL instead of the 'update-log'?). Communicate changes to the databases in human-readable SQL strings between the database nodes. But you must be very careful in your application to take into account that the two nodes will not be identical, because changes to them will be recorded in different orders. The problem of conflicting primary keys during a communication break can be solved with the above trick. - Transactions might help a little bit if you want to make several changes to the database as one atomic operation (= transaction). Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB . On Thursday 10 January 2002 17:22, Matthew Walker wrote: Where I work, we're at the point of needing to maintain two database servers, one inhouse, and the other at another physical location. We need to replicate between the servers, to keep them both up to date, as both will be getting updated. This leaves us with the problem of resolving primary key conflicts if the servers should ever lose touch with eachother for a while. We're looking at InnoDB, and wondering if it would be able to resolve those sorts of issues using transactions. Input please? We looked closely at native MySQL replication, but after a few questions about the handling of auto increment fields (we use them like row IDs all over the place), we decided that MySQL's replication was broken for our application. We have more than one live database and they replicate to each other. We write our own replication system using our own checkpointing and code. We start with the update logs (we're not happy about the future removal of the update logs because of this). I've looked through the MySQL source to see if I can figure out enough of the format of the binary log to see if I can make some Perl code that will parse it. It is really easy to use the update log since all SQL end with a semicolon as the last character on a line. Since Perl tends to be line-oriented, it is easy to find this. Here's what we do: - we run a special server process on each database. This replicator server will spool update log files on demand. - on each machine, we run a special client process. This process connects to the replicator server (not MySQL's), gets the update log information and puts it into the local database. If necessary, we can rewrite any SQL (we don't). The server process puts a special checkpoint comment between each SQL statement. The checkpoint comments has a special header and trailer so that we can recognize it. It contains the name of the file that is currently being spooled and the byte offset in that file. This information is stored on the client side in a special checkpoint file. If the client process needs to be restarted or the network drops or something causes replication to quit, it will restart where it left off based on the checkpoint file. This functionality is basically identical to the native MySQL replication. The reasons for using the update log and not native MySQL replication are these: - we can have more that two servers coupled together. - we use auto increment fields all over. With native mySQL replication, these fields are filled in with values on the source side. Thus, we get collisions on the target side. We have no need for the row IDs to match on each machine as every row has another unique ID field. We do need the row IDs for purging old data and other things that do not rely on the unique ID field. - we can do SQL rewriting if we really need to. These problems led us to write our own replication. If you have the luxury of redesigning your database or of designing with MySQL's native replication in mind (we did not), then I would use the native version. Best, Kyle - 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: Input Needed: Replication issues...
On Fri, Jan 11, 2002 at 08:08:51AM -0800, Kyle Hayes wrote: We write our own replication system using our own checkpointing and code. We start with the update logs (we're not happy about the future removal of the update logs because of this). I've looked through the MySQL source to see if I can figure out enough of the format of the binary log to see if I can make some Perl code that will parse it. It is really easy to use the update log since all SQL end with a semicolon as the last character on a line. Since Perl tends to be line-oriented, it is easy to find this. [snip] Several questions: (1) Do you use transactions at all, or is this MyISAM only? (2) Have you considered publishing the code? (3) Do I understand correctly, that you have your own primary keys which have nothing to do with MySQL's unique IDs? Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.41-max: up 10 days, processed 248,268,346 queries (280/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: Input Needed: Replication issues...
On Thursday 10 January 2002 17:22, Matthew Walker wrote: Where I work, we're at the point of needing to maintain two database servers, one inhouse, and the other at another physical location. We need to replicate between the servers, to keep them both up to date, as both will be getting updated. This leaves us with the problem of resolving primary key conflicts if the servers should ever lose touch with eachother for a while. We're looking at InnoDB, and wondering if it would be able to resolve those sorts of issues using transactions. Input please? We looked closely at native MySQL replication, but after a few questions about the handling of auto increment fields (we use them like row IDs all over the place), we decided that MySQL's replication was broken for our application. We have more than one live database and they replicate to each other. We write our own replication system using our own checkpointing and code. We start with the update logs (we're not happy about the future removal of the update logs because of this). I've looked through the MySQL source to see if I can figure out enough of the format of the binary log to see if I can make some Perl code that will parse it. It is really easy to use the update log since all SQL end with a semicolon as the last character on a line. Since Perl tends to be line-oriented, it is easy to find this. Here's what we do: - we run a special server process on each database. This replicator server will spool update log files on demand. - on each machine, we run a special client process. This process connects to the replicator server (not MySQL's), gets the update log information and puts it into the local database. If necessary, we can rewrite any SQL (we don't). The server process puts a special checkpoint comment between each SQL statement. The checkpoint comments has a special header and trailer so that we can recognize it. It contains the name of the file that is currently being spooled and the byte offset in that file. This information is stored on the client side in a special checkpoint file. If the client process needs to be restarted or the network drops or something causes replication to quit, it will restart where it left off based on the checkpoint file. This functionality is basically identical to the native MySQL replication. The reasons for using the update log and not native MySQL replication are these: - we can have more that two servers coupled together. - we use auto increment fields all over. With native mySQL replication, these fields are filled in with values on the source side. Thus, we get collisions on the target side. We have no need for the row IDs to match on each machine as every row has another unique ID field. We do need the row IDs for purging old data and other things that do not rely on the unique ID field. - we can do SQL rewriting if we really need to. These problems led us to write our own replication. If you have the luxury of redesigning your database or of designing with MySQL's native replication in mind (we did not), then I would use the native version. Best, Kyle - 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 issues
Jeremy Wilson: I tested replication recently and found it extremely lacking. Even the most minor of errors causes the daemon to stop replicating, effectively rendering the ability useless to me, as I use mysql for authentication. What errors are you talking about? Errors like duplicate entries? Those aren't "minor". They're serious data inconsistency problems. What does that mean? Does that means that, if I try to insert something in a table that already exists (for example, an existing value to a primary key), the slave would STOP RUNNING? If that happens, we should carefully change our whole application here... -- Leonardo Dias Catho Online WebDeveloper http://www.catho.com.br/ - 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 issues
I tested replication recently and found it extremely lacking. Even the most minor of errors causes the daemon to stop replicating, effectively rendering the ability useless to me, as I use mysql for authentication. Is there some configuration command to make it *ignore* errors and continue to replicate? - 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
