MySQL Upgrade

From KallestadWiki

Jump to: navigation, search

I finally ran into a piece of software that requires a mysql upgrade (vBulletin 3.6.4). Since I have to go about compiling source code and running through the upgrade, I figure I should upgrade to the latest and greatest so I don't have to run through this process again in the near future.

[edit]

Upgrade Path

It is not recommended to go from 4.1 directly to 5.1. Rather, the upgrade should go from 4.1 to 5.0, then from 5.0 to 5.1. If you have a lower version of mysql than 4.1, it is recommended to upgrade to 4.1 first, then to 5.0, then to 5.1. For 3.x, upgrade to 4.0 first, then 4.1, then 5.0, then 5.1. Sounds like fun doesn't it?

[edit]

Incompatibility Issues

There are a few changes in the 5.0 release that are incompatible with previous versions. This page spells out incompatibility issues between 4.1 and 5.0. Here are some highlights:

  • Problem: The indexing order for end-space in TEXT columns for InnoDB and MyISAM tables has changed.
    • Solution: Dump and reload the table if it is an InnoDB table, or run OPTIMIZE TABLE or REPAIR TABLE if it is a MyISAM table.
  • Problem: For BINARY columns, the pad value and how it is handled has changed as of MySQL 5.0.15
    • Solution: See this discussion of binary and varbinary datatypes - fortunately, I'm not affected by this one, so I won't spend much time researching it.
  • Problem: As of MySQL 5.0.3, the server by default no longer loads user-defined functions (UDFs) unless they have at least one auxiliary symbol defined in addition to the main function symbol.
    • Solution: Either use the --allow-suspicious-udfs option, or define at least one auxiliary symbol in user defined functions. Again, I'm safe here.
  • Problem: The update log has been removed in MySQL 5.0.
    • Solution: Enable the binary log instead, if required.
  • Problem: Support for the ISAM storage engine has been removed in MySQL 5.0.
    • Solution: MyISAM is still supported, so for any ISAM tables: 
      ALTER TABLE tbl_name ENGINE = MyISAM;
  • Problem: Support for RAID options in MyISAM tables has been removed in MySQL 5.0
    • Solution: Dump the tables and remove the RAID options from the create table statements.
  • Problem: As of MySQL 5.0.25, the lc_time_names system variable specifies the locale that controls the language used to display day and month names and abbreviations. This variable affects the output from the DATE_FORMAT(), DAYNAME() and MONTHNAME() functions.
    • Solution: Review your use of these functions, or update your lc_time_names variable to the system default.

There are also a series of SQL specific compatibility issues, and a couple of C API changes. Refer to the link above for details.

[edit]

Backing up existing data structures and data

phpMyAdmin actually provides a pretty nice interface for backing up existing tables and data structures. You can also use the mysqldump utility. For a 4.x to 5.x upgrade, the recommended path would be to export all of your existing structures and data, install the new version, then import structures and data.

Retrieved from "http://www.stevekallestad.com/wiki/MySQL_Upgrade"
Personal tools