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.


Ronald Bradford said...

One interesting thing in the update to Version 5 was the re-creation of privileges.

For example:
GRANT ALL on db.* to user@localhost identified by 'password';

When you update to Version 5, and then attempt to create a view by user, you can't. You explicitly have to add additional GRANTS , where if the previous GRANT statement was executed within Version 5, CREATE VIEW is within ALL

Sheeri said...

The only way to convince a DBA to upgrade is to PROVE that it's the solution to a problem. It's not enough to say "If you upgrade, it will make business logic easier." What you want to say is "See how you have that problem? It would be easy to solve in MySQL 5.0."

IE, CISP compliance comes up ALL the time at my work. We want to modify our billing code, and thus billing tables, but we need to not alter data. ARCHIVE tables would be perfect for this, and triggers -- some kind of log of what was changed when, and by who. In fact, my boss keeps saying, "stop talking about how great 5.0 is, we're not upgrading any time soon."

Of course, once upper management heard how great 5.0 was and how easily it would solve problems, they're all for it and are making the way easier.

The other trick is to offer support. When my company went to MySQL 4.1 and started using query cacheing, they query cached everything and for 3 months the site was slow and they did not understand why. (this was before I was on board). So there has to be an understanding, probably some education, etc.

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.