MYSQL5.6へアップグレード後のWarningを無くす為にmysqlのオプションファイルを編集

InnoDBのメモリー処理関連のWarning

2013-05-05 16:28:19 b784c8e0 InnoDB: Warning: Using innodb_additional_mem_pool_size is DEPRECATED. 
This option may be removed in future releases, together with the option innodb_use_sys_malloc and with the InnoDB's internal memory allocator.
[root@HOME001 data]# mysql -u root -p -e "show variables like 'innodb_use_sys_malloc'"
Enter password:
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_use_sys_malloc | ON    |
+-----------------------+-------+
[root@HOME001 data]#

以下の値をOptionファイルに追加して対応。
innodb_use_sys_malloc

innodb_buffer_pool_size = 32M
#innodb_additional_mem_pool_size = 2M
innodb_use_sys_malloc=1

http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html#sysvar_innodb_use_sys_malloc

http://docs.oracle.com/cd/E17952_01/refman-5.5-en/innodb-performance-use_sys_malloc.html

You can control whether InnoDB uses its own memory allocator or an allocator of the operating system,
by setting the value of the system configuration parameter innodb_use_sys_malloc in the MySQL option file (my.cnf or my.ini).
If set to ON or 1 (the default), InnoDB uses the malloc and free functions of the underlying system rather than manage memory pools itself.
This parameter is not dynamic, and takes effect only when the system is started.
To continue to use the InnoDB memory allocator, set innodb_use_sys_malloc to 0.

InnoDBのメモリーマネージャーではなく、OSのメモリ割り当てを任せるという事になるが、
どちらが良いかはタイミング見て確認。

TIMESTAMP型を指定した時の処理の変更点

2013-05-05 16:43:37 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. 
Please use --explicit_defaults_for_timestamp server option (see documentation for more details).

設定を変えなければ、TIMESTAMPを指定したときのテーブル属性に変更無し。

[root@HOME001 data]# mysql -u root -p -e "show variables like 'explicit_defaults_for_timestamp'"
Enter password:
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| explicit_defaults_for_timestamp | OFF   |
+---------------------------------+-------+
[root@HOME001 data]# mysql -u root -p -e "create table test.timestamp_chk (chk_explicit_defaults_for_timestamp timestamp) ENGINE=InnoDB;"
Enter password:
[root@HOME001 data]# mysql -u root -p -e "show create table test.timestamp_chk\G"
Enter password:
*************************** 1. row ***************************
       Table: timestamp_chk
Create Table: CREATE TABLE `timestamp_chk` (
  `chk_explicit_defaults_for_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1
[root@HOME001 data]#

この値をmy.cnfに追加してWarning対応。
explicit_defaults_for_timestamp=true

TIMESTAMP型を利用した時にテーブル属性が変わった事を確認

[root@HOME001 data]# vi /etc/my.cnf
[root@HOME001 data]# /etc/init.d/mysql restart
Shutting down MySQL..                                      [  OK  ]
Starting MySQL.                                            [  OK  ]
[root@HOME001 data]# mysql -u root -p -e "show variables like 'explicit_defaults_for_timestamp'"
Enter password:
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| explicit_defaults_for_timestamp | ON    |
+---------------------------------+-------+
[root@HOME001 data]#


[root@HOME001 data]# mysql -u root -p -e "create table test.timestamp_chk2 (chk_explicit_defaults_for_timestamp timestamp) ENGINE=InnoDB;"
Enter password:
[root@HOME001 data]# mysql -u root -p -e "show create table test.timestamp_chk2\G"
Enter password:
*************************** 1. row ***************************
       Table: timestamp_chk2
Create Table: CREATE TABLE `timestamp_chk2` (
  `chk_explicit_defaults_for_timestamp` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
[root@HOME001 data]#

新旧を並べるとこんな感じになります。

[root@HOME001 data]# mysql -u root -p -e "show create table test.timestamp_chk\G"
Enter password:
*************************** 1. row ***************************
       Table: timestamp_chk
Create Table: CREATE TABLE `timestamp_chk` (
  `chk_explicit_defaults_for_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1
[root@HOME001 data]# mysql -u root -p -e "show create table test.timestamp_chk2\G"
Enter password:
*************************** 1. row ***************************
       Table: timestamp_chk2
Create Table: CREATE TABLE `timestamp_chk2` (
  `chk_explicit_defaults_for_timestamp` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
[root@HOME001 data]#

MYSQLを再起動しても、Warningが無い事を確認してとりあえず確認終了。


130505 16:55:24 mysqld_safe mysqld from pid file /usr/local/mysql/data/HOME001.localdomain.pid ended
130505 16:55:25 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data
2013-05-05 16:55:25 7031 [Note] Plugin ‘FEDERATED’ is disabled.
2013-05-05 16:55:25 7031 [Note] InnoDB: The InnoDB memory heap is disabled
2013-05-05 16:55:25 7031 [Note] InnoDB: Mutexes and rw_locks use InnoDB’s own implementation
2013-05-05 16:55:25 7031 [Note] InnoDB: Compressed tables use zlib 1.2.3
2013-05-05 16:55:25 7031 [Note] InnoDB: Using Linux native AIO
2013-05-05 16:55:25 7031 [Note] InnoDB: Not using CPU crc32 instructions
2013-05-05 16:55:25 7031 [Note] InnoDB: Initializing buffer pool, size = 32.0M
2013-05-05 16:55:25 7031 [Note] InnoDB: Completed initialization of buffer pool
2013-05-05 16:55:25 7031 [Note] InnoDB: Highest supported file format is Barracuda.
2013-05-05 16:55:25 7031 [Note] InnoDB: 128 rollback segment(s) are active.
2013-05-05 16:55:25 7031 [Note] InnoDB: Waiting for purge to start
2013-05-05 16:55:26 7031 [Note] InnoDB: 5.6.11 started; log sequence number 1747262
2013-05-05 16:55:26 7031 [Note] Semi-sync replication initialized for transactions.
2013-05-05 16:55:26 7031 [Note] Semi-sync replication enabled on the master.
2013-05-05 16:55:26 7031 [Note] Server hostname (bind-address): ‘*’; port: 3306
2013-05-05 16:55:26 7031 [Note] IPv6 is available.
2013-05-05 16:55:26 7031 [Note] – ‘::’ resolves to ‘::’;
2013-05-05 16:55:26 7031 [Note] Server socket created on IP: ‘::’.
2013-05-05 16:55:26 7031 [Note] Event Scheduler: Loaded 0 events
2013-05-05 16:55:26 7031 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: ‘5.6.11-log’ socket: ‘/tmp/mysql.sock’ port: 3306 MySQL Community Server (GPL)
2013-05-05 16:56:21 7031 [Note] Start semi-sync binlog_dump to slave (server_id: 2), pos(mysql-bin.000027, 120)


http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_explicit_defaults_for_timestamp