MySQL Bugs: #24253: query_cache_size not 0 at start-up

Bug #24253	query_cache_size not 0 at start-up
Submitted:	13 Nov 2006 11:24	Modified:	23 Feb 2007 12:24
Reporter:	Yahoo Serious (Silver Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Installing	Severity:	S3 (Non-critical)
Version:	5.0.27, 4.1.21	OS:	Windows (Windows 2000)
Assigned to:	MC Brown	CPU Architecture:	Any
Tags:	qc

Description:
Documentation explicitly states query_cache_size will be initialized at a default size of 0, e.g.:
  http://dev.mysql.com/doc/refman/5.0/en/query-cache-configuration.html
"The default size is 0, so the query cache is disabled by default."

It is not.  This is probably due to configuration (MySQLInstanceConfig.exe), because the size varies, depending on the values I enter in there.  However I don't know how to register (hence start) my service without configuration...

How to repeat:
1. install and configure MySQL server (5.0.27 or 4.1.21)

2.
 a)  use query: SHOW VARIABLES LIKE 'query_cache_%', or 
 b)  read 'query_cache_size' in my.ini 

Suggested fix:
- Align documentation and practice
- Add an option to configuration to enable/disable query-caching

(Maybe related to Bug #28?)

Thank you for the bug report and changed to feature request.

The suggested solution may be a feature request, but if the installation is not according to documentation, it's a bug, IMHO.  

If it's not considered a bug, please tell me how to install, register and run the <MySql server service> on Windows with the documented behavior (query_cache_size defaults to 0, without explicitly setting it).

Note that this is not non-default behavior in the server.  It is a 'flaw' in the installer (changing certain values in my.ini), which leads to unexpected non-default (but correct) server behavior.  

Unexpected behavior that I did not see documented anywhere.  So this leads me to two conclusions:
 1.  the installer should
    a) also support a default installation (no [changed] values in my.ini)
    b) explicitly mention the default behavior being changed
 2.  the documentation should warn naive people like me for the present non-default behavior

In addition to #1b I would like to mention, that I do expect _values_ to change from default when inputting values about expected load, etc.  What else could be the point of inputting them.  But I expect a change in the values that differ in my-small.ini and my-huge.ini.  I do not expect a change in _behavior_ (like turning on the query-cache).

So I would stringly suggest changing the installer, and as a minor addition documenting the installer's behavior.

I've updated the documentation so that it mentions that when using the Windows configuration wizard that the default value will be altered from the normal default. I've also extended the description of the query_cache_size configuration to indicate that the value is rounded to the nearest 1024 bytes.

Thanks for extending the documentation.  

Technically it is still a bit off: the "default value" is still 0 - if you remove the value from the .ini, or the complete .ini the system will use 0.  However, the query cache is not "disabled by default".
So the text would be more correct if the last sentence was changed from:
---
When using the Windows Configuration Wizard to install or configure MySQL, the default value for query_cache_size will be configured automatically for you based on the different configuration types available. In this instance, the default value will not be 0.
---
To:
---
In this instance, the initial value will not be 0 and the query cache is enabled by default.
---

I do think the present behavior is already documented in a better way.  
But I still do think that the installer should offer a way to not change the default behavior (just some size-parameters), or else explicitly mention the default behavior being changed.
I think thi sis far more important, and that is why I changed the bug-category from "Documentation" to "Installing".  This is still not fixed?!

Well, "enabled by default" should of course have a "not":

In this instance, the initial value will not be 0 and the query cache is not enabled like the default.

Besides, this used to be "verified", could you please reset it to that?

Thank you for the feedback.

I have updated the manual to highlight the changes made by the Config Wizard. Rather than specifying that the Config Wizard will explicitly enable/disable the cache, instead the documentation has been updated to say that the Config Wizard may alter the value and the the setting of the QC. In particular, within the QC section, I have added this note in place of the 'QC is enabled by default' sentence: 

When using the Windows Configuration Wizard,
the query cache may be enabled (i.e. set to a non-zero value)
due to the selected configuration. The query cache is also
controlled by the setting of the
<literal>query_cache_type</literal> variable. You should check
the values of these variables as set in your
<filename>my.ini</filename> file after configuration has taken
place.

And within the Config Wizard sections: 

By selecting one of the preconfigured configurations, the
values and settings of various options in your
<filename>my.cnf</filename> or <filename>my.ini</filename>
will be altered accordingly. The default values and options
as described in the reference manual may therefore be
different to the options and values that were created during
the execution of the configuration wizard.

These are both intended to make it clear that the Config Wizard doesn't alter the *Default* setting, but it *does* alter the configuration of your MySQL server as part of the configuration process.