Drop Shadow

MySQL 5.0 Upgrade

It was the best of times, it was the worst of times. It was time to upgrade MySQL from 4.0 to 5.0. MySQL 5.0 was introduced on October 24, 2005, and new applications are beginning to expect it. We have upgraded MySQL on simmons.sandiego.edu from version 4.0 to version 5.0 on Monday, January 22, 2007.

This is a major upgrade. If you don’t use complex queries, you probably don’t have to worry about it.

Decimal changesDecimal column types have changed, and may require you to add more space for the decimal in your table’s structure.
JOINs in MySQL 5.0The change most likely to cause problems in your applications is a change in precedence of commas over joins. If you have complex queries that combine three or more tables, make sure you test them before we go live!
NULL comparisons are always FALSEComparisons against NULL always return NULL, which for all practical purposes means it returns FALSE in WHEREs.
Text vs. Numbers in MySQL 5.0In MySQL 5.0, items in the WHERE section of a query must be the same type as the field they’re checking; this also applies to IN lists. If they aren’t, MySQL will silently make them be the same type, which will not do what you expect it to.
Upgrading from 4.0 to 4.1We’ll be skipping over 4.1, but that doesn’t mean we can ignore the changes to SQL queries and storage.
Upgrading from 4.1 to 5.0The MySQL instructions include a lot of technical information for the server upgrade that you probably don’t care about. But it does also contain some useful information about what has changed since 4.x.

I strongly recommend testing all of your pages that use MySQL, especially if you have complex JOINs.

Comments

If you have comments or tips for readers of this page, you may post them here. Questions are more appropriately directed to the webmaster. Comment on this page