"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 <bmur...@paragon-cs.com> 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" <kongfra...@gmail.com> 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 <bmur...@paragon-cs.com> >>> 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" <kongfra...@gmail.com> 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 <rja...@yahoo-inc.com> >>>>> 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:w...@singerwang.com] >>>>>> Sent: Thursday, February 14, 2013 3:41 PM >>>>>> To: Rick James >>>>>> Cc: Mihail Manolov; Mike Franon; Akshay Suryavanshi; >>>>>> <mysql@lists.mysql.com> >>>>>> >>>>>> >>>>>> 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 <rja...@yahoo-inc.com> >>>>>> wrote: >>>>>> >>>>>> Singer, do you have some examples? >>>>>> >>>>>> >>>>>>> -----Original Message----- >>>>>>> From: Singer Wang [mailto:w...@singerwang.com] >>>>>>> Sent: Thursday, February 14, 2013 2:59 PM >>>>>>> To: Mihail Manolov >>>>>>> Cc: Mike Franon; Akshay Suryavanshi; <mysql@lists.mysql.com> >>>>>>> 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.mano...@liquidation.com> 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.
signature.asc
Description: OpenPGP digital signature
