From: Reindl Harald Date: February 15 2013 10:54pm Subject: Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6 List-Archive: http://lists.mysql.com/mysql/228990 Message-Id: <511EBC8C.9010604@thelounge.net> MIME-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="----enig2VKHWQCOOCBWUJUESHIMB" ------enig2VKHWQCOOCBWUJUESHIMB Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable "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.. >=20 > Thanks all. >=20 > On Fri, Feb 15, 2013 at 5:03 PM, Keith Murphy = 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 a= t the >> offending line. >> >> Keith >> >> On Feb 15, 2013 3:55 PM, "Mike Franon" 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 ou= r >>> database is 400 GB, mysqldump is 600MB file, about 30 minutes into th= e >>> 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 th= e >>> 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, an= d >>> 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 >>> 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 uncovere= d 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 cent= s. >>>> >>>> Keith >>>> >>>> On Feb 15, 2013 9:27 AM, "Mike Franon" wrote:= >>>>> >>>>> Thanks everyone for suggestions. >>>>> >>>>> I am doing this on a test box with a copy of our db before doing t= his >>>>> 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 i= s >>>>> 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 >>>>> wrote: >>>>>> Sounds like something that, once discovered, can be fixed in the o= ld >>>>>> 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 v= s >>>>>> 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; >>>>>> >>>>>> >>>>>> >>>>>> 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. >>>>>> >>>>>> =A7 In MySQL 5.1.23, alias declarations outside the table_referen= ces >>>>>> part >>>>>> of >>>>>> the statement were disallowed for theUSING variant of multiple-tab= le >>>>>> 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; >>>>>> >>>>>> =A7 Previously, for alias references in the list of tables from w= hich >>>>>> to >>>>>> delete rows in a multiple-table delete, the default database is us= ed >>>>>> unless >>>>>> one is specified explicitly. For example, if the default database = is >>>>>> db1, >>>>>> the following statement does not work because the unqualified alia= s >>>>>> reference a2 is interpreted as having a database of db1: >>>>>> >>>>>> =A7 >>>>>> >>>>>> =A7 DELETE a1, a2 FROM db1.t1 AS a1 INNER JOIN db2.t2 AS a2 >>>>>> >>>>>> WHERE a1.id=3Da2.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=3Da2.id; >>>>>> >>>>>> Now alias resolution does not require qualification and alias >>>>>> references >>>>>> should not be qualified with the database name. Qualified names ar= e >>>>>> interpreted as referring to tables, not aliases. >>>>>> >>>>>> Statements containing alias constructs that are no longer permitte= d >>>>>> must >>>>>> be >>>>>> rewritten. (Bug #27525) >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> On Thu, Feb 14, 2013 at 6:11 PM, Rick James = >>>>>> 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; >>>>>>> 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= =2E5, >>>>>>> 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. ------enig2VKHWQCOOCBWUJUESHIMB Content-Type: application/pgp-signature; name="signature.asc" Content-Description: OpenPGP digital signature Content-Disposition: attachment; filename="signature.asc" -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.13 (GNU/Linux) Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/ iEYEARECAAYFAlEevIwACgkQhmBjz394Anlj2ACeJQA9lEnNFSaHQn7PAVsS/Adt vuUAn0cXfpGysb8p6NwF4vCuY9b6xg/e =2XCp -----END PGP SIGNATURE----- ------enig2VKHWQCOOCBWUJUESHIMB--