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

[21 Mar 2003 13:09] Guilhem Bichot
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);
[22 Apr 2003 4:39] Guilhem Bichot
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.
[26 Apr 2003 7:05] Michael Widenius
Documented in the MySQL manual
[13 May 2003 14:17] Guilhem Bichot
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).