List:General Discussion« Previous MessageNext Message »
From:Mike Franon Date:February 15 2013 10:37pm
Subject:Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6
View as plain text  
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 <bmurphy@stripped> 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" <kongfranon@stripped> 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 <bmurphy@stripped>
>> 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" <kongfranon@stripped> 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
> <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
>> >> >> >
>> >> >> >
>> >> >
>> >> >
>> >>
>> >> --
>> >> 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