Replication problems: slave fails to update
I'm trying to resolve a frustrating replication problem with my databases. The master contains a number of schema, only using Innodb tables. Updates happen regularly, usually using bulk inserts of the form INSERT ... ON DUPLICATE UPDATE. Data is mostly numbers. The missing queries contain no non-deterministic functions, no BLOB/TEXT fields, no triggers or procedures, and no variables. The queries are generated by various C programs, connecting through Connector/ODBC. typical table (explain results): 'nodeID', 'int(10) unsigned', 'NO', 'PRI', '', '' 'calculationID', 'int(10) unsigned', 'NO', 'PRI', '', '' 'columnID', 'tinyint(3) unsigned', 'NO', 'PRI', '1', '' 'value', 'double', 'NO', '', '', '' 'lastUpdate', 'timestamp', 'NO', '', 'CURRENT_TIMESTAMP', 'on update CURRENT_TIMESTAMP typical bulk insert: INSERT INTO risk.risk_node_tree (nodeID, calculationID, columnID, value, lastUpdate) VALUES (1,2,1,1000,null),... ON DUPLICATE KEY UPDATE value=VALUES(value) Replication is active, and I am able to test it by doing single point modifications through the query browser. However, the bulk inserts seem to vanish: The Master updates, but the slave does not. There are no errors in the log file. SHOW SLAVE STATUS states no problems. I have attempted changing binlog_format, and have received the same results on all three settings. The Master has no settings to ignore any particular schema. Using the query browser, and running the same exact query with the same user, results in the query properly replicating. Both instances are 5.1.34. This is causing me to have to resynchronize the databases every night, which is getting to be something of a chore. Does anyone have any idea what might be happening, or could suggest an avenue of investigation? Any help would be greatly appreciated. Martin -- --- This is a signature. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
replication problems
I have a simple master-slave replication setup. Nothing very interesting as far as I know, both running identically configured 5.1.31 using 'mixed' replication. I have a couple of issues which I assume are related, although I suppose they might not be: 1) When the monitoring showed that slaving had stopped, I found the last_sql_error which was about a foreign key constraint failing. Is it possible that the queries against the master get into the bin log out of order so that a referent was changed while there were still outstanding references to it? If not, how is it possible that the query would succeed on the master but not on the slave? 2) Unable to get quick answers to that I decided that safest and easiest would be to take a dump of the master and use it to create a new slave from scratch. However, one of my databases failed to load into the new slave, with an Identifier too long (error 1509). But this dumpfile came from the master. If it's ok on the master, how can it be too long for the slave? And of course, the real question is how do I stop this happening again? Is it something wrong with my mysql server setup? Or is it bad code on the part of my users? And if the latter, what can I do to prevent their bad code making my slaving fail? -- Duncan Hutty System Administrator, ECE Carnegie Mellon University Please use informative subject lines -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
MySQL 5.0.27 replication problems
Hi, I keep getting the below in the error log. I can't see any problems (no other errors and replication is working) and the master DB is available the whole time. 070928 12:07:31 [Note] Slave: received end packet from server, apparent master shutdown: 070928 12:07:31 [Note] Slave I/O thread: Failed reading log event, reconnecting to retry, log 'mysql-bin.000346' position 69110563 070928 12:07:32 [Note] Slave: connected to master 'username@ip address:3306',replication resumed in log 'mysql-bin.000346' at position 69110563 070928 12:07:32 [Note] Slave: received end packet from server, apparent master shutdown: 070928 12:07:32 [Note] Slave I/O thread: Failed reading log event, reconnecting to retry, log 'mysql-bin.000346' position 69110563 070928 12:07:32 [Note] Slave: connected to master 'username@ip address:3306',replication resumed in log 'mysql-bin.000346' at position 69110563 Any ideas what is wrong? Cheers, Andrew Sql, query LOVEFiLM International Limited is a company registered in England and Wales. Registered Number: 04392195. Registered Office: No.9, 6 Portal Way, London W3 6RU, United Kingdom. This e-mail is confidential to the ordinary user of the e-mail address to which it was addressed. If you have received it in error, please delete it from your system and notify the sender immediately. This message has been scanned for viruses by BlackSpider MailControl - www.blackspider.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication problems - packet sizes and more
My master has two databases: channel and hardware. I'm only interested in replicating hardware, so I set up replicate-do-db=hardware on the slaves. However, I am having problems because of giant LOAD DATA operations performed nightly on channel. Replication blows up with max packet exceeded errors. Due to this weird java program we have to use to load data from our partner, I can't just make the packet size really large on the master and slave and be done with it. I tried doing that, but the java program freaks out if i set mysql's packet size too big for the java heap. (I hate java so much.) So, my questions are: - does anyone know how to make java not care how big the mysql packet is? - is there any way to have the mysql master just not even send big packets to the slaves? - is there any way to make the mysql slaves ignore too-big packets? they are only happening on a database that i don't want to replicate anyway. thanks -jsd- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication problems - packet sizes and more
i probably should have mentioned: both master slave are running 4.0.20 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication problems - packet sizes and more
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Jon Drukman wrote: My master has two databases: channel and hardware. I'm only interested in replicating hardware, so I set up replicate-do-db=hardware on the slaves. However, I am having problems because of giant LOAD DATA operations performed nightly on channel. Replication blows up with max packet exceeded errors. Due to this weird java program we have to use to load data from our partner, I can't just make the packet size really large on the master and slave and be done with it. I tried doing that, but the java program freaks out if i set mysql's packet size too big for the java heap. (I hate java so much.) So, my questions are: - does anyone know how to make java not care how big the mysql packet is? Not really fixable, due to the way the MySQL protocol works, a query (including the data for LOAD DATA LOCAL INFILE) has to fit in an entire packet, the packet size needs to be sent to the server _first_, so that means the clients have to allocate all memory required for a query, plus some overhead to escape any 'special' characters...The c-based client would have problems as well (it would have to allocate just as much memory), you just wouldn't get an exception, your program would end up in swap instead. The JDBC client only grows the packet to max_allowed_packet if it needs to. The one case where it probably doesn't need to is for LOAD DATA LOCAL INFILEThe C-library implementation of LOAD DATA LOCAL INFILE _does_ use max_allowed_packet sized packets to send the file from client - - server. Are you using LOAD DATA LOCAL INFILE with JDBC? If so, check out a nightly build of Connector/J 3.1.x from http://downloads.mysql.com/snapshots.php ... It has a change to it where it will use the smaller of 1MB or max_allowed_packet for LOAD DATA LOCAL INFILE operations (this still works, it's just not the 'normal' way the client library works). -Mark - -- Mark Matthews MySQL AB, Software Development Manager - Client Connectivity Office: +1 708 332 0507 www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.6 (GNU/Linux) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFBpTKctvXNTca6JD8RArT9AJ4/gvtsg3u9449Kk8y7N62zglfZOQCgrcXq Ga820/B6m+joEVsKueFf/18= =UHTi -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: replication problems
On Tuesday 09 November 2004 11:49 am, ian douglas wrote: My bad, I wasn't running START SLAVE to get them going. Seems odd that this has to be done manually. I never do.. once its started, it should be fine.. Your just getting errors making it stop. Also, overnight, my slaves reported this error: 041108 19:06:42 [Note] Slave I/O thread: connected to master '[EMAIL PROTECTED]:3306', replication started in log 'masterdb-bin.03' at position 7803960 041108 19:06:42 [ERROR] While trying to obtain the list of slaves from the master 'masterdb.org:3306', user 'repl_user' got the following error: 'Access denied; you need the REPLICATION SLAVE privilege for this operation' 041108 19:06:42 [ERROR] Slave I/O thread exiting, read up to log 'masterdb-bin.03', position 7803960 This is what i have: GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%.smelser.org' IDENTIFIED BY PASSWORD 'taken out'; If a slave dies, is there any way to determine that state from an external point of view (ie: via Perl/DBD) so we can write a script of some kind to either connect to the slave and issue a START SLAVE or send the last xx lines of the .err log file to our tech staff? I do this for the err files. It only send them if there is any data.. I use logrotate.. /var/log/mysql/mysql.err { missingok mail [EMAIL PROTECTED] mailfirst daily notifempty compress rotate 2 } /var/log/mysql/mysqld.err { missingok mail [EMAIL PROTECTED] mailfirst daily notifempty compress rotate 2 } Jeff pgp7Axkk8RKjY.pgp Description: PGP signature
Re: replication problems
If a slave dies, is there any way to determine that state from an external point of view (ie: via Perl/DBD) so we can write a script of some kind to either connect to the slave and issue a START SLAVE or send the last xx lines of the .err log file to our tech staff? I tried writing a Perl script to run a query of SHOW SLAVE STATUS and parsing the 30-or-so fields that would otherwise return, but it's complaining: DBD::mysql::st execute failed: Access denied; you need the SUPER,REPLICATION CLIENT privilege for this operation at ./checkslave.pl line 19. Snippet of code: my $dbh_2 = DBI-connect(dbi:mysql:ouofa:slave2.domain.org,systemcheck,mypasswd, { RaiseError = 1, AutoCommit = 0, PrintError = 0 } ) or die (SLAVE2 MySQL database offline: $!) ; my $cur_info = $dbh_2-prepare(SHOW SLAVE STATUS) ; $cur_info-execute() ; my @row = $cur_info-fetchrow_array() ; ... line 19 would be the -execute() call. I did this: GRANT SUPER,REPLICATION CLIENT to 'syscheck'@'mywebserver.domain.org' IDENTIFIED BY 'mypasswd' ; ... on the slaves, but that didn't help me any. How can I check a slave's status without having to manually log in to a mysql client?? -id -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: replication problems
On Tuesday 09 November 2004 03:23 pm, ian douglas wrote: GRANT SUPER,REPLICATION CLIENT to 'syscheck'@'mywebserver.domain.org' IDENTIFIED BY 'mypasswd' ; I have no idea how this script is gonna help, but your having permission problems. The last message you wrote was permission problems as well.. There is a default login in mysql, I think your getting in as that, but not able to do anything.. Make your your host variables are right.. Check your logs to make sure your getting is as who your expecting to be.. (show processlist even). Jeff pgpa5Z4LVImkC.pgp Description: PGP signature
Re: replication problems
At 9:49 -0800 11/9/04, ian douglas wrote: Except that changes made on the master are not automatically picked up by the slaves. My bad, I wasn't running START SLAVE to get them going. Seems odd that this has to be done manually. Perhaps you have the --skip-slave-start option in an option file? http://dev.mysql.com/doc/mysql/en/Replication_Options.html Another possibility is that the slave threads are starting when the server starts, but the SQL thread stops because it encounters a replication error. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
replication problems
One master, two slaves, mysql 4.1.7 installed via compiled source code, on RedHat 8.0 On the master system I did this: grant SUPER,REPLICATION CLIENT on *.* to 'repl_user'@'known.hostname1' identified by 'repl_passwd' ; grant SUPER,REPLICATION CLIENT on *.* to 'repl_user'@'known.hostname2' identified by 'repl_passwd' ; and on the two slaves: CHANGE MASTER TO MASTER_HOST='master.db.hostname', MASTER_PORT=3306, MASTER_USER='repl_user', MASTER_PASSWORD='repl_passwd' ; ... as per the documentation. On the slaves, the command: LOAD DATA FROM MASTER responds with ERROR 1218 (08S01): Error connecting to master: Access denied for user 'repl_user'@'known.hostname1' (using password: YES) or ERROR 1218 (08S01): Error connecting to master: Access denied for user 'repl_user'@'known.hostname2' (using password: YES) Any tips? /etc/my.cnf on the master sets the server-id to 1, the slaves are 2 and 3 respectively -id -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: replication problems
Answered my own question, sorry for the quick posting... When I was trying to do the LOAD DATA FROM MASTER, with the master set as GRANT ALL ON *.* TO 'repl_user'@ ... the slaves were reporting an error that stated I needed to GRANT SUPER,REPLICATION CLIENT on the master, which didn't work. After a little more RTFM-surfing, I found the GRANT REPLICATION SLAVE for the master, and a LOAD DATA worked just fine. -id ian douglas wrote: One master, two slaves, mysql 4.1.7 installed via compiled source code, on RedHat 8.0 On the master system I did this: grant SUPER,REPLICATION CLIENT on *.* to 'repl_user'@'known.hostname1' identified by 'repl_passwd' ; grant SUPER,REPLICATION CLIENT on *.* to 'repl_user'@'known.hostname2' identified by 'repl_passwd' ; and on the two slaves: CHANGE MASTER TO MASTER_HOST='master.db.hostname', MASTER_PORT=3306, MASTER_USER='repl_user', MASTER_PASSWORD='repl_passwd' ; ... as per the documentation. On the slaves, the command: LOAD DATA FROM MASTER responds with ERROR 1218 (08S01): Error connecting to master: Access denied for user 'repl_user'@'known.hostname1' (using password: YES) or ERROR 1218 (08S01): Error connecting to master: Access denied for user 'repl_user'@'known.hostname2' (using password: YES) Any tips? /etc/my.cnf on the master sets the server-id to 1, the slaves are 2 and 3 respectively -id -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: replication problems
After a little more RTFM-surfing, I found the GRANT REPLICATION SLAVE for the master, and a LOAD DATA worked just fine. Except that changes made on the master are not automatically picked up by the slaves. Am I missing something? -id -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Transaction Replication Problems
Has anyone ever encountered a problem where replication stops due to a partially completed query on the master? I am currently encountering this problem on my server and was wondering if I need to resync the slave server to the master by copying all the data files to the slave and then restarting replication. Is this my best action? The error I am receiving is: 040820 14:51:56 Slave: query 'BEGIN' partially completed on the master and was aborted. There is a chance that your master is inconsistent at this point. If you are sure that your master is ok, run this query manually on the slave and then restart the slave with SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; SLAVE START; . Error_code: 1158 040820 14:51:56 Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with SLAVE START. We stopped at log 'intranet-bin.007' position 16980559 MySql: ready for connections. Thanks for any help. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
replication problems
I'm not sure what's going on with our replication. Everything was working fine, then all of a sudden it stopped. I did STOP SLAVE; RESET SLAVE; LOAD DATA FROM MASTER and it completed after a few minutes, but when I checked SHOW SLAVE STATUS it no longer had any information about the master. I look in the log and there are these messages: 040510 12:46:15 Slave I/O thread: connected to master '[EMAIL PROTECTED]:3306', replication started in log 'FIRST' at position 4 040510 12:46:15 Error reading packet from server: Could not open log file (server_errno=1236) 040510 12:46:15 Got fatal error 1236: 'Could not open log file' from master when reading data from binary log 040510 12:46:15 Slave I/O thread exiting, read up to log 'FIRST', position 4 Any ideas what this is all about? I couldn't find any useful help in the manual or archives when I searched for those errors. - Gabriel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: replication problems
Gabriel Ricard wrote: I'm not sure what's going on with our replication. Everything was working fine, then all of a sudden it stopped. I did STOP SLAVE; RESET SLAVE; LOAD DATA FROM MASTER and it completed after a few minutes, but when I checked SHOW SLAVE STATUS it no longer had any information about the master. I look in the log and there are these messages: 040510 12:46:15 Slave I/O thread: connected to master '[EMAIL PROTECTED]:3306', replication started in log 'FIRST' at position 4 040510 12:46:15 Error reading packet from server: Could not open log file (server_errno=1236) 040510 12:46:15 Got fatal error 1236: 'Could not open log file' from master when reading data from binary log 040510 12:46:15 Slave I/O thread exiting, read up to log 'FIRST', position 4 Any ideas what this is all about? I couldn't find any useful help in the manual or archives when I searched for those errors. Gabriel: Check the master error log to see what binary log it is trying to read, and why the read is not successful, eg. ownership/permission problems. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication Problems: Ignore table
The replication between two servers works. But I tried to disable replication on one table. According to the MYSQL Documentation. I did the following: #1 stop Slave #2 Modified my.cnf on slave [mysqld] server-id=3 master-host=192.168.1.129 master-user=aert12 master-password=password replicate-ignore-table=info.notes # Added this Line #3 Restarted slave server /etc/init.d/mysql restart #4 start slave show status slave shows that replication is running , but actually it doesnt. I even can type start slave many times with no warning. Previously I got an error message if I try to start slave that is running. What I did wrong? _ Working moms: Find helpful tips here on managing kids, home, work and yourself. http://special.msn.com/msnbc/workingmom.armx -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Anyone experiencing replication problems with mysql-max-3.23.57-pc-linux-i686
mysql-max-3.23.57-pc-linux-i686 I notice that slaves which never went out of sync are now doing so regularly with the same traffic pattern. Since upgrading to 3.23.57 I've notice a large increase in CPU utilization with the same configuration as the previous version in production 3.23.54. Anyone else experiencing the same issue? -- Dathan
Re: Replication problems
That got me greping through my Master's log and I came accross the following : Error reading packet from server: log event entry exceeded max_allowed_packet - increase max_allowed_packet on master (server_errno=65535) This seems to have happened in the date ranges where replication was failing. I cant confirm it is related but is it possible this would cause replication to fail on the server ? Also a new table is created every day therefore a single failure cant have been the reason for replication failure over a period of time. It also brings me to a critical question what do I set as the max_allowed_packet so that I will successfully save all updates to a TEXT field? TEXT fields dont have a size limit so how do I make sure I will never see this error ? Mithun Jeremy Zawodny wrote: I can successfully run the SQL on the slave manually and it obviously has run successfully on the master. Why is that obvious? It's telling you that the slave expected to get an error but didn't. Those expectations come from the master's binlog. I'd repair the table on the master, since it's likely in some sort of trouble. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication problems
On Wed, Jun 11, 2003 at 05:22:50PM +0530, Mithun Bhattacharya wrote: I am frequently getting errors as given below on my slave server. - 030605 3:27:19 Slave: did not get the expected error running query from master - expected: 'Got error %d from table handler' (1030), got 'no error' (0) 030605 3:27:19 Slave: error running query 'delete from page_cache_2003_06_04 where FILEKEY = '0'' 030605 3:27:19 Error running query, slave aborted. Fix the problem, and re-start the slave thread with mysqladmin start-slave. We stopped at log 'ebldb05p2-bin.630' position 354859069 030605 3:27:19 Slave thread exiting, replication stopped in log 'ebldb05p2-bin.630' at position 354859069 - 030610 3:17:02 Slave: did not get the expected error running query from master - expected: 'Got error %d from table handler' (1030), got 'no error' (0) 030610 3:17:02 Slave: error running query 'delete from page_cache_2003_06_09 where FILEKEY = '0'' 030610 3:17:02 Error running query, slave aborted. Fix the problem, and re-start the slave thread with mysqladmin start-slave. We stopped at log 'ebldb05p2-bin.654' position 189278930 030610 3:17:02 Slave thread exiting, replication stopped in log 'ebldb05p2-bin.654' at position 189278930 - I can successfully run the SQL on the slave manually and it obviously has run successfully on the master. Why is that obvious? It's telling you that the slave expected to get an error but didn't. Those expectations come from the master's binlog. I'd repair the table on the master, since it's likely in some sort of trouble. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.13: up 9 days, processed 310,309,685 queries (378/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication problems (server_errno=1236)
Hello, I'm running MySQL Ver 12.18 Distrib 4.0.12 I had replication set up and working perfectly between two machines in a round robin fashion. (Serv 1 updates Serv 2 and Serv 2 updates Serv 1.) Recently serv 2 locked up, upon restarting, it automatically began to update with the files from serv 1. YEAH so far. Then I noticed that Serv 1 was not updating from serv 2. I checked all the settings and evertyhing seems to be the same, but I'm getting the following in the error log; 030602 21:03:09 Slave I/O thread: connected to master '[EMAIL PROTECTED]:3306', replication started in log ' serv2-bin.001' at position 376 030602 21:03:09 Error reading packet from server: Could not find first log file name in binary log index file (server_errno=1236) 030602 21:03:09 Got fatal error 1236: 'Could not find first log file name in binary log index file' from master when reading data from binary log 030602 21:03:09 Slave I/O thread exiting, read up to log ' serv2-bin.001', position 376 And the slave thread quits. I've quit and restarted the mysqld process on both servers. I've even reset the master and slave files and re-set the servers. Still I get the same error whenever I do a slave start on serv1. serv2 is still happily grabbing the changes made to serv1 though. Any ideas? Thanks Calvin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication problems (server_errno=1236)
Hi Calvin, I am not sure which table format you use, but somehow I assume you use innodb. If I remember correctly than Innodb will need all log files. If only one is missing than thats it, you get the error described. - anybody please correct me if I talk rubbish here Best regards Nils Valentin Tokyo/Japan 2003 6 3 12:12Calvin Hill : Hello, I'm running MySQL Ver 12.18 Distrib 4.0.12 I had replication set up and working perfectly between two machines in a round robin fashion. (Serv 1 updates Serv 2 and Serv 2 updates Serv 1.) Recently serv 2 locked up, upon restarting, it automatically began to update with the files from serv 1. YEAH so far. Then I noticed that Serv 1 was not updating from serv 2. I checked all the settings and evertyhing seems to be the same, but I'm getting the following in the error log; 030602 21:03:09 Slave I/O thread: connected to master '[EMAIL PROTECTED]:3306', replication started in log ' serv2-bin.001' at position 376 030602 21:03:09 Error reading packet from server: Could not find first log file name in binary log index file (server_errno=1236) 030602 21:03:09 Got fatal error 1236: 'Could not find first log file name in binary log index file' from master when reading data from binary log 030602 21:03:09 Slave I/O thread exiting, read up to log ' serv2-bin.001', position 376 And the slave thread quits. I've quit and restarted the mysqld process on both servers. I've even reset the master and slave files and re-set the servers. Still I get the same error whenever I do a slave start on serv1. serv2 is still happily grabbing the changes made to serv1 though. Any ideas? Thanks Calvin -- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication problems (server_errno=1236)
Nils, (B (BI agree with you, you do have to have all the log files, which is why I (Breset the master. (B (BAccording to the manual: (B (B4.10.7.5 RESET MASTER (master) (B (BDeletes all binary logs listed in the index file, resetting the binlog index (Bfile to be empty. Previously named FLUSH MASTER . (B (B (BBy doing this you eliminate all old master file and start over at (BFILENAME-bin-001 position 79. (B (BThanks for the input though. (B (BCalvin (B (B (BOn 6/2/03 21:32, "Nils Valentin" [EMAIL PROTECTED] wrote: (B (B Hi Calvin, (B (B I am not sure which table format you use, but somehow I assume you use innodb. (B (B If I remember correctly than Innodb will need all log files. If only one is (B missing than thats it, you get the error described. (B (B - anybody please correct me if I talk rubbish here (B (B Best regards (B (B Nils Valentin (B Tokyo/Japan (B (B (B 2003$BG/(J 6$B7n(J 3$BF|(J $B2PMKF|(J 12:12$B!"(JCalvin Hill (B $B$5$s$O=q$-$^$7$?(J: (B Hello, (B (B I'm running MySQL Ver 12.18 Distrib 4.0.12 I had replication set up and (B working perfectly between two machines in a round robin fashion. (Serv 1 (B updates Serv 2 and Serv 2 updates Serv 1.) (B (B Recently serv 2 locked up, upon restarting, it automatically began to (B update with the files from serv 1. YEAH so far. Then I noticed that Serv 1 (B was not updating from serv 2. I checked all the settings and evertyhing (B seems to be the same, but I'm getting the following in the error log; (B (B 030602 21:03:09 Slave I/O thread: connected to master (B '[EMAIL PROTECTED]:3306', replication started in log ' serv2-bin.001' at (B position 376 (B (B 030602 21:03:09 Error reading packet from server: Could not find first log (B file name in binary log index file (server_errno=1236) (B (B 030602 21:03:09 Got fatal error 1236: 'Could not find first log file name (B in binary log index file' from master when reading data from binary log (B (B 030602 21:03:09 Slave I/O thread exiting, read up to log ' serv2-bin.001', (B position 376 (B (B (B And the slave thread quits. I've quit and restarted the mysqld process on (B both servers. I've even reset the master and slave files and re-set the (B servers. Still I get the same error whenever I do a slave start on serv1. (B serv2 is still happily grabbing the changes made to serv1 though. (B (B Any ideas? (B (B Thanks (B Calvin (B (B (B-- (BMySQL General Mailing List (BFor list archives: http://lists.mysql.com/mysql (BTo unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication problems
I've got 3 databases running DB1, DB2, DB3. DB1 version 3.23.41 DB2 version 3.23.41 DB3 version 3.12.51 DB1 is the master for DB2 in replication I'm trying now to set up DB3 to replicate from DB2 So in essence DB1 - DB2 - DB3 I ran through the instructions for setting up replication of DB2 to DB3 All are using myISAM tables. I'm getting the following error on DB3 030404 20:16:01 Slave: connected to master '[EMAIL PROTECTED]:0', replication started in log 'db02tc07927-bin.049' at position 160 030404 20:18:01 Error reading packet from server: (server_errno=1159) 030404 20:18:01 Slave: Failed reading log event, reconnecting to retry, log 'db02tc07927-bin.049' position 160 030404 20:18:01 Slave: reconnected to master '[EMAIL PROTECTED]:0',replication resumed in log 'db02tc07927-bin.049' at position 160 030404 20:20:01 Error reading packet from server: (server_errno=1159) I've set the net_read_timeout=3600 on the DB3 my.cnf file as suggested by other threads I've seen but this hasn't helped. Any ideas? Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Various replication problems with 4.0.10-gamma
Hi, I'm having a bit of a nightmare getting replication working. Both slave and master are 4.0.10-gamma If I install fresh copies on two clean machines, and follow the instructions in manual.txt, everything works fine. The problem is that I need to do this in a production environment, so I can't quite follow the instructions. I've tried using LOAD DATA FROM MASTER, but this aborts and the logfile shows a ER_NET_MASTER_READ has occurred in the middle of the third table. So, I removed the mysql data directory on the slave, and completely re-installed and re-populated it (just to be sure I had a clean start). Then tried again by taking a snapshot - Just one database to start with. I then tried a CHANGE MASTER TO ... and SLAVE START This now fails with: 030218 14:21:53 Error updating slave list: Query error It seems that replication is trying to do a SHOW SLAVE HOSTS. I assume that this is running on the master, so I tried to login to the master by hand and run it to see what the error is. I get the error: ERROR 1227: Access denied. You need the REPLICATION SLAVE privilege for this operation I've tried setting the grants again, but it doesn't seem to make any difference: mysql GRANT REPLICATION SLAVE,RELOAD,SUPER ON *.* TO myuser@myhost IDENTIFIED BY 'mypass' mysql show grants for myuser@myhost; ++ | Grants for myuser@myhost | | ++ | GRANT RELOAD ON *.* TO 'myuser'@'myhost' IDENTIFIED BY PASSWORD 'asdadadasdadasas' | | ++ 1 row in set (0.00 sec) Whats going on here ? Thanks JohnT - 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 problems
Hi All I am trying to make a replication for my database. The master and the slave both in version 3.23.41 and i am working on windows 2000. I create user with FILE privilege and I flush all the tables and block write queries. Now I run the query SHOW MASTER STATUS and I get null in all the columns (file, position, Binlog_do_db and Binlog_ignore_db) What should I do? Thanks, Inbal - 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: Various replication problems with 4.0.10-gamma
On Tuesday 18 February 2003 15:36, Harald Fuchs wrote: In article [EMAIL PROTECTED], John Taylor [EMAIL PROTECTED] writes: I get the error: ERROR 1227: Access denied. You need the REPLICATION SLAVE privilege for this operation I've tried setting the grants again, but it doesn't seem to make any difference: mysql GRANT REPLICATION SLAVE,RELOAD,SUPER ON *.* TO myuser@myhost IDENTIFIED BY 'mypass' mysql show grants for myuser@myhost; ++ | Grants for myuser@myhost | ++ | GRANT RELOAD ON *.* TO 'myuser'@'myhost' IDENTIFIED BY PASSWORD 'asdadadasdadasas' | ++ 1 row in set (0.00 sec) Whats going on here ? Perhaps you forgot to run mysql_fix_privilege_tables? That was it - thanks. Replication now seems to be running, but the tables are not being updated on the slave. I can see the updates arriving in the relay log, but they don't get added to the database. There are no errors in the log Processlist looks OK to me ... mysql show full processlist; ++-+---+--+-+---+--+---+ | Id | User| Host | db | Command | Time | State || Info | ++-+---+--+-+---+--+---+ | 1 | system user | none | NULL | Connect | 11609 | Reading master update || NULL | | 2 | system user | none | NULL | Connect | 11609 | Slave: waiting for |binlog update | NULL | | 5 | root| localhost | vpopmail | Query | 0 | NULL || show full processlist | ++-+---+--+-+---+--+---+ 3 rows in set (0.00 sec) and the slave staus looks OK too ... mysql show slave status; +---+-+-+---+-+-++---+---+--+---+-+-+++--+-+-+ | Master_Host | Master_User | Master_Port | Connect_retry | Master_Log_File || Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_File | |Slave_IO_Running | Slave_SQL_Running | Replicate_do_db | Replicate_ignore_db | |Last_errno | Last_error | Skip_counter | Exec_master_log_pos | Relay_log_space | +---+-+-+---+-+-++---+---+--+---+-+-+++--+-+-+ | mymaster | myuser | 3306| 60| mysqld.013 || 516424 | loki-relay-bin.003 | 381962| mysqld.012| |Yes | Yes | | | 0 ||| 0| 516424 | 381966 | +---+-+-+---+-+-++---+---+--+---+-+-+++--+-+-+ 1 row in set (0.00 sec) The permissions on the database directory and files are rwx-- mysql:mysql Is here anything else that I can check for ? I have only taken a snapshot and copied one of the databases so far - could that be the problem ? Thanks JohnT - 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 Problems with Adding New Records
I am using MySQL version 4.0.10 on both Slave and Master. I am running a Visual Basic front end connected to MySQL via MySQL ODBC v3.51.04. When using MySQLCC, adding records to the master database will work fine, the server shows the additional record added. When using our app in VB, a record will be added on the Master, but the Slave does not pick up the added record. The log-bin file shows there is something recorded, but no error message or no change! If anyone has any input, that would be great... If you need or want further explaining please email, I will be glad to help... Thanks... Aaron - 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 Problems/Questions
-Ursprngliche Nachricht- Von: Guddack Thorsten ICM MP SCM GO 21 Gesendet: Freitag, 31. Januar 2003 20:51 An: 'Gelu Gogancea'; '[EMAIL PROTECTED]' Betreff: Re: Replication Problems/Questions Hi, thank you for you're response, but in the mysql-manual version 4.0 they said that the Problems with load data, load data infile have been solved. So I'm a little bit confused about that. Best regards -Ursprngliche Nachricht- Von: Gelu Gogancea [mailto:[EMAIL PROTECTED]] Gesendet: Donnerstag, 30. Januar 2003 20:48 An: Guddack Thorsten ICM MP SCM GO 21; [EMAIL PROTECTED] Betreff: Re: Replication Problems/Questions Hi, - Original Message - From: Guddack Thorsten ICM MP SCM GO 21 [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, January 30, 2003 3:44 PM Subject: Replication Problems/Questions Hi List I'm using 2 WinNT-Servers, Sp6 with mysql 4.0.5. max-nt. I ve setup a replication between these two servers as discribed in the manual and some small problems comes up. Maybe someone can give me some help. 1.) On the Master I create a user repl with the rights 'replication slave'. On the slave I used a user with all possible rights. Starting the replication with 'reset slave', 'change master to.' and 'start slave', I get an error: Show slave status says Slave_IO_running no and some entry in mysql.err. This Problem I could solve using a user in 'change master to ' that has also admin rights. What could cause the problem? 2.) The more important Problem: If the replication is running, and I try to replicate a table where data was inserted on the master with 'LOAD DATA INFILE...' or 'LOAD DATA LOCAL INFILE' the server process on the slaves crushes The files on the master, for LOAD DATA.were still there. Any Ideas? IMHO: ...this can happening because on the slave you don't have(...i guess) the file for LOAD DATA.By REPLICATION the slave trying to copy every movement of the master and not the data itself.This means that slave trying to execute LOAD DATA INFILE YOUR_PATH_TO_FILE .and not copy the data from the master. At Last one question: - could anyone explain me the meaning of the fields from show slave status ( I'm looking for a way to decide if the slave has replicated all the data from the master ) Best regards Best Regards, Gelu [EMAIL PROTECTED] - 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
Replication Problems/Questions
Hi List I'm using 2 WinNT-Servers, Sp6 with mysql 4.0.5. max-nt. I ve setup a replication between these two servers as discribed in the manual and some small problems comes up. Maybe someone can give me some help. 1.) On the Master I create a user repl with the rights 'replication slave'. On the slave I used a user with all possible rights. Starting the replication with 'reset slave', 'change master to.' and 'start slave', I get an error: Show slave status says Slave_IO_running no and some entry in mysql.err. This Problem I could solve using a user in 'change master to ' that has also admin rights. What could cause the problem? 2.) The more important Problem: If the replication is running, and I try to replicate a table where data was inserted on the master with 'LOAD DATA INFILE...' or 'LOAD DATA LOCAL INFILE' the server process on the slaves crushes The files on the master, for LOAD DATA.were still there. Any Ideas? At Last one question: - could anyone explain me the meaning of the fields from show slave status ( I'm looking for a way to decide if the slave has replicated all the data from the master ) Best regards [EMAIL PROTECTED] - 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 Problems/Questions
Hi, - Original Message - From: Guddack Thorsten ICM MP SCM GO 21 [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, January 30, 2003 3:44 PM Subject: Replication Problems/Questions Hi List I'm using 2 WinNT-Servers, Sp6 with mysql 4.0.5. max-nt. I ve setup a replication between these two servers as discribed in the manual and some small problems comes up. Maybe someone can give me some help. 1.) On the Master I create a user repl with the rights 'replication slave'. On the slave I used a user with all possible rights. Starting the replication with 'reset slave', 'change master to.' and 'start slave', I get an error: Show slave status says Slave_IO_running no and some entry in mysql.err. This Problem I could solve using a user in 'change master to ' that has also admin rights. What could cause the problem? 2.) The more important Problem: If the replication is running, and I try to replicate a table where data was inserted on the master with 'LOAD DATA INFILE...' or 'LOAD DATA LOCAL INFILE' the server process on the slaves crushes The files on the master, for LOAD DATA.were still there. Any Ideas? IMHO: ...this can happening because on the slave you don't have(...i guess) the file for LOAD DATA.By REPLICATION the slave trying to copy every movement of the master and not the data itself.This means that slave trying to execute LOAD DATA INFILE YOUR_PATH_TO_FILE .and not copy the data from the master. At Last one question: - could anyone explain me the meaning of the fields from show slave status ( I'm looking for a way to decide if the slave has replicated all the data from the master ) Best regards Best Regards, Gelu [EMAIL PROTECTED] - 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
replication problems with InnoDB tables...?
So I have been reading a lot lately about replication but I can't find a good summary/explanation of MySQL's replication features regarding InnoDB tables and how their transactional properties may cause problems in replication. I have seen suggestions that InnoDB tables should be converted to MyISAM tables before being loaded and then converted back to InnoDB tables. I have also seen many others here confused as to what problems there are between InnoDB and replication. So, could some friendly MySQL guru please explain to me, and many others, what the replication issues with InnoDB tables really are...please? The docs state that InnoDB is replication safe yet there seem to be so many problems with them when updates fail, roll back or are partially executed because some constraint conflict that causes replication to stop. Sincerely Kris Karski _ MSN 8 with e-mail virus protection service: 2 months FREE* http://join.msn.com/?page=features/virus - 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 problems with InnoDB tables...?
Kris, - Original Message - From: Krzysztof Karski [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Tuesday, January 21, 2003 11:35 AM Subject: replication problems with InnoDB tables...? So I have been reading a lot lately about replication but I can't find a good summary/explanation of MySQL's replication features regarding InnoDB tables and how their transactional properties may cause problems in replication. transactions should not cause problems in replication. However, a relevant bug fix is this: MySQL/InnoDB-4.0.6, December 19, 2002 ... Fixed a bug: in replication issuing SLAVE STOP in the middle of a multi-statement transaction could cause that SLAVE START would only perform part of the transaction. A similar error could occur if the slave crashed and was restarted. I have seen suggestions that InnoDB tables should be converted to MyISAM tables before being loaded and then converted back to InnoDB tables. LOAD TABLE FROM MASTER only works for MyISAM tables. To set up a new replication slave you should use a cold backup, or a hot backup made with the non-free InnoDB tool. I have also seen many others here confused as to what problems there are between InnoDB and replication. So, could some friendly MySQL guru please explain to me, and many others, what the replication issues with InnoDB tables really are...please? The docs state that InnoDB is replication safe yet there seem to be so many problems with them when updates fail, roll back or are partially executed because some constraint conflict that causes replication to stop. A relevant bug fix: MySQL/InnoDB-4.0.6, December 19, 2002 ... Fixed two bugs introduced in 4.0.4: in AUTO_INCREMENT, REPLACE could cause the counter to be left 1 too low. A deadlock or a lock wait timeout could cause the same problem. MySQL does not write to the binlog SQL statements which fail. It does not write to the binlog any SQL statements from a transaction which is rolled back. Thus, these are never replicated, and in the slave there should not be any constraint violation errors. Sincerely Kris Karski Best regards, Heikki Tuuri Innobase Oy --- InnoDB - transactions, hot backup, and foreign key support for MySQL See http://www.innodb.com, download MySQL-Max from http://www.mysql.com sql query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Redhat 8.0 MySQL 3.23.52-3 Replication Problems
I have been having a lot of trouble configuring mysql to do just the simplest replication. Its a standard RedHat 8.0 install, I have followed the directions on http://www.mysql.com/doc/en/Replication_HOWTO.html to the letter half a dozen times now and still, the slave server crashes repeatedly as soon as I enter SLAVE START command. Number of processes running now: 1 mysqld process hanging, pid 26754 - killed 030113 17:54:33 mysqld restarted /usr/libexec/mysqld: ready for connections Number of processes running now: 1 mysqld process hanging, pid 26778 - killed 030113 17:54:34 mysqld restarted /usr/libexec/mysqld: ready for connections Above is what is logged. I eventually have to kill the process and try again. I have tried entering master login/bin-log information in my.cnf and using CHANGE MASTER TO... while logged into slave mysql server. Neither method works. Was wondering if anyone had some insight into this problem. I would be happy to provide more information. Thanks all. -Chris - 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 problems
We have two users, lets call them test and test2. When we log in as test from our front end web servers, we can execute queries and they replicate. When we log in as test from our back end db servers (say to run a cleanup script by hand), the queries replicate. When we log in as test2 from our back end db servers (as part of a periodic process), it replicates the queries. When we log in from our front end web servers as test2, the queries do not replicate. They are never added to the binary update log. Otherwise, everything appears to work fine; the master db server runs the queries and updates the tables appropriately. If necessary I can upload a copy of our grant table, but I'm hoping someone has a quick. easy fix. :) Thanks - 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 Problems
I am getting this error while trying to Replicate to a second MySQL server. 021124 23:40:25 Slave: reconnected to master '[EMAIL PROTECTED]:3306',replication resumed in log 'FIRST' at position 4 021124 23:40:25 Error reading packet from server: Binary log is not open (server_errno=2000) I have added the following to the master: [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 It looks like the bin files are not being created on the master server. I could find very little regarding this problem. Any ideas? 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
mysql replication problems
dear everybody, I have tree linux 7.3 machines on which MySQL-3.23.51-1.i386.rpm, MySQL-client-3.23.51-1.i386.rpm and MySQL-shared-3.23.51-1.i386.rpm is installed. the intrusion detection system snort loggs to these machines. Every machine is set up the same way and contains the same table. The data of these three tables is always different. this works well, but now I want to have a place/machine where I can read the data of these three tables. Maybe I can set up a master to which all the other databases can log their data? Or perhaps I can configure every machine as master and as slave at the same time and thus every machine contains the whole data of these three machines. I am a newbie in mysql and don't know how to do it. I read the following link : http://www.mysql.com/doc/en/Replication.html but I don't know if mysql meets my requirements. Anyway I tried to configure two of my databases like the example configuration. I had no problem with the master. then I tried to set up the slave and couldn't find the my.cnf, the mysqld and the mysqld.log files. I set up the two machines the same way!! Perhaps I used the wrong softare, but why exit the files on the other machine? thanks for your help martina podesser FLEXTRONICS World Trade Center A-1300 Vienna Austria [EMAIL PROTECTED] www.flextronics.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Replication problems
Thanks a lot. I've fixed my problems with this. Best regards. - Original Message - From: Gerald Clark [EMAIL PROTECTED] To: Santiago Alba [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Friday, July 12, 2002 3:53 PM Subject: Re: Replication problems Did you copy the master.info file over too? Santiago Alba wrote: Hi All, I had a system replication on Linux consisting of MASTER and only one SLAVE. I stopped replication, I did a backup on my slave and it recreated in another machine so now I have two SLAVES connected to database MASTER. But I found problems. I show you the output on my database MASTER and SLAVE who is failed: *** MASTER mysql show master status; +-+--+--+--+ | File| Position | Binlog_do_db | Binlog_ignore_db | +-+--+--+--+ | log.101 | 54557| | | +-+--+--+--+ 1 row in set (0.00 sec) SLAVE: mysql show slave status; +--+-+-+---+--- - --+-+---+-+-+-- - -++--+ | Master_Host | Master_User | Master_Port | Connect_retry | Log_File | Pos | Slave_Running | Replicate_do_db | Replicate_ignore_db | Last_errno | Last_error | Skip_counter | +--+-+-+---+--- - --+-+---+-+-+-- - -++--+ | spiderman.terravista.srv | replica | 3306| 60| | 4 | Yes | | | 0 | | 0| +--+-+-+---+--- - --+-+---+-+-+-- - -++--+ 1 row in set (0.00 sec) mysql show processlist; ++-+---+--+-+--+--- - --+--+ | Id | User| Host | db | Command | Time | State | Info | ++-+---+--+-+--+--- - --+--+ | 1 | system user | none | NULL | Connect | 7729 | Waiting to reconnect after a failed read | NULL | | 2 | root| localhost | NULL | Sleep | 337 | | NULL | | 3 | root| localhost | NULL | Query | 0| NULL | show processlist | ++-+---+--+-+--+--- - --+--+ In errors log: 020712 12:43:40 Error reading packet from server: Could not open log file (server_errno=2) 020712 12:44:40 Slave: Failed reading log event, reconnecting to retry, log 'FIRST' position 4 020712 12:44:40 Slave: reconnected to master 'replica#spiderman.terravista.srv:3306',replication resumed in log 'FIRST' at position 4 020712 12:44:40 Error reading packet from server: Could not open log file (server_errno=2) 020712 12:45:40 Slave: Failed reading log event, reconnecting to retry, log 'FIRST' position 4 020712 12:45:40 Slave: reconnected to master 'replica#spiderman.terravista.srv:3306',replication resumed in log 'FIRST' at position 4 020712 12:45:40 Error reading packet from server: Could not open log file (server_errno=2) my.cnf: # The MySQL server [mysqld] port= 3306 socket = /tmp/mysql.sock skip-locking set-variable= key_buffer=384M set-variable= max_allowed_packet=1M set-variable= table_cache=512 set-variable= sort_buffer=2M set-variable= record_buffer=2M set-variable= thread_cache=8 # Try number of CPU's*2 for thread_concurrency set-variable= thread_concurrency=8 set-variable= myisam_sort_buffer_size=64M #log-bin= /mysql/log/log.nnn server-id = 3 master-host = spiderman.terravista.srv master-user = replica master-password = x *** Any suggestion? Thanks in advance. Santiago. - 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: Replication problems
Hi, That wasn't the problem. The problem was in master.info file. Thanks a lot. - Original Message - From: Ralf Narozny [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, July 12, 2002 4:39 PM Subject: Re: Replication problems Hello! You will get problems when you set the 2nd slaves server-id the same as other one. server-ids MUST be unique...once made that mistake too... Greetings Ralf Gerald Clark wrote: Did you copy the master.info file over too? Santiago Alba wrote: Hi All, I had a system replication on Linux consisting of MASTER and only one SLAVE. I stopped replication, I did a backup on my slave and it recreated in another machine so now I have two SLAVES connected to database MASTER. But I found problems. I show you the output on my database MASTER and SLAVE who is failed: *** MASTER mysql show master status; +-+--+--+--+ | File| Position | Binlog_do_db | Binlog_ignore_db | +-+--+--+--+ | log.101 | 54557| | | +-+--+--+--+ 1 row in set (0.00 sec) SLAVE: mysql show slave status; +--+-+-+---+ --+-+---+-+-+ --- -++--+ | Master_Host | Master_User | Master_Port | Connect_retry | Log_File | Pos | Slave_Running | Replicate_do_db | Replicate_ignore_db | Last_errno | Last_error | Skip_counter | +--+-+-+---+ --+-+---+-+-+ --- -++--+ | spiderman.terravista.srv | replica | 3306| 60| | 4 | Yes | | | 0 | | 0| +--+-+-+---+ --+-+---+-+-+ --- -++--+ 1 row in set (0.00 sec) mysql show processlist; ++-+---+--+-+--+ --+--+ | Id | User| Host | db | Command | Time | State | Info | ++-+---+--+-+--+ --+--+ | 1 | system user | none | NULL | Connect | 7729 | Waiting to reconnect after a failed read | NULL | | 2 | root| localhost | NULL | Sleep | 337 | | NULL | | 3 | root| localhost | NULL | Query | 0| NULL | show processlist | ++-+---+--+-+--+ --+--+ In errors log: 020712 12:43:40 Error reading packet from server: Could not open log file (server_errno=2) 020712 12:44:40 Slave: Failed reading log event, reconnecting to retry, log 'FIRST' position 4 020712 12:44:40 Slave: reconnected to master 'replica#spiderman.terravista.srv:3306',replication resumed in log 'FIRST' at position 4 020712 12:44:40 Error reading packet from server: Could not open log file (server_errno=2) 020712 12:45:40 Slave: Failed reading log event, reconnecting to retry, log 'FIRST' position 4 020712 12:45:40 Slave: reconnected to master 'replica#spiderman.terravista.srv:3306',replication resumed in log 'FIRST' at position 4 020712 12:45:40 Error reading packet from server: Could not open log file (server_errno=2) my.cnf: # The MySQL server [mysqld] port= 3306 socket = /tmp/mysql.sock skip-locking set-variable= key_buffer=384M set-variable= max_allowed_packet=1M set-variable= table_cache=512 set-variable= sort_buffer=2M set-variable= record_buffer=2M set-variable= thread_cache=8 # Try number of CPU's*2 for thread_concurrency set-variable= thread_concurrency=8 set-variable= myisam_sort_buffer_size=64M #log-bin= /mysql/log/log.nnn server-id = 3 master-host = spiderman.terravista.srv master-user = replica master-password = x *** Any suggestion? Thanks in advance. Santiago. - 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
Replication problems
Hi All, I had a system replication on Linux consisting of MASTER and only one SLAVE. I stopped replication, I did a backup on my slave and it recreated in another machine so now I have two SLAVES connected to database MASTER. But I found problems. I show you the output on my database MASTER and SLAVE who is failed: *** MASTER mysql show master status; +-+--+--+--+ | File| Position | Binlog_do_db | Binlog_ignore_db | +-+--+--+--+ | log.101 | 54557| | | +-+--+--+--+ 1 row in set (0.00 sec) SLAVE: mysql show slave status; +--+-+-+---+ --+-+---+-+-+--- -++--+ | Master_Host | Master_User | Master_Port | Connect_retry | Log_File | Pos | Slave_Running | Replicate_do_db | Replicate_ignore_db | Last_errno | Last_error | Skip_counter | +--+-+-+---+ --+-+---+-+-+--- -++--+ | spiderman.terravista.srv | replica | 3306| 60| | 4 | Yes | | | 0 | | 0| +--+-+-+---+ --+-+---+-+-+--- -++--+ 1 row in set (0.00 sec) mysql show processlist; ++-+---+--+-+--+ --+--+ | Id | User| Host | db | Command | Time | State | Info | ++-+---+--+-+--+ --+--+ | 1 | system user | none | NULL | Connect | 7729 | Waiting to reconnect after a failed read | NULL | | 2 | root| localhost | NULL | Sleep | 337 | | NULL | | 3 | root| localhost | NULL | Query | 0| NULL | show processlist | ++-+---+--+-+--+ --+--+ In errors log: 020712 12:43:40 Error reading packet from server: Could not open log file (server_errno=2) 020712 12:44:40 Slave: Failed reading log event, reconnecting to retry, log 'FIRST' position 4 020712 12:44:40 Slave: reconnected to master 'replica#spiderman.terravista.srv:3306',replication resumed in log 'FIRST' at position 4 020712 12:44:40 Error reading packet from server: Could not open log file (server_errno=2) 020712 12:45:40 Slave: Failed reading log event, reconnecting to retry, log 'FIRST' position 4 020712 12:45:40 Slave: reconnected to master 'replica#spiderman.terravista.srv:3306',replication resumed in log 'FIRST' at position 4 020712 12:45:40 Error reading packet from server: Could not open log file (server_errno=2) my.cnf: # The MySQL server [mysqld] port= 3306 socket = /tmp/mysql.sock skip-locking set-variable= key_buffer=384M set-variable= max_allowed_packet=1M set-variable= table_cache=512 set-variable= sort_buffer=2M set-variable= record_buffer=2M set-variable= thread_cache=8 # Try number of CPU's*2 for thread_concurrency set-variable= thread_concurrency=8 set-variable= myisam_sort_buffer_size=64M #log-bin= /mysql/log/log.nnn server-id = 3 master-host = spiderman.terravista.srv master-user = replica master-password = x *** Any suggestion? Thanks in advance. Santiago. - 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 problems
Did you copy the master.info file over too? Santiago Alba wrote: Hi All, I had a system replication on Linux consisting of MASTER and only one SLAVE. I stopped replication, I did a backup on my slave and it recreated in another machine so now I have two SLAVES connected to database MASTER. But I found problems. I show you the output on my database MASTER and SLAVE who is failed: *** MASTER mysql show master status; +-+--+--+--+ | File| Position | Binlog_do_db | Binlog_ignore_db | +-+--+--+--+ | log.101 | 54557| | | +-+--+--+--+ 1 row in set (0.00 sec) SLAVE: mysql show slave status; +--+-+-+---+ --+-+---+-+-+--- -++--+ | Master_Host | Master_User | Master_Port | Connect_retry | Log_File | Pos | Slave_Running | Replicate_do_db | Replicate_ignore_db | Last_errno | Last_error | Skip_counter | +--+-+-+---+ --+-+---+-+-+--- -++--+ | spiderman.terravista.srv | replica | 3306| 60| | 4 | Yes | | | 0 | | 0| +--+-+-+---+ --+-+---+-+-+--- -++--+ 1 row in set (0.00 sec) mysql show processlist; ++-+---+--+-+--+ --+--+ | Id | User| Host | db | Command | Time | State | Info | ++-+---+--+-+--+ --+--+ | 1 | system user | none | NULL | Connect | 7729 | Waiting to reconnect after a failed read | NULL | | 2 | root| localhost | NULL | Sleep | 337 | | NULL | | 3 | root| localhost | NULL | Query | 0| NULL | show processlist | ++-+---+--+-+--+ --+--+ In errors log: 020712 12:43:40 Error reading packet from server: Could not open log file (server_errno=2) 020712 12:44:40 Slave: Failed reading log event, reconnecting to retry, log 'FIRST' position 4 020712 12:44:40 Slave: reconnected to master 'replica#spiderman.terravista.srv:3306',replication resumed in log 'FIRST' at position 4 020712 12:44:40 Error reading packet from server: Could not open log file (server_errno=2) 020712 12:45:40 Slave: Failed reading log event, reconnecting to retry, log 'FIRST' position 4 020712 12:45:40 Slave: reconnected to master 'replica#spiderman.terravista.srv:3306',replication resumed in log 'FIRST' at position 4 020712 12:45:40 Error reading packet from server: Could not open log file (server_errno=2) my.cnf: # The MySQL server [mysqld] port= 3306 socket = /tmp/mysql.sock skip-locking set-variable= key_buffer=384M set-variable= max_allowed_packet=1M set-variable= table_cache=512 set-variable= sort_buffer=2M set-variable= record_buffer=2M set-variable= thread_cache=8 # Try number of CPU's*2 for thread_concurrency set-variable= thread_concurrency=8 set-variable= myisam_sort_buffer_size=64M #log-bin= /mysql/log/log.nnn server-id = 3 master-host = spiderman.terravista.srv master-user = replica master-password = x *** Any suggestion? Thanks in advance. Santiago. - 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: Replication problems
Subject: Re: Replication problems From: Jared Richardson [EMAIL PROTECTED] === Did you change the slave number in the second slave's config file? I forget the exact syntax, but if memory serves, there is a number in the my.ini (or my.cnf) that must be unique. If you just copied, you may have missed that. Santiago Alba [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED]... Hi All, I had a system replication on Linux consisting of MASTER and only one SLAVE. I stopped replication, I did a backup on my slave and it recreated in another machine so now I have two SLAVES connected to database MASTER. But I found problems. I show you the output on my database MASTER and SLAVE who is failed: *** MASTER mysql show master status; +-+--+--+--+ | File| Position | Binlog_do_db | Binlog_ignore_db | +-+--+--+--+ | log.101 | 54557| | | +-+--+--+--+ 1 row in set (0.00 sec) SLAVE: mysql show slave status; +--+-+-+---+ --+-+---+-+-+- -- -++--+ | Master_Host | Master_User | Master_Port | Connect_retry | Log_File | Pos | Slave_Running | Replicate_do_db | Replicate_ignore_db | Last_errno | Last_error | Skip_counter | +--+-+-+---+ --+-+---+-+-+- -- -++--+ | spiderman.terravista.srv | replica | 3306| 60| | 4 | Yes | | | 0 | | 0| +--+-+-+---+ --+-+---+-+-+- -- -++--+ 1 row in set (0.00 sec) mysql show processlist; ++-+---+--+-+--+ --+--+ | Id | User| Host | db | Command | Time | State | Info | ++-+---+--+-+--+ --+--+ | 1 | system user | none | NULL | Connect | 7729 | Waiting to reconnect after a failed read | NULL | | 2 | root| localhost | NULL | Sleep | 337 | | NULL | | 3 | root| localhost | NULL | Query | 0| NULL | show processlist | ++-+---+--+-+--+ --+--+ In errors log: 020712 12:43:40 Error reading packet from server: Could not open log file (server_errno=2) 020712 12:44:40 Slave: Failed reading log event, reconnecting to retry, log 'FIRST' position 4 020712 12:44:40 Slave: reconnected to master 'replica#spiderman.terravista.srv:3306',replication resumed in log 'FIRST' at position 4 020712 12:44:40 Error reading packet from server: Could not open log file (server_errno=2) 020712 12:45:40 Slave: Failed reading log event, reconnecting to retry, log 'FIRST' position 4 020712 12:45:40 Slave: reconnected to master 'replica#spiderman.terravista.srv:3306',replication resumed in log 'FIRST' at position 4 020712 12:45:40 Error reading packet from server: Could not open log file (server_errno=2) my.cnf: # The MySQL server [mysqld] port= 3306 socket = /tmp/mysql.sock skip-locking set-variable= key_buffer=384M set-variable= max_allowed_packet=1M set-variable= table_cache=512 set-variable= sort_buffer=2M set-variable= record_buffer=2M set-variable= thread_cache=8 # Try number of CPU's*2 for thread_concurrency set-variable= thread_concurrency=8 set-variable= myisam_sort_buffer_size=64M #log-bin= /mysql/log/log.nnn server-id = 3 master-host = spiderman.terravista.srv master-user = replica master-password = x *** Any suggestion? Thanks in advance. Santiago. - 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
mysql replication problems
Both my slave server say they are replicating correctly in the mysqld.log but they arn't. Any change made to the master is not reflected to the slaves. I have tried reseting the master and slaves but didn't change anything. Also tried redumping the databases to the slaves but that didn't effect anything. Anyone have any ideas? Justin Best NOC - Infostations - 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 problems
I'm having the same problems, but I've noticed that the reported error doesn't seem to be a real failure. The databases seem to be updated correctly. When I look at my slave error log, I can see that the at position is a number that always matches the size of the log file on the master. This cannot be a coincidence. Is it perhaps trying to read past the end of the log file or something? I haven't really looked at MySql in years because it's been working fine, but in setting up replication, I see now that I have some Innobase tables, some not. Haven't a clue how that happened, but my my.cnf does not have the related directives uncommented. This could be related to problems reading the logs, I assume? I'm doing some testing, but any suggestions appreciated. I don't have a spare server to play with and the master is our main production server, so I have to play carefully to remain employed. I tried with both master and slave running mysql-3.23.47-pc-linux-gnu-i686 and then tried moving the slave to 3..23.51 with the same error results. Thanks for any assistance, Jeff Jeff Hill www.HRpost.com Ph: 416-604-7251 Fx: 647-439-1414 - 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 mysql-unsubscribe-##L=##[EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: replication problems -- resolution
Just to let you know that when I installed 3.23.51 binary on both Debian Linux boxes, same problem, but when I re-installed the 3.23.49a binary on both boxes the problem went away. Uncertain why. Regards, Jeff Hill At 08:25 AM 18/06/2002 -0400, Jeff Hill wrote: I'm having the same problems, but I've noticed that the reported error doesn't seem to be a real failure. The databases seem to be updated correctly. When I look at my slave error log, I can see that the at position is a number that always matches the size of the log file on the master. This cannot be a coincidence. Is it perhaps trying to read past the end of the log file or something? I haven't really looked at MySql in years because it's been working fine, but in setting up replication, I see now that I have some Innobase tables, some not. Haven't a clue how that happened, but my my.cnf does not have the related directives uncommented. This could be related to problems reading the logs, I assume? I'm doing some testing, but any suggestions appreciated. I don't have a spare server to play with and the master is our main production server, so I have to play carefully to remain employed. I tried with both master and slave running mysql-3.23.47-pc-linux-gnu-i686 and then tried moving the slave to 3..23.51 with the same error results. Thanks for any assistance, Jeff Jeff Hill www.HRpost.com Ph: 416-604-7251 Fx: 647-439-1414 - 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 mysql-unsubscribe-##L=##[EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php Jeff Hill, Editor, HRpost / HR On-Line www.HRpost.com Ph: 416-858-3440 Fx: 647-439-1414 - 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 mysql-unsubscribe-##L=##[EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: [Repost: Mysql Replication Problems]
Hi, On 13 Feb 2002, at 12:44, Henning Sprang wrote: Was something wrong with this post or is it really such a weird problem that there is neither a solution nor anything i could do to solve it, nor anything i could do to find out more about it? Well. Replication is somewhat weird. ;-) Slave: error running query 'drop table tropon_base.TB_Tage,tropon_001.TB_Tage' 020209 3:49:32 Error running query, slave aborted. Fix the problem, and re-start the slave thread with mysqladmin start-slave. ERROR: 1051 Unknown table 'TB_Tage,TB_Tage' Sounds, as if you were doing some cross database updates / inserts. This might lead to problems if you only replicate some of the databases on the master. So, if there is a Database A, B, C, D on master each with tables A1,A2,A3 or B1,B2,B3 and so on, and you only replicate A and B and you do: use A; insert into C.C1 (a,b,c) VALUES (1,2,3); you'll get this error. You might avoid it with replicate-wild-ignore-table=C.* More details in: http://www.mysql.com/documentation/mysql/bychapter/manual_My SQL_Database_Administration.html#Replication_Options But note that: use C; insert into A.A1 (a,b,c) VALUES (1,2,3); would not be replicated to the slave, if you choosed to replicate only A and B. Even though it affects A. (Strange, but that's the way live goes). At least with MySQL 3.23.36 So best is to avoid cross-databases actions except for SELECT with replication enabled. Bye Tobias --- Tobias Eggendorfer E-Mail: [EMAIL PROTECTED] - 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
[Repost: Mysql Replication Problems]
Hy, Was something wrong with this post or is it really such a weird problem that there is neither a solution nor anything i could do to solve it, nor anything i could do to find out more about it? that was the message: I get some Errors when doing Replication on MySQL 3.23.47. And can't get it going on(I did not upgrade because in Changelog there is no replication or binlog related Bugfix mentioned between .47 and .48 and i only upgraded to .47 some days before) the Messages in error-log are: 020208 17:43:20 mysqld started /usr/local/mysql/3.23.47/libexec/mysqld: ready for connections 020208 17:43:22 Slave: connected to master '[EMAIL PROTECTED]:3306', replication started in log 'FIRST' at position 4 020209 3:49:32 Slave: did not get the expected error running query from master - expected: 'Got timeout reading communication packets' (1159), got 'no error' (0) 020209 3:49:32 Slave: error running query 'drop table tropon_base.TB_Tage,tropon_001.TB_Tage' 020209 3:49:32 Error running query, slave aborted. Fix the problem, and re-start the slave thread with mysqladmin start-slave. We stopped at log 'localhost-bin.001' position 330834 020209 3:49:32 Slave thread exiting, replication stopped in log 'localhost-bin.001' at position 330834 After restarting _wthout_ fixing an error because not knowing what the error is and just looking if it maybe works I get a somehow different message, even if i think it's the same problem: 020211 14:49:06 Slave: connected to master '[EMAIL PROTECTED]:3306', replication started in log 'localhost-bin.001' at position 330834 ERROR: 1051 Unknown table 'TB_Tage,TB_Tage' 020211 14:49:06 Slave: did not get the expected error running query from master - expected: 'Got timeout reading communication packets' (1159), got 'Unknown table 'TB_Tage,TB_Tage'' (1051) 020211 14:49:06 Slave: error running query 'drop table tropon_base.TB_Tage,tropon_001.TB_Tage' 020211 14:49:06 Error running query, slave aborted. Fix the problem, and re-start the slave thread with mysqladmin start-slave. We stopped at log 'localhost-bin.001' position 330834 020211 14:49:06 Slave thread exiting, replication stopped in log 'localhost-bin.001' at position 330834 Those problematic tables are temporary heap Tables, but in replication docs I read that those work fine since some Versions. I'd greatly appreciate some Ideas, TIA, Henning - 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
Mysql Replication Problems
Hy All, I get some Errors when doing Replication on MySQL 3.23.47. (I did not upgrade because in Changelog there is no Replication-Related Bugfix mentioned) the Messages in error-log are: 020208 17:43:20 mysqld started /usr/local/mysql/3.23.47/libexec/mysqld: ready for connections 020208 17:43:22 Slave: connected to master '[EMAIL PROTECTED]:3306', replication started in log 'FIRST' at position 4 020209 3:49:32 Slave: did not get the expected error running query from master - expected: 'Got timeout reading communication packets' (1159), got 'no error' (0) 020209 3:49:32 Slave: error running query 'drop table tropon_base.TB_Tage,tropon_001.TB_Tage' 020209 3:49:32 Error running query, slave aborted. Fix the problem, and re-start the slave thread with mysqladmin start-slave. We stopped at log 'localhost-bin.001' position 330834 020209 3:49:32 Slave thread exiting, replication stopped in log 'localhost-bin.001' at position 330834 After restarting _wthout_ fixing an error because not knowing what the error is and just looking if it maybe works I get a somehow different message, even if i think it's the same problem: 020211 14:49:06 Slave: connected to master '[EMAIL PROTECTED]:3306', replication started in log 'localhost-bin.001' at position 330834 ERROR: 1051 Unknown table 'TB_Tage,TB_Tage' 020211 14:49:06 Slave: did not get the expected error running query from master - expected: 'Got timeout reading communication packets' (1159), got 'Unknown table 'TB_Tage,TB_Tage'' (1051) 020211 14:49:06 Slave: error running query 'drop table tropon_base.TB_Tage,tropon_001.TB_Tage' 020211 14:49:06 Error running query, slave aborted. Fix the problem, and re-start the slave thread with mysqladmin start-slave. We stopped at log 'localhost-bin.001' position 330834 020211 14:49:06 Slave thread exiting, replication stopped in log 'localhost-bin.001' at position 330834 Those tables are temporary heap Tables, but in replication docs I read that those work fine since some Versions. I'd greatly appreciate some Ideas, TIA, Henning - 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
Replication Problems
Hy all, I just try to set up Replication with two mysql databases, i did everything like described in the Replication-Howto. Now i get the following errors when starting the slave: 020117 18:37:55 mysqld started Can't initialize InnoDB as 'innodb_data_file_path' is not set /usr/sbin/mysqld-max: ready for connections 020117 18:37:55 Slave: connected to master '[EMAIL PROTECTED]:3306', replication started in log 'samba-bin.001' at position 536 ERROR: 1017 Can't find file: './tropon_001/Tagesberichte.frm' (errno: 13) 020117 18:37:55 Slave: error running query 'CREATE TEMPORARY TABLE TB_Tage TYPE=HEAP SELECT DISTINCT Datum FROM Tagesberichte AS TB WHERE Besucher_ID=10021 ' 020117 18:37:55 Error running query, slave aborted. Fix the problem, and re-start the slave thread with mysqladmin start-slave. We stopped at log 'samba-bin.001' position 536 020117 18:37:55 Slave thread exiting, replication stopped in log 'samba-bin.001' at position 536 the file is actually there (in the data directory at least, i don't know what is meant with the ./tropon... directory exactly as it is a relative path, and i checked if mysql recognizes the data directory by setting some unimpoortant settings in a my.cnf file in there - the changes where accepted correctly, i did make sure to remove that testing file again. When I connect to the database on the slave with mysql-client i get the following: ... Didn't find any fields in table 'Statistiken' Didn't find any fields in table 'Tagesberichte' Database changed I see there are some tables which don't produce errors, but for all, with or without errors, there are .frm files in the data directory, i see there are different file permissions for differenbt table files, but again the failing tables don't have something of those in common. has anyone an idea what i can look for? henning - 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 Problems - solved!
Hy, sorry for the trouble! i tried to do a chown mysql:daemon -R * in the data dir, and after that it worked. I didn't try that before because somehow i couldn't see a connection between differing ownerships and disfunctional tables when looking at this. sorry, henning Henning Sprang wrote: Hy all, I just try to set up Replication with two mysql databases, i did everything like described in the Replication-Howto. Now i get the following errors when starting the slave: 020117 18:37:55 mysqld started Can't initialize InnoDB as 'innodb_data_file_path' is not set /usr/sbin/mysqld-max: ready for connections 020117 18:37:55 Slave: connected to master '[EMAIL PROTECTED]:3306', replication started in log 'samba-bin.001' at position 536 ERROR: 1017 Can't find file: './tropon_001/Tagesberichte.frm' (errno: 13) 020117 18:37:55 Slave: error running query 'CREATE TEMPORARY TABLE TB_Tage TYPE=HEAP SELECT DISTINCT Datum FROM Tagesberichte AS TB WHERE Besucher_ID=10021 ' 020117 18:37:55 Error running query, slave aborted. Fix the problem, and re-start the slave thread with mysqladmin start-slave. We stopped at log 'samba-bin.001' position 536 020117 18:37:55 Slave thread exiting, replication stopped in log 'samba-bin.001' at position 536 the file is actually there (in the data directory at least, i don't know what is meant with the ./tropon... directory exactly as it is a relative path, and i checked if mysql recognizes the data directory by setting some unimpoortant settings in a my.cnf file in there - the changes where accepted correctly, i did make sure to remove that testing file again. When I connect to the database on the slave with mysql-client i get the following: ... Didn't find any fields in table 'Statistiken' Didn't find any fields in table 'Tagesberichte' Database changed I see there are some tables which don't produce errors, but for all, with or without errors, there are .frm files in the data directory, i see there are different file permissions for differenbt table files, but again the failing tables don't have something of those in common. has anyone an idea what i can look for? henning - 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: Replication Problems
chown -R mysql:mysql /var/lib/mysql ( or whatever the data directory is ). Henning Sprang wrote: Hy all, I just try to set up Replication with two mysql databases, i did everything like described in the Replication-Howto. Now i get the following errors when starting the slave: 020117 18:37:55 mysqld started Can't initialize InnoDB as 'innodb_data_file_path' is not set /usr/sbin/mysqld-max: ready for connections 020117 18:37:55 Slave: connected to master '[EMAIL PROTECTED]:3306', replication started in log 'samba-bin.001' at position 536 ERROR: 1017 Can't find file: './tropon_001/Tagesberichte.frm' (errno: 13) 020117 18:37:55 Slave: error running query 'CREATE TEMPORARY TABLE TB_Tage TYPE=HEAP SELECT DISTINCT Datum FROM Tagesberichte AS TB WHERE Besucher_ID=10021 ' 020117 18:37:55 Error running query, slave aborted. Fix the problem, and re-start the slave thread with mysqladmin start-slave. We stopped at log 'samba-bin.001' position 536 020117 18:37:55 Slave thread exiting, replication stopped in log 'samba-bin.001' at position 536 the file is actually there (in the data directory at least, i don't know what is meant with the ./tropon... directory exactly as it is a relative path, and i checked if mysql recognizes the data directory by setting some unimpoortant settings in a my.cnf file in there - the changes where accepted correctly, i did make sure to remove that testing file again. When I connect to the database on the slave with mysql-client i get the following: ... Didn't find any fields in table 'Statistiken' Didn't find any fields in table 'Tagesberichte' Database changed I see there are some tables which don't produce errors, but for all, with or without errors, there are .frm files in the data directory, i see there are different file permissions for differenbt table files, but again the failing tables don't have something of those in common. has anyone an idea what i can look for? henning - 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[2]: Innodb/Replication problems.
Hello Heikki, Saturday, January 05, 2002, 11:55:02 PM, you wrote: Hello mysql, I'm using mysql 3.23.47 with Innodb and trying to play around with replication. The replication options are default. I got one table content occasionally deleted on slave, and note what HT Who deleted the table contents? You? Yes. I did it. this did not stop the replication: The inserts are successfully passed, therefore the updates to nonexistent rows simply ignored. HT But MySQL passes complete SQL statements to the slave. It is no error if an HT update does not update any rows. I do not believe that MySQL checks that the HT master updated as many rows as the slave. May be this is the problem, because for example for insert mysql check if it was able to insert data (no duplicates or what ever) but this does not work with updates. I remember then I tried this half a year ago with myisam, the behavior was to print an error and stop. Well probably I'm a bit mistaken - the error is checked for insert because duplicate key error is an error, therefore the update which does not update anything is quite OK. -- Best regards, Petermailto:[EMAIL PROTECTED] - 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
Innodb/Replication problems.
Hello mysql, I'm using mysql 3.23.47 with Innodb and trying to play around with replication. The replication options are default. I got one table content occasionally deleted on slave, and note what this did not stop the replication: The inserts are successfully passed, therefore the updates to nonexistent rows simply ignored. I remember then I tried this half a year ago with myisam, the behavior was to print an error and stop. SLAVE: mysql select count(*) from counter.quickstat; +--+ | count(*) | +--+ | 661 | +--+ 1 row in set (0.04 sec) MASTER: mysql select count(*) from counter.quickstat; +--+ | count(*) | +--+ | 294787 | +--+ 1 row in set (2.65 sec) -- Best regards, Peter mailto:[EMAIL PROTECTED] - 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: Innodb/Replication problems.
Hi! -Original Message- From: Peter Zaitsev [EMAIL PROTECTED] To: [EMAIL PROTECTED] [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] [EMAIL PROTECTED] Date: Saturday, January 05, 2002 10:35 PM Subject: Innodb/Replication problems. Hello mysql, I'm using mysql 3.23.47 with Innodb and trying to play around with replication. The replication options are default. I got one table content occasionally deleted on slave, and note what Who deleted the table contents? You? this did not stop the replication: The inserts are successfully passed, therefore the updates to nonexistent rows simply ignored. But MySQL passes complete SQL statements to the slave. It is no error if an update does not update any rows. I do not believe that MySQL checks that the master updated as many rows as the slave. I remember then I tried this half a year ago with myisam, the behavior was to print an error and stop. SLAVE: mysql select count(*) from counter.quickstat; +--+ | count(*) | +--+ | 661 | +--+ 1 row in set (0.04 sec) MASTER: mysql select count(*) from counter.quickstat; +--+ | count(*) | +--+ | 294787 | +--+ 1 row in set (2.65 sec) -- Best regards, Peter mailto:[EMAIL PROTECTED] Regards, Heikki Tuuri Innobase Oy --- MySQL/InnoDB - transactions, row level locking, and foreign key support for MySQL See http://www.innodb.com, download MySQL-Max from http://www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: strange replication problems
Hi. My first guess would be that somehow the slave got out of sync. I would try to update the slave by hand and see what happens after restart. Sorry, I don't see what could be the cause. Bye, Benjamin. On Tue, Oct 02, 2001 at 06:07:29PM +0200, [EMAIL PROTECTED] wrote: [...] I have 28 linux boxes configured identically, 27 as slaves, one as master. On all 27 slaves, everything works fine, but only on one I have strange errors in the logs: 011002 17:49:49 Slave: received 0 length packet from server, apparent master shutdown: (0) 011002 17:50:49 Slave: Failed reading log event, reconnecting to retry, log 'cp4230-bin.005' position 664857 011002 17:50:49 Slave: reconnected to master 'repl@xxx:3306',replication resumed in log 'xxx-bin.005' at position 664857 011002 17:51:49 Slave: received 0 length packet from server, apparent master shutdown: (0) This repeats every minute. [...] -- [EMAIL PROTECTED] - 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: strange replication problems
Benjamin Pflugmann writes: Hi. My first guess would be that somehow the slave got out of sync. I would try to update the slave by hand and see what happens after restart. Sorry, I don't see what could be the cause. I tried the following: on master: reset master on all slaves: slave stop drop table xxx load table xxx from master ... repeat for every replicated table ... slave start Still the same problem. Bye, Benjamin. On Tue, Oct 02, 2001 at 06:07:29PM +0200, [EMAIL PROTECTED] wrote: [...] I have 28 linux boxes configured identically, 27 as slaves, one as master. On all 27 slaves, everything works fine, but only on one I have strange errors in the logs: 011002 17:49:49 Slave: received 0 length packet from server, apparent master shutdown: (0) 011002 17:50:49 Slave: Failed reading log event, reconnecting to retry, log 'cp4230-bin.005' position 664857 011002 17:50:49 Slave: reconnected to master 'repl@xxx:3306',replication resumed in log 'xxx-bin.005' at position 664857 011002 17:51:49 Slave: received 0 length packet from server, apparent master shutdown: (0) This repeats every minute. [...] -- [EMAIL PROTECTED] -- Alex Ivanisevic, 2e systems Tel: +49 6192 978 497 Handy: +49 0174 310 9333 - 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: strange replication problems
Benjamin Pflugmann writes: Hi. Does the error log of the master contain any error? Nothing at all in the master logs. Does the new log position make sense? Yes, the position is ok, and the slave gets the updates. Its just that strange error messages about dropped connections and 0-byte packets, and only on one of the slaves. It hangs always on the same offset. So it doesn't get forward at all. That's why I assume some priciple error. No, no, the log position changes as the updates are arriving from the master. There is some file called master.info in the database directory of the client. Could you redo the reload and delete that file after you stop the slave in the process. No, nothing, still the same message. Thanks for trying to track this, but I dont think that this is some principle problem, because I have a whole bunch of servers running replication, and running it for quite some time, they are all working fine, except for these messages on jus one slave. I suspect that this is either some really wierd quirk in replication process or I am overlooking something in the configuration of that particular server, even though I checked everything thousand times already. Bye, Benjamin. On Thu, Oct 04, 2001 at 10:27:16AM +0200, [EMAIL PROTECTED] wrote: Benjamin Pflugmann writes: Hi. My first guess would be that somehow the slave got out of sync. I would try to update the slave by hand and see what happens after restart. Sorry, I don't see what could be the cause. I tried the following: on master: reset master on all slaves: slave stop drop table xxx load table xxx from master ... repeat for every replicated table ... slave start Still the same problem. Bye, Benjamin. On Tue, Oct 02, 2001 at 06:07:29PM +0200, [EMAIL PROTECTED] wrote: [...] I have 28 linux boxes configured identically, 27 as slaves, one as master. On all 27 slaves, everything works fine, but only on one I have strange errors in the logs: 011002 17:49:49 Slave: received 0 length packet from server, apparent master shutdown: (0) 011002 17:50:49 Slave: Failed reading log event, reconnecting to retry, log 'cp4230-bin.005' position 664857 011002 17:50:49 Slave: reconnected to master 'repl@xxx:3306',replication resumed in log 'xxx-bin.005' at position 664857 011002 17:51:49 Slave: received 0 length packet from server, apparent master shutdown: (0) This repeats every minute. [...] -- [EMAIL PROTECTED] -- Alex Ivanisevic, 2e systems Tel: +49 6192 978 497 Handy: +49 0174 310 9333 -- [EMAIL PROTECTED] -- Alex Ivanisevic, 2e systems Tel: +49 6192 978 497 Handy: +49 0174 310 9333 - 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
strange replication problems
Description: Hi, I have 28 linux boxes configured identically, 27 as slaves, one as master. On all 27 slaves, everything works fine, but only on one I have strange errors in the logs: 011002 17:49:49 Slave: received 0 length packet from server, apparent master shutdown: (0) 011002 17:50:49 Slave: Failed reading log event, reconnecting to retry, log 'cp4230-bin.005' position 664857 011002 17:50:49 Slave: reconnected to master 'repl@xxx:3306',replication resumed in log 'xxx-bin.005' at position 664857 011002 17:51:49 Slave: received 0 length packet from server, apparent master shutdown: (0) This repeats every minute. I repeat, replication is running fine, just these errors that are bugging me, and only on one of 27 machines. Could this be a networking problem of some sort? my my.cnf: [client] #password = your_password port= 3306 socket = /tmp/mysql.sock # Here follows entries for some specific programs # The MySQL server [mysqld] port= 3306 socket = /tmp/mysql.sock skip-locking set-variable= key_buffer=256M set-variable= max_allowed_packet=1M set-variable= table_cache=256 set-variable= sort_buffer=1M set-variable= record_buffer=1M set-variable= myisam_sort_buffer_size=64M set-variable= thread_cache=8 # Try number of CPU's*2 for thread_concurrency set-variable= thread_concurrency=4 set-variable= max_connections=2000 #log-bin server-id = 101 master-host=xxx master-user=repl master-password=micro master-port=3306 replicate-do-table=micro.screen_text replicate-do-table=micro.websp_prices replicate-do-table=micro.websp_flights replicate-do-table=micro.partner_config replicate-do-table=micro.banner_ads replicate-do-table=micro.country replicate-do-table=micro.partner_country replicate-do-table=micro.es_airports [mysqldump] quick set-variable= max_allowed_packet=16M [mysql] no-auto-rehash [isamchk] set-variable= key_buffer=128M set-variable= sort_buffer=128M set-variable= read_buffer=2M set-variable= write_buffer=2M [myisamchk] set-variable= key_buffer=128M set-variable= sort_buffer=128M set-variable= read_buffer=2M set-variable= write_buffer=2M [mysqlhotcopy] interactive-timeout How-To-Repeat: no idea Fix: no idea Submitter-Id: submitter ID Originator:root Organization: 2e Systems MySQL support: [none ] Synopsis: strange failures during replication Severity: non-critical Priority: low Category: mysql Class: sw-bug Release: mysql-3.23.39 (Source distribution) Environment: Dual PIII/866, 1Gb RAM, SuSe 7.1 System: Linux cp4120 2.2.18-SMP #1 SMP Sat Jan 20 07:41:17 GMT 2001 i686 unknown Architecture: i686 Some paths: /usr/bin/perl Compilation info: CC='gcc' CFLAGS='' CXX='c++' CXXFLAGS='' LDFLAGS='' LIBC: -rwxr-xr-x1 root root 1382179 Jan 19 2001 /lib/libc.so.6 -rw-r--r--1 root root 2585872 Jan 19 2001 /usr/lib/libc.a -rw-r--r--1 root root 178 Jan 19 2001 /usr/lib/libc.so Configure command: ./configure --prefix=/web/soft/mysql-3.23.39 --without-docs --without-bench --without-readline --without-debug --with-pthread --with-charset=german1 - 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 problems involving time
Also my master.info ends up being wrong whenever the server is rebooted. (binlog.100 becomes binlog.100\n). Can you expand on that problem a bit? When I do a show slave status before a reboot I have this: +-+-+-+---+-+--+---+-+-+ | Master_Host | Master_User | Master_Port | Connect_retry | Log_File| Pos | Slave_Running | Replicate_do_db | Replicate_ignore_db | +-+-+-+---+-+--+---+-+-+ | db_server | repl| 3306| 0 | db1-bin.200 | 18378935 | Yes | | | +-+-+-+---+-+--+---+-+-+ After a reboot I will have something like this: +-+-+-+---+-+--+---+-+-+ | Master_Host | Master_User | Master_Port | Connect_retry | Log_File| Pos | Slave_Running | Replicate_do_db | Replicate_ignore_db | +-+-+-+---+-+--+---+-+-+ | db_server | repl| 3306| 0 | db1-bin.200 | 18378935 | Yes | | | +-+-+-+---+-+--+---+-+-+ And it will not replicate because it has a carriage return after the file name, and says it cannot find the file. Mike sql,mysql - 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 problems involving time
On Wed, Aug 29, 2001 at 12:18:02PM -0400, Michael Eklund wrote: Am I the only one that thinks that mysql ought to replicate NOW, CURRENT_DATE, etc. as the time when those actual inserts happened not as NOW,CURRENT_DATE,etc. Probably not. If you are replicating accross a distance (12,15,20 hops) and there is a connectivity problem, then you end up with data that is out of sync. Very true. Also my master.info ends up being wrong whenever the server is rebooted. (binlog.100 becomes binlog.100\n). Can you expand on that problem a bit? 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 3 days, processed 33,538,278 queries (109/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 problems involving time
Am I the only one that thinks that mysql ought to replicate NOW, CURRENT_DATE, etc. as the time when those actual inserts happened not as NOW,CURRENT_DATE,etc. If you are replicating accross a distance (12,15,20 hops) and there is a connectivity problem, then you end up with data that is out of sync. Also my master.info ends up being wrong whenever the server is rebooted. (binlog.100 becomes binlog.100\n). Thanks, Mike Eklund DB Admin Netmechanic, Inc [EMAIL PROTECTED] http://www.netmechanic.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
replication problems questions
Hello, I'm very new to MySQL and have posted a few mails regarding replication in mySQL. I'm still having problems ! Can someone please send me a step by step guide on how I can setup replication between my ISPs MySQL database and one I wish to have and use locally. I have already a database created on my ISP's server and have created a table with data to test. My ISP runs RedHat and I have a username, password, database name and web address to access my own database. I run Win2K Professional and have just installed ver 1.2 (I think). I have not done anything else with the mySQL on my own computer. Thanks for any useful information Neil - 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
Fw: replication problems questions
Also regarding this matter, is it possible that I would on a local copy of the data on my own computer and it is then replicated automatically back to the ISP database ? Thanks Neil - Original Message - From: Neil Tompkins [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, August 15, 2001 10:55 PM Subject: replication problems questions Hello, I'm very new to MySQL and have posted a few mails regarding replication in mySQL. I'm still having problems ! Can someone please send me a step by step guide on how I can setup replication between my ISPs MySQL database and one I wish to have and use locally. I have already a database created on my ISP's server and have created a table with data to test. My ISP runs RedHat and I have a username, password, database name and web address to access my own database. I run Win2K Professional and have just installed ver 1.2 (I think). I have not done anything else with the mySQL on my own computer. Thanks for any useful information Neil - 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 problems questions
On Wed, Aug 15, 2001 at 10:55:53PM +0100, Neil Tompkins wrote: Hello, I'm very new to MySQL and have posted a few mails regarding replication in mySQL. I'm still having problems ! Can someone please send me a step by step guide on how I can setup replication between my ISPs MySQL database and one I wish to have and use locally. I have already a database created on my ISP's server and have created a table with data to test. If the instructions are not clear in the manual, please tell us how they can be improved. I made changes several months ago to improve them and will gladly do so again. But in order to do so, I need to know what's missing. At what step in the process it is unclear? I really would like the docs to be good. So we need to make an effort to fixing them if they're not--and helping you in the process. Thanks, 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.29: up 60 days, processed 572,260,825 queries (108/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 problems
Luc Foisy wrote: We are looking for a solution to backing up a remote database, without using mysqldump, and replication seemed to offer the solution required. I set up the master/slave configurations on both the master and the slave exactly the way it was shown in the MySQL documentation (4.10.3 How To Set Up Replication), With some additional options as shown: MASTER my.cnf [mysqld] log-bin binlog-do-db=masterslave server-id=1 SLAVE my.cnf [mysqld] master-host=172.16.0.100 master-user=rep1 master-password='yesmaster' master-port=3306 server-id=2 replicate-do-db=masterslave I checked the slave status thinking it was telling me everything was fine mysql show slave status; | Master_Host | Master_User | Master_Port | Connect_retry | Log_File | Pos | Slave_Running | Replicate_do_db | Replicate_ignore_db | | 172.16.0.100 | rep1| 3306| 60 | | 4| Yes | masterslave | | I made some updates on the master, a couple of inserts and deletes. Not a thing happened to the slave after a few minutes. I checked the error log to see if anything was wrong and this was written a few times (assuming each new attempt at a connection) 010809 13:16:51 Slave thread: error connecting to master:Access denied for user: '[EMAIL PROTECTED]' (Using password: YES)(0), retry in 60 sec I re-input my GRANT several times (checking/retyping the password each time), then connected remotely with the mysql -h option (not a problem) Has anyone else encountered this problem, and/or does anyone have a solution.. Have you given file privilege to user rep1 on your master server? -- _/_/ _/_/_/ - Rafa Jank [EMAIL PROTECTED] - _/ _/ _/ _/ _/ Wirtualna Polska SA http://www.wp.pl _/_/_/_/ _/_/_/ul. Uphagena 2, 80-237 Gdansk, tel/fax. (58) 5215625 _/ _/ _/ ==* http://szukaj.wp.pl *==-- - 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 problems
We are looking for a solution to backing up a remote database, without using mysqldump, and replication seemed to offer the solution required. I set up the master/slave configurations on both the master and the slave exactly the way it was shown in the MySQL documentation (4.10.3 How To Set Up Replication), With some additional options as shown: MASTER my.cnf [mysqld] log-bin binlog-do-db=masterslave server-id=1 SLAVE my.cnf [mysqld] master-host=172.16.0.100 master-user=rep1 master-password='yesmaster' master-port=3306 server-id=2 replicate-do-db=masterslave I checked the slave status thinking it was telling me everything was fine mysql show slave status; | Master_Host | Master_User | Master_Port | Connect_retry | Log_File | Pos | Slave_Running | Replicate_do_db | Replicate_ignore_db | | 172.16.0.100 | rep1| 3306| 60 | | 4| Yes | masterslave | | I made some updates on the master, a couple of inserts and deletes. Not a thing happened to the slave after a few minutes. I checked the error log to see if anything was wrong and this was written a few times (assuming each new attempt at a connection) 010809 13:16:51 Slave thread: error connecting to master:Access denied for user: '[EMAIL PROTECTED]' (Using password: YES)(0), retry in 60 sec I re-input my GRANT several times (checking/retyping the password each time), then connected remotely with the mysql -h option (not a problem) Has anyone else encountered this problem, and/or does anyone have a solution.. Thank you Luc Foisy Technical Magic 1 Stafford Road, Suite 325, Nepean, Ontario, K2H 1B9 Phone: (613) 829-7117 Fax: (613) 596-5096 E-Mail: [EMAIL PROTECTED] Fulfilling the Promise of Technology - 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 Problems
Hi Before enabling replication between two live machines, I've been doing some tests in the office between two similar machines. Everything appears to work well, as expected, except the command LOAD TABLE x FROM MASTER; This records an error on the screen ERROR 1188: Error From master: 'Access denied for user : ' and in mysql.err 010529 092358 fetch_nx_table: failed on create table Does anyone have an idea what might be causing this error ? Thanx Simon -- Simon Windsor CricInfo http://www.cricinfo.com/ Tel: +44 (0) 1249 700744 Fax: +44 (0) 1249 700725 Email: mailto:[EMAIL PROTECTED] This email message is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message. Thank you for your cooperation and assistance. - 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 Problems
Hi Problem solved. After adding select permission (now file+select) to the replication user the command LOAD TABLE x FROM MASTER works perfectly. The only question is, why does this command need SELECT permission, whilst the rest of replication does not ? Simon On Tuesday 29 May 2001 10:06, you wrote: Hi Before enabling replication between two live machines, I've been doing some tests in the office between two similar machines. Everything appears to work well, as expected, except the command LOAD TABLE x FROM MASTER; This records an error on the screen ERROR 1188: Error From master: 'Access denied for user : ' and in mysql.err 010529 092358 fetch_nx_table: failed on create table Does anyone have an idea what might be causing this error ? Thanx Simon -- Simon Windsor CricInfo http://www.cricinfo.com/ Tel: +44 (0) 1249 700744 Fax: +44 (0) 1249 700725 Email: mailto:[EMAIL PROTECTED] This email message is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message. Thank you for your cooperation and assistance. - 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 problems
does anyone that is using replication have any suggestions at all about how to get past this problem? is anyone using replication successfully? - Forwarded message from Jim Ziegler [EMAIL PROTECTED] - Envelope-to: [EMAIL PROTECTED] Delivery-date: Tue, 10 Apr 2001 16:42:26 -0400 Mailing-List: contact [EMAIL PROTECTED]; run by ezmlm (http://www.ezmlm.org) List-ID: mysql.mysql.com List-Help: mailto:[EMAIL PROTECTED] List-Unsubscribe: mailto:[EMAIL PROTECTED] List-Post: mailto:[EMAIL PROTECTED] List-Subscribe: mailto:[EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: replication problems From: Jim Ziegler [EMAIL PROTECTED] X-UIDL: bf6be71aa70b2cf435089831f4dbdcb3 mysql sql db I am unable to get replication running. I have set up both master and slave systems with version 3.23.33-log of musql. I can do a "load table from master" but when I start the slave, all I get in the slave log is: 010410 16:37:24 Slave: Failed reading log event, reconnecting to retry, log 'FIRST' position 4 010410 16:37:24 Slave: reconnected to master '[EMAIL PROTECTED]:3306',replication resumed in log 'FIRST' at position 4 010410 16:37:24 Error reading packet from server: Binlog has bad magic number, fire your magician (read_errno 0,server_errno=65535) and in the master log: 010410 16:35:583006 Connect[EMAIL PROTECTED] on 3007 Connect[EMAIL PROTECTED] on 3007 Quit 3006 Binlog Dum What is wrong? Where do I get a better magician? -- [EMAIL PROTECTED] (Jim Ziegler) - 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 - End forwarded message - -- [EMAIL PROTECTED] (Jim Ziegler) - 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 problems
mysql sql db I am unable to get replication running. I have set up both master and slave systems with version 3.23.33-log of musql. I can do a "load table from master" but when I start the slave, all I get in the slave log is: 010410 16:37:24 Slave: Failed reading log event, reconnecting to retry, log 'FIRST' position 4 010410 16:37:24 Slave: reconnected to master '[EMAIL PROTECTED]:3306',replication resumed in log 'FIRST' at position 4 010410 16:37:24 Error reading packet from server: Binlog has bad magic number, fire your magician (read_errno 0,server_errno=65535) and in the master log: 010410 16:35:583006 Connect[EMAIL PROTECTED] on 3007 Connect[EMAIL PROTECTED] on 3007 Quit 3006 Binlog Dum What is wrong? Where do I get a better magician? -- [EMAIL PROTECTED] (Jim Ziegler) - 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, problems with binlog-ignore-db
Hi, we use mysql with replication, all works fine. Now we wan't exclude some tables from the replication. I enter in my.cnf the line binlog-ignore-db = news (stop the master and restart the master after the change in my.cnf) After this, the master don't repliacted any databases, only the tables on the master db are updated. If i enter in mysql "show master status;" i get following result: +-+--+--+--+ | File| Position | Binlog_do_db | Binlog_ignore_db | +-+--+--+--+ | sumatra-bin.014 | 73 | | news | +-+--+--+--+ 1 row in set (0.01 sec) on thew slave i get: mysql show slave status; +-+-+-+---+-+-+---+-+-+++--+ | Master_Host | Master_User | Master_Port | Connect_retry | Log_File| Pos | Slave_Running | Replicate_do_db | Replicate_ignore_db | Last_errno | Last_error | Skip_counter | +-+-+-+---+-+-+---+-+-+++--+ | sumatra | blah| 3306| 60| sumatra-bin.014 | 73 | Yes | | news| 0 || 0 | +-+-+-+---+-+-+---+-+-+++--+ 1 row in set (0.00 sec) which each time has the same position number (on Master and SLave), when i enter the above statement I don't find something about souch a Problem in the manual. I use mysql Version 3.23.36 on Sun Solaris 2.6 Master configuration : log-bin server-id = 1 binlog-ignore-db = news Slave confguration: master-host=sumatra master-user=blah master-password=blah master-port=3306 server-id=2 Thank for your help Dani Daniel Tschanz Phone: +41 32 344 86 00 Rolotec AG Direct: +41 32 344 86 42 Gottstattstrasse 24, Postfach 8 Fax:+41 32 344 86 86 2500 Biel 8 mailto: [EMAIL PROTECTED] - 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
inserts with now() resulting in replication-problems
My Replication-Slave crashed a few days ago with an error in the error-log saying something like "duplicate primary key processing query "INSERT INTO testtable (time,name,number) VALUES (NOW(),'Hello',10)" testtable's primary key was (time,name) time: datetime name: varchar number: int What I was wondering after that: I do a "INSERT INTO testtable (time,name,number) VALUES (NOW(),'Hello',10) on the master. Then I do the *same* query again 1 Minute later. No Problem, since time has passed and NOW() is now different, so i do not have a duplictae key. If the Slave gets out of sync (loses the connection to the server), connects again and tries to catch up, it processes those two queries *within one second* which would result in an duplicate primary key on the slave. The Slave would abort with an error and the replication would stop. Is that right or did I misunderstand the concept? I changed the query to REPLACE instead of INSERT which is, in this case, fine with me, but is it possible to tell mysql to ignore such problems and keep the Replication up? I'd like to make the server send be a mail or SMS on my mobile phone if that happens. So if I could make the slave keep on replicating but write a line into a logfile like "serious-errors.log". I could then make some shell-script send me a mail... i'm running mysql 3.23.28-gamma on debian linux on Athlon-PCs. thanks for any explaination! Jan - 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: inserts with now() resulting in replication-problems
"JK" == Jan Kirchhoff [EMAIL PROTECTED] writes: JK If the Slave gets out of sync (loses the connection to the server), connects JK again and tries to catch up, it processes those two queries *within one JK second* which would result in an duplicate primary key on the slave. The JK Slave would abort with an error and the replication would stop. But then the slave is still not in sync since the data is different. That doesn't seem to make sense for a replication scheme... Just seems wrong to me, but what do I know ;-) - 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