From: Reindl Harald Date: February 15 2013 11:59pm Subject: Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6 List-Archive: http://lists.mysql.com/mysql/228994 Message-Id: <511ECBF9.4000908@thelounge.net> MIME-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="----enig2TNEKUSAFJPEKDWAVSXWL" ------enig2TNEKUSAFJPEKDWAVSXWL Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable 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, >=20 > I somewhat dont agree with your statement of mysqldump backup size bein= g way bigger than the actual datasets, just > beacuse its SQL plain text. What I can tell you is, mysqldump files wou= ld be significantly smaller than the total > dataset size, because it doesnt contain "index data". So, if out of 400= G, 100G is index data then the dump file > should be 300G. >=20 > On Sat, Feb 16, 2013 at 4:24 AM, Reindl Harald > wrote: >=20 > "our database is 400 GB, mysqldump is 600MB" was not a typo and you= > honestly believed that you can import this dump to somewhat? >=20 > 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 >=20 > 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 > wrote: > >> Something doesn't add up. If the data set is 400 GB then your du= mp 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" > 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 m= ind our > >>> database is 400 GB, mysqldump is 600MB file, about 30 minutes i= nto 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 tabl= es, 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 > > >>> 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 un= covered as > >>>> it > >>>> moves out to a wider audience. > >>>> > >>>> Upgrade to 5.5 (through 5.1) first as it is quite proven. Slav= e 5.6 off > >>>> it > >>>> and test. Be patient. Save yourself some heartache. Just my tw= o cents. > >>>> > >>>> 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 d= oing 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 data= base 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 instea= d? > >>>>> > >>>>> On Thu, Feb 14, 2013 at 7:40 PM, Rick James > > >>>>> 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 comma= join 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; > >>>>>> > > >>>>>> > >>>>>> > >>>>>> 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 i= t was an > >>>>>> issue > >>>>>> at my last job. It basically involved multi-table deletes an= d > >>>>>> aliasing.. > >>>>>> I > >>>>>> quote the change notes for MySQL 5.5.3 > >>>>>> > >>>>>> > >>>>>> > >>>>>> Incompatible Change: Several changes were made to alias reso= lution in > >>>>>> multiple-table DELETE statements so that it is no longer pos= sible to > >>>>>> have > >>>>>> inconsistent or ambiguous table aliases. > >>>>>> > >>>>>> =A7 In MySQL 5.1.23, alias declarations outside the table_r= eferences > >>>>>> part > >>>>>> of > >>>>>> the statement were disallowed for theUSING variant of multip= le-table > >>>>>> DELETE > >>>>>> syntax, to reduce the possibility of ambiguous aliases that = could > >>>>>> lead > >>>>>> to > >>>>>> ambiguous statements that have unexpected results such as de= leting > >>>>>> rows > >>>>>> from > >>>>>> the wrong table. > >>>>>> > >>>>>> Now alias declarations outside table_references are disallow= ed for > >>>>>> all > >>>>>> multiple-table DELETE statements. Alias declarations are per= mitted > >>>>>> 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 which > >>>>>> to > >>>>>> delete rows in a multiple-table delete, the default database= is used > >>>>>> unless > >>>>>> one is specified explicitly. For example, if the default dat= abase is > >>>>>> db1, > >>>>>> the following statement does not work because the unqualifie= d alias > >>>>>> 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 t= he > >>>>>> 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 alia= s > >>>>>> references > >>>>>> should not be qualified with the database name. Qualified na= mes are > >>>>>> interpreted as referring to tables, not aliases. > >>>>>> > >>>>>> Statements containing alias constructs that are no longer pe= rmitted > >>>>>> 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.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 ha= ve > >>>>>>>> without > >>>>>>>> any issues. There are some configuration file change, whic= h you > >>>>>>>> may > >>>>>>>> want to consider checking. I definitely recommend upgradin= g your > >>>>>>>> development servers for an extensive testing. Some queries= _may_ > >>>>>>>> run > >>>>>>>> slower or not work at all and you may have to rearrange ho= w 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 thi= s 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 upgr= ade to > >>>>>>>>> 5.6 > >>>>>>>>> and test. ------enig2TNEKUSAFJPEKDWAVSXWL 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/ iEYEARECAAYFAlEey/kACgkQhmBjz394AnnUpACfR8bJPvvgo3J1ss+6c2mB1hMI Z7AAnjFHpPc5JByzRUxrpuOQOlDsxP3x =m/yr -----END PGP SIGNATURE----- ------enig2TNEKUSAFJPEKDWAVSXWL--