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

By: shlomi

shlomi — Thu, 09 Aug 2012 04:58:05 +0000

@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”.

By: Sheeri

Sheeri — Wed, 08 Aug 2012 18:31:34 +0000

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……

By: Mark Leith

Mark Leith — Thu, 02 Aug 2012 08:48:46 +0000

The mode doesn’t matter, all it means is “nothing shall be written to the binary log after setting this to 0”.

By: shlomi

shlomi — Thu, 02 Aug 2012 08:32:10 +0000

@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.

By: Mark Leith

Mark Leith — Thu, 02 Aug 2012 07:45:49 +0000

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..

By: shlomi

shlomi — Thu, 02 Aug 2012 07:30:22 +0000

@Justin,
You are right on both accounts.

By: Justin Swanhart

Justin Swanhart — Thu, 02 Aug 2012 07:07:20 +0000

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.

By: shlomi

shlomi — Thu, 02 Aug 2012 06:25:56 +0000

@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.

By: Uri

Uri — Thu, 02 Aug 2012 05:12:31 +0000

“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)