List:General Discussion« Previous MessageNext Message »
From:Reindl Harald Date:February 15 2013 11:59pm
Subject:Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6
View as plain text  
not really

* it is unlikely that you have 1:4 relations key/data
* you have sql-statement overhead even for tinyint 1
* you have overhead to escape data

Am 16.02.2013 00:55, schrieb Akshay Suryavanshi:
> 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.
> 
> On Sat, Feb 16, 2013 at 4:24 AM, Reindl Harald <h.reindl@stripped
> <mailto: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
> <mailto: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
> <mailto: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 <mailto: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
> <mailto: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 <mailto: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
> <mailto:wang@stripped>]
>     >>>>>> Sent: Thursday, February 14, 2013 3:41 PM
>     >>>>>> To: Rick James
>     >>>>>> Cc: Mihail Manolov; Mike Franon; Akshay Suryavanshi;
>     >>>>>> <mysql@stripped
> <mailto: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@stripped>]
>     >>>>>>> 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@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.


Attachment: [application/pgp-signature] OpenPGP digital signature signature.asc
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