[MDEV-7969] dynamically enable/disable binlog Created: 2015-04-09  Updated: 2015-04-22

Status: Open
Project: MariaDB Server
Component/s: Admin statements
Fix Version/s: None

Type: Task Priority: Major
Reporter: VAROQUI Stephane Assignee: Unassigned
Resolution: Unresolved Votes: 3
Labels: None


 Description   

This task would be to re enabling dynamic global binlog setting in MariaDB ?

set global log_bin=OFF;
Variable 'log_bin' is a read only variable;

Most common scenario are

  • production server that simply can't be restarted and need to become master
  • Spider, Galera can control binlog to resync node (removing gcache)
  • Dynamic auto configuration scripts
  • Fail over scripts can promote a slave that as no binlog

SQL_LOG_BIN can be use for SESSIONS and LOG_BIN=0N for GLOBAL
We get the best of all world and no risk of confusion.

Other solution would be to introduce new GRANTS
GLOBAL BINARY LOG

Thanks



 Comments   
Comment by Elena Stepanova [ 2015-04-09 ]

I don't see why it is a regression, why it is a critical and why it is a bug.
Reducing priority and switching to a task. I'll keep the original summary though because I just don't get it.

Comment by VAROQUI Stephane [ 2015-04-09 ]

It's a regression because before the patch one can start a server without taking binary logs and activate them later on without restarting.
After the patch i can't see a way of doing this, may be i miss education here. If a patch cancel the usage of drop table, would it be a bug or a task ? Someone can also complain that drop table is dangerous and should be remove. Fortunately someone clever decided that there could be granted permission for this action. So having special grants would be a solution as well as having different variables to disable binlog global or per session. So it's already humiliating that famous blogger and partner blog post was first picked up by MySQL, but that we simply merging it without thinking of a proper design that take into account where MariaDB should stay in regard to dynamic setting of variables and for online operations.

Comment by Elena Stepanova [ 2015-04-09 ]

It's a regression because before the patch one can start a server without taking binary logs and activate them later on without restarting.

No, one couldn't. By changing the value of SQL_LOG_BIN one could disable binary logging if it was activated at startup (and then enable again), but If binary log wasn't enabled at startup, sql_log_bin had no effect, either global or session.
Please try, and if you manage to enable the binary log at runtime, please paste the output of how you did it. Here is mine, of MariaDB 5.5.40.

MariaDB [test]> select @@log_bin;
+-----------+
| @@log_bin |
+-----------+
|         0 |
+-----------+
1 row in set (0.00 sec)
 
MariaDB [test]> set sql_log_bin=1;
Query OK, 0 rows affected (0.00 sec)
 
MariaDB [test]> create table t1 (i int);
Query OK, 0 rows affected (0.38 sec)
 
MariaDB [test]> show binary logs;
ERROR 1381 (HY000): You are not using binary logging
MariaDB [test]> 
MariaDB [test]> set global sql_log_bin=1;
Query OK, 0 rows affected (0.00 sec)
 
MariaDB [test]> create table t2 (i int);
Query OK, 0 rows affected (0.25 sec)
 
MariaDB [test]> show binary logs;
ERROR 1381 (HY000): You are not using binary logging
MariaDB [test]> exit
Bye

elenst@wheezy-64:/data/releases/mariadb-5.5.40-linux-x86_64$ ml test
Reading 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 MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.40-MariaDB MariaDB Server
 
Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
MariaDB [test]> select @@log_bin, @@global.log_bin, @@sql_log_bin, @@global.sql_log_bin;
+-----------+------------------+---------------+----------------------+
| @@log_bin | @@global.log_bin | @@sql_log_bin | @@global.sql_log_bin |
+-----------+------------------+---------------+----------------------+
|         0 |                0 |             1 |                    1 |
+-----------+------------------+---------------+----------------------+
1 row in set (0.00 sec)
 
MariaDB [test]> create table t3 (i int);
Query OK, 0 rows affected (0.28 sec)
 
MariaDB [test]> show binary logs;
ERROR 1381 (HY000): You are not using binary logging
MariaDB [test]> exit

Comment by VAROQUI Stephane [ 2015-04-09 ]

Yes correct, i'm taking about the possibility to
activate binlog in the configuration file and an init_scipt with set global sql_log_bin=0;

Such default allow me to activate the binlog on demand like for example if a slave should become a master in any slave promotion tool.

Comment by Elena Stepanova [ 2015-04-10 ]

I see.
It sounds like a scary approach, so I'm not quite sure it's such a bad thing that people can't do it anymore.
Anyway, the request stays open and will be considered.

Comment by Daniel Black [ 2015-04-11 ]

I quite like Jeremy's thoughts on global sql_log_bin that it should be only a session thing and Oracle have made it so in 5.7
http://blog.jcole.us/2014/08/08/stupid-and-dangerous-set-global-sql_log_bin / http://blog.jcole.us/2014/12/04/thanks-oracle-for-fixing-the-stupid-and-dangerous-set-global-sql_log_bin/

Having log_bin as global wr sounds like a fair idea.

conceptually this is possible.

mysql_mutex_lock(&LOCK_log);
if var=off {
mysql_bin_log.close(LOG_CLOSE_STOP_EVENT|LOG_CLOSE_INDEX)
} else {
mysql_bin_log.open(var, LOG_BIN, 0,
                                        WRITE_CACHE, max_binlog_size, 0, TRUE)
}
mysql_mutex_unlock(&LOCK_log);

There of course a lot of calls to mysql_bin_log.is_open (volatile log_state!=CLOSED) doing extra work for binary logging without any locking. Even MYSQL_BIN_LOG::write checks is_open before acquiring LOCK_LOG.

At least one is_open call is in DBUG_ASSERT. It would be a fairly big undertaking to ensure that these paths return safely after disabling/enabling or changing the variable log_bin.

Comment by Guillaume Lefranc [ 2015-04-16 ]

Such a feature would be a great differentiator for mariadb in the sense of having multi tier replication setups and being able to promote slaves on the fly without restarting them.

Comment by Daniel Black [ 2015-04-22 ]

MDEV-6555 seems sql_log_bin isn't global any more.

Having log_bin change having an effect once flush logs are issued seems like a fair way to not too intrusively introduce this. I'm assuming there's sufficient locking around that implementation.

Generated at Thu Feb 08 07:23:40 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.