{"id":5163,"date":"2012-08-02T06:32:57","date_gmt":"2012-08-02T04:32:57","guid":{"rendered":"http:\/\/code.openark.org\/blog\/?p=5163"},"modified":"2012-08-02T08:26:41","modified_gmt":"2012-08-02T06:26:41","slug":"things-that-cant-and-some-that-can-be-done-from-within-a-mysql-stored-routine","status":"publish","type":"post","link":"https:\/\/code.openark.org\/blog\/mysql\/things-that-cant-and-some-that-can-be-done-from-within-a-mysql-stored-routine","title":{"rendered":"Things that can&#8217;t (and some that can) be done from within a MySQL stored routine"},"content":{"rendered":"<p>I&#8217;m doing a lot of stored routine programming lately, working on <a href=\"http:\/\/code.google.com\/p\/common-schema\/\">common_schema<\/a>. I&#8217;m in particular touching at the extremes of abilities. Some things just can&#8217;t be done from within a stored routine. Here&#8217;s a list of <strong>can&#8217;t be done<\/strong>:<\/p>\n<ul>\n<li>Cursor for <strong>SHOW<\/strong> statements: can&#8217;t be done &#8212; this is explicitly blocked from operating (it once used to work).<\/li>\n<li>Get detailed error information on exceptions: apparently <strong>5.6<\/strong> has support for this. <strong>5.1<\/strong> and <strong>5.5<\/strong> <a href=\"http:\/\/code.openark.org\/blog\/mysql\/mysql-error-handling-on-server-side-a-no-go\">do not<\/a>.<\/li>\n<li>Change <strong>binlog_format<\/strong>: this is obvious, if you think about it. <strong>binlog_format<\/strong> dictates how the routine itself is replicated in the first place.<\/li>\n<li>Set <strong>sql_log_bin<\/strong>. Again, this makes sense.<\/li>\n<li>Work out different results depending on current machine. For example, you can&#8217;t have a routine that returns with <strong>&#8216;master&#8217;<\/strong> on the master and with <strong>&#8216;slave&#8217;<\/strong> on the slave. That is, not under <em>any condition<\/em>. Consider: if <em>Row Based Replication<\/em> is used, you don&#8217;t actually have a routine <em>executing<\/em> on the slave. I&#8217;m happy to be proven wrong on this.<\/li>\n<li>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&#8217;t tell what database was in use just a couple milliseconds before.<\/li>\n<li>Likewise, know what <strong>sql_mode<\/strong> was in use by calling code. Stored routines have their own <strong>sql_mode<\/strong> &#8211; the one they were created with. No way to check up on the calling stack.<\/li>\n<li>And you can&#8217;t <strong>USE<\/strong> another database (database as in <em>schema<\/em>). <strong>USE<\/strong> is a client command.<\/li>\n<li>Reconnect after failure (kind of obvious, isn&#8217;t it?)<\/li>\n<li>Connect to other servers (not so obvious to SQL Server DBAs). You can&#8217;t issue queries on other servers. Bummer.<\/li>\n<li>Shutdown the server<\/li>\n<li>Fork (you&#8217;re in a connection, you can&#8217;t issue a new connection from your own connection)<\/li>\n<\/ul>\n<p><!--more-->Well, some of the above can be solved using plugins or User Defined Functions, but I&#8217;m looking at standard servers.<\/p>\n<h4>Things that can be done<\/h4>\n<p>I can&#8217;t list anything that can be done from within a routine, but, to balance, here&#8217;s a brief list of things that <strong>can be done<\/strong>:<\/p>\n<ul>\n<li>Recover from errors (e.g. deadlocks) via <strong>DECLARE CONTINUE HANDLER<\/strong>.<\/li>\n<li>Perform table operations (<strong>ANALYZE<\/strong>, <strong>OPTIMIZE<\/strong>, &#8230;) &#8211; though not read the results of these operations other than knowing they succeeded.<\/li>\n<li>Perform all DDL statements (create\/drop\/modify views, routines, triggers, events, tables, users)<\/li>\n<li>Modify session\/global variables (<strong>group_concat_max_len<\/strong>, <strong>innodb_stats_on_metadata<\/strong>, &#8230;<code>)<\/code><\/li>\n<\/ul>\n<h4>You should be aware of<\/h4>\n<ul>\n<li><strong>max_sp_recursion_depth<\/strong>: the maximum recursion depth, if you&#8217;re thinking of recursions.<\/li>\n<li><strong>thread_stack<\/strong>: I find that setting to <strong>256K<\/strong> makes a huge difference over the <strong>5.1<\/strong> default of <strong>192K<\/strong>. With <strong>192K<\/strong>, I frequently run into &#8220;out of stack space&#8221; problems. With <strong>256K<\/strong> &#8211; I have yet to encounter that. Dunno, some kind of magic number? This is my observation.<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>I&#8217;m doing a lot of stored routine programming lately, working on common_schema. I&#8217;m in particular touching at the extremes of abilities. Some things just can&#8217;t be done from within a stored routine. Here&#8217;s a list of can&#8217;t be done: Cursor for SHOW statements: can&#8217;t be done &#8212; this is explicitly blocked from operating (it once [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"enabled":false},"version":2}},"categories":[5],"tags":[59],"class_list":["post-5163","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-stored-routines"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2bZZp-1lh","_links":{"self":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/5163","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/comments?post=5163"}],"version-history":[{"count":28,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/5163\/revisions"}],"predecessor-version":[{"id":5217,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/5163\/revisions\/5217"}],"wp:attachment":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/media?parent=5163"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/categories?post=5163"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/tags?post=5163"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}