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

[12 Jan 2006 17:35] Jose Peña
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.
[18 Jan 2006 18:17] Valeriy Kravchuk
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.
[10 Apr 2006 14:21] Bugs System
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
[12 Apr 2006 15:29] Bugs System
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
[23 Apr 2006 3:51] Konstantin Osipov
Fixed in 5.0.21, 5.1.10
[4 May 2006 17:14] Paul DuBois
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)