Bug #36141 information_schema.ENGINES is not refreshed after a SET GLOBAL storage_engine.

• Submitted: 16 Apr 2008 14:31
• Modified: 17 Apr 2008 9:42
• Reporter: Santo Leto
• Status: Not a Bug
• Category: MySQL Server: Information schema
• Severity: S2 (Serious)
• Version: 5.1.23-rc
• OS: Windows (XPSP2)
• CPU Architecture: Any
• Tags: default storage engine, information_schema.engines

[16 Apr 2008 14:31] Santo Leto

Description:
information_schema.ENGINES is not refreshed after a SET GLOBAL storage_engine.

How to repeat:
Test Script:

SELECT CONNECTION_ID(), VERSION(), CURRENT_USER();

SHOW GLOBAL VARIABLES LIKE 'storage_engine';
SELECT `ENGINE`, `SUPPORT` FROM `INFORMATION_SCHEMA`.`ENGINES`;
SET GLOBAL storage_engine = 'InnoDB';
SHOW GLOBAL VARIABLES LIKE 'storage_engine';
SELECT `ENGINE`, `SUPPORT` FROM `INFORMATION_SCHEMA`.`ENGINES`;
DROP TABLE IF EXISTS `test`.`is_engine_bug`;
CREATE TABLE `test`.`is_engine_bug` (
	`id` BIGINT NULL ,
	`f1` TEXT NULL 
);
SHOW TABLE STATUS FROM test LIKE 'is_engine_bug'\G

Script Output:

mysql> SELECT CONNECTION_ID(), VERSION(), CURRENT_USER();
+-----------------+-------------------------+----------------+
| CONNECTION_ID() | VERSION()               | CURRENT_USER() |
+-----------------+-------------------------+----------------+
|             977 | 5.1.23-rc-community-log | root@localhost |
+-----------------+-------------------------+----------------+
1 row in set (0.00 sec)

mysql>
mysql> SHOW GLOBAL VARIABLES LIKE 'storage_engine';
+----------------+--------+
| Variable_name  | Value  |
+----------------+--------+
| storage_engine | MyISAM |
+----------------+--------+
1 row in set (0.00 sec)

mysql> SELECT `ENGINE`, `SUPPORT` FROM `INFORMATION_SCHEMA`.`ENGINES`;
+------------+---------+
| ENGINE     | SUPPORT |
+------------+---------+
| EXAMPLE    | YES     |
| CSV        | YES     |
| MyISAM     | DEFAULT |
| BLACKHOLE  | YES     |
| MRG_MYISAM | YES     |
| InnoDB     | YES     |
| ARCHIVE    | YES     |
| MEMORY     | YES     |
| FEDERATED  | YES     |
+------------+---------+
9 rows in set (0.00 sec)

## Ok, they match
## Now we change from MyISAM to InnoDB

mysql> SET GLOBAL storage_engine = 'InnoDB';
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GLOBAL VARIABLES LIKE 'storage_engine';
+----------------+--------+
| Variable_name  | Value  |
+----------------+--------+
| storage_engine | InnoDB |
+----------------+--------+
1 row in set (0.00 sec)

mysql> SELECT `ENGINE`, `SUPPORT` FROM `INFORMATION_SCHEMA`.`ENGINES`;
+------------+---------+
| ENGINE     | SUPPORT |
+------------+---------+
| EXAMPLE    | YES     |
| CSV        | YES     |
| MyISAM     | DEFAULT |
| BLACKHOLE  | YES     |
| MRG_MYISAM | YES     |
| InnoDB     | YES     |
| ARCHIVE    | YES     |
| MEMORY     | YES     |
| FEDERATED  | YES     |
+------------+---------+
9 rows in set (0.00 sec)

## They do not match !
## Just a check, MyISAM is used

mysql> DROP TABLE IF EXISTS `test`.`is_engine_bug`;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE `test`.`is_engine_bug` (
    ->  `id` BIGINT NULL ,
    ->  `f1` TEXT NULL
    -> );
Query OK, 0 rows affected (0.08 sec)

mysql> SHOW TABLE STATUS FROM test LIKE 'is_engine_bug'\G
*************************** 1. row ***************************
           Name: is_engine_bug
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 0
 Avg_row_length: 0
    Data_length: 0
Max_data_length: 281474976710655
   Index_length: 1024
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2008-04-16 15:46:42
    Update_time: 2008-04-16 15:46:42
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

Further Consideration - Workaround:

# If we open a new connection, information_schema.engines is refreshed:

SELECT CONNECTION_ID(), VERSION(), CURRENT_USER;
SHOW GLOBAL VARIABLES LIKE 'storage_engine';
SELECT `ENGINE`, `SUPPORT` FROM `INFORMATION_SCHEMA`.`ENGINES`;

mysql> SELECT CONNECTION_ID(), VERSION(), CURRENT_USER;
+-----------------+-------------------------+----------------+
| CONNECTION_ID() | VERSION()               | CURRENT_USER   |
+-----------------+-------------------------+----------------+
|             978 | 5.1.23-rc-community-log | root@localhost |
+-----------------+-------------------------+----------------+
1 row in set (0.00 sec)

mysql> SHOW GLOBAL VARIABLES LIKE 'storage_engine';
+----------------+--------+
| Variable_name  | Value  |
+----------------+--------+
| storage_engine | InnoDB |
+----------------+--------+
1 row in set (0.00 sec)

mysql> SELECT `ENGINE`, `SUPPORT` FROM `INFORMATION_SCHEMA`.`ENGINES`;
+------------+---------+
| ENGINE     | SUPPORT |
+------------+---------+
| EXAMPLE    | YES     |
| CSV        | YES     |
| MyISAM     | YES     |
| BLACKHOLE  | YES     |
| MRG_MYISAM | YES     |
| InnoDB     | DEFAULT |
| ARCHIVE    | YES     |
| MEMORY     | YES     |
| FEDERATED  | YES     |
+------------+---------+
9 rows in set (0.00 sec)

mysql>

[17 Apr 2008 9:42] Valeriy Kravchuk

This looks like expected and documented behaviour. Please, read http://dev.mysql.com/doc/refman/5.0/en/set-option.html:

"If you change a global system variable, the value is remembered and used for new connections until the server restarts. (To make a global system variable setting permanent, you should set it in an option file.) The change is visible to any client that accesses that global variable. However, the change affects the corresponding session variable only for clients that connect after the change. The global variable change does not affect the session variable for any client that is currently connected (not even that of the client that issues the SET GLOBAL statement)."