Re: Upgrading How To

[Reindl Harald]

Am 26.12.2014 um 20:52 schrieb Grant Peel:

Shawn & all,

Thank you for taking to time to reply.

So, to be clear, what I understand from your post is that replacing the
new build's grant/system tables with the archived ones from the previous
version, generally works fine, upgrade issues not withstanding. This is the
answer I was hoping for.

FWIW, I have already tested using some sample databases from the old
version to the new one. Also, I'm not sure I mentioned, but I am moving from
5.1.39 to 5.6.17. I have already ran into the password hash issues on a
number of tables, but, other than that things seem fine.


that should be in general fine, i would recommend a scipted "optimize 
table" for any tables on the old machine before starting for two reasons


* the data to transfer will be smaller
* all old tables will be for sure rebuilt and not in 5.0 format

the rsync works also fine with a USB stick, ext4 format preferred 
because owner / permissions, but that can be fixed easily in any case on 
the new machine per chmod / chown


the only difference in your case is that you have a larger downtime

if the hot rsync followed by a cold one with a USB stick as destination 
is noticeable faster needs to be tested, maybe have a empty datadir on 
the destination and transfer all data is faster then the checksumming





signature.asc
Description: OpenPGP digital signature

Re: Upgrading How To

[Grant Peel]

Shawn & all,

 

 Thank you for taking to time to reply.

 

 So, to be clear, what I understand from your post is that replacing the
new build's grant/system tables with the archived ones from the previous
version, generally works fine, upgrade issues not withstanding. This is the
answer I was hoping for. 

 

 FWIW, I have already tested using some sample databases from the old
version to the new one. Also, I'm not sure I mentioned, but I am moving from
5.1.39 to 5.6.17. I have already ran into the password hash issues on a
number of tables, but, other than that things seem fine.

 

 Any other comments are welcome.

 

-G

 

Re: Upgrading How To

[shawn l.green]

Hi Grant,

On 12/26/2014 11:18 AM, Grant Peel wrote:

Reindl,

I am sorry, in my original post, I forgot to mention that the OLD box and
the NEW box are the same physical machine. I need to be able to save all
data into files on a memstick or portable disc and restore them to the newly
staged machine (with the new version of mysql).

-Grant



There are a few file-level storage changes between 5.x (where x < 6) and 
5.6 that you may need to resolve before the upgrade.  Examples:


* 5.6 will not read any tables that were physically created in a version 
older than 5.0 and never rebuilt using a newer version.


* the YEAR(2) data type is no longer supported.

* pre 4.1 passwords - If you are upgrading from version 5.1 or older, 
you will need to update their hashes or configure 5.6 to recognize the 
older hashes as valid. The user authentication system in 5.6 is more 
advanced than in earlier versions.


Several features are removed as of 5.6
http://dev.mysql.com/doc/refman/5.6/en/mysql-nutshell.html#mysql-nutshell-removals

Many defaults were changed starting with 5.6. These and other things to 
consider before a move to 5.6 (like the SQL Mode and timestamp 
behaviors) are all listed here:

http://dev.mysql.com/doc/refman/5.6/en/upgrading-from-previous-series.html

mysql_upgrade will update the system tables in the `mysql` database and 
run a CHECK TABLE ... FOR UPGRADE on all your tables but it cannot 
handle all of the possible upgrade issues you may encounter due to the 
other things about the server that may have changed.


Reindl's technique with the rsync is just like what you are doing with 
your full-image save/restore. His is just optimized for operating 
between two live machines.


You are also very strongly encouraged to test the upgrade to 5.6 on a 
lab box long before you push it into production. This will give you the 
chance to find any of those new 5.6 changes that your clients may not be 
ready to handle.


Yours,
--
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN

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

RE: Upgrading How To

[Grant Peel]

Reindl,

I am sorry, in my original post, I forgot to mention that the OLD box and
the NEW box are the same physical machine. I need to be able to save all
data into files on a memstick or portable disc and restore them to the newly
staged machine (with the new version of mysql).

-Grant


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

Re: Upgrading How To

[Reindl Harald]

Am 25.12.2014 um 16:01 schrieb Grant Peel:

I was wondering if anyone knows of a concise tutorial on how to upgrade (by
moving from one box (old) to another box (new) mysql in a virtual
environment (many mysql users, many databases).

Mysql 5.x setup on freebsd 8.x (x86/32b), call this box A.

Want to move to a new box:

 Mysql 5.6.17 on freebsd 9.3 (x86/32b) call this box B.


* setup the new box
* stop mysqld on the old
* rsync /var/lib/mysqld to the new one
* start mysql on the new one
* mysql_upgrade -u root -p
* enter root pwd
* done

to keep downtime as low as possible rsync one or two times *hot* without 
stop mysqld on the old machine so the last rsync only transfer diffs - 
doing that for 12 years now from MySQl 3.x to 5.5 between Windows, 
MacOSX and Linux systems in all directions


rsync params:
--force --delete-after -tPrlpogEAX



signature.asc
Description: OpenPGP digital signature

Upgrading How To

[Grant Peel]

Hi all,

 

I was wondering if anyone knows of a concise tutorial on how to upgrade (by
moving from one box (old) to another box (new) mysql in a virtual
environment (many mysql users, many databases).

 

Example:

 

Mysql 5.x setup on freebsd 8.x (x86/32b), call this box A.

 

Want to move to a new box:

 

Mysql 5.6.17 on freebsd 9.3 (x86/32b) call this box B. 

 

1)  How does one correctly move the users and all the permissions (grant
tables),

2)  How does one move the data.

3)  Assume lots of backups have been done and there is no risk of
permanently loosing data. 
Also, move/copy to be done using files (to memstick or external disk or
NFS), not using ssh directly (rcopy etc).

4)  Basically, dumpt the data and users and perms and put it on a new
box J

 

I suspect this question is trivial to a lot of admins out there, but, I
suspect it would be helpful to many out there.

 

Happy Holidays!

 

-Grant

 

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]

not really

* it is unlikely that you have 1:4 relations key/data
* you have sql-statement overhead even for tinyint 1
* you have overhead to escape data

Am 16.02.2013 00:55, schrieb Akshay Suryavanshi:
> Harald,
> 
> I somewhat dont agree with your statement of mysqldump backup size being way 
> bigger than the actual datasets, just
> beacuse its SQL plain text. What I can tell you is, mysqldump files would be 
> significantly smaller than the total
> dataset size, because it doesnt contain "index data". So, if out of 400G, 
> 100G is index data then the dump file
> should be 300G.
> 
> On Sat, Feb 16, 2013 at 4:24 AM, Reindl Harald  <mailto:h.rei...@thelounge.net>> wrote:
> 
> "our database is 400 GB, mysqldump is 600MB" was not a typo and you
> honestly believed that you can import this dump to somewhat?
> 
> WTF - as admin you should be able to see if the things in front
> of you are theoretically possible before your start any action
> and 1:400 is impossible, specially because mysql-dumps are
> ALWAYS WAY LARGER then the databasses because they contain
> sql-statements and not only data
> 
> Am 15.02.2013 23:37, schrieb Mike Franon:
> > Your right I am going to run another mysqldump, maybe something
> > happened and pick this up next week..
> >
> > Thanks all.
> >
> > On Fri, Feb 15, 2013 at 5:03 PM, Keith Murphy  <mailto:bmur...@paragon-cs.com>> wrote:
> >> Something doesn't add up. If the data set is 400 GB then your dump has 
> to
> >> bigger than 600 mb. That is better than a 400:1 ratio. Maybe the dump 
> isn't
> >> working correctly or your data set is much smaller? If the dump output 
> is
> >> less than a gig I would just edit it with something like vi and look 
> at the
> >> offending line.
> >>
> >> Keith
> >>
> >> On Feb 15, 2013 3:55 PM, "Mike Franon"  <mailto:kongfra...@gmail.com>> wrote:
> >>>
> >>> I am having a real hard time upgrading just from 5.0.96 to 5.1
> >>>
> >>> I did a full mysqldump and then restore the database, keep in mind our
> >>> database is 400 GB, mysqldump is 600MB file, about 30 minutes into the
> >>> restore get this error on one table on an insert:
> >>>
> >>> ERROR 1064 (42000) at line 1388: You have an error in your SQL syntax;
> >>> check the manual that corresponds to your MySQL server version for the
> >>> right syntax to use near ''2010-04-10 20' at line 1
> >>>
> >>> It weird because If I upgrade 5.1 right over 5.0 without doing a
> >>> mysqldump, and then do a mysqlcheck it works, except for 5 tables, and
> >>> triggers, so trying to think of the best way to get to 5.1
> >>>
> >>> On Fri, Feb 15, 2013 at 12:39 PM, Keith Murphy 
> mailto:bmur...@paragon-cs.com>>
> >>> wrote:
> >>>> While it might be GA I would not recommend that you deploy it for a
> >>>> while.
> >>>> ... at least several point releases. There will be new bugs 
> uncovered as
> >>>> it
> >>>> moves out to a wider audience.
> >>>>
> >>>> Upgrade to 5.5 (through 5.1) first as it is quite proven. Slave 5.6 
> off
> >>>> it
> >>>> and test. Be patient. Save yourself some heartache. Just my two 
> cents.
> >>>>
> >>>> Keith
> >>>>
> >>>> On Feb 15, 2013 9:27 AM, "Mike Franon"  <mailto:kongfra...@gmail.com>> wrote:
> >>>>>
> >>>>> Thanks everyone for suggestions.
> >>>>>
> >>>>> I am doing this on a test box  with a copy of our db before doing 
> this
> >>>>> on production db servers.
> >>>>>
> >>>>> I just upgraded from 5.0 to 5.1, and ran mysql_upgrade
> >>>>>
> >>>>> and see I have a few tables with the following error:
> >>>>>
> >>>>> error: Table upgrade required. Please do "REPAIR TABLE
> >>>>> `tablename`" or dump/reload to fix it!
> >>>>>
> >>>>> I got this on 4 tables so far, but it still checking, my database is
> >>>&

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.

Re: A better REPAIR TABLE for myisam tables (or for upgrading tables)

[Hank]

Sorry...

One small correction to my above post..

'FLUSH TABLES' should be issued between steps 8 and 9.

My 200+ million record table completed in 71 minutes.

-Hank


mysql; query;

A better REPAIR TABLE for myisam tables (or for upgrading tables)

[Hank]

I've posted a similar post in the past -- but there I was mucking around
with blank index files and frm files to fool myisamchk into repairing a
table.

 But now I think I've come across a much better and more efficient way to do
a REPAIR Table in order to upgrade my database tables from Mysql 4.1.x to
5.5.8.

All this comes from the fact that REPAIR TABLE does not rebuild the table
indexes like myisamchk does, which is very unfortunate.  Sure, REPAIR TABLE
works great for small tables, but if you have any tables of larger size
(millions of records or more, with multiple indexes), REPAIR TABLE can take
hours or days to do a simple repair/upgrade.  And in most cases,
applications just can't be down for that long during an upgrade cycle (not
everyone runs a huge shop with multiple dev/test/upgrade/production
servers).

So here is what I have done, and propose this as a better REPAIR TABLE for
MYISAM tables (in pseudo code):

1.  Retrieve the original CREATE TABLE DDL with "show create table SOURCE"
2.  Modify DDL to change the table name to a new target table, let's call it
TARGET
3.  Execute new DDL to create empty TARGET table
4.  Run 'myisamchk -r --keys-used=0 TARGET'  (to disable all index keys on
new table)
5.  flush tables; lock table SOURCE read, TARGET write;
6.  insert into TARGET select * From SOURCE;
7.  flush tables; unlock tables;
8.  'myisamchk -prqn TARGET'  (repair to re-enable all keys, do not modify
MYD table, use sorting, in parallel)
9.  rename tables to replace SOURCE with TARGET

I've written a PHP script to do exactly this, and it works beautifully.  My
source tables are mysql 4.1.x tables, and the target tables are now fully
5.5 compliant  (verified with mysqlcheck --check-upgrade).

The best part is that for tables with 50 million short rows, it ran in 7
minutes, and a table with 30 million rows, it ran in 4 minutes.

I'm now running it on a table with over 200 million rows, and I expect it to
take an hour or so... but in all cases, doing a REPAIR TABLE on any of these
large tables would take days to complete.

So why can't the REPAIR TABLE command do something like this in the
background for large MYISAM tables?

-Hank

Re: Upgrading of mysql database

[Machiel Richards]

How would I do an inplace upgrade?


-Original Message-
From: Johan De Meersman 
To: Machiel Richards 
Cc: mysql mailing list 
Subject: Re: Upgrading of mysql database
Date: Mon, 22 Nov 2010 15:25:44 +0100

That would work, yes.

You could also try to upgrade in place - the upgrade scripts *should*
take care of everything between those versions, I think. Make sure you
have a backup in any case :-)



On Mon, Nov 22, 2010 at 12:57 PM, Machiel Richards 
wrote:

Hi All

   Sorry for all my posts today but this one client is
keeping me
busy.

   the version of MySQL installed on the ubuntu server is
5.0.51a-3ubuntu5.8-log as this was the latest one available in
the
repository.

   We will need to upgrade this to version 5.1.53.

Am I correct in assuming the following steps?

   1. setup version 5.1.53 on the machine (different
port)
   2. shutdown the current database.
   3. create backup file
   4. restore backup
   5. change port to 3306
   6. startup new database.
   7. disable the old database so that it would not
start
up during reboot.

Regards
Machiel



-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

Re: Upgrading of mysql database

[Johan De Meersman]

Replace the software - if you're using packaged versions, they should take
care of most anything. If not, there's mysql-upgrade or some script. See the
online docs for specifics.

On Mon, Nov 22, 2010 at 3:56 PM, Machiel Richards wrote:

>  How would I do an inplace upgrade?
>
>
>
> -Original Message-
> *From*: Johan De Meersman 
> 
> >
> *To*: Machiel Richards 
> 
> >
> *Cc*: mysql mailing list 
> 
> >
> *Subject*: Re: Upgrading of mysql database
> *Date*: Mon, 22 Nov 2010 15:25:44 +0100
>
> That would work, yes.
>
> You could also try to upgrade in place - the upgrade scripts *should* take
> care of everything between those versions, I think. Make sure you have a
> backup in any case :-)
>
>
>
> On Mon, Nov 22, 2010 at 12:57 PM, Machiel Richards 
> wrote:
>
> Hi All
>
>Sorry for all my posts today but this one client is keeping me
> busy.
>
>the version of MySQL installed on the ubuntu server is
> 5.0.51a-3ubuntu5.8-log as this was the latest one available in the
> repository.
>
>We will need to upgrade this to version 5.1.53.
>
> Am I correct in assuming the following steps?
>
>1. setup version 5.1.53 on the machine (different port)
>2. shutdown the current database.
>3. create backup file
>4. restore backup
>5. change port to 3306
>6. startup new database.
>7. disable the old database so that it would not start
> up during reboot.
>
> Regards
> Machiel
>
>
>
>
> --
> Bier met grenadyn
> Is als mosterd by den wyn
> Sy die't drinkt, is eene kwezel
> Hy die't drinkt, is ras een ezel
>
>


-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

Re: Upgrading of mysql database

[Johan De Meersman]

That would work, yes.

You could also try to upgrade in place - the upgrade scripts *should* take
care of everything between those versions, I think. Make sure you have a
backup in any case :-)



On Mon, Nov 22, 2010 at 12:57 PM, Machiel Richards wrote:

> Hi All
>
>Sorry for all my posts today but this one client is keeping me
> busy.
>
>the version of MySQL installed on the ubuntu server is
> 5.0.51a-3ubuntu5.8-log as this was the latest one available in the
> repository.
>
>We will need to upgrade this to version 5.1.53.
>
> Am I correct in assuming the following steps?
>
>1. setup version 5.1.53 on the machine (different port)
>2. shutdown the current database.
>3. create backup file
>4. restore backup
>5. change port to 3306
>6. startup new database.
>7. disable the old database so that it would not start
> up during reboot.
>
> Regards
> Machiel
>



-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

Upgrading of mysql database

[Machiel Richards]

Hi All

Sorry for all my posts today but this one client is keeping me
busy.

the version of MySQL installed on the ubuntu server is
5.0.51a-3ubuntu5.8-log as this was the latest one available in the
repository.

We will need to upgrade this to version 5.1.53.

 Am I correct in assuming the following steps?

1. setup version 5.1.53 on the machine (different port)
2. shutdown the current database.
3. create backup file
4. restore backup
5. change port to 3306
6. startup new database.
7. disable the old database so that it would not start
up during reboot.

Regards
Machiel

Re: Is upgrading from 4.X to 5.X really that easy?

[Nunzio Daveri]

Thanks Keith :-)  Last question, do you think it's ok for me to do a sqldump on 
4.1.22 at say 3 am on sun, then import to 5.1.48 at 4 am and then just edit 
etc/hosts and have the web servers now point to 5.1?

This should work without any problems right?  Plus I have the original 4.x in 
case I break something during the dump and can revert within mins back to the 
4.x version?

Still don't know why I should do a mysql dump from 4.1.X to 5.0.x and then 
upgrade 5.0.x to 5.1.48 esp. if I am doing nothing more than a mysql dump and 
not upgrading in place ;-)

TIA...

Nunzio





From: Keith Murphy 
To: Nunzio Daveri 
Cc: mysql@lists.mysql.com
Sent: Mon, August 16, 2010 11:47:39 AM
Subject: Re: Is upgrading from 4.X to 5.X really that easy?

I would really recommend that you have a second server set up running MySQL 
5.0.  Otherwise the complexity is going to drive you crazy. Either way you are 
going to have to get a backup of the master (4.1) server somehow. I am curious. 
If you can't take an hour or so take a mysqldump of the server how are you 
running backups now? And if you aren't runninng backups you need to run to your 
boss and say "It's REALLY REALLY REALLY critical that we start making backups." 
And do it beginning tonight at the latest.

Otherwise something is going to happen, the data is going to be lost and you 
are 
best case going to look REALLY REALLY bad. 


I wouldn't recommend going straight to 5.1. The upgrade from 5.0 to 5.1 is 
fairly trivial and doesn't require a dump/reload but I would still take the 
time 
to stop at 5.0 and make sure everything is working before moving on to 5.1.

keith




On Mon, Aug 16, 2010 at 12:42 PM, Nunzio Daveri  wrote:

Thanks William and Keith.  So how to have min down time since this is a stand
>alone mysql 4.1.22 box?  Are you saying install 5.X on the same box (port 
3307),
>then replicate the data as it comes into 4.x to 5.x and when it is all sync'd 
up
>then turn 4.x off, remove it and have 5.x responding on port 3306?
>
>Mysqldump takes over an hour and then prob more to reimport?  I only have a 30 
-
>45 min window.
>
>If I do a straight dump from 4.1.22 with all options why stop at 5.0 and not
>just go straight to 5.1.48?  This is a single box, no replication or clustering
>going on ;-)  Also all the data is in MyISAM, zero InnoDB :-)
>
>Thanks again for the advice :-)
>
>
>Nunzio
>
>
>
>
>____
>From: Keith Murphy 
>To: Nunzio Daveri 
>Sent: Mon, August 16, 2010 9:42:07 AM
>Subject: Re: Is upgrading from 4.X to 5.X really that easy?
>
>
>No, that would be a huge mistake. There are subtle differences between the two
>versions. For example, check up on DECIMAL. Also, 5.0 and 5.1 have numerous new
>reserved words.
>
>
>You need to think about this carefully before you do it. I know there is binary
>incompatability between Innodb tables (vers 4.X - 5.X). I will take you word
>that what you are saying would actually work, but I still wouldn't recommend 
it.
>
>
>Just my 2 cents...
>
>keith
>
>
>On Mon, Aug 16, 2010 at 10:26 AM, Nunzio Daveri  wrote:
>
>Hi all, I was reading a few of the notes on this forum from a few months back
>>and it seems that ONE WAY of upgrading from 4.x to 5.X with MyISAM only
>>databases is to copy the actual data folder from the 4.X version to a temp
>>place, then remove 4.x from the OS, install 5.X and then just put the 4.X data
>>folder into the 5.X folder???
>>
>>
>>Is it really that simple?  Has anyone done this and can verify this please?  I
>>am thinking I am missing a few commands you have to run at least??? My 
database
>>is pretty small is an only 1.8GB so I am thinking this is a walk in the park
>:-)
>>
>>Please advise...
>>
>>And as always... TIA...
>>
>>Nunzio
>>
>>
>>
>
>
>--
>Chief Training Officer
>Paragon Consulting Services
>850-637-3877
>
>
>
> 


-- 
Chief Training Officer
Paragon Consulting Services
850-637-3877



  

Re: Is upgrading from 4.X to 5.X really that easy?

[Keith Murphy]

I would really recommend that you have a second server set up running MySQL
5.0.  Otherwise the complexity is going to drive you crazy. Either way you
are going to have to get a backup of the master (4.1) server somehow. I am
curious. If you can't take an hour or so take a mysqldump of the server how
are you running backups now? And if you aren't runninng backups you need to
run to your boss and say "It's REALLY REALLY REALLY critical that we start
making backups." And do it beginning tonight at the latest.

Otherwise something is going to happen, the data is going to be lost and you
are best case going to look REALLY REALLY bad.

I wouldn't recommend going straight to 5.1. The upgrade from 5.0 to 5.1 is
fairly trivial and doesn't require a dump/reload but I would still take the
time to stop at 5.0 and make sure everything is working before moving on to
5.1.

keith



On Mon, Aug 16, 2010 at 12:42 PM, Nunzio Daveri wrote:

> Thanks William and Keith.  So how to have min down time since this is a
> stand
> alone mysql 4.1.22 box?  Are you saying install 5.X on the same box (port
> 3307),
> then replicate the data as it comes into 4.x to 5.x and when it is all
> sync'd up
> then turn 4.x off, remove it and have 5.x responding on port 3306?
>
> Mysqldump takes over an hour and then prob more to reimport?  I only have a
> 30 -
> 45 min window.
>
> If I do a straight dump from 4.1.22 with all options why stop at 5.0 and
> not
> just go straight to 5.1.48?  This is a single box, no replication or
> clustering
> going on ;-)  Also all the data is in MyISAM, zero InnoDB :-)
>
> Thanks again for the advice :-)
>
> Nunzio
>
>
>
>
> ____
> From: Keith Murphy 
> To: Nunzio Daveri 
> Sent: Mon, August 16, 2010 9:42:07 AM
> Subject: Re: Is upgrading from 4.X to 5.X really that easy?
>
> No, that would be a huge mistake. There are subtle differences between the
> two
> versions. For example, check up on DECIMAL. Also, 5.0 and 5.1 have numerous
> new
> reserved words.
>
>
> You need to think about this carefully before you do it. I know there is
> binary
> incompatability between Innodb tables (vers 4.X - 5.X). I will take you
> word
> that what you are saying would actually work, but I still wouldn't
> recommend it.
>
>
> Just my 2 cents...
>
> keith
>
>
> On Mon, Aug 16, 2010 at 10:26 AM, Nunzio Daveri 
> wrote:
>
> Hi all, I was reading a few of the notes on this forum from a few months
> back
> >and it seems that ONE WAY of upgrading from 4.x to 5.X with MyISAM only
> >databases is to copy the actual data folder from the 4.X version to a temp
> >place, then remove 4.x from the OS, install 5.X and then just put the 4.X
> data
> >folder into the 5.X folder???
> >
> >
> >Is it really that simple?  Has anyone done this and can verify this
> please?  I
> >am thinking I am missing a few commands you have to run at least??? My
> database
> >is pretty small is an only 1.8GB so I am thinking this is a walk in the
> park
> :-)
> >
> >Please advise...
> >
> >And as always... TIA...
> >
> >Nunzio
> >
> >
> >
>
>
> --
> Chief Training Officer
> Paragon Consulting Services
> 850-637-3877
>
>
>
>
>



-- 
Chief Training Officer
Paragon Consulting Services
850-637-3877

Re: Is upgrading from 4.X to 5.X really that easy?

[Nunzio Daveri]

Thanks William and Keith.  So how to have min down time since this is a stand 
alone mysql 4.1.22 box?  Are you saying install 5.X on the same box (port 
3307), 
then replicate the data as it comes into 4.x to 5.x and when it is all sync'd 
up 
then turn 4.x off, remove it and have 5.x responding on port 3306?

Mysqldump takes over an hour and then prob more to reimport?  I only have a 30 
- 
45 min window.

If I do a straight dump from 4.1.22 with all options why stop at 5.0 and not 
just go straight to 5.1.48?  This is a single box, no replication or clustering 
going on ;-)  Also all the data is in MyISAM, zero InnoDB :-)

Thanks again for the advice :-)

Nunzio





From: Keith Murphy 
To: Nunzio Daveri 
Sent: Mon, August 16, 2010 9:42:07 AM
Subject: Re: Is upgrading from 4.X to 5.X really that easy?

No, that would be a huge mistake. There are subtle differences between the two 
versions. For example, check up on DECIMAL. Also, 5.0 and 5.1 have numerous new 
reserved words. 


You need to think about this carefully before you do it. I know there is binary 
incompatability between Innodb tables (vers 4.X - 5.X). I will take you word 
that what you are saying would actually work, but I still wouldn't recommend 
it. 


Just my 2 cents...

keith


On Mon, Aug 16, 2010 at 10:26 AM, Nunzio Daveri  wrote:

Hi all, I was reading a few of the notes on this forum from a few months back
>and it seems that ONE WAY of upgrading from 4.x to 5.X with MyISAM only
>databases is to copy the actual data folder from the 4.X version to a temp
>place, then remove 4.x from the OS, install 5.X and then just put the 4.X data
>folder into the 5.X folder???
>
>
>Is it really that simple?  Has anyone done this and can verify this please?  I
>am thinking I am missing a few commands you have to run at least??? My database
>is pretty small is an only 1.8GB so I am thinking this is a walk in the park 
:-)
>
>Please advise...
>
>And as always... TIA...
>
>Nunzio
>
>
> 


-- 
Chief Training Officer
Paragon Consulting Services
850-637-3877



  

Re: Is upgrading from 4.X to 5.X really that easy?

[Keith Murphy]

MySQL rightly says you should dump and reload. As William said, you should
read the release notes for every release between your current release and
the target release.

I have done this several times. I am currently planning a migration from
4.1.22 to 5.1.49 with a brief stop at 5.0 along the way. There is almost 200
gigs of data on the master server in this situation so it does take some
foresight and planning. However, the end result will be no significant
downtime.

You can configure MySQL 5.0 as a slave of MySQL 4.1 so you might consider
doing that. That way when its time for the actual upgrade you just point the
application to the MySQL 5.0 server and shut down the MySQL 4.1 server
(which CANNOT be a slave of MysQL 5.0 -- it's a one way relationship due to
the changes in the binary logging.

Hope that helps.

keith

On Mon, Aug 16, 2010 at 11:42 AM, Wm Mussatto  wrote:

>
>
> On Mon, August 16, 2010 07:26, Nunzio Daveri wrote:
> > Hi all, I
> was reading a few of the notes on this forum from a few months
> >
> back
> > and it seems that ONE WAY of upgrading from 4.x to 5.X with
> MyISAM only
> > databases is to copy the actual data folder from the
> 4.X version to a temp
> > place, then remove 4.x from the OS,
> install 5.X and then just put the 4.X
> > data
> > folder into
> the 5.X folder???
> >
> >
> > Is it really that
> simple?  Has anyone done this and can verify this
> > please?  I
> > am thinking I am missing a few commands you have to run at least???
> My
> > database
> > is pretty small is an only 1.8GB so I am
> thinking this is a walk in the
> > park :-)
> >
> >
> Please advise...
> >
> > And as always... TIA...
> >
>
> > Nunzio
> What version of 4.x,  I upgraded between
> Debian stable versions and got burned because in the middle of the 4.x
> group MySQL changed to a more "correct" version of JOINs.
> Didn't effect to data, which will should work as you expect, but did have
> subtle impact on the select statements embedded in various programs.
> Read the release note between YOUR current and new versions.
> --
> William R. Mussatto
> Systems Engineer
> http://www.csz.com
> 909-920-9154
>



-- 
Chief Training Officer
Paragon Consulting Services
850-637-3877

Re: Is upgrading from 4.X to 5.X really that easy?

[Wm Mussatto]

On Mon, August 16, 2010 07:26, Nunzio Daveri wrote:
> Hi all, I
was reading a few of the notes on this forum from a few months
>
back
> and it seems that ONE WAY of upgrading from 4.x to 5.X with
MyISAM only
> databases is to copy the actual data folder from the
4.X version to a temp
> place, then remove 4.x from the OS,
install 5.X and then just put the 4.X
> data
> folder into
the 5.X folder???
> 
> 
> Is it really that
simple?  Has anyone done this and can verify this
> please?  I
> am thinking I am missing a few commands you have to run at least???
My
> database
> is pretty small is an only 1.8GB so I am
thinking this is a walk in the
> park :-)
> 
>
Please advise...
> 
> And as always... TIA...
>

> Nunzio
What version of 4.x,� I upgraded between
Debian stable versions and got burned because in the middle of the 4.x
group MySQL changed to a more "correct" version of JOINs.�
Didn't effect to data, which will should work as you expect, but did have
subtle impact on the select statements embedded in various programs.�
Read the release note between YOUR current and new versions.
--
William R. Mussatto
Systems Engineer
http://www.csz.com
909-920-9154

Is upgrading from 4.X to 5.X really that easy?

[Nunzio Daveri]

Hi all, I was reading a few of the notes on this forum from a few months back 
and it seems that ONE WAY of upgrading from 4.x to 5.X with MyISAM only 
databases is to copy the actual data folder from the 4.X version to a temp 
place, then remove 4.x from the OS, install 5.X and then just put the 4.X data 
folder into the 5.X folder???  


Is it really that simple?  Has anyone done this and can verify this please?  I 
am thinking I am missing a few commands you have to run at least??? My database 
is pretty small is an only 1.8GB so I am thinking this is a walk in the park :-)

Please advise...

And as always... TIA...

Nunzio


  

Re: MySQL Upgrading

[Rob Wultsch]

On Wed, Jun 23, 2010 at 6:33 AM, Steven Staples  wrote:
> Hi,
>
> I am looking at upgrading my servers Debian version from Etch to Lenny, and
> in doing that, I think it will upgrade MySQL from 5.0.32 to the lenny
> version, which is  5.0.53 (I think).
>
> I have also been thinking about using the 'dotdeb' packages, which will
> upgrade it even further to 5.1.47.  I have done this on a test server, and
> it all my stored procedures and stuff work fine, so now to my question.
>
> Will this break any replication if I don't upgrade my replication server
> that is still running 5.0.32 (until I upgrade that server as well, which
> could be a few weeks due to timing)?

It will. I suggest upgrading to at least 5.0.67 if you have no fear of
internal users, and the most recent version of 5.0 and 5.1 (I don't
recall what they are) if you have fears of malicious users.

> The other issue, is that the replication server is running multiple
> instances of the same MySQL on different ports, so that I can replicate
> multiple sources to a single server (that has attached tape drives for
> backup purposes), is it possible to run both versions of mysql (the 5.0.32
> and the 5.1.47)?    granted, this is not the debian mailing list, just
> thought I would ask that last part ;)
>
>
> Steven Staples
>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=wult...@gmail.com
>
>



-- 
Rob Wultsch
wult...@gmail.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

MySQL Upgrading

[Steven Staples]

Hi,

I am looking at upgrading my servers Debian version from Etch to Lenny, and
in doing that, I think it will upgrade MySQL from 5.0.32 to the lenny
version, which is  5.0.53 (I think).

I have also been thinking about using the 'dotdeb' packages, which will
upgrade it even further to 5.1.47.  I have done this on a test server, and
it all my stored procedures and stuff work fine, so now to my question.

Will this break any replication if I don't upgrade my replication server
that is still running 5.0.32 (until I upgrade that server as well, which
could be a few weeks due to timing)?

The other issue, is that the replication server is running multiple
instances of the same MySQL on different ports, so that I can replicate
multiple sources to a single server (that has attached tape drives for
backup purposes), is it possible to run both versions of mysql (the 5.0.32
and the 5.1.47)?granted, this is not the debian mailing list, just
thought I would ask that last part ;)


Steven Staples




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: upgrading mysql

[Paul DuBois]

On Jan 13, 2010, at 1:28 PM, Lawrence Sorrillo wrote:

> The issue is that in theory this should work given the facts announced by 
> MySQL regarding binary logging and replication.
> I can certainly do it the way you propose, but to my mind I should also be 
> able to do it using the fact that both machines are fully synced and hence at
> that point I should be able to to local respective dumps and restores and 
> still be in sync.
> 
> Anyone knows anything special about position 106? It seems to be the very 
> initial position in MySQL 5.1 servers?

It's not. 4 is still the initial position, as shown by the "at 4" in your 
mysqlbinlog output below. The 106 that you observe is the position *after* the 
server writes the initial event to the binary log. It writes this event 
immediately after opening the file, even before executing any statements.

If you want the gory details: This event is the format description event that 
identifies in the binary log file the server version and other information. See 
http://forge.mysql.com/wiki/MySQL_Internals_Binary_Log#Binary_Log_Versions if 
you have a high tolerance for pain. :-)

> 
> mysql> show master status;
> +---+--+--+--+
> | File  | Position | Binlog_Do_DB | Binlog_Ignore_DB |
> +---+--+--+--+
> | X-bin.01 |  106 |  |  |
> +---+--+--+--+
> 1 row in set (0.00 sec)
> 
> 
> 
> r...@:/usr/local/mysql/data ] /usr/local/mysql/bin/mysqlbinlog 
> mssdb2-bin.01
> /*!40019 SET @@session.max_insert_delayed_threads=0*/;
> /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
> DELIMITER /*!*/;
> # at 4
> #100113 13:50:40 server id 5  end_log_pos 106   Start: binlog v 4, server v 
> 5.1.42-log created 100113 13:50:40 at startup
> # Warning: this binlog is either in use or was not closed properly.
> ROLLBACK/*!*/;
> BINLOG '
> ABZOSw8FZgAAAGoBAAQANS4xLjQyLWxvZwAA
> Fk5LEzgNAAgAEgAEBAQEEgAAUwAEGggICAgC
> '/*!*/;
> DELIMITER ;
> # End of log file
> ROLLBACK /* added by mysqlbinlog */;
> /*!50003 SET completion_ty...@old_completion_type*/;
> r...@:/usr/local/mysql/data ]
> 
> ~Lawrence
> 
> 
> 
> 
> Tom Worster wrote:
>> Frankly, I didn't entirely understand what you were proposing. I got lost
>> around step 6.
>> 
>> Is the issue total time for the procedure or service downtime?
>> 
>> 
>> On 1/12/10 12:58 PM, "Lawrence Sorrillo"  wrote:
>> 
>>  
>>> This is two upgrades done in sequence(the reload takes about three hours
>>> per machine) . I can do what I am proposing in parallel.
>>> 
>>> Do you see it as problematic?
>>> 
>>> ~Lawrence
>>> 
>>> 
>>> Tom Worster wrote:
>>>
 How about:
 
 1 shut down the slave, upgrade it, restart it, let it catch up.
 
 2 shut down the master, upgrade it, restart it, let the slave catch up.
 
 ?
 
 
 
 
 
 On 1/12/10 12:34 PM, "Lawrence Sorrillo"  wrote:
 

> Hi:
> 
> I want to upgrade a master and slave server from mysql 4.1 to mysql 5.1.
> 
> I want to so something like follows:
> 
> 1. Stop all write access to the master server.
> 2. Ensure that replication on the slave is caught up to the last change
> on the master.
> 3. stop binary logging on the master.
> 4. stop replication on the slave.
> 5. dump the master, stop old 4.1 server, start new 5.1 server and reload
> master dump file under 5.1 server ( binary logging is turned off)
> 6. dump the slave, stop old 4.1 server, start new 5.1 server and reload
> slave dump file under 5.1 server.
> 7. After loading is complete, test then start binary logging on master
> while still preventing updates to updates.
> 8. After loading slave, test then start slave (get configs in place and
> restart server).
> 
> I am thinking that in this scenario I dont have to bother with recording
> binlog file names and position etc etc.
> That both servers will have the same databases abd replication and
> binary logging will start on the two databases with no data loss and
> continue forward.
> 
> 
> Comments?
> 
> ~Lawrence
> 
> 
> 
>

>>>
>> 
>> 
>> 
>>  
> 
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql?unsub=paul.dub...@sun.com
> 

-- 
Paul DuBois
Sun Microsystems / MySQL Documentation Team
Madison, Wisconsin, USA
www.mysql.com


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: upgrading mysql

[fsb]

On 1/13/10 2:28 PM, "Lawrence Sorrillo"  wrote:

> The issue is that in theory this should work given the facts announced
> by MySQL regarding binary logging and replication.
> I can certainly do it the way you propose, but to my mind I should also
> be able to do it using the fact that both machines are fully synced and
> hence at
> that point I should be able to to local respective dumps and restores
> and still be in sync.

i can't point at anything in your recipe and say that it doesn't work. it
might work. i'd be nervous that something in steps 5 and 6 might involve a
change on the master that needs to be replicated. since your using a dump
and not a binary copy of myisam file, i suppose this ought to be safe. but i
would be nervous all the same.

on the other hand, i do know that the recipe i gave works because i've used
it often. it also has the virtue of no need for "recording binlog file names
and position etc etc". plus it's the procedure recommended by the mysql folk
themselves, which is worth something to me.

the other thing i've done is:

initial status: A is the master and B is the slave. service is operating off
the master.

1 stop B, upgrade it, restart it, let it catch up.

2 stop service and then stop A

3 change B's conf file to make it the master. restart it

4 resume service using B

5 upgrade A and bring it online as a slave

this has the virtue of very short service outage. with some rehearsal, it
isn't beyond my skills.


> Anyone knows anything special about position 106? It seems to be the
> very initial position in MySQL 5.1 servers?

the manual says:

"If the master has been running previously without binary logging enabled,
the log name and position values displayed by SHOW MASTER STATUS or
mysqldump --master-data will be empty. In that case, the values that you
need to use later when specifying the slave's log file and position are the
empty string ('') and 4."

perhaps you have an init-file that advances it to position 106?



> mysql> show master status;
> +---+--+--+--+
> | File  | Position | Binlog_Do_DB | Binlog_Ignore_DB |
> +---+--+--+--+
> | X-bin.01 |  106 |  |  |
> +---+--+--+--+
> 1 row in set (0.00 sec)
> 
> 
> 
> r...@:/usr/local/mysql/data ] /usr/local/mysql/bin/mysqlbinlog
> mssdb2-bin.01
> /*!40019 SET @@session.max_insert_delayed_threads=0*/;
> /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
> DELIMITER /*!*/;
> # at 4
> #100113 13:50:40 server id 5  end_log_pos 106   Start: binlog v 4,
> server v 5.1.42-log created 100113 13:50:40 at startup
> # Warning: this binlog is either in use or was not closed properly.
> ROLLBACK/*!*/;
> BINLOG '
> ABZOSw8FZgAAAGoBAAQANS4xLjQyLWxvZwAA
> Fk5LEzgNAAgAEgAEBAQEEgAAUwAEGggICAgC
> '/*!*/;
> DELIMITER ;
> # End of log file
> ROLLBACK /* added by mysqlbinlog */;
> /*!50003 SET completion_ty...@old_completion_type*/;
> r...@:/usr/local/mysql/data ]
> 
> ~Lawrence
> 
> 
> 
> 
> Tom Worster wrote:
>> Frankly, I didn't entirely understand what you were proposing. I got lost
>> around step 6.
>> 
>> Is the issue total time for the procedure or service downtime?
>> 
>> 
>> On 1/12/10 12:58 PM, "Lawrence Sorrillo"  wrote:
>> 
>>   
>>> This is two upgrades done in sequence(the reload takes about three hours
>>> per machine) . I can do what I am proposing in parallel.
>>> 
>>> Do you see it as problematic?
>>> 
>>> ~Lawrence
>>> 
>>> 
>>> Tom Worster wrote:
>>> 
 How about:
 
 1 shut down the slave, upgrade it, restart it, let it catch up.
 
 2 shut down the master, upgrade it, restart it, let the slave catch up.
 
 ?
 
 
 
 
 
 On 1/12/10 12:34 PM, "Lawrence Sorrillo"  wrote:
 
   
   
> Hi:
> 
> I want to upgrade a master and slave server from mysql 4.1 to mysql 5.1.
> 
> I want to so something like follows:
> 
> 1. Stop all write access to the master server.
> 2. Ensure that replication on the slave is caught up to the last change
> on the master.
> 3. stop binary logging on the master.
> 4. stop replication on the slave.
> 5. dump the master, stop old 4.1 server, start new 5.1 server and reload
> master dump file under 5.1 server ( binary logging is turned off)
> 6. dump the slave, stop old 4.1 server, start new 5.1 server and reload
> slave dump file under 5.1 server.
> 7. After loading is complete, test then start binary logging on master
> while still preventing updates to updates.
> 8. After loading slave, test then start slave (get configs in place and
> restart server).
> 
> I am thinking that in this scenario I dont have to bother with recording
> binlog file na

Re: upgrading mysql

[Lawrence Sorrillo]

The issue is that in theory this should work given the facts announced 
by MySQL regarding binary logging and replication.
I can certainly do it the way you propose, but to my mind I should also 
be able to do it using the fact that both machines are fully synced and 
hence at
that point I should be able to to local respective dumps and restores 
and still be in sync.


Anyone knows anything special about position 106? It seems to be the 
very initial position in MySQL 5.1 servers?


mysql> show master status;
+---+--+--+--+
| File  | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---+--+--+--+
| X-bin.01 |  106 |  |  |
+---+--+--+--+
1 row in set (0.00 sec)



r...@:/usr/local/mysql/data ] /usr/local/mysql/bin/mysqlbinlog 
mssdb2-bin.01

/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#100113 13:50:40 server id 5  end_log_pos 106   Start: binlog v 4, 
server v 5.1.42-log created 100113 13:50:40 at startup

# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
ABZOSw8FZgAAAGoBAAQANS4xLjQyLWxvZwAA
Fk5LEzgNAAgAEgAEBAQEEgAAUwAEGggICAgC
'/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET completion_ty...@old_completion_type*/;
r...@:/usr/local/mysql/data ]

~Lawrence




Tom Worster wrote:

Frankly, I didn't entirely understand what you were proposing. I got lost
around step 6.

Is the issue total time for the procedure or service downtime?


On 1/12/10 12:58 PM, "Lawrence Sorrillo"  wrote:

  

This is two upgrades done in sequence(the reload takes about three hours
per machine) . I can do what I am proposing in parallel.

Do you see it as problematic?

~Lawrence


Tom Worster wrote:


How about:

1 shut down the slave, upgrade it, restart it, let it catch up.

2 shut down the master, upgrade it, restart it, let the slave catch up.

?





On 1/12/10 12:34 PM, "Lawrence Sorrillo"  wrote:

  
  

Hi:

I want to upgrade a master and slave server from mysql 4.1 to mysql 5.1.

I want to so something like follows:

1. Stop all write access to the master server.
2. Ensure that replication on the slave is caught up to the last change
on the master.
3. stop binary logging on the master.
4. stop replication on the slave.
5. dump the master, stop old 4.1 server, start new 5.1 server and reload
master dump file under 5.1 server ( binary logging is turned off)
6. dump the slave, stop old 4.1 server, start new 5.1 server and reload
slave dump file under 5.1 server.
7. After loading is complete, test then start binary logging on master
while still preventing updates to updates.
8. After loading slave, test then start slave (get configs in place and
restart server).

I am thinking that in this scenario I dont have to bother with recording
binlog file names and position etc etc.
That both servers will have the same databases abd replication and
binary logging will start on the two databases with no data loss and
continue forward.


Comments?

~Lawrence





  
  





  




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: upgrading mysql

[Suresh Kuna]

Hi,
The step 6 in simple terms is

Here we need to build two server ( both master and slave ). Instead of
building two server as it takes double the time of building in one server.
After building an server, make a copy of the first server files at OS level
and copy it to the server and start the same. Configure the replication
between the two server.

By doing this, We will save the import time in second server.

Thanks
Suresh Kuna
MySQL DBA

On Wed, Jan 13, 2010 at 3:58 AM, Tom Worster  wrote:

> Frankly, I didn't entirely understand what you were proposing. I got lost
> around step 6.
>
> Is the issue total time for the procedure or service downtime?
>
>
> On 1/12/10 12:58 PM, "Lawrence Sorrillo"  wrote:
>
> > This is two upgrades done in sequence(the reload takes about three hours
> > per machine) . I can do what I am proposing in parallel.
> >
> > Do you see it as problematic?
> >
> > ~Lawrence
> >
> >
> > Tom Worster wrote:
> >> How about:
> >>
> >> 1 shut down the slave, upgrade it, restart it, let it catch up.
> >>
> >> 2 shut down the master, upgrade it, restart it, let the slave catch up.
> >>
> >> ?
> >>
> >>
> >>
> >>
> >>
> >> On 1/12/10 12:34 PM, "Lawrence Sorrillo"  wrote:
> >>
> >>
> >>> Hi:
> >>>
> >>> I want to upgrade a master and slave server from mysql 4.1 to mysql
> 5.1.
> >>>
> >>> I want to so something like follows:
> >>>
> >>> 1. Stop all write access to the master server.
> >>> 2. Ensure that replication on the slave is caught up to the last change
> >>> on the master.
> >>> 3. stop binary logging on the master.
> >>> 4. stop replication on the slave.
> >>> 5. dump the master, stop old 4.1 server, start new 5.1 server and
> reload
> >>> master dump file under 5.1 server ( binary logging is turned off)
> >>> 6. dump the slave, stop old 4.1 server, start new 5.1 server and reload
> >>> slave dump file under 5.1 server.
> >>> 7. After loading is complete, test then start binary logging on master
> >>> while still preventing updates to updates.
> >>> 8. After loading slave, test then start slave (get configs in place and
> >>> restart server).
> >>>
> >>> I am thinking that in this scenario I dont have to bother with
> recording
> >>> binlog file names and position etc etc.
> >>> That both servers will have the same databases abd replication and
> >>> binary logging will start on the two databases with no data loss and
> >>> continue forward.
> >>>
> >>>
> >>> Comments?
> >>>
> >>> ~Lawrence
> >>>
> >>>
> >>>
> >>>
> >>
> >>
> >>
> >
> >
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=sureshkumar...@gmail.com
>
>


-- 
Thanks
Suresh Kuna
MySQL DBA

Re: upgrading mysql

[Tom Worster]

Frankly, I didn't entirely understand what you were proposing. I got lost
around step 6.

Is the issue total time for the procedure or service downtime?


On 1/12/10 12:58 PM, "Lawrence Sorrillo"  wrote:

> This is two upgrades done in sequence(the reload takes about three hours
> per machine) . I can do what I am proposing in parallel.
> 
> Do you see it as problematic?
> 
> ~Lawrence
> 
> 
> Tom Worster wrote:
>> How about:
>> 
>> 1 shut down the slave, upgrade it, restart it, let it catch up.
>> 
>> 2 shut down the master, upgrade it, restart it, let the slave catch up.
>> 
>> ?
>> 
>> 
>> 
>> 
>> 
>> On 1/12/10 12:34 PM, "Lawrence Sorrillo"  wrote:
>> 
>>   
>>> Hi:
>>> 
>>> I want to upgrade a master and slave server from mysql 4.1 to mysql 5.1.
>>> 
>>> I want to so something like follows:
>>> 
>>> 1. Stop all write access to the master server.
>>> 2. Ensure that replication on the slave is caught up to the last change
>>> on the master.
>>> 3. stop binary logging on the master.
>>> 4. stop replication on the slave.
>>> 5. dump the master, stop old 4.1 server, start new 5.1 server and reload
>>> master dump file under 5.1 server ( binary logging is turned off)
>>> 6. dump the slave, stop old 4.1 server, start new 5.1 server and reload
>>> slave dump file under 5.1 server.
>>> 7. After loading is complete, test then start binary logging on master
>>> while still preventing updates to updates.
>>> 8. After loading slave, test then start slave (get configs in place and
>>> restart server).
>>> 
>>> I am thinking that in this scenario I dont have to bother with recording
>>> binlog file names and position etc etc.
>>> That both servers will have the same databases abd replication and
>>> binary logging will start on the two databases with no data loss and
>>> continue forward.
>>> 
>>> 
>>> Comments?
>>> 
>>> ~Lawrence
>>> 
>>> 
>>> 
>>> 
>> 
>> 
>>   
> 
> 



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: upgrading mysql

[Paul DuBois]

On Jan 12, 2010, at 12:36 PM, Lawrence Sorrillo wrote:

> Hi:
> 
> I want to ensure that right after the reload that the same data is present in 
> both the master and the slave. They are in perfect sync. Then I think its 
> safe to consider starting binary logging and replication etc. And after these 
> are started, changes can start?
> 
> And in setting up replication in this manner I would not use the CHANGE 
> MASTER... I will just
> 
> master-host=xxx.xxx.xxx.xxx
> master-connect-retry=60
> master-user=auser
> master-password=apassword
> 
> in the my.cnf file and restart the slave server. From there it should start 
> reading the binary logs and committing changes properly.
> 
> Is this correct?

You're upgrading to MySQL 5.1, for which several of those options no longer 
have any effect.  Better to use CHANGE MASTER. See:

http://dev.mysql.com/doc/refman/5.1/en/replication-options-slave.html
http://dev.mysql.com/doc/refman/5.1/en/news-5-1-17.html

-- 
Paul DuBois
Sun Microsystems / MySQL Documentation Team
Madison, Wisconsin, USA
www.mysql.com


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: upgrading mysql

[Lawrence Sorrillo]

Hi:

I want to ensure that right after the reload that the same data is 
present in both the master and the slave. They are in perfect sync. Then 
I think its safe to consider starting binary logging and replication 
etc. And after these are started, changes can start?


And in setting up replication in this manner I would not use the CHANGE 
MASTER... I will just


master-host=xxx.xxx.xxx.xxx
master-connect-retry=60
master-user=auser
master-password=apassword

in the my.cnf file and restart the slave server. From there it should 
start reading the binary logs and committing changes properly.


Is this correct?

~Lawrence


Shawn Green wrote:

Lawrence Sorrillo wrote:

Hi:

I want to upgrade a master and slave server from mysql 4.1 to mysql 5.1.

I want to so something like follows:

1. Stop all write access to the master server.


ok

2. Ensure that replication on the slave is caught up to the last 
change on the master.


why? You are just going to replace it later.


3. stop binary logging on the master.


why? You can just disconnect the slave



4. stop replication on the slave.


You can do this at step 2. Just issue STOP SLAVE IO_THREAD;  The SQL 
thread can keep moving along.


5. dump the master, stop old 4.1 server, start new 5.1 server and 
reload master dump file under 5.1 server ( binary logging is turned off)


Yes. No need to create binary logs for the rebuild.

6. dump the slave, stop old 4.1 server, start new 5.1 server and 
reload slave dump file under 5.1 server.


There is a faster way.

7. After loading is complete, test then start binary logging on 
master while still preventing updates to updates.


Once you have QA-ed your new 5.1 master, you can shut it down then 
copy the entire image (binaries and all) directly to the slave 
machine.  This is much faster than rebuilding from a dump and it 
ensures that you have identical data to start replication with.


After the copy, then restart the master with binary logging.


8. After loading slave, test then start slave (get configs in place 
and restart server).




Yes, it's always good to test any server image before putting it online.

The CHANGE MASTER TO command to use for the slave will be at position 
4 of the first binary log created after the binary image was captured.



I am thinking that in this scenario I dont have to bother with 
recording binlog file names and position etc etc.
That both servers will have the same databases abd replication and 
binary logging will start on the two databases with no data loss and 
continue forward.


You are correct. Because you are re-imaging your slave from your 
master, there is no need to track binary log or relay log positions.


See also:
http://dev.mysql.com/doc/refman/5.1/en/replication-howto-rawdata.html

** SAFETY ADVICE ** - always ensure you have a clean binary backup of 
any server you want to perform major maintenance to. In the off-chance 
that something does happen to go wrong, you will have it available for 
the fastest possible restore-to-original-state







--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: upgrading mysql

[Shawn Green]

Lawrence Sorrillo wrote:

Hi:

I want to upgrade a master and slave server from mysql 4.1 to mysql 5.1.

I want to so something like follows:

1. Stop all write access to the master server.


ok

2. Ensure that replication on the slave is caught up to the last change 
on the master.


why? You are just going to replace it later.


3. stop binary logging on the master.


why? You can just disconnect the slave



4. stop replication on the slave.


You can do this at step 2. Just issue STOP SLAVE IO_THREAD;  The SQL 
thread can keep moving along.


5. dump the master, stop old 4.1 server, start new 5.1 server and reload 
master dump file under 5.1 server ( binary logging is turned off)


Yes. No need to create binary logs for the rebuild.

6. dump the slave, stop old 4.1 server, start new 5.1 server and reload 
slave dump file under 5.1 server.


There is a faster way.

7. After loading is complete, test then start binary logging on master 
while still preventing updates to updates.


Once you have QA-ed your new 5.1 master, you can shut it down then copy 
the entire image (binaries and all) directly to the slave machine.  This 
is much faster than rebuilding from a dump and it ensures that you have 
identical data to start replication with.


After the copy, then restart the master with binary logging.


8. After loading slave, test then start slave (get configs in place and 
restart server).




Yes, it's always good to test any server image before putting it online.

The CHANGE MASTER TO command to use for the slave will be at position 4 
of the first binary log created after the binary image was captured.



I am thinking that in this scenario I dont have to bother with recording 
binlog file names and position etc etc.
That both servers will have the same databases abd replication and 
binary logging will start on the two databases with no data loss and 
continue forward.


You are correct. Because you are re-imaging your slave from your master, 
there is no need to track binary log or relay log positions.


See also:
http://dev.mysql.com/doc/refman/5.1/en/replication-howto-rawdata.html

** SAFETY ADVICE ** - always ensure you have a clean binary backup of 
any server you want to perform major maintenance to. In the off-chance 
that something does happen to go wrong, you will have it available for 
the fastest possible restore-to-original-state


--
Shawn Green, MySQL Senior Support Engineer
Sun Microsystems, Inc.
Office: Blountville, TN



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: upgrading mysql

[Lawrence Sorrillo]

This is two upgrades done in sequence(the reload takes about three hours 
per machine) . I can do what I am proposing in parallel.


Do you see it as problematic?

~Lawrence


Tom Worster wrote:

How about:

1 shut down the slave, upgrade it, restart it, let it catch up.

2 shut down the master, upgrade it, restart it, let the slave catch up.

?





On 1/12/10 12:34 PM, "Lawrence Sorrillo"  wrote:

  

Hi:

I want to upgrade a master and slave server from mysql 4.1 to mysql 5.1.

I want to so something like follows:

1. Stop all write access to the master server.
2. Ensure that replication on the slave is caught up to the last change
on the master.
3. stop binary logging on the master.
4. stop replication on the slave.
5. dump the master, stop old 4.1 server, start new 5.1 server and reload
master dump file under 5.1 server ( binary logging is turned off)
6. dump the slave, stop old 4.1 server, start new 5.1 server and reload
slave dump file under 5.1 server.
7. After loading is complete, test then start binary logging on master
while still preventing updates to updates.
8. After loading slave, test then start slave (get configs in place and
restart server).

I am thinking that in this scenario I dont have to bother with recording
binlog file names and position etc etc.
That both servers will have the same databases abd replication and
binary logging will start on the two databases with no data loss and
continue forward.


Comments?

~Lawrence







  




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

RE: upgrading mysql

[Joshua Gordon]

Also see http://dev.mysql.con/doc/refman/5.0/en/mysql-upgrade.html.
And make sure you make a backup before you do anything :)

-Original Message-
From: Tom Worster [mailto:f...@thefsb.org] 
Sent: Tuesday, January 12, 2010 10:47 AM
To: Lawrence Sorrillo; mysql@lists.mysql.com
Subject: Re: upgrading mysql

How about:

1 shut down the slave, upgrade it, restart it, let it catch up.

2 shut down the master, upgrade it, restart it, let the slave catch up.

?





On 1/12/10 12:34 PM, "Lawrence Sorrillo"  wrote:

> Hi:
> 
> I want to upgrade a master and slave server from mysql 4.1 to mysql
5.1.
> 
> I want to so something like follows:
> 
> 1. Stop all write access to the master server.
> 2. Ensure that replication on the slave is caught up to the last
change
> on the master.
> 3. stop binary logging on the master.
> 4. stop replication on the slave.
> 5. dump the master, stop old 4.1 server, start new 5.1 server and
reload
> master dump file under 5.1 server ( binary logging is turned off)
> 6. dump the slave, stop old 4.1 server, start new 5.1 server and
reload
> slave dump file under 5.1 server.
> 7. After loading is complete, test then start binary logging on master
> while still preventing updates to updates.
> 8. After loading slave, test then start slave (get configs in place
and
> restart server).
> 
> I am thinking that in this scenario I dont have to bother with
recording
> binlog file names and position etc etc.
> That both servers will have the same databases abd replication and
> binary logging will start on the two databases with no data loss and
> continue forward.
> 
> 
> Comments?
> 
> ~Lawrence
> 
> 
> 



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=jgor...@westernwats.com


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: upgrading mysql

[Tom Worster]

How about:

1 shut down the slave, upgrade it, restart it, let it catch up.

2 shut down the master, upgrade it, restart it, let the slave catch up.

?





On 1/12/10 12:34 PM, "Lawrence Sorrillo"  wrote:

> Hi:
> 
> I want to upgrade a master and slave server from mysql 4.1 to mysql 5.1.
> 
> I want to so something like follows:
> 
> 1. Stop all write access to the master server.
> 2. Ensure that replication on the slave is caught up to the last change
> on the master.
> 3. stop binary logging on the master.
> 4. stop replication on the slave.
> 5. dump the master, stop old 4.1 server, start new 5.1 server and reload
> master dump file under 5.1 server ( binary logging is turned off)
> 6. dump the slave, stop old 4.1 server, start new 5.1 server and reload
> slave dump file under 5.1 server.
> 7. After loading is complete, test then start binary logging on master
> while still preventing updates to updates.
> 8. After loading slave, test then start slave (get configs in place and
> restart server).
> 
> I am thinking that in this scenario I dont have to bother with recording
> binlog file names and position etc etc.
> That both servers will have the same databases abd replication and
> binary logging will start on the two databases with no data loss and
> continue forward.
> 
> 
> Comments?
> 
> ~Lawrence
> 
> 
> 



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

upgrading mysql

[Lawrence Sorrillo]

Hi:

I want to upgrade a master and slave server from mysql 4.1 to mysql 5.1.

I want to so something like follows:

1. Stop all write access to the master server.
2. Ensure that replication on the slave is caught up to the last change 
on the master.

3. stop binary logging on the master.
4. stop replication on the slave.
5. dump the master, stop old 4.1 server, start new 5.1 server and reload 
master dump file under 5.1 server ( binary logging is turned off)
6. dump the slave, stop old 4.1 server, start new 5.1 server and reload 
slave dump file under 5.1 server.
7. After loading is complete, test then start binary logging on master 
while still preventing updates to updates.
8. After loading slave, test then start slave (get configs in place and 
restart server).


I am thinking that in this scenario I dont have to bother with recording 
binlog file names and position etc etc.
That both servers will have the same databases abd replication and 
binary logging will start on the two databases with no data loss and 
continue forward.



Comments?

~Lawrence




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: upgrading from 4.1 to 5.4

[Joerg Bruehe]

Hi!


I don't do DBA work, so my info may be incomplete:

monem mysql wrote:
> Hello
> 
> 
> I have to upgrade many mysql databases from mysql 4 and 4.1 to 5.4 with a
> large size 2.7 TB
> 
> [[...]]
> 
> 
> The official method takes too much time. But I've read that we can use '*dump
> and reload'* to upgrade directly to 5.1, will it work with 5.4?

I am not aware of any significant difference between 5.1 and 5.4 in the
MyISAM area, so there are good chances that experiences with 5.1 are
applicable to 5.4 as well. (InnoDB would be a different matter.)

> 
> Also the tables contain many charset? Will they be altered?

Going from 4.0 or 4.1 to 5.1 and up brings you all the changes in
charset / collation handling, but there should be no additional
difference between 5.1 and 5.4 AFAIK.

> 
> [[...]]
> 
> Are there any better solution and any precaution to take?

I second Gavin's advice: Do a test migration and check the most
important ones of your applications. Pay special attention to "strange"
characters outside the ASCII range.


HTH,
Jörg

-- 
Joerg Bruehe,  MySQL Build Team,  joerg.bru...@sun.com
Sun Microsystems GmbH,   Komturstraße 18a,   D-12099 Berlin
Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Wolf Frenkel
Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB161028


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

RE: upgrading from 4.1 to 5.4

[Gavin Towey]

Using mysqldump and loading directly into 5.4 *might* work, but you should 
never do any of this on your production system without testing.

Get another box, start with 4.1 and do the upgrade on a test server -- even 
test your queries as there a few incompatible changes between 4 and 5.  One you 
know the exact steps you need to take, and how much time it takes, then you can 
plan the upgrade accordingly on your live system.

Regards,
Gavin Towey

-Original Message-
From: monem mysql [mailto:monem.my...@gmail.com]
Sent: Thursday, October 01, 2009 9:31 AM
To: mysql@lists.mysql.com
Subject: upgrading from 4.1 to 5.4

Hello


I have to upgrade many mysql databases from mysql 4 and 4.1 to 5.4 with a
large size 2.7 TB

All tables use the MyISAM engine.

I have to make that update on live system with minimal down time possible.



The official method takes too much time. But I've read that we can use '*dump
and reload'* to upgrade directly to 5.1, will it work with 5.4?

Also the tables contain many charset? Will they be altered?



It's first time that I do that.

Are there any better solution and any precaution to take?



thanks for your help.



monem

The information contained in this transmission may contain privileged and 
confidential information. It is intended only for the use of the person(s) 
named above. If you are not the intended recipient, you are hereby notified 
that any review, dissemination, distribution or duplication of this 
communication is strictly prohibited. If you are not the intended recipient, 
please contact the sender by reply email and destroy all copies of the original 
message.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

upgrading from 4.1 to 5.4

[monem mysql]

Hello


I have to upgrade many mysql databases from mysql 4 and 4.1 to 5.4 with a
large size 2.7 TB

All tables use the MyISAM engine.

I have to make that update on live system with minimal down time possible.



The official method takes too much time. But I’ve read that we can use ‘*dump
and reload’* to upgrade directly to 5.1, will it work with 5.4?

Also the tables contain many charset? Will they be altered?



It's first time that I do that.

Are there any better solution and any precaution to take?



thanks for your help.



monem

Re: Upgrading from 5.0.32 via a replication chain and bug 24432

[Per Jessen]

David Harrison wrote:

> Hi all,
> 
> I've got a quite large database (23G) that is running on a 5.0.32
> version of MySQL.  I really want to upgrade out of 5.0.32 to the
> latest version of 5.1 (or even 5.4) but a straight mysql_upgrade of
> the database takes long enough that I'd have serious down-time issues
> (last time I benchmarked the upgrade it came in at over day).

This may or may not be useful, but I've just upgraded from 5.0.51 to
5.1.36, which took about 6 hours using mysqldump+reload - the database
is about 20Gb. 


/Per Jessen, Zürich


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Upgrading from 5.0.32 via a replication chain and bug 24432

[David Harrison]

Hi all,

I've got a quite large database (23G) that is running on a 5.0.32
version of MySQL.  I really want to upgrade out of 5.0.32 to the
latest version of 5.1 (or even 5.4) but a straight mysql_upgrade of
the database takes long enough that I'd have serious down-time issues
(last time I benchmarked the upgrade it came in at over day).

To try and work around this I wanted to set up binary replication from
my current database (as master) to a new database.  This has meant
that I have run across bug #24432
(http://bugs.mysql.com/bug.php?id=24432) which means that replication
is broken from my current version to versions above 5.0.34.

The bug listing includes the following replication table:

  master  (-inf, 5.0.23)[5.0.24, 5.0.34]  [5.0.36,+inf)
slave
(-inf, 5.0.23)BUG#20188 both bugs, no error   BUG#20188
[5.0.24, 5.0.34]  this bug, no errorno bugthis bug, no error
[5.0.36, +inf)no bugthis bug, error   no bug

which seems to indicate to me that there's no way out of the version
I'm running aside from a mysql_upgrade.

Does anyone have experience of this bug, or of any options I have that
wouldn't require a significant outage ?

Cheers
Dave

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: upgrading from 4.1 to 5.0 "trick"

[Hank]

On Fri, Aug 28, 2009 at 9:18 AM, Shawn Green  wrote:

> Hank wrote:
>
>> Hello All,
>>  I'm in the process of upgrading my database from 4.1 to 5.0 on CentOS.
>>  I've been testing the "mysqlcheck --check-upgrade   --auto-repair"
>> command,
>> and on one of my MYISAM tables, it's taking forever to upgrade the table.
>>  It has about 114 million rows, and I'm guessing it needs to be upgraded
>> due
>> to the VARCHAR columns. Anyway, it's been running for a day and a half,
>> and
>> I finally had to kill it.
>>
>> So will this old "trick" still work?  I've done this many times on 4.1
>> with
>> great success:
>>
>> In mysql 5.0 - I create two new empty tables, one identical to the
>> original
>> and one identical but with no indexes.  I name these tables with "_ion"
>> and
>> "_ioff" suffixes.
>>
>> I then do a "insert into table_ioff select * from source" which inserts
>> just
>> the original data into the new table, but doesn't have to rebuild any
>> indexes.  I then flush the tables.
>>
>> Then in the file system, I swap the "table_ion.frm" and "table_ion.MYI"
>> files with the table_ioff ones.  Flush tables again.
>>
>>  I then just use myisamchk -r to repair the index file.  It runs in about
>> an
>> hour.
>>
>> Can I do this same thing to "upgrade" the tables, instead of using
>> mysqlcheck, which seems to be rebuilding the table row-by-row, instead of
>> sorting (which myisamchk does).
>>
>> thanks.
>>
>> -Hank
>>
>>
> Hello Hank,
>
> Your technique will work within the following narrow limits of operation:
>
> * This will only work for MyISAM tables.
>
> * myisamchk is dangerous to run against any table that is in active use as
> it operates at the file level and has caused corruptions with live tables.
>  Whenever possible either stop the server or prevent access from MySQL to
> that table with a FLUSH TABLES WITH READ LOCK before using myisamchk.
>
> http://dev.mysql.com/doc/refman/5.0/en/flush.html
>
> Alternatively, you should be able to match or improve this "import then
> index" process if you use an "ALTER TABLE ... DISABLE KEYS" command before
> the import followed by an "ALTER TABLE ... ENABLE KEYS" command after the
> import or if you use LOAD DATA INFILE ... . Also if you can import all of
> the data to an empty table in a single batch (statement), the indexes will
> be computed only once using the batch-index algorithm (it's a sort, not a
> merge) and that will also save processing time.
>
> http://dev.mysql.com/doc/refman/5.0/en/alter-table.html
> http://dev.mysql.com/doc/refman/5.0/en/load-data.html
>
> http://dev.mysql.com/doc/refman/5.0/en/insert-speed.html
>
> The overall problem is still that the on-disk structure of the 5.0 tables
> has changed and that you still need to perform some kind of dump-restore or
> rebuild of the data as part of the conversion.
> Warmest regards,
> Shawn Green, MySQL Senior Support Engineer
> Sun Microsystems, Inc.
> Office: Blountville, TN
>
>
> Hello Shawn,

 Thanks for your reply.   Yes, I have all of your conditions covered.
1. They are myisam tables
2. This is not a production system, so other people aren't accessing the
tables.
3. And your last comment about dump/restore is taken care of (in my original
note) since I am creating a new table (without indexes) in mysql 5.0, and
then inserting all the data from the old table into the new one.  Then I'm
swapping the MYI/frm files, and then rebuilding the new table.

I've tested this several times now, and it works like a charm.

Finally, I don't like to use the "ALTER TABLE DISABLE/ENABLE" statements,
since they operate in silent mode -- I have no idea what it's doing, or how
long to expect the process to take.  It would be very nice of those commands
had some built-in progress meter or feedback/callback method.

Re: upgrading from 4.1 to 5.0 "trick"

[Shawn Green]

Hank wrote:

Hello All,
  I'm in the process of upgrading my database from 4.1 to 5.0 on CentOS.
 I've been testing the "mysqlcheck --check-upgrade   --auto-repair" command,
and on one of my MYISAM tables, it's taking forever to upgrade the table.
 It has about 114 million rows, and I'm guessing it needs to be upgraded due
to the VARCHAR columns. Anyway, it's been running for a day and a half, and
I finally had to kill it.

So will this old "trick" still work?  I've done this many times on 4.1 with
great success:

In mysql 5.0 - I create two new empty tables, one identical to the original
and one identical but with no indexes.  I name these tables with "_ion" and
"_ioff" suffixes.

I then do a "insert into table_ioff select * from source" which inserts just
the original data into the new table, but doesn't have to rebuild any
indexes.  I then flush the tables.

Then in the file system, I swap the "table_ion.frm" and "table_ion.MYI"
files with the table_ioff ones.  Flush tables again.

 I then just use myisamchk -r to repair the index file.  It runs in about an
hour.

Can I do this same thing to "upgrade" the tables, instead of using
mysqlcheck, which seems to be rebuilding the table row-by-row, instead of
sorting (which myisamchk does).

thanks.

-Hank



Hello Hank,

Your technique will work within the following narrow limits of operation:

* This will only work for MyISAM tables.

* myisamchk is dangerous to run against any table that is in active use 
as it operates at the file level and has caused corruptions with live 
tables.  Whenever possible either stop the server or prevent access from 
MySQL to that table with a FLUSH TABLES WITH READ LOCK before using 
myisamchk.


http://dev.mysql.com/doc/refman/5.0/en/flush.html

Alternatively, you should be able to match or improve this "import then 
index" process if you use an "ALTER TABLE ... DISABLE KEYS" command 
before the import followed by an "ALTER TABLE ... ENABLE KEYS" command 
after the import or if you use LOAD DATA INFILE ... . Also if you can 
import all of the data to an empty table in a single batch (statement), 
the indexes will be computed only once using the batch-index algorithm 
(it's a sort, not a merge) and that will also save processing time.


http://dev.mysql.com/doc/refman/5.0/en/alter-table.html
http://dev.mysql.com/doc/refman/5.0/en/load-data.html

http://dev.mysql.com/doc/refman/5.0/en/insert-speed.html


The overall problem is still that the on-disk structure of the 5.0 
tables has changed and that you still need to perform some kind of 
dump-restore or rebuild of the data as part of the conversion.


Warmest regards,
--
Shawn Green, MySQL Senior Support Engineer
Sun Microsystems, Inc.
Office: Blountville, TN



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

upgrading from 4.1 to 5.0 "trick"

[Hank]

Hello All,
  I'm in the process of upgrading my database from 4.1 to 5.0 on CentOS.
 I've been testing the "mysqlcheck --check-upgrade   --auto-repair" command,
and on one of my MYISAM tables, it's taking forever to upgrade the table.
 It has about 114 million rows, and I'm guessing it needs to be upgraded due
to the VARCHAR columns. Anyway, it's been running for a day and a half, and
I finally had to kill it.

So will this old "trick" still work?  I've done this many times on 4.1 with
great success:

In mysql 5.0 - I create two new empty tables, one identical to the original
and one identical but with no indexes.  I name these tables with "_ion" and
"_ioff" suffixes.

I then do a "insert into table_ioff select * from source" which inserts just
the original data into the new table, but doesn't have to rebuild any
indexes.  I then flush the tables.

Then in the file system, I swap the "table_ion.frm" and "table_ion.MYI"
files with the table_ioff ones.  Flush tables again.

 I then just use myisamchk -r to repair the index file.  It runs in about an
hour.

Can I do this same thing to "upgrade" the tables, instead of using
mysqlcheck, which seems to be rebuilding the table row-by-row, instead of
sorting (which myisamchk does).

thanks.

-Hank

Re: Upgrading MySQL from 5.0 to 5.1

[Claudio Nanni]

What I always did since 3.23 upwards is new installation and import.
My tecnique allows me to install as many mysql instances as I want,
I always use specific user, homedir, datadir, my.cnf, for each installation.
In this way I can have theoretically unlimited number of mysql instances on
one host.
In your case I would install the new 5.1 in a custom home (e.g.
/home/mysql5123)
use your previous my.cnf from the 5.0 installation to get all previous
settings and make it use the same datadir.
To force one installation to use ONE AND ONLY ONE my.cnf start the instance
with the command line option:
(E.g. --defaults-file=/home/mysql5123/my.cnf)
in this way no side effects will take place (as long you configure correctly
my.cnf to work from the new home)

let me know if this is enough, enough clear or just was better for me to
shut up!

Claudio




2009/3/22 

> See Thread at: http://www.techienuggets.com/Detail?tx=78654 Posted on
> behalf of a User
>
> This has proved to be quite challenging. I wasn't able to do the upgrade
> and basically created another instance of 5.1 and took a backup of my
> database from 5.0 and restored it to 5.1. Then added all my users, etc.
> There's got to be a better way?
>
> I couldn't figure out how to get the new version 5.1 to see the file system
> (containing the database) that was in use by 5.0. I looked at all the
> documents and nowhere (or at least I couldn't see it) does it talk about
> upgrading the database. When you install the new version how do you get it
> ot upgrade and use the 5.0 database? Any pointers/help on this would be
> great for the next time I have to do this.
>
> Thanks.
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=claudio.na...@gmail.com
>
>

Upgrading MySQL from 5.0 to 5.1

[samk]

See Thread at: http://www.techienuggets.com/Detail?tx=78654 Posted on behalf of 
a User

This has proved to be quite challenging. I wasn't able to do the upgrade and 
basically created another instance of 5.1 and took a backup of my database from 
5.0 and restored it to 5.1. Then added all my users, etc. There's got to be a 
better way?

I couldn't figure out how to get the new version 5.1 to see the file system 
(containing the database) that was in use by 5.0. I looked at all the documents 
and nowhere (or at least I couldn't see it) does it talk about upgrading the 
database. When you install the new version how do you get it ot upgrade and use 
the 5.0 database? Any pointers/help on this would be great for the next time I 
have to do this.

Thanks.



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Upgrading and screwed up

[Carl]

Trying to upgrade from 5.0.37 to 5.1.32,  These are the steps I have taken:

1. Took a dump of the our  production database using mysqldump.

2. Downloaded the binary version for i86 Linux and placed it on a Slackware 12 
server.

3.  Foolishly ran mysql_upgrade against the data (/storage/data/mysql... these 
are all Inodb) thinking I had started mysqld for the new version.  However, it 
was probably the prior version (5.0.37.)

4.  Moved the data directory (/storage/data) to .bak (/storage/data.bak).

5.  Unpacked the .gz and changed the linked directory to the new installed 
directory.

6.  Ran the install script (scripts/mysql_install_db --user=root).  Yes, I know 
this is out of sequence, no excuses.

7.  Realized I could not make my time deadline for the install, so I started 
reverting to the previous setup... moved the new data directory to data.new, 
moved the data.bak to data, moved the new link to mysql.new and the old link 
(now named mysql.old) back to mysql.

8.  Started mysqld (really mysqld_safe) under 5.0.37 and it looked good (I 
could see the data tables, etc.)  When I attempted to start our app, the system 
complained that it a stored procedure did not exist.  Oh-oh.

At this point, I can edit the stored procedure (there are a couple of hundred 
that behave the same way) but not save it.  I can see that the stored procedure 
is in the mysql proc table and it is complete (when I bring the stored 
procedure up in Navicat, it is missing the paramters which indicates to me that 
Navicat is using something it has stored because I can see the praramters when 
I look in the table.)

To summarize the problem, mysql does not seem to be able to find/see the stored 
procedures.

Anyone have any ideas?

TIA,

Carl

Re: Upgrading

[Wm Mussatto]

On Mon, March 9, 2009 12:51, Mihail Manolov wrote:
> I would strongly suggest logging all your 4.0 queries for at least 24
> hours and then running them on your new 5.x server to avoid any
> surprises such as incompatible queries for example.
>
> Good luck!
>
> Mihail
Good idea.  I would pay particular attention to LEFT JOINs.  MySQL began
more strictly following the SQL specs and that caused me problems when I
did the upgrade.

Bill
> On Mar 9, 2009, at 1:42 PM, Matthew Stuart wrote:
>
>> Hi all, I am on... wait for it... version 4.0.25 and I want to
>> upgrade to MySQL 5.x
>>
>> Is there anything special I should do in order to upgrade? Do I need
>> to uninstall v4 or can I just download the most current version and
>> double click to upgrade? I am not particularly hardcore mysql
>> minded, and quite honestly, if it ain't broke don't fix it is a good
>> mantra of mine... but I need to move on now as I have received an
>> sql file that is v5 compatible but not v4 compatible.
>>
> mysql@lists.mysql.com
>>
>> Once I have it up and running I'll be fine, it's just that I am
>> nervous about upgrading and consequently breaking it, and at that
>> point, I'll be struggling to put it right.
>>
>> Any advice on how I can best do this / best practices etc will be
>> very much appreciated.
>>
>> Many thanks.
>>
>> Mat
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:
>> http://lists.mysql.com/mysql?unsub=mmano...@liquidation.com
>>
>
>



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Upgrading

[Mihail Manolov]

I would strongly suggest logging all your 4.0 queries for at least 24  
hours and then running them on your new 5.x server to avoid any  
surprises such as incompatible queries for example.


Good luck!

Mihail

On Mar 9, 2009, at 1:42 PM, Matthew Stuart wrote:

Hi all, I am on... wait for it... version 4.0.25 and I want to  
upgrade to MySQL 5.x


Is there anything special I should do in order to upgrade? Do I need  
to uninstall v4 or can I just download the most current version and  
double click to upgrade? I am not particularly hardcore mysql  
minded, and quite honestly, if it ain't broke don't fix it is a good  
mantra of mine... but I need to move on now as I have received an  
sql file that is v5 compatible but not v4 compatible.



mysql@lists.mysql.com


Once I have it up and running I'll be fine, it's just that I am  
nervous about upgrading and consequently breaking it, and at that  
point, I'll be struggling to put it right.


Any advice on how I can best do this / best practices etc will be  
very much appreciated.


Many thanks.

Mat

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=mmano...@liquidation.com

Re: Upgrading

[Andy Shellam]

Hi Mat,

How many databases have you got running on 4.0?  You can certainly go 
through the motions of downloading each interim release, however my best 
advice would be (if time/disk space permits) to dump your databases to 
plain SQL files (using mysqldump) obliterate your 4.0 install, install a 
fresh copy of 5.1 and restore your database dumps.


If your databases are hefty (I'd say above 1GB) you may need to go for 
an upgrade, in which case I think you need to go to 4.1 before 5.0 if 
memory serves me correctly.


Of course, depending on your setup, you could install a fresh copy of 
5.1 and run it alongside 4.0 (ie on different port numbers) then you can 
migrate your databases/systems across one-by-one.


Andy

Matthew Stuart wrote:
Hi all, I am on... wait for it... version 4.0.25 and I want to upgrade 
to MySQL 5.x


Is there anything special I should do in order to upgrade? Do I need 
to uninstall v4 or can I just download the most current version and 
double click to upgrade? I am not particularly hardcore mysql minded, 
and quite honestly, if it ain't broke don't fix it is a good mantra of 
mine... but I need to move on now as I have received an sql file that 
is v5 compatible but not v4 compatible.


Once I have it up and running I'll be fine, it's just that I am 
nervous about upgrading and consequently breaking it, and at that 
point, I'll be struggling to put it right.


Any advice on how I can best do this / best practices etc will be very 
much appreciated.


Many thanks.

Mat



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Upgrading

[Matthew Stuart]

Hi all, I am on... wait for it... version 4.0.25 and I want to upgrade  
to MySQL 5.x


Is there anything special I should do in order to upgrade? Do I need  
to uninstall v4 or can I just download the most current version and  
double click to upgrade? I am not particularly hardcore mysql minded,  
and quite honestly, if it ain't broke don't fix it is a good mantra of  
mine... but I need to move on now as I have received an sql file that  
is v5 compatible but not v4 compatible.


Once I have it up and running I'll be fine, it's just that I am  
nervous about upgrading and consequently breaking it, and at that  
point, I'll be struggling to put it right.


Any advice on how I can best do this / best practices etc will be very  
much appreciated.


Many thanks.

Mat

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Upgrading from 4.1 to 5.0

[Brent Baisley]

You may want to try replication. Setup your replication server as  
5.0.  That server gives you a chance to play to get things right  
without affecting the master server. You'll still need to do a dump to  
get the slave up to speed. Once you get everything right, you can  
switch over and the slave becomes the master.


Very simple in theory, a bit more complicated in practice.

Brent Baisley
Systems Architect


On Apr 23, 2008, at 2:28 PM, Paul Choi wrote:

Does anyone have experience with upgrading large databases (~500GB  
each)
from MySQL 4.1 to 5.0? The tables are in InnoDB format. We are using  
the

Community version.

I've read that it's recommended that you use mysqldump and then  
restore,

but this is not possible for us, as we cannot have our databases down
for long, nor can we have our tables locked while doing dump.

I've tried doing the following steps:
  ibbackup --restore
  copy over mysql table dirs.
  set default char set to latin1 (or will default to utf8) in my.cnf
because that's the original char set in 4.1
  Upgrade only mysql database (user and privilege tables)
  mysqlcheck --check-upgrade --auto-repair mysql
  mysql_fix_privilege_tables

I've written a script to compare data between the original 4.1 and the
new 5.0. Looks like certain rows have different numerical data... so
this is not good.

I didn't want to do mysql_upgrade on all databases in this instance of
MySQL because that resulted in 2 things happening:
  1) Don't set default char set to latin1. Run mysql_upgrade
 Some rows had data truncated in certain columns.
  2) Set default char set to latin1. Run mysql_upgrade
 Copies to TMP table. Takes forever... This is unacceptable for  
us.


What is the recommended way to upgrade from 4.1 to 5.0? Or are we  
stuck

using 4.1 forever?

-Paul Choi
Plaxo, Inc.


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




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

Upgrading from 4.1 to 5.0

[Paul Choi]

Does anyone have experience with upgrading large databases (~500GB each)
from MySQL 4.1 to 5.0? The tables are in InnoDB format. We are using the
Community version.

I've read that it's recommended that you use mysqldump and then restore,
but this is not possible for us, as we cannot have our databases down
for long, nor can we have our tables locked while doing dump.

I've tried doing the following steps:
   ibbackup --restore
   copy over mysql table dirs.
   set default char set to latin1 (or will default to utf8) in my.cnf
because that's the original char set in 4.1
   Upgrade only mysql database (user and privilege tables)
   mysqlcheck --check-upgrade --auto-repair mysql
   mysql_fix_privilege_tables

I've written a script to compare data between the original 4.1 and the
new 5.0. Looks like certain rows have different numerical data... so
this is not good.

I didn't want to do mysql_upgrade on all databases in this instance of
MySQL because that resulted in 2 things happening:
   1) Don't set default char set to latin1. Run mysql_upgrade
  Some rows had data truncated in certain columns.
   2) Set default char set to latin1. Run mysql_upgrade
  Copies to TMP table. Takes forever... This is unacceptable for us.

What is the recommended way to upgrade from 4.1 to 5.0? Or are we stuck
using 4.1 forever?

-Paul Choi
Plaxo, Inc.


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

Upgrading MySQL 4.0 to 5.0

[John Pacylowski]

Has anyone upgraded MySQL 4.0 to 5.0 on a Mac running Mac OS X  
Panther, 10.3.9 Sever with Lasso 8.5.4?  I'm debating whether to just  
upgrade to MySQL to 5.0 or jump to Apples Leopard Server.


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

Re: upgrading mysql on RHEL4

[Joerg Bruehe]

Hi !

perl pra schrieb:
> [[...]]
> 
> Also please tell me where can i get mysql5.1 enterprise edition.

5.1 is currently labeled "rc" (current version is 5.1.22-rc), so there
is no enterprise edition yet.

When there will be one, it will be for paying customers, and they have
got (or will receive) the download instructions.


HTH,
Jörg

-- 
Joerg Bruehe, Senior Production Engineer
MySQL AB, www.mysql.com


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

Re: upgrading mysql on RHEL4

[Saravanan]

Hi,

Take backup of the existing data before upgrading for safety. There is RHEL 4 
specific  rpm binary is existing in the downloading section. After installing 
run the required tools comes with mysql.


Before upgrading with existing datas read the documentation carefully.

http://dev.mysql.com/doc/refman/5.1/en/installing.html

Saravanan

--- On Fri, 1/18/08, perl pra <[EMAIL PROTECTED]> wrote:

> From: perl pra <[EMAIL PROTECTED]>
> Subject: upgrading mysql on RHEL4
> To: mysql@lists.mysql.com
> Date: Friday, January 18, 2008, 10:21 PM
> Hi Gurus,
> 
> I have mysql4.x installed on REHL4.
> 
> Can anybody let me know how to upgrade it to 5.1.
> 
> Also please tell me where can i get mysql5.1 enterprise
> edition.
> 
> Thanks in advance
> Siva


  

Looking for last minute shopping deals?  
Find them fast with Yahoo! Search.  
http://tools.search.yahoo.com/newsearch/category.php?category=shopping

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

upgrading mysql on RHEL4

[perl pra]

Hi Gurus,

I have mysql4.x installed on REHL4.

Can anybody let me know how to upgrade it to 5.1.

Also please tell me where can i get mysql5.1 enterprise edition.

Thanks in advance
Siva

Upgrading PHP + MySQL

[David Zentgraf]

Hi,

I have a CentOS 3 server that I need to update to MySQL 5 + PHP4. I  
downloaded and installed the MySQL client and server RPM packages for  
Red Hat 3, including the Shared Compatibility Libraries which  
provide /usr/lib/libmysqlclient.so.10, .12, .14 and .15, which seems  
to be the recommended way of upgrading a MySQL 3 installation. I then  
went on to recompile PHP 4.4.7, which worked fine. But my PHP  
installation is still using libmysqlclient.so.10, which does not play  
100% correctly with the current MySQL 5 server. I'd need it to use  
libmysqlclient.so.15. Apparently /usr/lib/php4/mysql.so is linked to  
libmysqlclient.so.10.


How do I update/replace/relink the php-mysql connector to have PHP  
talk to MySQL using the current client libraries?


Any hints'd greatly appreciated.
Cheers,
Dav

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

Re: log files and upgrading

[Michael Dykman]

I don't think there is any way to lock down the general log to a
single database..  perhaps if you tell us what you are trying to
accomplish, we might be able to propose something..


As of today, 5.0.45 is the recommended install version.
http://dev.mysql.com/downloads/mysql/5.0.html

 - michael dykman

On 9/20/07, Malka Cymbalista <[EMAIL PROTECTED]> wrote:
> Is it possible to log information to the general log file only for a specific 
> database?
>
> We are currently running MySQL 4.0.15.  We are planning on moving to a new 
> server so and will upgrade MySQL.  What is the latest most stable version 
> that is recommended?
>
> Thanks for any information.
> --
>
> Malka Cymbalista
> Webmaster, Weizmann Institute of Science
> [EMAIL PROTECTED]
> 08-934-3036
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>
>


-- 
 - michael dykman
 - [EMAIL PROTECTED]

 - All models are wrong.  Some models are useful.

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

log files and upgrading

[Malka Cymbalista]

Is it possible to log information to the general log file only for a specific 
database?

We are currently running MySQL 4.0.15.  We are planning on moving to a new 
server so and will upgrade MySQL.  What is the latest most stable version that 
is recommended?

Thanks for any information.
-- 

Malka Cymbalista
Webmaster, Weizmann Institute of Science
[EMAIL PROTECTED]
08-934-3036


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

Re: Unknown column error after upgrading from 4.0 to 5.0

[Johan Höök]

Hi Frederico,
the precedence between the comma-operator and JOIN changed
with 5.0.12.
See http://dev.mysql.com/doc/refman/5.0/en/join.html
Excerpt from that article:
Previously, the comma operator (,) and JOIN both had the same 
precedence, so the join expression t1, t2 JOIN t3 was interpreted as 
((t1, t2) JOIN t3). Now JOIN has higher precedence, so the expression is 
interpreted as (t1, (t2 JOIN t3)). This change affects statements that 
use an ON clause, because that clause can refer only to columns in the 
operands of the join, and the change in precedence changes 
interpretation of what those operands are.


Example:
CREATE TABLE t1 (i1 INT, j1 INT);
CREATE TABLE t2 (i2 INT, j2 INT);
CREATE TABLE t3 (i3 INT, j3 INT);
INSERT INTO t1 VALUES(1,1);
INSERT INTO t2 VALUES(1,1);
INSERT INTO t3 VALUES(1,1);
SELECT * FROM t1, t2 JOIN t3 ON (t1.i1 = t3.i3);

Previously, the SELECT was legal due to the implicit grouping of t1,t2 
as (t1,t2). Now the JOIN takes precedence, so the operands for the ON 
clause are t2 and t3. Because t1.i1 is not a column in either of the 
operands, the result is an Unknown column 't1.i1' in 'on clause' error. 
To allow the join to be processed, group the first two tables explicitly 
with parentheses so that the operands for the ON clause are (t1,t2) and t3:

End excerpt.

/Johan

Federico Giannici skrev:
Since we upgraded from MySQL 4.0 to 5.0 (under OpenBSD 4.1 amd64) the 
following command:


select count(*) as total from products_description pd, products p left 
join manufacturers m on p.manufacturers_id = m.manufacturers_id, 
products_to_categories p2c left join specials s on p.products_id = 
s.products_id where p.products_status = '1' and p.products_id = 
p2c.products_id and pd.products_id = p2c.products_id and pd.language_id 
= '1' and p2c.categories_id = '1'


give the following error:

ERROR 1054 (42S22): Unknown column 'p.products_id' in 'on clause'

What's wrong with that command?
And why it worked correctly under 4.0?


I tried to eliminate the aliases and use directly the real tables names 
but nothing changed.


Obviously the column exists, the following command works:

select products.products_id from products



Thanks.



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

Re: Unknown column error after upgrading from 4.0 to 5.0

[Baron Schwartz]

Try not mixing left join and comma-joins, and use an INNER JOIN keyword 
between "m.manufacturers_id, products_to_categories"


Baron

Federico Giannici wrote:
Since we upgraded from MySQL 4.0 to 5.0 (under OpenBSD 4.1 amd64) the 
following command:


select count(*) as total from products_description pd, products p left 
join manufacturers m on p.manufacturers_id = m.manufacturers_id, 
products_to_categories p2c left join specials s on p.products_id = 
s.products_id where p.products_status = '1' and p.products_id = 
p2c.products_id and pd.products_id = p2c.products_id and pd.language_id 
= '1' and p2c.categories_id = '1'


give the following error:

ERROR 1054 (42S22): Unknown column 'p.products_id' in 'on clause'

What's wrong with that command?
And why it worked correctly under 4.0?


I tried to eliminate the aliases and use directly the real tables names 
but nothing changed.


Obviously the column exists, the following command works:

select products.products_id from products



Thanks.




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

Unknown column error after upgrading from 4.0 to 5.0

[Federico Giannici]

Since we upgraded from MySQL 4.0 to 5.0 (under OpenBSD 4.1 amd64) the 
following command:


select count(*) as total from products_description pd, products p left 
join manufacturers m on p.manufacturers_id = m.manufacturers_id, 
products_to_categories p2c left join specials s on p.products_id = 
s.products_id where p.products_status = '1' and p.products_id = 
p2c.products_id and pd.products_id = p2c.products_id and pd.language_id 
= '1' and p2c.categories_id = '1'


give the following error:

ERROR 1054 (42S22): Unknown column 'p.products_id' in 'on clause'

What's wrong with that command?
And why it worked correctly under 4.0?


I tried to eliminate the aliases and use directly the real tables names 
but nothing changed.


Obviously the column exists, the following command works:

select products.products_id from products



Thanks.

--
___
__
   |-  [EMAIL PROTECTED]
   |ederico Giannici  http://www.neomedia.it
___

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

Upgrading mysql questions

[Andreas Widerøe Andersen]

Hi,
I'm currently running several Joomla websites and phpBB forums on an old
FreeBSD server running mysql-server 3.23. Yesterday I upgraded the mysql
installation to 4.0.27 successfully. No problems at all. First I made
backups, then deinstalled mysql 3.23 and finally installed 4.0.27. I then
ran the mysql_fix_privilege_tables script which gave me some warnings, but
seemed OK. The test forum and databases started up fine.

Soon, I'll upgrade to 4.1 and then later to version 5 of the mysql server.

My question is: When upgrading mysql-server and running suggested/included
update scripts etc, do they only affect the base mysql server (and
associated files)? What about all the databases (Ie. phpBB/Joomla) that was
created under 3.23, should I run some sort of upgrading script on these
also? Need some advice here. Sounds logic to me that they also need to be
updated/optimized for the new system - somehow.

I hope someone will be able to assist me a little here.

Thanks and best regards,
Andreas

Re: Upgrading databases?

[Dan Buettner]

Hi Seth -

I believe MySQL's official position is that you should always dump-and-load
the data when upgrading major or minor versions (4.0 to 4.1, 4.1 to 5.0,
etc.)

I've done it both ways (dump-load and just moving table files) and have
never had a problem with either, even when moving files across OS platforms
*knock on wood*.  That said, I think you might find dump-and-load just the
ticket to work around the problem you're having.

What you're doing *should* work, but since it isn't, I'd try another avenue
myself to avoid spending much more time on it.

Something as simple as

mysqldump -u root -ppassword -h hostwith41 --all-databases | mysql -u root
-ppassword -h hostwith51

would do it, if you want to transfer everything

Mind that you've got an appropriate network connection - you wouldn't want
to make your laptop on home wireless with DSL the in-between if you have
50GB of data to transfer.

Hope this helps, and let me know if you have any questions.

Dan


On 6/21/07, Seth Seeger <[EMAIL PROTECTED]> wrote:


On Jun 21, 2007, at 12:21 PM, Gerald L. Clark wrote:

> Seth Seeger wrote:
>> Hello,
>> I'm having trouble copying a database from MySQL 4.1.22 to 5.1.19-
>> beta.  Both are FreeBSD i386-based machines.  I have run the
>> following commands:
>> mysqlcheck --check-upgrade --all-databases --auto-repair
>> mysql_fix_privilege_tables
>> Both executed with no problems.  (mysqlcheck reported "OK" for
>> all  tables.)  When I try to access any of the tables, I get this:
>> mysql> select * from users;
>> ERROR 1034 (HY000): Incorrect key file for table 'users'; try to
>> repair it
>> So I tried to repair it:
>> mysql> repair table users;
>> +++--
>> ++
>> | Table  | Op | Msg_type |
>> Msg_text   |
>> +++--
>> ++
>> | seth_icsx_mands_live.users | repair | error| Incorrect key
>> file  for table 'users'; try to repair it |
>> +++--
>> ++
>> 1 row in set, 1 warning (0.10 sec)
>> Running "repair table users" doesn't seem to have any effect on
>> it  because the problem persists.  I have tried to run
>> mysql_upgrade,  with no success:
>> # mysql_upgrade --basedir=/usr/local --datadir=/var/db --verbose
>> Looking for 'mysql' in: mysql
>> FATAL ERROR: Can't find 'mysql'
>> I have tried it with all different combinations for the two
>> directory  options with no luck.  All tables are MyISAM.
>> Can anyone shed some light on what I'm supposed to do?
>> Thanks,
>> Seth
> Shut the server down and run myisamchk on users.MYI

Sadly, no success.  I tried running it two different ways:

# myisamchk -e -r users
- recovering (with sort) MyISAM-table 'users'
Data records: 1283
- Fixing index 1
Found block with too small length at 101420; Skipped

# myisamchk -c -r users
- recovering (with sort) MyISAM-table 'users'
Data records: 1283
- Fixing index 1

I still get the same error when I try to access the table.

Thanks,
Seth



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

Re: Upgrading databases?

[Seth Seeger]

On Jun 21, 2007, at 12:21 PM, Gerald L. Clark wrote:


Seth Seeger wrote:

Hello,
I'm having trouble copying a database from MySQL 4.1.22 to 5.1.19-  
beta.  Both are FreeBSD i386-based machines.  I have run the   
following commands:

mysqlcheck --check-upgrade --all-databases --auto-repair
mysql_fix_privilege_tables
Both executed with no problems.  (mysqlcheck reported "OK" for  
all  tables.)  When I try to access any of the tables, I get this:

mysql> select * from users;
ERROR 1034 (HY000): Incorrect key file for table 'users'; try to   
repair it

So I tried to repair it:
mysql> repair table users;
+++--  
++
| Table  | Op | Msg_type |   
Msg_text   |
+++--  
++
| seth_icsx_mands_live.users | repair | error| Incorrect key  
file  for table 'users'; try to repair it |
+++--  
++

1 row in set, 1 warning (0.10 sec)
Running "repair table users" doesn't seem to have any effect on  
it  because the problem persists.  I have tried to run  
mysql_upgrade,  with no success:

# mysql_upgrade --basedir=/usr/local --datadir=/var/db --verbose
Looking for 'mysql' in: mysql
FATAL ERROR: Can't find 'mysql'
I have tried it with all different combinations for the two  
directory  options with no luck.  All tables are MyISAM.

Can anyone shed some light on what I'm supposed to do?
Thanks,
Seth

Shut the server down and run myisamchk on users.MYI


Sadly, no success.  I tried running it two different ways:

# myisamchk -e -r users
- recovering (with sort) MyISAM-table 'users'
Data records: 1283
- Fixing index 1
Found block with too small length at 101420; Skipped

# myisamchk -c -r users
- recovering (with sort) MyISAM-table 'users'
Data records: 1283
- Fixing index 1

I still get the same error when I try to access the table.

Thanks,
Seth



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

Re: Upgrading databases?

[Gerald L. Clark]

Seth Seeger wrote:

Hello,

I'm having trouble copying a database from MySQL 4.1.22 to 5.1.19- 
beta.  Both are FreeBSD i386-based machines.  I have run the  following 
commands:


mysqlcheck --check-upgrade --all-databases --auto-repair
mysql_fix_privilege_tables

Both executed with no problems.  (mysqlcheck reported "OK" for all  
tables.)  When I try to access any of the tables, I get this:


mysql> select * from users;
ERROR 1034 (HY000): Incorrect key file for table 'users'; try to  repair it

So I tried to repair it:

mysql> repair table users;
+++-- 
++
| Table  | Op | Msg_type |  
Msg_text   |
+++-- 
++
| seth_icsx_mands_live.users | repair | error| Incorrect key file  
for table 'users'; try to repair it |
+++-- 
++

1 row in set, 1 warning (0.10 sec)

Running "repair table users" doesn't seem to have any effect on it  
because the problem persists.  I have tried to run mysql_upgrade,  with 
no success:


# mysql_upgrade --basedir=/usr/local --datadir=/var/db --verbose
Looking for 'mysql' in: mysql
FATAL ERROR: Can't find 'mysql'

I have tried it with all different combinations for the two directory  
options with no luck.  All tables are MyISAM.


Can anyone shed some light on what I'm supposed to do?

Thanks,
Seth


Shut the server down and run myisamchk on users.MYI

--
Gerald L. Clark
Supplier Systems Corporation

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

Upgrading databases?

[Seth Seeger]

Hello,

I'm having trouble copying a database from MySQL 4.1.22 to 5.1.19- 
beta.  Both are FreeBSD i386-based machines.  I have run the  
following commands:


mysqlcheck --check-upgrade --all-databases --auto-repair
mysql_fix_privilege_tables

Both executed with no problems.  (mysqlcheck reported "OK" for all  
tables.)  When I try to access any of the tables, I get this:


mysql> select * from users;
ERROR 1034 (HY000): Incorrect key file for table 'users'; try to  
repair it


So I tried to repair it:

mysql> repair table users;
+++-- 
++
| Table  | Op | Msg_type |  
Msg_text   |
+++-- 
++
| seth_icsx_mands_live.users | repair | error| Incorrect key file  
for table 'users'; try to repair it |
+++-- 
++

1 row in set, 1 warning (0.10 sec)

Running "repair table users" doesn't seem to have any effect on it  
because the problem persists.  I have tried to run mysql_upgrade,  
with no success:


# mysql_upgrade --basedir=/usr/local --datadir=/var/db --verbose
Looking for 'mysql' in: mysql
FATAL ERROR: Can't find 'mysql'

I have tried it with all different combinations for the two directory  
options with no luck.  All tables are MyISAM.


Can anyone shed some light on what I'm supposed to do?

Thanks,
Seth

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

Re: upgrading from mysql 4.0 to 4.1 : TIMESTAMP & OTHER FEATURES

[Dan Buettner]

Regarding the format of TIMESTAMP columns, one of the user comments on
http://dev.mysql.com/doc/refman/4.1/en/timestamp-4-1.html
offers the solution below:

Posted by Kjell Arne Rekaa on April 14 2005 11:11pm
If you want the same view of a timestamp field in 4.1.x as it was in
in earlier mysql versions, without the delimiter characters in date
and time, simply add a "+0" to the column name:
mysql> create table date (remember timestamp);
Query OK, 0 rows affected (0.03 sec)
mysql> insert date values ('2005051712');
Query OK, 1 row affected (0.00 sec)
mysql> select remember from date;
+-+
| remember|
+-+
| 2005-05-17 12:00:00 |
+-+
1 row in set (0.00 sec)
mysql> select remember+0 from date;
++
| remember+0 |
++
| 2005051712 |
++
1 row in set (0.00 sec)

As far as your character set, I believe that is stored by table, so
your data should remain OK.

Another major consideration with 4.1 is that passwords changed
considerably from 4.0 to 4.1:
http://dev.mysql.com/doc/refman/4.1/en/password-hashing.html

HTH,
Dan



On 12/20/06, tere <[EMAIL PROTECTED]> wrote:

Hi! I would like to upgrade the database of my organisation from
mysql4.0.22 from 4.1. We use Debian.
I've read info in the manual, but i don't have things clear.

We process data of DB with scripts, and  I'm annoyed because the change
of format  of timestamp, is there any command in 4.1 to obtain  this
info in the previous format (4.0) ??? I want that out in 4.1 as
MMDDHHMMSS

Furthermore, my databases are in latin1, i've read that 4.1 take data in
utf8, but i don't understand reading manual how this affect to my info.

And to finish, do i have to keep more features in mind

Thanks

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




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