Details

    • Type: Task
    • Status: Open
    • Priority: Major
    • Resolution: Unresolved
    • Fix Version/s: None
    • Component/s: Admin statements
    • 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

        Gliffy Diagrams

          Attachments

            Activity

            Hide
            elenst Elena Stepanova added a comment -

            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.

            Show
            elenst Elena Stepanova added a comment - 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.
            Hide
            stephane@skysql.com VAROQUI Stephane added a comment - - edited

            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.

            Show
            stephane@skysql.com VAROQUI Stephane added a comment - - edited 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.
            Hide
            elenst Elena Stepanova added a comment -

            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
            
            Show
            elenst Elena Stepanova added a comment - 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
            Hide
            stephane@skysql.com VAROQUI Stephane added a comment -

            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.

            Show
            stephane@skysql.com VAROQUI Stephane added a comment - 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.
            Hide
            elenst Elena Stepanova added a comment -

            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.

            Show
            elenst Elena Stepanova added a comment - 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.
            Hide
            danblack Daniel Black added a comment -

            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.

            Show
            danblack Daniel Black added a comment - 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.
            Hide
            tanj Guillaume Lefranc added a comment -

            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.

            Show
            tanj Guillaume Lefranc added a comment - 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.
            Hide
            danblack Daniel Black added a comment -

            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.

            Show
            danblack Daniel Black added a comment -  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.

              People

              • Assignee:
                Unassigned
                Reporter:
                stephane@skysql.com VAROQUI Stephane
              • Votes:
                2 Vote for this issue
                Watchers:
                5 Start watching this issue

                Dates

                • Created:
                  Updated: