List:General Discussion« Previous MessageNext Message »
From:Akshay Suryavanshi Date:February 15 2013 11:55pm
Subject:Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6
View as plain text  
Harald,

I somewhat dont agree with your statement of mysqldump backup size being
way bigger than the actual datasets, just beacuse its SQL plain text. What
I can tell you is, mysqldump files would be significantly smaller than the
total dataset size, because it doesnt contain "index data". So, if out of
400G, 100G is index data then the dump file should be 300G.

I hope you agree...

Cheers!

On Sat, Feb 16, 2013 at 4:24 AM, 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.
>
>

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