Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6

[Mike Franon]

going form 5.1 - to 5.5 was easy, I did not have to dump any tabels or
triggers, just upgraded binary, ran mysql_upgrade and worked in no
time.

Thanks everyone for the help!

On Wed, Feb 20, 2013 at 2:33 PM, Reindl Harald  wrote:
> fine and much faster and probably safer too :-)
>
> a backup with rsync is faster as dump/import and
> can be done with minimize downtime by use it
> twice, the first time hot-backup with running
> server and the second time after stop server
> to get the diffs
>
> doing rsync -> stop -> rsync -> start in a script may
> reduce the downtime to a few seconds
>
> Am 20.02.2013 20:29, schrieb Mike Franon:
>> OK I got it to work.
>>
>> I dumped the tables that it was complaining about first, and then
>> dumped the triggers.
>>
>> I then uninstalled anything to do with mysql, and installed 5.1
>>
>> Then imported the tables and triggers, and and able to run
>> mysql_upgrade without any errors.
>>
>> This is all without using a full mysqldump.
>>
>> I am now going to go from 5.1 to 5.5
>>
>>
>>
>> On Wed, Feb 20, 2013 at 12:54 PM, Mike Franon  wrote:
>>> I am pretty sure I did, and when I did I got the following errors:
>>>
>>>
>>> Error: Table Upgrade Required, Please dump/reload to fix it
>>>
>>>
>>> I got that on 10 tables, and also got the following:
>>>
>>> Warning:  Triggers for table ' have no creation context.
>>>
>>> I think it has to do with no triggers.
>>>
>>>
>>> I know hen I ran the mysql_upgrade it tired to auto repair but did not
>>> work and failed.
>>>
>>> But I will give it a shot again, maybe I missed something.  The other
>>> thing I was thinking was maybe I can just mysqldump those 10 tables
>>> that it fails on, and just restore those instead of my entire db.
>>>
>>>
>>> On Wed, Feb 20, 2013 at 12:43 PM, Reindl Harald  
>>> wrote:


 Am 20.02.2013 18:26, schrieb Mike Franon:
> So I did a full mysqldump over the weekend for a second time and this
> time it is 220GB, no clue what happened last time, I should have
> realized looking at the file size something was wrong, but since I got
> no errors did not think about it, and this time I timed it, took 7
> hours to do a complete mysqldump
>
> Restoring it is not fun 18+ hours and counting, at this rate it will
> be a week, there has to be a better way of doing this, and this is
> only going form 5.0 to 5.1
>
> I know some are saying don't need to do a mysqldump, but if i don't do
> it, the upgrade errors out on 10 tables, and then gives me errors
> about triggers

 and did you ALWAYS "mysql_upgarde -root -p" after ANY mysql-update?
 at least before try a major upgrade?

 did you try "mysqlcheck -h localhost --check-upgrade --all-databases 
 --auto-repair --user=root -p"
 BEFORE the upgrade? did you try it ALSo after the upgrade?

 sorry, i do not believe that dump/import is needed and idoubt
 it will not give better results
>

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6

[Reindl Harald]

fine and much faster and probably safer too :-)

a backup with rsync is faster as dump/import and
can be done with minimize downtime by use it
twice, the first time hot-backup with running
server and the second time after stop server
to get the diffs

doing rsync -> stop -> rsync -> start in a script may
reduce the downtime to a few seconds

Am 20.02.2013 20:29, schrieb Mike Franon:
> OK I got it to work.
> 
> I dumped the tables that it was complaining about first, and then
> dumped the triggers.
> 
> I then uninstalled anything to do with mysql, and installed 5.1
> 
> Then imported the tables and triggers, and and able to run
> mysql_upgrade without any errors.
> 
> This is all without using a full mysqldump.
> 
> I am now going to go from 5.1 to 5.5
> 
> 
> 
> On Wed, Feb 20, 2013 at 12:54 PM, Mike Franon  wrote:
>> I am pretty sure I did, and when I did I got the following errors:
>>
>>
>> Error: Table Upgrade Required, Please dump/reload to fix it
>>
>>
>> I got that on 10 tables, and also got the following:
>>
>> Warning:  Triggers for table ' have no creation context.
>>
>> I think it has to do with no triggers.
>>
>>
>> I know hen I ran the mysql_upgrade it tired to auto repair but did not
>> work and failed.
>>
>> But I will give it a shot again, maybe I missed something.  The other
>> thing I was thinking was maybe I can just mysqldump those 10 tables
>> that it fails on, and just restore those instead of my entire db.
>>
>>
>> On Wed, Feb 20, 2013 at 12:43 PM, Reindl Harald  
>> wrote:
>>>
>>>
>>> Am 20.02.2013 18:26, schrieb Mike Franon:
 So I did a full mysqldump over the weekend for a second time and this
 time it is 220GB, no clue what happened last time, I should have
 realized looking at the file size something was wrong, but since I got
 no errors did not think about it, and this time I timed it, took 7
 hours to do a complete mysqldump

 Restoring it is not fun 18+ hours and counting, at this rate it will
 be a week, there has to be a better way of doing this, and this is
 only going form 5.0 to 5.1

 I know some are saying don't need to do a mysqldump, but if i don't do
 it, the upgrade errors out on 10 tables, and then gives me errors
 about triggers
>>>
>>> and did you ALWAYS "mysql_upgarde -root -p" after ANY mysql-update?
>>> at least before try a major upgrade?
>>>
>>> did you try "mysqlcheck -h localhost --check-upgrade --all-databases 
>>> --auto-repair --user=root -p"
>>> BEFORE the upgrade? did you try it ALSo after the upgrade?
>>>
>>> sorry, i do not believe that dump/import is needed and idoubt
>>> it will not give better results



signature.asc
Description: OpenPGP digital signature

Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6

[Mike Franon]

OK I got it to work.

I dumped the tables that it was complaining about first, and then
dumped the triggers.

I then uninstalled anything to do with mysql, and installed 5.1

Then imported the tables and triggers, and and able to run
mysql_upgrade without any errors.

This is all without using a full mysqldump.

I am now going to go from 5.1 to 5.5



On Wed, Feb 20, 2013 at 12:54 PM, Mike Franon  wrote:
> I am pretty sure I did, and when I did I got the following errors:
>
>
> Error: Table Upgrade Required, Please dump/reload to fix it
>
>
> I got that on 10 tables, and also got the following:
>
> Warning:  Triggers for table ' have no creation context.
>
> I think it has to do with no triggers.
>
>
> I know hen I ran the mysql_upgrade it tired to auto repair but did not
> work and failed.
>
> But I will give it a shot again, maybe I missed something.  The other
> thing I was thinking was maybe I can just mysqldump those 10 tables
> that it fails on, and just restore those instead of my entire db.
>
>
> On Wed, Feb 20, 2013 at 12:43 PM, Reindl Harald  
> wrote:
>>
>>
>> Am 20.02.2013 18:26, schrieb Mike Franon:
>>> So I did a full mysqldump over the weekend for a second time and this
>>> time it is 220GB, no clue what happened last time, I should have
>>> realized looking at the file size something was wrong, but since I got
>>> no errors did not think about it, and this time I timed it, took 7
>>> hours to do a complete mysqldump
>>>
>>> Restoring it is not fun 18+ hours and counting, at this rate it will
>>> be a week, there has to be a better way of doing this, and this is
>>> only going form 5.0 to 5.1
>>>
>>> I know some are saying don't need to do a mysqldump, but if i don't do
>>> it, the upgrade errors out on 10 tables, and then gives me errors
>>> about triggers
>>
>> and did you ALWAYS "mysql_upgarde -root -p" after ANY mysql-update?
>> at least before try a major upgrade?
>>
>> did you try "mysqlcheck -h localhost --check-upgrade --all-databases 
>> --auto-repair --user=root -p"
>> BEFORE the upgrade? did you try it ALSo after the upgrade?
>>
>> sorry, i do not believe that dump/import is needed and idoubt
>> it will not give better results
>>

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6

[Mike Franon]

I am pretty sure I did, and when I did I got the following errors:


Error: Table Upgrade Required, Please dump/reload to fix it


I got that on 10 tables, and also got the following:

Warning:  Triggers for table ' have no creation context.

I think it has to do with no triggers.


I know hen I ran the mysql_upgrade it tired to auto repair but did not
work and failed.

But I will give it a shot again, maybe I missed something.  The other
thing I was thinking was maybe I can just mysqldump those 10 tables
that it fails on, and just restore those instead of my entire db.


On Wed, Feb 20, 2013 at 12:43 PM, Reindl Harald  wrote:
>
>
> Am 20.02.2013 18:26, schrieb Mike Franon:
>> So I did a full mysqldump over the weekend for a second time and this
>> time it is 220GB, no clue what happened last time, I should have
>> realized looking at the file size something was wrong, but since I got
>> no errors did not think about it, and this time I timed it, took 7
>> hours to do a complete mysqldump
>>
>> Restoring it is not fun 18+ hours and counting, at this rate it will
>> be a week, there has to be a better way of doing this, and this is
>> only going form 5.0 to 5.1
>>
>> I know some are saying don't need to do a mysqldump, but if i don't do
>> it, the upgrade errors out on 10 tables, and then gives me errors
>> about triggers
>
> and did you ALWAYS "mysql_upgarde -root -p" after ANY mysql-update?
> at least before try a major upgrade?
>
> did you try "mysqlcheck -h localhost --check-upgrade --all-databases 
> --auto-repair --user=root -p"
> BEFORE the upgrade? did you try it ALSo after the upgrade?
>
> sorry, i do not believe that dump/import is needed and idoubt
> it will not give better results
>

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6

[Mike Franon]

So I did a full mysqldump over the weekend for a second time and this
time it is 220GB, no clue what happened last time, I should have
realized looking at the file size something was wrong, but since I got
no errors did not think about it, and this time I timed it, took 7
hours to do a complete mysqldump



Restoring it is not fun 18+ hours and counting, at this rate it will
be a week, there has to be a better way of doing this, and this is
only going form 5.0 to 5.1

I know some are saying don't need to do a mysqldump, but if i don't do
it, the upgrade errors out on 10 tables, and then gives me errors
about triggers






On Tue, Feb 19, 2013 at 6:34 PM, Reindl Harald  wrote:
>
>
> Am 19.02.2013 23:53, schrieb Divesh Kamra:
>> Hi Reindi
>>
>>
>> Thanks for solution .
>>
>> Can u share complete steps ?
>
> which steps?
>
> * update
> * call "mysql_upgrade -u root -p"
>
> in doubt "mysqlcheck -h localhost --check-upgrade --all-databases 
> --auto-repair --user=root -p"
>
> and if you do "mysql_upgrade -u root -p" and are always
> up-to-date that was it, no matter if you move your data
> from windows to MacOSX and finally to linux or whatever OS
>
>> On 20-Feb-2013, at 2:50, Reindl Harald  wrote:
>>
>>> surely
>>>
>>> * use "mysql_upgrade -u root -p" after EACH update
>>> * upgrade regulary
>>>
>>> we went from MySQL 3.x to 5.5.30 until know without
>>> any dump and here are around 5000 tables
>>>
>>> Am 19.02.2013 22:12, schrieb Divesh Kamra:
 Is there any better way for grade MySQL version without taking backup with 
 mysqldump

 Or if there any tool for this

 R's
 DK

 On 16-Feb-2013, at 16:07, Reindl Harald  wrote:
>
> Am 16.02.2013 09:42, schrieb Manuel Arostegui:
>> 2013/2/15 Reindl Harald > >
>>
>>   "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
>>
>> That's not completely true. If you have a poor maintained database or 
>> just tables with lot of writes and deletes
>> and you don't periodically optimize it - you can end up with lot of 
>> blank spaces in your tables which will use _a
>> lot_ of space. If you do a "du" or whatever to measure your database 
>> size...you can get really confused.
>> mysqldump obviously doesn't backup blank spaces and once you get rid of 
>> them, your database will use much less space.
>
> ok, normally i expect there is a admin and doing his job
> especially for large datasets
>>>
>
> --
>
> Reindl Harald
> the lounge interactive design GmbH
> A-1060 Vienna, Hofmühlgasse 17
> CTO / CISO / Software-Development
> p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40
> icq: 154546673, http://www.thelounge.net/
>
> http://www.thelounge.net/signature.asc.what.htm
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6

[Reindl Harald]

Am 20.02.2013 18:26, schrieb Mike Franon:
> So I did a full mysqldump over the weekend for a second time and this
> time it is 220GB, no clue what happened last time, I should have
> realized looking at the file size something was wrong, but since I got
> no errors did not think about it, and this time I timed it, took 7
> hours to do a complete mysqldump
> 
> Restoring it is not fun 18+ hours and counting, at this rate it will
> be a week, there has to be a better way of doing this, and this is
> only going form 5.0 to 5.1
> 
> I know some are saying don't need to do a mysqldump, but if i don't do
> it, the upgrade errors out on 10 tables, and then gives me errors
> about triggers

and did you ALWAYS "mysql_upgarde -root -p" after ANY mysql-update?
at least before try a major upgrade?

did you try "mysqlcheck -h localhost --check-upgrade --all-databases 
--auto-repair --user=root -p"
BEFORE the upgrade? did you try it ALSo after the upgrade?

sorry, i do not believe that dump/import is needed and idoubt
it will not give better results



signature.asc
Description: OpenPGP digital signature

Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6

[Divesh Kamra]

Hi all


Is there any better way for grade MySQL version without taking backup with 
mysqldump

Or if there any tool for this 

R's
DK

On 16-Feb-2013, at 16:07, Reindl Harald  wrote:

> 
> 
> Am 16.02.2013 09:42, schrieb Manuel Arostegui:
>> 2013/2/15 Reindl Harald > >
>> 
>>"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
>> 
>> That's not completely true. If you have a poor maintained database or just 
>> tables with lot of writes and deletes
>> and you don't periodically optimize it - you can end up with lot of blank 
>> spaces in your tables which will use _a
>> lot_ of space. If you do a "du" or whatever to measure your database 
>> size...you can get really confused.
>> mysqldump obviously doesn't backup blank spaces and once you get rid of 
>> them, your database will use much less space.
> 
> ok, normally i expect there is a admin and doing his job
> especially for large datasets
> 

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6

[Reindl Harald]

Am 19.02.2013 23:53, schrieb Divesh Kamra:
> Hi Reindi
> 
> 
> Thanks for solution .
> 
> Can u share complete steps ? 

which steps?

* update
* call "mysql_upgrade -u root -p"

in doubt "mysqlcheck -h localhost --check-upgrade --all-databases --auto-repair 
--user=root -p"

and if you do "mysql_upgrade -u root -p" and are always
up-to-date that was it, no matter if you move your data
from windows to MacOSX and finally to linux or whatever OS

> On 20-Feb-2013, at 2:50, Reindl Harald  wrote:
> 
>> surely
>>
>> * use "mysql_upgrade -u root -p" after EACH update
>> * upgrade regulary
>>
>> we went from MySQL 3.x to 5.5.30 until know without
>> any dump and here are around 5000 tables
>>
>> Am 19.02.2013 22:12, schrieb Divesh Kamra:
>>> Is there any better way for grade MySQL version without taking backup with 
>>> mysqldump
>>>
>>> Or if there any tool for this 
>>>
>>> R's
>>> DK
>>>
>>> On 16-Feb-2013, at 16:07, Reindl Harald  wrote:

 Am 16.02.2013 09:42, schrieb Manuel Arostegui:
> 2013/2/15 Reindl Harald  >
>
>   "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
>
> That's not completely true. If you have a poor maintained database or 
> just tables with lot of writes and deletes
> and you don't periodically optimize it - you can end up with lot of blank 
> spaces in your tables which will use _a
> lot_ of space. If you do a "du" or whatever to measure your database 
> size...you can get really confused.
> mysqldump obviously doesn't backup blank spaces and once you get rid of 
> them, your database will use much less space.

 ok, normally i expect there is a admin and doing his job
 especially for large datasets
>>

-- 

Reindl Harald
the lounge interactive design GmbH
A-1060 Vienna, Hofmühlgasse 17
CTO / CISO / Software-Development
p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40
icq: 154546673, http://www.thelounge.net/

http://www.thelounge.net/signature.asc.what.htm



signature.asc
Description: OpenPGP digital signature

Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6

[Divesh Kamra]

Hi Reindi


Thanks for solution .

Can u share complete steps ? 

R's
DK

On 20-Feb-2013, at 2:50, Reindl Harald  wrote:

> surely
> 
> * use "mysql_upgrade -u root -p" after EACH update
> * upgrade regulary
> 
> we went from MySQL 3.x to 5.5.30 until know without
> any dump and here are around 5000 tables
> 
> Am 19.02.2013 22:12, schrieb Divesh Kamra:
>> Is there any better way for grade MySQL version without taking backup with 
>> mysqldump
>> 
>> Or if there any tool for this 
>> 
>> R's
>> DK
>> 
>> On 16-Feb-2013, at 16:07, Reindl Harald  wrote:
>>> 
>>> Am 16.02.2013 09:42, schrieb Manuel Arostegui:
 2013/2/15 Reindl Harald >>> >
 
   "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
 
 That's not completely true. If you have a poor maintained database or just 
 tables with lot of writes and deletes
 and you don't periodically optimize it - you can end up with lot of blank 
 spaces in your tables which will use _a
 lot_ of space. If you do a "du" or whatever to measure your database 
 size...you can get really confused.
 mysqldump obviously doesn't backup blank spaces and once you get rid of 
 them, your database will use much less space.
>>> 
>>> ok, normally i expect there is a admin and doing his job
>>> especially for large datasets
> 

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6

[Sabika Makhdoom]

Use replication as your fail over and why not percona's xtrabackup or lvm type 
backup if you need a backup?

Sabika 


On Feb 19, 2013, at 1:20 PM, Reindl Harald  wrote:

> surely
> 
> * use "mysql_upgrade -u root -p" after EACH update
> * upgrade regulary
> 
> we went from MySQL 3.x to 5.5.30 until know without
> any dump and here are around 5000 tables
> 
> Am 19.02.2013 22:12, schrieb Divesh Kamra:
>> Is there any better way for grade MySQL version without taking backup with 
>> mysqldump
>> 
>> Or if there any tool for this 
>> 
>> R's
>> DK
>> 
>> On 16-Feb-2013, at 16:07, Reindl Harald  wrote:
>>> 
>>> Am 16.02.2013 09:42, schrieb Manuel Arostegui:
 2013/2/15 Reindl Harald >>> >
 
   "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
 
 That's not completely true. If you have a poor maintained database or just 
 tables with lot of writes and deletes
 and you don't periodically optimize it - you can end up with lot of blank 
 spaces in your tables which will use _a
 lot_ of space. If you do a "du" or whatever to measure your database 
 size...you can get really confused.
 mysqldump obviously doesn't backup blank spaces and once you get rid of 
 them, your database will use much less space.
>>> 
>>> ok, normally i expect there is a admin and doing his job
>>> especially for large datasets
> 

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6

[Reindl Harald]

surely

* use "mysql_upgrade -u root -p" after EACH update
* upgrade regulary

we went from MySQL 3.x to 5.5.30 until know without
any dump and here are around 5000 tables

Am 19.02.2013 22:12, schrieb Divesh Kamra:
> Is there any better way for grade MySQL version without taking backup with 
> mysqldump
> 
> Or if there any tool for this 
> 
> R's
> DK
> 
> On 16-Feb-2013, at 16:07, Reindl Harald  wrote:
>>
>> Am 16.02.2013 09:42, schrieb Manuel Arostegui:
>>> 2013/2/15 Reindl Harald >> >
>>>
>>>"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
>>>
>>> That's not completely true. If you have a poor maintained database or just 
>>> tables with lot of writes and deletes
>>> and you don't periodically optimize it - you can end up with lot of blank 
>>> spaces in your tables which will use _a
>>> lot_ of space. If you do a "du" or whatever to measure your database 
>>> size...you can get really confused.
>>> mysqldump obviously doesn't backup blank spaces and once you get rid of 
>>> them, your database will use much less space.
>>
>> ok, normally i expect there is a admin and doing his job
>> especially for large datasets



signature.asc
Description: OpenPGP digital signature

Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6

[Reindl Harald]

Am 16.02.2013 09:42, schrieb Manuel Arostegui:
> 2013/2/15 Reindl Harald  >
> 
> "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
> 
> That's not completely true. If you have a poor maintained database or just 
> tables with lot of writes and deletes
> and you don't periodically optimize it - you can end up with lot of blank 
> spaces in your tables which will use _a
> lot_ of space. If you do a "du" or whatever to measure your database 
> size...you can get really confused.
> mysqldump obviously doesn't backup blank spaces and once you get rid of them, 
> your database will use much less space. 

ok, normally i expect there is a admin and doing his job
especially for large datasets



signature.asc
Description: OpenPGP digital signature

Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6

[Manuel Arostegui]

2013/2/15 Reindl Harald 

> "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


That's not completely true. If you have a poor maintained database or just
tables with lot of writes and deletes and you don't periodically optimize
it - you can end up with lot of blank spaces in your tables which will use
_a lot_ of space. If you do a "du" or whatever to measure your database
size...you can get really confused.
mysqldump obviously doesn't backup blank spaces and once you get rid of
them, your database will use much less space.

I have seen this scenario many times and I have seen tables using like 30GB
disk space and after an "optimize" their reported disk size would be just
5-10GB.

Manuel.

Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6

[Reindl Harald]

gt;> 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  <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;
> >>>>>> 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;
> 

Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6

[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.

I hope you agree...

Cheers!

On Sat, Feb 16, 2013 at 4:24 AM, Reindl Harald 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 
> 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"  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  >
> >>> 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"  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 
> >>>>> wrote:
> >>>>>> Sounds like something that, once discovered, can be fixed in the old
> >>>>>> version
> &

Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6

[Reindl Harald]

well, that is why i never in my life will dump and import
large databases - never, for no money on the world

backups of whole servers are done with replication and
restored with rsync if needed but why in the world would
someone export large datasets with dependencies to a PLAIN
TEXTFILE and pray this is becoming a consistent database
on any target?

bseides the fact it takes years to import huge data from
dumps - how do you make sure they are 100% clean after that

Am 15.02.2013 23:59, schrieb Johnny Withers:
> I can't even imagine an SQL dump of a 400GB database would restore anyway.
> How long would that take? 3 weeks?
> 
> Might want to dump the data to CSV files and the schema to an SQL file if
> you want a full dump/restore.
> 
> On Fri, Feb 15, 2013 at 4:54 PM, Reindl Harald 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 
>> 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"  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 >>
>>>>> 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"  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 wh

Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6

[Johnny Withers]

I can't even imagine an SQL dump of a 400GB database would restore anyway.
How long would that take? 3 weeks?

Might want to dump the data to CSV files and the schema to an SQL file if
you want a full dump/restore.


On Fri, Feb 15, 2013 at 4:54 PM, Reindl Harald 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 
> 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"  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  >
> >>> 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"  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 
> >>>>> wrote:
> >>>>>> Sounds like something that, once discovered, can be fixed in the old
> >>>>>> version
> >>>>>> -- then it works correctly in both.
> >>>>>>
> >>>>>>
> >>>>>>
> >>>>>> That is wh

Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6

[Reindl Harald]

"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  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"  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 
>>> 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"  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 
>>>>> 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;
>>>>>> 
>>>>

Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6

[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 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"  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 
>> 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"  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 
>> >> 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;
>> >> > 
>> >> >
>> >> >
>> >> > 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
>

Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6

[Keith Murphy]

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"  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 
> 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"  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 
> 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;
> >> > 
> >> >
> >> >
> >> > 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 

Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6

[Reindl Harald]

Am 15.02.2013 22:55, schrieb Mike Franon:
> 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

i have never in my life used a dumpfile and i am coming
from mysql 3.3 while all machines was migrated to 5.5 with
all steps between and around 50 mysql-instances coming from
the same clones originally installed on Windows, later moved
to MacOSX and since 2008 running on fedora Linux

i have even done downgrades from MySQL 6.0 alpha years ago
to 5.0 without any dump and problems except verify and change
the scheme of the mysqld database (users and permsissions)

did you run "mysql_upgrade" after EACH update of your server
and if not why?

P.S.: nobofy which a working brain would NOW upgrade to the
first MySQL 5.6 release in production



signature.asc
Description: OpenPGP digital signature

Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6

[Mike Franon]

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  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"  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  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;
>> > 
>> >
>> >
>> > 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 exa

Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6

[Keith Murphy]

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"  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  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 
> wrote:
> >
> > Singer, do you have some examples?
> >
>

Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6

[Mike Franon]

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  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; 
>
>
> 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  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; 
>> 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

Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6

[Mihail Manolov]

The ones that didn't work for me required table rearrangement in the query. 
MySQL 5.5 was very particular about the table join order.

On Feb 14, 2013, at 6:11 PM, Rick James 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; 
>> 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
>>>>  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
>> 
>>> 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

Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6

[Singer Wang]

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
> >  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 
> 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
>
>

Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6

[Mihail Manolov]

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
>  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  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

Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6

[Singer Wang]

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  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; 
> > 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
> > > >  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
> > 
> > > wrote:
> > > >>>
> > > >>> I 

RE: Upgrading form mysql 5.0.90 to 5.5 or 5.6

[Rick James]

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; 
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 
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; 
> 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.
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > > On Thu, Feb 14, 2013 at 4:22 PM, Akshay Suryavanshi
> > > mailto: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.

RE: Upgrading form mysql 5.0.90 to 5.5 or 5.6

[Rick James]

Are you saying there was a regression in the Optimizer?

(Sounds like a workaround is to do STRAIGHT_JOIN -- yuck!)

I compared several hundred "slow" queries on 5.1 versus MariaDB 5.5.  I found 
several improvements.

> -Original Message-
> From: Mihail Manolov [mailto:mihail.mano...@liquidation.com]
> Sent: Thursday, February 14, 2013 3:30 PM
> To: Rick James
> Cc: Singer Wang; Mike Franon; Akshay Suryavanshi;
> 
> Subject: Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6
> 
> The ones that didn't work for me required table rearrangement in the
> query. MySQL 5.5 was very particular about the table join order.
> 
> On Feb 14, 2013, at 6:11 PM, Rick James 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; 
> >> 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
> >>>>  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
> >> 
> >>> 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

RE: Upgrading form mysql 5.0.90 to 5.5 or 5.6

[Rick James]

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; 
> 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
> > >  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
> 
> > 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
> >
> >

RE: Upgrading form mysql 5.0.90 to 5.5 or 5.6

[Rick James]

Ditto.  I would mysqldump 5.0, load it onto a 5.5 (or 5.6) box that you have as 
a slave of the 5.0 master.  The load may uncover some issues.  Testing reads 
may uncover issues.  The replication stream will test the writes; it may 
uncover issues.

After being comfortable with that, build new slaves off the 5.5/5.6 box.  Then 
cutover writes to that box.  And jettison the 5.0 boxes.

5.5 -> 5.6 may have more changes/improvements that all of 5.0->5.1->5.5.  (Or, 
at least, Oracle salesmen would like you to believe it.)  There is clearly a 
lot new optimizations in 5.6.

So should you go all the way to 5.6?  Maybe.  You need to do a lot of shakedown 
anyway.

> -Original Message-
> From: Mihail Manolov [mailto:mihail.mano...@liquidation.com]
> Sent: Thursday, February 14, 2013 2:22 PM
> To: Mike Franon
> Cc: Akshay Suryavanshi; 
> Subject: Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6
> 
> 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
> >  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 
> 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

Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6

[Akshay Suryavanshi]

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  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
>
>

Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6

[Mike Franon]

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
 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  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

Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6

[Manuel Arostegui]

2013/2/14 Mike Franon 

> 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.


Do not forget to leave that spare server running for several days before
upgrading the rest of machines to 5.6. If possible, I would do some stress
tests or benchmarking to make sure it performs as you expect.

Manuel.