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. >> > >> >> > >> 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
