Percona Server moved to https://jira.percona.com/projects/PS

innodb_log_block_size is not dynamic

Bug #1054090 reported by Jay Janssen on 2012-09-21

This bug affects 2 people

	Status	Importance	Assigned to
Percona Server moved to https://jira.percona.com/projects/PS	Fix Released	Medium	Hrvoje Matijakovic
5.1	Fix Released	Medium	Hrvoje Matijakovic
5.5	Fix Released	Medium	Hrvoje Matijakovic

Bug Description

http://www.percona.com/doc/percona-server/5.5/scalability/innodb_io_55.html#innodb_log_block_size

states the variable is 'dynamic, but it clearly is not:

mysql> set global innodb_log_block_size=4096;
ERROR 1238 (HY000): Variable 'innodb_log_block_size' is a read only variable

Also, does this either break backwards compatibility or require a fresh set logs? If so, it should be documented as such

Related branches

lp:~hrvojem/percona-server/bug1054090-5.1

Merged into lp:percona-server/5.1 at revision 484

Alexey Kopytov (community): Approve on 2012-10-05

lp:~hrvojem/percona-server/bug1054090-5.5-t2

Merged into lp:percona-server/5.5 at revision 313

Alexey Kopytov (community): Approve on 2012-10-05

Revision history for this message

Ovais Tariq (ovais-tariq) wrote on 2012-09-22:

Download full text (11.7 KiB)

A fresh pair of logs is needed for the variable 'innodb_log_block_size' to be changed, which means a clean restart and removal of the old logs.

Here is how I tested it:

[root@ovaistariq-net mysql-test]# ./mtr --start-and-exit
Logging: ./mtr --start-and-exit
120922 13:38:24 [Note] Flashcache bypass: disabled
120922 13:38:24 [Note] Flashcache setup error is : ioctl failed

120922 13:38:24 [Note] Plugin 'FEDERATED' is disabled.
MySQL Version 5.5.23
Checking supported features...
- skipping ndbcluster
- SSL connections supported
Using suites: main,sys_vars,binlog,federated,rpl,innodb,perfschema
Collecting tests...
vardir: /work/sources/percona-server/5.5.23-25.3/Percona-Server/mysql-test/var
Removing old var directory...
Creating var directory '/work/sources/percona-server/5.5.23-25.3/Percona-Server/mysql-test/var'...
Installing system database...
Using server port 59141

==============================================================================

TEST RESULT TIME (ms) or COMMENT
--------------------------------------------------------------------------

worker[1] - 'localhost:13000' was not free
worker[1] Using MTR_BUILD_THREAD 301, with reserved ports 13010..13019
worker[1] mysql-test-run: WARNING: running this script as _root_ will cause some tests to be skipped
worker[1]
Started [mysqld.1 - pid: 10340, winpid: 10340]
worker[1] Using config for test main.1st
worker[1] Port and socket path for server(s):
worker[1] mysqld.1 13010 /work/sources/percona-server/5.5.23-25.3/Percona-Server/mysql-test/var/tmp/mysqld.1.sock
worker[1] Server(s) started, not waiting for them to finish

[root@ovaistariq-net mysql-test]# ../client/mysql -S ./var/tmp/mysqld.1.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.23-rel25.3-log Percona Server with XtraDB (GPL), Release rel25.3, Revision 240

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select @@innodb_log_block_size;
+-------------------------+
| @@innodb_log_block_size |
+-------------------------+
| 512 |
+-------------------------+
1 row in set (0.00 sec)

mysql> set global innodb_log_block_size=4*1024;
ERROR 1238 (HY000): Variable 'innodb_log_block_size' is a read only variable
mysql> Bye

[root@ovaistariq-net mysql-test]# vim ./var/my.cnf
innodb_log_block_size=4096

[root@ovaistariq-net mysql-test]# ../client/mysqladmin -S ./var/tmp/mysqld.1.sock shutdown
120922 13:58:31 [Note] /work/sources/percona-server/5.5.23-25.3/Percona-Server/sql/mysqld: Normal shutdown

120922 13:58:31 [Note] Event Scheduler: Purging the queue. 0 events
120922 13:58:32 InnoDB: Starting shutdown...
120922 13:58:35 InnoDB: Shutdown completed; log sequence number 12599894
120922 13:58:35 [Note] /work/sources/percona-server/5.5.23-25.3/Percona-Server/sql/mysqld: Shutdown complete

[root@ovaistariq-net mysql-test]# /work/sources/percona-server/5.5.2...

A fresh pair of logs is needed for the variable 'innodb_log_block_size' to be changed, which means a clean restart and removal of the old logs.

Here is how I tested it:

[root@ovaistariq-net mysql-test]# ./mtr --start-and-exit
Logging: ./mtr  --start-and-exit
120922 13:38:24 [Note] Flashcache bypass: disabled
120922 13:38:24 [Note] Flashcache setup error is : ioctl failed

120922 13:38:24 [Note] Plugin 'FEDERATED' is disabled.
MySQL Version 5.5.23
Checking supported features...
 - skipping ndbcluster
 - SSL connections supported
Using suites: main,sys_vars,binlog,federated,rpl,innodb,perfschema
Collecting tests...
vardir: /work/sources/percona-server/5.5.23-25.3/Percona-Server/mysql-test/var
Removing old var directory...
Creating var directory '/work/sources/percona-server/5.5.23-25.3/Percona-Server/mysql-test/var'...
Installing system database...
Using server port 59141

==============================================================================

TEST                                      RESULT   TIME (ms) or COMMENT
--------------------------------------------------------------------------

worker[1]  - 'localhost:13000' was not free
worker[1] Using MTR_BUILD_THREAD 301, with reserved ports 13010..13019
worker[1] mysql-test-run: WARNING: running this script as _root_ will cause some tests to be skipped
worker[1] 
Started [mysqld.1 - pid: 10340, winpid: 10340]
worker[1] Using config for test main.1st
worker[1] Port and socket path for server(s):
worker[1] mysqld.1  13010  /work/sources/percona-server/5.5.23-25.3/Percona-Server/mysql-test/var/tmp/mysqld.1.sock
worker[1] Server(s) started, not waiting for them to finish

[root@ovaistariq-net mysql-test]# ../client/mysql -S ./var/tmp/mysqld.1.sock 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.23-rel25.3-log Percona Server with XtraDB (GPL), Release rel25.3, Revision 240

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select @@innodb_log_block_size;
+-------------------------+
| @@innodb_log_block_size |
+-------------------------+
|                     512 |
+-------------------------+
1 row in set (0.00 sec)

mysql> set global innodb_log_block_size=4*1024;
ERROR 1238 (HY000): Variable 'innodb_log_block_size' is a read only variable
mysql> Bye

[root@ovaistariq-net mysql-test]# vim ./var/my.cnf 
innodb_log_block_size=4096

[root@ovaistariq-net mysql-test]# ../client/mysqladmin -S ./var/tmp/mysqld.1.sock shutdown
120922 13:58:31 [Note] /work/sources/percona-server/5.5.23-25.3/Percona-Server/sql/mysqld: Normal shutdown

120922 13:58:31 [Note] Event Scheduler: Purging the queue. 0 events
120922 13:58:32  InnoDB: Starting shutdown...
120922 13:58:35  InnoDB: Shutdown completed; log sequence number 12599894
120922 13:58:35 [Note] /work/sources/percona-server/5.5.23-25.3/Percona-Server/sql/mysqld: Shutdown complete

[root@ovaistariq-net mysql-test]# /work/sources/percona-server/5.5.23-25.3/Percona-Server/sql/mysqld --defaults-group-suffix=.1 --defaults-file=/work/sources/percona-server/5.5.23-25.3/Percona-Server/mysql-test/var/my.cnf --user=root --log-output=file --default-storage-engine=MyISAM --loose-skip-log-bin --core-file --loose-debug-sync-timeout=300 &
[1] 10397
[root@ovaistariq-net mysql-test]# 120922 13:58:40 [Note] Flashcache bypass: disabled
120922 13:58:40 [Note] Flashcache setup error is : ioctl failed

120922 13:58:40 [Note] Plugin 'FEDERATED' is disabled.
InnoDB: Warning: innodb_log_block_size has been changed from default value 512. (###EXPERIMENTAL### operation)
InnoDB: The log block size is set to 4096.
120922 13:58:40 InnoDB: The InnoDB memory heap is disabled
120922 13:58:40 InnoDB: Mutexes and rw_locks use GCC atomic builtins
120922 13:58:40 InnoDB: Compressed tables use zlib 1.2.3
120922 13:58:40 InnoDB: Using Linux native AIO
120922 13:58:40 InnoDB: Initializing buffer pool, size = 32.0M
120922 13:58:40 InnoDB: Completed initialization of buffer pool
120922 13:58:40 InnoDB: highest supported file format is Barracuda.
InnoDB: No valid checkpoint found.
InnoDB: If this error appears when you are creating an InnoDB database,
InnoDB: the problem may be that during an earlier attempt you managed
InnoDB: to create the InnoDB data files, but log file creation failed.
InnoDB: If that is the case, please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/error-creating-innodb.html
120922 13:58:40 [ERROR] Plugin 'InnoDB' init function returned error.
120922 13:58:40 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
120922 13:58:40 [Warning] /work/sources/percona-server/5.5.23-25.3/Percona-Server/sql/mysqld: unknown variable 'loose-debug-sync-timeout=300'
120922 13:58:40 [Note] Event Scheduler: Loaded 0 events
120922 13:58:40 [Note] /work/sources/percona-server/5.5.23-25.3/Percona-Server/sql/mysqld: ready for connections.
Version: '5.5.23-rel25.3-log'  socket: '/work/sources/percona-server/5.5.23-25.3/Percona-Server/mysql-test/var/tmp/mysqld.1.sock'  port: 13010  Percona Server with XtraDB (GPL), Release rel25.3, Revision 240

[root@ovaistariq-net mysql-test]# ../client/mysql -S ./var/tmp/mysqld.1.sock 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.23-rel25.3-log Percona Server with XtraDB (GPL), Release rel25.3, Revision 240

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MyISAM             | DEFAULT | MyISAM storage engine                                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
8 rows in set (0.00 sec)

mysql> Bye

[root@ovaistariq-net mysql-test]# ../client/mysqladmin -S ./var/tmp/mysqld.1.sock shutdown
120922 14:04:11 [Note] /work/sources/percona-server/5.5.23-25.3/Percona-Server/sql/mysqld: Normal shutdown

120922 14:04:11 [Note] Event Scheduler: Purging the queue. 0 events
120922 14:04:11 [Note] /work/sources/percona-server/5.5.23-25.3/Percona-Server/sql/mysqld: Shutdown complete

[root@ovaistariq-net mysql-test]# mv ./var/mysqld.1/data/ib_logfile0 ./var/mysqld.1/data/ib_logfile0.bak
[root@ovaistariq-net mysql-test]# mv ./var/mysqld.1/data/ib_logfile1 ./var/mysqld.1/data/ib_logfile1.bak

[root@ovaistariq-net mysql-test]# /work/sources/percona-server/5.5.23-25.3/Percona-Server/sql/mysqld --defaults-group-suffix=.1 --defaults-file=/work/sources/percona-server/5.5.23-25.3/Percona-Server/mysql-test/var/my.cnf --user=root --log-output=file --default-storage-engine=MyISAM --loose-skip-log-bin --core-file --loose-debug-sync-timeout=300 &
[1] 10428
[root@ovaistariq-net mysql-test]# 120922 14:05:25 [Note] Flashcache bypass: disabled
120922 14:05:25 [Note] Flashcache setup error is : ioctl failed

120922 14:05:25 [Note] Plugin 'FEDERATED' is disabled.
InnoDB: Warning: innodb_log_block_size has been changed from default value 512. (###EXPERIMENTAL### operation)
InnoDB: The log block size is set to 4096.
120922 14:05:25 InnoDB: The InnoDB memory heap is disabled
120922 14:05:25 InnoDB: Mutexes and rw_locks use GCC atomic builtins
120922 14:05:25 InnoDB: Compressed tables use zlib 1.2.3
120922 14:05:25 InnoDB: Using Linux native AIO
120922 14:05:25 InnoDB: Initializing buffer pool, size = 32.0M
120922 14:05:25 InnoDB: Completed initialization of buffer pool
120922 14:05:25  InnoDB: Log file ./ib_logfile0 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile0 size to 5 MB
InnoDB: Database physically writes the file full: wait...
120922 14:05:26  InnoDB: Log file ./ib_logfile1 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile1 size to 5 MB
InnoDB: Database physically writes the file full: wait...
120922 14:05:26 InnoDB: highest supported file format is Barracuda.
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
120922 14:05:26  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
120922 14:05:26  InnoDB: Waiting for the background threads to start
120922 14:05:27 Percona XtraDB (http://www.percona.com) 1.1.8-rel25.3 started; log sequence number 12603404
120922 14:05:27 [Warning] /work/sources/percona-server/5.5.23-25.3/Percona-Server/sql/mysqld: unknown variable 'loose-debug-sync-timeout=300'
120922 14:05:27 [Note] Event Scheduler: Loaded 0 events
120922 14:05:27 [Note] /work/sources/percona-server/5.5.23-25.3/Percona-Server/sql/mysqld: ready for connections.
Version: '5.5.23-rel25.3-log'  socket: '/work/sources/percona-server/5.5.23-25.3/Percona-Server/mysql-test/var/tmp/mysqld.1.sock'  port: 13010  Percona Server with XtraDB (GPL), Release rel25.3, Revision 240

[root@ovaistariq-net mysql-test]# ../client/mysql -S ./var/tmp/mysqld.1.sock 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.23-rel25.3-log Percona Server with XtraDB (GPL), Release rel25.3, Revision 240

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select @@innodb_log_block_size;
+-------------------------+
| @@innodb_log_block_size |
+-------------------------+
|                    4096 |
+-------------------------+
1 row in set (0.00 sec)

mysql> Bye
[root@ovaistariq-net mysql-test]# ../client/mysqladmin -S ./var/tmp/mysqld.1.sock shutdown
120922 14:22:10 [Note] /work/sources/percona-server/5.5.23-25.3/Percona-Server/sql/mysqld: Normal shutdown

120922 14:22:10 [Note] Event Scheduler: Purging the queue. 0 events
120922 14:22:10  InnoDB: Starting shutdown...
120922 14:22:12  InnoDB: Shutdown completed; log sequence number 12603534
120922 14:22:12 [Note] /work/sources/percona-server/5.5.23-25.3/Percona-Server/sql/mysqld: Shutdown complete

Revision history for this message

Shahriyar Rzayev (rzayev-sehriyar) wrote on 2018-01-25:

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-1271

Report a bug

This report contains Public information

Everyone can see this information.

You are

Subscribing...

Edit bug mail

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.