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.
“Connect to other servers” – While indeed you can’t connect to other servers as in other RDBMSs, you still have the FEDERATED engine which can be used to connect to a predefined tables from other servers or even databases within the same server (so this one also goes to the “And you can’t USE another database.” comment)
@Uri,
Yes, FEDERATED engine can be used to query particular tables from other servers. This is not strictly what I meant, tough, as you are limited to those particular tables. But you are right on this.
I should note FEDERATED is nowadays disabled by default (also does not even compile by default) and FEDERATEDX is long anticipated.
With regard to “USE” — this has nothing to do with connecting to servers. The name “database” is misleading here. “USE” means “change working database (== schema)”, not database server.
While you can execute DDL in a routine, the DDL you can execute via dynamic sql (prepare/execute/deallocate) is limited. You can’t create triggers via dynamic SQL (at least last time I tried) for example.
You also can’t use LOAD DATA INFILE in a routine.
@Justin,
You are right on both accounts.
You can use SQL_LOG_BIN in a stored procedure (in fact, I did this in example recently here http://www.markleith.co.uk/2012/07/24/a-mysql-replication-load-average-with-performance-schema/) – you just need to make sure that the procedure is run as a SUPER user..
@Mark,
Hrrrmmmm… something’s very fishy here. What is the meaning of setting this variable if you’re in RBR? I need to test this again — I have a previous experience with this, but must refresh my memory.
The mode doesn’t matter, all it means is “nothing shall be written to the binary log after setting this to 0”.
I’m late to the game, but….I’m surprised you can’t do something with the @@global.hostname variable, set an internal variable (say, @my_hostname) to it and do an if statement that gives different results on the master and slave……
@Sheeri,
Yes, indeed. Same for “report_host” etc. What I meant, tough, is a generic solution, which would not assume your master’s hostname is “foo” and the slave’s is “bar”. Something that would just say “hey, this is the master because I’m checking up on internal stuff, and right now this is the slave”.