RE: Upgrading How To
Reindl, I am sorry, in my original post, I forgot to mention that the OLD box and the NEW box are the same physical machine. I need to be able to save all data into files on a memstick or portable disc and restore them to the newly staged machine (with the new version of mysql). -Grant -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Upgrading How To
Hi Grant, On 12/26/2014 11:18 AM, Grant Peel wrote: Reindl, I am sorry, in my original post, I forgot to mention that the OLD box and the NEW box are the same physical machine. I need to be able to save all data into files on a memstick or portable disc and restore them to the newly staged machine (with the new version of mysql). -Grant There are a few file-level storage changes between 5.x (where x 6) and 5.6 that you may need to resolve before the upgrade. Examples: * 5.6 will not read any tables that were physically created in a version older than 5.0 and never rebuilt using a newer version. * the YEAR(2) data type is no longer supported. * pre 4.1 passwords - If you are upgrading from version 5.1 or older, you will need to update their hashes or configure 5.6 to recognize the older hashes as valid. The user authentication system in 5.6 is more advanced than in earlier versions. Several features are removed as of 5.6 http://dev.mysql.com/doc/refman/5.6/en/mysql-nutshell.html#mysql-nutshell-removals Many defaults were changed starting with 5.6. These and other things to consider before a move to 5.6 (like the SQL Mode and timestamp behaviors) are all listed here: http://dev.mysql.com/doc/refman/5.6/en/upgrading-from-previous-series.html mysql_upgrade will update the system tables in the `mysql` database and run a CHECK TABLE ... FOR UPGRADE on all your tables but it cannot handle all of the possible upgrade issues you may encounter due to the other things about the server that may have changed. Reindl's technique with the rsync is just like what you are doing with your full-image save/restore. His is just optimized for operating between two live machines. You are also very strongly encouraged to test the upgrade to 5.6 on a lab box long before you push it into production. This will give you the chance to find any of those new 5.6 changes that your clients may not be ready to handle. Yours, -- Shawn Green MySQL Senior Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Upgrading How To
Shawn all, Thank you for taking to time to reply. So, to be clear, what I understand from your post is that replacing the new build's grant/system tables with the archived ones from the previous version, generally works fine, upgrade issues not withstanding. This is the answer I was hoping for. FWIW, I have already tested using some sample databases from the old version to the new one. Also, I'm not sure I mentioned, but I am moving from 5.1.39 to 5.6.17. I have already ran into the password hash issues on a number of tables, but, other than that things seem fine. Any other comments are welcome. -G
Re: Upgrading How To
Am 26.12.2014 um 20:52 schrieb Grant Peel: Shawn all, Thank you for taking to time to reply. So, to be clear, what I understand from your post is that replacing the new build's grant/system tables with the archived ones from the previous version, generally works fine, upgrade issues not withstanding. This is the answer I was hoping for. FWIW, I have already tested using some sample databases from the old version to the new one. Also, I'm not sure I mentioned, but I am moving from 5.1.39 to 5.6.17. I have already ran into the password hash issues on a number of tables, but, other than that things seem fine. that should be in general fine, i would recommend a scipted optimize table for any tables on the old machine before starting for two reasons * the data to transfer will be smaller * all old tables will be for sure rebuilt and not in 5.0 format the rsync works also fine with a USB stick, ext4 format preferred because owner / permissions, but that can be fixed easily in any case on the new machine per chmod / chown the only difference in your case is that you have a larger downtime if the hot rsync followed by a cold one with a USB stick as destination is noticeable faster needs to be tested, maybe have a empty datadir on the destination and transfer all data is faster then the checksumming signature.asc Description: OpenPGP digital signature
Upgrading How To
Hi all, I was wondering if anyone knows of a concise tutorial on how to upgrade (by moving from one box (old) to another box (new) mysql in a virtual environment (many mysql users, many databases). Example: Mysql 5.x setup on freebsd 8.x (x86/32b), call this box A. Want to move to a new box: Mysql 5.6.17 on freebsd 9.3 (x86/32b) call this box B. 1) How does one correctly move the users and all the permissions (grant tables), 2) How does one move the data. 3) Assume lots of backups have been done and there is no risk of permanently loosing data. Also, move/copy to be done using files (to memstick or external disk or NFS), not using ssh directly (rcopy etc). 4) Basically, dumpt the data and users and perms and put it on a new box J I suspect this question is trivial to a lot of admins out there, but, I suspect it would be helpful to many out there. Happy Holidays! -Grant
Re: Upgrading How To
Am 25.12.2014 um 16:01 schrieb Grant Peel: I was wondering if anyone knows of a concise tutorial on how to upgrade (by moving from one box (old) to another box (new) mysql in a virtual environment (many mysql users, many databases). Mysql 5.x setup on freebsd 8.x (x86/32b), call this box A. Want to move to a new box: Mysql 5.6.17 on freebsd 9.3 (x86/32b) call this box B. * setup the new box * stop mysqld on the old * rsync /var/lib/mysqld to the new one * start mysql on the new one * mysql_upgrade -u root -p * enter root pwd * done to keep downtime as low as possible rsync one or two times *hot* without stop mysqld on the old machine so the last rsync only transfer diffs - doing that for 12 years now from MySQl 3.x to 5.5 between Windows, MacOSX and Linux systems in all directions rsync params: --force --delete-after -tPrlpogEAX signature.asc Description: OpenPGP digital signature
Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6
Am 20.02.2013 18:26, schrieb Mike Franon: So I did a full mysqldump over the weekend for a second time and this time it is 220GB, no clue what happened last time, I should have realized looking at the file size something was wrong, but since I got no errors did not think about it, and this time I timed it, took 7 hours to do a complete mysqldump Restoring it is not fun 18+ hours and counting, at this rate it will be a week, there has to be a better way of doing this, and this is only going form 5.0 to 5.1 I know some are saying don't need to do a mysqldump, but if i don't do it, the upgrade errors out on 10 tables, and then gives me errors about triggers and did you ALWAYS mysql_upgarde -root -p after ANY mysql-update? at least before try a major upgrade? did you try mysqlcheck -h localhost --check-upgrade --all-databases --auto-repair --user=root -p BEFORE the upgrade? did you try it ALSo after the upgrade? sorry, i do not believe that dump/import is needed and idoubt it will not give better results signature.asc Description: OpenPGP digital signature
Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6
So I did a full mysqldump over the weekend for a second time and this time it is 220GB, no clue what happened last time, I should have realized looking at the file size something was wrong, but since I got no errors did not think about it, and this time I timed it, took 7 hours to do a complete mysqldump Restoring it is not fun 18+ hours and counting, at this rate it will be a week, there has to be a better way of doing this, and this is only going form 5.0 to 5.1 I know some are saying don't need to do a mysqldump, but if i don't do it, the upgrade errors out on 10 tables, and then gives me errors about triggers On Tue, Feb 19, 2013 at 6:34 PM, Reindl Harald h.rei...@thelounge.net wrote: Am 19.02.2013 23:53, schrieb Divesh Kamra: Hi Reindi Thanks for solution . Can u share complete steps ? which steps? * update * call mysql_upgrade -u root -p in doubt mysqlcheck -h localhost --check-upgrade --all-databases --auto-repair --user=root -p and if you do mysql_upgrade -u root -p and are always up-to-date that was it, no matter if you move your data from windows to MacOSX and finally to linux or whatever OS On 20-Feb-2013, at 2:50, Reindl Harald h.rei...@thelounge.net wrote: surely * use mysql_upgrade -u root -p after EACH update * upgrade regulary we went from MySQL 3.x to 5.5.30 until know without any dump and here are around 5000 tables Am 19.02.2013 22:12, schrieb Divesh Kamra: Is there any better way for grade MySQL version without taking backup with mysqldump Or if there any tool for this R's DK On 16-Feb-2013, at 16:07, Reindl Harald h.rei...@thelounge.net wrote: Am 16.02.2013 09:42, schrieb Manuel Arostegui: 2013/2/15 Reindl Harald h.rei...@thelounge.net mailto:h.rei...@thelounge.net our database is 400 GB, mysqldump is 600MB was not a typo and you honestly believed that you can import this dump to somewhat? WTF - as admin you should be able to see if the things in front of you are theoretically possible before your start any action and 1:400 is impossible, specially because mysql-dumps are ALWAYS WAY LARGER then the databasses because they contain sql-statements and not only data That's not completely true. If you have a poor maintained database or just tables with lot of writes and deletes and you don't periodically optimize it - you can end up with lot of blank spaces in your tables which will use _a lot_ of space. If you do a du or whatever to measure your database size...you can get really confused. mysqldump obviously doesn't backup blank spaces and once you get rid of them, your database will use much less space. ok, normally i expect there is a admin and doing his job especially for large datasets -- Reindl Harald the lounge interactive design GmbH A-1060 Vienna, Hofmühlgasse 17 CTO / CISO / Software-Development p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40 icq: 154546673, http://www.thelounge.net/ http://www.thelounge.net/signature.asc.what.htm -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6
I am pretty sure I did, and when I did I got the following errors: Error: Table Upgrade Required, Please dump/reload to fix it I got that on 10 tables, and also got the following: Warning: Triggers for table ' have no creation context. I think it has to do with no triggers. I know hen I ran the mysql_upgrade it tired to auto repair but did not work and failed. But I will give it a shot again, maybe I missed something. The other thing I was thinking was maybe I can just mysqldump those 10 tables that it fails on, and just restore those instead of my entire db. On Wed, Feb 20, 2013 at 12:43 PM, Reindl Harald h.rei...@thelounge.net wrote: Am 20.02.2013 18:26, schrieb Mike Franon: So I did a full mysqldump over the weekend for a second time and this time it is 220GB, no clue what happened last time, I should have realized looking at the file size something was wrong, but since I got no errors did not think about it, and this time I timed it, took 7 hours to do a complete mysqldump Restoring it is not fun 18+ hours and counting, at this rate it will be a week, there has to be a better way of doing this, and this is only going form 5.0 to 5.1 I know some are saying don't need to do a mysqldump, but if i don't do it, the upgrade errors out on 10 tables, and then gives me errors about triggers and did you ALWAYS mysql_upgarde -root -p after ANY mysql-update? at least before try a major upgrade? did you try mysqlcheck -h localhost --check-upgrade --all-databases --auto-repair --user=root -p BEFORE the upgrade? did you try it ALSo after the upgrade? sorry, i do not believe that dump/import is needed and idoubt it will not give better results -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6
OK I got it to work. I dumped the tables that it was complaining about first, and then dumped the triggers. I then uninstalled anything to do with mysql, and installed 5.1 Then imported the tables and triggers, and and able to run mysql_upgrade without any errors. This is all without using a full mysqldump. I am now going to go from 5.1 to 5.5 On Wed, Feb 20, 2013 at 12:54 PM, Mike Franon kongfra...@gmail.com wrote: I am pretty sure I did, and when I did I got the following errors: Error: Table Upgrade Required, Please dump/reload to fix it I got that on 10 tables, and also got the following: Warning: Triggers for table ' have no creation context. I think it has to do with no triggers. I know hen I ran the mysql_upgrade it tired to auto repair but did not work and failed. But I will give it a shot again, maybe I missed something. The other thing I was thinking was maybe I can just mysqldump those 10 tables that it fails on, and just restore those instead of my entire db. On Wed, Feb 20, 2013 at 12:43 PM, Reindl Harald h.rei...@thelounge.net wrote: Am 20.02.2013 18:26, schrieb Mike Franon: So I did a full mysqldump over the weekend for a second time and this time it is 220GB, no clue what happened last time, I should have realized looking at the file size something was wrong, but since I got no errors did not think about it, and this time I timed it, took 7 hours to do a complete mysqldump Restoring it is not fun 18+ hours and counting, at this rate it will be a week, there has to be a better way of doing this, and this is only going form 5.0 to 5.1 I know some are saying don't need to do a mysqldump, but if i don't do it, the upgrade errors out on 10 tables, and then gives me errors about triggers and did you ALWAYS mysql_upgarde -root -p after ANY mysql-update? at least before try a major upgrade? did you try mysqlcheck -h localhost --check-upgrade --all-databases --auto-repair --user=root -p BEFORE the upgrade? did you try it ALSo after the upgrade? sorry, i do not believe that dump/import is needed and idoubt it will not give better results -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6
fine and much faster and probably safer too :-) a backup with rsync is faster as dump/import and can be done with minimize downtime by use it twice, the first time hot-backup with running server and the second time after stop server to get the diffs doing rsync - stop - rsync - start in a script may reduce the downtime to a few seconds Am 20.02.2013 20:29, schrieb Mike Franon: OK I got it to work. I dumped the tables that it was complaining about first, and then dumped the triggers. I then uninstalled anything to do with mysql, and installed 5.1 Then imported the tables and triggers, and and able to run mysql_upgrade without any errors. This is all without using a full mysqldump. I am now going to go from 5.1 to 5.5 On Wed, Feb 20, 2013 at 12:54 PM, Mike Franon kongfra...@gmail.com wrote: I am pretty sure I did, and when I did I got the following errors: Error: Table Upgrade Required, Please dump/reload to fix it I got that on 10 tables, and also got the following: Warning: Triggers for table ' have no creation context. I think it has to do with no triggers. I know hen I ran the mysql_upgrade it tired to auto repair but did not work and failed. But I will give it a shot again, maybe I missed something. The other thing I was thinking was maybe I can just mysqldump those 10 tables that it fails on, and just restore those instead of my entire db. On Wed, Feb 20, 2013 at 12:43 PM, Reindl Harald h.rei...@thelounge.net wrote: Am 20.02.2013 18:26, schrieb Mike Franon: So I did a full mysqldump over the weekend for a second time and this time it is 220GB, no clue what happened last time, I should have realized looking at the file size something was wrong, but since I got no errors did not think about it, and this time I timed it, took 7 hours to do a complete mysqldump Restoring it is not fun 18+ hours and counting, at this rate it will be a week, there has to be a better way of doing this, and this is only going form 5.0 to 5.1 I know some are saying don't need to do a mysqldump, but if i don't do it, the upgrade errors out on 10 tables, and then gives me errors about triggers and did you ALWAYS mysql_upgarde -root -p after ANY mysql-update? at least before try a major upgrade? did you try mysqlcheck -h localhost --check-upgrade --all-databases --auto-repair --user=root -p BEFORE the upgrade? did you try it ALSo after the upgrade? sorry, i do not believe that dump/import is needed and idoubt it will not give better results signature.asc Description: OpenPGP digital signature
Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6
going form 5.1 - to 5.5 was easy, I did not have to dump any tabels or triggers, just upgraded binary, ran mysql_upgrade and worked in no time. Thanks everyone for the help! On Wed, Feb 20, 2013 at 2:33 PM, Reindl Harald h.rei...@thelounge.net wrote: fine and much faster and probably safer too :-) a backup with rsync is faster as dump/import and can be done with minimize downtime by use it twice, the first time hot-backup with running server and the second time after stop server to get the diffs doing rsync - stop - rsync - start in a script may reduce the downtime to a few seconds Am 20.02.2013 20:29, schrieb Mike Franon: OK I got it to work. I dumped the tables that it was complaining about first, and then dumped the triggers. I then uninstalled anything to do with mysql, and installed 5.1 Then imported the tables and triggers, and and able to run mysql_upgrade without any errors. This is all without using a full mysqldump. I am now going to go from 5.1 to 5.5 On Wed, Feb 20, 2013 at 12:54 PM, Mike Franon kongfra...@gmail.com wrote: I am pretty sure I did, and when I did I got the following errors: Error: Table Upgrade Required, Please dump/reload to fix it I got that on 10 tables, and also got the following: Warning: Triggers for table ' have no creation context. I think it has to do with no triggers. I know hen I ran the mysql_upgrade it tired to auto repair but did not work and failed. But I will give it a shot again, maybe I missed something. The other thing I was thinking was maybe I can just mysqldump those 10 tables that it fails on, and just restore those instead of my entire db. On Wed, Feb 20, 2013 at 12:43 PM, Reindl Harald h.rei...@thelounge.net wrote: Am 20.02.2013 18:26, schrieb Mike Franon: So I did a full mysqldump over the weekend for a second time and this time it is 220GB, no clue what happened last time, I should have realized looking at the file size something was wrong, but since I got no errors did not think about it, and this time I timed it, took 7 hours to do a complete mysqldump Restoring it is not fun 18+ hours and counting, at this rate it will be a week, there has to be a better way of doing this, and this is only going form 5.0 to 5.1 I know some are saying don't need to do a mysqldump, but if i don't do it, the upgrade errors out on 10 tables, and then gives me errors about triggers and did you ALWAYS mysql_upgarde -root -p after ANY mysql-update? at least before try a major upgrade? did you try mysqlcheck -h localhost --check-upgrade --all-databases --auto-repair --user=root -p BEFORE the upgrade? did you try it ALSo after the upgrade? sorry, i do not believe that dump/import is needed and idoubt it will not give better results -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6
surely * use mysql_upgrade -u root -p after EACH update * upgrade regulary we went from MySQL 3.x to 5.5.30 until know without any dump and here are around 5000 tables Am 19.02.2013 22:12, schrieb Divesh Kamra: Is there any better way for grade MySQL version without taking backup with mysqldump Or if there any tool for this R's DK On 16-Feb-2013, at 16:07, Reindl Harald h.rei...@thelounge.net wrote: Am 16.02.2013 09:42, schrieb Manuel Arostegui: 2013/2/15 Reindl Harald h.rei...@thelounge.net mailto:h.rei...@thelounge.net our database is 400 GB, mysqldump is 600MB was not a typo and you honestly believed that you can import this dump to somewhat? WTF - as admin you should be able to see if the things in front of you are theoretically possible before your start any action and 1:400 is impossible, specially because mysql-dumps are ALWAYS WAY LARGER then the databasses because they contain sql-statements and not only data That's not completely true. If you have a poor maintained database or just tables with lot of writes and deletes and you don't periodically optimize it - you can end up with lot of blank spaces in your tables which will use _a lot_ of space. If you do a du or whatever to measure your database size...you can get really confused. mysqldump obviously doesn't backup blank spaces and once you get rid of them, your database will use much less space. ok, normally i expect there is a admin and doing his job especially for large datasets signature.asc Description: OpenPGP digital signature
Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6
Use replication as your fail over and why not percona's xtrabackup or lvm type backup if you need a backup? Sabika On Feb 19, 2013, at 1:20 PM, Reindl Harald h.rei...@thelounge.net wrote: surely * use mysql_upgrade -u root -p after EACH update * upgrade regulary we went from MySQL 3.x to 5.5.30 until know without any dump and here are around 5000 tables Am 19.02.2013 22:12, schrieb Divesh Kamra: Is there any better way for grade MySQL version without taking backup with mysqldump Or if there any tool for this R's DK On 16-Feb-2013, at 16:07, Reindl Harald h.rei...@thelounge.net wrote: Am 16.02.2013 09:42, schrieb Manuel Arostegui: 2013/2/15 Reindl Harald h.rei...@thelounge.net mailto:h.rei...@thelounge.net our database is 400 GB, mysqldump is 600MB was not a typo and you honestly believed that you can import this dump to somewhat? WTF - as admin you should be able to see if the things in front of you are theoretically possible before your start any action and 1:400 is impossible, specially because mysql-dumps are ALWAYS WAY LARGER then the databasses because they contain sql-statements and not only data That's not completely true. If you have a poor maintained database or just tables with lot of writes and deletes and you don't periodically optimize it - you can end up with lot of blank spaces in your tables which will use _a lot_ of space. If you do a du or whatever to measure your database size...you can get really confused. mysqldump obviously doesn't backup blank spaces and once you get rid of them, your database will use much less space. ok, normally i expect there is a admin and doing his job especially for large datasets -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6
Hi Reindi Thanks for solution . Can u share complete steps ? R's DK On 20-Feb-2013, at 2:50, Reindl Harald h.rei...@thelounge.net wrote: surely * use mysql_upgrade -u root -p after EACH update * upgrade regulary we went from MySQL 3.x to 5.5.30 until know without any dump and here are around 5000 tables Am 19.02.2013 22:12, schrieb Divesh Kamra: Is there any better way for grade MySQL version without taking backup with mysqldump Or if there any tool for this R's DK On 16-Feb-2013, at 16:07, Reindl Harald h.rei...@thelounge.net wrote: Am 16.02.2013 09:42, schrieb Manuel Arostegui: 2013/2/15 Reindl Harald h.rei...@thelounge.net mailto:h.rei...@thelounge.net our database is 400 GB, mysqldump is 600MB was not a typo and you honestly believed that you can import this dump to somewhat? WTF - as admin you should be able to see if the things in front of you are theoretically possible before your start any action and 1:400 is impossible, specially because mysql-dumps are ALWAYS WAY LARGER then the databasses because they contain sql-statements and not only data That's not completely true. If you have a poor maintained database or just tables with lot of writes and deletes and you don't periodically optimize it - you can end up with lot of blank spaces in your tables which will use _a lot_ of space. If you do a du or whatever to measure your database size...you can get really confused. mysqldump obviously doesn't backup blank spaces and once you get rid of them, your database will use much less space. ok, normally i expect there is a admin and doing his job especially for large datasets -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6
Am 19.02.2013 23:53, schrieb Divesh Kamra: Hi Reindi Thanks for solution . Can u share complete steps ? which steps? * update * call mysql_upgrade -u root -p in doubt mysqlcheck -h localhost --check-upgrade --all-databases --auto-repair --user=root -p and if you do mysql_upgrade -u root -p and are always up-to-date that was it, no matter if you move your data from windows to MacOSX and finally to linux or whatever OS On 20-Feb-2013, at 2:50, Reindl Harald h.rei...@thelounge.net wrote: surely * use mysql_upgrade -u root -p after EACH update * upgrade regulary we went from MySQL 3.x to 5.5.30 until know without any dump and here are around 5000 tables Am 19.02.2013 22:12, schrieb Divesh Kamra: Is there any better way for grade MySQL version without taking backup with mysqldump Or if there any tool for this R's DK On 16-Feb-2013, at 16:07, Reindl Harald h.rei...@thelounge.net wrote: Am 16.02.2013 09:42, schrieb Manuel Arostegui: 2013/2/15 Reindl Harald h.rei...@thelounge.net mailto:h.rei...@thelounge.net our database is 400 GB, mysqldump is 600MB was not a typo and you honestly believed that you can import this dump to somewhat? WTF - as admin you should be able to see if the things in front of you are theoretically possible before your start any action and 1:400 is impossible, specially because mysql-dumps are ALWAYS WAY LARGER then the databasses because they contain sql-statements and not only data That's not completely true. If you have a poor maintained database or just tables with lot of writes and deletes and you don't periodically optimize it - you can end up with lot of blank spaces in your tables which will use _a lot_ of space. If you do a du or whatever to measure your database size...you can get really confused. mysqldump obviously doesn't backup blank spaces and once you get rid of them, your database will use much less space. ok, normally i expect there is a admin and doing his job especially for large datasets -- Reindl Harald the lounge interactive design GmbH A-1060 Vienna, Hofmühlgasse 17 CTO / CISO / Software-Development p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40 icq: 154546673, http://www.thelounge.net/ http://www.thelounge.net/signature.asc.what.htm signature.asc Description: OpenPGP digital signature
Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6
Hi all Is there any better way for grade MySQL version without taking backup with mysqldump Or if there any tool for this R's DK On 16-Feb-2013, at 16:07, Reindl Harald h.rei...@thelounge.net wrote: Am 16.02.2013 09:42, schrieb Manuel Arostegui: 2013/2/15 Reindl Harald h.rei...@thelounge.net mailto:h.rei...@thelounge.net our database is 400 GB, mysqldump is 600MB was not a typo and you honestly believed that you can import this dump to somewhat? WTF - as admin you should be able to see if the things in front of you are theoretically possible before your start any action and 1:400 is impossible, specially because mysql-dumps are ALWAYS WAY LARGER then the databasses because they contain sql-statements and not only data That's not completely true. If you have a poor maintained database or just tables with lot of writes and deletes and you don't periodically optimize it - you can end up with lot of blank spaces in your tables which will use _a lot_ of space. If you do a du or whatever to measure your database size...you can get really confused. mysqldump obviously doesn't backup blank spaces and once you get rid of them, your database will use much less space. ok, normally i expect there is a admin and doing his job especially for large datasets -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6
2013/2/15 Reindl Harald h.rei...@thelounge.net our database is 400 GB, mysqldump is 600MB was not a typo and you honestly believed that you can import this dump to somewhat? WTF - as admin you should be able to see if the things in front of you are theoretically possible before your start any action and 1:400 is impossible, specially because mysql-dumps are ALWAYS WAY LARGER then the databasses because they contain sql-statements and not only data That's not completely true. If you have a poor maintained database or just tables with lot of writes and deletes and you don't periodically optimize it - you can end up with lot of blank spaces in your tables which will use _a lot_ of space. If you do a du or whatever to measure your database size...you can get really confused. mysqldump obviously doesn't backup blank spaces and once you get rid of them, your database will use much less space. I have seen this scenario many times and I have seen tables using like 30GB disk space and after an optimize their reported disk size would be just 5-10GB. Manuel.
Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6
Am 16.02.2013 09:42, schrieb Manuel Arostegui: 2013/2/15 Reindl Harald h.rei...@thelounge.net mailto:h.rei...@thelounge.net our database is 400 GB, mysqldump is 600MB was not a typo and you honestly believed that you can import this dump to somewhat? WTF - as admin you should be able to see if the things in front of you are theoretically possible before your start any action and 1:400 is impossible, specially because mysql-dumps are ALWAYS WAY LARGER then the databasses because they contain sql-statements and not only data That's not completely true. If you have a poor maintained database or just tables with lot of writes and deletes and you don't periodically optimize it - you can end up with lot of blank spaces in your tables which will use _a lot_ of space. If you do a du or whatever to measure your database size...you can get really confused. mysqldump obviously doesn't backup blank spaces and once you get rid of them, your database will use much less space. ok, normally i expect there is a admin and doing his job especially for large datasets signature.asc Description: OpenPGP digital signature
Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6
Thanks everyone for suggestions. I am doing this on a test box with a copy of our db before doing this on production db servers. I just upgraded from 5.0 to 5.1, and ran mysql_upgrade and see I have a few tables with the following error: error: Table upgrade required. Please do REPAIR TABLE `tablename` or dump/reload to fix it! I got this on 4 tables so far, but it still checking, my database is huge so might be a while. The question I have what is the best way to fix this? To install all I did was remove all of the 5.0, and then did a yum install 5.1 on my AWS machine. and then just started mysql. Should I instead do a complete mysqldump, and use that instead? On Thu, Feb 14, 2013 at 7:40 PM, Rick James rja...@yahoo-inc.com wrote: Sounds like something that, once discovered, can be fixed in the old version -- then it works correctly in both. That is what happened with a 4.0-5.1 conversion years ago. With 1000 different tables and associated code, we encountered two incompatibilities. One had to do with NULLs, the other with precedence of commajoin vs explicit JOIN. From: Singer Wang [mailto:w...@singerwang.com] Sent: Thursday, February 14, 2013 3:41 PM To: Rick James Cc: Mihail Manolov; Mike Franon; Akshay Suryavanshi; mysql@lists.mysql.com Subject: Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6 Its a very pedantic case, but we had a few instances where it was an issue at my last job. It basically involved multi-table deletes and aliasing.. I quote the change notes for MySQL 5.5.3 Incompatible Change: Several changes were made to alias resolution in multiple-table DELETE statements so that it is no longer possible to have inconsistent or ambiguous table aliases. § In MySQL 5.1.23, alias declarations outside the table_references part of the statement were disallowed for theUSING variant of multiple-table DELETE syntax, to reduce the possibility of ambiguous aliases that could lead to ambiguous statements that have unexpected results such as deleting rows from the wrong table. Now alias declarations outside table_references are disallowed for all multiple-table DELETE statements. Alias declarations are permitted only in the table_references part. Incorrect: DELETE FROM t1 AS a2 USING t1 AS a1 INNER JOIN t2 AS a2; DELETE t1 AS a2 FROM t1 AS a1 INNER JOIN t2 AS a2; Correct: DELETE FROM t1 USING t1 AS a1 INNER JOIN t2 AS a2; DELETE t1 FROM t1 AS a1 INNER JOIN t2 AS a2; § Previously, for alias references in the list of tables from which to delete rows in a multiple-table delete, the default database is used unless one is specified explicitly. For example, if the default database is db1, the following statement does not work because the unqualified alias reference a2 is interpreted as having a database of db1: § § DELETE a1, a2 FROM db1.t1 AS a1 INNER JOIN db2.t2 AS a2 WHERE a1.id=a2.id; To correctly match an alias that refers to a table outside the default database, you must explicitly qualify the reference with the name of the proper database: DELETE a1, db2.a2 FROM db1.t1 AS a1 INNER JOIN db2.t2 AS a2 WHERE a1.id=a2.id; Now alias resolution does not require qualification and alias references should not be qualified with the database name. Qualified names are interpreted as referring to tables, not aliases. Statements containing alias constructs that are no longer permitted must be rewritten. (Bug #27525) On Thu, Feb 14, 2013 at 6:11 PM, Rick James rja...@yahoo-inc.com wrote: Singer, do you have some examples? -Original Message- From: Singer Wang [mailto:w...@singerwang.com] Sent: Thursday, February 14, 2013 2:59 PM To: Mihail Manolov Cc: Mike Franon; Akshay Suryavanshi; mysql@lists.mysql.com Subject: Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6 There are queries that works with 5.1/5.0 that do not work with 5.5, I would test extensively.. S On Thu, Feb 14, 2013 at 5:22 PM, Mihail Manolov mihail.mano...@liquidation.com wrote: You could jump from 5.0 directly to 5.5 and skip 5.1. I have without any issues. There are some configuration file change, which you may want to consider checking. I definitely recommend upgrading your development servers for an extensive testing. Some queries _may_ run slower or not work at all and you may have to rearrange how you join tables in your queries. The upgrade from 5.5 to 5.6 should me smoother, though. On Feb 14, 2013, at 4:28 PM, Mike Franon wrote: Great thanks for the info, I guess the best way to do this is take a spare server, set it up with our standard setup, and then start the upgrade as you said 5.0 - 5.1 - 5.5, test and then upgrade to 5.6 and test. On Thu, Feb 14, 2013 at 4:22 PM, Akshay Suryavanshi akshay.suryavansh...@gmail.com wrote: Mike, 5.6 is GA now, so its stable release. Also you should not jump to 5.6 directly, atleast from 5.0
Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6
While it might be GA I would not recommend that you deploy it for a while. ... at least several point releases. There will be new bugs uncovered as it moves out to a wider audience. Upgrade to 5.5 (through 5.1) first as it is quite proven. Slave 5.6 off it and test. Be patient. Save yourself some heartache. Just my two cents. Keith On Feb 15, 2013 9:27 AM, Mike Franon kongfra...@gmail.com wrote: Thanks everyone for suggestions. I am doing this on a test box with a copy of our db before doing this on production db servers. I just upgraded from 5.0 to 5.1, and ran mysql_upgrade and see I have a few tables with the following error: error: Table upgrade required. Please do REPAIR TABLE `tablename` or dump/reload to fix it! I got this on 4 tables so far, but it still checking, my database is huge so might be a while. The question I have what is the best way to fix this? To install all I did was remove all of the 5.0, and then did a yum install 5.1 on my AWS machine. and then just started mysql. Should I instead do a complete mysqldump, and use that instead? On Thu, Feb 14, 2013 at 7:40 PM, Rick James rja...@yahoo-inc.com wrote: Sounds like something that, once discovered, can be fixed in the old version -- then it works correctly in both. That is what happened with a 4.0-5.1 conversion years ago. With 1000 different tables and associated code, we encountered two incompatibilities. One had to do with NULLs, the other with precedence of commajoin vs explicit JOIN. From: Singer Wang [mailto:w...@singerwang.com] Sent: Thursday, February 14, 2013 3:41 PM To: Rick James Cc: Mihail Manolov; Mike Franon; Akshay Suryavanshi; mysql@lists.mysql.com Subject: Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6 Its a very pedantic case, but we had a few instances where it was an issue at my last job. It basically involved multi-table deletes and aliasing.. I quote the change notes for MySQL 5.5.3 Incompatible Change: Several changes were made to alias resolution in multiple-table DELETE statements so that it is no longer possible to have inconsistent or ambiguous table aliases. § In MySQL 5.1.23, alias declarations outside the table_references part of the statement were disallowed for theUSING variant of multiple-table DELETE syntax, to reduce the possibility of ambiguous aliases that could lead to ambiguous statements that have unexpected results such as deleting rows from the wrong table. Now alias declarations outside table_references are disallowed for all multiple-table DELETE statements. Alias declarations are permitted only in the table_references part. Incorrect: DELETE FROM t1 AS a2 USING t1 AS a1 INNER JOIN t2 AS a2; DELETE t1 AS a2 FROM t1 AS a1 INNER JOIN t2 AS a2; Correct: DELETE FROM t1 USING t1 AS a1 INNER JOIN t2 AS a2; DELETE t1 FROM t1 AS a1 INNER JOIN t2 AS a2; § Previously, for alias references in the list of tables from which to delete rows in a multiple-table delete, the default database is used unless one is specified explicitly. For example, if the default database is db1, the following statement does not work because the unqualified alias reference a2 is interpreted as having a database of db1: § § DELETE a1, a2 FROM db1.t1 AS a1 INNER JOIN db2.t2 AS a2 WHERE a1.id=a2.id; To correctly match an alias that refers to a table outside the default database, you must explicitly qualify the reference with the name of the proper database: DELETE a1, db2.a2 FROM db1.t1 AS a1 INNER JOIN db2.t2 AS a2 WHERE a1.id=a2.id; Now alias resolution does not require qualification and alias references should not be qualified with the database name. Qualified names are interpreted as referring to tables, not aliases. Statements containing alias constructs that are no longer permitted must be rewritten. (Bug #27525) On Thu, Feb 14, 2013 at 6:11 PM, Rick James rja...@yahoo-inc.com wrote: Singer, do you have some examples? -Original Message- From: Singer Wang [mailto:w...@singerwang.com] Sent: Thursday, February 14, 2013 2:59 PM To: Mihail Manolov Cc: Mike Franon; Akshay Suryavanshi; mysql@lists.mysql.com Subject: Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6 There are queries that works with 5.1/5.0 that do not work with 5.5, I would test extensively.. S On Thu, Feb 14, 2013 at 5:22 PM, Mihail Manolov mihail.mano...@liquidation.com wrote: You could jump from 5.0 directly to 5.5 and skip 5.1. I have without any issues. There are some configuration file change, which you may want to consider checking. I definitely recommend upgrading your development servers for an extensive testing. Some queries _may_ run slower or not work at all and you may have to rearrange how you join tables in your queries. The upgrade from 5.5 to 5.6
Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6
I am having a real hard time upgrading just from 5.0.96 to 5.1 I did a full mysqldump and then restore the database, keep in mind our database is 400 GB, mysqldump is 600MB file, about 30 minutes into the restore get this error on one table on an insert: ERROR 1064 (42000) at line 1388: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''2010-04-10 20' at line 1 It weird because If I upgrade 5.1 right over 5.0 without doing a mysqldump, and then do a mysqlcheck it works, except for 5 tables, and triggers, so trying to think of the best way to get to 5.1 On Fri, Feb 15, 2013 at 12:39 PM, Keith Murphy bmur...@paragon-cs.com wrote: While it might be GA I would not recommend that you deploy it for a while. ... at least several point releases. There will be new bugs uncovered as it moves out to a wider audience. Upgrade to 5.5 (through 5.1) first as it is quite proven. Slave 5.6 off it and test. Be patient. Save yourself some heartache. Just my two cents. Keith On Feb 15, 2013 9:27 AM, Mike Franon kongfra...@gmail.com wrote: Thanks everyone for suggestions. I am doing this on a test box with a copy of our db before doing this on production db servers. I just upgraded from 5.0 to 5.1, and ran mysql_upgrade and see I have a few tables with the following error: error: Table upgrade required. Please do REPAIR TABLE `tablename` or dump/reload to fix it! I got this on 4 tables so far, but it still checking, my database is huge so might be a while. The question I have what is the best way to fix this? To install all I did was remove all of the 5.0, and then did a yum install 5.1 on my AWS machine. and then just started mysql. Should I instead do a complete mysqldump, and use that instead? On Thu, Feb 14, 2013 at 7:40 PM, Rick James rja...@yahoo-inc.com wrote: Sounds like something that, once discovered, can be fixed in the old version -- then it works correctly in both. That is what happened with a 4.0-5.1 conversion years ago. With 1000 different tables and associated code, we encountered two incompatibilities. One had to do with NULLs, the other with precedence of commajoin vs explicit JOIN. From: Singer Wang [mailto:w...@singerwang.com] Sent: Thursday, February 14, 2013 3:41 PM To: Rick James Cc: Mihail Manolov; Mike Franon; Akshay Suryavanshi; mysql@lists.mysql.com Subject: Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6 Its a very pedantic case, but we had a few instances where it was an issue at my last job. It basically involved multi-table deletes and aliasing.. I quote the change notes for MySQL 5.5.3 Incompatible Change: Several changes were made to alias resolution in multiple-table DELETE statements so that it is no longer possible to have inconsistent or ambiguous table aliases. § In MySQL 5.1.23, alias declarations outside the table_references part of the statement were disallowed for theUSING variant of multiple-table DELETE syntax, to reduce the possibility of ambiguous aliases that could lead to ambiguous statements that have unexpected results such as deleting rows from the wrong table. Now alias declarations outside table_references are disallowed for all multiple-table DELETE statements. Alias declarations are permitted only in the table_references part. Incorrect: DELETE FROM t1 AS a2 USING t1 AS a1 INNER JOIN t2 AS a2; DELETE t1 AS a2 FROM t1 AS a1 INNER JOIN t2 AS a2; Correct: DELETE FROM t1 USING t1 AS a1 INNER JOIN t2 AS a2; DELETE t1 FROM t1 AS a1 INNER JOIN t2 AS a2; § Previously, for alias references in the list of tables from which to delete rows in a multiple-table delete, the default database is used unless one is specified explicitly. For example, if the default database is db1, the following statement does not work because the unqualified alias reference a2 is interpreted as having a database of db1: § § DELETE a1, a2 FROM db1.t1 AS a1 INNER JOIN db2.t2 AS a2 WHERE a1.id=a2.id; To correctly match an alias that refers to a table outside the default database, you must explicitly qualify the reference with the name of the proper database: DELETE a1, db2.a2 FROM db1.t1 AS a1 INNER JOIN db2.t2 AS a2 WHERE a1.id=a2.id; Now alias resolution does not require qualification and alias references should not be qualified with the database name. Qualified names are interpreted as referring to tables, not aliases. Statements containing alias constructs that are no longer permitted must be rewritten. (Bug #27525) On Thu, Feb 14, 2013 at 6:11 PM, Rick James rja...@yahoo-inc.com wrote: Singer, do you have some examples? -Original Message- From: Singer Wang [mailto:w...@singerwang.com] Sent: Thursday, February 14, 2013 2:59 PM To: Mihail
Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6
Am 15.02.2013 22:55, schrieb Mike Franon: I am having a real hard time upgrading just from 5.0.96 to 5.1 I did a full mysqldump and then restore the database, keep in mind our database is 400 GB, mysqldump is 600MB file, about 30 minutes into the restore get this error on one table on an insert: ERROR 1064 (42000) at line 1388: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''2010-04-10 20' at line 1 It weird because If I upgrade 5.1 right over 5.0 without doing a mysqldump, and then do a mysqlcheck it works, except for 5 tables, and triggers, so trying to think of the best way to get to 5.1 i have never in my life used a dumpfile and i am coming from mysql 3.3 while all machines was migrated to 5.5 with all steps between and around 50 mysql-instances coming from the same clones originally installed on Windows, later moved to MacOSX and since 2008 running on fedora Linux i have even done downgrades from MySQL 6.0 alpha years ago to 5.0 without any dump and problems except verify and change the scheme of the mysqld database (users and permsissions) did you run mysql_upgrade after EACH update of your server and if not why? P.S.: nobofy which a working brain would NOW upgrade to the first MySQL 5.6 release in production signature.asc Description: OpenPGP digital signature
Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6
Something doesn't add up. If the data set is 400 GB then your dump has to bigger than 600 mb. That is better than a 400:1 ratio. Maybe the dump isn't working correctly or your data set is much smaller? If the dump output is less than a gig I would just edit it with something like vi and look at the offending line. Keith On Feb 15, 2013 3:55 PM, Mike Franon kongfra...@gmail.com wrote: I am having a real hard time upgrading just from 5.0.96 to 5.1 I did a full mysqldump and then restore the database, keep in mind our database is 400 GB, mysqldump is 600MB file, about 30 minutes into the restore get this error on one table on an insert: ERROR 1064 (42000) at line 1388: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''2010-04-10 20' at line 1 It weird because If I upgrade 5.1 right over 5.0 without doing a mysqldump, and then do a mysqlcheck it works, except for 5 tables, and triggers, so trying to think of the best way to get to 5.1 On Fri, Feb 15, 2013 at 12:39 PM, Keith Murphy bmur...@paragon-cs.com wrote: While it might be GA I would not recommend that you deploy it for a while. ... at least several point releases. There will be new bugs uncovered as it moves out to a wider audience. Upgrade to 5.5 (through 5.1) first as it is quite proven. Slave 5.6 off it and test. Be patient. Save yourself some heartache. Just my two cents. Keith On Feb 15, 2013 9:27 AM, Mike Franon kongfra...@gmail.com wrote: Thanks everyone for suggestions. I am doing this on a test box with a copy of our db before doing this on production db servers. I just upgraded from 5.0 to 5.1, and ran mysql_upgrade and see I have a few tables with the following error: error: Table upgrade required. Please do REPAIR TABLE `tablename` or dump/reload to fix it! I got this on 4 tables so far, but it still checking, my database is huge so might be a while. The question I have what is the best way to fix this? To install all I did was remove all of the 5.0, and then did a yum install 5.1 on my AWS machine. and then just started mysql. Should I instead do a complete mysqldump, and use that instead? On Thu, Feb 14, 2013 at 7:40 PM, Rick James rja...@yahoo-inc.com wrote: Sounds like something that, once discovered, can be fixed in the old version -- then it works correctly in both. That is what happened with a 4.0-5.1 conversion years ago. With 1000 different tables and associated code, we encountered two incompatibilities. One had to do with NULLs, the other with precedence of commajoin vs explicit JOIN. From: Singer Wang [mailto:w...@singerwang.com] Sent: Thursday, February 14, 2013 3:41 PM To: Rick James Cc: Mihail Manolov; Mike Franon; Akshay Suryavanshi; mysql@lists.mysql.com Subject: Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6 Its a very pedantic case, but we had a few instances where it was an issue at my last job. It basically involved multi-table deletes and aliasing.. I quote the change notes for MySQL 5.5.3 Incompatible Change: Several changes were made to alias resolution in multiple-table DELETE statements so that it is no longer possible to have inconsistent or ambiguous table aliases. § In MySQL 5.1.23, alias declarations outside the table_references part of the statement were disallowed for theUSING variant of multiple-table DELETE syntax, to reduce the possibility of ambiguous aliases that could lead to ambiguous statements that have unexpected results such as deleting rows from the wrong table. Now alias declarations outside table_references are disallowed for all multiple-table DELETE statements. Alias declarations are permitted only in the table_references part. Incorrect: DELETE FROM t1 AS a2 USING t1 AS a1 INNER JOIN t2 AS a2; DELETE t1 AS a2 FROM t1 AS a1 INNER JOIN t2 AS a2; Correct: DELETE FROM t1 USING t1 AS a1 INNER JOIN t2 AS a2; DELETE t1 FROM t1 AS a1 INNER JOIN t2 AS a2; § Previously, for alias references in the list of tables from which to delete rows in a multiple-table delete, the default database is used unless one is specified explicitly. For example, if the default database is db1, the following statement does not work because the unqualified alias reference a2 is interpreted as having a database of db1: § § DELETE a1, a2 FROM db1.t1 AS a1 INNER JOIN db2.t2 AS a2 WHERE a1.id=a2.id; To correctly match an alias that refers to a table outside the default database, you must explicitly qualify the reference with the name of the proper database: DELETE a1, db2.a2 FROM db1.t1 AS a1 INNER JOIN db2.t2 AS a2 WHERE a1.id=a2.id; Now alias resolution does
Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6
Your right I am going to run another mysqldump, maybe something happened and pick this up next week.. Thanks all. On Fri, Feb 15, 2013 at 5:03 PM, Keith Murphy bmur...@paragon-cs.com wrote: Something doesn't add up. If the data set is 400 GB then your dump has to bigger than 600 mb. That is better than a 400:1 ratio. Maybe the dump isn't working correctly or your data set is much smaller? If the dump output is less than a gig I would just edit it with something like vi and look at the offending line. Keith On Feb 15, 2013 3:55 PM, Mike Franon kongfra...@gmail.com wrote: I am having a real hard time upgrading just from 5.0.96 to 5.1 I did a full mysqldump and then restore the database, keep in mind our database is 400 GB, mysqldump is 600MB file, about 30 minutes into the restore get this error on one table on an insert: ERROR 1064 (42000) at line 1388: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''2010-04-10 20' at line 1 It weird because If I upgrade 5.1 right over 5.0 without doing a mysqldump, and then do a mysqlcheck it works, except for 5 tables, and triggers, so trying to think of the best way to get to 5.1 On Fri, Feb 15, 2013 at 12:39 PM, Keith Murphy bmur...@paragon-cs.com wrote: While it might be GA I would not recommend that you deploy it for a while. ... at least several point releases. There will be new bugs uncovered as it moves out to a wider audience. Upgrade to 5.5 (through 5.1) first as it is quite proven. Slave 5.6 off it and test. Be patient. Save yourself some heartache. Just my two cents. Keith On Feb 15, 2013 9:27 AM, Mike Franon kongfra...@gmail.com wrote: Thanks everyone for suggestions. I am doing this on a test box with a copy of our db before doing this on production db servers. I just upgraded from 5.0 to 5.1, and ran mysql_upgrade and see I have a few tables with the following error: error: Table upgrade required. Please do REPAIR TABLE `tablename` or dump/reload to fix it! I got this on 4 tables so far, but it still checking, my database is huge so might be a while. The question I have what is the best way to fix this? To install all I did was remove all of the 5.0, and then did a yum install 5.1 on my AWS machine. and then just started mysql. Should I instead do a complete mysqldump, and use that instead? On Thu, Feb 14, 2013 at 7:40 PM, Rick James rja...@yahoo-inc.com wrote: Sounds like something that, once discovered, can be fixed in the old version -- then it works correctly in both. That is what happened with a 4.0-5.1 conversion years ago. With 1000 different tables and associated code, we encountered two incompatibilities. One had to do with NULLs, the other with precedence of commajoin vs explicit JOIN. From: Singer Wang [mailto:w...@singerwang.com] Sent: Thursday, February 14, 2013 3:41 PM To: Rick James Cc: Mihail Manolov; Mike Franon; Akshay Suryavanshi; mysql@lists.mysql.com Subject: Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6 Its a very pedantic case, but we had a few instances where it was an issue at my last job. It basically involved multi-table deletes and aliasing.. I quote the change notes for MySQL 5.5.3 Incompatible Change: Several changes were made to alias resolution in multiple-table DELETE statements so that it is no longer possible to have inconsistent or ambiguous table aliases. § In MySQL 5.1.23, alias declarations outside the table_references part of the statement were disallowed for theUSING variant of multiple-table DELETE syntax, to reduce the possibility of ambiguous aliases that could lead to ambiguous statements that have unexpected results such as deleting rows from the wrong table. Now alias declarations outside table_references are disallowed for all multiple-table DELETE statements. Alias declarations are permitted only in the table_references part. Incorrect: DELETE FROM t1 AS a2 USING t1 AS a1 INNER JOIN t2 AS a2; DELETE t1 AS a2 FROM t1 AS a1 INNER JOIN t2 AS a2; Correct: DELETE FROM t1 USING t1 AS a1 INNER JOIN t2 AS a2; DELETE t1 FROM t1 AS a1 INNER JOIN t2 AS a2; § Previously, for alias references in the list of tables from which to delete rows in a multiple-table delete, the default database is used unless one is specified explicitly. For example, if the default database is db1, the following statement does not work because the unqualified alias reference a2 is interpreted as having a database of db1: § § DELETE a1, a2 FROM db1.t1 AS a1 INNER JOIN db2.t2 AS a2 WHERE a1.id=a2.id; To correctly match an alias that refers to a table outside the default
Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6
our database is 400 GB, mysqldump is 600MB was not a typo and you honestly believed that you can import this dump to somewhat? WTF - as admin you should be able to see if the things in front of you are theoretically possible before your start any action and 1:400 is impossible, specially because mysql-dumps are ALWAYS WAY LARGER then the databasses because they contain sql-statements and not only data Am 15.02.2013 23:37, schrieb Mike Franon: Your right I am going to run another mysqldump, maybe something happened and pick this up next week.. Thanks all. On Fri, Feb 15, 2013 at 5:03 PM, Keith Murphy bmur...@paragon-cs.com wrote: Something doesn't add up. If the data set is 400 GB then your dump has to bigger than 600 mb. That is better than a 400:1 ratio. Maybe the dump isn't working correctly or your data set is much smaller? If the dump output is less than a gig I would just edit it with something like vi and look at the offending line. Keith On Feb 15, 2013 3:55 PM, Mike Franon kongfra...@gmail.com wrote: I am having a real hard time upgrading just from 5.0.96 to 5.1 I did a full mysqldump and then restore the database, keep in mind our database is 400 GB, mysqldump is 600MB file, about 30 minutes into the restore get this error on one table on an insert: ERROR 1064 (42000) at line 1388: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''2010-04-10 20' at line 1 It weird because If I upgrade 5.1 right over 5.0 without doing a mysqldump, and then do a mysqlcheck it works, except for 5 tables, and triggers, so trying to think of the best way to get to 5.1 On Fri, Feb 15, 2013 at 12:39 PM, Keith Murphy bmur...@paragon-cs.com wrote: While it might be GA I would not recommend that you deploy it for a while. ... at least several point releases. There will be new bugs uncovered as it moves out to a wider audience. Upgrade to 5.5 (through 5.1) first as it is quite proven. Slave 5.6 off it and test. Be patient. Save yourself some heartache. Just my two cents. Keith On Feb 15, 2013 9:27 AM, Mike Franon kongfra...@gmail.com wrote: Thanks everyone for suggestions. I am doing this on a test box with a copy of our db before doing this on production db servers. I just upgraded from 5.0 to 5.1, and ran mysql_upgrade and see I have a few tables with the following error: error: Table upgrade required. Please do REPAIR TABLE `tablename` or dump/reload to fix it! I got this on 4 tables so far, but it still checking, my database is huge so might be a while. The question I have what is the best way to fix this? To install all I did was remove all of the 5.0, and then did a yum install 5.1 on my AWS machine. and then just started mysql. Should I instead do a complete mysqldump, and use that instead? On Thu, Feb 14, 2013 at 7:40 PM, Rick James rja...@yahoo-inc.com wrote: Sounds like something that, once discovered, can be fixed in the old version -- then it works correctly in both. That is what happened with a 4.0-5.1 conversion years ago. With 1000 different tables and associated code, we encountered two incompatibilities. One had to do with NULLs, the other with precedence of commajoin vs explicit JOIN. From: Singer Wang [mailto:w...@singerwang.com] Sent: Thursday, February 14, 2013 3:41 PM To: Rick James Cc: Mihail Manolov; Mike Franon; Akshay Suryavanshi; mysql@lists.mysql.com Subject: Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6 Its a very pedantic case, but we had a few instances where it was an issue at my last job. It basically involved multi-table deletes and aliasing.. I quote the change notes for MySQL 5.5.3 Incompatible Change: Several changes were made to alias resolution in multiple-table DELETE statements so that it is no longer possible to have inconsistent or ambiguous table aliases. § In MySQL 5.1.23, alias declarations outside the table_references part of the statement were disallowed for theUSING variant of multiple-table DELETE syntax, to reduce the possibility of ambiguous aliases that could lead to ambiguous statements that have unexpected results such as deleting rows from the wrong table. Now alias declarations outside table_references are disallowed for all multiple-table DELETE statements. Alias declarations are permitted only in the table_references part. Incorrect: DELETE FROM t1 AS a2 USING t1 AS a1 INNER JOIN t2 AS a2; DELETE t1 AS a2 FROM t1 AS a1 INNER JOIN t2 AS a2; Correct: DELETE FROM t1 USING t1 AS a1 INNER JOIN t2 AS a2; DELETE t1 FROM t1 AS a1 INNER JOIN t2 AS a2; § Previously, for alias references in the list of tables from which to delete rows in a multiple-table delete, the default database is used unless one is specified explicitly. For example, if the default database is db1, the following statement does not work because
Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6
I can't even imagine an SQL dump of a 400GB database would restore anyway. How long would that take? 3 weeks? Might want to dump the data to CSV files and the schema to an SQL file if you want a full dump/restore. On Fri, Feb 15, 2013 at 4:54 PM, Reindl Harald h.rei...@thelounge.netwrote: our database is 400 GB, mysqldump is 600MB was not a typo and you honestly believed that you can import this dump to somewhat? WTF - as admin you should be able to see if the things in front of you are theoretically possible before your start any action and 1:400 is impossible, specially because mysql-dumps are ALWAYS WAY LARGER then the databasses because they contain sql-statements and not only data Am 15.02.2013 23:37, schrieb Mike Franon: Your right I am going to run another mysqldump, maybe something happened and pick this up next week.. Thanks all. On Fri, Feb 15, 2013 at 5:03 PM, Keith Murphy bmur...@paragon-cs.com wrote: Something doesn't add up. If the data set is 400 GB then your dump has to bigger than 600 mb. That is better than a 400:1 ratio. Maybe the dump isn't working correctly or your data set is much smaller? If the dump output is less than a gig I would just edit it with something like vi and look at the offending line. Keith On Feb 15, 2013 3:55 PM, Mike Franon kongfra...@gmail.com wrote: I am having a real hard time upgrading just from 5.0.96 to 5.1 I did a full mysqldump and then restore the database, keep in mind our database is 400 GB, mysqldump is 600MB file, about 30 minutes into the restore get this error on one table on an insert: ERROR 1064 (42000) at line 1388: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''2010-04-10 20' at line 1 It weird because If I upgrade 5.1 right over 5.0 without doing a mysqldump, and then do a mysqlcheck it works, except for 5 tables, and triggers, so trying to think of the best way to get to 5.1 On Fri, Feb 15, 2013 at 12:39 PM, Keith Murphy bmur...@paragon-cs.com wrote: While it might be GA I would not recommend that you deploy it for a while. ... at least several point releases. There will be new bugs uncovered as it moves out to a wider audience. Upgrade to 5.5 (through 5.1) first as it is quite proven. Slave 5.6 off it and test. Be patient. Save yourself some heartache. Just my two cents. Keith On Feb 15, 2013 9:27 AM, Mike Franon kongfra...@gmail.com wrote: Thanks everyone for suggestions. I am doing this on a test box with a copy of our db before doing this on production db servers. I just upgraded from 5.0 to 5.1, and ran mysql_upgrade and see I have a few tables with the following error: error: Table upgrade required. Please do REPAIR TABLE `tablename` or dump/reload to fix it! I got this on 4 tables so far, but it still checking, my database is huge so might be a while. The question I have what is the best way to fix this? To install all I did was remove all of the 5.0, and then did a yum install 5.1 on my AWS machine. and then just started mysql. Should I instead do a complete mysqldump, and use that instead? On Thu, Feb 14, 2013 at 7:40 PM, Rick James rja...@yahoo-inc.com wrote: Sounds like something that, once discovered, can be fixed in the old version -- then it works correctly in both. That is what happened with a 4.0-5.1 conversion years ago. With 1000 different tables and associated code, we encountered two incompatibilities. One had to do with NULLs, the other with precedence of commajoin vs explicit JOIN. From: Singer Wang [mailto:w...@singerwang.com] Sent: Thursday, February 14, 2013 3:41 PM To: Rick James Cc: Mihail Manolov; Mike Franon; Akshay Suryavanshi; mysql@lists.mysql.com Subject: Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6 Its a very pedantic case, but we had a few instances where it was an issue at my last job. It basically involved multi-table deletes and aliasing.. I quote the change notes for MySQL 5.5.3 Incompatible Change: Several changes were made to alias resolution in multiple-table DELETE statements so that it is no longer possible to have inconsistent or ambiguous table aliases. § In MySQL 5.1.23, alias declarations outside the table_references part of the statement were disallowed for theUSING variant of multiple-table DELETE syntax, to reduce the possibility of ambiguous aliases that could lead to ambiguous statements that have unexpected results such as deleting rows from the wrong table. Now alias declarations outside table_references are disallowed for all multiple-table DELETE statements. Alias declarations are permitted only in the table_references part. Incorrect: DELETE FROM t1 AS a2 USING t1 AS a1 INNER JOIN t2 AS a2
Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6
well, that is why i never in my life will dump and import large databases - never, for no money on the world backups of whole servers are done with replication and restored with rsync if needed but why in the world would someone export large datasets with dependencies to a PLAIN TEXTFILE and pray this is becoming a consistent database on any target? bseides the fact it takes years to import huge data from dumps - how do you make sure they are 100% clean after that Am 15.02.2013 23:59, schrieb Johnny Withers: I can't even imagine an SQL dump of a 400GB database would restore anyway. How long would that take? 3 weeks? Might want to dump the data to CSV files and the schema to an SQL file if you want a full dump/restore. On Fri, Feb 15, 2013 at 4:54 PM, Reindl Harald h.rei...@thelounge.netwrote: our database is 400 GB, mysqldump is 600MB was not a typo and you honestly believed that you can import this dump to somewhat? WTF - as admin you should be able to see if the things in front of you are theoretically possible before your start any action and 1:400 is impossible, specially because mysql-dumps are ALWAYS WAY LARGER then the databasses because they contain sql-statements and not only data Am 15.02.2013 23:37, schrieb Mike Franon: Your right I am going to run another mysqldump, maybe something happened and pick this up next week.. Thanks all. On Fri, Feb 15, 2013 at 5:03 PM, Keith Murphy bmur...@paragon-cs.com wrote: Something doesn't add up. If the data set is 400 GB then your dump has to bigger than 600 mb. That is better than a 400:1 ratio. Maybe the dump isn't working correctly or your data set is much smaller? If the dump output is less than a gig I would just edit it with something like vi and look at the offending line. Keith On Feb 15, 2013 3:55 PM, Mike Franon kongfra...@gmail.com wrote: I am having a real hard time upgrading just from 5.0.96 to 5.1 I did a full mysqldump and then restore the database, keep in mind our database is 400 GB, mysqldump is 600MB file, about 30 minutes into the restore get this error on one table on an insert: ERROR 1064 (42000) at line 1388: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''2010-04-10 20' at line 1 It weird because If I upgrade 5.1 right over 5.0 without doing a mysqldump, and then do a mysqlcheck it works, except for 5 tables, and triggers, so trying to think of the best way to get to 5.1 On Fri, Feb 15, 2013 at 12:39 PM, Keith Murphy bmur...@paragon-cs.com wrote: While it might be GA I would not recommend that you deploy it for a while. ... at least several point releases. There will be new bugs uncovered as it moves out to a wider audience. Upgrade to 5.5 (through 5.1) first as it is quite proven. Slave 5.6 off it and test. Be patient. Save yourself some heartache. Just my two cents. Keith On Feb 15, 2013 9:27 AM, Mike Franon kongfra...@gmail.com wrote: Thanks everyone for suggestions. I am doing this on a test box with a copy of our db before doing this on production db servers. I just upgraded from 5.0 to 5.1, and ran mysql_upgrade and see I have a few tables with the following error: error: Table upgrade required. Please do REPAIR TABLE `tablename` or dump/reload to fix it! I got this on 4 tables so far, but it still checking, my database is huge so might be a while. The question I have what is the best way to fix this? To install all I did was remove all of the 5.0, and then did a yum install 5.1 on my AWS machine. and then just started mysql. Should I instead do a complete mysqldump, and use that instead? On Thu, Feb 14, 2013 at 7:40 PM, Rick James rja...@yahoo-inc.com wrote: Sounds like something that, once discovered, can be fixed in the old version -- then it works correctly in both. That is what happened with a 4.0-5.1 conversion years ago. With 1000 different tables and associated code, we encountered two incompatibilities. One had to do with NULLs, the other with precedence of commajoin vs explicit JOIN. From: Singer Wang [mailto:w...@singerwang.com] Sent: Thursday, February 14, 2013 3:41 PM To: Rick James Cc: Mihail Manolov; Mike Franon; Akshay Suryavanshi; mysql@lists.mysql.com Subject: Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6 Its a very pedantic case, but we had a few instances where it was an issue at my last job. It basically involved multi-table deletes and aliasing.. I quote the change notes for MySQL 5.5.3 Incompatible Change: Several changes were made to alias resolution in multiple-table DELETE statements so that it is no longer possible to have inconsistent or ambiguous table aliases. § In MySQL 5.1.23, alias declarations outside the table_references part of the statement were disallowed for theUSING variant of multiple-table DELETE syntax, to reduce the possibility of ambiguous
Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6
Harald, I somewhat dont agree with your statement of mysqldump backup size being way bigger than the actual datasets, just beacuse its SQL plain text. What I can tell you is, mysqldump files would be significantly smaller than the total dataset size, because it doesnt contain index data. So, if out of 400G, 100G is index data then the dump file should be 300G. I hope you agree... Cheers! On Sat, Feb 16, 2013 at 4:24 AM, Reindl Harald h.rei...@thelounge.netwrote: our database is 400 GB, mysqldump is 600MB was not a typo and you honestly believed that you can import this dump to somewhat? WTF - as admin you should be able to see if the things in front of you are theoretically possible before your start any action and 1:400 is impossible, specially because mysql-dumps are ALWAYS WAY LARGER then the databasses because they contain sql-statements and not only data Am 15.02.2013 23:37, schrieb Mike Franon: Your right I am going to run another mysqldump, maybe something happened and pick this up next week.. Thanks all. On Fri, Feb 15, 2013 at 5:03 PM, Keith Murphy bmur...@paragon-cs.com wrote: Something doesn't add up. If the data set is 400 GB then your dump has to bigger than 600 mb. That is better than a 400:1 ratio. Maybe the dump isn't working correctly or your data set is much smaller? If the dump output is less than a gig I would just edit it with something like vi and look at the offending line. Keith On Feb 15, 2013 3:55 PM, Mike Franon kongfra...@gmail.com wrote: I am having a real hard time upgrading just from 5.0.96 to 5.1 I did a full mysqldump and then restore the database, keep in mind our database is 400 GB, mysqldump is 600MB file, about 30 minutes into the restore get this error on one table on an insert: ERROR 1064 (42000) at line 1388: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''2010-04-10 20' at line 1 It weird because If I upgrade 5.1 right over 5.0 without doing a mysqldump, and then do a mysqlcheck it works, except for 5 tables, and triggers, so trying to think of the best way to get to 5.1 On Fri, Feb 15, 2013 at 12:39 PM, Keith Murphy bmur...@paragon-cs.com wrote: While it might be GA I would not recommend that you deploy it for a while. ... at least several point releases. There will be new bugs uncovered as it moves out to a wider audience. Upgrade to 5.5 (through 5.1) first as it is quite proven. Slave 5.6 off it and test. Be patient. Save yourself some heartache. Just my two cents. Keith On Feb 15, 2013 9:27 AM, Mike Franon kongfra...@gmail.com wrote: Thanks everyone for suggestions. I am doing this on a test box with a copy of our db before doing this on production db servers. I just upgraded from 5.0 to 5.1, and ran mysql_upgrade and see I have a few tables with the following error: error: Table upgrade required. Please do REPAIR TABLE `tablename` or dump/reload to fix it! I got this on 4 tables so far, but it still checking, my database is huge so might be a while. The question I have what is the best way to fix this? To install all I did was remove all of the 5.0, and then did a yum install 5.1 on my AWS machine. and then just started mysql. Should I instead do a complete mysqldump, and use that instead? On Thu, Feb 14, 2013 at 7:40 PM, Rick James rja...@yahoo-inc.com wrote: Sounds like something that, once discovered, can be fixed in the old version -- then it works correctly in both. That is what happened with a 4.0-5.1 conversion years ago. With 1000 different tables and associated code, we encountered two incompatibilities. One had to do with NULLs, the other with precedence of commajoin vs explicit JOIN. From: Singer Wang [mailto:w...@singerwang.com] Sent: Thursday, February 14, 2013 3:41 PM To: Rick James Cc: Mihail Manolov; Mike Franon; Akshay Suryavanshi; mysql@lists.mysql.com Subject: Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6 Its a very pedantic case, but we had a few instances where it was an issue at my last job. It basically involved multi-table deletes and aliasing.. I quote the change notes for MySQL 5.5.3 Incompatible Change: Several changes were made to alias resolution in multiple-table DELETE statements so that it is no longer possible to have inconsistent or ambiguous table aliases. § In MySQL 5.1.23, alias declarations outside the table_references part of the statement were disallowed for theUSING variant of multiple-table DELETE syntax, to reduce the possibility of ambiguous aliases that could lead to ambiguous statements that have unexpected results such as deleting rows from the wrong table. Now alias declarations outside table_references are disallowed for all multiple-table
Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6
not really * it is unlikely that you have 1:4 relations key/data * you have sql-statement overhead even for tinyint 1 * you have overhead to escape data Am 16.02.2013 00:55, schrieb Akshay Suryavanshi: Harald, I somewhat dont agree with your statement of mysqldump backup size being way bigger than the actual datasets, just beacuse its SQL plain text. What I can tell you is, mysqldump files would be significantly smaller than the total dataset size, because it doesnt contain index data. So, if out of 400G, 100G is index data then the dump file should be 300G. On Sat, Feb 16, 2013 at 4:24 AM, Reindl Harald h.rei...@thelounge.net mailto:h.rei...@thelounge.net wrote: our database is 400 GB, mysqldump is 600MB was not a typo and you honestly believed that you can import this dump to somewhat? WTF - as admin you should be able to see if the things in front of you are theoretically possible before your start any action and 1:400 is impossible, specially because mysql-dumps are ALWAYS WAY LARGER then the databasses because they contain sql-statements and not only data Am 15.02.2013 23:37, schrieb Mike Franon: Your right I am going to run another mysqldump, maybe something happened and pick this up next week.. Thanks all. On Fri, Feb 15, 2013 at 5:03 PM, Keith Murphy bmur...@paragon-cs.com mailto:bmur...@paragon-cs.com wrote: Something doesn't add up. If the data set is 400 GB then your dump has to bigger than 600 mb. That is better than a 400:1 ratio. Maybe the dump isn't working correctly or your data set is much smaller? If the dump output is less than a gig I would just edit it with something like vi and look at the offending line. Keith On Feb 15, 2013 3:55 PM, Mike Franon kongfra...@gmail.com mailto:kongfra...@gmail.com wrote: I am having a real hard time upgrading just from 5.0.96 to 5.1 I did a full mysqldump and then restore the database, keep in mind our database is 400 GB, mysqldump is 600MB file, about 30 minutes into the restore get this error on one table on an insert: ERROR 1064 (42000) at line 1388: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''2010-04-10 20' at line 1 It weird because If I upgrade 5.1 right over 5.0 without doing a mysqldump, and then do a mysqlcheck it works, except for 5 tables, and triggers, so trying to think of the best way to get to 5.1 On Fri, Feb 15, 2013 at 12:39 PM, Keith Murphy bmur...@paragon-cs.com mailto:bmur...@paragon-cs.com wrote: While it might be GA I would not recommend that you deploy it for a while. ... at least several point releases. There will be new bugs uncovered as it moves out to a wider audience. Upgrade to 5.5 (through 5.1) first as it is quite proven. Slave 5.6 off it and test. Be patient. Save yourself some heartache. Just my two cents. Keith On Feb 15, 2013 9:27 AM, Mike Franon kongfra...@gmail.com mailto:kongfra...@gmail.com wrote: Thanks everyone for suggestions. I am doing this on a test box with a copy of our db before doing this on production db servers. I just upgraded from 5.0 to 5.1, and ran mysql_upgrade and see I have a few tables with the following error: error: Table upgrade required. Please do REPAIR TABLE `tablename` or dump/reload to fix it! I got this on 4 tables so far, but it still checking, my database is huge so might be a while. The question I have what is the best way to fix this? To install all I did was remove all of the 5.0, and then did a yum install 5.1 on my AWS machine. and then just started mysql. Should I instead do a complete mysqldump, and use that instead? On Thu, Feb 14, 2013 at 7:40 PM, Rick James rja...@yahoo-inc.com mailto:rja...@yahoo-inc.com wrote: Sounds like something that, once discovered, can be fixed in the old version -- then it works correctly in both. That is what happened with a 4.0-5.1 conversion years ago. With 1000 different tables and associated code, we encountered two incompatibilities. One had to do with NULLs, the other with precedence of commajoin vs explicit JOIN. From: Singer Wang [mailto:w...@singerwang.com mailto:w...@singerwang.com] Sent: Thursday, February 14, 2013 3:41 PM To: Rick James Cc: Mihail Manolov; Mike Franon; Akshay Suryavanshi; mysql@lists.mysql.com mailto:mysql@lists.mysql.com Subject: Re: Upgrading form mysql 5.0.90 to 5.5
Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6
2013/2/14 Mike Franon kongfra...@gmail.com Great thanks for the info, I guess the best way to do this is take a spare server, set it up with our standard setup, and then start the upgrade as you said 5.0 - 5.1 - 5.5, test and then upgrade to 5.6 and test. Do not forget to leave that spare server running for several days before upgrading the rest of machines to 5.6. If possible, I would do some stress tests or benchmarking to make sure it performs as you expect. Manuel.
Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6
Great thanks for the info, I guess the best way to do this is take a spare server, set it up with our standard setup, and then start the upgrade as you said 5.0 - 5.1 - 5.5, test and then upgrade to 5.6 and test. On Thu, Feb 14, 2013 at 4:22 PM, Akshay Suryavanshi akshay.suryavansh...@gmail.com wrote: Mike, 5.6 is GA now, so its stable release. Also you should not jump to 5.6 directly, atleast from 5.0. There are many bug fixes and changes in 5.1, so you should consider this way. 5.0--5.1--5.5 (all slaves first, and then the master) And further 5.5 -- 5.6 (again all slaves first and then the master) Hope this helps. Cheers! On Fri, Feb 15, 2013 at 2:38 AM, Mike Franon kongfra...@gmail.com wrote: I have 1 master with many slaves, using the master only for inserts and the rest are readers. Is 5.6 stable? Or better off to go to 5.5? If so do I need to make a few steps or can go straight from 5.0 to 5.6? Any best practices and recommendations? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6
Mike, 5.6 is GA now, so its stable release. Also you should not jump to 5.6 directly, atleast from 5.0. There are many bug fixes and changes in 5.1, so you should consider this way. 5.0--5.1--5.5 (all slaves first, and then the master) And further 5.5 -- 5.6 (again all slaves first and then the master) Hope this helps. Cheers! On Fri, Feb 15, 2013 at 2:38 AM, Mike Franon kongfra...@gmail.com wrote: I have 1 master with many slaves, using the master only for inserts and the rest are readers. Is 5.6 stable? Or better off to go to 5.5? If so do I need to make a few steps or can go straight from 5.0 to 5.6? Any best practices and recommendations? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: Upgrading form mysql 5.0.90 to 5.5 or 5.6
Ditto. I would mysqldump 5.0, load it onto a 5.5 (or 5.6) box that you have as a slave of the 5.0 master. The load may uncover some issues. Testing reads may uncover issues. The replication stream will test the writes; it may uncover issues. After being comfortable with that, build new slaves off the 5.5/5.6 box. Then cutover writes to that box. And jettison the 5.0 boxes. 5.5 - 5.6 may have more changes/improvements that all of 5.0-5.1-5.5. (Or, at least, Oracle salesmen would like you to believe it.) There is clearly a lot new optimizations in 5.6. So should you go all the way to 5.6? Maybe. You need to do a lot of shakedown anyway. -Original Message- From: Mihail Manolov [mailto:mihail.mano...@liquidation.com] Sent: Thursday, February 14, 2013 2:22 PM To: Mike Franon Cc: Akshay Suryavanshi; mysql@lists.mysql.com Subject: Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6 You could jump from 5.0 directly to 5.5 and skip 5.1. I have without any issues. There are some configuration file change, which you may want to consider checking. I definitely recommend upgrading your development servers for an extensive testing. Some queries _may_ run slower or not work at all and you may have to rearrange how you join tables in your queries. The upgrade from 5.5 to 5.6 should me smoother, though. On Feb 14, 2013, at 4:28 PM, Mike Franon wrote: Great thanks for the info, I guess the best way to do this is take a spare server, set it up with our standard setup, and then start the upgrade as you said 5.0 - 5.1 - 5.5, test and then upgrade to 5.6 and test. On Thu, Feb 14, 2013 at 4:22 PM, Akshay Suryavanshi akshay.suryavansh...@gmail.com wrote: Mike, 5.6 is GA now, so its stable release. Also you should not jump to 5.6 directly, atleast from 5.0. There are many bug fixes and changes in 5.1, so you should consider this way. 5.0--5.1--5.5 (all slaves first, and then the master) And further 5.5 -- 5.6 (again all slaves first and then the master) Hope this helps. Cheers! On Fri, Feb 15, 2013 at 2:38 AM, Mike Franon kongfra...@gmail.com wrote: I have 1 master with many slaves, using the master only for inserts and the rest are readers. Is 5.6 stable? Or better off to go to 5.5? If so do I need to make a few steps or can go straight from 5.0 to 5.6? Any best practices and recommendations? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: Upgrading form mysql 5.0.90 to 5.5 or 5.6
Singer, do you have some examples? -Original Message- From: Singer Wang [mailto:w...@singerwang.com] Sent: Thursday, February 14, 2013 2:59 PM To: Mihail Manolov Cc: Mike Franon; Akshay Suryavanshi; mysql@lists.mysql.com Subject: Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6 There are queries that works with 5.1/5.0 that do not work with 5.5, I would test extensively.. S On Thu, Feb 14, 2013 at 5:22 PM, Mihail Manolov mihail.mano...@liquidation.com wrote: You could jump from 5.0 directly to 5.5 and skip 5.1. I have without any issues. There are some configuration file change, which you may want to consider checking. I definitely recommend upgrading your development servers for an extensive testing. Some queries _may_ run slower or not work at all and you may have to rearrange how you join tables in your queries. The upgrade from 5.5 to 5.6 should me smoother, though. On Feb 14, 2013, at 4:28 PM, Mike Franon wrote: Great thanks for the info, I guess the best way to do this is take a spare server, set it up with our standard setup, and then start the upgrade as you said 5.0 - 5.1 - 5.5, test and then upgrade to 5.6 and test. On Thu, Feb 14, 2013 at 4:22 PM, Akshay Suryavanshi akshay.suryavansh...@gmail.com wrote: Mike, 5.6 is GA now, so its stable release. Also you should not jump to 5.6 directly, atleast from 5.0. There are many bug fixes and changes in 5.1, so you should consider this way. 5.0--5.1--5.5 (all slaves first, and then the master) And further 5.5 -- 5.6 (again all slaves first and then the master) Hope this helps. Cheers! On Fri, Feb 15, 2013 at 2:38 AM, Mike Franon kongfra...@gmail.com wrote: I have 1 master with many slaves, using the master only for inserts and the rest are readers. Is 5.6 stable? Or better off to go to 5.5? If so do I need to make a few steps or can go straight from 5.0 to 5.6? Any best practices and recommendations? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: Upgrading form mysql 5.0.90 to 5.5 or 5.6
Are you saying there was a regression in the Optimizer? (Sounds like a workaround is to do STRAIGHT_JOIN -- yuck!) I compared several hundred slow queries on 5.1 versus MariaDB 5.5. I found several improvements. -Original Message- From: Mihail Manolov [mailto:mihail.mano...@liquidation.com] Sent: Thursday, February 14, 2013 3:30 PM To: Rick James Cc: Singer Wang; Mike Franon; Akshay Suryavanshi; mysql@lists.mysql.com Subject: Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6 The ones that didn't work for me required table rearrangement in the query. MySQL 5.5 was very particular about the table join order. On Feb 14, 2013, at 6:11 PM, Rick James wrote: Singer, do you have some examples? -Original Message- From: Singer Wang [mailto:w...@singerwang.com] Sent: Thursday, February 14, 2013 2:59 PM To: Mihail Manolov Cc: Mike Franon; Akshay Suryavanshi; mysql@lists.mysql.com Subject: Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6 There are queries that works with 5.1/5.0 that do not work with 5.5, I would test extensively.. S On Thu, Feb 14, 2013 at 5:22 PM, Mihail Manolov mihail.mano...@liquidation.com wrote: You could jump from 5.0 directly to 5.5 and skip 5.1. I have without any issues. There are some configuration file change, which you may want to consider checking. I definitely recommend upgrading your development servers for an extensive testing. Some queries _may_ run slower or not work at all and you may have to rearrange how you join tables in your queries. The upgrade from 5.5 to 5.6 should me smoother, though. On Feb 14, 2013, at 4:28 PM, Mike Franon wrote: Great thanks for the info, I guess the best way to do this is take a spare server, set it up with our standard setup, and then start the upgrade as you said 5.0 - 5.1 - 5.5, test and then upgrade to 5.6 and test. On Thu, Feb 14, 2013 at 4:22 PM, Akshay Suryavanshi akshay.suryavansh...@gmail.com wrote: Mike, 5.6 is GA now, so its stable release. Also you should not jump to 5.6 directly, atleast from 5.0. There are many bug fixes and changes in 5.1, so you should consider this way. 5.0--5.1--5.5 (all slaves first, and then the master) And further 5.5 -- 5.6 (again all slaves first and then the master) Hope this helps. Cheers! On Fri, Feb 15, 2013 at 2:38 AM, Mike Franon kongfra...@gmail.com wrote: I have 1 master with many slaves, using the master only for inserts and the rest are readers. Is 5.6 stable? Or better off to go to 5.5? If so do I need to make a few steps or can go straight from 5.0 to 5.6? Any best practices and recommendations? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: Upgrading form mysql 5.0.90 to 5.5 or 5.6
Sounds like something that, once discovered, can be fixed in the old version -- then it works correctly in both. That is what happened with a 4.0-5.1 conversion years ago. With 1000 different tables and associated code, we encountered two incompatibilities. One had to do with NULLs, the other with precedence of commajoin vs explicit JOIN. From: Singer Wang [mailto:w...@singerwang.com] Sent: Thursday, February 14, 2013 3:41 PM To: Rick James Cc: Mihail Manolov; Mike Franon; Akshay Suryavanshi; mysql@lists.mysql.com Subject: Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6 Its a very pedantic case, but we had a few instances where it was an issue at my last job. It basically involved multi-table deletes and aliasing.. I quote the change notes for MySQL 5.5.3 Incompatible Change: Several changes were made to alias resolution in multiple-table DELETE statements so that it is no longer possible to have inconsistent or ambiguous table aliases. § In MySQL 5.1.23, alias declarations outside the table_references part of the statement were disallowed for theUSING variant of multiple-table DELETE syntax, to reduce the possibility of ambiguous aliases that could lead to ambiguous statements that have unexpected results such as deleting rows from the wrong table. Now alias declarations outside table_references are disallowed for all multiple-table DELETE statements. Alias declarations are permitted only in the table_references part. Incorrect: DELETE FROM t1 AS a2 USING t1 AS a1 INNER JOIN t2 AS a2; DELETE t1 AS a2 FROM t1 AS a1 INNER JOIN t2 AS a2; Correct: DELETE FROM t1 USING t1 AS a1 INNER JOIN t2 AS a2; DELETE t1 FROM t1 AS a1 INNER JOIN t2 AS a2; § Previously, for alias references in the list of tables from which to delete rows in a multiple-table delete, the default database is used unless one is specified explicitly. For example, if the default database is db1, the following statement does not work because the unqualified alias reference a2 is interpreted as having a database of db1: § § DELETE a1, a2 FROM db1.t1 AS a1 INNER JOIN db2.t2 AS a2 WHERE a1.idhttp://a1.id=a2.idhttp://a2.id; To correctly match an alias that refers to a table outside the default database, you must explicitly qualify the reference with the name of the proper database: DELETE a1, db2.a2 FROM db1.t1 AS a1 INNER JOIN db2.t2 AS a2 WHERE a1.idhttp://a1.id=a2.idhttp://a2.id; Now alias resolution does not require qualification and alias references should not be qualified with the database name. Qualified names are interpreted as referring to tables, not aliases. Statements containing alias constructs that are no longer permitted must be rewritten. (Bug #27525) On Thu, Feb 14, 2013 at 6:11 PM, Rick James rja...@yahoo-inc.commailto:rja...@yahoo-inc.com wrote: Singer, do you have some examples? -Original Message- From: Singer Wang [mailto:w...@singerwang.commailto:w...@singerwang.com] Sent: Thursday, February 14, 2013 2:59 PM To: Mihail Manolov Cc: Mike Franon; Akshay Suryavanshi; mysql@lists.mysql.commailto:mysql@lists.mysql.com Subject: Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6 There are queries that works with 5.1/5.0 that do not work with 5.5, I would test extensively.. S On Thu, Feb 14, 2013 at 5:22 PM, Mihail Manolov mihail.mano...@liquidation.commailto:mihail.mano...@liquidation.com wrote: You could jump from 5.0 directly to 5.5 and skip 5.1. I have without any issues. There are some configuration file change, which you may want to consider checking. I definitely recommend upgrading your development servers for an extensive testing. Some queries _may_ run slower or not work at all and you may have to rearrange how you join tables in your queries. The upgrade from 5.5 to 5.6 should me smoother, though. On Feb 14, 2013, at 4:28 PM, Mike Franon wrote: Great thanks for the info, I guess the best way to do this is take a spare server, set it up with our standard setup, and then start the upgrade as you said 5.0 - 5.1 - 5.5, test and then upgrade to 5.6 and test. On Thu, Feb 14, 2013 at 4:22 PM, Akshay Suryavanshi akshay.suryavansh...@gmail.commailto:akshay.suryavansh...@gmail.com wrote: Mike, 5.6 is GA now, so its stable release. Also you should not jump to 5.6 directly, atleast from 5.0. There are many bug fixes and changes in 5.1, so you should consider this way. 5.0--5.1--5.5 (all slaves first, and then the master) And further 5.5 -- 5.6 (again all slaves first and then the master) Hope this helps. Cheers! On Fri, Feb 15, 2013 at 2:38 AM, Mike Franon kongfra...@gmail.commailto:kongfra...@gmail.com wrote: I have 1 master with many slaves, using the master only for inserts and the rest are readers. Is 5.6 stable? Or better off to go to 5.5? If so do I need to make a few steps or can go straight from 5.0
Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6
Its a very pedantic case, but we had a few instances where it was an issue at my last job. It basically involved multi-table deletes and aliasing.. I quote the change notes for MySQL 5.5.3 *Incompatible Change:* Several changes were made to alias resolution in multiple-table DELETE statements so that it is no longer possible to have inconsistent or ambiguous table aliases. - In MySQL 5.1.23, alias declarations outside the *table_references* part of the statement were disallowed for theUSING variant of multiple-table DELETE syntax, to reduce the possibility of ambiguous aliases that could lead to ambiguous statements that have unexpected results such as deleting rows from the wrong table. Now alias declarations outside *table_references* are disallowed for all multiple-table DELETE statements. Alias declarations are permitted only in the *table_references* part. Incorrect: DELETE FROM t1 AS a2 USING t1 AS a1 INNER JOIN t2 AS a2; DELETE t1 AS a2 FROM t1 AS a1 INNER JOIN t2 AS a2; Correct: DELETE FROM t1 USING t1 AS a1 INNER JOIN t2 AS a2; DELETE t1 FROM t1 AS a1 INNER JOIN t2 AS a2; - Previously, for alias references in the list of tables from which to delete rows in a multiple-table delete, the default database is used unless one is specified explicitly. For example, if the default database is db1, the following statement does not work because the unqualified alias reference a2 is interpreted as having a database of db1: DELETE a1, a2 FROM db1.t1 AS a1 INNER JOIN db2.t2 AS a2 WHERE a1.id=a2.id; To correctly match an alias that refers to a table outside the default database, you must explicitly qualify the reference with the name of the proper database: DELETE a1, db2.a2 FROM db1.t1 AS a1 INNER JOIN db2.t2 AS a2 WHERE a1.id=a2.id; Now alias resolution does not require qualification and alias references should not be qualified with the database name. Qualified names are interpreted as referring to tables, not aliases. Statements containing alias constructs that are no longer permitted must be rewritten. (Bug #27525) On Thu, Feb 14, 2013 at 6:11 PM, Rick James rja...@yahoo-inc.com wrote: Singer, do you have some examples? -Original Message- From: Singer Wang [mailto:w...@singerwang.com] Sent: Thursday, February 14, 2013 2:59 PM To: Mihail Manolov Cc: Mike Franon; Akshay Suryavanshi; mysql@lists.mysql.com Subject: Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6 There are queries that works with 5.1/5.0 that do not work with 5.5, I would test extensively.. S On Thu, Feb 14, 2013 at 5:22 PM, Mihail Manolov mihail.mano...@liquidation.com wrote: You could jump from 5.0 directly to 5.5 and skip 5.1. I have without any issues. There are some configuration file change, which you may want to consider checking. I definitely recommend upgrading your development servers for an extensive testing. Some queries _may_ run slower or not work at all and you may have to rearrange how you join tables in your queries. The upgrade from 5.5 to 5.6 should me smoother, though. On Feb 14, 2013, at 4:28 PM, Mike Franon wrote: Great thanks for the info, I guess the best way to do this is take a spare server, set it up with our standard setup, and then start the upgrade as you said 5.0 - 5.1 - 5.5, test and then upgrade to 5.6 and test. On Thu, Feb 14, 2013 at 4:22 PM, Akshay Suryavanshi akshay.suryavansh...@gmail.com wrote: Mike, 5.6 is GA now, so its stable release. Also you should not jump to 5.6 directly, atleast from 5.0. There are many bug fixes and changes in 5.1, so you should consider this way. 5.0--5.1--5.5 (all slaves first, and then the master) And further 5.5 -- 5.6 (again all slaves first and then the master) Hope this helps. Cheers! On Fri, Feb 15, 2013 at 2:38 AM, Mike Franon kongfra...@gmail.com wrote: I have 1 master with many slaves, using the master only for inserts and the rest are readers. Is 5.6 stable? Or better off to go to 5.5? If so do I need to make a few steps or can go straight from 5.0 to 5.6? Any best practices and recommendations? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6
You could jump from 5.0 directly to 5.5 and skip 5.1. I have without any issues. There are some configuration file change, which you may want to consider checking. I definitely recommend upgrading your development servers for an extensive testing. Some queries _may_ run slower or not work at all and you may have to rearrange how you join tables in your queries. The upgrade from 5.5 to 5.6 should me smoother, though. On Feb 14, 2013, at 4:28 PM, Mike Franon wrote: Great thanks for the info, I guess the best way to do this is take a spare server, set it up with our standard setup, and then start the upgrade as you said 5.0 - 5.1 - 5.5, test and then upgrade to 5.6 and test. On Thu, Feb 14, 2013 at 4:22 PM, Akshay Suryavanshi akshay.suryavansh...@gmail.com wrote: Mike, 5.6 is GA now, so its stable release. Also you should not jump to 5.6 directly, atleast from 5.0. There are many bug fixes and changes in 5.1, so you should consider this way. 5.0--5.1--5.5 (all slaves first, and then the master) And further 5.5 -- 5.6 (again all slaves first and then the master) Hope this helps. Cheers! On Fri, Feb 15, 2013 at 2:38 AM, Mike Franon kongfra...@gmail.com wrote: I have 1 master with many slaves, using the master only for inserts and the rest are readers. Is 5.6 stable? Or better off to go to 5.5? If so do I need to make a few steps or can go straight from 5.0 to 5.6? Any best practices and recommendations? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6
There are queries that works with 5.1/5.0 that do not work with 5.5, I would test extensively.. S On Thu, Feb 14, 2013 at 5:22 PM, Mihail Manolov mihail.mano...@liquidation.com wrote: You could jump from 5.0 directly to 5.5 and skip 5.1. I have without any issues. There are some configuration file change, which you may want to consider checking. I definitely recommend upgrading your development servers for an extensive testing. Some queries _may_ run slower or not work at all and you may have to rearrange how you join tables in your queries. The upgrade from 5.5 to 5.6 should me smoother, though. On Feb 14, 2013, at 4:28 PM, Mike Franon wrote: Great thanks for the info, I guess the best way to do this is take a spare server, set it up with our standard setup, and then start the upgrade as you said 5.0 - 5.1 - 5.5, test and then upgrade to 5.6 and test. On Thu, Feb 14, 2013 at 4:22 PM, Akshay Suryavanshi akshay.suryavansh...@gmail.com wrote: Mike, 5.6 is GA now, so its stable release. Also you should not jump to 5.6 directly, atleast from 5.0. There are many bug fixes and changes in 5.1, so you should consider this way. 5.0--5.1--5.5 (all slaves first, and then the master) And further 5.5 -- 5.6 (again all slaves first and then the master) Hope this helps. Cheers! On Fri, Feb 15, 2013 at 2:38 AM, Mike Franon kongfra...@gmail.com wrote: I have 1 master with many slaves, using the master only for inserts and the rest are readers. Is 5.6 stable? Or better off to go to 5.5? If so do I need to make a few steps or can go straight from 5.0 to 5.6? Any best practices and recommendations? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6
The ones that didn't work for me required table rearrangement in the query. MySQL 5.5 was very particular about the table join order. On Feb 14, 2013, at 6:11 PM, Rick James wrote: Singer, do you have some examples? -Original Message- From: Singer Wang [mailto:w...@singerwang.com] Sent: Thursday, February 14, 2013 2:59 PM To: Mihail Manolov Cc: Mike Franon; Akshay Suryavanshi; mysql@lists.mysql.com Subject: Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6 There are queries that works with 5.1/5.0 that do not work with 5.5, I would test extensively.. S On Thu, Feb 14, 2013 at 5:22 PM, Mihail Manolov mihail.mano...@liquidation.com wrote: You could jump from 5.0 directly to 5.5 and skip 5.1. I have without any issues. There are some configuration file change, which you may want to consider checking. I definitely recommend upgrading your development servers for an extensive testing. Some queries _may_ run slower or not work at all and you may have to rearrange how you join tables in your queries. The upgrade from 5.5 to 5.6 should me smoother, though. On Feb 14, 2013, at 4:28 PM, Mike Franon wrote: Great thanks for the info, I guess the best way to do this is take a spare server, set it up with our standard setup, and then start the upgrade as you said 5.0 - 5.1 - 5.5, test and then upgrade to 5.6 and test. On Thu, Feb 14, 2013 at 4:22 PM, Akshay Suryavanshi akshay.suryavansh...@gmail.com wrote: Mike, 5.6 is GA now, so its stable release. Also you should not jump to 5.6 directly, atleast from 5.0. There are many bug fixes and changes in 5.1, so you should consider this way. 5.0--5.1--5.5 (all slaves first, and then the master) And further 5.5 -- 5.6 (again all slaves first and then the master) Hope this helps. Cheers! On Fri, Feb 15, 2013 at 2:38 AM, Mike Franon kongfra...@gmail.com wrote: I have 1 master with many slaves, using the master only for inserts and the rest are readers. Is 5.6 stable? Or better off to go to 5.5? If so do I need to make a few steps or can go straight from 5.0 to 5.6? Any best practices and recommendations? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
A better REPAIR TABLE for myisam tables (or for upgrading tables)
I've posted a similar post in the past -- but there I was mucking around with blank index files and frm files to fool myisamchk into repairing a table. But now I think I've come across a much better and more efficient way to do a REPAIR Table in order to upgrade my database tables from Mysql 4.1.x to 5.5.8. All this comes from the fact that REPAIR TABLE does not rebuild the table indexes like myisamchk does, which is very unfortunate. Sure, REPAIR TABLE works great for small tables, but if you have any tables of larger size (millions of records or more, with multiple indexes), REPAIR TABLE can take hours or days to do a simple repair/upgrade. And in most cases, applications just can't be down for that long during an upgrade cycle (not everyone runs a huge shop with multiple dev/test/upgrade/production servers). So here is what I have done, and propose this as a better REPAIR TABLE for MYISAM tables (in pseudo code): 1. Retrieve the original CREATE TABLE DDL with show create table SOURCE 2. Modify DDL to change the table name to a new target table, let's call it TARGET 3. Execute new DDL to create empty TARGET table 4. Run 'myisamchk -r --keys-used=0 TARGET' (to disable all index keys on new table) 5. flush tables; lock table SOURCE read, TARGET write; 6. insert into TARGET select * From SOURCE; 7. flush tables; unlock tables; 8. 'myisamchk -prqn TARGET' (repair to re-enable all keys, do not modify MYD table, use sorting, in parallel) 9. rename tables to replace SOURCE with TARGET I've written a PHP script to do exactly this, and it works beautifully. My source tables are mysql 4.1.x tables, and the target tables are now fully 5.5 compliant (verified with mysqlcheck --check-upgrade). The best part is that for tables with 50 million short rows, it ran in 7 minutes, and a table with 30 million rows, it ran in 4 minutes. I'm now running it on a table with over 200 million rows, and I expect it to take an hour or so... but in all cases, doing a REPAIR TABLE on any of these large tables would take days to complete. So why can't the REPAIR TABLE command do something like this in the background for large MYISAM tables? -Hank
Re: A better REPAIR TABLE for myisam tables (or for upgrading tables)
Sorry... One small correction to my above post.. 'FLUSH TABLES' should be issued between steps 8 and 9. My 200+ million record table completed in 71 minutes. -Hank mysql; query;
Upgrading of mysql database
Hi All Sorry for all my posts today but this one client is keeping me busy. the version of MySQL installed on the ubuntu server is 5.0.51a-3ubuntu5.8-log as this was the latest one available in the repository. We will need to upgrade this to version 5.1.53. Am I correct in assuming the following steps? 1. setup version 5.1.53 on the machine (different port) 2. shutdown the current database. 3. create backup file 4. restore backup 5. change port to 3306 6. startup new database. 7. disable the old database so that it would not start up during reboot. Regards Machiel
Re: Upgrading of mysql database
That would work, yes. You could also try to upgrade in place - the upgrade scripts *should* take care of everything between those versions, I think. Make sure you have a backup in any case :-) On Mon, Nov 22, 2010 at 12:57 PM, Machiel Richards machi...@rdc.co.zawrote: Hi All Sorry for all my posts today but this one client is keeping me busy. the version of MySQL installed on the ubuntu server is 5.0.51a-3ubuntu5.8-log as this was the latest one available in the repository. We will need to upgrade this to version 5.1.53. Am I correct in assuming the following steps? 1. setup version 5.1.53 on the machine (different port) 2. shutdown the current database. 3. create backup file 4. restore backup 5. change port to 3306 6. startup new database. 7. disable the old database so that it would not start up during reboot. Regards Machiel -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: Upgrading of mysql database
Replace the software - if you're using packaged versions, they should take care of most anything. If not, there's mysql-upgrade or some script. See the online docs for specifics. On Mon, Nov 22, 2010 at 3:56 PM, Machiel Richards machi...@rdc.co.zawrote: How would I do an inplace upgrade? -Original Message- *From*: Johan De Meersman vegiv...@tuxera.bejohan%20de%20meersman%20%3cvegiv...@tuxera.be%3e *To*: Machiel Richards machi...@rdc.co.zamachiel%20richards%20%3cmachi...@rdc.co.za%3e *Cc*: mysql mailing list mysql@lists.mysql.commysql%20mailing%20list%20%3cmy...@lists.mysql.com%3e *Subject*: Re: Upgrading of mysql database *Date*: Mon, 22 Nov 2010 15:25:44 +0100 That would work, yes. You could also try to upgrade in place - the upgrade scripts *should* take care of everything between those versions, I think. Make sure you have a backup in any case :-) On Mon, Nov 22, 2010 at 12:57 PM, Machiel Richards machi...@rdc.co.za wrote: Hi All Sorry for all my posts today but this one client is keeping me busy. the version of MySQL installed on the ubuntu server is 5.0.51a-3ubuntu5.8-log as this was the latest one available in the repository. We will need to upgrade this to version 5.1.53. Am I correct in assuming the following steps? 1. setup version 5.1.53 on the machine (different port) 2. shutdown the current database. 3. create backup file 4. restore backup 5. change port to 3306 6. startup new database. 7. disable the old database so that it would not start up during reboot. Regards Machiel -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: Upgrading of mysql database
How would I do an inplace upgrade? -Original Message- From: Johan De Meersman vegiv...@tuxera.be To: Machiel Richards machi...@rdc.co.za Cc: mysql mailing list mysql@lists.mysql.com Subject: Re: Upgrading of mysql database Date: Mon, 22 Nov 2010 15:25:44 +0100 That would work, yes. You could also try to upgrade in place - the upgrade scripts *should* take care of everything between those versions, I think. Make sure you have a backup in any case :-) On Mon, Nov 22, 2010 at 12:57 PM, Machiel Richards machi...@rdc.co.za wrote: Hi All Sorry for all my posts today but this one client is keeping me busy. the version of MySQL installed on the ubuntu server is 5.0.51a-3ubuntu5.8-log as this was the latest one available in the repository. We will need to upgrade this to version 5.1.53. Am I correct in assuming the following steps? 1. setup version 5.1.53 on the machine (different port) 2. shutdown the current database. 3. create backup file 4. restore backup 5. change port to 3306 6. startup new database. 7. disable the old database so that it would not start up during reboot. Regards Machiel -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Is upgrading from 4.X to 5.X really that easy?
Hi all, I was reading a few of the notes on this forum from a few months back and it seems that ONE WAY of upgrading from 4.x to 5.X with MyISAM only databases is to copy the actual data folder from the 4.X version to a temp place, then remove 4.x from the OS, install 5.X and then just put the 4.X data folder into the 5.X folder??? Is it really that simple? Has anyone done this and can verify this please? I am thinking I am missing a few commands you have to run at least??? My database is pretty small is an only 1.8GB so I am thinking this is a walk in the park :-) Please advise... And as always... TIA... Nunzio
Re: Is upgrading from 4.X to 5.X really that easy?
On Mon, August 16, 2010 07:26, Nunzio Daveri wrote: Hi all, I was reading a few of the notes on this forum from a few months back and it seems that ONE WAY of upgrading from 4.x to 5.X with MyISAM only databases is to copy the actual data folder from the 4.X version to a temp place, then remove 4.x from the OS, install 5.X and then just put the 4.X data folder into the 5.X folder??? Is it really that simple? Has anyone done this and can verify this please? I am thinking I am missing a few commands you have to run at least??? My database is pretty small is an only 1.8GB so I am thinking this is a walk in the park :-) Please advise... And as always... TIA... Nunzio What version of 4.x,� I upgraded between Debian stable versions and got burned because in the middle of the 4.x group MySQL changed to a more correct version of JOINs.� Didn't effect to data, which will should work as you expect, but did have subtle impact on the select statements embedded in various programs.� Read the release note between YOUR current and new versions. -- William R. Mussatto Systems Engineer http://www.csz.com 909-920-9154
Re: Is upgrading from 4.X to 5.X really that easy?
MySQL rightly says you should dump and reload. As William said, you should read the release notes for every release between your current release and the target release. I have done this several times. I am currently planning a migration from 4.1.22 to 5.1.49 with a brief stop at 5.0 along the way. There is almost 200 gigs of data on the master server in this situation so it does take some foresight and planning. However, the end result will be no significant downtime. You can configure MySQL 5.0 as a slave of MySQL 4.1 so you might consider doing that. That way when its time for the actual upgrade you just point the application to the MySQL 5.0 server and shut down the MySQL 4.1 server (which CANNOT be a slave of MysQL 5.0 -- it's a one way relationship due to the changes in the binary logging. Hope that helps. keith On Mon, Aug 16, 2010 at 11:42 AM, Wm Mussatto mussa...@csz.com wrote: On Mon, August 16, 2010 07:26, Nunzio Daveri wrote: Hi all, I was reading a few of the notes on this forum from a few months back and it seems that ONE WAY of upgrading from 4.x to 5.X with MyISAM only databases is to copy the actual data folder from the 4.X version to a temp place, then remove 4.x from the OS, install 5.X and then just put the 4.X data folder into the 5.X folder??? Is it really that simple? Has anyone done this and can verify this please? I am thinking I am missing a few commands you have to run at least??? My database is pretty small is an only 1.8GB so I am thinking this is a walk in the park :-) Please advise... And as always... TIA... Nunzio What version of 4.x, I upgraded between Debian stable versions and got burned because in the middle of the 4.x group MySQL changed to a more correct version of JOINs. Didn't effect to data, which will should work as you expect, but did have subtle impact on the select statements embedded in various programs. Read the release note between YOUR current and new versions. -- William R. Mussatto Systems Engineer http://www.csz.com 909-920-9154 -- Chief Training Officer Paragon Consulting Services 850-637-3877
Re: Is upgrading from 4.X to 5.X really that easy?
Thanks William and Keith. So how to have min down time since this is a stand alone mysql 4.1.22 box? Are you saying install 5.X on the same box (port 3307), then replicate the data as it comes into 4.x to 5.x and when it is all sync'd up then turn 4.x off, remove it and have 5.x responding on port 3306? Mysqldump takes over an hour and then prob more to reimport? I only have a 30 - 45 min window. If I do a straight dump from 4.1.22 with all options why stop at 5.0 and not just go straight to 5.1.48? This is a single box, no replication or clustering going on ;-) Also all the data is in MyISAM, zero InnoDB :-) Thanks again for the advice :-) Nunzio From: Keith Murphy bmur...@paragon-cs.com To: Nunzio Daveri nunziodav...@yahoo.com Sent: Mon, August 16, 2010 9:42:07 AM Subject: Re: Is upgrading from 4.X to 5.X really that easy? No, that would be a huge mistake. There are subtle differences between the two versions. For example, check up on DECIMAL. Also, 5.0 and 5.1 have numerous new reserved words. You need to think about this carefully before you do it. I know there is binary incompatability between Innodb tables (vers 4.X - 5.X). I will take you word that what you are saying would actually work, but I still wouldn't recommend it. Just my 2 cents... keith On Mon, Aug 16, 2010 at 10:26 AM, Nunzio Daveri nunziodav...@yahoo.com wrote: Hi all, I was reading a few of the notes on this forum from a few months back and it seems that ONE WAY of upgrading from 4.x to 5.X with MyISAM only databases is to copy the actual data folder from the 4.X version to a temp place, then remove 4.x from the OS, install 5.X and then just put the 4.X data folder into the 5.X folder??? Is it really that simple? Has anyone done this and can verify this please? I am thinking I am missing a few commands you have to run at least??? My database is pretty small is an only 1.8GB so I am thinking this is a walk in the park :-) Please advise... And as always... TIA... Nunzio -- Chief Training Officer Paragon Consulting Services 850-637-3877
Re: Is upgrading from 4.X to 5.X really that easy?
I would really recommend that you have a second server set up running MySQL 5.0. Otherwise the complexity is going to drive you crazy. Either way you are going to have to get a backup of the master (4.1) server somehow. I am curious. If you can't take an hour or so take a mysqldump of the server how are you running backups now? And if you aren't runninng backups you need to run to your boss and say It's REALLY REALLY REALLY critical that we start making backups. And do it beginning tonight at the latest. Otherwise something is going to happen, the data is going to be lost and you are best case going to look REALLY REALLY bad. I wouldn't recommend going straight to 5.1. The upgrade from 5.0 to 5.1 is fairly trivial and doesn't require a dump/reload but I would still take the time to stop at 5.0 and make sure everything is working before moving on to 5.1. keith On Mon, Aug 16, 2010 at 12:42 PM, Nunzio Daveri nunziodav...@yahoo.comwrote: Thanks William and Keith. So how to have min down time since this is a stand alone mysql 4.1.22 box? Are you saying install 5.X on the same box (port 3307), then replicate the data as it comes into 4.x to 5.x and when it is all sync'd up then turn 4.x off, remove it and have 5.x responding on port 3306? Mysqldump takes over an hour and then prob more to reimport? I only have a 30 - 45 min window. If I do a straight dump from 4.1.22 with all options why stop at 5.0 and not just go straight to 5.1.48? This is a single box, no replication or clustering going on ;-) Also all the data is in MyISAM, zero InnoDB :-) Thanks again for the advice :-) Nunzio From: Keith Murphy bmur...@paragon-cs.com To: Nunzio Daveri nunziodav...@yahoo.com Sent: Mon, August 16, 2010 9:42:07 AM Subject: Re: Is upgrading from 4.X to 5.X really that easy? No, that would be a huge mistake. There are subtle differences between the two versions. For example, check up on DECIMAL. Also, 5.0 and 5.1 have numerous new reserved words. You need to think about this carefully before you do it. I know there is binary incompatability between Innodb tables (vers 4.X - 5.X). I will take you word that what you are saying would actually work, but I still wouldn't recommend it. Just my 2 cents... keith On Mon, Aug 16, 2010 at 10:26 AM, Nunzio Daveri nunziodav...@yahoo.com wrote: Hi all, I was reading a few of the notes on this forum from a few months back and it seems that ONE WAY of upgrading from 4.x to 5.X with MyISAM only databases is to copy the actual data folder from the 4.X version to a temp place, then remove 4.x from the OS, install 5.X and then just put the 4.X data folder into the 5.X folder??? Is it really that simple? Has anyone done this and can verify this please? I am thinking I am missing a few commands you have to run at least??? My database is pretty small is an only 1.8GB so I am thinking this is a walk in the park :-) Please advise... And as always... TIA... Nunzio -- Chief Training Officer Paragon Consulting Services 850-637-3877 -- Chief Training Officer Paragon Consulting Services 850-637-3877
Re: Is upgrading from 4.X to 5.X really that easy?
Thanks Keith :-) Last question, do you think it's ok for me to do a sqldump on 4.1.22 at say 3 am on sun, then import to 5.1.48 at 4 am and then just edit etc/hosts and have the web servers now point to 5.1? This should work without any problems right? Plus I have the original 4.x in case I break something during the dump and can revert within mins back to the 4.x version? Still don't know why I should do a mysql dump from 4.1.X to 5.0.x and then upgrade 5.0.x to 5.1.48 esp. if I am doing nothing more than a mysql dump and not upgrading in place ;-) TIA... Nunzio From: Keith Murphy bmur...@paragon-cs.com To: Nunzio Daveri nunziodav...@yahoo.com Cc: mysql@lists.mysql.com Sent: Mon, August 16, 2010 11:47:39 AM Subject: Re: Is upgrading from 4.X to 5.X really that easy? I would really recommend that you have a second server set up running MySQL 5.0. Otherwise the complexity is going to drive you crazy. Either way you are going to have to get a backup of the master (4.1) server somehow. I am curious. If you can't take an hour or so take a mysqldump of the server how are you running backups now? And if you aren't runninng backups you need to run to your boss and say It's REALLY REALLY REALLY critical that we start making backups. And do it beginning tonight at the latest. Otherwise something is going to happen, the data is going to be lost and you are best case going to look REALLY REALLY bad. I wouldn't recommend going straight to 5.1. The upgrade from 5.0 to 5.1 is fairly trivial and doesn't require a dump/reload but I would still take the time to stop at 5.0 and make sure everything is working before moving on to 5.1. keith On Mon, Aug 16, 2010 at 12:42 PM, Nunzio Daveri nunziodav...@yahoo.com wrote: Thanks William and Keith. So how to have min down time since this is a stand alone mysql 4.1.22 box? Are you saying install 5.X on the same box (port 3307), then replicate the data as it comes into 4.x to 5.x and when it is all sync'd up then turn 4.x off, remove it and have 5.x responding on port 3306? Mysqldump takes over an hour and then prob more to reimport? I only have a 30 - 45 min window. If I do a straight dump from 4.1.22 with all options why stop at 5.0 and not just go straight to 5.1.48? This is a single box, no replication or clustering going on ;-) Also all the data is in MyISAM, zero InnoDB :-) Thanks again for the advice :-) Nunzio From: Keith Murphy bmur...@paragon-cs.com To: Nunzio Daveri nunziodav...@yahoo.com Sent: Mon, August 16, 2010 9:42:07 AM Subject: Re: Is upgrading from 4.X to 5.X really that easy? No, that would be a huge mistake. There are subtle differences between the two versions. For example, check up on DECIMAL. Also, 5.0 and 5.1 have numerous new reserved words. You need to think about this carefully before you do it. I know there is binary incompatability between Innodb tables (vers 4.X - 5.X). I will take you word that what you are saying would actually work, but I still wouldn't recommend it. Just my 2 cents... keith On Mon, Aug 16, 2010 at 10:26 AM, Nunzio Daveri nunziodav...@yahoo.com wrote: Hi all, I was reading a few of the notes on this forum from a few months back and it seems that ONE WAY of upgrading from 4.x to 5.X with MyISAM only databases is to copy the actual data folder from the 4.X version to a temp place, then remove 4.x from the OS, install 5.X and then just put the 4.X data folder into the 5.X folder??? Is it really that simple? Has anyone done this and can verify this please? I am thinking I am missing a few commands you have to run at least??? My database is pretty small is an only 1.8GB so I am thinking this is a walk in the park :-) Please advise... And as always... TIA... Nunzio -- Chief Training Officer Paragon Consulting Services 850-637-3877 -- Chief Training Officer Paragon Consulting Services 850-637-3877
MySQL Upgrading
Hi, I am looking at upgrading my servers Debian version from Etch to Lenny, and in doing that, I think it will upgrade MySQL from 5.0.32 to the lenny version, which is 5.0.53 (I think). I have also been thinking about using the 'dotdeb' packages, which will upgrade it even further to 5.1.47. I have done this on a test server, and it all my stored procedures and stuff work fine, so now to my question. Will this break any replication if I don't upgrade my replication server that is still running 5.0.32 (until I upgrade that server as well, which could be a few weeks due to timing)? The other issue, is that the replication server is running multiple instances of the same MySQL on different ports, so that I can replicate multiple sources to a single server (that has attached tape drives for backup purposes), is it possible to run both versions of mysql (the 5.0.32 and the 5.1.47)?granted, this is not the debian mailing list, just thought I would ask that last part ;) Steven Staples -- 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 Upgrading
On Wed, Jun 23, 2010 at 6:33 AM, Steven Staples sstap...@mnsi.net wrote: Hi, I am looking at upgrading my servers Debian version from Etch to Lenny, and in doing that, I think it will upgrade MySQL from 5.0.32 to the lenny version, which is 5.0.53 (I think). I have also been thinking about using the 'dotdeb' packages, which will upgrade it even further to 5.1.47. I have done this on a test server, and it all my stored procedures and stuff work fine, so now to my question. Will this break any replication if I don't upgrade my replication server that is still running 5.0.32 (until I upgrade that server as well, which could be a few weeks due to timing)? It will. I suggest upgrading to at least 5.0.67 if you have no fear of internal users, and the most recent version of 5.0 and 5.1 (I don't recall what they are) if you have fears of malicious users. The other issue, is that the replication server is running multiple instances of the same MySQL on different ports, so that I can replicate multiple sources to a single server (that has attached tape drives for backup purposes), is it possible to run both versions of mysql (the 5.0.32 and the 5.1.47)? granted, this is not the debian mailing list, just thought I would ask that last part ;) Steven Staples -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=wult...@gmail.com -- Rob Wultsch wult...@gmail.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: upgrading mysql
The issue is that in theory this should work given the facts announced by MySQL regarding binary logging and replication. I can certainly do it the way you propose, but to my mind I should also be able to do it using the fact that both machines are fully synced and hence at that point I should be able to to local respective dumps and restores and still be in sync. Anyone knows anything special about position 106? It seems to be the very initial position in MySQL 5.1 servers? mysql show master status; +---+--+--+--+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +---+--+--+--+ | X-bin.01 | 106 | | | +---+--+--+--+ 1 row in set (0.00 sec) r...@:/usr/local/mysql/data ] /usr/local/mysql/bin/mysqlbinlog mssdb2-bin.01 /*!40019 SET @@session.max_insert_delayed_threads=0*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #100113 13:50:40 server id 5 end_log_pos 106 Start: binlog v 4, server v 5.1.42-log created 100113 13:50:40 at startup # Warning: this binlog is either in use or was not closed properly. ROLLBACK/*!*/; BINLOG ' ABZOSw8FZgAAAGoBAAQANS4xLjQyLWxvZwAA Fk5LEzgNAAgAEgAEBAQEEgAAUwAEGggICAgC '/*!*/; DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET completion_ty...@old_completion_type*/; r...@:/usr/local/mysql/data ] ~Lawrence Tom Worster wrote: Frankly, I didn't entirely understand what you were proposing. I got lost around step 6. Is the issue total time for the procedure or service downtime? On 1/12/10 12:58 PM, Lawrence Sorrillo sorri...@jlab.org wrote: This is two upgrades done in sequence(the reload takes about three hours per machine) . I can do what I am proposing in parallel. Do you see it as problematic? ~Lawrence Tom Worster wrote: How about: 1 shut down the slave, upgrade it, restart it, let it catch up. 2 shut down the master, upgrade it, restart it, let the slave catch up. ? On 1/12/10 12:34 PM, Lawrence Sorrillo sorri...@jlab.org wrote: Hi: I want to upgrade a master and slave server from mysql 4.1 to mysql 5.1. I want to so something like follows: 1. Stop all write access to the master server. 2. Ensure that replication on the slave is caught up to the last change on the master. 3. stop binary logging on the master. 4. stop replication on the slave. 5. dump the master, stop old 4.1 server, start new 5.1 server and reload master dump file under 5.1 server ( binary logging is turned off) 6. dump the slave, stop old 4.1 server, start new 5.1 server and reload slave dump file under 5.1 server. 7. After loading is complete, test then start binary logging on master while still preventing updates to updates. 8. After loading slave, test then start slave (get configs in place and restart server). I am thinking that in this scenario I dont have to bother with recording binlog file names and position etc etc. That both servers will have the same databases abd replication and binary logging will start on the two databases with no data loss and continue forward. Comments? ~Lawrence -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: upgrading mysql
On 1/13/10 2:28 PM, Lawrence Sorrillo sorri...@jlab.org wrote:
The issue is that in theory this should work given the facts announced
by MySQL regarding binary logging and replication.
I can certainly do it the way you propose, but to my mind I should also
be able to do it using the fact that both machines are fully synced and
hence at
that point I should be able to to local respective dumps and restores
and still be in sync.
i can't point at anything in your recipe and say that it doesn't work. it
might work. i'd be nervous that something in steps 5 and 6 might involve a
change on the master that needs to be replicated. since your using a dump
and not a binary copy of myisam file, i suppose this ought to be safe. but i
would be nervous all the same.
on the other hand, i do know that the recipe i gave works because i've used
it often. it also has the virtue of no need for recording binlog file names
and position etc etc. plus it's the procedure recommended by the mysql folk
themselves, which is worth something to me.
the other thing i've done is:
initial status: A is the master and B is the slave. service is operating off
the master.
1 stop B, upgrade it, restart it, let it catch up.
2 stop service and then stop A
3 change B's conf file to make it the master. restart it
4 resume service using B
5 upgrade A and bring it online as a slave
this has the virtue of very short service outage. with some rehearsal, it
isn't beyond my skills.
Anyone knows anything special about position 106? It seems to be the
very initial position in MySQL 5.1 servers?
the manual says:
If the master has been running previously without binary logging enabled,
the log name and position values displayed by SHOW MASTER STATUS or
mysqldump --master-data will be empty. In that case, the values that you
need to use later when specifying the slave's log file and position are the
empty string ('') and 4.
perhaps you have an init-file that advances it to position 106?
mysql show master status;
+---+--+--+--+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---+--+--+--+
| X-bin.01 | 106 | | |
+---+--+--+--+
1 row in set (0.00 sec)
r...@:/usr/local/mysql/data ] /usr/local/mysql/bin/mysqlbinlog
mssdb2-bin.01
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#100113 13:50:40 server id 5 end_log_pos 106 Start: binlog v 4,
server v 5.1.42-log created 100113 13:50:40 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
ABZOSw8FZgAAAGoBAAQANS4xLjQyLWxvZwAA
Fk5LEzgNAAgAEgAEBAQEEgAAUwAEGggICAgC
'/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET completion_ty...@old_completion_type*/;
r...@:/usr/local/mysql/data ]
~Lawrence
Tom Worster wrote:
Frankly, I didn't entirely understand what you were proposing. I got lost
around step 6.
Is the issue total time for the procedure or service downtime?
On 1/12/10 12:58 PM, Lawrence Sorrillo sorri...@jlab.org wrote:
This is two upgrades done in sequence(the reload takes about three hours
per machine) . I can do what I am proposing in parallel.
Do you see it as problematic?
~Lawrence
Tom Worster wrote:
How about:
1 shut down the slave, upgrade it, restart it, let it catch up.
2 shut down the master, upgrade it, restart it, let the slave catch up.
?
On 1/12/10 12:34 PM, Lawrence Sorrillo sorri...@jlab.org wrote:
Hi:
I want to upgrade a master and slave server from mysql 4.1 to mysql 5.1.
I want to so something like follows:
1. Stop all write access to the master server.
2. Ensure that replication on the slave is caught up to the last change
on the master.
3. stop binary logging on the master.
4. stop replication on the slave.
5. dump the master, stop old 4.1 server, start new 5.1 server and reload
master dump file under 5.1 server ( binary logging is turned off)
6. dump the slave, stop old 4.1 server, start new 5.1 server and reload
slave dump file under 5.1 server.
7. After loading is complete, test then start binary logging on master
while still preventing updates to updates.
8. After loading slave, test then start slave (get configs in place and
restart server).
I am thinking that in this scenario I dont have to bother with recording
binlog file names and position etc etc.
That both servers will have the same databases abd replication and
binary logging will start on the two databases with no data loss and
continue forward.
Comments?
~Lawrence
Re: upgrading mysql
On Jan 13, 2010, at 1:28 PM, Lawrence Sorrillo wrote: The issue is that in theory this should work given the facts announced by MySQL regarding binary logging and replication. I can certainly do it the way you propose, but to my mind I should also be able to do it using the fact that both machines are fully synced and hence at that point I should be able to to local respective dumps and restores and still be in sync. Anyone knows anything special about position 106? It seems to be the very initial position in MySQL 5.1 servers? It's not. 4 is still the initial position, as shown by the at 4 in your mysqlbinlog output below. The 106 that you observe is the position *after* the server writes the initial event to the binary log. It writes this event immediately after opening the file, even before executing any statements. If you want the gory details: This event is the format description event that identifies in the binary log file the server version and other information. See http://forge.mysql.com/wiki/MySQL_Internals_Binary_Log#Binary_Log_Versions if you have a high tolerance for pain. :-) mysql show master status; +---+--+--+--+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +---+--+--+--+ | X-bin.01 | 106 | | | +---+--+--+--+ 1 row in set (0.00 sec) r...@:/usr/local/mysql/data ] /usr/local/mysql/bin/mysqlbinlog mssdb2-bin.01 /*!40019 SET @@session.max_insert_delayed_threads=0*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #100113 13:50:40 server id 5 end_log_pos 106 Start: binlog v 4, server v 5.1.42-log created 100113 13:50:40 at startup # Warning: this binlog is either in use or was not closed properly. ROLLBACK/*!*/; BINLOG ' ABZOSw8FZgAAAGoBAAQANS4xLjQyLWxvZwAA Fk5LEzgNAAgAEgAEBAQEEgAAUwAEGggICAgC '/*!*/; DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET completion_ty...@old_completion_type*/; r...@:/usr/local/mysql/data ] ~Lawrence Tom Worster wrote: Frankly, I didn't entirely understand what you were proposing. I got lost around step 6. Is the issue total time for the procedure or service downtime? On 1/12/10 12:58 PM, Lawrence Sorrillo sorri...@jlab.org wrote: This is two upgrades done in sequence(the reload takes about three hours per machine) . I can do what I am proposing in parallel. Do you see it as problematic? ~Lawrence Tom Worster wrote: How about: 1 shut down the slave, upgrade it, restart it, let it catch up. 2 shut down the master, upgrade it, restart it, let the slave catch up. ? On 1/12/10 12:34 PM, Lawrence Sorrillo sorri...@jlab.org wrote: Hi: I want to upgrade a master and slave server from mysql 4.1 to mysql 5.1. I want to so something like follows: 1. Stop all write access to the master server. 2. Ensure that replication on the slave is caught up to the last change on the master. 3. stop binary logging on the master. 4. stop replication on the slave. 5. dump the master, stop old 4.1 server, start new 5.1 server and reload master dump file under 5.1 server ( binary logging is turned off) 6. dump the slave, stop old 4.1 server, start new 5.1 server and reload slave dump file under 5.1 server. 7. After loading is complete, test then start binary logging on master while still preventing updates to updates. 8. After loading slave, test then start slave (get configs in place and restart server). I am thinking that in this scenario I dont have to bother with recording binlog file names and position etc etc. That both servers will have the same databases abd replication and binary logging will start on the two databases with no data loss and continue forward. Comments? ~Lawrence -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=paul.dub...@sun.com -- Paul DuBois Sun Microsystems / MySQL Documentation Team Madison, Wisconsin, USA www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
upgrading mysql
Hi: I want to upgrade a master and slave server from mysql 4.1 to mysql 5.1. I want to so something like follows: 1. Stop all write access to the master server. 2. Ensure that replication on the slave is caught up to the last change on the master. 3. stop binary logging on the master. 4. stop replication on the slave. 5. dump the master, stop old 4.1 server, start new 5.1 server and reload master dump file under 5.1 server ( binary logging is turned off) 6. dump the slave, stop old 4.1 server, start new 5.1 server and reload slave dump file under 5.1 server. 7. After loading is complete, test then start binary logging on master while still preventing updates to updates. 8. After loading slave, test then start slave (get configs in place and restart server). I am thinking that in this scenario I dont have to bother with recording binlog file names and position etc etc. That both servers will have the same databases abd replication and binary logging will start on the two databases with no data loss and continue forward. Comments? ~Lawrence -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: upgrading mysql
How about: 1 shut down the slave, upgrade it, restart it, let it catch up. 2 shut down the master, upgrade it, restart it, let the slave catch up. ? On 1/12/10 12:34 PM, Lawrence Sorrillo sorri...@jlab.org wrote: Hi: I want to upgrade a master and slave server from mysql 4.1 to mysql 5.1. I want to so something like follows: 1. Stop all write access to the master server. 2. Ensure that replication on the slave is caught up to the last change on the master. 3. stop binary logging on the master. 4. stop replication on the slave. 5. dump the master, stop old 4.1 server, start new 5.1 server and reload master dump file under 5.1 server ( binary logging is turned off) 6. dump the slave, stop old 4.1 server, start new 5.1 server and reload slave dump file under 5.1 server. 7. After loading is complete, test then start binary logging on master while still preventing updates to updates. 8. After loading slave, test then start slave (get configs in place and restart server). I am thinking that in this scenario I dont have to bother with recording binlog file names and position etc etc. That both servers will have the same databases abd replication and binary logging will start on the two databases with no data loss and continue forward. Comments? ~Lawrence -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: upgrading mysql
Also see http://dev.mysql.con/doc/refman/5.0/en/mysql-upgrade.html. And make sure you make a backup before you do anything :) -Original Message- From: Tom Worster [mailto:f...@thefsb.org] Sent: Tuesday, January 12, 2010 10:47 AM To: Lawrence Sorrillo; mysql@lists.mysql.com Subject: Re: upgrading mysql How about: 1 shut down the slave, upgrade it, restart it, let it catch up. 2 shut down the master, upgrade it, restart it, let the slave catch up. ? On 1/12/10 12:34 PM, Lawrence Sorrillo sorri...@jlab.org wrote: Hi: I want to upgrade a master and slave server from mysql 4.1 to mysql 5.1. I want to so something like follows: 1. Stop all write access to the master server. 2. Ensure that replication on the slave is caught up to the last change on the master. 3. stop binary logging on the master. 4. stop replication on the slave. 5. dump the master, stop old 4.1 server, start new 5.1 server and reload master dump file under 5.1 server ( binary logging is turned off) 6. dump the slave, stop old 4.1 server, start new 5.1 server and reload slave dump file under 5.1 server. 7. After loading is complete, test then start binary logging on master while still preventing updates to updates. 8. After loading slave, test then start slave (get configs in place and restart server). I am thinking that in this scenario I dont have to bother with recording binlog file names and position etc etc. That both servers will have the same databases abd replication and binary logging will start on the two databases with no data loss and continue forward. Comments? ~Lawrence -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=jgor...@westernwats.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: upgrading mysql
This is two upgrades done in sequence(the reload takes about three hours per machine) . I can do what I am proposing in parallel. Do you see it as problematic? ~Lawrence Tom Worster wrote: How about: 1 shut down the slave, upgrade it, restart it, let it catch up. 2 shut down the master, upgrade it, restart it, let the slave catch up. ? On 1/12/10 12:34 PM, Lawrence Sorrillo sorri...@jlab.org wrote: Hi: I want to upgrade a master and slave server from mysql 4.1 to mysql 5.1. I want to so something like follows: 1. Stop all write access to the master server. 2. Ensure that replication on the slave is caught up to the last change on the master. 3. stop binary logging on the master. 4. stop replication on the slave. 5. dump the master, stop old 4.1 server, start new 5.1 server and reload master dump file under 5.1 server ( binary logging is turned off) 6. dump the slave, stop old 4.1 server, start new 5.1 server and reload slave dump file under 5.1 server. 7. After loading is complete, test then start binary logging on master while still preventing updates to updates. 8. After loading slave, test then start slave (get configs in place and restart server). I am thinking that in this scenario I dont have to bother with recording binlog file names and position etc etc. That both servers will have the same databases abd replication and binary logging will start on the two databases with no data loss and continue forward. Comments? ~Lawrence -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: upgrading mysql
Lawrence Sorrillo wrote: Hi: I want to upgrade a master and slave server from mysql 4.1 to mysql 5.1. I want to so something like follows: 1. Stop all write access to the master server. ok 2. Ensure that replication on the slave is caught up to the last change on the master. why? You are just going to replace it later. 3. stop binary logging on the master. why? You can just disconnect the slave 4. stop replication on the slave. You can do this at step 2. Just issue STOP SLAVE IO_THREAD; The SQL thread can keep moving along. 5. dump the master, stop old 4.1 server, start new 5.1 server and reload master dump file under 5.1 server ( binary logging is turned off) Yes. No need to create binary logs for the rebuild. 6. dump the slave, stop old 4.1 server, start new 5.1 server and reload slave dump file under 5.1 server. There is a faster way. 7. After loading is complete, test then start binary logging on master while still preventing updates to updates. Once you have QA-ed your new 5.1 master, you can shut it down then copy the entire image (binaries and all) directly to the slave machine. This is much faster than rebuilding from a dump and it ensures that you have identical data to start replication with. After the copy, then restart the master with binary logging. 8. After loading slave, test then start slave (get configs in place and restart server). Yes, it's always good to test any server image before putting it online. The CHANGE MASTER TO command to use for the slave will be at position 4 of the first binary log created after the binary image was captured. I am thinking that in this scenario I dont have to bother with recording binlog file names and position etc etc. That both servers will have the same databases abd replication and binary logging will start on the two databases with no data loss and continue forward. You are correct. Because you are re-imaging your slave from your master, there is no need to track binary log or relay log positions. See also: http://dev.mysql.com/doc/refman/5.1/en/replication-howto-rawdata.html ** SAFETY ADVICE ** - always ensure you have a clean binary backup of any server you want to perform major maintenance to. In the off-chance that something does happen to go wrong, you will have it available for the fastest possible restore-to-original-state -- Shawn Green, MySQL Senior Support Engineer Sun Microsystems, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: upgrading mysql
Hi: I want to ensure that right after the reload that the same data is present in both the master and the slave. They are in perfect sync. Then I think its safe to consider starting binary logging and replication etc. And after these are started, changes can start? And in setting up replication in this manner I would not use the CHANGE MASTER... I will just master-host=xxx.xxx.xxx.xxx master-connect-retry=60 master-user=auser master-password=apassword in the my.cnf file and restart the slave server. From there it should start reading the binary logs and committing changes properly. Is this correct? ~Lawrence Shawn Green wrote: Lawrence Sorrillo wrote: Hi: I want to upgrade a master and slave server from mysql 4.1 to mysql 5.1. I want to so something like follows: 1. Stop all write access to the master server. ok 2. Ensure that replication on the slave is caught up to the last change on the master. why? You are just going to replace it later. 3. stop binary logging on the master. why? You can just disconnect the slave 4. stop replication on the slave. You can do this at step 2. Just issue STOP SLAVE IO_THREAD; The SQL thread can keep moving along. 5. dump the master, stop old 4.1 server, start new 5.1 server and reload master dump file under 5.1 server ( binary logging is turned off) Yes. No need to create binary logs for the rebuild. 6. dump the slave, stop old 4.1 server, start new 5.1 server and reload slave dump file under 5.1 server. There is a faster way. 7. After loading is complete, test then start binary logging on master while still preventing updates to updates. Once you have QA-ed your new 5.1 master, you can shut it down then copy the entire image (binaries and all) directly to the slave machine. This is much faster than rebuilding from a dump and it ensures that you have identical data to start replication with. After the copy, then restart the master with binary logging. 8. After loading slave, test then start slave (get configs in place and restart server). Yes, it's always good to test any server image before putting it online. The CHANGE MASTER TO command to use for the slave will be at position 4 of the first binary log created after the binary image was captured. I am thinking that in this scenario I dont have to bother with recording binlog file names and position etc etc. That both servers will have the same databases abd replication and binary logging will start on the two databases with no data loss and continue forward. You are correct. Because you are re-imaging your slave from your master, there is no need to track binary log or relay log positions. See also: http://dev.mysql.com/doc/refman/5.1/en/replication-howto-rawdata.html ** SAFETY ADVICE ** - always ensure you have a clean binary backup of any server you want to perform major maintenance to. In the off-chance that something does happen to go wrong, you will have it available for the fastest possible restore-to-original-state -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: upgrading mysql
On Jan 12, 2010, at 12:36 PM, Lawrence Sorrillo wrote: Hi: I want to ensure that right after the reload that the same data is present in both the master and the slave. They are in perfect sync. Then I think its safe to consider starting binary logging and replication etc. And after these are started, changes can start? And in setting up replication in this manner I would not use the CHANGE MASTER... I will just master-host=xxx.xxx.xxx.xxx master-connect-retry=60 master-user=auser master-password=apassword in the my.cnf file and restart the slave server. From there it should start reading the binary logs and committing changes properly. Is this correct? You're upgrading to MySQL 5.1, for which several of those options no longer have any effect. Better to use CHANGE MASTER. See: http://dev.mysql.com/doc/refman/5.1/en/replication-options-slave.html http://dev.mysql.com/doc/refman/5.1/en/news-5-1-17.html -- Paul DuBois Sun Microsystems / MySQL Documentation Team Madison, Wisconsin, USA www.mysql.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: upgrading mysql
Frankly, I didn't entirely understand what you were proposing. I got lost around step 6. Is the issue total time for the procedure or service downtime? On 1/12/10 12:58 PM, Lawrence Sorrillo sorri...@jlab.org wrote: This is two upgrades done in sequence(the reload takes about three hours per machine) . I can do what I am proposing in parallel. Do you see it as problematic? ~Lawrence Tom Worster wrote: How about: 1 shut down the slave, upgrade it, restart it, let it catch up. 2 shut down the master, upgrade it, restart it, let the slave catch up. ? On 1/12/10 12:34 PM, Lawrence Sorrillo sorri...@jlab.org wrote: Hi: I want to upgrade a master and slave server from mysql 4.1 to mysql 5.1. I want to so something like follows: 1. Stop all write access to the master server. 2. Ensure that replication on the slave is caught up to the last change on the master. 3. stop binary logging on the master. 4. stop replication on the slave. 5. dump the master, stop old 4.1 server, start new 5.1 server and reload master dump file under 5.1 server ( binary logging is turned off) 6. dump the slave, stop old 4.1 server, start new 5.1 server and reload slave dump file under 5.1 server. 7. After loading is complete, test then start binary logging on master while still preventing updates to updates. 8. After loading slave, test then start slave (get configs in place and restart server). I am thinking that in this scenario I dont have to bother with recording binlog file names and position etc etc. That both servers will have the same databases abd replication and binary logging will start on the two databases with no data loss and continue forward. Comments? ~Lawrence -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: upgrading mysql
Hi, The step 6 in simple terms is Here we need to build two server ( both master and slave ). Instead of building two server as it takes double the time of building in one server. After building an server, make a copy of the first server files at OS level and copy it to the server and start the same. Configure the replication between the two server. By doing this, We will save the import time in second server. Thanks Suresh Kuna MySQL DBA On Wed, Jan 13, 2010 at 3:58 AM, Tom Worster f...@thefsb.org wrote: Frankly, I didn't entirely understand what you were proposing. I got lost around step 6. Is the issue total time for the procedure or service downtime? On 1/12/10 12:58 PM, Lawrence Sorrillo sorri...@jlab.org wrote: This is two upgrades done in sequence(the reload takes about three hours per machine) . I can do what I am proposing in parallel. Do you see it as problematic? ~Lawrence Tom Worster wrote: How about: 1 shut down the slave, upgrade it, restart it, let it catch up. 2 shut down the master, upgrade it, restart it, let the slave catch up. ? On 1/12/10 12:34 PM, Lawrence Sorrillo sorri...@jlab.org wrote: Hi: I want to upgrade a master and slave server from mysql 4.1 to mysql 5.1. I want to so something like follows: 1. Stop all write access to the master server. 2. Ensure that replication on the slave is caught up to the last change on the master. 3. stop binary logging on the master. 4. stop replication on the slave. 5. dump the master, stop old 4.1 server, start new 5.1 server and reload master dump file under 5.1 server ( binary logging is turned off) 6. dump the slave, stop old 4.1 server, start new 5.1 server and reload slave dump file under 5.1 server. 7. After loading is complete, test then start binary logging on master while still preventing updates to updates. 8. After loading slave, test then start slave (get configs in place and restart server). I am thinking that in this scenario I dont have to bother with recording binlog file names and position etc etc. That both servers will have the same databases abd replication and binary logging will start on the two databases with no data loss and continue forward. Comments? ~Lawrence -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=sureshkumar...@gmail.com -- Thanks Suresh Kuna MySQL DBA
Re: upgrading from 4.1 to 5.4
Hi! I don't do DBA work, so my info may be incomplete: monem mysql wrote: Hello I have to upgrade many mysql databases from mysql 4 and 4.1 to 5.4 with a large size 2.7 TB [[...]] The official method takes too much time. But I've read that we can use '*dump and reload'* to upgrade directly to 5.1, will it work with 5.4? I am not aware of any significant difference between 5.1 and 5.4 in the MyISAM area, so there are good chances that experiences with 5.1 are applicable to 5.4 as well. (InnoDB would be a different matter.) Also the tables contain many charset? Will they be altered? Going from 4.0 or 4.1 to 5.1 and up brings you all the changes in charset / collation handling, but there should be no additional difference between 5.1 and 5.4 AFAIK. [[...]] Are there any better solution and any precaution to take? I second Gavin's advice: Do a test migration and check the most important ones of your applications. Pay special attention to strange characters outside the ASCII range. HTH, Jörg -- Joerg Bruehe, MySQL Build Team, joerg.bru...@sun.com Sun Microsystems GmbH, Komturstraße 18a, D-12099 Berlin Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Wolf Frenkel Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB161028 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
upgrading from 4.1 to 5.4
Hello I have to upgrade many mysql databases from mysql 4 and 4.1 to 5.4 with a large size 2.7 TB All tables use the MyISAM engine. I have to make that update on live system with minimal down time possible. The official method takes too much time. But I’ve read that we can use ‘*dump and reload’* to upgrade directly to 5.1, will it work with 5.4? Also the tables contain many charset? Will they be altered? It's first time that I do that. Are there any better solution and any precaution to take? thanks for your help. monem
RE: upgrading from 4.1 to 5.4
Using mysqldump and loading directly into 5.4 *might* work, but you should never do any of this on your production system without testing. Get another box, start with 4.1 and do the upgrade on a test server -- even test your queries as there a few incompatible changes between 4 and 5. One you know the exact steps you need to take, and how much time it takes, then you can plan the upgrade accordingly on your live system. Regards, Gavin Towey -Original Message- From: monem mysql [mailto:monem.my...@gmail.com] Sent: Thursday, October 01, 2009 9:31 AM To: mysql@lists.mysql.com Subject: upgrading from 4.1 to 5.4 Hello I have to upgrade many mysql databases from mysql 4 and 4.1 to 5.4 with a large size 2.7 TB All tables use the MyISAM engine. I have to make that update on live system with minimal down time possible. The official method takes too much time. But I've read that we can use '*dump and reload'* to upgrade directly to 5.1, will it work with 5.4? Also the tables contain many charset? Will they be altered? It's first time that I do that. Are there any better solution and any precaution to take? thanks for your help. monem 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
Upgrading from 5.0.32 via a replication chain and bug 24432
Hi all, I've got a quite large database (23G) that is running on a 5.0.32 version of MySQL. I really want to upgrade out of 5.0.32 to the latest version of 5.1 (or even 5.4) but a straight mysql_upgrade of the database takes long enough that I'd have serious down-time issues (last time I benchmarked the upgrade it came in at over day). To try and work around this I wanted to set up binary replication from my current database (as master) to a new database. This has meant that I have run across bug #24432 (http://bugs.mysql.com/bug.php?id=24432) which means that replication is broken from my current version to versions above 5.0.34. The bug listing includes the following replication table: master (-inf, 5.0.23)[5.0.24, 5.0.34] [5.0.36,+inf) slave (-inf, 5.0.23)BUG#20188 both bugs, no error BUG#20188 [5.0.24, 5.0.34] this bug, no errorno bugthis bug, no error [5.0.36, +inf)no bugthis bug, error no bug which seems to indicate to me that there's no way out of the version I'm running aside from a mysql_upgrade. Does anyone have experience of this bug, or of any options I have that wouldn't require a significant outage ? Cheers Dave -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Upgrading from 5.0.32 via a replication chain and bug 24432
David Harrison wrote: Hi all, I've got a quite large database (23G) that is running on a 5.0.32 version of MySQL. I really want to upgrade out of 5.0.32 to the latest version of 5.1 (or even 5.4) but a straight mysql_upgrade of the database takes long enough that I'd have serious down-time issues (last time I benchmarked the upgrade it came in at over day). This may or may not be useful, but I've just upgraded from 5.0.51 to 5.1.36, which took about 6 hours using mysqldump+reload - the database is about 20Gb. /Per Jessen, Zürich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: upgrading from 4.1 to 5.0 trick
On Fri, Aug 28, 2009 at 9:18 AM, Shawn Green shawn.gr...@sun.com wrote: Hank wrote: Hello All, I'm in the process of upgrading my database from 4.1 to 5.0 on CentOS. I've been testing the mysqlcheck --check-upgrade --auto-repair command, and on one of my MYISAM tables, it's taking forever to upgrade the table. It has about 114 million rows, and I'm guessing it needs to be upgraded due to the VARCHAR columns. Anyway, it's been running for a day and a half, and I finally had to kill it. So will this old trick still work? I've done this many times on 4.1 with great success: In mysql 5.0 - I create two new empty tables, one identical to the original and one identical but with no indexes. I name these tables with _ion and _ioff suffixes. I then do a insert into table_ioff select * from source which inserts just the original data into the new table, but doesn't have to rebuild any indexes. I then flush the tables. Then in the file system, I swap the table_ion.frm and table_ion.MYI files with the table_ioff ones. Flush tables again. I then just use myisamchk -r to repair the index file. It runs in about an hour. Can I do this same thing to upgrade the tables, instead of using mysqlcheck, which seems to be rebuilding the table row-by-row, instead of sorting (which myisamchk does). thanks. -Hank Hello Hank, Your technique will work within the following narrow limits of operation: * This will only work for MyISAM tables. * myisamchk is dangerous to run against any table that is in active use as it operates at the file level and has caused corruptions with live tables. Whenever possible either stop the server or prevent access from MySQL to that table with a FLUSH TABLES WITH READ LOCK before using myisamchk. http://dev.mysql.com/doc/refman/5.0/en/flush.html Alternatively, you should be able to match or improve this import then index process if you use an ALTER TABLE ... DISABLE KEYS command before the import followed by an ALTER TABLE ... ENABLE KEYS command after the import or if you use LOAD DATA INFILE ... . Also if you can import all of the data to an empty table in a single batch (statement), the indexes will be computed only once using the batch-index algorithm (it's a sort, not a merge) and that will also save processing time. http://dev.mysql.com/doc/refman/5.0/en/alter-table.html http://dev.mysql.com/doc/refman/5.0/en/load-data.html http://dev.mysql.com/doc/refman/5.0/en/insert-speed.html The overall problem is still that the on-disk structure of the 5.0 tables has changed and that you still need to perform some kind of dump-restore or rebuild of the data as part of the conversion. Warmest regards, Shawn Green, MySQL Senior Support Engineer Sun Microsystems, Inc. Office: Blountville, TN Hello Shawn, Thanks for your reply. Yes, I have all of your conditions covered. 1. They are myisam tables 2. This is not a production system, so other people aren't accessing the tables. 3. And your last comment about dump/restore is taken care of (in my original note) since I am creating a new table (without indexes) in mysql 5.0, and then inserting all the data from the old table into the new one. Then I'm swapping the MYI/frm files, and then rebuilding the new table. I've tested this several times now, and it works like a charm. Finally, I don't like to use the ALTER TABLE DISABLE/ENABLE statements, since they operate in silent mode -- I have no idea what it's doing, or how long to expect the process to take. It would be very nice of those commands had some built-in progress meter or feedback/callback method.
Re: upgrading from 4.1 to 5.0 trick
Hank wrote: Hello All, I'm in the process of upgrading my database from 4.1 to 5.0 on CentOS. I've been testing the mysqlcheck --check-upgrade --auto-repair command, and on one of my MYISAM tables, it's taking forever to upgrade the table. It has about 114 million rows, and I'm guessing it needs to be upgraded due to the VARCHAR columns. Anyway, it's been running for a day and a half, and I finally had to kill it. So will this old trick still work? I've done this many times on 4.1 with great success: In mysql 5.0 - I create two new empty tables, one identical to the original and one identical but with no indexes. I name these tables with _ion and _ioff suffixes. I then do a insert into table_ioff select * from source which inserts just the original data into the new table, but doesn't have to rebuild any indexes. I then flush the tables. Then in the file system, I swap the table_ion.frm and table_ion.MYI files with the table_ioff ones. Flush tables again. I then just use myisamchk -r to repair the index file. It runs in about an hour. Can I do this same thing to upgrade the tables, instead of using mysqlcheck, which seems to be rebuilding the table row-by-row, instead of sorting (which myisamchk does). thanks. -Hank Hello Hank, Your technique will work within the following narrow limits of operation: * This will only work for MyISAM tables. * myisamchk is dangerous to run against any table that is in active use as it operates at the file level and has caused corruptions with live tables. Whenever possible either stop the server or prevent access from MySQL to that table with a FLUSH TABLES WITH READ LOCK before using myisamchk. http://dev.mysql.com/doc/refman/5.0/en/flush.html Alternatively, you should be able to match or improve this import then index process if you use an ALTER TABLE ... DISABLE KEYS command before the import followed by an ALTER TABLE ... ENABLE KEYS command after the import or if you use LOAD DATA INFILE ... . Also if you can import all of the data to an empty table in a single batch (statement), the indexes will be computed only once using the batch-index algorithm (it's a sort, not a merge) and that will also save processing time. http://dev.mysql.com/doc/refman/5.0/en/alter-table.html http://dev.mysql.com/doc/refman/5.0/en/load-data.html http://dev.mysql.com/doc/refman/5.0/en/insert-speed.html The overall problem is still that the on-disk structure of the 5.0 tables has changed and that you still need to perform some kind of dump-restore or rebuild of the data as part of the conversion. Warmest regards, -- Shawn Green, MySQL Senior Support Engineer Sun Microsystems, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
upgrading from 4.1 to 5.0 trick
Hello All, I'm in the process of upgrading my database from 4.1 to 5.0 on CentOS. I've been testing the mysqlcheck --check-upgrade --auto-repair command, and on one of my MYISAM tables, it's taking forever to upgrade the table. It has about 114 million rows, and I'm guessing it needs to be upgraded due to the VARCHAR columns. Anyway, it's been running for a day and a half, and I finally had to kill it. So will this old trick still work? I've done this many times on 4.1 with great success: In mysql 5.0 - I create two new empty tables, one identical to the original and one identical but with no indexes. I name these tables with _ion and _ioff suffixes. I then do a insert into table_ioff select * from source which inserts just the original data into the new table, but doesn't have to rebuild any indexes. I then flush the tables. Then in the file system, I swap the table_ion.frm and table_ion.MYI files with the table_ioff ones. Flush tables again. I then just use myisamchk -r to repair the index file. It runs in about an hour. Can I do this same thing to upgrade the tables, instead of using mysqlcheck, which seems to be rebuilding the table row-by-row, instead of sorting (which myisamchk does). thanks. -Hank
Re: Upgrading MySQL from 5.0 to 5.1
What I always did since 3.23 upwards is new installation and import. My tecnique allows me to install as many mysql instances as I want, I always use specific user, homedir, datadir, my.cnf, for each installation. In this way I can have theoretically unlimited number of mysql instances on one host. In your case I would install the new 5.1 in a custom home (e.g. /home/mysql5123) use your previous my.cnf from the 5.0 installation to get all previous settings and make it use the same datadir. To force one installation to use ONE AND ONLY ONE my.cnf start the instance with the command line option: (E.g. --defaults-file=/home/mysql5123/my.cnf) in this way no side effects will take place (as long you configure correctly my.cnf to work from the new home) let me know if this is enough, enough clear or just was better for me to shut up! Claudio 2009/3/22 s...@twinix.com See Thread at: http://www.techienuggets.com/Detail?tx=78654 Posted on behalf of a User This has proved to be quite challenging. I wasn't able to do the upgrade and basically created another instance of 5.1 and took a backup of my database from 5.0 and restored it to 5.1. Then added all my users, etc. There's got to be a better way? I couldn't figure out how to get the new version 5.1 to see the file system (containing the database) that was in use by 5.0. I looked at all the documents and nowhere (or at least I couldn't see it) does it talk about upgrading the database. When you install the new version how do you get it ot upgrade and use the 5.0 database? Any pointers/help on this would be great for the next time I have to do this. Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=claudio.na...@gmail.com
Upgrading and screwed up
Trying to upgrade from 5.0.37 to 5.1.32, These are the steps I have taken: 1. Took a dump of the our production database using mysqldump. 2. Downloaded the binary version for i86 Linux and placed it on a Slackware 12 server. 3. Foolishly ran mysql_upgrade against the data (/storage/data/mysql... these are all Inodb) thinking I had started mysqld for the new version. However, it was probably the prior version (5.0.37.) 4. Moved the data directory (/storage/data) to .bak (/storage/data.bak). 5. Unpacked the .gz and changed the linked directory to the new installed directory. 6. Ran the install script (scripts/mysql_install_db --user=root). Yes, I know this is out of sequence, no excuses. 7. Realized I could not make my time deadline for the install, so I started reverting to the previous setup... moved the new data directory to data.new, moved the data.bak to data, moved the new link to mysql.new and the old link (now named mysql.old) back to mysql. 8. Started mysqld (really mysqld_safe) under 5.0.37 and it looked good (I could see the data tables, etc.) When I attempted to start our app, the system complained that it a stored procedure did not exist. Oh-oh. At this point, I can edit the stored procedure (there are a couple of hundred that behave the same way) but not save it. I can see that the stored procedure is in the mysql proc table and it is complete (when I bring the stored procedure up in Navicat, it is missing the paramters which indicates to me that Navicat is using something it has stored because I can see the praramters when I look in the table.) To summarize the problem, mysql does not seem to be able to find/see the stored procedures. Anyone have any ideas? TIA, Carl
Upgrading MySQL from 5.0 to 5.1
See Thread at: http://www.techienuggets.com/Detail?tx=78654 Posted on behalf of a User This has proved to be quite challenging. I wasn't able to do the upgrade and basically created another instance of 5.1 and took a backup of my database from 5.0 and restored it to 5.1. Then added all my users, etc. There's got to be a better way? I couldn't figure out how to get the new version 5.1 to see the file system (containing the database) that was in use by 5.0. I looked at all the documents and nowhere (or at least I couldn't see it) does it talk about upgrading the database. When you install the new version how do you get it ot upgrade and use the 5.0 database? Any pointers/help on this would be great for the next time I have to do this. Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Upgrading
Hi all, I am on... wait for it... version 4.0.25 and I want to upgrade to MySQL 5.x Is there anything special I should do in order to upgrade? Do I need to uninstall v4 or can I just download the most current version and double click to upgrade? I am not particularly hardcore mysql minded, and quite honestly, if it ain't broke don't fix it is a good mantra of mine... but I need to move on now as I have received an sql file that is v5 compatible but not v4 compatible. Once I have it up and running I'll be fine, it's just that I am nervous about upgrading and consequently breaking it, and at that point, I'll be struggling to put it right. Any advice on how I can best do this / best practices etc will be very much appreciated. Many thanks. Mat -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Upgrading
Hi Mat, How many databases have you got running on 4.0? You can certainly go through the motions of downloading each interim release, however my best advice would be (if time/disk space permits) to dump your databases to plain SQL files (using mysqldump) obliterate your 4.0 install, install a fresh copy of 5.1 and restore your database dumps. If your databases are hefty (I'd say above 1GB) you may need to go for an upgrade, in which case I think you need to go to 4.1 before 5.0 if memory serves me correctly. Of course, depending on your setup, you could install a fresh copy of 5.1 and run it alongside 4.0 (ie on different port numbers) then you can migrate your databases/systems across one-by-one. Andy Matthew Stuart wrote: Hi all, I am on... wait for it... version 4.0.25 and I want to upgrade to MySQL 5.x Is there anything special I should do in order to upgrade? Do I need to uninstall v4 or can I just download the most current version and double click to upgrade? I am not particularly hardcore mysql minded, and quite honestly, if it ain't broke don't fix it is a good mantra of mine... but I need to move on now as I have received an sql file that is v5 compatible but not v4 compatible. Once I have it up and running I'll be fine, it's just that I am nervous about upgrading and consequently breaking it, and at that point, I'll be struggling to put it right. Any advice on how I can best do this / best practices etc will be very much appreciated. Many thanks. Mat -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Upgrading
I would strongly suggest logging all your 4.0 queries for at least 24 hours and then running them on your new 5.x server to avoid any surprises such as incompatible queries for example. Good luck! Mihail On Mar 9, 2009, at 1:42 PM, Matthew Stuart wrote: Hi all, I am on... wait for it... version 4.0.25 and I want to upgrade to MySQL 5.x Is there anything special I should do in order to upgrade? Do I need to uninstall v4 or can I just download the most current version and double click to upgrade? I am not particularly hardcore mysql minded, and quite honestly, if it ain't broke don't fix it is a good mantra of mine... but I need to move on now as I have received an sql file that is v5 compatible but not v4 compatible. mysql@lists.mysql.com Once I have it up and running I'll be fine, it's just that I am nervous about upgrading and consequently breaking it, and at that point, I'll be struggling to put it right. Any advice on how I can best do this / best practices etc will be very much appreciated. Many thanks. Mat -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=mmano...@liquidation.com
Re: Upgrading
On Mon, March 9, 2009 12:51, Mihail Manolov wrote: I would strongly suggest logging all your 4.0 queries for at least 24 hours and then running them on your new 5.x server to avoid any surprises such as incompatible queries for example. Good luck! Mihail Good idea. I would pay particular attention to LEFT JOINs. MySQL began more strictly following the SQL specs and that caused me problems when I did the upgrade. Bill On Mar 9, 2009, at 1:42 PM, Matthew Stuart wrote: Hi all, I am on... wait for it... version 4.0.25 and I want to upgrade to MySQL 5.x Is there anything special I should do in order to upgrade? Do I need to uninstall v4 or can I just download the most current version and double click to upgrade? I am not particularly hardcore mysql minded, and quite honestly, if it ain't broke don't fix it is a good mantra of mine... but I need to move on now as I have received an sql file that is v5 compatible but not v4 compatible. mysql@lists.mysql.com Once I have it up and running I'll be fine, it's just that I am nervous about upgrading and consequently breaking it, and at that point, I'll be struggling to put it right. Any advice on how I can best do this / best practices etc will be very much appreciated. Many thanks. Mat -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=mmano...@liquidation.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Upgrading from 4.1 to 5.0
Does anyone have experience with upgrading large databases (~500GB each) from MySQL 4.1 to 5.0? The tables are in InnoDB format. We are using the Community version. I've read that it's recommended that you use mysqldump and then restore, but this is not possible for us, as we cannot have our databases down for long, nor can we have our tables locked while doing dump. I've tried doing the following steps: ibbackup --restore copy over mysql table dirs. set default char set to latin1 (or will default to utf8) in my.cnf because that's the original char set in 4.1 Upgrade only mysql database (user and privilege tables) mysqlcheck --check-upgrade --auto-repair mysql mysql_fix_privilege_tables I've written a script to compare data between the original 4.1 and the new 5.0. Looks like certain rows have different numerical data... so this is not good. I didn't want to do mysql_upgrade on all databases in this instance of MySQL because that resulted in 2 things happening: 1) Don't set default char set to latin1. Run mysql_upgrade Some rows had data truncated in certain columns. 2) Set default char set to latin1. Run mysql_upgrade Copies to TMP table. Takes forever... This is unacceptable for us. What is the recommended way to upgrade from 4.1 to 5.0? Or are we stuck using 4.1 forever? -Paul Choi Plaxo, Inc. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Upgrading from 4.1 to 5.0
You may want to try replication. Setup your replication server as 5.0. That server gives you a chance to play to get things right without affecting the master server. You'll still need to do a dump to get the slave up to speed. Once you get everything right, you can switch over and the slave becomes the master. Very simple in theory, a bit more complicated in practice. Brent Baisley Systems Architect On Apr 23, 2008, at 2:28 PM, Paul Choi wrote: Does anyone have experience with upgrading large databases (~500GB each) from MySQL 4.1 to 5.0? The tables are in InnoDB format. We are using the Community version. I've read that it's recommended that you use mysqldump and then restore, but this is not possible for us, as we cannot have our databases down for long, nor can we have our tables locked while doing dump. I've tried doing the following steps: ibbackup --restore copy over mysql table dirs. set default char set to latin1 (or will default to utf8) in my.cnf because that's the original char set in 4.1 Upgrade only mysql database (user and privilege tables) mysqlcheck --check-upgrade --auto-repair mysql mysql_fix_privilege_tables I've written a script to compare data between the original 4.1 and the new 5.0. Looks like certain rows have different numerical data... so this is not good. I didn't want to do mysql_upgrade on all databases in this instance of MySQL because that resulted in 2 things happening: 1) Don't set default char set to latin1. Run mysql_upgrade Some rows had data truncated in certain columns. 2) Set default char set to latin1. Run mysql_upgrade Copies to TMP table. Takes forever... This is unacceptable for us. What is the recommended way to upgrade from 4.1 to 5.0? Or are we stuck using 4.1 forever? -Paul Choi Plaxo, Inc. -- 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]
Upgrading MySQL 4.0 to 5.0
Has anyone upgraded MySQL 4.0 to 5.0 on a Mac running Mac OS X Panther, 10.3.9 Sever with Lasso 8.5.4? I'm debating whether to just upgrade to MySQL to 5.0 or jump to Apples Leopard Server. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
upgrading mysql on RHEL4
Hi Gurus, I have mysql4.x installed on REHL4. Can anybody let me know how to upgrade it to 5.1. Also please tell me where can i get mysql5.1 enterprise edition. Thanks in advance Siva
Re: upgrading mysql on RHEL4
Hi, Take backup of the existing data before upgrading for safety. There is RHEL 4 specific rpm binary is existing in the downloading section. After installing run the required tools comes with mysql. Before upgrading with existing datas read the documentation carefully. http://dev.mysql.com/doc/refman/5.1/en/installing.html Saravanan --- On Fri, 1/18/08, perl pra [EMAIL PROTECTED] wrote: From: perl pra [EMAIL PROTECTED] Subject: upgrading mysql on RHEL4 To: mysql@lists.mysql.com Date: Friday, January 18, 2008, 10:21 PM Hi Gurus, I have mysql4.x installed on REHL4. Can anybody let me know how to upgrade it to 5.1. Also please tell me where can i get mysql5.1 enterprise edition. Thanks in advance Siva Looking for last minute shopping deals? Find them fast with Yahoo! Search. http://tools.search.yahoo.com/newsearch/category.php?category=shopping -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: upgrading mysql on RHEL4
Hi ! perl pra schrieb: [[...]] Also please tell me where can i get mysql5.1 enterprise edition. 5.1 is currently labeled rc (current version is 5.1.22-rc), so there is no enterprise edition yet. When there will be one, it will be for paying customers, and they have got (or will receive) the download instructions. HTH, Jörg -- Joerg Bruehe, Senior Production Engineer MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Upgrading PHP + MySQL
Hi, I have a CentOS 3 server that I need to update to MySQL 5 + PHP4. I downloaded and installed the MySQL client and server RPM packages for Red Hat 3, including the Shared Compatibility Libraries which provide /usr/lib/libmysqlclient.so.10, .12, .14 and .15, which seems to be the recommended way of upgrading a MySQL 3 installation. I then went on to recompile PHP 4.4.7, which worked fine. But my PHP installation is still using libmysqlclient.so.10, which does not play 100% correctly with the current MySQL 5 server. I'd need it to use libmysqlclient.so.15. Apparently /usr/lib/php4/mysql.so is linked to libmysqlclient.so.10. How do I update/replace/relink the php-mysql connector to have PHP talk to MySQL using the current client libraries? Any hints'd greatly appreciated. Cheers, Dav -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
log files and upgrading
Is it possible to log information to the general log file only for a specific database? We are currently running MySQL 4.0.15. We are planning on moving to a new server so and will upgrade MySQL. What is the latest most stable version that is recommended? Thanks for any information. -- Malka Cymbalista Webmaster, Weizmann Institute of Science [EMAIL PROTECTED] 08-934-3036 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: log files and upgrading
I don't think there is any way to lock down the general log to a single database.. perhaps if you tell us what you are trying to accomplish, we might be able to propose something.. As of today, 5.0.45 is the recommended install version. http://dev.mysql.com/downloads/mysql/5.0.html - michael dykman On 9/20/07, Malka Cymbalista [EMAIL PROTECTED] wrote: Is it possible to log information to the general log file only for a specific database? We are currently running MySQL 4.0.15. We are planning on moving to a new server so and will upgrade MySQL. What is the latest most stable version that is recommended? Thanks for any information. -- Malka Cymbalista Webmaster, Weizmann Institute of Science [EMAIL PROTECTED] 08-934-3036 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- - michael dykman - [EMAIL PROTECTED] - 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/[EMAIL PROTECTED]
Unknown column error after upgrading from 4.0 to 5.0
Since we upgraded from MySQL 4.0 to 5.0 (under OpenBSD 4.1 amd64) the following command: select count(*) as total from products_description pd, products p left join manufacturers m on p.manufacturers_id = m.manufacturers_id, products_to_categories p2c left join specials s on p.products_id = s.products_id where p.products_status = '1' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '1' and p2c.categories_id = '1' give the following error: ERROR 1054 (42S22): Unknown column 'p.products_id' in 'on clause' What's wrong with that command? And why it worked correctly under 4.0? I tried to eliminate the aliases and use directly the real tables names but nothing changed. Obviously the column exists, the following command works: select products.products_id from products Thanks. -- ___ __ |- [EMAIL PROTECTED] |ederico Giannici http://www.neomedia.it ___ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unknown column error after upgrading from 4.0 to 5.0
Try not mixing left join and comma-joins, and use an INNER JOIN keyword between m.manufacturers_id, products_to_categories Baron Federico Giannici wrote: Since we upgraded from MySQL 4.0 to 5.0 (under OpenBSD 4.1 amd64) the following command: select count(*) as total from products_description pd, products p left join manufacturers m on p.manufacturers_id = m.manufacturers_id, products_to_categories p2c left join specials s on p.products_id = s.products_id where p.products_status = '1' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '1' and p2c.categories_id = '1' give the following error: ERROR 1054 (42S22): Unknown column 'p.products_id' in 'on clause' What's wrong with that command? And why it worked correctly under 4.0? I tried to eliminate the aliases and use directly the real tables names but nothing changed. Obviously the column exists, the following command works: select products.products_id from products Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unknown column error after upgrading from 4.0 to 5.0
Hi Frederico, the precedence between the comma-operator and JOIN changed with 5.0.12. See http://dev.mysql.com/doc/refman/5.0/en/join.html Excerpt from that article: Previously, the comma operator (,) and JOIN both had the same precedence, so the join expression t1, t2 JOIN t3 was interpreted as ((t1, t2) JOIN t3). Now JOIN has higher precedence, so the expression is interpreted as (t1, (t2 JOIN t3)). This change affects statements that use an ON clause, because that clause can refer only to columns in the operands of the join, and the change in precedence changes interpretation of what those operands are. Example: CREATE TABLE t1 (i1 INT, j1 INT); CREATE TABLE t2 (i2 INT, j2 INT); CREATE TABLE t3 (i3 INT, j3 INT); INSERT INTO t1 VALUES(1,1); INSERT INTO t2 VALUES(1,1); INSERT INTO t3 VALUES(1,1); SELECT * FROM t1, t2 JOIN t3 ON (t1.i1 = t3.i3); Previously, the SELECT was legal due to the implicit grouping of t1,t2 as (t1,t2). Now the JOIN takes precedence, so the operands for the ON clause are t2 and t3. Because t1.i1 is not a column in either of the operands, the result is an Unknown column 't1.i1' in 'on clause' error. To allow the join to be processed, group the first two tables explicitly with parentheses so that the operands for the ON clause are (t1,t2) and t3: End excerpt. /Johan Federico Giannici skrev: Since we upgraded from MySQL 4.0 to 5.0 (under OpenBSD 4.1 amd64) the following command: select count(*) as total from products_description pd, products p left join manufacturers m on p.manufacturers_id = m.manufacturers_id, products_to_categories p2c left join specials s on p.products_id = s.products_id where p.products_status = '1' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '1' and p2c.categories_id = '1' give the following error: ERROR 1054 (42S22): Unknown column 'p.products_id' in 'on clause' What's wrong with that command? And why it worked correctly under 4.0? I tried to eliminate the aliases and use directly the real tables names but nothing changed. Obviously the column exists, the following command works: select products.products_id from products Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Upgrading mysql questions
Hi, I'm currently running several Joomla websites and phpBB forums on an old FreeBSD server running mysql-server 3.23. Yesterday I upgraded the mysql installation to 4.0.27 successfully. No problems at all. First I made backups, then deinstalled mysql 3.23 and finally installed 4.0.27. I then ran the mysql_fix_privilege_tables script which gave me some warnings, but seemed OK. The test forum and databases started up fine. Soon, I'll upgrade to 4.1 and then later to version 5 of the mysql server. My question is: When upgrading mysql-server and running suggested/included update scripts etc, do they only affect the base mysql server (and associated files)? What about all the databases (Ie. phpBB/Joomla) that was created under 3.23, should I run some sort of upgrading script on these also? Need some advice here. Sounds logic to me that they also need to be updated/optimized for the new system - somehow. I hope someone will be able to assist me a little here. Thanks and best regards, Andreas
Upgrading databases?
Hello, I'm having trouble copying a database from MySQL 4.1.22 to 5.1.19- beta. Both are FreeBSD i386-based machines. I have run the following commands: mysqlcheck --check-upgrade --all-databases --auto-repair mysql_fix_privilege_tables Both executed with no problems. (mysqlcheck reported OK for all tables.) When I try to access any of the tables, I get this: mysql select * from users; ERROR 1034 (HY000): Incorrect key file for table 'users'; try to repair it So I tried to repair it: mysql repair table users; +++-- ++ | Table | Op | Msg_type | Msg_text | +++-- ++ | seth_icsx_mands_live.users | repair | error| Incorrect key file for table 'users'; try to repair it | +++-- ++ 1 row in set, 1 warning (0.10 sec) Running repair table users doesn't seem to have any effect on it because the problem persists. I have tried to run mysql_upgrade, with no success: # mysql_upgrade --basedir=/usr/local --datadir=/var/db --verbose Looking for 'mysql' in: mysql FATAL ERROR: Can't find 'mysql' I have tried it with all different combinations for the two directory options with no luck. All tables are MyISAM. Can anyone shed some light on what I'm supposed to do? Thanks, Seth -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Upgrading databases?
Seth Seeger wrote: Hello, I'm having trouble copying a database from MySQL 4.1.22 to 5.1.19- beta. Both are FreeBSD i386-based machines. I have run the following commands: mysqlcheck --check-upgrade --all-databases --auto-repair mysql_fix_privilege_tables Both executed with no problems. (mysqlcheck reported OK for all tables.) When I try to access any of the tables, I get this: mysql select * from users; ERROR 1034 (HY000): Incorrect key file for table 'users'; try to repair it So I tried to repair it: mysql repair table users; +++-- ++ | Table | Op | Msg_type | Msg_text | +++-- ++ | seth_icsx_mands_live.users | repair | error| Incorrect key file for table 'users'; try to repair it | +++-- ++ 1 row in set, 1 warning (0.10 sec) Running repair table users doesn't seem to have any effect on it because the problem persists. I have tried to run mysql_upgrade, with no success: # mysql_upgrade --basedir=/usr/local --datadir=/var/db --verbose Looking for 'mysql' in: mysql FATAL ERROR: Can't find 'mysql' I have tried it with all different combinations for the two directory options with no luck. All tables are MyISAM. Can anyone shed some light on what I'm supposed to do? Thanks, Seth Shut the server down and run myisamchk on users.MYI -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Upgrading databases?
On Jun 21, 2007, at 12:21 PM, Gerald L. Clark wrote: Seth Seeger wrote: Hello, I'm having trouble copying a database from MySQL 4.1.22 to 5.1.19- beta. Both are FreeBSD i386-based machines. I have run the following commands: mysqlcheck --check-upgrade --all-databases --auto-repair mysql_fix_privilege_tables Both executed with no problems. (mysqlcheck reported OK for all tables.) When I try to access any of the tables, I get this: mysql select * from users; ERROR 1034 (HY000): Incorrect key file for table 'users'; try to repair it So I tried to repair it: mysql repair table users; +++-- ++ | Table | Op | Msg_type | Msg_text | +++-- ++ | seth_icsx_mands_live.users | repair | error| Incorrect key file for table 'users'; try to repair it | +++-- ++ 1 row in set, 1 warning (0.10 sec) Running repair table users doesn't seem to have any effect on it because the problem persists. I have tried to run mysql_upgrade, with no success: # mysql_upgrade --basedir=/usr/local --datadir=/var/db --verbose Looking for 'mysql' in: mysql FATAL ERROR: Can't find 'mysql' I have tried it with all different combinations for the two directory options with no luck. All tables are MyISAM. Can anyone shed some light on what I'm supposed to do? Thanks, Seth Shut the server down and run myisamchk on users.MYI Sadly, no success. I tried running it two different ways: # myisamchk -e -r users - recovering (with sort) MyISAM-table 'users' Data records: 1283 - Fixing index 1 Found block with too small length at 101420; Skipped # myisamchk -c -r users - recovering (with sort) MyISAM-table 'users' Data records: 1283 - Fixing index 1 I still get the same error when I try to access the table. Thanks, Seth -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Upgrading databases?
Hi Seth - I believe MySQL's official position is that you should always dump-and-load the data when upgrading major or minor versions (4.0 to 4.1, 4.1 to 5.0, etc.) I've done it both ways (dump-load and just moving table files) and have never had a problem with either, even when moving files across OS platforms *knock on wood*. That said, I think you might find dump-and-load just the ticket to work around the problem you're having. What you're doing *should* work, but since it isn't, I'd try another avenue myself to avoid spending much more time on it. Something as simple as mysqldump -u root -ppassword -h hostwith41 --all-databases | mysql -u root -ppassword -h hostwith51 would do it, if you want to transfer everything Mind that you've got an appropriate network connection - you wouldn't want to make your laptop on home wireless with DSL the in-between if you have 50GB of data to transfer. Hope this helps, and let me know if you have any questions. Dan On 6/21/07, Seth Seeger [EMAIL PROTECTED] wrote: On Jun 21, 2007, at 12:21 PM, Gerald L. Clark wrote: Seth Seeger wrote: Hello, I'm having trouble copying a database from MySQL 4.1.22 to 5.1.19- beta. Both are FreeBSD i386-based machines. I have run the following commands: mysqlcheck --check-upgrade --all-databases --auto-repair mysql_fix_privilege_tables Both executed with no problems. (mysqlcheck reported OK for all tables.) When I try to access any of the tables, I get this: mysql select * from users; ERROR 1034 (HY000): Incorrect key file for table 'users'; try to repair it So I tried to repair it: mysql repair table users; +++-- ++ | Table | Op | Msg_type | Msg_text | +++-- ++ | seth_icsx_mands_live.users | repair | error| Incorrect key file for table 'users'; try to repair it | +++-- ++ 1 row in set, 1 warning (0.10 sec) Running repair table users doesn't seem to have any effect on it because the problem persists. I have tried to run mysql_upgrade, with no success: # mysql_upgrade --basedir=/usr/local --datadir=/var/db --verbose Looking for 'mysql' in: mysql FATAL ERROR: Can't find 'mysql' I have tried it with all different combinations for the two directory options with no luck. All tables are MyISAM. Can anyone shed some light on what I'm supposed to do? Thanks, Seth Shut the server down and run myisamchk on users.MYI Sadly, no success. I tried running it two different ways: # myisamchk -e -r users - recovering (with sort) MyISAM-table 'users' Data records: 1283 - Fixing index 1 Found block with too small length at 101420; Skipped # myisamchk -c -r users - recovering (with sort) MyISAM-table 'users' Data records: 1283 - Fixing index 1 I still get the same error when I try to access the table. Thanks, Seth -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
upgrading from mysql 4.0 to 4.1 : TIMESTAMP OTHER FEATURES
Hi! I would like to upgrade the database of my organisation from mysql4.0.22 from 4.1. We use Debian. I've read info in the manual, but i don't have things clear. We process data of DB with scripts, and I'm annoyed because the change of format of timestamp, is there any command in 4.1 to obtain this info in the previous format (4.0) ??? I want that out in 4.1 as MMDDHHMMSS Furthermore, my databases are in latin1, i've read that 4.1 take data in utf8, but i don't understand reading manual how this affect to my info. And to finish, do i have to keep more features in mind Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
