Tell Me Your MySQL Upgrade Stories

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
volomike
Forum Regular
Posts: 633
Joined: Wed Jan 16, 2008 9:04 am
Location: Myrtle Beach, South Carolina, USA

Tell Me Your MySQL Upgrade Stories

Post by volomike »

I'm taking a gig where I need to convert SQL and table design from MySQL4 to MySQL5 on 4 websites. Besides my own research on the differences, please share with me any stories of your own for what may have surprised you.
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Re: Tell Me Your MySQL Upgrade Stories

Post by onion2k »

There are a few more reserved words on 5 if I remember correctly. If your SQL isn't backticked properly that can trip you up. Besides that there's nothing much that can go wrong.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Re: Tell Me Your MySQL Upgrade Stories

Post by John Cartwright »

Agreed. I've migrated from 4 to 5 without any code changes.
User avatar
chaos
Forum Newbie
Posts: 22
Joined: Thu May 15, 2008 9:20 am
Location: New Jersey

Re: Tell Me Your MySQL Upgrade Stories

Post by chaos »

The main problems I ran into were to do with replication synchronization (things like LOAD TABLE FROM MASTER still being implemented but not actually, y'know, working any more). If you're not doing replication, you need not care.
User avatar
volomike
Forum Regular
Posts: 633
Joined: Wed Jan 16, 2008 9:04 am
Location: Myrtle Beach, South Carolina, USA

Re: Tell Me Your MySQL Upgrade Stories

Post by volomike »

onion2k wrote:There are a few more reserved words on 5 if I remember correctly. If your SQL isn't backticked properly that can trip you up. Besides that there's nothing much that can go wrong.
I think that's what we're finding. Another problem is the DECIMAL() data type, which changed from MySQL4 to MySQL5. For example:

DECIMAL(3,1) in 4 stores 100.0 as 100.0, even though it's not supposed to unless it's DECIMAL(4,1).

DECIMAL(3,1) in 5 stores 100.0 as 99.9, and requires you to use DECIMAL(4,1) to store 100.0.

Another problem is that one needs to follow MySQL's website explicitly on how to either upgrade an existing database inplace, or export and import a database properly from 4 to 5.

But other than that, I predict smooth sailing.
Post Reply