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.rei...@thelounge.net > <mailto:h.rei...@thelounge.net>> 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 <bmur...@paragon-cs.com > <mailto: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 > <mailto: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 <mailto: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 > <mailto: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 > <mailto: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 > <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 <mailto: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 <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 <rja...@yahoo-inc.com > <mailto:rja...@yahoo-inc.com>> > >>>>>> wrote: > >>>>>> > >>>>>> Singer, do you have some examples? > >>>>>> > >>>>>> > >>>>>>> -----Original Message----- > >>>>>>> From: Singer Wang [mailto:w...@singerwang.com > <mailto:w...@singerwang.com>] > >>>>>>> Sent: Thursday, February 14, 2013 2:59 PM > >>>>>>> To: Mihail Manolov > >>>>>>> Cc: Mike Franon; Akshay Suryavanshi; <mysql@lists.mysql.com > <mailto: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 > <mailto: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
