Stored procedures are really important for implementing security features IMHO. For example, you can give your MySQL user which logs into the database from a web application just SELECT and EXECUTE privileges. Any CRUD operations will have to go through SP’s running with DEFINER privileges (assuming that the definer has CRUD privileges), but for most adequately normalized schemata, these usually require operations on multiple tables which need to be executed within a transaction as a logical unit. Keeping that logic inside a SP makes application development much simpler IMHO and eliminates the need for CRUD privileges at the table level. SELECT statements can be executed normally by client application code since they are read-only. And since MySQL lacks the ROLE features of Oracle, PostgreSQL et al., you can mimic that by implementing login validation checks within a SP.

And speaking of table-level access, dealing with adding and deleting columns in a table, etc. — here is where you would find views useful. Views and SP’s provide the best means of implementation and data abstraction.

There are a few other alternatives to SQL/PSM: Using UDFs (also a great way to crash a server!) or something which is ran by a UDF like the V8 UDF from Roland Bouman: http://www.slideshare.net/rpbouman/my-sq-lv8udfs

The environment had many PHP app servers that were mostly idle, while the single database server whose CPU load was pegged.

It turned out the project had a policy that all SQL code was mandated to be implemented as stored procedures. Many of the procedures did dynamic SQL, and had to do complex validation of input parameters against INFORMATION_SCHEMA to assure against SQL injection.

So a disproportionate amount of application load was focused on the single db server, instead of distributed over many app servers where it could be scaled better.

Flexviews does just that. The logging function checks a session variable and only does the inserts in debug mode to reduce overhead.

I’m a great proponent of having the database manage itself for routine tasks, and have written routines that do things like daily partition management, killing long running transactions, etc. (many things like common schema), and have always built logging in to them.

The current “framework” I use is a standard InnoDB table with timestamp, program name, log level (debug, notice, warning, critical) and a string message. A procedure and or function that wraps inserting the messages (takes level, program name and message), and a table that stores the config for each program (name, log level set). Each program fetches it’s own config and then starts to log at the appropriate level depending on the flag. These can range from logging actions taken, to performance debugging (because the logs have a millisecond resolution timestamp).

Now, the extra overhead this might add to performance critical routines may not be insignificant, but you can set it to not log too, and skip most of it (other than config check).

The log table itself is partitioned, and managed with events with a rolling window stored (that log to the log table!)

Thanks for the performance_schema pointer. It seems like p_s turns into the answer for the universe.

About debugging, one can (agreed, it takes some extra work) add asserts — yes, assert — in the stored program logic.

http://stackoverflow.com/questions/9238978/sql-scripts-does-the-equivalent-of-a-define-exist/9276721#9276721

And up vote me while you are at it. Yes, shameless 😉

About the trash bin, the performance schema can help.

SELECT OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME
FROM events_statements_summary_by_program
WHERE COUNT_STAR = 0

Regards,
— Marc

Thank you. Good comments; allow me to reply to some:

– “Having used extensively pl/sql in the past, it seems to me that most of these points are either moot or only apply to the particular flavour of stored-procedures available in mysql, but not in other rdbms.”

-> Exactly so. The title of this post clearly states “MySQL”, and specifically indicates the use of MySQL stored routines.

– Debugging: please suggest “a plethora of tools and techniques to help you do that” *on MySQL* 🙂

– “In fact stored routines help shield the app from the db schema implementation, as they expose an API which is closer to the business logic than to storage concerns.”

-> agreed. I mention this in the text: “…security (with stored routines you can obfuscate/hide internal datasets, and provide with limited and expected API)… ”

– “When you mention adding a column, you forget to mention that all app code written directly accessing the table must not have a select *, or there are still chances it might break.
And what about changing the semantics/type of a column or table, or dropping it altogether? Could you do an upgrade-without-stop-the-world when NOT using stored procedures? Would direct-table-access help?”

-> Yes, there are ways to refactor a table without-top-the-world when not using stored procedures. Ironically, I am author of the first such tool for MySQL called oak-online-alter-table, later forked by Facebook and re-implemented by Percona, and it uses triggers, which are some form of stored routines in MySQL as you indicated. This solution allows direct table access even while table is being altered.

– “testing: lazy devs do not write tests for their app code, either.”

-> I agree. However, when you throw a developer into an environment that has poor support for testing, as opposed to throwing her into an environment with strong support for testing, where would you expect said developer to be more tests-productive?

– “efficiency. Putting code close to the data gives you order of magnitude improvements over using an ORM in speed of execution and resource consumption”

-> I did in fact mention that; I also stated that this post does not zoom into this.

– multi-app access. Encapsulating data access in an API layer means you can write apps in java, php, c#, without having to reimplement the same data consistency checks and table joins in all of them

-> Absolutely agree. But also to the point of having a single server possibly do the CPU computation that multiple other servers might have done themselves.

– migrating to a different database vendor.

-> Hear here. I have yet to see a large scale database use that is completely abstractized.

I believe you come from the world of Oracle DB. I am well accustomed to training Oracle or SQL Server DBAs migrating to MySQL. The MySQL stored routines are not nearly as strong or fast as those in Oracle & SQL Server. There is a painful switch such DBAs have to make when switching over to MySQL.

Thanks again

a) big drawback of using stored procs for your whole application: yes, the db-based languages suck big time compared to ruby/php/python

b) I am not against IDEs in general. When coding on Oracle, I always asked my employer to shell out for a license of TOAD before I’d sign the contract