MySQL Bugs: #59393: have_innodb=YES when mysqld started with --skip-innodb

Bug #59393	have_innodb=YES when mysqld started with --skip-innodb
Submitted:	10 Jan 2011 12:47	Modified:	25 Apr 2011 13:15
Reporter:	Craig Thomson	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Options	Severity:	S2 (Serious)
Version:	5.5.8, 5.5.9-bzr	OS:	Any (SunOS 5.10 Generic_142910-17 i86pc i386 i86pc)
Assigned to:	Kristofer Pettersson	CPU Architecture:	Any
Tags:	--skip-innodb, DBD::MySQL, have_innodb, regression

Description:
Starting mysqld with --skip-innodb correctly disables the innodb engine, however the have_innodb variable contains a value of YES.

Similarly, starting mysqld with ignore_builtin_innodb correctly maked mysqld behave as if the built-in InnoDB is not present, however the have_innodb variable contains a value of YES.

As a consequence of this, the perl module DBD::mysql cannot be installed.  It fails several tests because it believes the innodb engine is enabled (and should therefore be tested).

In the example below, mysqld was started with --skip-innodb

[1076] root@server:/root # mysql mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.8 MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show engines; show variables like '%innodb%';
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MyISAM             | DEFAULT | MyISAM storage engine                                          | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| InnoDB             | NO      | Supports transactions, row-level locking, and foreign keys     | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| have_innodb           | YES   |
| ignore_builtin_innodb | OFF   |
+-----------------------+-------+
2 rows in set (0.00 sec)

mysql> Bye
[1077] root@server:/root #

How to repeat:
Start mysqld with either --skip-innodb or ignore_builtin_innodb.

root@host # mysql mysql

mysql> show engines; show variables like '%innodb%';

Suggested fix:
No suggested fix for the have_innodb variable.

Suggested fix to install DBD::mysql is to start mysqld with the innodb engine enabled, compile/install DBD::mysql, then restart mysqld with innodb disabled.

Please, send my.cnf and error log from your server.

/etc/my.cnf

Attachment: my.cnf (text/plain), 4.63 KiB.

server.err

Attachment: server.err (text/plain), 13.72 KiB.

Requested files attached.

The server.err log shows that innodb was disabling as requested (when mysqld was started with --skip-innodb).  It also shows that I started mysqld once with innodb enabled so I could successfully install DBD::mysql.

Now it is all clear. I've missed explicit setting of MyISAM as default storage engine.

Verified with current mysql-5.5 tree on Mac OS X:

macbook-pro:5.5 openxs$ bin/mysqld_safe --default-storage-engine=MyISAM --skip-innodb &
[1] 38143
macbook-pro:5.5 openxs$ 110110 16:28:50 mysqld_safe Logging to '/Users/openxs/dbs/5.5/data/macbook-pro.err'.
chown: /Users/openxs/dbs/5.5/data/macbook-pro.err: Operation not permitted
110110 16:28:50 mysqld_safe Starting mysqld daemon with databases from /Users/openxs/dbs/5.5/data

macbook-pro:5.5 openxs$ bin/mysql -uroot testReading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.9-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MyISAM             | DEFAULT | MyISAM storage engine                                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| InnoDB             | NO      | Supports transactions, row-level locking, and foreign keys     | NULL         | NULL | NULL       |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

mysql> show variables like 'have_i%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_innodb   | YES   |
+---------------+-------+
1 row in set (0.01 sec)

This does not happen in 5.1.x, so we have a regression bug here.

This is just a matter of setting the default. Easily fixable imho.

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

Noted in 5.5.12, 5.6.3 changelogs.

When the server was started with the --skip-innodb option, it
initialized the have_innodb system variable to YES rather than
DISABLED. 

CHANGESET - http://lists.mysql.com/commits/133753

Correction: No 5.6.3 changelog entry. have_innodb has been removed from 5.6.x.