Tuesday, January 24, 2006

MySQL 5 Upgrade: Convincing a DBA to upgrade

Ok, from time to time I get asked the same question by database administrators who surprisingly are still using MySQL 3.XX or MySQL 4.

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)

What may affect your application
  • " 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.
How can I convince an "experienced DBA" to really take the step?


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.

1 comment:

Frank said...

Thank you Sheeri for your comment.

I could not agree more, taking the "problem" approach should help in convincing this DBA.

Caching all queries will no doubt slow things down.

Thanks for the tips again.