MySQL Bugs: #16461: connection_id() does not work properly inside trigger

Bug #16461	connection_id() does not work properly inside trigger
Submitted:	12 Jan 2006 17:35	Modified:	4 May 2006 17:14
Reporter:	Jose Peña	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S3 (Non-critical)
Version:	5.0.19-BK, 5.0.18-max	OS:	Linux (Linux, MAC OS X 10.4)
Assigned to:	Tomash Brechko	CPU Architecture:	Any

Description:
Triggers uses the same thread although you call them a lot of times (triggers has the same connection_id). It seams that when the trigger finish and the connection is closed the thread is not closed. 

How to repeat:
You must create two tables, bugs and users:
  
  CREATE TABLE test.bugs (var1 VARCHAR(255));

  CREATE TABLE test.users (
       UID INT UNSIGNED NOT NULL AUTO_INCREMENT
     , username VARCHAR(255)
     , password VARCHAR(255)
     , UNIQUE UQ_users_username (username)
     , PRIMARY KEY (UID)
  )TYPE=InnoDB;

and one trigger that run when you insert data in 'users' table  ( be aware, 'users' is not mysql.user table)

create trigger checkSession before insert on test.users for each row
begin
   insert into bugs values (connection_id());
end

Now if you try to insert two o more times data into test.users

INSERT INTO test.users VALUES (0,'user',MD5('xxx'));
INSERT INTO test.users VALUES (0,'user',MD5('xxx'));
INSERT INTO test.users VALUES (0,'user',MD5('xxx'));

The bugs table show always the same connection_id.

This happens always, although you close de local connection with mysql.

Now if you do  mysql>quit;  and you connect to mysql another time your connection_id is diferent but when you try to insert data into test.users

INSERT INTO test.users VALUES (0,'user',MD5('xxx'));

You can see that the connection_id stored is the always is the same, this seems to be because triggers has their own thread that is not closed.

Suggested fix:
The trigger thread sould be closed after trigger ends or the connection is closed
This is a big problem when somebody wants to use the connection_id for security issues.

Thank you for a problem report. Verified just as described on 5.0.19-BK (ChangeSet@1.2025.6.1, 2006-01-17 21:10:47+03:00) on Linux:

mysql> CREATE TABLE test.bugs (var1 VARCHAR(255));
Query OK, 0 rows affected (0.16 sec)

mysql>   CREATE TABLE test.users (
    ->        UID INT UNSIGNED NOT NULL AUTO_INCREMENT
    ->      , username VARCHAR(255)
    ->      , password VARCHAR(255)
    ->      , UNIQUE UQ_users_username (username)
    ->      , PRIMARY KEY (UID)
    ->   )TYPE=InnoDB;
Query OK, 0 rows affected, 1 warning (0.25 sec)

mysql> delimiter //
mysql> create trigger checkSession before insert on test.users for each row
    -> begin
    ->    insert into bugs values (connection_id());
    -> end//
Query OK, 0 rows affected (0.02 sec)

mysql> delimiter ;
mysql> INSERT INTO test.users VALUES (0,'user',MD5('xxx'));
INSERT INTO test.users VALUES (0,'user',MD5('xxx'));
Query OK, 1 row affected (0.11 sec)

mysql> INSERT INTO test.users VALUES (0,'user',MD5('xxx'));
ERROR 1062 (23000): Duplicate entry 'user' for key 2
mysql> INSERT INTO test.users VALUES (0,'user',MD5('xxx'));
ERROR 1062 (23000): Duplicate entry 'user' for key 2

mysql> INSERT INTO test.users VALUES (0,'user2',MD5('xxx'));
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO test.users VALUES (0,'user3',MD5('xxx'));
Query OK, 1 row affected (0.00 sec)

mysql> select * from bugs;
+------+
| var1 |
+------+
| 2    |
| 2    |
| 2    |
| 2    |
| 2    |
+------+
5 rows in set (0.01 sec)

mysql> exit
Bye
[openxs@Fedora 5.0]$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.0.19-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> INSERT INTO test.users VALUES (0,'user4',MD5('xxx'));
Query OK, 1 row affected (0.00 sec)

mysql> select * from bugs;
+------+
| var1 |
+------+
| 2    |
| 2    |
| 2    |
| 2    |
| 2    |
| 2    |
+------+
6 rows in set (0.00 sec)

mysql> show processlist;
+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host      | db   | Command | Time | State | Info             |
+----+------+-----------+------+---------+------+-------+------------------+
|  3 | root | localhost | test | Query   |    0 | NULL  | show processlist |
+----+------+-----------+------+---------+------+-------+------------------+
1 row in set (0.02 sec)

mysql> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
|               3 |
+-----------------+
1 row in set (0.00 sec)

This problem of connection_id() usage in triggers is not described neither in
http://dev.mysql.com/doc/refman/5.0/en/information-functions.html nor in http://dev.mysql.com/doc/refman/5.0/en/using-triggers.html. So, it is either a bug or a documentation request.

I changed synopsis to a more precise one.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/4717

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/4870

Fixed in 5.0.21, 5.1.10

Noted in 5.0.21, 5.1.10 changelogs.

Within a trigger, <literal>CONNECTION_ID()</literal> did not
return the connection ID of the thread that caused the trigger
to be activated. (Bug #16461)