List:General Discussion« Previous MessageNext Message »
From:Reindl Harald Date:February 15 2013 10:54pm
Subject:Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6
View as plain text  
"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.


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