If a company is running MySQL 4 now and is happy with it, shouldn't it stick with that version? Yes and no, says Mike Hillyer, this site's resident MySQL expert. In this tip, he discusses when and why MySQL users should upgrade, the benefits of MySQL's new features, and how to evaluate and deploy the upcoming MySQL 5.0, now available in its beta release.
Many companies keep a policy of staying one version back from the latest release, meaning that they use Windows 2000 instead of Windows XP, MySQL 4 instead of 4.1, and so on. Such a policy promotes stability over features.
So, companies that find MySQL 4 sufficient for their needs may want to stay where they are because MySQL 4 and MySQL 4.1 are quite stable at the moment and work reliably.
Why would that company want to move to MySQL 5 now? The biggest three features of interest to most companies are stored procedures, triggers, and views.
MySQL 5's big bonuses
Stored procedures allow you to move business logic from end applications to the MySQL server, which can save effort when you have multiple end applications (instead of implementing a price calculation routine in PHP for the web and VC for the desktop, you can implement it in a stored procedure and call it from both PHP and VC).
Stored procedures can also enhance security by allowing you to prevent a user from accessing a table directly, but instead giving them permission to call a stored procedure that has access to the table.
Triggers are stored routines that are written with a syntax like that of stored procedures, but instead of being called by a user or application, triggers are activated by table events such as inserts, updates, and deletes. The triggers can modify or abort the triggering table actions, or take the information provided by the action and use it elsewhere.
I recently used a trigger to create a MyISAM FULLTEXT lookup table for an InnoDB table: any inserts, updat...
To continue reading for free, register below or login
To read more you must become a member of SearchEnterpriseLinux.com
es, or deletes to the InnoDB table were mirrored to the lookup table by triggers, and the end users didn't have to change anything about the way they interacted with the InnoDB table.
Views are essentially named virtual tables defined by SQL queries. I can take a complex set of tables, create a SELECT query that might be more meaningful to end users, then turn it into a view. The end users can then perform SELECT queries against the view and, in most cases, also execute INSERT, UPDATE, and DELETE statements against the view, with the changes being reflected in the underlying tables. Views help to simplify complicated data and also enhance security. As with a stored procedure, you can create a view and give a user permission to SELECT from the view but not the underlying table.
Evaluating MySQL 5
In the end most users will eventually move to MySQL 5 as MySQL 4 and 4.1 age and become outdated. The timing will have to depend on whether you consider the features of MySQL 5 to be useful to your organization.
When evaluating the new features, I would recommend installing MySQL 5 on a separate machine and trying it with your existing applications, run as many tests as you can and see if everything runs smoothly. If you do not encounter any errors you can then bring MySQL 5 into your production systems (after a production release of MySQL 5).
There really should not be things to watch out for, in the sense of bugs or incompatibilities, but you can always watch the changelogs at the 5.0 news page on MySQL's site and look for entries that are marked 'Incompatible Change'.
So far, bug reports have been many and varied, depending on the feature being used. As expected, most of the bugs are related to new MySQL features, such as stored procedures, triggers, and views. The full list of features added and bugs fixed can be browsed in MySQL's 5.0 news.
Preparing for an upgrade
The best tip before upgrading is to back up your data: while the upgrade process should be relatively painless, not having a backup is a recipe for disaster.
Those looking to upgrade major versions of MySQL in a production environment should test and evaluate their systems before upgrading. The ideal would be to install all software on a test machine, upgrade the MySQL installation on the test machine to MySQL 5, and then run the test machine with real data for a while to ensure there are no suprises. If all goes well on the test machine you can then move the production systems to the newer version.
Production machines should also only be upgraded to a production version of MySQL. While the beta versions of MySQL have been very stable, there are no guarantees that your application will run flawlessly on beta software.
A common error when upgrading from MySQL 4.0 to MySQL 4.1 is password hashing. Users who encounter an error such as "Client does not support authentication protocol requested" can find at solution at OpenWin.org.
There are detailed upgrade notes for all versions of MySQL on this MySQL.com page.
Most applications should not have any problem with MySQL 5, as opposed to MySQL 4.1. However, users upgrading from MySQL 4.0 should first upgrade to MySQL 4.1, then upgrade to MySQL 5. You can get all MySQL downloads on MySQL's download page.
