List:General Discussion« Previous MessageNext Message »
From:Johnny Withers Date:February 15 2013 10:59pm
Subject:Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6
View as plain text  
I can't even imagine an SQL dump of a 400GB database would restore anyway.
How long would that take? 3 weeks?

Might want to dump the data to CSV files and the schema to an SQL file if
you want a full dump/restore.


On Fri, Feb 15, 2013 at 4:54 PM, Reindl Harald <h.reindl@stripped>wrote:

> "our database is 400 GB, mysqldump is 600MB" was not a typo and you
> honestly believed that you can import this dump to somewhat?
>
> WTF - as admin you should be able to see if the things in front
> of you are theoretically possible before your start any action
> and 1:400 is impossible, specially because mysql-dumps are
> ALWAYS WAY LARGER then the databasses because they contain
> sql-statements and not only data
>
> Am 15.02.2013 23:37, schrieb Mike Franon:
> > 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.
>
>


-- 
-----------------------------
Johnny Withers
601.209.4985
johnny@stripped

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