BackgroundNormally, I would use a comment. The first thing I would think is
CREATE PROCEDURE p1(i int) select "hello" /* This is my text */
But most client libraries will strip it.
There was a clever trick by Roland Bouman that allowed users to bypass this limitation. You could use a qualified comment such as
/*!999999 This is my test */, but unfortunately it only works in MySQL 5.0.x, while MySQL 5.1 strips everything down, even if the comment is a legitimate keyword.
create procedure p9 (j int) insert /*!50000 INTO */ t1 values (j) ; Query OK, 0 rows affected (0.00 sec) show create procedure p9\G *************************** 1. row *************************** Procedure: p9 sql_mode: Create Procedure: CREATE DEFINER=`msandbox`@`%` PROCEDURE `p9`(j int) insert INTO t1 values (j) character_set_client: latin1 collation_connection: latin1_swedish_ci Database Collation: latin1_swedish_ci 1 row in set (0.00 sec)
Other tricksSomething else that I have tried: I can inject a query before or after the one that I need to monitor.
create table if not exists comments (t varchar(100)) engine=blackhole; update comments set t='the next statement is what I need'; create procedure p1(i int) insert into t1 values (j); update comments set t='the previous statement is what I need';This approach does possibly introduce some overhead.
Or I can add a comment clause in the statement.
create procedure p1(i int) comment="this is what I need" insert into t1 values (j);This approach requires parsing the SQL, and dealing with artistic indentation and usage of other options in the query. And if I need to deal with commands that don't support the "comment" option, I am back to square one.
Advice wantedSo far, the only method that works almost always is the blackhole trick (1)
I would like to know if there is any method of introducing a piece of information related to a given statement, in such a way that the comment survives after one of the following:
- The binary log is converted to queries and passed to a MySQL client that applies the stream of queries to another server.
- The binary log is associated with another master, and then passed to a slave through regular replication.
As a related matter, I know that MySQL, in regular replication, passes some information across binary logs, and that information is the server-id. If I set an intermediate server as relay slave, the server-id of the original master is associated with the query recorder in the binary log of every slave. I don't know if I can use this information for my purposes, but I would like to know how does the replication process maintain the server ID across servers.
Maybe it's too late for me and I can't see an obvious solution. I will appreciate any suggestion. Thanks in advance
(1) If the blackhole is disabled, the method fails, or introduce unacceptable overhead.