MySQL Bugs: #209: SET OPTION SQL_SELECT_LIMIT not being honored on Solaris

Bug #209	SET OPTION SQL_SELECT_LIMIT not being honored on Solaris
Submitted:	28 Mar 2003 16:11	Modified:	30 Mar 2003 13:12
Reporter:	Mark Matthews	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	All 4.0	OS:	Solaris (Solaris)
Assigned to:	Oleksandr Byelkin	CPU Architecture:	Any

Description:
G'Day! We have a problem where the application talking to MySQL through
Connector/J asks for an increasing large result set, starting at 50 and
growing to 250, where there are 219 records in the table that meet the WHERE
criteria.

On every occasion we get back 50 results. Clearly we need to change the
setting for how many records to return on each occasion, but we do, and
apparently to no effect. In order to diagnose this we used an instance of
MySQL with logging turned on, so we have the logs of exactly what was sent
to the database. I can take that log and enter the relevant lines one at a
time into mysql directly and get the same results, on each occasion I get
back 50 records.

I'm running 4.0.12 on Solaris 8, however I tried it again with Solaris 9 and
with 4.0.11a and got the same thing.
------------------
I (mark) have the database dump required to reproduce it. I was unable to reproduce the problem on Intel (Linux or Windows), but tfr reproduced it on Solaris.

How to repeat:

OK, It turns out I also turned on (ie created an entry in my.cnf for Query Cache memory) the Query Cache when we did the Friday Maintenance Window and the 4.0.12 upgrade.

I believe now that the Query Cache and do not work together. For example in our case we first SET OPTION SQL_SELECT_LIMIT to 50, do our query, set SQL_SELECT_LIMIT back to DEFAULT. Our application decides it didn't get enough results so it doesit again this time using 100 as the value for SQL_SELECT_LIMIT.

As SET OPTION SQL_SELECT_LIMIT is one query and the SELECT we are trying to do is another, and as it is othwerwise identical in each case, the Query Cache kicks in and gives it the data it has in cache, which of course is only 50 records because the first time around SQL_SELECT_LIMIT was 50.

Thus in our example with5 identical SELECTS in a row each one preceeded by a progressively higher SQL_SELECT_LIMIT the result set was always the result set of 50 records in the cahce after the first time.

This explains why we did not see the problem before Friday (because Query Cache wasn't set before Friday) and on Friday we could see it on the newly installed 4.0.12 but could also reproduce it if we went back to 4.0.11a - because we didn't change my.cnf when we rolled back versions.

If correct this would make the problem not only on Solaris, but on all platforms, and the difference in test results from Mark's efforts on Friday are because his systems do not have Query Cache running, and the tfr system which was tested against on Friday does have query cache running. In other words it was reproduced on Friday on Solaris by luck.

I belive it will reproduce on all platforms if Query Cache is enabled. I would test this using my own Mac system here at home but it's 2:30am on Sunday and I just woke up to this revelation. Now I'll go back to bed and you guys can look at it sometime.

Bruce.

diff -Nrc a/sql/sql_cache.cc b/sql/sql_cache.cc 
*** a/sql/sql_cache.cc	Sun Mar 30 23:52:12 2003 
--- b/sql/sql_cache.cc	Sun Mar 30 23:52:12 2003 
*************** 
*** 763,769 **** 
  			     thd->query, &thd->lex, tables_used))) 
    { 
      NET *net= &thd->net; 
!     byte flags = (thd->client_capabilities & CLIENT_LONG_FLAG ? 
0x80 : 0); 
      STRUCT_LOCK(&structure_guard_mutex); 
   
      if (query_cache_size == 0) 
--- 763,769 ---- 
  			     thd->query, &thd->lex, tables_used))) 
    { 
      NET *net= &thd->net; 
!     byte flags= (thd->client_capabilities & CLIENT_LONG_FLAG ? 
0x80 : 0); 
      STRUCT_LOCK(&structure_guard_mutex); 
   
      if (query_cache_size == 0) 
*************** 
*** 788,795 **** 
      */ 
      flags|= (byte) thd->charset()->number; 
      DBUG_ASSERT(thd->charset()->number < 128); 
!     tot_length=thd->query_length+thd->db_length+2; 
!     thd->query[tot_length-1] = (char) flags; 
   
      /* Check if another thread is processing the same query? */ 
      Query_cache_block *competitor = (Query_cache_block *) 
--- 788,797 ---- 
      */ 
      flags|= (byte) thd->charset()->number; 
      DBUG_ASSERT(thd->charset()->number < 128); 
!     tot_length= thd->query_length+thd->db_length+2+sizeof(ha_rows); 
!     thd->query[tot_length-1]= (char) flags; 
!     memcpy((void *)(thd->query + (tot_length-sizeof(ha_rows)-1)), 
! 	   (const void *)&thd->variables.select_limit, sizeof(ha_rows)); 
   
      /* Check if another thread is processing the same query? */ 
      Query_cache_block *competitor = (Query_cache_block *) 
*************** 
*** 923,929 **** 
    } 
    Query_cache_block *query_block; 
   
!   tot_length=query_length+thd->db_length+2; 
    if (thd->db_length) 
    { 
      memcpy(sql+query_length+1, thd->db, thd->db_length); 
--- 925,931 ---- 
    } 
    Query_cache_block *query_block; 
   
!   tot_length= query_length+thd->db_length+2+sizeof(ha_rows); 
    if (thd->db_length) 
    { 
      memcpy(sql+query_length+1, thd->db, thd->db_length); 
*************** 
*** 939,948 **** 
       Most significant bit - CLIENT_LONG_FLAG, 
       Other - charset number (0 no charset convertion) 
    */ 
!   flags = (thd->client_capabilities & CLIENT_LONG_FLAG ? 0x80 : 
0); 
!   flags |= (byte) thd->charset()->number; 
    DBUG_ASSERT(thd->charset()->number < 128); 
!   sql[tot_length-1] = (char) flags; 
    query_block = (Query_cache_block *)  hash_search(&queries, 
(byte*) sql, 
  						   tot_length); 
    /* Quick abort on unlocked data */ 
--- 941,953 ---- 
       Most significant bit - CLIENT_LONG_FLAG, 
       Other - charset number (0 no charset convertion) 
    */ 
!   flags= (thd->client_capabilities & CLIENT_LONG_FLAG ? 0x80 : 0); 
!   flags|= (byte) thd->charset()->number; 
    DBUG_ASSERT(thd->charset()->number < 128); 
!   sql[tot_length-1]= (char) flags; 
!   memcpy((void *)(sql + (tot_length-sizeof(ha_rows)-1)), 
! 	 (const void *)&thd->variables.select_limit, sizeof(ha_rows)); 
!    
    query_block = (Query_cache_block *)  hash_search(&queries, 
(byte*) sql, 
  						   tot_length); 
    /* Quick abort on unlocked data */ 
diff -Nrc a/sql/sql_parse.cc b/sql/sql_parse.cc 
*** a/sql/sql_parse.cc	Sun Mar 30 23:52:12 2003 
--- b/sql/sql_parse.cc	Sun Mar 30 23:52:12 2003 
*************** 
*** 913,919 **** 
      buff[length]=0; 
      thd->current_tablenr=0; 
      thd->query_length=length; 
!     thd->query= thd->memdup_w_gap(buff, length+1, 
thd->db_length+1); 
      thd->query[length] = '\0'; 
      thd->query_id=query_id++; 
      if (mqh_used && thd->user_connect && check_mqh(thd, 
SQLCOM_END)) 
--- 913,919 ---- 
      buff[length]=0; 
      thd->current_tablenr=0; 
      thd->query_length=length; 
!     thd->query= thd->memdup_w_gap(buff, length+1, thd->db_length 
+ 1); 
      thd->query[length] = '\0'; 
      thd->query_id=query_id++; 
      if (mqh_used && thd->user_connect && check_mqh(thd, 
SQLCOM_END)) 
*************** 
*** 1549,1555 **** 
    /* We must allocate some extra memory for query cache */ 
    if (!(thd->query= (char*) thd->memdup_w_gap((gptr) (packet), 
  					      packet_length, 
! 					      thd->db_length+2))) 
      return 1; 
    thd->query[packet_length]=0; 
    thd->query_length= packet_length; 
--- 1549,1556 ---- 
    /* We must allocate some extra memory for query cache */ 
    if (!(thd->query= (char*) thd->memdup_w_gap((gptr) (packet), 
  					      packet_length, 
! 					      thd->db_length + 2 + 
! 					      sizeof(ha_rows)))) 
      return 1; 
    thd->query[packet_length]=0; 
    thd->query_length= packet_length;

diff -Nrc a/sql/sql_cache.cc b/sql/sql_cache.cc 
*** a/sql/sql_cache.cc	Sun Mar 30 23:52:12 2003 
--- b/sql/sql_cache.cc	Sun Mar 30 23:52:12 2003 
*************** 
*** 763,769 **** 
  			     thd->query, &thd->lex, tables_used))) 
    { 
      NET *net= &thd->net; 
!     byte flags = (thd->client_capabilities & CLIENT_LONG_FLAG ? 
0x80 : 0); 
      STRUCT_LOCK(&structure_guard_mutex); 
   
      if (query_cache_size == 0) 
--- 763,769 ---- 
  			     thd->query, &thd->lex, tables_used))) 
    { 
      NET *net= &thd->net; 
!     byte flags= (thd->client_capabilities & CLIENT_LONG_FLAG ? 
0x80 : 0); 
      STRUCT_LOCK(&structure_guard_mutex); 
   
      if (query_cache_size == 0) 
*************** 
*** 788,795 **** 
      */ 
      flags|= (byte) thd->charset()->number; 
      DBUG_ASSERT(thd->charset()->number < 128); 
!     tot_length=thd->query_length+thd->db_length+2; 
!     thd->query[tot_length-1] = (char) flags; 
   
      /* Check if another thread is processing the same query? */ 
      Query_cache_block *competitor = (Query_cache_block *) 
--- 788,797 ---- 
      */ 
      flags|= (byte) thd->charset()->number; 
      DBUG_ASSERT(thd->charset()->number < 128); 
!     tot_length= thd->query_length+thd->db_length+2+sizeof(ha_rows); 
!     thd->query[tot_length-1]= (char) flags; 
!     memcpy((void *)(thd->query + (tot_length-sizeof(ha_rows)-1)), 
! 	   (const void *)&thd->variables.select_limit, sizeof(ha_rows)); 
   
      /* Check if another thread is processing the same query? */ 
      Query_cache_block *competitor = (Query_cache_block *) 
*************** 
*** 923,929 **** 
    } 
    Query_cache_block *query_block; 
   
!   tot_length=query_length+thd->db_length+2; 
    if (thd->db_length) 
    { 
      memcpy(sql+query_length+1, thd->db, thd->db_length); 
--- 925,931 ---- 
    } 
    Query_cache_block *query_block; 
   
!   tot_length= query_length+thd->db_length+2+sizeof(ha_rows); 
    if (thd->db_length) 
    { 
      memcpy(sql+query_length+1, thd->db, thd->db_length); 
*************** 
*** 939,948 **** 
       Most significant bit - CLIENT_LONG_FLAG, 
       Other - charset number (0 no charset convertion) 
    */ 
!   flags = (thd->client_capabilities & CLIENT_LONG_FLAG ? 0x80 : 
0); 
!   flags |= (byte) thd->charset()->number; 
    DBUG_ASSERT(thd->charset()->number < 128); 
!   sql[tot_length-1] = (char) flags; 
    query_block = (Query_cache_block *)  hash_search(&queries, 
(byte*) sql, 
  						   tot_length); 
    /* Quick abort on unlocked data */ 
--- 941,953 ---- 
       Most significant bit - CLIENT_LONG_FLAG, 
       Other - charset number (0 no charset convertion) 
    */ 
!   flags= (thd->client_capabilities & CLIENT_LONG_FLAG ? 0x80 : 0); 
!   flags|= (byte) thd->charset()->number; 
    DBUG_ASSERT(thd->charset()->number < 128); 
!   sql[tot_length-1]= (char) flags; 
!   memcpy((void *)(sql + (tot_length-sizeof(ha_rows)-1)), 
! 	 (const void *)&thd->variables.select_limit, sizeof(ha_rows)); 
!    
    query_block = (Query_cache_block *)  hash_search(&queries, 
(byte*) sql, 
  						   tot_length); 
    /* Quick abort on unlocked data */ 
diff -Nrc a/sql/sql_parse.cc b/sql/sql_parse.cc 
*** a/sql/sql_parse.cc	Sun Mar 30 23:52:12 2003 
--- b/sql/sql_parse.cc	Sun Mar 30 23:52:12 2003 
*************** 
*** 913,919 **** 
      buff[length]=0; 
      thd->current_tablenr=0; 
      thd->query_length=length; 
!     thd->query= thd->memdup_w_gap(buff, length+1, 
thd->db_length+1); 
      thd->query[length] = '\0'; 
      thd->query_id=query_id++; 
      if (mqh_used && thd->user_connect && check_mqh(thd, 
SQLCOM_END)) 
--- 913,919 ---- 
      buff[length]=0; 
      thd->current_tablenr=0; 
      thd->query_length=length; 
!     thd->query= thd->memdup_w_gap(buff, length+1, thd->db_length 
+ 1); 
      thd->query[length] = '\0'; 
      thd->query_id=query_id++; 
      if (mqh_used && thd->user_connect && check_mqh(thd, 
SQLCOM_END)) 
*************** 
*** 1549,1555 **** 
    /* We must allocate some extra memory for query cache */ 
    if (!(thd->query= (char*) thd->memdup_w_gap((gptr) (packet), 
  					      packet_length, 
! 					      thd->db_length+2))) 
      return 1; 
    thd->query[packet_length]=0; 
    thd->query_length= packet_length; 
--- 1549,1556 ---- 
    /* We must allocate some extra memory for query cache */ 
    if (!(thd->query= (char*) thd->memdup_w_gap((gptr) (packet), 
  					      packet_length, 
! 					      thd->db_length + 2 + 
! 					      sizeof(ha_rows)))) 
      return 1; 
    thd->query[packet_length]=0; 
    thd->query_length= packet_length;