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. > >> >> > > > >> >> > > > >> >> > > > >> >> > > > >> >> > > > >> >> > > > >> >> > > > >> >> > > > >> >> > > On Thu, Feb 14, 2013 at 4:22 PM, Akshay Suryavanshi > >> >> > > <akshay.suryavansh...@gmail.com> wrote: > >> >> > >> Mike, > >> >> > >> > >> >> > >> 5.6 is GA now, so its stable release. Also you should not jump > to > >> >> > >> 5.6 directly, atleast from 5.0. > >> >> > >> > >> >> > >> There are many bug fixes and changes in 5.1, so you should > >> >> consider > >> >> > >> this way. > >> >> > >> > >> >> > >> 5.0-->5.1-->5.5 (all slaves first, and then the master) > >> >> > >> > >> >> > >> And further 5.5 --> 5.6 (again all slaves first and then the > >> >> > >> master) > >> >> > >> > >> >> > >> Hope this helps. > >> >> > >> > >> >> > >> Cheers! > >> >> > >> > >> >> > >> On Fri, Feb 15, 2013 at 2:38 AM, Mike Franon > >> >> <kongfra...@gmail.com> > >> >> > wrote: > >> >> > >>> > >> >> > >>> I have 1 master with many slaves, using the master only for > >> >> > >>> inserts and the rest are readers. > >> >> > >>> > >> >> > >>> > >> >> > >>> Is 5.6 stable? Or better off to go to 5.5? > >> >> > >>> > >> >> > >>> If so do I need to make a few steps or can go straight from 5.0 > >> >> to 5.6? > >> >> > >>> > >> >> > >>> > >> >> > >>> Any best practices and recommendations? > >> >> > >>> > >> >> > >>> Thanks > >> >> > >>> > >> >> > >>> -- > >> >> > >>> MySQL General Mailing List > >> >> > >>> For list archives: http://lists.mysql.com/mysql > >> >> > >>> To unsubscribe: http://lists.mysql.com/mysql > >> >> > >>> > >> >> > >> > >> >> > > > >> >> > > -- > >> >> > > MySQL General Mailing List > >> >> > > For list archives: http://lists.mysql.com/mysql > >> >> > > To unsubscribe: http://lists.mysql.com/mysql > >> >> > > > >> >> > > >> >> > > >> >> > -- > >> >> > MySQL General Mailing List > >> >> > For list archives: http://lists.mysql.com/mysql > >> >> > To unsubscribe: http://lists.mysql.com/mysql > >> >> > > >> >> > > >> > > >> > > >> > >> -- > >> MySQL General Mailing List > >> For list archives: http://lists.mysql.com/mysql > >> To unsubscribe: http://lists.mysql.com/mysql > >> > > >
