MySQL Bugs: #177: CONNECTION_ID() is badly replicated

Bug #177	CONNECTION_ID() is badly replicated
Submitted:	21 Mar 2003 13:09	Modified:	13 May 2003 14:17
Reporter:	Guilhem Bichot	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	4.0	OS:	Any (all)
Assigned to:	Guilhem Bichot	CPU Architecture:	Any

Description:
When replicating an insert that uses connection_id(), the slave stupidly uses
the connection id of the slave SQL thread.

How to repeat:
MASTER> create table t2(a int, b int);

MASTER> insert into t2 values(connection_id(),99);

MASTER> select * from t2;
+------+------+
| a    | b    |
+------+------+
|    2 |   99 |
+------+------+
1 row in set (0.03 sec)

SLAVE> select * from t2;
+------+------+
| a    | b    |
+------+------+
|    3 |   99 |
+------+------+
1 row in set (0.00 sec)

#3 is the connection_id of the slave SQL thread

Suggested fix:
Easy; in the relay log we know the connection id that was used on the master :
#030321 21:48:14 server id 1  log_pos 131       Query   thread_id=2    ...
SET TIMESTAMP=1048279694;
insert into t2 values(connection_id(),99);

Can be fixed in 3 ways :
- when we have some sort of row-level logging
- if we create a new type of binlog event to handle
all these strange functions (CONNECTION_ID(), USER(),
even the UDF functions when they are not repeatable)
- simply use the thread id which is contained in the event
(works, but does not solve other functions' problem)
- do substitution before writing to the binlog

Not urgent.

Documented in the MySQL manual

Thank you for your bug report. This issue has been fixed in the latest
development tree for that product. You can find more information about
accessing our development trees at 
    http://www.mysql.com/doc/en/Installing_source_tree.html

Fixed in 4.1.1 (cset 1.1531).