Bug #20023 mysql_change_user() resets the value of SQL_BIG_SELECTS
Submitted: 23 May 2006 16:04 Modified: 23 Oct 2007 19:32
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.1 OS:Any (*)
Assigned to: Alexander Nozdrin CPU Architecture:Any
Tags: mysql_change_user

[23 May 2006 16:04] Shane Bester
Description:
According to the documentation, after calling mysql_change_user(), the connection should resemble a newly created connection.  However, it's not the case.

SELECT @@SQL_BIG_SELECTS; is returning different value.  This affects applications and even Connector/NET when used with pooling.

Some queries fail due to max_join_size errors.  

http://dev.mysql.com/doc/refman/5.0/en/mysql-change-user.html
"and resets the state as if one had done a new connect."

How to repeat:
attached is PHP and C testcase.  I reproduced in C# testcase too.

[sbester@fc4 ~]$ ./bug
Reading SQL_BIG_RESULTS without calling mysql_change_user first:

Row 0
Field Name: @@SQL_BIG_SELECTS
Field Value: 1
Field Type: 8

Reading SQL_BIG_RESULTS after calling mysql_change_user first:

Row 0
Field Name: @@SQL_BIG_SELECTS
Field Value: 0
Field Type: 8

Suggested fix:
Don't reset the SQL_BIG_SELECTS to 0.  Keep it set to 1, as it's set in a new connection.

Current workaround is to run SET SQL_BIG_SELECTS=1 when a new connection is established.
[23 May 2006 16:07] Shane Bester
mysqli php testcase.

Attachment: changeuser.php (application/octet-stream, text), 671 bytes.

[23 May 2006 16:07] Shane Bester
C testcase

Attachment: csc9641.c (text/x-csrc), 1.82 KiB.

[1 Sep 2006 10:57] Shane Bester
marked bug #11427 as a duplicate of this
[1 Sep 2006 11:01] Shane Bester
marked bug #21962 as a duplicate of this
[1 Sep 2006 11:11] Shane Bester
marked bug #20017 as a duplicate of this
[1 Sep 2006 11:24] Shane Bester
see bug #21960 also
[28 Sep 2006 17:16] Chad MILLER
Surely there are more variables and states that should be affected, also.
[14 Nov 2006 23:26] Daniel Wille
I'd recommend the severity of the bug be upgraded. The bug requires finding and modifying any queries so that you either SET the appropriate variable before a broken query, or you have to disable connection pooling which can have drastic effects on application performance from what I've seen.
[3 Oct 2007 13:25] Konstantin Osipov
Affects customer, upgrading.
[10 Oct 2007 13:56] 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/35289

ChangeSet@1.2582, 2007-10-10 17:57:01+04:00, anozdrin@station. +4 -0
  Fix for BUG#20023: mysql_change_user() resets the value
  of SQL_BIG_SELECTS.
  
  The bug was that SQL_BIG_SELECTS was not properly set
  in COM_CHANGE_USER.
  
  The fix is to update SQL_BIG_SELECTS properly.
[10 Oct 2007 13:59] Alexander Nozdrin
Pushed into 5.1-runtime.
[19 Oct 2007 18:52] Bugs System
Pushed into 5.1.23-beta
[23 Oct 2007 19:32] Paul Dubois
Noted in 5.1.23 changelog.
[8 Nov 2007 13:41] Alexander Nozdrin
Murtaza,

we're sorry, but the bug system is not the appropriate forum for asking help on using MySQL products.

Support on using our products is available both free in our forums at http://forums.mysql.com/ and for a reasonable fee direct from our skilled support engineers at http://www.mysql.com/support/

Thank you for your interest in MySQL.
[8 May 2009 12:03] imacat .
Dear MySQL staff,

    Hi.  This is imacat from Taiwan.  I found this problem still exists when running "make test" on my i686.  My environ is: Debian GNU/Linux Lenny 5.0.1, Kernel 2.6.26-2-686, GCC 4.3.2, GLIBC 2.7.  It passed the tests on the x86_64, though.

    The log is attached below.  Please tell me if you need any more information.  Thank you.

imacat@gaya mysql-5.0.81/mysql-test % ./mysql-test-run.pl mysql_client_test
Logging: ./mysql-test-run.pl mysql_client_test
090508 19:32:22 [Warning] option 'max_join_size': unsigned value 18446744073709551615 adjusted to 4294967295
090508 19:32:22 [Warning] option 'max_join_size': unsigned value 18446744073709551615 adjusted to 4294967295
MySQL Version 5.0.81
Skipping ndbcluster, mysqld not compiled with ndbcluster
Skipping SSL, mysqld not compiled with SSL
Using MTR_BUILD_THREAD      = 0
Using MASTER_MYPORT         = 9306
Using MASTER_MYPORT1        = 9307
Using SLAVE_MYPORT          = 9308
Using SLAVE_MYPORT1         = 9309
Using SLAVE_MYPORT2         = 9310
Using IM_PORT               = 9313
Using IM_MYSQLD1_PORT       = 9314
Using IM_MYSQLD2_PORT       = 9315
Killing Possible Leftover Processes
Removing Stale Files
Creating Directories
Installing Master Database
=======================================================
Starting Tests in the 'main' suite

TEST                           RESULT         TIME (ms)
-------------------------------------------------------

mysql_client_test              [ fail ]

mysqltest: At line 12: command "$MYSQL_CLIENT_TEST --getopt-ll-test=25600M >> $MYSQLTEST_VARDIR/log/mysql_client_test.out.log 2>&1" failed

The result from queries just before the failure was:
exec of '/usr/local/src/mysql-5.0.81/tests/mysql_client_test --no-defaults --testcase --user=root --port=9306 --socket=/usr/local/src/mysql-5.0.81/mysql-test/var/tmp/master.sock --vardir=/usr/local/src/mysql-5.0.81/mysql-test/var --getopt-ll-test=25600M >> /usr/local/src/mysql-5.0.81/mysql-test/var/log/mysql_client_test.out.log 2>&1' failed, error: 256, status: 1, errno: 0

More results from queries before failure can be found in /usr/local/src/mysql-5.0.81/mysql-test/var/log/mysql_client_test.log

Aborting: mysql_client_test failed in default mode.
To continue, re-run with '--force'.
Stopping All Servers
imacat@gaya mysql-5.0.81/mysql-test % tail var/log/mysql_client_test.out.log

        +------+
        | a    |
        +------+
        |    1 |
        +------+

        1 row returned

 total parameters in stmt: `1` (expected: `1`)mysql_client_test.c:16442: check failed: 'sql_big_selects_5 == sql_big_selects_orig'
imacat@gaya mysql-5.0.81/mysql-test %
[8 May 2009 12:20] Shane Bester
Hi imaca,

this was only fixed in >= 5.1.  So 5.0 still contains the bug.
[8 May 2009 16:47] imacat .
Dear Shane Bester,

    I see.  Then, can I safely ignore this error and run MySQL 5.0 on my production server?
[25 Jul 2009 23:10] Davi Arnaut
For future reference: the fix was backported to 5.0. See Bug#40363.
[27 Jul 2009 15:31] 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/79347

2790 Davi Arnaut	2009-07-27
      Bug#20023: mysql_change_user() resets the value of SQL_BIG_SELECTS
      
      Post-merge fix: test case could fail due to a conversion of the
      max_join_size value to a integer. Fixed by preserving the value
      as a string for comparison purposes.
     @ tests/mysql_client_test.c
        Preserve max_join_size value as a string instead of converting
        it to a integer -- value can be larger then the type used.
[27 Jul 2009 15:38] Alexander Nozdrin
The patch is approved.
[4 Aug 2009 13:56] Bugs System
Pushed into 5.0.85 (revid:davi.arnaut@sun.com-20090804135315-6lfdnk4zjwk7kn7r) (version source revid:davi.arnaut@sun.com-20090804135315-6lfdnk4zjwk7kn7r) (merge vers: 5.0.85) (pib:11)
[4 Aug 2009 19:52] Bugs System
Pushed into 5.4.4-alpha (revid:alik@sun.com-20090804194615-h40sa098mx4z49qg) (version source revid:davi.arnaut@sun.com-20090727191546-d0glf93zrlefm0mq) (merge vers: 5.4.4-alpha) (pib:11)
[4 Aug 2009 20:45] Bugs System
Pushed into 5.1.38 (revid:davi.arnaut@sun.com-20090804204317-ggodqkik7de6nfpz) (version source revid:davi.arnaut@sun.com-20090804204317-ggodqkik7de6nfpz) (merge vers: 5.1.38) (pib:11)
[1 Oct 2009 5:59] Bugs System
Pushed into 5.1.39-ndb-6.3.28 (revid:jonas@mysql.com-20091001055605-ap2kiaarr7p40mmv) (version source revid:jonas@mysql.com-20091001055605-ap2kiaarr7p40mmv) (merge vers: 5.1.39-ndb-6.3.28) (pib:11)
[1 Oct 2009 7:25] Bugs System
Pushed into 5.1.39-ndb-7.0.9 (revid:jonas@mysql.com-20091001072547-kv17uu06hfjhgjay) (version source revid:jonas@mysql.com-20091001071652-irejtnumzbpsbgk2) (merge vers: 5.1.39-ndb-7.0.9) (pib:11)
[1 Oct 2009 13:25] Bugs System
Pushed into 5.1.39-ndb-7.1.0 (revid:jonas@mysql.com-20091001123013-g9ob2tsyctpw6zs0) (version source revid:jonas@mysql.com-20091001123013-g9ob2tsyctpw6zs0) (merge vers: 5.1.39-ndb-7.1.0) (pib:11)
[5 Oct 2009 10:50] Bugs System
Pushed into 5.1.39-ndb-6.2.19 (revid:jonas@mysql.com-20091005103850-dwij2dojwpvf5hi6) (version source revid:jonas@mysql.com-20090930185117-bhud4ek1y0hsj1nv) (merge vers: 5.1.39-ndb-6.2.19) (pib:11)