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
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
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
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
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
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
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
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
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
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]
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]
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]
Re: upgrading from mysql 4.0 to 4.1 : TIMESTAMP OTHER FEATURES
Regarding the format of TIMESTAMP columns, one of the user comments on
http://dev.mysql.com/doc/refman/4.1/en/timestamp-4-1.html
offers the solution below:
Posted by Kjell Arne Rekaa on April 14 2005 11:11pm
If you want the same view of a timestamp field in 4.1.x as it was in
in earlier mysql versions, without the delimiter characters in date
and time, simply add a +0 to the column name:
mysql create table date (remember timestamp);
Query OK, 0 rows affected (0.03 sec)
mysql insert date values ('2005051712');
Query OK, 1 row affected (0.00 sec)
mysql select remember from date;
+-+
| remember|
+-+
| 2005-05-17 12:00:00 |
+-+
1 row in set (0.00 sec)
mysql select remember+0 from date;
++
| remember+0 |
++
| 2005051712 |
++
1 row in set (0.00 sec)
As far as your character set, I believe that is stored by table, so
your data should remain OK.
Another major consideration with 4.1 is that passwords changed
considerably from 4.0 to 4.1:
http://dev.mysql.com/doc/refman/4.1/en/password-hashing.html
HTH,
Dan
On 12/20/06, tere [EMAIL PROTECTED] wrote:
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]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: upgrading mysql...
On 8/2/06, bruce [EMAIL PROTECTED] wrote: hi.. i have FC3, with 4.1.13, i also have FC4 with 4.1.20. however, i can't seem to find 5.0.x RPMs for FC3/4. do i have to go ahead and build this from source for the FC3/4 boxes that i have... Linux x86 generic RPM at: http://dev.mysql.com/downloads/mysql/5.0.html It wasnt that hard... -- Daniel da Veiga Computer Operator - RS - Brazil -BEGIN GEEK CODE BLOCK- Version: 3.1 GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V- PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++ --END GEEK CODE BLOCK-- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Upgrading from 3.23.58 5.0.22?
Dan, I haven't seen any other responses, so I'll chime in with my $.02. I think you should have very few problems upgrading from 3.23.58 to 5.0.22. I think you will in fact be able to do pretty much what you describe. I've upgraded in both fashions in the past (re-importing mysqldump output and re-using existing MyISAM data dirs files), and both are pretty straightforward. I've gone 3.23 to 4.0, 4.0 to 4.1, and 4.1 to 5.0, over the course of a few years, all smoothly. I wouldn't hesitate to try a 3.23 direct to 5.0 upgrade either way. If InnoDB were involved I'd be hesitant to upgrade without using mysqldump, due to my own lack of experience with InnoDB. Potential gotchas: 1 - re-importing a mysqldump can take a while. 2 - you'll need a fair bit of disk space available to hold the mysqldump output and the resulting new database files 3 - If you are using any ISAM tables (MyISAM is OK) in 3.23 those could be problematic, as the table type / storage engine will be specified in the mysqldump output, and ISAM is no longer supported. 4 - passwords changed significantly with 4.1, but you can use the old password scheme with 4.1 and 5.0. You just have to know that you need to specify to allow old passwords. http://dev.mysql.com/doc/refman/5.0/en/password-hashing.html HTH, Dan Dan Trainor wrote: Good morning, all - I've read for quite a while tonight, but still haven't been able to figure out - can I upgrade directly from 3.23.58 to 5.0.22? I've read that I'd have to do something like 3.23.58 4.0 4.1 5.0.22, but then also the 'mysql_upgrade' application which, as documented, sounds like it can do magic things. To be quite honest I'd like nothing more than to take a 'mysqldump' of the database, and just re-import that, and run an app such as 'mysql_upgrade' against it and call it good. This will all be done on a pretty recent Linux distribution, CentOS 4.3. Nothing out of the ordinary, but an upgrade such as this one is definitely a bit out of my realm. I was able to do such an upgrade a few weeks ago, but added stuff like permissions by hand - there were only a few. However, for this particular instance, there's 250+ MySQL users. Anyone have any suggestions? Thanks! -dant -- Dan Buettner -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Upgrading from 3.23.58 5.0.22?
On 6/25/06, Dan Trainor [EMAIL PROTECTED] wrote: Good morning, all - I've read for quite a while tonight, but still haven't been able to figure out - can I upgrade directly from 3.23.58 to 5.0.22? I've read that I'd have to do something like 3.23.58 4.0 4.1 5.0.22, but then also the 'mysql_upgrade' application which, as documented, sounds like it can do magic things. I strongly recommend you following this line. There were significant changes between 3, 4, 4.1 and 5. I say that because I found many problems, and 4 to 4.1 were a kinda messy upgrade. Folow: http://dev.mysql.com/doc/refman/5.0/en/upgrade.html http://dev.mysql.com/doc/refman/4.1/en/upgrade.html And you should be fine (I didn't, hehe). To be quite honest I'd like nothing more than to take a 'mysqldump' of the database, and just re-import that, and run an app such as 'mysql_upgrade' against it and call it good. Sorry, I don't think you can do that, the privileges table, for instance, have changed a LOT, so, there's this fix_privileges script on 4.1. This will all be done on a pretty recent Linux distribution, CentOS 4.3. Nothing out of the ordinary, but an upgrade such as this one is definitely a bit out of my realm. I was able to do such an upgrade a few weeks ago, but added stuff like permissions by hand - there were only a few. However, for this particular instance, there's 250+ MySQL users. Anyone have any suggestions? Upgrade to 4, then 4.1, then 5. Follow the above links, its quite fast as you'll only be careful with things your particular system is affected. PS: BACKUP EVERYTHING!!! 2 copies are never too good -- Daniel da Veiga Computer Operator - RS - Brazil -BEGIN GEEK CODE BLOCK- Version: 3.1 GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V- PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++ --END GEEK CODE BLOCK-- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: upgrading problem
Yes Eric You are correct. Thankyou for your reply. It helped me. Bala
Re: upgrading problem
You need root privileges. Login as root or use sudo. E.g. sudo rpm -Uvh MySQL-shared-compat-5.0.19-0.rhel4.i386.rpm If you don't have root or sudo access, you can't perform this upgrade. You could possibly use the --prefix and --relocate rpm install options to install mysql into a directory for which you do have write permissions, but that gets complicated. Eric balaraju mandala wrote: Hi Comunity, I am getting problem while i am upgrading to MySql ver 4 to MySql ver 5. I planned first to install MySQL-shared-compat-5.0.19-0.rhel4.i386.rpm, but i am getting following error. Preparing...### [100%] 1:MySQL-shared-compat### [100%] error: unpacking of archive failed on file /usr/lib/libmysqlclient.so;44361c01: cpio: symlink failed - Permission denied i am unable to understand the problem. Please help me. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Upgrading to 5.0.15
Gary Richardson wrote: We moved directly from 4.0.20 to 5.0.16. Worked like a charm. I had a script that went through and optimized all tables with keys on text/varchar and char fields. We're also slowly ALTERing innodb tables to get them into the new compact format. i tryed to update from 4.1.15 to 5.0.17. it was a disaster... many varchar columns showed corrupted data after the update. can i do anything in mysql 4.1.15 to make the update work or is my only solution a complete dump and insert? best regards chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Upgrading to 5.0.15
Hello. Manual recommends to perform an upgrade step by step. So I suggest you to move to 4.1 at first. When you said 'tablespace' have you meant that you're using InnoDB tables? I'm not sure about them, check the change logs to find out any incompatible changes. In case of MyISAM,very often it is enough just to copy files to the directory of the new server and repair indexes. Tripp Bishop wrote: Howdy all, I've got a MySQL 4.0.24 database that I'd like to upgrade to 5.0.15. Is it possible to backup the tablespace file in the mysql data directory and then install the new database then move the backed up files into the new installations data directory? Are we stuck running a mysqldump script? That would take hours and we'd like to avoid it if at all possible. If not we'll deal with it but it would be nice to just move the files. Thanks, Tripp __ Yahoo! for Good - Make a difference this year. http://brand.yahoo.com/cybergivingweek2005/ -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Upgrading to 5.0.15
We moved directly from 4.0.20 to 5.0.16. Worked like a charm. I had a script that went through and optimized all tables with keys on text/varchar and char fields. We're also slowly ALTERing innodb tables to get them into the new compact format. On 12/28/05, Gleb Paharenko [EMAIL PROTECTED] wrote: Hello. Manual recommends to perform an upgrade step by step. So I suggest you to move to 4.1 at first. When you said 'tablespace' have you meant that you're using InnoDB tables? I'm not sure about them, check the change logs to find out any incompatible changes. In case of MyISAM,very often it is enough just to copy files to the directory of the new server and repair indexes. Tripp Bishop wrote: Howdy all, I've got a MySQL 4.0.24 database that I'd like to upgrade to 5.0.15. Is it possible to backup the tablespace file in the mysql data directory and then install the new database then move the backed up files into the new installations data directory? Are we stuck running a mysqldump script? That would take hours and we'd like to avoid it if at all possible. If not we'll deal with it but it would be nice to just move the files. Thanks, Tripp __ Yahoo! for Good - Make a difference this year. http://brand.yahoo.com/cybergivingweek2005/ -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: upgrading to mysql 5
PaginaDeSpud [EMAIL PROTECTED] wrote on 12/27/2005 03:33:58 PM: hi, I've upgraded from mysql 4.1 to mysql 5 and some queries doesn't work. It's not explained on mysql changes incompatibilities... for example: snip FROM yabbse_topics, yabbse_messages, yabbse_messages as m2 LEFT JOIN yabbse_members AS mem ON (mem.ID_MEMBER=yabbse_messages.ID_MEMBER) LEFT JOIN yabbse_members AS mem2 ON (mem2.ID_MEMBER=m2.ID_MEMBER) LEFT JOIN yabbse_log_topics AS lt ON (lt.ID_TOPIC=yabbse_topics.ID_TOPIC AND lt.ID_MEMBER=2) LEFT JOIN yabbse_log_mark_read AS lmr ON (lmr.ID_BOARD=3 AND lmr.ID_MEMBER=2) WHERE yabbse_topics.ID_TOPIC IN (38562,39516,66603,24732,67085,59867,43884,68331,68330,56897,68090, 68144,68174,68202,68329,68280,68258,68294,68299,68326,68325,67977, 67286,55125,67897,65748,68316,68317,68152,68291,68187,68000,68240, 68237,66796,68271,67486,57293,68027,67332,67990,68089,68072,42517, 68315,63612,66121,68275,68032,68261,68308,67919,68305,68302,68300, 68296,40983,68256,68293,68295,68270,68212,68198,45444,67246,68215, 68264,68208,68133,67017) AND yabbse_messages.ID_MSG=yabbse_topics.ID_LAST_MSG AND m2.ID_MSG=yabbse_topics.ID_FIRST_MSG ORDER BY t.isSticky DESC, yabbse_messages.posterTime DESC ERROR: Unknown column 'yabbse_messages.ID_MEMBER' in 'on clause' snip I don't know which incompatible change list you looked at but it is definitely on the top of this page: http://dev.mysql.com/doc/refman/5.0/en/news-5-0-12.html complete with a link to more information: http://dev.mysql.com/doc/refman/5.0/en/join.html Once you read over that, if you can't find the flaw in your query come back to the list and I or someone else will help point it out to you. Your query has always been broken, it's just that some of the bugs in the SQL engine were eliminated with 5.0.12 so that it evaluates SQL statements more according to the specification and your query cannot be evaluated according to the updated rules. It's like having a bad spell-checker in a word processing appliction. You could go for months using one spelling of a word and never get flagged for it. However if you upgraded the spell-checker it may start flagging you on the same word that used to pass muster in the old version. This is a good thing because as your query becomes more ANSI compliant, it becomes more likely to be used cross-platform and not just on MySQL. As an observation: unless you are using the comma-separated form of creating an implicit CROSS JOIN, you don't get caught by the tightening of the rules. Nobody has posted a question of this same nature to the list that only uses explicit JOIN statements. hmmm. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: upgrading to mysql 5
I've upgraded from mysql 4.1 to mysql 5 and some queries doesn't work. It's not explained on mysql changes incompatibilities... It is: see the first change item, marked 'incompatible change', at http://dev.mysql.com/doc/refman/5.0/en/news-5-0-12.html. We can no longer get away with syntactically loose (SQL2003-incompatible) combinations of commas and JOIN clauses. PB PaginaDeSpud wrote: hi, I've upgraded from mysql 4.1 to mysql 5 and some queries doesn't work. It's not explained on mysql changes incompatibilities... for example: SELECT yabbse_topics.ID_LAST_MSG, yabbse_topics.ID_TOPIC, yabbse_topics.numReplies, yabbse_topics.locked, yabbse_messages.posterName, yabbse_messages.ID_MEMBER, IFNULL(mem.realName, yabbse_messages.posterName) AS posterDisplayName, yabbse_topics.numViews, yabbse_messages.posterTime, yabbse_messages.modifiedTime, yabbse_topics.ID_FIRST_MSG, yabbse_topics.isSticky, yabbse_topics.ID_POLL, m2.posterName as mname, m2.ID_MEMBER as mid, IFNULL(mem2.realName, m2.posterName) AS firstPosterDisplayName, m2.subject as msub, m2.icon as micon, IFNULL(lt.logTime, 0) AS isRead, IFNULL(lmr.logTime, 0) AS isMarkedRead FROM yabbse_topics, yabbse_messages, yabbse_messages as m2 LEFT JOIN yabbse_members AS mem ON (mem.ID_MEMBER=yabbse_messages.ID_MEMBER) LEFT JOIN yabbse_members AS mem2 ON (mem2.ID_MEMBER=m2.ID_MEMBER) LEFT JOIN yabbse_log_topics AS lt ON (lt.ID_TOPIC=yabbse_topics.ID_TOPIC AND lt.ID_MEMBER=2) LEFT JOIN yabbse_log_mark_read AS lmr ON (lmr.ID_BOARD=3 AND lmr.ID_MEMBER=2) WHERE yabbse_topics.ID_TOPIC IN (38562,39516,66603,24732,67085,59867,43884,68331,68330,56897,68090,68144,68174,68202,68329,68280,68258,68294,68299,68326,68325,67977,67286,55125,67897,65748,68316,68317,68152,68291,68187,68000,68240,68237,66796,68271,67486,57293,68027,67332,67990,68089,68072,42517,68315,63612,66121,68275,68032,68261,68308,67919,68305,68302,68300,68296,40983,68256,68293,68295,68270,68212,68198,45444,67246,68215,68264,68208,68133,67017) AND yabbse_messages.ID_MSG=yabbse_topics.ID_LAST_MSG AND m2.ID_MSG=yabbse_topics.ID_FIRST_MSG ORDER BY t.isSticky DESC, yabbse_messages.posterTime DESC ERROR: Unknown column 'yabbse_messages.ID_MEMBER' in 'on clause' show create table yabbse_topics; CREATE TABLE `yabbse_topics` ( `ID_TOPIC` int(11) NOT NULL auto_increment, `ID_BOARD` int(11) NOT NULL default '0', `ID_MEMBER_STARTED` int(11) NOT NULL default '0', `ID_MEMBER_UPDATED` int(11) NOT NULL default '0', `ID_FIRST_MSG` int(11) NOT NULL default '0', `ID_LAST_MSG` int(11) NOT NULL default '0', `ID_POLL` int(11) NOT NULL default '-1', `numReplies` int(11) NOT NULL default '0', `numViews` int(11) NOT NULL default '0', `locked` tinyint(4) NOT NULL default '0', `notifies` text, `isSticky` tinyint(4) NOT NULL default '0', PRIMARY KEY (`ID_TOPIC`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 show create table yabbse_messages; CREATE TABLE `yabbse_messages` ( `ID_MSG` int(11) NOT NULL auto_increment, `ID_TOPIC` int(11) NOT NULL default '0', `ID_MEMBER` int(11) NOT NULL default '0', `subject` tinytext, `posterName` tinytext NOT NULL, `posterEmail` tinytext, `posterTime` bigint(20) default NULL, `posterIP` tinytext NOT NULL, `smiliesEnabled` tinyint(4) NOT NULL default '1', `modifiedTime` bigint(20) default NULL, `modifiedName` tinytext, `body` text, `icon` tinytext, `attachmentSize` mediumint(9) NOT NULL default '0', `attachmentFilename` tinytext, PRIMARY KEY (`ID_MSG`), KEY `ID_TOPIC` (`ID_TOPIC`), KEY `ID_MEMBER` (`ID_MEMBER`), KEY `posterTime` (`posterTime`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.371 / Virus Database: 267.14.7/214 - Release Date: 12/23/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: upgrading to mysql 5
I only saw this changes: http://dev.mysql.com/doc/refman/5.0/en/upgrading-from-4-1.html I've rebuild this query according to the new sql join sintax and works fine. Thanks a lot because i don't know how many days were spent to solve this without your help :) Ivan Lopez. Logosur. - Original Message - From: Peter Brawley [EMAIL PROTECTED] To: PaginaDeSpud [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Tuesday, December 27, 2005 10:29 PM Subject: Re: upgrading to mysql 5 /I've upgraded from mysql 4.1 to mysql 5 and some queries doesn't work. It's not explained on mysql changes incompatibilities... / It is: see the first change item, marked 'incompatible change', at http://dev.mysql.com/doc/refman/5.0/en/news-5-0-12.html. We can no longer get away with syntactically loose (SQL2003-incompatible) combinations of commas and JOIN clauses. PB PaginaDeSpud wrote: hi, I've upgraded from mysql 4.1 to mysql 5 and some queries doesn't work. It's not explained on mysql changes incompatibilities... for example: SELECT yabbse_topics.ID_LAST_MSG, yabbse_topics.ID_TOPIC, yabbse_topics.numReplies, yabbse_topics.locked, yabbse_messages.posterName, yabbse_messages.ID_MEMBER, IFNULL(mem.realName, yabbse_messages.posterName) AS posterDisplayName, yabbse_topics.numViews, yabbse_messages.posterTime, yabbse_messages.modifiedTime, yabbse_topics.ID_FIRST_MSG, yabbse_topics.isSticky, yabbse_topics.ID_POLL, m2.posterName as mname, m2.ID_MEMBER as mid, IFNULL(mem2.realName, m2.posterName) AS firstPosterDisplayName, m2.subject as msub, m2.icon as micon, IFNULL(lt.logTime, 0) AS isRead, IFNULL(lmr.logTime, 0) AS isMarkedRead FROM yabbse_topics, yabbse_messages, yabbse_messages as m2 LEFT JOIN yabbse_members AS mem ON (mem.ID_MEMBER=yabbse_messages.ID_MEMBER) LEFT JOIN yabbse_members AS mem2 ON (mem2.ID_MEMBER=m2.ID_MEMBER) LEFT JOIN yabbse_log_topics AS lt ON (lt.ID_TOPIC=yabbse_topics.ID_TOPIC AND lt.ID_MEMBER=2) LEFT JOIN yabbse_log_mark_read AS lmr ON (lmr.ID_BOARD=3 AND lmr.ID_MEMBER=2) WHERE yabbse_topics.ID_TOPIC IN (38562,39516,66603,24732,67085,59867,43884,68331,68330,56897,68090,68144,68174,68202,68329,68280,68258,68294,68299,68326,68325,67977,67286,55125,67897,65748,68316,68317,68152,68291,68187,68000,68240,68237,66796,68271,67486,57293,68027,67332,67990,68089,68072,42517,68315,63612,66121,68275,68032,68261,68308,67919,68305,68302,68300,68296,40983,68256,68293,68295,68270,68212,68198,45444,67246,68215,68264,68208,68133,67017) AND yabbse_messages.ID_MSG=yabbse_topics.ID_LAST_MSG AND m2.ID_MSG=yabbse_topics.ID_FIRST_MSG ORDER BY t.isSticky DESC, yabbse_messages.posterTime DESC ERROR: Unknown column 'yabbse_messages.ID_MEMBER' in 'on clause' show create table yabbse_topics; CREATE TABLE `yabbse_topics` ( `ID_TOPIC` int(11) NOT NULL auto_increment, `ID_BOARD` int(11) NOT NULL default '0', `ID_MEMBER_STARTED` int(11) NOT NULL default '0', `ID_MEMBER_UPDATED` int(11) NOT NULL default '0', `ID_FIRST_MSG` int(11) NOT NULL default '0', `ID_LAST_MSG` int(11) NOT NULL default '0', `ID_POLL` int(11) NOT NULL default '-1', `numReplies` int(11) NOT NULL default '0', `numViews` int(11) NOT NULL default '0', `locked` tinyint(4) NOT NULL default '0', `notifies` text, `isSticky` tinyint(4) NOT NULL default '0', PRIMARY KEY (`ID_TOPIC`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 show create table yabbse_messages; CREATE TABLE `yabbse_messages` ( `ID_MSG` int(11) NOT NULL auto_increment, `ID_TOPIC` int(11) NOT NULL default '0', `ID_MEMBER` int(11) NOT NULL default '0', `subject` tinytext, `posterName` tinytext NOT NULL, `posterEmail` tinytext, `posterTime` bigint(20) default NULL, `posterIP` tinytext NOT NULL, `smiliesEnabled` tinyint(4) NOT NULL default '1', `modifiedTime` bigint(20) default NULL, `modifiedName` tinytext, `body` text, `icon` tinytext, `attachmentSize` mediumint(9) NOT NULL default '0', `attachmentFilename` tinytext, PRIMARY KEY (`ID_MSG`), KEY `ID_TOPIC` (`ID_TOPIC`), KEY `ID_MEMBER` (`ID_MEMBER`), KEY `posterTime` (`posterTime`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.371 / Virus Database: 267.14.7/214 - Release Date: 12/23/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Upgrading from 4.0 to 4.1
When mysql had trouble starting up what error messages did it give in the .err file in the datadir? -Eric James Tu wrote: I had to migrate from 4.0.11 to 4.1.12. I saved the data directory from the older version. I also exported all the information using phpMyAdmin's export tool. I have one huge .sql file. When I upgraded to 4.1.12, I followed instructions to copy the contents of the old data directory to the new one. This didn't work...MySQL had trouble starting up. So, I restored the new data folder, and ran the .sql file to recreate all the table and to insert all the new records. This works fine. What is the easiest way to upgrade and migrate the data? Did I do something wrong by just copying the contents of the data directory? Should that method work? -James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: upgrading mysql on RH fedora core 3
hey... i now have FC2 running mysql-4.1.10a, php5, apache 2.0.51. for some reason, the mysql rpms that i got from rpmfind.net/rpm.pbone, etc... didn't seem to work, as i kept getting weird lib related errors... i started using mysql-3.23 on FC2. i basically followed the path/instructions from http://www.whoopis.com/howtos/php5-mysql4-FC3-rpm.html, replacing my system/requirements where applicable. basic steps: 1) use 'rpm -qa | grep -i mysql' to get all rpms dealing with mysql 2) remove these rpms 'rpm -e ...' 3) get the replacement rpms from www.mysql.com 4) upgrade using 'rpm -Uvh ' 5) resolve any errors/issues... for my system, i only needed to focus on the mysql, as i already had php5 up/running. i chose this approach, as i didn't want to deal with rebuilding from source at this time. good luck!! -Original Message- From: Florin Andrei [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 29, 2005 8:40 PM To: mysql@lists.mysql.com Subject: Re: upgrading mysql on RH fedora core 3 On Tue, 29 Mar 2005 19:28:56 -0800, bruce [EMAIL PROTECTED] wrote: we're trying to install mysql/mysql-server (4.1.10a-1.i386) and are running into some serious problems. we had mysql/server (3.23.52-3.i386) running, but needed to go to the higher version... I was about to attempt the same thing. My thinking was to grab the mysql src.rpm from Fedora Core 4 test 1 and rebuild it on FC3: rpmbuild --rebuild mysql...src.rpm Please try that and see how it goes. also, this has to be running with apache/php/perl/etc... My feeling is that apache does not need to be rebuilt. PHP certainly does, from src.rpm, after the new mysql is installed (including mysql-devel). I am not sure whether it would just work to grab the PHP src.rpm from FC3 updates and rebuild it on top of the new mysql, or get the PHP src.rpm from FC4-test and rebuild. Perl is in the same situation, but it's probably even more complex. If i were you, i would probably post on the fedora-test mailing list and ask the same question. Anyway, good luck and let us know how it goes. -- Florin Andrei http://florin.myip.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: upgrading mysql on RH fedora core 3
On Tue, 29 Mar 2005 19:28:56 -0800, bruce [EMAIL PROTECTED] wrote: we're trying to install mysql/mysql-server (4.1.10a-1.i386) and are running into some serious problems. we had mysql/server (3.23.52-3.i386) running, but needed to go to the higher version... I was about to attempt the same thing. My thinking was to grab the mysql src.rpm from Fedora Core 4 test 1 and rebuild it on FC3: rpmbuild --rebuild mysql...src.rpm Please try that and see how it goes. also, this has to be running with apache/php/perl/etc... My feeling is that apache does not need to be rebuilt. PHP certainly does, from src.rpm, after the new mysql is installed (including mysql-devel). I am not sure whether it would just work to grab the PHP src.rpm from FC3 updates and rebuild it on top of the new mysql, or get the PHP src.rpm from FC4-test and rebuild. Perl is in the same situation, but it's probably even more complex. If i were you, i would probably post on the fedora-test mailing list and ask the same question. Anyway, good luck and let us know how it goes. -- Florin Andrei http://florin.myip.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: upgrading mysql on RH fedora core 3
bruce wrote: hi... we're trying to install mysql/mysql-server (4.1.10a-1.i386) and are running into some serious problems. we had mysql/server (3.23.52-3.i386) running, but needed to go to the higher version... can someone tell us how/what we need to do to get this working correctly. actually, if anybody's managed to do this, can you tell us exactly what rpm packages you used? as you know, dealing with the rpms gets into dependency hell, which we believe has a lot to do with our issues... if you managed to get this version of mysql running on FC3, and you built it from source, can you provide directions/pointers on what you did, where you placed the resulting libs/etc... also, this has to be running with apache/php/perl/etc... thanks bruce I would strongly suggest backing up your databases first :) I would also ( if possible ) do the import and testing on another PC, while the production server continues. You may have to recompile php and Perl's DBI drivers, or you *may* be able to find some RPMs that will just 'drop in', but I don't use rpms so I couldn't say. The DBI drivers aren't marked as stable yet on my distro ( Gentoo ), so be careful there. If you use ODBC, you will have to upgrade to the latest version of MyODBC - which has some bugs so scan the MyODBC mailing list first. -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: upgrading mysql on RH fedora core 3
On Tuesday, March 29, 2005 21:29, bruce wrote: hi... we're trying to install mysql/mysql-server (4.1.10a-1.i386) and are running into some serious problems. we had mysql/server (3.23.52-3.i386) running, but needed to go to the higher version... can someone tell us how/what we need to do to get this working correctly. actually, if anybody's managed to do this, can you tell us exactly what rpm packages you used? as you know, dealing with the rpms gets into dependency hell, which we believe has a lot to do with our issues... if you managed to get this version of mysql running on FC3, and you built it from source, can you provide directions/pointers on what you did, where you placed the resulting libs/etc... also, this has to be running with apache/php/perl/etc... thanks bruce I use mysql primarily on RHEL3, but I just upgraded a FC2 box just to try it. I was able to upgrade it using the rpm's from dev.mysql.com. I did notice one thing. The rpm install didn't seem to kill the old mysqld properly, so I got an access denied right after the install. I ran the following as root: #killall mysqld #service mysql start After that I was then able to connect just fine. As far as perl, php, and apache, everything will continue to work without modification if you continue to use the old password hashes. You can force this. Refer to http://dev.mysql.com/doc/mysql/en/old-client.html, specifically the old-password option. To make perl work with the new passwords, all you need to do is build DBD::mysql from source. To do this, uninstall the dbd-mysql rpm if you have it installed. I can't remember the exact name because I don't use it. Then as root run: #perl -e shell -MCPAN cpaninstall DBD::mysql Regards, -- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: upgrading mysql on RH fedora core 3
On Tue, 29 Mar 2005 20:39:54 -0800, Florin Andrei [EMAIL PROTECTED] wrote: On Tue, 29 Mar 2005 19:28:56 -0800, bruce [EMAIL PROTECTED] wrote: we're trying to install mysql/mysql-server (4.1.10a-1.i386) and are running into some serious problems. we had mysql/server (3.23.52-3.i386) running, but needed to go to the higher version... I was about to attempt the same thing. My thinking was to grab the mysql src.rpm from Fedora Core 4 test 1 and rebuild it on FC3: rpmbuild --rebuild mysql...src.rpm Hey, whaddayaknow, it worked! :-) I just rebuilt the FC4t1 mysql src.rpm on FC3. It's just that i cannot try it, not today. I'll see if i can play with it tomorrow. Ideally, i'd like to get a spare system, nuke all it's content, do a fresh minimal install of FC3 (unselect all package categories except development), apply all updates (yum update), rebuild mysql-4, install it, then play with Perl and PHP and see if they need to be rebuilt as well (hopefully not). -- Florin Andrei http://florin.myip.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: upgrading problems
Hello. Please, search in MySQL archives (at lists.mysql.com/mysql) about successful solutions to your problem. You may use --focre --nodeps flags for the rpm command. [snip] I want to upgrade mysql client 3.23. to mysql 4.1.10 But when I type rpm -Uvh mysql-client-4.1...rpm It's says that some file from install mysqlrpm conflict with file from package mysql3.23 I try to uninstall package mysql3.23 with the rpm on the cd, but it's doesn't work I have Red hat Enterprise Linus AS How can I fix itGuillaume Chartrand [EMAIL PROTECTED] wrote: [snip] -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Upgrading to MySQL 4.1X Win32 Horror
zzapper wrote: None of your db apps will now run! It's something to do with a new password algorithm You are supposed to be able to fix this by specifying old passwords in your new my.ini You are also advised to upgrade Perl,PHP, Drivers etc This issue has been raised basically every day in this forum and the MyODBC forum - sometimes a couple of times a day!!! It does pay to do *some* research before installing a new version of anything, to see if there are going to be any issues!!! You certainly wouldn't have had to look far to stumble across this one!!! !!! -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Upgrading mySql from 3.23 to 4.1.10
Hello. Install MySQL-client-4.1.10-0.i386.rpm. Troy Richard [EMAIL PROTECTED] wrote: I have upgraded from mysql 3.23 to 4.1.10 everything seems to be working. I'm trying to run the mysql_fix_privilege_tables script to update the privileges and I'm getting the following error: /usr/bin/mysql_fix_privilege_tables: line 185: /usr/bin/mysql: No such file or directory /usr/bin/mysql existed before I upgraded. I'm not sure what happen to it. I just downloaded the rpms and ran the upgraded. Files downloaded: MySQL-server-4.1.10-0.i386.rpm MySQL-shared-compat-4.1.10-0.i386.rpm Command: rpm -Uvh MySQL* Any help would be a big hand, Thanks Troy -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Upgrading from 3.23.57 to 4.1.9 using mysqldump
Hello.
Try --complete-insert command line option for mysqldump. See:
http://dev.mysql.com/doc/mysql/en/mysqldump.html
[EMAIL PROTECTED] wrote:
Hi,
I just tried to upgrade from mysql 3.23.57 (SPARC/Solaris) to 4.1.9
(i386/Linux) using mysqldump but when importing the dump on the new machine
mysql gives the error:
ERROR 1136 (21S01) at line NNN: Column count doesn't match value count at row
1
and in line NNN you find
INSERT INTO `db` VALUES
('localhost','dbxxx','','Y','Y','Y','Y','Y','Y','N','Y','Y','Y'),...
so he is right. The table 'db' has more entries in 4.1.9 than in 3.23.57! But
what is the correct way to upgrade? (NO! It is not possible for me to upgrade
directly on the 3.23.57 machine!)
MANY thanx in advance,
Frank
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Upgrading from 3.23.57 to 4.1.9 using mysqldump
Modify the insert statement so that you name the column you're inserting
to.
INSERT INTO `db` (put the column names in here)
VALUES('localhost','dbxxx','','Y','Y','Y','Y','Y','Y','N','Y','Y','Y'),.
..
Kevin Cowley
RD
Tel: 0118 902 9099 (direct line)
Email: [EMAIL PROTECTED]
Web: http://www.alchemetrics.co.uk
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: 10 February 2005 12:08
To: mysql@lists.mysql.com
Subject: Upgrading from 3.23.57 to 4.1.9 using mysqldump
Hi,
I just tried to upgrade from mysql 3.23.57 (SPARC/Solaris) to 4.1.9
(i386/Linux) using mysqldump but when importing the dump on the new
machine mysql gives the error:
ERROR 1136 (21S01) at line NNN: Column count doesn't match value count
at
row 1
and in line NNN you find
INSERT INTO `db` VALUES
('localhost','dbxxx','','Y','Y','Y','Y','Y','Y','N','Y','Y','Y'),...
so he is right. The table 'db' has more entries in 4.1.9 than in
3.23.57!
But what is the correct way to upgrade? (NO! It is not possible for me
to
upgrade directly on the 3.23.57 machine!)
MANY thanx in advance,
Frank
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
**
ALCHEMETRICS LIMITED (ALCHEMETRICS)
Mulberry Park, Fishponds Road, Wokingham, Berkshire, RG41 2GX
Tel: +44 (0) 118 902 9000Fax: +44 (0) 118 902 9001
This e-mail is confidential and is intended for the use of the addressee only.
If you are not the intended recipient, you are hereby notified that you must
not use, copy, disclose, otherwise disseminate or take any action based on
this e-mail or any information herein.
If you receive this transmission in error, please notify the sender
immediately by reply e-mail or by using the contact details above and then
delete this e-mail.
Please note that e-mail may be susceptible to data corruption, interception
and unauthorised amendment. Alchemetrics does not accept any liability for
any such corruption, interception, amendment or the consequences thereof.
**
--
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.8 to 4.1.9
I'm using Fedora Core 3 and just did an upgrade from 4.1.7 to 4.1.9, using Linux AMD64 rpms from the MySQL web site. It looks like everything went well. For me, the hard part was backing up my databases with mysqldump -- see my comments under the thread MySQL upgrading on this list. I shut down the MySQL server using 'mysqladmin shutdown', then upgraded the client package, followed by the server, followed by the shared and devel packages. Then I re-built PHP 5.0.3 to take in the new MySQL client stuff. It all seems to work well. Of course, your mileage may vary. (I'd like to use RHEL some day myself.) Bob Cochran Shaun T. Erickson wrote: I'd like to upgrade my RHEL ES v3 u4 server from MySQL 4.1.8 to 4.1.9. It's using rpms from the MySQL site, and the new ones will be from there as well. Assuming I shut down MySQL and any apps that use it, is it as simple as upgrading the rpms? Will the apps be affected by the change? -ste -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Upgrading to MySQL 5 (for testing)
Hello. Yes, if you have one of the last releases of MySQL, you can try to test the fifth version. But some features will be unavailable. See: http://dev.mysql.com/doc/mysql/en/Upgrading-from-4.1.html Jonathan Villa [EMAIL PROTECTED] wrote: I have an installation of 4.1 and 5 running on the same test server... I want to start using 5 for testing but still want to use the same test data I had before... Is it possible to simply point MySQL 5 to the old 4 data dir? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Upgrading from 3.23.58 to 4.1
Joshua J. Kugler wrote: Take a look at http://mysql.he.net/doc/mysql/en/Upgrade.html Hmmm... Can 4.1 mirror a 3.2 server? That might be a good deal easier. -- A. Clausen [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Upgrading 3.23 to 4.1
Hello. perror 13 Error code 13: Permission denied Check if you have necessary permissions on your data directory. Aaron E. Diehl [EMAIL PROTECTED] wrote: Hello All, I'm having trouble upgrading 3.23. to 4.1. Since I don't want to break production, I'm trying to start a test instance on the machine. The = problem I'm having seems to be a missing .frm file. The following details invocation and the log file. Any help would be greatly appreciated. Thanks in advance, Aaron /opt/csw/mysql4/bin/mysqld_safe --basedir=3D/opt/csw/mysql4 --datadir=3D/opt/csw/mysql4/data --port=3D3307 Log File: 041206 11:19:53 mysqld started 041206 11:19:53 InnoDB: Started 041206 11:19:53 Fatal error: Can't open privilege tables: Can't find = file: './mysql/host.frm' (errno: 13) 041206 11:19:54 Aborting 041206 11:19:54 InnoDB: Starting shutdown... 041206 11:19:56 InnoDB: Shutdown completed 041206 11:19:56 /opt/csw/mysql4/libexec/mysqld: Shutdown Complete 041206 11:19:56 mysqld ended -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Upgrading from 3.23.58 to 4.1
The 'gotchas' are listed in the manul for 3.23 to 4.0 here: http://dev.mysql.com/doc/mysql/en/Upgrading-from-3.23.html and from 4.0 to 4.1 here: http://dev.mysql.com/doc/mysql/en/Upgrading-from-4.0.html On Tue, 07 Dec 2004 16:01:51 -0800, A. Clausen [EMAIL PROTECTED] wrote: I've been running, with great success, version 3.23.58 on my Win2k box for quite a while now, but am interested in moving up to 4.1. Is there any incompatiblities between the two versions, or any gotchas to the upgrade? -- A. Clausen [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Eric Bergen [EMAIL PROTECTED] http://www.bleated.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Upgrading from 3.23.58 to 4.1
Take a look at http://mysql.he.net/doc/mysql/en/Upgrade.html j- k- On Tuesday 07 December 2004 15:01, A. Clausen said something like: I've been running, with great success, version 3.23.58 on my Win2k box for quite a while now, but am interested in moving up to 4.1. Is there any incompatiblities between the two versions, or any gotchas to the upgrade? -- A. Clausen [EMAIL PROTECTED] -- Joshua J. Kugler -- Fairbanks, Alaska -- ICQ#:13706295 Every knee shall bow, and every tongue confess, in heaven, on earth, and under the earth, that Jesus Christ is LORD -- Count on it! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Upgrading bundled ver of mysql included with php 4.3.1
Stembridge, Michael wrote: I have tried recompiling php 4.3.1 without --with-mysql on the configure line. Doing so did not remove the builtin 3.23.49 package. You need to run configure with the new MySQL directory explicitly specified, e.g. ./configure --with-mysql=/usr/local/mysql_4.1.7 HTH, -- Hassan Schroeder - [EMAIL PROTECTED] Webtuitive Design === (+1) 408-938-0567 === http://webtuitive.com dream. code. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Upgrading MySql on OSX 10.3.6
The directory mysql is a symbolic link to one of the other dirs. If mysql points to old one rename it and make a new one: mv mysql mysqlold ls -s mysql-max-4.1.7-apple-darwin7.5.0-powerpc mysql Do not remove mysql-max-4.0.20-apple-darwin7.3.0-powerpc/data it contains your old databases!!! Santino At 11:17 -0600 10-11-2004, [EMAIL PROTECTED] wrote: I was running ver 4.0.15 and just upgraded to the latest version of 4.1.7. In between I had done an upgrade to ver. 4.0.20. I can get the server to start and stop via the Preferences panel but before and after any upgrade it indicates that 4.0.15 is running. In Navicat which I use as the gui front end it also indicates that 4.0.15 is running. In /usr/local I have 3 MySql related directories: mysql mysql-max-4.1.7-apple-darwin7.5.0-powerpc mysql-max-4.0.20-apple-darwin7.3.0-powerpc My question is how do I get Navicat and the preferences panel to recognize 4.1.7 and do I need to remove 4.0.20 and 4.0.15? Thanks. Kevin -- 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]
