From: Mike Franon Date: February 15 2013 3:24pm Subject: Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6 List-Archive: http://lists.mysql.com/mysql/228984 Message-Id: MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable 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 wrote: > Sounds like something that, once discovered, can be fixed in the old vers= ion > -- 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 incompatibilitie= s. > One had to do with NULLs, the other with precedence of commajoin vs expli= cit > JOIN. > > > > From: Singer Wang [mailto:wang@stripped] > Sent: Thursday, February 14, 2013 3:41 PM > To: Rick James > Cc: Mihail Manolov; Mike Franon; Akshay Suryavanshi; > > > 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 issu= e > 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. > > =A7 In MySQL 5.1.23, alias declarations outside the table_references par= t of > the statement were disallowed for theUSING variant of multiple-table DELE= TE > syntax, to reduce the possibility of ambiguous aliases that could lead to > ambiguous statements that have unexpected results such as deleting rows f= rom > the wrong table. > > Now alias declarations outside table_references are disallowed for all > multiple-table DELETE statements. Alias declarations are permitted only i= n > 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; > > =A7 Previously, for alias references in the list of tables from which to > delete rows in a multiple-table delete, the default database is used unle= ss > 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: > > =A7 > > =A7 DELETE a1, a2 FROM db1.t1 AS a1 INNER JOIN db2.t2 AS a2 > > WHERE a1.id=3Da2.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=3Da2.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 wrote: > > Singer, do you have some examples? > > >> -----Original Message----- >> From: Singer Wang [mailto:wang@stripped] >> Sent: Thursday, February 14, 2013 2:59 PM >> To: Mihail Manolov >> Cc: Mike Franon; Akshay Suryavanshi; >> 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.manolov@stripped> 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 >> > > 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 >> >> > 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 >> > >> > > >