List:General Discussion« Previous MessageNext Message »
From:Rick James Date:February 15 2013 12:40am
Subject:RE: Upgrading form mysql 5.0.90 to 5.5 or 5.6
View as plain text  
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<http://a1.id>=a2.id<http://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<http://a1.id>=a2.id<http://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<mailto:rjames@stripped>> wrote:
Singer, do you have some examples?

> -----Original Message-----
> From: Singer Wang [mailto:wang@stripped<mailto:wang@strippedm>]
> Sent: Thursday, February 14, 2013 2:59 PM
> To: Mihail Manolov
> Cc: Mike Franon; Akshay Suryavanshi; <mysql@stripped<mailto: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<mailto:mihail.manolov@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.suryavanshi50@stripped<mailto: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@gmail.com<mailto: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