Planet MySQL Planet MySQL: Meta Deutsch Español Français Italiano 日本語 Русский Português 中文
Fun with MySQL options
+3 Vote Up -0 Vote Down

While testing MySQL 5.6, I came across some curious values for the new values used to set the crash-safe slave tables. To get safety, we need to set relay_log_info_repository and master_info_repository to 'TABLE'. That way, the replication information, instead of going to a file, will be saved to two tables in the mysql schema (mysql.slave_relay_log_info and mysql.slave_master_info).

So I was setting these values back and forth between 'FILE' and 'TABLE', until I made a "mistake." Instead of typing


set global relay_log_info_repository='table';

I wrote


set global relay_log_info_repository=1;
To my surprise, it did what I wanted!

show variables like '%repository%';
+---------------------------+-------+
| Variable_name | Value |
+---------------------------+-------+
| master_info_repository | FILE |
| relay_log_info_repository | TABLE |
+---------------------------+-------+

It seems that the server accepts 1 for 'TABLE' and 0 for 'FILE'. But this is not a firm rule. I tried the same thing with another variable that supports 'FILE' and 'TABLE':


set global log_output=0; show variables like 'log_output';
ERROR 1231 (42000): Variable 'log_output' can't be set to the value of '0'
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output | FILE |
+---------------+-------+
1 row in set (0.00 sec)

set global log_output=1; show variables like 'log_output';
Query OK, 0 rows affected (0.00 sec)

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output | NONE |
+---------------+-------+
1 row in set (0.00 sec)

set global log_output=2; show variables like 'log_output';
Query OK, 0 rows affected (0.00 sec)

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output | FILE |
+---------------+-------+
1 row in set (0.00 sec)

set global log_output=3; show variables like 'log_output';
Query OK, 0 rows affected (0.00 sec)

+---------------+-----------+
| Variable_name | Value |
+---------------+-----------+
| log_output | NONE,FILE |
+---------------+-----------+
1 row in set (0.00 sec)

set global log_output=4; show variables like 'log_output';
Query OK, 0 rows affected (0.00 sec)

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output | TABLE |
+---------------+-------+
1 row in set (0.00 sec)
set global log_output=5; show variables like 'log_output';
Query OK, 0 rows affected (0.00 sec)

+---------------+------------+
| Variable_name | Value |
+---------------+------------+
| log_output | NONE,TABLE |
+---------------+------------+
1 row in set (0.00 sec)

set global log_output=6; show variables like 'log_output';
Query OK, 0 rows affected (0.00 sec)

+---------------+------------+
| Variable_name | Value |
+---------------+------------+
| log_output | FILE,TABLE |
+---------------+------------+
1 row in set (0.00 sec)

set global log_output=7; show variables like 'log_output';
Query OK, 0 rows affected (0.00 sec)

+---------------+-----------------+
| Variable_name | Value |
+---------------+-----------------+
| log_output | NONE,FILE,TABLE |
+---------------+-----------------+
1 row in set (0.00 sec)

The manual does not mention multiple values for this variable. The fact that the server accepts such value is strange enough. What is more strange is that in one case, option 1 resolves to 'TABLE' (but not the quoted '1', which is rejected), while in the other case, 'TABLE' is number 4.

I don't think this is intentional. But surely it's confusing.

Votes:

You must be logged in with a MySQL account to vote on Planet MySQL entries. More information on PlanetMySQL voting.

Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.