MySQL Bugs: #40986: Leading line comments prevent Query Cache from caching result.

Bug #40986	Leading line comments prevent Query Cache from caching result.
Submitted:	24 Nov 2008 16:29	Modified:	7 Jul 2017 9:44
Reporter:	David Walker	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: Query Cache	Severity:	S3 (Non-critical)
Version:	4.1, 5.0, 5,1, 6.0 bzr	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	query cache comment

Description:
SELECT statements with leading line comments (beginning with '#' or '--') are still not cached correctly by the Query Cache. 

'/* */'-style comments are fine and do not affect caching. See fix for bug#824, (which is only a partial fix)

Also, the manual page for comment syntax states that leading comments should no longer prevent the query cache from caching results in MySQL 5.0+, but this is only true for '/* */'-style comments, and should be corrected.

How to repeat:

Configure query cache.

mysql> show status like 'query_cache%';

    >>> sql_query = "-- my comment\nSELECT * FROM my_table LIMIT 1"
    >>> cursor.execute(sql_query)
    >>> cursor.execute(sql_query)
    >>> cursor.execute(sql_query)

mysql> show status like 'query_cache%';

Note that number of hits has not increased between "SHOW STATUS..." invocations. Remove the leading comment, and the hits will increase, as expected.

Suggested fix:
Finish the fix to bug#824: also do a cache lookup when '-' and '#' are encountered as the first non-whitespace character in the query text.

Sorry, the manual page I was trying to refer to was:
http://dev.mysql.com/doc/refman/5.0/en/query-cache-how.html
(Not the comment syntax page.)

Thank you for the report.

Verified as described.

PHP test case:

<?
$mysqli = mysqli_connect('127.0.0.1', 'root', '', 'test', 4040);
mysqli_multi_query($mysqli, '
	flush status;
	drop table if exists t1;
	create table t1(id int);
	insert into t1 values(1),(2),(3),(3),(4),(5);
	insert into t1 select * from t1;
	insert into t1 select * from t1;
	insert into t1 select * from t1;
	insert into t1 select * from t1;');

do { if ($res = $mysqli->store_result()) { $res->free(); } } while ($mysqli->next_result());

echo "show status like 'Qcache%':\n";
var_dump(mysqli_error($mysqli));
mysqli_multi_query($mysqli, "show status like 'Qcache%'");
do {
		if ($res = $mysqli->store_result()) {
			while ($row = mysqli_fetch_array($res)) echo $row[0] . ': ' . $row[1] . "\n";
				$res->free();
		}
}while ($mysqli->next_result());

//$query = "select * from t1";
$query="--comment\nselect * from t1";
mysqli_multi_query($mysqli, $query);
do { if ($res = $mysqli->store_result()) { $res->free(); } } while ($mysqli->next_result());
mysqli_multi_query($mysqli, $query);
do { if ($res = $mysqli->store_result()) { $res->free(); } } while ($mysqli->next_result());
mysqli_multi_query($mysqli, $query);
do { if ($res = $mysqli->store_result()) { $res->free(); } } while ($mysqli->next_result());

echo "Error: " . mysqli_error($mysqli) . "\n";

mysqli_multi_query($mysqli, "show status like 'Qcache%'");
echo "show status like 'Qcache%':\n";
do {
		if ($res = $mysqli->store_result()) {
			while ($row = mysqli_fetch_array($res)) echo $row[0] . ': ' . $row[1] . "\n";
						$res->free();
		}
}while ($mysqli->next_result());

?>

It is a problem with 5.1.45 with non-static comments, even with /* */ comments.  I made a PHP test case:

<?php

$conn = mysql_connect('127.0.0.1','root','',true) or die(mysql_error() . "\n");

function get_counters() {
$com_select=0;
$qcache_hits=0;
  global $conn;
  $stmt = mysql_query("show global status where Variable_name in( 'com_select', 'Qcache_hits' )", $conn) or die(mysql_error() ."\n");
  while($row = mysql_fetch_assoc($stmt)) {
  #print_r($row);
    if($row['Variable_name'] == 'Com_select') $com_select = $row['Value'];
    if($row['Variable_name'] == 'Qcache_hits') $qcache_hits = $row['Value'];
  }
  return array("cs" => $com_select, "qc" => $qcache_hits);
}

/* return 1 on qc hit, 0 on miss*/
function test_sql($sql) {
global $conn;
  $sql = sprintf($sql, mt_rand(1,10000));
  $old = get_counters();
  $stmt = mysql_query($sql, $conn);
  $new = get_counters();

  /* was it a miss? */
  if ($new['cs'] - $old['cs'] > 0) return 0;

  return 1; 
}

function testit($sql, $count=25) {
  // this will only change the query with %d in it..
  $hits = 0;
  for($i=0;$i<$count;++$i) {
    if(test_sql($sql) == 1) {
      ++$hits;
    }
  }

  echo "$sql\nTotal: $count\nHits: $hits\n";
}
  

$sql = "select c1 from test.t1 limit 1";
testit($sql);
$sql = " select c1 from test.t1 limit 1";
testit($sql);
$sql = "/* static comment*/select c1 from test.t1 limit 1";
testit($sql);
$sql = "/* changing comment %d*/select c1 from test.t1 limit 1";
testit($sql);
$sql = "/* changing query*/select c1 from test.t1 where c1 <= %d limit 1";
testit($sql);
$sql = "-- static comment\nselect c1 from test.t1 limit 1";
testit($sql);

 echo "\ndone\n";

----- OUTPUT -----

select c1 from test.t1 limit 1
Total: 25
Hits: 25
 select c1 from test.t1 limit 1
Total: 25
Hits: 25
/* static comment*/select c1 from test.t1 limit 1
Total: 25
Hits: 25
/* changing comment %d*/select c1 from test.t1 limit 1
Total: 25
Hits: 0
/* changing query*/select c1 from test.t1 where c1 <= %d limit 1
Total: 25
Hits: 0
-- static comment
select c1 from test.t1 limit 1
Total: 25
Hits: 0

done

I do not think that this should be marked as a feature request.  This is clearly a bug with the query cache!

Bug #55016 was marked as a duplicate of this one.

MySQL will no longer invest in the query cache, see:

http://mysqlserverteam.com/mysql-8-0-retiring-support-for-the-query-cache/