Upgrading from MySQL 4.1 to 5.0

From DreamHost
Jump to: navigation, search

DreamHost is currently slowly upgrading your MySQL servers from version 4.1 to 5.0. You can also email support and request they upgrade your databases. There are some incompatibilities between versions 4.1 and 5.0, particularly with JOINs. This upgrade could cause some breakage of your application(s).

3rd Party Applications

osCommerce

osCommerce versions 2.2 milestone 2 without the '051112' update are known to be incompatible with MySQL 5.0. This is mostly because of changes to JOIN syntax (to make it more ANSI SQL compliant).

osCommerce installed using DreamHost One Click Installs before Friday January 27, 2006 are known to be incompatible with MySQL 5.0. If you have such an install, please visit the panel and click on upgrade (Note: As of January 27, 2006, this capability is not yet active on the panel. Please contact support to perform the upgrade for you instead).

osCommerce installed via One Click Installs on Friday January 27, 2006 should work just fine.

Invision Power Board

Invision Power Board with (at least) versions 1.3 and earlier are known to be incompatible with MySQL 5.0. Please contact the vendor for an upgrade.

Alternatively, you can edit the source code yourself. We know that the following query breaks:

SELECT m.id, m.name, m.msg_total, m.view_pop, m.email_pm, m.language, 
m.email, me.vdirs, g.g_max_messages, g.g_use_pm 
FROM ibf_members m, ibf_groups g LEFT JOIN ibf_member_extra me ON (m.id=me.id) 
WHERE LOWER(m.name) IN ('bobbob') AND m.mgroup=g.g_id;

We're not sure where in the code it needs to be changed, or if that query is the only query that breaks. But we do know that adding a pair of parentheses to the query fixes it. Here is how we fixed this query:

SELECT m.id, m.name, m.msg_total, m.view_pop, m.email_pm, m.language, 
m.email, me.vdirs, g.g_max_messages, g.g_use_pm 
FROM (ibf_members m, ibf_groups g) LEFT JOIN ibf_member_extra me ON (m.id=me.id) 
WHERE LOWER(m.name) IN ('bobbob') AND m.mgroup=g.g_id;

If anyone knows the specific files and changes that need to be made, we encourage you to update this page.

Invision 2.0.4

This adjustment does indeed work but there was a slight difference in code for my 2.0.4 board.

In sources > sql > mysql_queries.php around line 231 find the function... function msg_get_cc_users( $a )

That's where you should make the adjustments. Just use the above formatting information to make your changes and it should work just fine.

Invision 2.1.6

Invision Power Board 2.1.6 does work alright even though they claim it doesn't on the official site, however, they did say that they plan on supporting MYSQL 5 in the future so even if it is a little buggy now it won't be forever. Some mods do not work so just keep that in mind if you decide to use it. Just make sure you can restore your board if something goes wrong when installing a modification and you will be fine.

Your Applications

JOIN changes

There are several changes to JOINs that could break your queries. You may be using JOINs without realizing you are using them.

Below are two examples of common JOINs that you may use.

SELECT * FROM table1,table2 WHERE table1.id=table2.id;
SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id;

The changes to the way JOINs are handled are too numerous to detail here. It is HIGHLY recommended that you read and fully understand the MySQL Documentation detailing the changes to JOIN behavior.

It is HIGHLY recommended that you alter any JOINs in your application(s) to conform to the new standards before we upgrade your service from MySQL 4.1 to 5.0.

DECIMAL data type changes

The DECIMAL data type conforms more tightly to the SQL standard. The change most likely to impact you is the following. In MySQL 5.0, a DECIMAL(3,0) allows values from -999 to 999; in contrast, MySQL 4.1 additionally allows values from 1000 to 9999. Applications relying on this behavior will break during the upgrade from 4.1 to 5.0.

If your application does rely on this behavior, we recommend adding an additional digit to your DECIMAL. Assuming a DECIMAL(3,0), the ALTER TABLE statement might look like this:

ALTER TABLE t1 CHANGE COLUMN d1 d1 DECIMAL(4,0);

Other changes to the DECIMAL data type are less likely to impact you. If you are concerned, please reference the MySQL Documentation on the DECIMAL data type changes.

New Reserved Words

MySQL 5.0 introduces many new Reserved Words, which are listed below. If any of your tables or columns have the same name as the words listed below, you may run into difficulty, and some of your applications may break. If you do use one of the Reserved Words listed below as a table name or column name, you can remedy the situation by backquoting the offending name. For example, the following query,

SELECT t1.specific, undo.col2 FROM t1,undo WHERE undo.col1 = "10";

would be fixed by changing it to

SELECT t1.`specific`, `undo`.col2 FROM t1,`undo` WHERE `undo`.col1 = "10";

or perhaps even better with

SELECT `t1`.`specific`, `undo`.`col2` FROM `t1`,`undo` WHERE `undo`.`col1` = "10";

The list of Reserved Words introduced in MySQL 5.0 are as follows:

SENSITIVE, CALL, CONDITION, CONNECTION, CONTINUE, CURSOR, DECLARE, DETERMINISTIC, EACH, ELSEIF, EXIT, FETCH
GOTO, INOUT, INSENSITIVE, ITERATE, LABEL, LEAVE, LOOP, MODIFIES, OUT, READS, RELEASE, REPEAT, RETURN, SCHEMA
SCHEMAS, SENSITIVE, SPECIFIC, SQL, SQLEXCEPTION, SQLSTATE, SQLWARNING, TRIGGER, UNDO, WHILE

The full list of Reserved Words can be found at the MySQL Documentation on Treatment of Reserved Words in MySQL.

Full MySQL Documentation on Upgrading from MySQL 4.1 to 5.0

The full MySQL documentation on this topic can be found here.

There are numerous changes to MySQL 5.0 that we have not listed here. We encourage everyone to at least browse the entire list of changes described in the MySQL documentation. There may be items in that documentation not listed here that will break your application.