MySQL Bugs: #36970: main.max_user_connections

Bug #36970	main.max_user_connections_func.test fails in pushbuild
Submitted:	26 May 2008 14:37	Modified:	14 Oct 2008 18:07
Reporter:	Ingo Strüwing	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Tests: Server	Severity:	S3 (Non-critical)
Version:	6.0	OS:	Any
Assigned to:	Tatiana Azundris Nuernberg	CPU Architecture:	Any
Tags:	pushbuild, sporadic, test failure

Description:
Seen on sol10-sparc-a in 6.0-engines-merge:

main.max_user_connections_func [ fail ]

mysqltest: At line 92: query 'SET GLOBAL max_user_connections = @default_max_user_connections' failed: 2013: Lost connection to MySQL server during query

The result from queries just before the failure was:
** Setup **

SET @default_max_user_connections = @@max_user_connections;
Set Global max_user_connections=2;
'#--------------------FN_DYNVARS_114_01-------------------------#'
** Connecting conn1 using username 'root' **
** Connecting conn2 using username 'root' **
** Connecting conn3 using username 'root' **
ERROR 42000: User root already has more than 'max_user_connections' active connections
Expected error "too many connections"
** Disconnecting conn1 **
'#--------------------FN_DYNVARS_114_02-------------------------#'
Set Global max_user_connections=3;
** Connecting conn5 using username 'root' **
** Connecting conn6 using username 'root' **
** Connection default **
** Disconnecting conn5, conn6 **
SET GLOBAL max_user_connections = @default_max_user_connections;

More results from queries before failure can be found in /tmp/var-ps_stm_threadpool-101/log/max_user_connections_func.log

I will disable the test case. Please re-enable after fix.

How to repeat:
See pushbuild.

there we go, libevent was missing.

Assertion failed: uc->connections, file sql_connect.cc, line 186
Abort (core dumped)

void decrease_user_connections(USER_CONN *uc)
{
  DBUG_ENTER("decrease_user_connections");
  (void) pthread_mutex_lock(&LOCK_user_conn);
  DBUG_ASSERT(uc->connections);
  if (!--uc->connections && !mqh_used)
  {
    /* Last connection for user; Delete it */
    (void) hash_delete(&hash_user_connections,(uchar*) uc);
  }
  (void) pthread_mutex_unlock(&LOCK_user_conn);
  DBUG_VOID_RETURN;
}

=>[6] decrease_user_connections(uc = (nil)), line 186 in "sql_connect.cc"
  [7] end_connection(thd = 0x6c930e0), line 1005 in "sql_connect.cc"
  [8] libevent_connection_close(thd = 0x6c930e0), line 505 in "scheduler.cc"
  [9] libevent_thread_proc(arg = (nil)), line 614 in "scheduler.cc"
  [10] _thr_setup(0x0, 0x0, 0x0, 0x0, 0x0, 0x0), at 0xfffffd7ffef6aa1b 
  [11] _lwp_start(0x0, 0x0, 0x0, 0x0, 0x0, 0x0), at 0xfffffd7ffef6ac50 

(dbx) up
Current function is end_connection
 1005       decrease_user_connections(thd->user_connect);

(dbx) list 1000,1005
 1000   void end_connection(THD *thd)
 1001   {
 1002     NET *net= &thd->net;
 1003     plugin_thdvar_cleanup(thd);
 1004     if (thd->user_connect)
 1005       decrease_user_connections(thd->user_connect);

(dbx) print thd->user_connect 
thd->user_connect = 0x6b6b3d0

(dbx) print *thd->user_connect
*thd->user_connect = {
    user           = 0x6b6b410 ""
    host           = 0x6b6b411 ""
    reset_utime    = 0
    len            = 2U
    connections    = 0
    conn_per_hour  = 2U
    updates        = 0
    questions      = 0
    user_resources = {
        questions        = 0
        updates          = 0
        conn_per_hour    = 0
        user_conn        = 0
        specified_limits = 0
    }
}

Hmmmmmmmm …

analysis:

scheduler.cc:libevent_connection_close() calls sql_connect.cc:end_connection() which decreases our connection counter by calling sql_connect.cc:decrease_user_connections(). If we're closing a connection during login because it exceeded the allowed maximum, we already decreased the counter in sql_connect.cc:check_for_max_user_connections() (called from check_user()). This understandably upsets our counting, and when we later try to drop a connection which in fact does exist, but which our count says shouldn't, we throw.

internal notes:

sql_connect.cc:1005
  if (thd->user_connect && thd->user_connect->connections)
avoids assert, obviously

export PATH=/usr/local/bin:/usr/bin:/bin:/usr/sbin:/sbin:/usr/ccs/bin:/opt/SUNWspro/bin

CC="gcc" CXX="gcc" CFLAGS="-m64 -O2 -g -mtune=k8 -DMY_ATOMIC_MODE_RWLOCKS" CXXFLAGS="-m64 -O2 -g -mtune=k8 -DMY_ATOMIC_MODE_RWLOCKS" LDFLAGS="-m64 -O2 -g -mtune=k8 -static-libgcc" LIBS=-lmtmalloc LIBRARY_PATH=/usr/local/lib/amd64

while ./mtr --mysqld=--thread-handling=pool-of-threads --do-test=max_user_connections_func --mem; do true; done

can reproduce on lin-x64

cf.

pthread_handler_t handle_one_connection(void *arg)
{
  for (;;)
  {
    if (login_connection(thd))
      goto end_thread;

    prepare_new_connection_state(thd);

    while (!net->error && net->vio != 0 &&
           !(thd->killed == THD::KILL_CONNECTION))
    {
      mysql_audit_release(thd);
      if (do_command(thd))
	break;
    }
    end_connection(thd);
   
end_thread:
    close_connection(thd, 0, 1);
  }
}

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

ChangeSet@1.2643, 2008-06-17 03:37:33+02:00, tnurnberg@noir.wlan.koehntopp.de +2 -0
  Bug#36970: max_user_connections_func.test fails in pushbuild
  
  If thread-pooling was used and an attempt to login was
  denied on the grounds of exceeding the user limits, the
  number of active connections for that user was erroneously
  decreased twice. The difference between actual connections
  and our count could then cause debug-builds of the server
  to throw in an ASSERT() sanity check.

I believe this has already been fixed by a patch from Konstantin Osipov:

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

Noted in 6.0.6 changelog.

If thread-pooling was used and a connection attempt was denied on the
grounds of exceeding the user limits, the number of active
connections for that user was erroneously decreased twice. The
difference between the actual number connections and the internal
count could then cause debug builds of the server to raise an
assertion.