Things that can’t (and some that can) be done from within a MySQL stored routine

I’m doing a lot of stored routine programming lately, working on common_schema. I’m in particular touching at the extremes of abilities. Some things just can’t be done from within a stored routine. Here’s a list of can’t be done:

• Cursor for SHOW statements: can’t be done — this is explicitly blocked from operating (it once used to work).
• Get detailed error information on exceptions: apparently 5.6 has support for this. 5.1 and 5.5 do not.
• Change binlog_format: this is obvious, if you think about it. binlog_format dictates how the routine itself is replicated in the first place.
• Set sql_log_bin. Again, this makes sense.
• Work out different results depending on current machine. For example, you can’t have a routine that returns with ‘master’ on the master and with ‘slave’ on the slave. That is, not under any condition. Consider: if Row Based Replication is used, you don’t actually have a routine executing on the slave. I’m happy to be proven wrong on this.
• Know what database was in use by calling code. The routine executes within the context of the database where it is defined. But you can’t tell what database was in use just a couple milliseconds before.
• Likewise, know what sql_mode was in use by calling code. Stored routines have their own sql_mode – the one they were created with. No way to check up on the calling stack.
• And you can’t USE another database (database as in schema). USE is a client command.
• Reconnect after failure (kind of obvious, isn’t it?)
• Connect to other servers (not so obvious to SQL Server DBAs). You can’t issue queries on other servers. Bummer.
• Shutdown the server
• Fork (you’re in a connection, you can’t issue a new connection from your own connection)

Well, some of the above can be solved using plugins or User Defined Functions, but I’m looking at standard servers.

Things that can be done

I can’t list anything that can be done from within a routine, but, to balance, here’s a brief list of things that can be done:

• Recover from errors (e.g. deadlocks) via DECLARE CONTINUE HANDLER.
• Perform table operations (ANALYZE, OPTIMIZE, …) – though not read the results of these operations other than knowing they succeeded.
• Perform all DDL statements (create/drop/modify views, routines, triggers, events, tables, users)
• Modify session/global variables (group_concat_max_len, innodb_stats_on_metadata, …)

You should be aware of

• max_sp_recursion_depth: the maximum recursion depth, if you’re thinking of recursions.
• thread_stack: I find that setting to 256K makes a huge difference over the 5.1 default of 192K. With 192K, I frequently run into “out of stack space” problems. With 256K – I have yet to encounter that. Dunno, some kind of magic number? This is my observation.