Bug #41743 Functions to get information about the original thread
Submitted: 25 Dec 2008 11:39 Modified: 4 Oct 2010 19:04
Reporter: Serdar S. Kacar Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: General Severity:S4 (Feature request)
Version:5.1, 6.X OS:Any
Assigned to: CPU Architecture:Any
Tags: CONNECTION_ID, current_user
Triage: Needs Triage: D5 (Feature request)

[25 Dec 2008 11:39] Serdar S. Kacar
Description:
Some of the original thead information (user, thread_id) is not accessible when thread changes identity.

They should be already kept in well defined/established structures. Please expose them by functions and/or session variables :

ORIGINAL_USER()
CURRENT_USER() value changes in a thread  
- when SQL SECURITY DEFINER is used in stored procedures, triggers and views,
- if it is an event,
- if it is Slave SQL.

ORIGINAL_CONNECTION_ID()
Most of the time, it is the same as CONNECTION_ID().
But Slave SQL thread change(s) it ? (Confirmation required)

ORIGINAL_CONNECTION_TYPE()
"User"  : Standard user connection
"Generic System" : E.g. for the process that executes "init_file" sql
"Event" : Ones spawned by event scheduler
"SlaveSQL": Slave SQL thread

It should be easy to implement since all of the above functions are primitive : read thread variables.
For the same reason, there should be no code stability risk, since it is almost impossible to introduce bugs while implementing those kind of primitive functions.
Malicious use risk is none as fas as I can think of.

Benefits are :
- being able to keep log about the callers where identity could have been changed temporarily (read stored rouines)
- design safe&understandable custom execution paths on master / slave (it is still possible but requires tricks/"hard to read" and assumptions/"could be broken in any future release"
- .. add your own ones ..

How to repeat:
N/A
[5 Oct 2010 15:44] Lig Isler-Turmelle
One testcase where being able to find out a base thread_id would be just to be able to differentiate if you are in the SQL Thread or not on a slave.  

When auditing you could then have separate logic branches within a stored procedure or trigger for if it is run within the SQL thread and "normal" connections.

So a IS_SQLTHREAD() function or a SERVER_THREAD_ID to always give the servers thread_id value - the CONNECTION_ID() on the slave in the SQL Thread gives the thread_id from the master...