Sunday, January 06, 2013

Fun with MySQL options

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.

3 comments:

Anonymous said...

"... The manual does not mention multiple values for this variable."

Not so. The manual states:

Type: set
Default: FILE
Valid Values:
TABLE
FILE
NONE

And since the value is a SET, your findings with regard log_output make perfect sense.
Well, "NONE,TABLE" doesn't make sense, I agree... But the multi-valued "3", "5", "6" are just turning on multiple bits in the set.

Giuseppe Maxia said...

Shlomi,
I was too quick to draw my conclusions. Indeed, multiple values are allowed. But the numeric equivalent are not stated in the manual, which leads to nonsensical combinations such as 'NONE,TABLE,FILE'

Anonymous said...

We would surely be better off with making this an ENUM: "NONE, FILE, TABLE, BOTH", rather than the funny 'NONE,FILE,TABLE' value.

Cheers