Understanding SHOW VARIABLES: DISABLED and NO values

When you use SHOW VARIABLES LIKE “have_%” to see whether a particular feature is enabled, you will note the value of NO for some, and DISABLED for others. These values are not intrinsically clear for the casual onlooker, and often cause confusion. Typically, this happens with SSL and InnoDB. So, here is a quick clarification!

  • NO means that the feature was not enabled (or was actively disabled) in the build. This means the code and any required libraries are not present in the binary.
  • DISABLED means that the feature is built in and capable of working in the binary, but is disabled due to relevant my.cnf settings.
  • YES means the feature is available, and configured in my.cnf.

SSL tends to show up as DISABLED, until you configure the appropriate settings to use it in my.cnf (SHOW VARIABLES LIKE “ssl_%”). From then on it will show up as YES.

Depending on your MySQL version and distro build, InnoDB can be disabled via the “skip-innodb” option. Obviously that’s not recommended as InnoDB should generally be your primary engine of choice!

However, InnoDB can also show up as DISABLED if the plugin fails to load due to configuration or other errors on startup. When this happens, review the error log (often redirected to syslog/messages) to identify the problem.

If InnoDB is configured as the default storage engine, failed initialisation of the plugin should now result in mysqld not starting, rather than starting with InnoDB disabled, as obviously InnoDB is required in that case.

One thought on “Understanding SHOW VARIABLES: DISABLED and NO values”

  1. Good post. I didn’t know the exact difference between NO and DISABLED before.

    One nitpick: I would escape the underscore in the show variables query, like this: SHOW VARIABLES LIKE ‘have\_%';

    It doesn’t change the results in this specific case, but given the proliferation of underscores used in MySQL names and the fact that it also happens to be a single character wildcard, I always like to escape it as a matter of habit.

    Here’s a more useful show variables example to illustrate the difference. Run these and you should get more than you want from the first query, and exactly what you want from the second query

    show variables like “time_%”;
    show variables like “time\_%”;

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>