List:General Discussion« Previous MessageNext Message »
From:Mike Franon Date:February 15 2013 3:24pm
Subject:Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6
View as plain text  
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 <rjames@stripped> 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:wang@stripped]
> Sent: Thursday, February 14, 2013 3:41 PM
> To: Rick James
> Cc: Mihail Manolov; Mike Franon; Akshay Suryavanshi; <mysql@stripped>
>
>
> 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 <rjames@stripped> 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; <mysql@stripped>
>> 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
>> > > <akshay.suryavanshi50@stripped> 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
>> <kongfranon@stripped>
>> > 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
>> >
>> >
>
>
Thread
Upgrading form mysql 5.0.90 to 5.5 or 5.6Mike Franon14 Feb
  • Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6Akshay Suryavanshi14 Feb
    • Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6Mike Franon14 Feb
      • Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6Manuel Arostegui14 Feb
      • Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6Mihail Manolov14 Feb
        • RE: Upgrading form mysql 5.0.90 to 5.5 or 5.6Rick James14 Feb
        • Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6Singer Wang14 Feb
          • RE: Upgrading form mysql 5.0.90 to 5.5 or 5.6Rick James14 Feb
            • Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6Mihail Manolov14 Feb
              • RE: Upgrading form mysql 5.0.90 to 5.5 or 5.6Rick James14 Feb
            • Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6Singer Wang14 Feb
              • RE: Upgrading form mysql 5.0.90 to 5.5 or 5.6Rick James15 Feb
                • Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6Mike Franon15 Feb
                  • Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6Keith Murphy15 Feb
                    • Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6Mike Franon15 Feb
                      • Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6Keith Murphy15 Feb
                        • Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6Mike Franon15 Feb
                          • Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6Reindl Harald15 Feb
                            • Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6Johnny Withers15 Feb
                              • Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6Reindl Harald15 Feb
                            • Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6Akshay Suryavanshi15 Feb
                              • Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6Reindl Harald15 Feb
                            • Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6Manuel Arostegui16 Feb
                              • Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6Reindl Harald16 Feb
                                • Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6Divesh Kamra19 Feb
                                  • Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6Reindl Harald19 Feb
                                    • Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6Divesh Kamra19 Feb
                                      • Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6Reindl Harald19 Feb
                                        • Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6Mike Franon20 Feb
                                          • Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6Reindl Harald20 Feb
                                            • Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6Mike Franon20 Feb
                                              • Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6Mike Franon20 Feb
                                                • Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6Reindl Harald20 Feb
                                                  • Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6Mike Franon20 Feb
                                    • Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6Sabika Makhdoom19 Feb
                      • Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6Reindl Harald15 Feb