Re: Problem with replication
Hi Dana, (B (Bthe tool might be free for developers, but is it also Open source ?? (BDoes it solve the issues described by the threat owner ? (BIf not, can the owner easily adapt it to solve his problems with it ? (B (BI am just adding this question, to make it not look like your are (Bmissusing the mailing list to just advertise your product and we get some (Breal usable info here. (B (BBest regards (B (BNils Valentin (BTokyo /Japan (B (B (B (B On Mon, Sep 27, 2004 at 10:43:53AM -0400, Dana Henderson wrote: (B My name is Dana Henderson. I am from IT-Map. A My SQL Migration (B Technology Partner. (B (B We have announced in this months MySQL Newsletter(Partners Corner) That (B you (B can get a fantastic, mature MySQL database toolkit for your company for (B free. (B (B http://www.it-map.com/html/mydbpal_.html (B (B (B dbPAL can be downloaded at www.it-map.com. This will help for (B migration, (B conversion, replication, database documentation, etl and change (B management. (B (B Go ahead and try it out then let me know what you think. (B (B Does it solve this problem? (B (B Jeremy (B -- (B Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! (B [EMAIL PROTECTED] | http://jeremy.zawodny.com/ (B (B [book] High Performance MySQL -- http://highperformancemysql.com/ (B (B -- (B MySQL General Mailing List (B For list archives: http://lists.mysql.com/mysql (B To unsubscribe: (B http://lists.mysql.com/[EMAIL PROTECTED] (B (B (B (B (B-- (BMySQL General Mailing List (BFor list archives: http://lists.mysql.com/mysql (BTo unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with Replication - Slave Option replicate-ignore-tabl e and replicate-wild-ignore-table
Yes. I also tried that. Are there any other suggestions? What's about the different sections in my.cnf? Should these statements beeing written under [mysqld_safe], too? Victor Pendleton wrote: In the slave's my.cnf have you tried just expliciting statement what tables to ignore? replicate-ignore-table=db.table1 replicate-ignore-table=db.table2 ... replicate-ignore-table=db.tablen And remove the other statements? -Original Message- From: Mike Lohman To: [EMAIL PROTECTED] Sent: 9/10/04 10:53 AM Subject: Problem with Replication - Slave Option replicate-ignore-table and replicate-wild-ignore-table Hi, I've got a running Master-Slave environment with 8 Slaves and 1 Master. The problem is not the replication itself, but to exclude some tables out of it. Excluding databases runs without problems. All server are of the same version: mysql --version mysql Ver 12.22 Distrib 4.0.18, for pc-linux-gnu (i686) From several forums, discussion groups and the manual I found out, that I have no mistake in my configs. But I cannot get it to run. Worse. Perhaps someone has an idea. I found some statements in this list, generated earlier, but never be answered: http://lists.mysql.com/mysql/153722 I tried several times to delete the master.info on the slave and restart the slave-server. Replication allways got up to work again. But the replicate-wild and replicate-ignore-table entries are never used. Please help. Part of the slave- my.cnf, concerning replication: master-host=masterip master-user=repl master-password=password master-port=3306 server-id = 2 replicate-ignore-db = mysql replicate-ignore-db = test replicate-do-db=normal replicate-wild-ignore-table=normal.page% Part of the master- my.cnf, concerning replication: server-id = 1 log-bin = /var/log/mysql/mysql-bin.log #log-update = /var/log/mysql/mysql-update.log binlog-do-db= normal binlog-ignore-db= mysql SHOW MASTER STATUS: mysql SHOW MASTER STATUS; +---+--+--+--+ | File | Position | Binlog_do_db | Binlog_ignore_db | +---+--+--+--+ | mysql-bin.002 | 761239 | normal| mysql| +---+--+--+--+ 1 row in set (0.02 sec) SHOW SLAVE STATUS: mysql SHOW SLAVE STATUS\G *** 1. row *** Master_Host: master IP Master_User: repl Master_Port: 3306 Connect_retry: 60 Master_Log_File: mysql-bin.002 Read_Master_Log_Pos: 774689 Relay_Log_File: slave-relay-bin.006 Relay_Log_Pos: 323911 Relay_Master_Log_File: mysql-bin.002 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_do_db: normal Replicate_ignore_db: mysql,test Last_errno: 0 Last_error: Skip_counter: 0 Exec_master_log_pos: 774689 Relay_log_space: 323911 1 row in set (0.00 sec) Thanks in advance. Mike -- [werk01] - Mike Lohmann Software Engineering | System Administration Herforder Strasse 24 33602 Bielefeld Telefon:+49 52 1 - 96 74 26 1 Fax:+49 52 1 - 96 74 36 4 Mobil: +49 17 1 - 27 04 15 6 http://www.werk01.de http://mike.werk01.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with Replication - Slave Option replicate-ignore-tabl e and replicate-wild-ignore-table
Hi, I have had similar problems with do-table, but never succeded finalizing my bug-report. I have added (on master) ... SET SQL_LOG_BIN = 0 as start of each action where not to replicate data SET SQL_LOG_BIN = 1 as start of each action where to replicate data Further I have had some issues where data seams to be lost during replication, mainly index, hardly data. For the last couple of days it seams OK after having added FLUSH LOGS (save log-files) after each action where data must be replicated. This migh give a number of smal replication-files, but actually they seams to be replicated faster than one big file. So right now, my master and two slaves seams to be in fine condition. Further I have made ans ASP/XML program where to read table-status from master and each slave, and comparing datasize, indexsize and so, for having a complete database-status view . Best regards Peter - Original Message - From: Mike Lohmann [EMAIL PROTECTED] To: Victor Pendleton [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Monday, September 13, 2004 11:49 AM Subject: Re: Problem with Replication - Slave Option replicate-ignore-tabl e and replicate-wild-ignore-table Yes. I also tried that. Are there any other suggestions? What's about the different sections in my.cnf? Should these statements beeing written under [mysqld_safe], too? Victor Pendleton wrote: In the slave's my.cnf have you tried just expliciting statement what tables to ignore? replicate-ignore-table=db.table1 replicate-ignore-table=db.table2 ... replicate-ignore-table=db.tablen And remove the other statements? -Original Message- From: Mike Lohman To: [EMAIL PROTECTED] Sent: 9/10/04 10:53 AM Subject: Problem with Replication - Slave Option replicate-ignore-table and replicate-wild-ignore-table Hi, I've got a running Master-Slave environment with 8 Slaves and 1 Master. The problem is not the replication itself, but to exclude some tables out of it. Excluding databases runs without problems. All server are of the same version: mysql --version mysql Ver 12.22 Distrib 4.0.18, for pc-linux-gnu (i686) From several forums, discussion groups and the manual I found out, that I have no mistake in my configs. But I cannot get it to run. Worse. Perhaps someone has an idea. I found some statements in this list, generated earlier, but never be answered: http://lists.mysql.com/mysql/153722 I tried several times to delete the master.info on the slave and restart the slave-server. Replication allways got up to work again. But the replicate-wild and replicate-ignore-table entries are never used. Please help. Part of the slave- my.cnf, concerning replication: master-host=masterip master-user=repl master-password=password master-port=3306 server-id = 2 replicate-ignore-db = mysql replicate-ignore-db = test replicate-do-db=normal replicate-wild-ignore-table=normal.page% Part of the master- my.cnf, concerning replication: server-id = 1 log-bin = /var/log/mysql/mysql-bin.log #log-update = /var/log/mysql/mysql-update.log binlog-do-db= normal binlog-ignore-db= mysql SHOW MASTER STATUS: mysql SHOW MASTER STATUS; +---+--+--+--+ | File | Position | Binlog_do_db | Binlog_ignore_db | +---+--+--+--+ | mysql-bin.002 | 761239 | normal| mysql| +---+--+--+--+ 1 row in set (0.02 sec) SHOW SLAVE STATUS: mysql SHOW SLAVE STATUS\G *** 1. row *** Master_Host: master IP Master_User: repl Master_Port: 3306 Connect_retry: 60 Master_Log_File: mysql-bin.002 Read_Master_Log_Pos: 774689 Relay_Log_File: slave-relay-bin.006 Relay_Log_Pos: 323911 Relay_Master_Log_File: mysql-bin.002 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_do_db: normal Replicate_ignore_db: mysql,test Last_errno: 0 Last_error: Skip_counter: 0 Exec_master_log_pos: 774689 Relay_log_space: 323911 1 row in set (0.00 sec) Thanks in advance. Mike -- [werk01] - Mike Lohmann Software Engineering | System Administration Herforder Strasse 24 33602 Bielefeld Telefon:+49 52 1 - 96 74 26 1 Fax:+49 52 1 - 96 74 36 4 Mobil: +49 17 1 - 27 04 15 6 http://www.werk01.de http://mike.werk01.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem with Replication - Slave Option replicate-ignore-table and replicate-wild-ignore-table
Hi, I've got a running Master-Slave environment with 8 Slaves and 1 Master. The problem is not the replication itself, but to exclude some tables out of it. Excluding databases runs without problems. All server are of the same version: mysql --version mysql Ver 12.22 Distrib 4.0.18, for pc-linux-gnu (i686) From several forums, discussion groups and the manual I found out, that I have no mistake in my configs. But I cannot get it to run. Worse. Perhaps someone has an idea. I found some statements in this list, generated earlier, but never be answered: http://lists.mysql.com/mysql/153722 I tried several times to delete the master.info on the slave and restart the slave-server. Replication allways got up to work again. But the replicate-wild and replicate-ignore-table entries are never used. Please help. Part of the slave- my.cnf, concerning replication: master-host=masterip master-user=repl master-password=password master-port=3306 server-id = 2 replicate-ignore-db = mysql replicate-ignore-db = test replicate-do-db=normal replicate-wild-ignore-table=normal.page% Part of the master- my.cnf, concerning replication: server-id = 1 log-bin = /var/log/mysql/mysql-bin.log #log-update = /var/log/mysql/mysql-update.log binlog-do-db= normal binlog-ignore-db= mysql SHOW MASTER STATUS: mysql SHOW MASTER STATUS; +---+--+--+--+ | File | Position | Binlog_do_db | Binlog_ignore_db | +---+--+--+--+ | mysql-bin.002 | 761239 | normal| mysql| +---+--+--+--+ 1 row in set (0.02 sec) SHOW SLAVE STATUS: mysql SHOW SLAVE STATUS\G *** 1. row *** Master_Host: master IP Master_User: repl Master_Port: 3306 Connect_retry: 60 Master_Log_File: mysql-bin.002 Read_Master_Log_Pos: 774689 Relay_Log_File: slave-relay-bin.006 Relay_Log_Pos: 323911 Relay_Master_Log_File: mysql-bin.002 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_do_db: normal Replicate_ignore_db: mysql,test Last_errno: 0 Last_error: Skip_counter: 0 Exec_master_log_pos: 774689 Relay_log_space: 323911 1 row in set (0.00 sec) Thanks in advance. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Problem with Replication - Slave Option replicate-ignore-tabl e and replicate-wild-ignore-table
In the slave's my.cnf have you tried just expliciting statement what tables to ignore? replicate-ignore-table=db.table1 replicate-ignore-table=db.table2 ... replicate-ignore-table=db.tablen And remove the other statements? -Original Message- From: Mike Lohman To: [EMAIL PROTECTED] Sent: 9/10/04 10:53 AM Subject: Problem with Replication - Slave Option replicate-ignore-table and replicate-wild-ignore-table Hi, I've got a running Master-Slave environment with 8 Slaves and 1 Master. The problem is not the replication itself, but to exclude some tables out of it. Excluding databases runs without problems. All server are of the same version: mysql --version mysql Ver 12.22 Distrib 4.0.18, for pc-linux-gnu (i686) From several forums, discussion groups and the manual I found out, that I have no mistake in my configs. But I cannot get it to run. Worse. Perhaps someone has an idea. I found some statements in this list, generated earlier, but never be answered: http://lists.mysql.com/mysql/153722 I tried several times to delete the master.info on the slave and restart the slave-server. Replication allways got up to work again. But the replicate-wild and replicate-ignore-table entries are never used. Please help. Part of the slave- my.cnf, concerning replication: master-host=masterip master-user=repl master-password=password master-port=3306 server-id = 2 replicate-ignore-db = mysql replicate-ignore-db = test replicate-do-db=normal replicate-wild-ignore-table=normal.page% Part of the master- my.cnf, concerning replication: server-id = 1 log-bin = /var/log/mysql/mysql-bin.log #log-update = /var/log/mysql/mysql-update.log binlog-do-db= normal binlog-ignore-db= mysql SHOW MASTER STATUS: mysql SHOW MASTER STATUS; +---+--+--+--+ | File | Position | Binlog_do_db | Binlog_ignore_db | +---+--+--+--+ | mysql-bin.002 | 761239 | normal| mysql| +---+--+--+--+ 1 row in set (0.02 sec) SHOW SLAVE STATUS: mysql SHOW SLAVE STATUS\G *** 1. row *** Master_Host: master IP Master_User: repl Master_Port: 3306 Connect_retry: 60 Master_Log_File: mysql-bin.002 Read_Master_Log_Pos: 774689 Relay_Log_File: slave-relay-bin.006 Relay_Log_Pos: 323911 Relay_Master_Log_File: mysql-bin.002 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_do_db: normal Replicate_ignore_db: mysql,test Last_errno: 0 Last_error: Skip_counter: 0 Exec_master_log_pos: 774689 Relay_log_space: 323911 1 row in set (0.00 sec) Thanks in advance. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with replication
SQL Hi! Does anybody have any solution? I change time where query run. Each new day where query run first ones in day replication crash. Slave start on slave server restore replication. How can it be? I have no any ideas. Both servers have 4.0.16 SQL Hi all! Nearly very night I have this problem. This query is done every 3 hours. But problem is happened only at 3:00 Both master and slave are 4 CPU (2 with HTT) computers. Both have mysql 4.0.16 And when I test table analit. There are many records which have the same conditions as in WHERE statement. This records are inserted into analit before this UPDATE. UPDATE finds records on master, I have checked the records on master and I see the result of UPDATE. But there is the error on slave server. If I run any query on analit like select count(*) from analit where label=1070931600 and after this SLAVE STOP; SLAVE START problem is solved. Is it a bug in server? Can mysqld break the order of replication query on multy-CPU computers? Any ideas, please. ++-+-+---+ -+-+--+---+ ---+--+---+ -+-++ - - - - - +--+- +-+ _ 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 _ ++-+-+---+ -+-+--+---+ ---+--+---+ -+-++ - - - - - +--+-+ -+ _ XXX _ Z _ _ 60_ a0-bin.029 _ 2210728 _ a1-relay-bin.001 _ 5880071 _ a0-bin.029 _ Yes _ No_ atas_ _ 1032 _ Error 'Can't find record in 'analit'' on query 'UPDATE analit, price_grp SET analit.status=price_grp.status, analit.currency=price_grp.currency WHERE analit.price=price_grp.price AND analit.service=price_grp.service AND analit.city=price_grp.city AND label=1070931600'. Default database: '' _ 0_ 2210187 _ 5880612 _ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Best regards. ___ Andrey Kotrekhov [EMAIL PROTECTED] ISP Alkar Teleport . +380 562 34-00-44 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem with replication
SQL Hi all! Nearly very night I have this problem. This query is done every 3 hours. But problem is happened only at 3:00 Both master and slave are 4 CPU (2 with HTT) computers. Both have mysql 4.0.16 And when I test table analit. There are many records which have the same conditions as in WHERE statement. This records are inserted into analit before this UPDATE. UPDATE finds records on master, I have checked the records on master and I see the result of UPDATE. But there is the error on slave server. If I run any query on analit like select count(*) from analit where label=1070931600 and after this SLAVE STOP; SLAVE START problem is solved. Is it a bug in server? Can mysqld break the order of replication query on multy-CPU computers? Any ideas, please. ++-+-+---+ -+-+--+---+ ---+--+---+ -+-++ - - - - - +--+- +-+ _ 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 _ ++-+-+---+ -+-+--+---+ ---+--+---+ -+-++ - - - - - +--+-+ -+ _ XXX _ Z _ _ 60_ a0-bin.029 _ 2210728 _ a1-relay-bin.001 _ 5880071 _ a0-bin.029 _ Yes _ No_ atas_ _ 1032 _ Error 'Can't find record in 'analit'' on query 'UPDATE analit, price_grp SET analit.status=price_grp.status, analit.currency=price_grp.currency WHERE analit.price=price_grp.price AND analit.service=price_grp.service AND analit.city=price_grp.city AND label=1070931600'. Default database: '' _ 0_ 2210187 _ 5880612 _ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Serious problem with replication
I still have this problem with replication, and i dont know how to fix it. I have a slave that has been running for a long time without any problems, but now its not replicating any more. So I tried to do a LOAD DATA MASTER; which always gives me this error now: Version: '4.0.15-standard-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 031008 15:07:59 Slave I/O thread: connected to master '[EMAIL PROTECTED]:3306', replication started in log 'mysql-bin.006' at position 357019 031008 15:08:14 Slave I/O thread exiting, read up to log 'mysql-bin.006', position 357019 031008 15:08:14 Couldn't fix table with quick recovery: Found wrong number of deleted records 031008 15:08:14 Run recovery again without -q 031008 15:08:14 Note: Retrying repair of: './otamanager/otafrontenduser' with keycache 031008 15:08:14 Couldn't fix table with quick recovery: Found wrong number of deleted records 031008 15:08:14 Run recovery again without -q 031008 15:08:14 Note: Retrying repair of: './otamanager/profile' with keycache 031008 15:08:16 Slave I/O thread: connected to master '[EMAIL PROTECTED]:3306', replication started in log 'mysql-bin.006' at position 357019 I have tried to do a REPAIR TABLE otafrontenduser,profile EXTENDED; but no success (its says ok, but the replication does still not work). I have attached my SHOW SLAVE STATUS; from the problem machine. I have tried dropping the database, rebooting the machine, and creating a new database, no success. Please let me know if I need to supply more info, I have no idea on what to do next. Med venlig hilsen/Best regards Søren Neigaard System Architect Mobilethink A/S Arosgaarden Åboulevarden 23, 4.sal DK - 8000 Århus C Telefon: +45 86207800 Direct: +45 86207810 Fax: +45 86207801 Email: [EMAIL PROTECTED] Web: www.mobilethink.dk +--+-+-+---+-+-+-+---+---+--+---+-+-+++--+-+-+ | 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 | +--+-+-+---+-+-+-+---+---+--+---+-+-+++--+-+-+ | 172.16.1.103 | dba | 3306| 60| mysql-bin.037 | 11894440| waspstat1-relay-bin.001 | 4 | mysql-bin.037 | Yes | Yes | | | 0 || 0| 11894440| 4 | +--+-+-+---+-+-+-+---+---+--+---+-+-+++--+-+-+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Problem with replication and corrupting tables
Quick question: Are the binlog and relaylog files the same format? Initial tests seem to indicate that they are the same. Can I use mysqlbinlog -o Relay_Log_Pos Relay_Log_File | mysql to get the slave more up to date (without having the slave SQL thread running)? I tried the above but the Relay_Log_Pos from 'show slave status' seemed way past the end of the file as it returned no results. :( How do I get a proper offset from which to start? Being able to do this would isolate the issue squarely at the slave SQL thread if the above had no issues. Also, I uploaded a small trace file that shows the corruption. It is the smallest I was able to make last night (about 72MB -- 6MB gzipped). It is in the secret folder. Hopefully it will help. -steve- - 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: Problem with replication and corrupting tables
Hi, mysqlbinlog -j Relay_Log_Pos Relay_Log_File | mysql works fine. I used -o instead of -j before. So I answered my last question. When doing this: mysqlbinlog -j Relay_Log_Pos Relay_Log_File | more I see that it had advanced to the query after the one with the problem in the trace file. In fact, the query succeeded and was there after a REPAIR TABLE .. USE_FRM. Now that I got the above to work, I ran it. And I found a surprising result (to me): It still failed. So the problem is not with the replication code per se. So maybe I can make a test case - 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: Problem with replication and corrupting tables
Hi, And fixed. Sorry for the waste of time. Only 4 days before I was set to replace the disk the database was on, and it is going bad. :( -steve- sql,query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Problem with replication and corrupting tables
An update. I'm now running the debug version on the slave. I could not trace out 'info' since it wrote way too much to the trace file. What I did find that was unique when the table crashed is this: handle_slave_sql: query: insert into forums_posts_new_3 ( w_search: error: Got errno: 0 from key_cache_read mi_write: error: Got error: 126 on write my_message_sql: error: Message: 'Incorrect key file for table: 'forums_posts_new_3'. Try to repair it' sql_print_error: error: Slave: error 'Incorrect key file for table: 'forums_posts_new_3'. Try to repair it' on query 'insert into forums_posts_new_3 I don't know why it had a problem with error zero from key_cache_read -- that seems to be the oddest thing in the log. It appears no where else. I'll keep digging. Does no one else have a problem with a slave stopping and corrupting its own tables? The only thing about the insert query that may be seen as odd is that it has binary data in it. That is one of the fields is like this: 'xÚÍTKoã6^P¾÷W~L}ÉÅÕf~K^B^EÚÝ,~\®~Qm7Û\0qÐ`~O#~S~R^HQ¤AÒQÕ_ßoH%V^N=ôP`~Q~Ge~ J3ó=ffKIó¡Ó~AZ~Ò$^O~MiRGm`¥é`9FbGø0^[´K?Ó~MN~T:m^B^]9à$RòHc~\^NÄ^T~S^O^S~M ^Fi~X^F^_Ø^Rç^PÒN~QoȤ~J^^ðÕé¿^R)~^ò»Þ¨H^G^^4Õ|èQ^TW^]¾LåC·~@±(~DòRÇèX½»¾¿ú. ÿûÌ©~[(²Q^[Z^?~Y¨Áw^].@^A~Oa\0;I;~B¨°e² ^^ݶß[~^~Lk¡~@~K^UÝ¡b2~@2ÊoÀ^Wîå?^E¹~\^Y^O^\z(a ^N^PGyö.3i~Bw~I¢æ~\\\N~NæÐ~_~N4\{®n~R^D~]~F£~DË~EàYel\\Ø~U*#Ô¥Æf`ò²^N¾×ùi^P w ®~N±Z~_ù¯·B¯ã^DÊ~R¥~H?~Cȶ|X~S`Uùdö~!àú£w^W~I~N§~T~QL/~D~A3Û~P~I®~V%ÿÔ0»õ^mÖ Yù^?Éü~GxmMJVÓíé0Q`^S~A~Cá~E+ÜgßîP³áÁØ~IÐ_³qq^CÍ}E×@^F^Es0^]:è èñ¡ð^UÍ~K1^U=~JÍR@ù¿¡{¦ágc7P?^U?^P7áb^BÞÜ^ZÞYá~C~FÅKk~^æ21C~K*Ø~Y87»^A~U ¥;ø^SL˽ÀÃ~IɶN^WA?×zåÛ^C^NiäܨÆÍ^e~]*úÝwÎM~R_Ò·n¹^A^T±~CJ^_~Vɾj| ^Yø~B~G^Ua^H^T+%7-ÚF/Así!Â0ÑV ^?áÐê~Jö`8^C±¦í^Rf¶^E\~L~N:^EiÄ~O:ê~Ph~O^TCÖD~VÆÑ2P~Kj~]^L·$^Qí^A¯fP~W:þ ÷´Ó¨~Wt^HF l^zÎ^Q¥tís~_| qGÀíµ\\^D#^H*UN®~D^A2~DL^BÚWU%)T`×g~WæÀç~Y^\±e~T^_^]E~OõC£?Y%vÕAs~_·~QË^XÀC¡ ^OB¦^Oç`~T*hÞ^^S^HÞ^_caÚ^[k^QóÓåiCÙ}9t~A¹t^A~J^CcQ¤A^W\4ÅÕ^YÀ~\ì~GKé3ý:^ S¸¢{ç^MQ[Ù^Ay%ü\^G¯ÂS¦^R^S^P~W^TbR͹û~\ÂÎ| E¿Ü`xq^A}åâr~H;^O~[^Z#éÊ8c8e¹| HÙãe^R^T®ç~~_^[M~WÅ!kp@^^ÜÏ~Ijû²~B^Vµ÷i^ÿ~_$~LŦ^\wnÉ2¥à~F~]Vk*´¡lãå\0ÝÞå~_ ^U½3CK1^\Þ¯ß~X~A[^]ßt|NUk~Z58~Jïßþ^HMTêðpõÿ^G~\AþçRtïc\\Ä×á*ÿ}s^\éáÓ~N®w;º ÙíöôøÛí-mo^_·_÷t^?÷ëçÕê~[õå^_-:^G^W' I'm starting to run out of ideas... -steve- - 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: Problem with replication and corrupting tables
Below is a trace (--debug=d,enter,exit,info,error,query,general,where: O,/tmp/mysqld.trace) of the slave thread. This is the best I can do as far as a bug report. No other queries were running and the slave I/O thread was idle (I firewalled its connection to the master/rest of the world). Without the SQL slave thread all is OK. This server can do any number of normal operations without error. The IO slave works fine. The SQL slave normally causes corruption, but has also caused a crash (a backtrace is in the first message of this thread). The error in this more detailed log seems different than in the previous log. But both point to the key cache. Why the SQL slave thread would cause something bad to happen in the key cache is beyond me. Another day... Very tired, -steve- my_b_seek: enter: pos: 0 my_malloc: exit: ptr: 84dc248 my_malloc: exit: ptr: 84bffd8 my_malloc: exit: ptr: 8525b18 handle_slave_sql: query: insert into forums_posts_new_0 ( forumid, messageid, parent, title, author, message, approved, email, ip, rootmessageid,loginid,autorespond,user_id ) values (32380, 1045077656, 0, 'Faculty experts available to discuss issues involving Korea', 'UM', 'http://www.umich.edu/news/Releases/2003/Feb03/r020703a.html', 'yes', '', inet_aton('244.118.132.197'), 1045077656, 0,'no','4a119100a6134a6dee9964dc257ea582' ) my_malloc: exit: ptr: 8522f60 set_lock_for_tables: enter: lock_type: 7 for_update: 1 check_access: enter: want_access: 2 master_access: 4294967295 hash_search: exit: found key at 26 my_malloc: exit: ptr: 8512f48 mi_get_status: info: key_file: 302662656 data_file: 1911596088 mi_write: enter: isam: 56 data: 57 _mi_make_key: exit: keynr: 0 w_search: enter: page: 64677888 key_cache_read: enter: file 56, filepos 64677888, length 1024 find_key_block: enter: file 56, filepos 64677888 _mi_bin_search: exit: flag: 1 keypos: 2 w_search: enter: page: 12455936 key_cache_read: enter: file 56, filepos 12455936, length 1024 find_key_block: enter: file 56, filepos 12455936 _mi_bin_search: exit: flag: 1 keypos: 4 w_search: enter: page: 8588288 key_cache_read: enter: file 56, filepos 8588288, length 1024 find_key_block: enter: file 56, filepos 8588288 _mi_bin_search: exit: flag: 1 keypos: 31 w_search: enter: page: 8554496 key_cache_read: enter: file 56, filepos 8554496, length 1024 find_key_block: enter: file 56, filepos 8554496 _mi_bin_search: exit: flag: 1 keypos: 28 _mi_insert: enter: key_pos: bfefc8ae key_cache_write: enter: file 56, filepos 8554496, length 1024 find_key_block: enter: file 56, filepos 8554496 _mi_make_key: exit: keynr: 1 w_search: enter: page: 118468608 key_cache_read: enter: file 56, filepos 118468608, length 1024 find_key_block: enter: file 56, filepos 118468608 _mi_bin_search: exit: flag: 1 keypos: 1 w_search: enter: page: 7552 key_cache_read: enter: file 56, filepos 7552, length 1024 find_key_block: enter: file 56, filepos 7552 _mi_bin_search: exit: flag: 1 keypos: 23 w_search: enter: page: 71856128 key_cache_read: enter: file 56, filepos 71856128, length 1024 find_key_block: enter: file 56, filepos 71856128 _mi_bin_search: exit: flag: 1 keypos: 11 w_search: enter: page: 71792640 key_cache_read: enter: file 56, filepos 71792640, length 1024 find_key_block: enter: file 56, filepos 71792640 w_search: error: page 71792640 had wrong page length: 26656 w_search: exit: Error: 126 mi_write: error: Got error: 126 on write print_error: enter: error: 126 my_message_sql: error: Message: 'Incorrect key file for table: 'forums_posts_new_0'. Try to repair it' thr_unlock: info: updating status: key_file: 302662656 data_file: 1911596088 flush_key_blocks_int: enter: file: 56 blocks_used: 8647 blocks_changed: 1 send_error: enter: sql_errno: 0 err: Incorrect key file for table: 'forums_posts_new_0'. Try to repair it close_thread_tables: info: thd-open_tables=0x84f4fc0 mi_extra: enter: function: 2 sql_print_error: error: Slave: error 'Incorrect key file for table: 'forums_posts_new_0'. Try to repair it' on query 'insert into forums_posts_new_0 ( forumid, messageid, parent, title, author, message, approved, email, ip, rootmessageid,loginid,autorespond,user_id ) values (32380, 1045077656, 0, 'Faculty experts available to discuss issues involving Korea', 'UM', 'http://www.umich.edu/news/Releases/2003/Feb03/r020703a.html', 'yes', '', inet_aton('144.118.132.197'), 1045077656, 0,'no','4a119100a6134a6dee9964dc257ea586' )', error_code=1034 sql_print_error: error: Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with SLAVE START. We stopped at log 'binlog.004' position 116581764 ~THD(): info: freeing host my_malloc: exit: ptr: 84aa508 hash_init: enter: hash: 84aa9b0 size: 16 my_malloc: exit: ptr: 84c74b8 vio_new: enter: sd=90 my_malloc:
Problem with replication and corrupting tables
Hi all, I have a problem with replication, that while repeatable for me very easily, I can not come up with a way for others to repeat it without all our tables and binlogs (tens of gigabytes). So I'm simply going to describe things here and see if anyone else has experienced anything similar or might have some suggestions. After thinking about using replication, for what seems like forever, I finally got around to it. Both the master and the slave are v4.0.10. I started it up and all seemed to work well for a while. Maybe a few hours. Then I found that a table got corrupted on the slave: ERROR: 1034 Incorrect key file for table: 'forums_posts_new_0'. Try to repair it 030215 10:01:12 Slave: error 'Incorrect key file for table: 'forums_posts_new_0'. Try to repair it' on query 'insert into forums_posts_new_0... Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with SLAVE START. We stopped at log 'binlog.003' position 97273308 At this point the slave SQL thread stopped. The IO thread continued. A couple of days later I noticed the error, repaired the table and started the slave thread again. With the IO thread so far ahead, the SQL thread could pump through the queries much faster. Now it only takes 3-4 minutes before another table gets corrupted. However, it is not just any table. I have tables 'forums_posts_new_0' to 'forums_posts_new_9' that hold messages. Out of all the tables, only these get corrupted. If I repair the table, then start the slave it will work for 5-15 minutes until another table is corrupted. Repeat. Repeat. Repeat. I checked the drives and the file system for errors and found no problems. The machine that acts as a slave to the master is also used for data-warehouse and FTS operations, has lots of disk access on its database and has no errors. I have tried stopping data warehouse and FTS operations while the slave runs, but it makes no difference. BTW: Sometimes the slave crashes when doing replication (and in the following example, only replication). Example of a backtrace: 0x806f53b handle_segfault + 447 0x826ae18 pthread_sighandler + 184 0x8296b07 memcpy + 39 0x823703d _mi_balance_page + 649 0x8236994 _mi_insert + 392 0x82367d2 w_search + 518 0x8236793 w_search + 455 0x8236793 w_search + 455 0x8236793 w_search + 455 0x8236793 w_search + 455 0x8236482 _mi_ck_write_btree + 142 0x82363e9 _mi_ck_write + 65 0x823602f mi_write + 591 0x80c257d write_row__9ha_myisamPc + 101 0x80a17f5 write_record__FP8st_tableP12st_copy_info + 513 0x80a110d mysql_insert__FP3THDP13st_table_listRt4List1Z4ItemRt4List1Zt4List1Z4Item 15enum_duplicates + 1129 0x807ad7a mysql_execute_command__Fv + 6598 0x807d226 mysql_parse__FP3THDPcUi + 146 0x80add97 exec_event__15Query_log_eventP17st_relay_log_info + 427 0x80e3faa exec_relay_log_event__FP3THDP17st_relay_log_info + 542 0x80e4aca handle_slave_sql + 602 0x82685cc pthread_start_thread + 220 0x829dd8a thread_start + 4 After the above crash, mysqld restarted and the slave continued to run for a while without error. Weird. For a while... -steve- - 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: problem with replication
Now is ok ... thank you very much! --- Jeremy Zawodny [EMAIL PROTECTED] ha scritto: On Wed, Dec 11, 2002 at 05:03:51PM +0100, Natale Babbo wrote: Hi all, I have a problem with replication between two servers. I created user repl on the master, I restored the master's dump on the slave, I changed both my.cnf settings but I got this error from slave .err file: MySql: ready for connections 021211 16:56:12 InnoDB: Started 021211 16:56:12 Slave I/O thread: connected to master 'repl@sviluppo01:3306', replication started in log 'FIRST' at position 4 021211 16:56:12 Error updating slave list: Query error 021211 16:56:12 Slave I/O thread exiting, read up to log 'FIRST', position 4 If your master is MySQL 4.0.x, make sure that the repl user has the REPLICATION SLAVE privilege. -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 3.23.51: up 1 days, processed 37,721,449 queries (412/sec. avg) __ Yahoo! Foto: salva, ritocca, condividi e ordina stampe professionali http://it.yahoo.com/mail_it/foot/?http://it.photos.yahoo.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: problem with replication
On Wed, Dec 11, 2002 at 05:03:51PM +0100, Natale Babbo wrote: Hi all, I have a problem with replication between two servers. I created user repl on the master, I restored the master's dump on the slave, I changed both my.cnf settings but I got this error from slave .err file: MySql: ready for connections 021211 16:56:12 InnoDB: Started 021211 16:56:12 Slave I/O thread: connected to master 'repl@sviluppo01:3306', replication started in log 'FIRST' at position 4 021211 16:56:12 Error updating slave list: Query error 021211 16:56:12 Slave I/O thread exiting, read up to log 'FIRST', position 4 If your master is MySQL 4.0.x, make sure that the repl user has the REPLICATION SLAVE privilege. -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 3.23.51: up 1 days, processed 37,721,449 queries (412/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: problem with replication
On Wednesday 11 December 2002 18:03, Natale Babbo wrote: I have a problem with replication between two servers. I created user repl on the master, I restored the master's dump on the slave, I changed both my.cnf settings but I got this error from slave .err file: MySql: ready for connections 021211 16:56:12 InnoDB: Started 021211 16:56:12 Slave I/O thread: connected to master 'repl@sviluppo01:3306', replication started in log 'FIRST' at position 4 021211 16:56:12 Error updating slave list: Query error 021211 16:56:12 Slave I/O thread exiting, read up to log 'FIRST', position 4 We had the same problem here at Ensita.NET. Installing MySQL server from the latest 4.0.6 tree solved this problem. So wait for the 4.0.6 release. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ 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: problem with replication
hi check the master-slave replication compatibility see http://www.mysql.com/doc/en/Replication_Implementation.html maxime -Message d'origine- De : Natale Babbo [mailto:[EMAIL PROTECTED]] Envoyé : jeudi 12 décembre 2002 17:49 À : [EMAIL PROTECTED] Cc : [EMAIL PROTECTED] Objet : RE: problem with replication Hi ... i have just tried by nothing's changed! ... i mean ... now in the show slave status i can see the right master_log_filename, log_position etc. but the error persists! What's wrong? help me please! Thanks and bye --- Maxime LEMAIRE [EMAIL PROTECTED] ha scritto: hi, try this : mysql SHOW MASTER STATUS; +--++--+--+ | File | Position | Binlog_do_db | Binlog_ignore_db | +--++--+- + | some-file| XX | | | +--++--+--+ mysql CHANGE MASTER TO MASTER_HOST='host', MASTER_USER='repl', MASTER_PASSWORD='repl', MASTER_LOG_FILE='some-file', MASTER_LOG_POS=XX; then restart the slave. -Message d'origine- De : Natale Babbo [mailto:[EMAIL PROTECTED]] Envoyé : jeudi 12 décembre 2002 16:27 À : [EMAIL PROTECTED] Objet : Fwd: problem with replication Any suggestion about this error? Thanks to all. --- Natale Babbo [EMAIL PROTECTED] ha scritto: Data: Wed, 11 Dec 2002 17:03:51 +0100 (CET) Da: Natale Babbo [EMAIL PROTECTED] Oggetto: problem with replication A: [EMAIL PROTECTED] Hi all, I have a problem with replication between two servers. I created user repl on the master, I restored the master's dump on the slave, I changed both my.cnf settings but I got this error from slave .err file: MySql: ready for connections 021211 16:56:12 InnoDB: Started 021211 16:56:12 Slave I/O thread: connected to master 'repl@sviluppo01:3306', replication started in log 'FIRST' at position 4 021211 16:56:12 Error updating slave list: Query error 021211 16:56:12 Slave I/O thread exiting, read up to log 'FIRST', position 4 Master my.cnf: log-bin=/usr/local/mysql/log/MySQL-bin server-id=1 Slave My.ini: server-id=2 master-host=sviluppo01 master-user=repl master-password=repl What's wrong? Thanks in advance. __ Yahoo! Foto: salva, ritocca, condividi e ordina stampe professionali http://it.yahoo.com/mail_it/foot/?http://it.photos.yahoo.com __ Yahoo! Foto: salva, ritocca, condividi e ordina stampe professionali http://it.yahoo.com/mail_it/foot/?http://it.photos.yahoo.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 - 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 __ Yahoo! Foto: salva, ritocca, condividi e ordina stampe professionali http://it.yahoo.com/mail_it/foot/?http://it.photos.yahoo.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
Fwd: problem with replication
Any suggestion about this error? Thanks to all. --- Natale Babbo [EMAIL PROTECTED] ha scritto: Data: Wed, 11 Dec 2002 17:03:51 +0100 (CET) Da: Natale Babbo [EMAIL PROTECTED] Oggetto: problem with replication A: [EMAIL PROTECTED] Hi all, I have a problem with replication between two servers. I created user repl on the master, I restored the master's dump on the slave, I changed both my.cnf settings but I got this error from slave .err file: MySql: ready for connections 021211 16:56:12 InnoDB: Started 021211 16:56:12 Slave I/O thread: connected to master 'repl@sviluppo01:3306', replication started in log 'FIRST' at position 4 021211 16:56:12 Error updating slave list: Query error 021211 16:56:12 Slave I/O thread exiting, read up to log 'FIRST', position 4 Master my.cnf: log-bin=/usr/local/mysql/log/MySQL-bin server-id=1 Slave My.ini: server-id=2 master-host=sviluppo01 master-user=repl master-password=repl What's wrong? Thanks in advance. __ Yahoo! Foto: salva, ritocca, condividi e ordina stampe professionali http://it.yahoo.com/mail_it/foot/?http://it.photos.yahoo.com __ Yahoo! Foto: salva, ritocca, condividi e ordina stampe professionali http://it.yahoo.com/mail_it/foot/?http://it.photos.yahoo.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: problem with replication
hi, try this : mysql SHOW MASTER STATUS; +--++--+--+ | File | Position | Binlog_do_db | Binlog_ignore_db | +--++--+- + | some-file| XX | | | +--++--+--+ mysql CHANGE MASTER TO MASTER_HOST='host', MASTER_USER='repl', MASTER_PASSWORD='repl', MASTER_LOG_FILE='some-file', MASTER_LOG_POS=XX; then restart the slave. -Message d'origine- De : Natale Babbo [mailto:[EMAIL PROTECTED]] Envoyé : jeudi 12 décembre 2002 16:27 À : [EMAIL PROTECTED] Objet : Fwd: problem with replication Any suggestion about this error? Thanks to all. --- Natale Babbo [EMAIL PROTECTED] ha scritto: Data: Wed, 11 Dec 2002 17:03:51 +0100 (CET) Da: Natale Babbo [EMAIL PROTECTED] Oggetto: problem with replication A: [EMAIL PROTECTED] Hi all, I have a problem with replication between two servers. I created user repl on the master, I restored the master's dump on the slave, I changed both my.cnf settings but I got this error from slave .err file: MySql: ready for connections 021211 16:56:12 InnoDB: Started 021211 16:56:12 Slave I/O thread: connected to master 'repl@sviluppo01:3306', replication started in log 'FIRST' at position 4 021211 16:56:12 Error updating slave list: Query error 021211 16:56:12 Slave I/O thread exiting, read up to log 'FIRST', position 4 Master my.cnf: log-bin=/usr/local/mysql/log/MySQL-bin server-id=1 Slave My.ini: server-id=2 master-host=sviluppo01 master-user=repl master-password=repl What's wrong? Thanks in advance. __ Yahoo! Foto: salva, ritocca, condividi e ordina stampe professionali http://it.yahoo.com/mail_it/foot/?http://it.photos.yahoo.com __ Yahoo! Foto: salva, ritocca, condividi e ordina stampe professionali http://it.yahoo.com/mail_it/foot/?http://it.photos.yahoo.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 - 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: problem with replication
Hi ... i have just tried by nothing's changed! ... i mean ... now in the show slave status i can see the right master_log_filename, log_position etc. but the error persists! What's wrong? help me please! Thanks and bye --- Maxime LEMAIRE [EMAIL PROTECTED] ha scritto: hi, try this : mysql SHOW MASTER STATUS; +--++--+--+ | File | Position | Binlog_do_db | Binlog_ignore_db | +--++--+- + | some-file| XX | | | +--++--+--+ mysql CHANGE MASTER TO MASTER_HOST='host', MASTER_USER='repl', MASTER_PASSWORD='repl', MASTER_LOG_FILE='some-file', MASTER_LOG_POS=XX; then restart the slave. -Message d'origine- De : Natale Babbo [mailto:[EMAIL PROTECTED]] Envoyé : jeudi 12 décembre 2002 16:27 À : [EMAIL PROTECTED] Objet : Fwd: problem with replication Any suggestion about this error? Thanks to all. --- Natale Babbo [EMAIL PROTECTED] ha scritto: Data: Wed, 11 Dec 2002 17:03:51 +0100 (CET) Da: Natale Babbo [EMAIL PROTECTED] Oggetto: problem with replication A: [EMAIL PROTECTED] Hi all, I have a problem with replication between two servers. I created user repl on the master, I restored the master's dump on the slave, I changed both my.cnf settings but I got this error from slave .err file: MySql: ready for connections 021211 16:56:12 InnoDB: Started 021211 16:56:12 Slave I/O thread: connected to master 'repl@sviluppo01:3306', replication started in log 'FIRST' at position 4 021211 16:56:12 Error updating slave list: Query error 021211 16:56:12 Slave I/O thread exiting, read up to log 'FIRST', position 4 Master my.cnf: log-bin=/usr/local/mysql/log/MySQL-bin server-id=1 Slave My.ini: server-id=2 master-host=sviluppo01 master-user=repl master-password=repl What's wrong? Thanks in advance. __ Yahoo! Foto: salva, ritocca, condividi e ordina stampe professionali http://it.yahoo.com/mail_it/foot/?http://it.photos.yahoo.com __ Yahoo! Foto: salva, ritocca, condividi e ordina stampe professionali http://it.yahoo.com/mail_it/foot/?http://it.photos.yahoo.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 - 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 __ Yahoo! Foto: salva, ritocca, condividi e ordina stampe professionali http://it.yahoo.com/mail_it/foot/?http://it.photos.yahoo.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
problem with replication
Hi all, I have a problem with replication between two servers. I created user repl on the master, I restored the master's dump on the slave, I changed both my.cnf settings but I got this error from slave .err file: MySql: ready for connections 021211 16:56:12 InnoDB: Started 021211 16:56:12 Slave I/O thread: connected to master 'repl@sviluppo01:3306', replication started in log 'FIRST' at position 4 021211 16:56:12 Error updating slave list: Query error 021211 16:56:12 Slave I/O thread exiting, read up to log 'FIRST', position 4 Master my.cnf: log-bin=/usr/local/mysql/log/MySQL-bin server-id=1 Slave My.ini: server-id=2 master-host=sviluppo01 master-user=repl master-password=repl What's wrong? Thanks in advance. __ Yahoo! Foto: salva, ritocca, condividi e ordina stampe professionali http://it.yahoo.com/mail_it/foot/?http://it.photos.yahoo.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: problem with replication
SHOW GRANTS FOR 'repluser'@'replmachine'; yes there is file grant on *.* - i used a simple password to avoid writing it wrong. as i said, if i connect with mysql client tool from the slave machine, i CAN login into the master server! Only the slave can't login into the master server (with the same authentication data!) (on the master server, of course). Check for the FILE privilege. The slave user won't need anything else. It doesn't need SELECT or whatever, only FILE. On the master server, you can do the following: DELETE FROM mysql.user WHERE User = 'repluser'; DELETE FROM mysql.db WHERE User = 'repluser'; FLUSH PRIVILEGES; GRANT FILE ON *.* [or only on the database you want to replicate] TO 'repluser'@'replmachine' IDENTIFIED BY 'some-password'; i did that - as described in the manual! - 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
problem with replication
hello, i wanna setup replication, everything works so far, but the slave can'tconenct to the master, access denied. but if i connect with the 'mysql' program from slave machine to master machine, it works! going crazy .. both mysqld's are 3.23.53 ! what is going wrong here ahh? regards, soenke. - 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: problem with replication
Dear Soenke, i wanna setup replication, everything works so far, but the slave can'tconenct to the master, access denied. but if i connect with the 'mysql' program from slave machine to master machine, it works! Before you go crazy, check if the slave (user) has sufficient privileges. Let's say the slave user is called repluser which connects from replmachine. In this case you do SHOW GRANTS FOR 'repluser'@'replmachine'; (on the master server, of course). Check for the FILE privilege. The slave user won't need anything else. It doesn't need SELECT or whatever, only FILE. On the master server, you can do the following: DELETE FROM mysql.user WHERE User = 'repluser'; DELETE FROM mysql.db WHERE User = 'repluser'; FLUSH PRIVILEGES; GRANT FILE ON *.* [or only on the database you want to replicate] TO 'repluser'@'replmachine' IDENTIFIED BY 'some-password'; Now, your slave should be able to replicate from the master. Hope it helps. Regards, -- Stefan Hinz [EMAIL PROTECTED] CEO / Geschäftsleitung iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Telefon: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Sönke Ruempler [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, November 24, 2002 11:53 AM Subject: problem with replication hello, i wanna setup replication, everything works so far, but the slave can'tconenct to the master, access denied. but if i connect with the 'mysql' program from slave machine to master machine, it works! going crazy .. both mysqld's are 3.23.53 ! what is going wrong here ahh? regards, soenke. - 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
Problem with replication
After much stress, I finally found the answer to my problem. Here is the error message that I was getting: 020322 11:54:29 Slave: Failed reading log event, reconnecting to retry, log 'bend149-bin.002' position 2407727 020322 11:54:29 Slave: reconnected to master '[EMAIL PROTECTED]:3306',replication resumed in log 'bend149-bin.002' at position 2407727 020322 11:54:29 Slave: received 0 length packet from server, apparent master shutdown: (0) and the solution that I found was to make sure the all of the servers in the system, including the master db server, had unique server id's. it took me a while to realize that the master and one of the slaves had the same id. this is in part because we had to switch out the old master db and replace it with one of the slave machines and then built a new machine to take the place of the missing slave. This is just to help out the next person that comes across this problem. it took me a while to find the solution and I hope this makes their search much shorter. Jim Lucas www.bend.com sql, query, select, insert, update, replace, etc... is that enough... - 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: Problem with replication
On Sun, Mar 11, 2001 at 06:16:22PM +0100, Rick Jansen - Tweakers.net wrote: Hi there, I'm trying to set up replication. But it's not working, for some strange reason. This is the error out of the slave log: 010311 18:11:25 Slave thread: error connecting to master:Access denied for user: 'repl@athena' (Using password: YES)(0), retry in 60 sec When i try to connect "manually", ie using the mysql tool, it works like it should. I get in with no problems. There are no errors in the master server logfile. Any idea what's going on here? What if you remove the "master.info" file on the slave and restart the replication? I've found something that I haven't reported as a bug, but I probably should. If you start replication, the slave stores the replication info in the master.info file. If you stop replication and the change the parameters in your my.cnf file, mysql reads the master.info file but never "sees" the changes you made in my.cnf. It just uses the old values in master.info. I've noted it in the on-line docs (in the comments at the end): http://www.mysql.com/doc/T/r/Troubleshooting_Replication.html but it should either be integrated into the docs or fixed. I'll find out if it is a bug or not first, I guess. Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 328-7878Fax: (408) 530-5454 Cell: (408) 439-9951 - 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: Problem with replication
Thanks for your time. I disabled replication (slave stop), removed the master.info file (it did contain a wrong/old password), stopped the mysqld (mysqladmin -p shutdown), and restarted it (safe_mysqld ). The information in master.info is correct now, but still it gets an access denied. The password is right, the user is right, the hostname is right.. sigh. Rick ** Server Administrator [Linux - FreeBSD] Websites: www.shellz.nl www.tweakers.net ICQ#: 37416519 E-mail: [EMAIL PROTECTED], [EMAIL PROTECTED] ** -Original Message- From: Jeremy D. Zawodny [mailto:[EMAIL PROTECTED]] Sent: Sunday, March 11, 2001 8:09 PM To: Rick Jansen - Tweakers.net Cc: [EMAIL PROTECTED] Subject: Re: Problem with replication On Sun, Mar 11, 2001 at 06:16:22PM +0100, Rick Jansen - Tweakers.net wrote: Hi there, I'm trying to set up replication. But it's not working, for some strange reason. This is the error out of the slave log: 010311 18:11:25 Slave thread: error connecting to master:Access denied for user: 'repl@athena' (Using password: YES)(0), retry in 60 sec When i try to connect "manually", ie using the mysql tool, it works like it should. I get in with no problems. There are no errors in the master server logfile. Any idea what's going on here? What if you remove the "master.info" file on the slave and restart the replication? I've found something that I haven't reported as a bug, but I probably should. If you start replication, the slave stores the replication info in the master.info file. If you stop replication and the change the parameters in your my.cnf file, mysql reads the master.info file but never "sees" the changes you made in my.cnf. It just uses the old values in master.info. I've noted it in the on-line docs (in the comments at the end): http://www.mysql.com/doc/T/r/Troubleshooting_Replication.html but it should either be integrated into the docs or fixed. I'll find out if it is a bug or not first, I guess. Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 328-7878Fax: (408) 530-5454 Cell: (408) 439-9951 - 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: Problem with replication
On Sun, Mar 11, 2001 at 09:02:57PM +0100, Rick Jansen - Tweakers.net wrote: Thanks for your time. I disabled replication (slave stop), removed the master.info file (it did contain a wrong/old password), stopped the mysqld (mysqladmin -p shutdown), and restarted it (safe_mysqld ). The information in master.info is correct now, but still it gets an access denied. The password is right, the user is right, the hostname is right.. sigh. Hmm... Anything relevant in the server's error log? -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 328-7878Fax: (408) 530-5454 Cell: (408) 439-9951 - 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: Problem with replication
No, nothing concerning the replication user that is. I do get this error alot (ie, every second): 010311 21:38:37 Aborted connection 2318 to db: 'gotforum' user: 'mysql' host: `aphrodite' (Got an error reading communication packets) 010311 21:38:38 Aborted connection 4700 to db: 'gotforum' user: 'mysql' host: `aphrodite' (Got an error reading communication packets) 010311 21:38:39 Aborted connection 4188 to db: 'tweakers' user: 'mysql' host: `athena' (Got an error reading communication packets) 010311 21:38:39 Aborted connection 1579 to db: 'stats' user: 'mysql' host: `athena' (Got an error reading communication packets) 010311 21:38:39 Aborted connection 4043 to db: 'gotforum' user: 'mysql' host: `aphrodite' (Got an error reading communication packets) 010311 21:38:40 Aborted connection 4632 to db: 'fokzine' user: 'mysql' host: `aphrodite' (Got an error reading communication packets) 010311 21:38:41 Aborted connection 5162 to db: 'stats' user: 'mysql' host: `athena' (Got an error reading communication packets) 010311 21:38:41 Aborted connection 4631 to db: 'gotforum' user: 'mysql' host: `aphrodite' (Got an error reading communication packets) 010311 21:38:43 Aborted connection 4685 to db: 'stats' user: 'mysql' host: `aphrodite' (Got an error reading communication packets) 010311 21:38:44 Aborted connection 4013 to db: 'gotforum' user: 'mysql' host: `aphrodite' (Got an error reading communication packets) But i don't know whether it's related. The errors were there before i tried to set up replication. Rick ** Server Administrator [Linux - FreeBSD] Websites: www.shellz.nl www.tweakers.net ICQ#: 37416519 E-mail: [EMAIL PROTECTED], [EMAIL PROTECTED] ** -Original Message- From: Jeremy D. Zawodny [mailto:[EMAIL PROTECTED]] Sent: Sunday, March 11, 2001 9:15 PM To: Rick Jansen - Tweakers.net Cc: [EMAIL PROTECTED] Subject: Re: Problem with replication Hmm... Anything relevant in the server's error log? -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 328-7878Fax: (408) 530-5454 Cell: (408) 439-9951 - 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
Another problem with REPLICATIOn
It's amazing. Configuration is fine (same as our test machines here). We are trying to use it on production machines now. These are the errors I get: 010208 12:35:29 Slave thread: error re-connecting to master:Binary log is not open, last_errno=0, retry in 0 sec I also have got this one: 010208 12:06:58 Slave thread: error connecting to master:Can't connect to MySQL server on '0' (111)(107), retry in 200 sec That '0' already changed to '' and to '4'. my.cnf on the master reads: [mysqld] bin-log server-id = 1 my.cnf in the slave reads: [mysqld] master-host = 200.212.x.y #we're using IP master-user = replication_user master-password = replication_password server-id = 2 When I started it at once, it seemed to be working fine. Now the slave just won't connect. What could be wrong? -- Leonardo Dias Catho Online WebDeveloper http://www.catho.com.br/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Problem with replication in 3.23.32
Greetings- I have been experiencing an unusual issue with replication in 3.23.32. We are using a dual-master setup on two FreeBSD4.2-STABLE machines. I am able to initially set up the replication, and issue simple updates from one or the other machine, but in certain circumstances we receive errors like this, although I've not changed any settings: 010126 23:31:14 Slave thread: error connecting to master:Can't connect to MySQL server on '86' (65)(22), retry in 3306 sec mysql show slave status; +-+--+-+---+ --+-+---+-+ -+ | Master_Host | Master_User | Master_Port | Connect_retry | Log_File | Pos | Slave_Running | Replicate_do_db | Replicate_ignore_db | +-+--+-+---+ --+-+---+-+ -+ | 86 | 209.95.64.253 | 0 | 3306 | test1-bin.001 | 0 | Yes | | | +-+--+-+---+ --+-+---+-+ -+ 1 row in set (0.01 sec) This has happened under a number of circumstances, the most recent being after resynching the two database, successfully performing a couple of updates, and then issuing "reset master" and "reset slave" on both machines. I then shutdown one machine's MySQL server, and inserted 100,000 very simple rows into the other server. Upon restarting the first server, it begins to replicate, all the way to about 93,000 records, and then quits with a message like this: 010126 22:27:30 Error reading packet from server: Could not find first log (read_errno 0,server_errno=65535) At this point, show slave status looks normal, but all replication has ceased. However, the next time that the slave is restarted, I see an anomaly such as the show slave status above, and also this in the master.info file (which was previously normal): test2-bin.001 0 73 test2.circle.n replicator 0 3306 ! 3306 10 As well as the following in the error log (timestamp equivalent to MySQL server startup): 010126 22:39:56 Warning: setrlimit returned ok, but didn't change limits. Max open files is 1064 010126 22:39:56 Warning: Changed limits: max_connections: 250 table_cache: 402 010126 22:40:41 Slave thread: error connecting to master:Can't connect to MySQL server on '86' (65)(65), retry in 3306 sec I have verified that the binary logs do exist and are readable by the mysql user. I have tried clearing all logs, resetting both master and slave on both servers, resynching and then restarting, and am still encountering this issue. A similar error also occurs when both servers are running - the replication stops at a certain point and the slave table gets corrupted as above. my.cnf reads as follows on both machines (with the master-host and server-id different): [mysqld] port= 3306 socket = /tmp/mysql.sock skip-locking set-variable= key_buffer=16M set-variable= max_allowed_packet=1M set-variable= table_cache=64 set-variable= sort_buffer=512K set-variable= net_buffer_length=8K set-variable= myisam_sort_buffer_size=8M set-variable= max_connections=250 set-variable= connect_timeout=60 log-update master-host=test2.circle.net master-user=x master-password=x master-connect-retry=10 log-bin server-id=2 Does anyone have any suggestions or ideas about this? Any help would be most appreciated. I would be glad to provide any more information that might be necessary. Please cc me off the list with any responses. Thanks for everyone's time- Brendan Sherar Server Operations Circle Net, Inc. http://www.circle.net/ - 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
Problem with replication. :-(
Hi, I'm running linux, mysql 3.23.32 (latest stable) I have 2 servers running a circular master-slave. Server1 has: log-bin=/var/lib/mysql/binary_log master-host=server 1 host master-user=user master-password=password log-slave-updates Server2 has: log-bin=/var/lib/mysql/binary_log master-host=server 2 host master-user=user master-password=password log-slave-updates I've verified many times that replication works quite well. But both servers are frequently getting into a situation where I continuously get: --- (from server 1) --- 010125 11:08:36 Slave: reconnected to master,replication resumed in log 'binary_log.001 ' at position 1169067 010125 11:08:36 Error reading packet from server: Could not find first log (read_errno 0,server_errno=65535) --- (from server 1) --- 010125 11:09:20 Slave: reconnected to master,replication resumed in log 'binary_log.002 ' at position 19702 010125 11:09:20 Error reading packet from server: Could not find first log (read_errno 0,server_errno=65535) This just goes on and on. How can I remedy this?? Thanks Ricardo - 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