Upgrade Mysql
Hi, I need to upgrade Mysql 4 to Mysql 5 on Linux. I will uninstall version 4 and install version 5. With uninstallation usually database files remain in /var/lib/mysql/ I want to know if with the installation of Mysql 5 those database will be recognized and imported to work with the new version automatically. Thank You.
Re: Upgrade Mysql
On May 20, 2009, at 1:27 AM, Webmaster Studio Informatica wrote: I need to upgrade Mysql 4 to Mysql 5 on Linux. Sometimes I will uninstall version 4 and install version 5. With uninstallation usually database files remain in /var/lib/mysql/ I want to know if with the installation of Mysql 5 those database will be recognized and imported to work with the new version automatically. In most cases, but you should have a database dump of all of them just in case. You do not specify what version of 4 you are at. You need to at least read this: http://dev.mysql.com/doc/refman/5.0/en/upgrading-from-4-1.html Specifically look at Incompatible change, there are quote a few. Most will not bother you. For me, the biggest issue was my use of timestamp, and how that changed a bit. Luckily I had a function that I used in my code to format that timestamp value, so it was just a matter of going through all my code and updating one function to all sites. This was also only a display issue for me and did not change my data. You do have to know your code. If you do not, I would use a staging server, and do them one database at a time, test, make sure it works, and go from there. -- Scott * If you contact me off list replace talklists@ with scott@ * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Mail System Error - Returned Mail
This Message was undeliverable due to the following reason: Your message was not delivered because the destination computer was not reachable within the allowed queue period. The amount of time a message is queued before it is returned depends on local configura- tion parameters. Most likely there is a network problem that prevented delivery, but it is also possible that the computer is turned off, or does not have a mail system running right now. Your message was not delivered within 2 days: Host 44.90.214.229 is not responding. The following recipients did not receive this message: mysql@lists.mysql.com Please reply to postmas...@upmraflatac.com if you feel this message to be in error. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Upgrade Mysql
On Wed, May 20, 2009 at 10:27:51AM +0200, Webmaster Studio Informatica wrote: Hi, I need to upgrade Mysql 4 to Mysql 5 on Linux. I will uninstall version 4 and install version 5. With uninstallation usually database files remain in /var/lib/mysql/ I want to know if with the installation of Mysql 5 those database will be recognized and imported to work with the new version automatically. Thank You. -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. Your best path is to If 4.0, Compile 4.1 Follow upgrade procedure If 4.1 Compile 5.0 Follow upgrade procedure If 5.0 Compile 5.1 Follow upgrade procedure. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
service terminating
I'm having a problem where every day or so, the mysql service is restarting. This is MySql 5 running on a win2003 server. The error in the event log says: Faulting application mysqld-nt.exe, version 0.0.0.0, faulting module mysqld-nt.exe, version 0.0.0.0, fault address 0x001fa173. In MySql's error file, it only shows that the service stopped unexpectedly and it's trying to recover: Version: '5.0.18-nt' socket: '' port: 3306 MySQL Community Edition (GPL) 090519 8:04:31 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... 090519 8:04:32 InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 7 3461755255. InnoDB: Doing recovery: scanned up to log sequence number 7 3461778224 090519 8:04:32 InnoDB: Starting an apply batch of log records to the database... InnoDB: Progress in percents: 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 InnoDB: Apply batch completed InnoDB: Last MySQL binlog file position 0 0, file name 090519 8:04:32 InnoDB: Started; log sequence number 7 3461778224 090519 8:04:32 [Note] D:\Mysql\bin\mysqld-nt: ready for connections. I have excluded the mysql data directory from the virus scan (Trend Micro) and this seems to have reduced the frequency but it still happens every day or two. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Large insert question
Hello, I'm working on a project that will be inserting very large text streams into a database. They range from 100K to 100M. I suspect that the average will be about 2M per insert. This is a low volume (under 20 inserts per day). I don't really need to optimize much on this but I had a question regarding max data per insert. I know some time ago on another project I had to increase sometime to handle inserts over a certain size because of a default setting that limited the size of the data per connection. Anyone know what setting I need to tweak to ensure that it can accept large inserts of this size? Thanks, Gary -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
SOS mysql signal syntax error
Hi Folks, I am getting syntax error with the mysql signal. I have a trigger that needs a signal for raising an error condition if a row with specific value is removed. CREATE TRIGGER my_trig BEFORE DELETE ON my_tbl FOR EACH ROW BEGIN DECLARE mysig CONDITION FOR SQLSTATE '45000'; IF OLD.name = 'base' THEN SIGNAL mysig SET MESSAGE_TEXT='base row removal is not allowed'; END IF; END -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: SOS mysql signal syntax error
Interesting. This syntax is only supposed to be available as of 5.4, but it doesn't even work there. The reference I found was at : http://dev.mysql.com/tech-resources/articles/mysql-54.html But I couldn't find other references to the new signal support. This is listed as the example on that page, but it doesn't work in 5.4.0-beta CREATE PROCEDURE p (divisor INT) BEGIN DECLARE divide_by_zero CONDITION FOR SQLSTATE '22012'; IF divisor = 0 THEN SIGNAL divide_by_zero; END IF; END Methinks someone forgot to include this feature in the release! -Original Message- From: Alex Katebi [mailto:alex.kat...@gmail.com] Sent: Wednesday, May 20, 2009 10:58 AM To: mysql Subject: SOS mysql signal syntax error Hi Folks, I am getting syntax error with the mysql signal. I have a trigger that needs a signal for raising an error condition if a row with specific value is removed. CREATE TRIGGER my_trig BEFORE DELETE ON my_tbl FOR EACH ROW BEGIN DECLARE mysig CONDITION FOR SQLSTATE '45000'; IF OLD.name = 'base' THEN SIGNAL mysig SET MESSAGE_TEXT='base row removal is not allowed'; END IF; END -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=gto...@ffn.com The information contained in this transmission may contain privileged and confidential information. It is intended only for the use of the person(s) named above. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: service terminating
Hi, aren't there any other clues before restarting? 2009/5/20 Duane Hebert duane.heb...@group-upc.com I'm having a problem where every day or so, the mysql service is restarting. This is MySql 5 running on a win2003 server. The error in the event log says: Faulting application mysqld-nt.exe, version 0.0.0.0, faulting module mysqld-nt.exe, version 0.0.0.0, fault address 0x001fa173. In MySql's error file, it only shows that the service stopped unexpectedly and it's trying to recover: Version: '5.0.18-nt' socket: '' port: 3306 MySQL Community Edition (GPL) 090519 8:04:31 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... 090519 8:04:32 InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 7 3461755255. InnoDB: Doing recovery: scanned up to log sequence number 7 3461778224 090519 8:04:32 InnoDB: Starting an apply batch of log records to the database... InnoDB: Progress in percents: 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 InnoDB: Apply batch completed InnoDB: Last MySQL binlog file position 0 0, file name 090519 8:04:32 InnoDB: Started; log sequence number 7 3461778224 090519 8:04:32 [Note] D:\Mysql\bin\mysqld-nt: ready for connections. I have excluded the mysql data directory from the virus scan (Trend Micro) and this seems to have reduced the frequency but it still happens every day or two. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=claudio.na...@gmail.com
RE: Large insert question
Michael, Thanks. Thats what I was looking for, I just couldn't remember what it was. Gary From: Michael Dykman [mdyk...@gmail.com] Sent: Wednesday, May 20, 2009 9:17 AM To: Gary Smith Cc: mysql@lists.mysql.com Subject: Re: Large insert question On Wed, May 20, 2009 at 12:02 PM, Gary Smith g...@primeexalia.com wrote: Hello, I'm working on a project that will be inserting very large text streams into a database. They range from 100K to 100M. I suspect that the average will be about 2M per insert. This is a low volume (under 20 inserts per day). I don't really need to optimize much on this but I had a question regarding max data per insert. I know some time ago on another project I had to increase sometime to handle inserts over a certain size because of a default setting that limited the size of the data per connection. Anyone know what setting I need to tweak to ensure that it can accept large inserts of this size? Thanks, Gary As I recall, max_allowed_packet is what controls that limit. -- - michael dykman - mdyk...@gmail.com - All models are wrong. Some models are useful. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=g...@primeexalia.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Large insert question
On Wed, May 20, 2009 at 12:02 PM, Gary Smith g...@primeexalia.com wrote: Hello, I'm working on a project that will be inserting very large text streams into a database. They range from 100K to 100M. I suspect that the average will be about 2M per insert. This is a low volume (under 20 inserts per day). I don't really need to optimize much on this but I had a question regarding max data per insert. I know some time ago on another project I had to increase sometime to handle inserts over a certain size because of a default setting that limited the size of the data per connection. Anyone know what setting I need to tweak to ensure that it can accept large inserts of this size? Thanks, Gary As I recall, max_allowed_packet is what controls that limit. -- - michael dykman - mdyk...@gmail.com - All models are wrong. Some models are useful. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Upgrade Mysql
Hi, I don't know what Linux distro you're using, but I'd make a backup of /var/lib/mysql dir before you do anything (in case the mysql package decides to nuke your stuff). If you have a dump of your dbs, that's fine too. And maybe a backup of your my.cnf. Just install the new mysql package, then start it. Then you'll need to run mysqlupgrade. Depending on the size of your database and type of tables you are using it can take a while. For InnoDB tables, for example, upgrade simply means copy to tmp table... that's really slow if you have a large table. Once mysqlupgrade runs without a hitch, you should be back in business. -Paul Webmaster Studio Informatica wrote: Hi, I need to upgrade Mysql 4 to Mysql 5 on Linux. I will uninstall version 4 and install version 5. With uninstallation usually database files remain in /var/lib/mysql/ I want to know if with the installation of Mysql 5 those database will be recognized and imported to work with the new version automatically. Thank You. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
mySQL slave IO Running and SQL Running
We have a master / slave setup and as you know, one bad query can ruin your whole day. Or if you accidentally write to the slave when you meant to write to the master, or any number of other things that break the fragility of a replication setup. The magic incantation to get them synched again seems to be to login to the slave and do this (over and over again until the Slave_IO_Running and Slave_SQL_Running both say Yes): mysql stop slave; SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; start slave; show slave status\G Is there a way to automate this a little bit. Maybe some bash script that uses mysql -e and parses for those two strings? Is this dangerous to do? Is there a setting to have the slave do this already? In every case I've ever seen, it's always some SQL that got out of whack like this: Last_Error: Error 'Duplicate key name 'id_operator'' on query. Default database: 'core'. Query: 'ALTER TABLE `user_has_notification` ADD INDEX `id_operator` (`id_operator`)'
Re: SOS mysql signal syntax error
OK I tried this exact syntax and I get the same error. I tried it on mysql client for 6.0.10 On Wed, May 20, 2009 at 2:22 PM, Gavin Towey gto...@ffn.com wrote: Interesting. This syntax is only supposed to be available as of 5.4, but it doesn't even work there. The reference I found was at : http://dev.mysql.com/tech-resources/articles/mysql-54.html But I couldn't find other references to the new signal support. This is listed as the example on that page, but it doesn't work in 5.4.0-beta CREATE PROCEDURE p (divisor INT) BEGIN DECLARE divide_by_zero CONDITION FOR SQLSTATE '22012'; IF divisor = 0 THEN SIGNAL divide_by_zero; END IF; END Methinks someone forgot to include this feature in the release! -Original Message- From: Alex Katebi [mailto:alex.kat...@gmail.com] Sent: Wednesday, May 20, 2009 10:58 AM To: mysql Subject: SOS mysql signal syntax error Hi Folks, I am getting syntax error with the mysql signal. I have a trigger that needs a signal for raising an error condition if a row with specific value is removed. CREATE TRIGGER my_trig BEFORE DELETE ON my_tbl FOR EACH ROW BEGIN DECLARE mysig CONDITION FOR SQLSTATE '45000'; IF OLD.name = 'base' THEN SIGNAL mysig SET MESSAGE_TEXT='base row removal is not allowed'; END IF; END -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=gto...@ffn.com The information contained in this transmission may contain privileged and confidential information. It is intended only for the use of the person(s) named above. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message.
Re: mySQL slave IO Running and SQL Running
Yeah Daevid! I know very well the issue! first set the slave to READ ONLY [mysqld] read-only then there is a configuration option to tell the server to skip some type of errors automatically slave-skip-errors= http://dev.mysql.com/doc/refman/5.1/en/replication-options-slave.html#option_mysqld_slave-skip-errors But, But, BUT! What I did is to remove the constraint on the table of the slave so that you can control better the thing. Because if you systematically skip the 'foreign key forcing' error, you will skip them with any table, if you remove just that constraint on that table you have the situation more under control. I think one of these two are enough, the cron is very not recomended! Ciao Claudio 2009/5/20 Daevid Vincent dae...@daevid.com We have a master / slave setup and as you know, one bad query can ruin your whole day. Or if you accidentally write to the slave when you meant to write to the master, or any number of other things that break the fragility of a replication setup. The magic incantation to get them synched again seems to be to login to the slave and do this (over and over again until the Slave_IO_Running and Slave_SQL_Running both say Yes): mysql stop slave; SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; start slave; show slave status\G Is there a way to automate this a little bit. Maybe some bash script that uses mysql -e and parses for those two strings? Is this dangerous to do? Is there a setting to have the slave do this already? In every case I've ever seen, it's always some SQL that got out of whack like this: Last_Error: Error 'Duplicate key name 'id_operator'' on query. Default database: 'core'. Query: 'ALTER TABLE `user_has_notification` ADD INDEX `id_operator` (`id_operator`)'
Re: Upgrade Mysql
I already posted about this: http://lists.mysql.com/mysql/215100 or google for moving from 3.23.58 to 5.0.45 I never recommend to install mysql as default package, is way too limiting. I manage to have as many mysql installations on the same server as the hardware can carry, but not software conflict. Always install mysql in custom way: http://lists.mysql.com/mysql/211957 Cheers Claudio 2009/5/20 Paul Choi paulc...@plaxo.com Hi, I don't know what Linux distro you're using, but I'd make a backup of /var/lib/mysql dir before you do anything (in case the mysql package decides to nuke your stuff). If you have a dump of your dbs, that's fine too. And maybe a backup of your my.cnf. Just install the new mysql package, then start it. Then you'll need to run mysqlupgrade. Depending on the size of your database and type of tables you are using it can take a while. For InnoDB tables, for example, upgrade simply means copy to tmp table... that's really slow if you have a large table. Once mysqlupgrade runs without a hitch, you should be back in business. -Paul Webmaster Studio Informatica wrote: Hi, I need to upgrade Mysql 4 to Mysql 5 on Linux. I will uninstall version 4 and install version 5. With uninstallation usually database files remain in /var/lib/mysql/ I want to know if with the installation of Mysql 5 those database will be recognized and imported to work with the new version automatically. Thank You. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=claudio.na...@gmail.com
Stored Procedure Data Types
Hello, I would like to do a select on a table to get back the IDs of some of the records. Then take those IDs and do a single update using a WHERE clause like (recordID IN (2,44,21)) My question is: Can I build a string using a cursor that has all of the IDs and then issue an update using the string as part of the WHERE clause? Are there functions that facilitate this better? I'm wondering if there is some sort of column function that will grab the IDs from the initial select. Below is my code. Thanks for any advice. DELIMITER $$ DROP PROCEDURE IF EXISTS sp_getNextQueueBlock$$ CREATE PROCEDURE sp_getNextQueueBlock() BEGIN DECLARE l_LinkQueueID INTEGER; DECLARE no_more_queue_items INT DEFAULT 0; DECLARE l_updateString VARCHAR(2000) DEFAULT ''; DECLARE queue_csr CURSOR FOR SELECT LinkQueueID FROM linkqueue WHERE Completed 0 LIMIT 200; DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_queue_items=1; START Transaction; OPEN queue_csr; queue_loop:LOOP FETCH queue_csr INTO l_LinkQueueID; IF no_more_queue_items=1 THEN LEAVE queue_loop; END IF; SET l_updateString=CONCAT(l_updateString,', ',l_LinkQueueID); END LOOP queue_loop; IF LENGTH(l_updateString) 2 THEN SET l_updateString=SUBSTRING(l_updateString,3,LENGTH(l_updateString)-2); END IF; UPDATE linkqueue SET Completed = 0 WHERE (LinkQueueID IN (l_updateString)); commit; END$$ DELIMITER ; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: mySQL slave IO Running and SQL Running
Please note that this is *NOT* a way to get them synched again In fact if you have to skip a replication statement on the slave then it is usually a sign your slave has different data than you master already. Skipping statements/errors may keep replication running, but you're just masking problems. -Original Message- From: Claudio Nanni [mailto:claudio.na...@gmail.com] Sent: Wednesday, May 20, 2009 12:49 PM To: Daevid Vincent Cc: mysql@lists.mysql.com Subject: Re: mySQL slave IO Running and SQL Running Yeah Daevid! I know very well the issue! first set the slave to READ ONLY [mysqld] read-only then there is a configuration option to tell the server to skip some type of errors automatically slave-skip-errors= http://dev.mysql.com/doc/refman/5.1/en/replication-options-slave.html#option_mysqld_slave-skip-errors But, But, BUT! What I did is to remove the constraint on the table of the slave so that you can control better the thing. Because if you systematically skip the 'foreign key forcing' error, you will skip them with any table, if you remove just that constraint on that table you have the situation more under control. I think one of these two are enough, the cron is very not recomended! Ciao Claudio 2009/5/20 Daevid Vincent dae...@daevid.com We have a master / slave setup and as you know, one bad query can ruin your whole day. Or if you accidentally write to the slave when you meant to write to the master, or any number of other things that break the fragility of a replication setup. The magic incantation to get them synched again seems to be to login to the slave and do this (over and over again until the Slave_IO_Running and Slave_SQL_Running both say Yes): mysql stop slave; SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; start slave; show slave status\G Is there a way to automate this a little bit. Maybe some bash script that uses mysql -e and parses for those two strings? Is this dangerous to do? Is there a setting to have the slave do this already? In every case I've ever seen, it's always some SQL that got out of whack like this: Last_Error: Error 'Duplicate key name 'id_operator'' on query. Default database: 'core'. Query: 'ALTER TABLE `user_has_notification` ADD INDEX `id_operator` (`id_operator`)' The information contained in this transmission may contain privileged and confidential information. It is intended only for the use of the person(s) named above. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Starting MySQL in Maintenance mode
How do I start MySQL in maintenance/single user mode so I can create indexes on a table that is being updated often? I prefer for the update transactions to fail immediately with a connection error rather than to wait for a lock to be released. In Oracle you can startup nomount. What is the equivalent for MySQL? George Tetterton Alcatel-Lucent 3400 W. Plano Pkwy Plano, TX 75075 Office: 972-477-1571 Cell: 972-400-0887 Email: george.tetter...@alcatel-lucent.com blocked::mailto:george.tetter...@alcatel-lucent.com Yahoo Messenger ID: george_tetterton
RE: mySQL slave IO Running and SQL Running
Well, in 90% of our cases it is. Most often caused by some dumb-ass (usually me) doing an INSERT or UPDATE on the slave on accident since I'm often logged into it doing SELECTs but I sometimes need to 'debug' or 'test' something and forget which box I'm on. So I happily do my altering of the slave's data and check my pages (which now are reading from slave) and all looks great, only to realize that saving via the web page isn't working. I then spend some time pulling my hair out and debugging the page only to realize that the page is writing to master (as it should) but replication has shit the bed from my aforementioned dumb-assed-ness and then I have to run said incantation below to get the binlog to skip and sync up again. But I understand what you're trying to say and concur. Blindly skipping binlog SQL commands is not any way to solve a problem. Eyeballs have to view the Last_Error and act appropriately. The 'read-only' seems to be a great preventative step that we're going to take and hopefully that will stave off a good portion of my stupid-user-mistakes. -Original Message- From: Gavin Towey [mailto:gto...@ffn.com] Sent: Wednesday, May 20, 2009 1:20 PM To: Claudio Nanni; Daevid Vincent Cc: mysql@lists.mysql.com Subject: RE: mySQL slave IO Running and SQL Running Please note that this is *NOT* a way to get them synched again In fact if you have to skip a replication statement on the slave then it is usually a sign your slave has different data than you master already. Skipping statements/errors may keep replication running, but you're just masking problems. -Original Message- From: Claudio Nanni [mailto:claudio.na...@gmail.com] Sent: Wednesday, May 20, 2009 12:49 PM To: Daevid Vincent Cc: mysql@lists.mysql.com Subject: Re: mySQL slave IO Running and SQL Running Yeah Daevid! I know very well the issue! first set the slave to READ ONLY [mysqld] read-only then there is a configuration option to tell the server to skip some type of errors automatically slave-skip-errors= http://dev.mysql.com/doc/refman/5.1/en/replication-options-sla ve.html#option_mysqld_slave-skip-errors But, But, BUT! What I did is to remove the constraint on the table of the slave so that you can control better the thing. Because if you systematically skip the 'foreign key forcing' error, you will skip them with any table, if you remove just that constraint on that table you have the situation more under control. I think one of these two are enough, the cron is very not recomended! Ciao Claudio 2009/5/20 Daevid Vincent dae...@daevid.com We have a master / slave setup and as you know, one bad query can ruin your whole day. Or if you accidentally write to the slave when you meant to write to the master, or any number of other things that break the fragility of a replication setup. The magic incantation to get them synched again seems to be to login to the slave and do this (over and over again until the Slave_IO_Running and Slave_SQL_Running both say Yes): mysql stop slave; SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; start slave; show slave status\G Is there a way to automate this a little bit. Maybe some bash script that uses mysql -e and parses for those two strings? Is this dangerous to do? Is there a setting to have the slave do this already? In every case I've ever seen, it's always some SQL that got out of whack like this: Last_Error: Error 'Duplicate key name 'id_operator'' on query. Default database: 'core'. Query: 'ALTER TABLE `user_has_notification` ADD INDEX `id_operator` (`id_operator`)' The information contained in this transmission may contain privileged and confidential information. It is intended only for the use of the person(s) named above. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Starting MySQL in Maintenance mode
Do 'service mysql restart --skip-networking' This prevents all TCP/IP connections You can login as r...@localhost and the client program will use the socket file rather than TCP/IP. Do all your DDL work. When done, 'service mysql restart' Rolando A. Edwards MySQL DBA (CMDBA) 155 Avenue of the Americas, Fifth Floor New York, NY 10013 212-625-5307 (Work) 201-660-3221 (Cell) AIM Skype : RolandoLogicWorx redwa...@logicworks.net -Original Message- From: TETTERTON George [mailto:george.tetter...@alcatel-lucent.com] Sent: Wednesday, May 20, 2009 4:30 PM To: mysql@lists.mysql.com Subject: Starting MySQL in Maintenance mode How do I start MySQL in maintenance/single user mode so I can create indexes on a table that is being updated often? I prefer for the update transactions to fail immediately with a connection error rather than to wait for a lock to be released. In Oracle you can startup nomount. What is the equivalent for MySQL? George Tetterton Alcatel-Lucent 3400 W. Plano Pkwy Plano, TX 75075 Office: 972-477-1571 Cell: 972-400-0887 Email: george.tetter...@alcatel-lucent.com blocked::mailto:george.tetter...@alcatel-lucent.com Yahoo Messenger ID: george_tetterton -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Starting MySQL in Maintenance mode
But my updates are coming from a web server on the localhost so will this still block connections? Sounds like the answer is no. I forgot to say that I am running MySql 5.0 on Solaris 10 if that makes a difference. -Original Message- From: Rolando Edwards [mailto:redwa...@logicworks.net] Sent: Wednesday, May 20, 2009 4:13 PM To: TETTERTON George; mysql@lists.mysql.com Subject: RE: Starting MySQL in Maintenance mode Do 'service mysql restart --skip-networking' This prevents all TCP/IP connections You can login as r...@localhost and the client program will use the socket file rather than TCP/IP. Do all your DDL work. When done, 'service mysql restart' Rolando A. Edwards MySQL DBA (CMDBA) 155 Avenue of the Americas, Fifth Floor New York, NY 10013 212-625-5307 (Work) 201-660-3221 (Cell) AIM Skype : RolandoLogicWorx redwa...@logicworks.net -Original Message- From: TETTERTON George [mailto:george.tetter...@alcatel-lucent.com] Sent: Wednesday, May 20, 2009 4:30 PM To: mysql@lists.mysql.com Subject: Starting MySQL in Maintenance mode How do I start MySQL in maintenance/single user mode so I can create indexes on a table that is being updated often? I prefer for the update transactions to fail immediately with a connection error rather than to wait for a lock to be released. In Oracle you can startup nomount. What is the equivalent for MySQL? George Tetterton Alcatel-Lucent 3400 W. Plano Pkwy Plano, TX 75075 Office: 972-477-1571 Cell: 972-400-0887 Email: george.tetter...@alcatel-lucent.com blocked::mailto:george.tetter...@alcatel-lucent.com Yahoo Messenger ID: george_tetterton -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
