MySQL Bugs: #42384: thread_handling = pool-of-threads, wrong handling of max

Bug #42384	thread_handling = pool-of-threads, wrong handling of max_questions user limit
Submitted:	27 Jan 18:48	Modified:	3 Apr 4:49
Reporter:	Matthias Leich
Status:	Closed
Category:	Server	Severity:	S2 (Serious)
Version:	6.0	OS:	Any
Assigned to:	Alexander Nozdrin	Target Version:	6.0-rc
Triage:	Triaged: D3 (Medium)

Description:
The current problem is the cause of the first bad
effect reported in
   Bug#41147 main.user_limits test fails with user
       exceeding the 'max_questions' resource
It happens only if the server is started with
thread_handling = pool-of-threads (6.0 only).
The problem is systematic (<> random>)
--------------------------------------------------
Snip from the protocol:
GRANT USAGE ON *.* TO testuser17@localhost
WITH MAX_QUERIES_PER_HOUR 3;
FLUSH USER_RESOURCES;
# Establish connection testuser17 (user=testuser17)
connect(localhost,testuser17,,test,MYSQL_PORT,MYSQL_SOCK);
# Switch to connection testuser17
SELECT 'First', USER();
First	USER()
First	testuser17@localhost
SELECT 'Second', USER();
Second	USER()
Second	testuser17@localhost
SELECT 'Third', USER();
ERROR 42000: User 'testuser17' has exceeded the 'max_questions' resource (current value:
3)
     <========= This must not happen.
     <========= It's the third and not the fourth query.
# ---- Expect here 'max_questions' limit exceeded -----------
SELECT 'Forth', USER();
ERROR 42000: User 'testuser17' has exceeded the 'max_questions' resource (current value:
3)

Observations during experiments:
--------------------------------
Note: User A and B have a max_questions limit
      First session ~ First session of this
                      user after restart of
                      the server
1. max_questions limit   error on select number
     2                      1 
     3                      2
   if we get the current bug at all.
   --> Assigned and expected limitation: n
       In case of bug limitation got: n - 1
2. Assume
   - there was already a session of user A
     which executed something similar to our subtest
     (One select seems to be sufficient)
   - the root user executed a FLUSH TABLES
     after that
   Any new session of user A will get correct
   results when running the subtest.
   Any new session of a user B will which runs
   our subtest gets our current bug if this is
   the first session of this user.
3. Start first session of user A
   USE <database>
   disconnect user A
   connect user A again
   run subtest
   --> get bug
4. Start first session of user A
   as user root: FLUSH USER_RESOURCES 
   as user A: run subtest
   --> get bug
5. Start first session of user A
   SELECT <something>
   as user root: FLUSH USER_RESOURCES 
   as user A: run subtest
   --> get no bug

Hypothesis for "pool-of-threads":
   When the first session of user A gets established
   than some memory structures for account management
   have to be created and initialized for this user.
   I guess
   - the initialization is somehow wrong
   - any direct successing FLUSH USER_RESOURCES executed
     by the root user does not change this wrong
     initialization  

My environment:
- MySQL compiled from source
  mysql-6.0-bugteam  last changeset Jan 2009
  BUILD/compile-pentium64-debug-max
- Intel Core2Duo (64 Bit)
- OpenSuSE 11.0 (64 Bit)

My patch for
   Bug#41147 main.user_limits test fails with user
             exceeding the 'max_questions' resource
will contain a workaround (skip the execution of
user_limits test in case of "pool-of-threads").
Please modify user_limits test depending on the decisions
for the current bug.

How to repeat:
Please use the attached testcase.
./mysql-test-run.pl --mysqld=--thread-handling=pool-of-threads ml033

testscript

Attachment: ml033.test (application/octet-stream, text), 5.51 KiB.

expected results

Attachment: ml033.result (application/octet-stream, text), 4.84 KiB.

Dear bug fixer,

the first ~ 20 lines of my bug replay testscript
are the important ones. The remaining stuff are
just experiments.

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/70484

2752 Alexander Nozdrin	2009-03-26
      A patch for Bug#42384 (thread_handling = pool-of-threads,
      wrong handling of max_questions user limit).
      
      The problem was that in case of pool-of-threads a thd object
      was not properly initialized, i.e. thr_create_time and start_utime
      were not set. So, time_out_user_resource_limits(), that is
      essential for handling user limits, was not able to work properly.
      
      The fix is to initialize forgotten attributes properly.

Pushed into 6.0-runtime.

Pushed into 6.0.11-alpha (revid:davi.arnaut@sun.com-20090331130058-yonsevjt1833wt5o)
(version source revid:alik@sun.com-20090326090425-cein0jhoqmd54ql9) (merge vers:
6.0.11-alpha) (pib:6)

Noted in 6.0.11 changelog.

If the server was started with --thread_handling=pool-of-threads, the
MAX_QUERIES_PER_HOUR user resource limit.