Why some of mysql system variables are readonly

Question

There are almost more or less 277 mysql server system variables . In these variables some of the variables are dynamic and few are not.

I don't under stand what is the reason behind read only variables . why few of mysql system variables are read only. what will happen if they make those variable dynamic.

Is there any deep reason for the read only variables?

For now we can consider :

• innodb_buffer_pool_size
• innodb_checksums
• datetime_format
• log-bin

and there are many other variable we can find at http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html

Of course, I know we can't change variable like version. But many other variables like log-bin are not dynamic. For me, it does not make sense if general_log and log_slow_queries are dynamic. Why is log-bin not dynamic? like wise there are many other variables.

Answer 1

innodb_buffer_pool_size

The InnoDB Storage Engine has too many moving parts to just allow the buffer pool to be resized on the fly because the buffer pool interacts with system tablespace (ibdata1) and the transactions logs (ib_logfile0, ib_logfile1).

log-bin

In spite of the MySQL Documentation, you can change the effect of log-bin using another variable called SQL_LOG_BIN. Look at the effects:

SESSION

If you run

SET SQL_LOG_BIN = 0; 

this will disable your DB Session from recording binlog events.

If you run

SET GLOBAL SQL_LOG_BIN = 0; 

this will disable all incoming DB Sessions from recording binlog events.

General Log / Slow Log

As a DBA, I am very glad that the general log and slow log are dynamic. Otherwise

• how could do perform any log rotation ?
• how to stop these logs before diskspace runs out ?

Here are some posts I made about doing log rotations

• Nov 10, 2012 : Redirecting MySQL errors and slow logs into another database
• Mar 30, 2012 : MySQL slow log configuration
• Jun 30, 2011 : How do I output MySQL logs to syslog?

Datetime Format

mysqld is usually cognizant of timezone and internalization of datetime displays. I do not see a beneficial reason for shifting the datetime format on demand. This may actual cause problems, not so much with mysqld, but with PHP/Python/Ruby scripts that read dates from the MySQL instance and may misinterpret '04/12/2013' as April 12th when it should be Dec 4th. That would be developer's nightmare to deal with. Having datetime format a read-only variable simply provides a safety net against such a death-defying move whereas proper planning of the database and code interoperability with dates would be in order.

• neotam: Where does the (sql_log_bin)session binary logs will store.
• RolandoMySQLDBA: There is only one set of binlogs for mysqld. All DB connections write to the same set of binlogs. Setting SQL_LOG_BIN signals mysqld that my session will not record binlog events. Setting GLOBAL SQL_LOG_BIN signals mysqld to reject all binlog recording. When you run SHOW GLOBAL VARIABLES LIKE 'log_bin';, you will see either on or off, 1 or 0.
• neotam: You mean all session(sql_log_bin) binary logs and global(bin_log) binary logs will store in only single binary log file ? I understand sql_log_bin is a session variable and bin_log is a global variable. To enable the session binary logs we will use sql_log_bin , my question is how to find that binary log file where session binary logs does store
• RolandoMySQLDBA: There is no such thing as session binary logs. There are only global binary logs.