MySQL Bugs: #20023: mysql_change_user() resets the value of SQL_BIG

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

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.

mysqli php testcase.

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

C testcase

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

marked bug #11427 as a duplicate of this

marked bug #21962 as a duplicate of this

marked bug #20017 as a duplicate of this

see bug #21960 also

Surely there are more variables and states that should be affected, also.

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.

Affects customer, upgrading.

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.

Pushed into 5.1-runtime.

Pushed into 5.1.23-beta

Noted in 5.1.23 changelog.

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.

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 %

Hi imaca,

this was only fixed in >= 5.1.  So 5.0 still contains the bug.

Dear Shane Bester,

    I see.  Then, can I safely ignore this error and run MySQL 5.0 on my production server?

For future reference: the fix was backported to 5.0. See Bug#40363.

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.

The patch is approved.

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)

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)

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)

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)

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)

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)

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)