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
- [5] Stored procedures: move business logic to the database
- From MySQL: This support requires the
proctable in themysqldatabase. 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”. - [5] Triggers stored routines
- [5] Support for views
"This support requires extra privilege columns in the
useranddbtables in themysqldatabase. 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”". - [5] 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.
Thanks
Frank
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.
3 comments:
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
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.
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.
Frank
Post a Comment