I know there is a new version of MySQL out. Is it wise for me to upgrade? My previous experience with non-incremental upgrades hasn't been very good. I have heard that there isn't much benefit to upgrading.
As you can imagine, the answer to this question can be found in scattered forms everywhere. MySQL blogs, tutorials and forums are filled with information about why upgrading is the solutions to most problems and definitely a good idea.
Here on MySQL Database Administration blog, I have also posted quite a few posts about why and how to upgrade to MySQL 5.
What I am aiming for is to compile the top ways to convince a reluctant DBA.
Here's my list (please add more)
- Query caching was introduced in MySQL 4 which can cache queries and improve performance of applications
-  Stored procedures: move business logic to the database
- From MySQL: This support requires the
proctable in the
mysqldatabase. To create this file, you should run the mysql_fix_privilege_tables script as described in Section 5.6, “mysql_fix_privilege_tables — Upgrade MySQL System Tables”.
-  Triggers stored routines
-  Support for views
"This support requires extra privilege columns in the
dbtables in the
mysqldatabase. To create these columns, you should run the mysql_fix_privilege_tables script as described in Section 5.6, “mysql_fix_privilege_tables — Upgrade MySQL System Tables”".
-  For replication see: upgrading replication setup
- " Several visible behaviors have changed between MySQL 4.1 and MySQL 5.0 to make MySQL more compatible with standard SQL. " See the section "Server Changes" and "SQL changes" for more
- If you store any AES_ENCRYPT data, see Mike Krejci's comment
- If after upgrading "CHECK TABLE" reports tables as crashed then explicitly "drop the fulltext indexes and add them again."
- More reserved words
- Stored procedures and functions work with replication [See]. The stored procedures and functions created on a master server replicate to slave.
JFalcon answered in response to my question:
Stored Procedures (which are good for security as you call procedures with parameters and encapsulate your queries) as opposed to dynamically generating SQL, Triggers (which allow you to perform data 'massaging' or special logging based on values that change in the database, and Views, which allow you to perform SELECT statements as if they were being done on a table, but instead a view that can internally limit the subset of data that can be returned.
Views are also good for security as you only allow access to specific fields and you can also boost performance by having your views limit the amount of data (for example, within a particular date range).
All of these database objects are well worth the move as they help increase security and productivity with regards to development of applications that take advantage of them.