sql_mode – variable.jp [データベース,パフォーマンス,運用]

234月 2018

MySQL5.7からMySQL8.0へのアップグレード

by rdbms

MySQL5.7からMySQL8.0へのアップグレード対応時のメモ

他のインスタンスも既に、アップグレードしましたが、UTF8MB4以外の文字コードを利用していたり、パーティションエンジンを利用していたので、
少々手間取ってしまいました。こちらの、アップグレード例はもともと、MySQL5.7で初期インストールして利用していたデータベースでUTF8MB4を利用して、
パーティションもInnoDB Native Partitionを利用していたので直ぐにアップグレードする事が出来ました。ただし、いくつか設定を変更しました。

アップグレード手順
1: mysqlsh (Ver 8.0.11) にてアップグレード事前チェックを行う
2: MySQL8.0のバリナリーダウンロード (Generic Tarを利用しました)
3: 既存のサービスを停止して、シンボリックリンクの張り直し
4: 必要なファイルやフォルダーをコピー(直ぐに、ロールバック出来るようにオリジナルフォルダーは残しています)
5: my.cnfに事前に必要な設定を入れて、MySQL8.0に無いパラメータを削除しておく
6: MySQLサービスを開始して、mysql_upgradeを実行
7: 必要に応じて、SchemaやTableのCOLLATEを変更
8: アプリケーションの接続やStrictモードを必要に応じて変更
9: 動作確認して終了

STEP1: mysqlshによるアップグレード対象インスタンスの互換性の確認
色々なアドバイスが出てくるので、必要に応じて適宜対応してください。(例：文字コード変換、パーティションストレージエンジンをInnoDBに変換等)


[root@AP01 bin]# ./mysqlsh root:password@localhost:3306 -e "util.checkForServerUpgrade();"
mysqlsh: [Warning] Using a password on the command line interface can be insecure.
The MySQL server at localhost:3306 will now be checked for compatibility issues for upgrade to MySQL 8.0...
MySQL version: 5.7.21-log - MySQL Community Server (GPL)

1) Usage of db objects with names conflicting with reserved keywords in 8.0
  No issues found

2) Usage of utf8mb3 charset
  No issues found

3) Usage of use ZEROFILL/display length type attributes
  Notice: The following table columns specify a ZEROFILL/display length attributes. Please be aware that they will be ignored in MySQL 8.0

  APP.T_Laravel.age - int(4)

4) Issues reported by 'check table x for upgrade' command
  No issues found

5) Table names in the mysql schema conflicting with new tables in 8.0
  No issues found

6) Usage of old temporal type
  No issues found

7) Foreign key constraint names longer than 64 characters
  No issues found

8) Usage of obsolete MAXDB sql_mode flag
  No issues found

9) Usage of obsolete sql_mode flags
  No issues found

10) Usage of partitioned tables in shared tablespaces
  No issues found

11) Usage of removed functions
  No issues found

No fatal errors were found that would prevent a MySQL 8 upgrade, but some potential issues were detected. Please ensure that the reported issues are not significant before upgrading.
[root@AP01 bin]#

■　8.0のバイナリーをダウンロードして展開

[root@AP01 local]# tar zxvf mysql-8.0.11-linux-glibc2.12-x86_64.tar.gz 
mysql-8.0.11-linux-glibc2.12-x86_64/bin/myisam_ftdump
mysql-8.0.11-linux-glibc2.12-x86_64/bin/myisamchk
mysql-8.0.11-linux-glibc2.12-x86_64/bin/myisamlog
mysql-8.0.11-linux-glibc2.12-x86_64/bin/myisampack
mysql-8.0.11-linux-glibc2.12-x86_64/bin/mysql
mysql-8.0.11-linux-glibc2.12-x86_64/bin/mysql_config_editor
mysql-8.0.11-linux-glibc2.12-x86_64/bin/mysql_secure_installation
mysql-8.0.11-linux-glibc2.12-x86_64/bin/mysql_ssl_rsa_setup
mysql-8.0.11-linux-glibc2.12-x86_64/bin/mysql_tzinfo_to_sql
mysql-8.0.11-linux-glibc2.12-x86_64/bin/mysql_upgrade
mysql-8.0.11-linux-glibc2.12-x86_64/bin/mysqladmin
mysql-8.0.11-linux-glibc2.12-x86_64/bin/mysqlbinlog
mysql-8.0.11-linux-glibc2.12-x86_64/bin/mysqlcheck
mysql-8.0.11-linux-glibc2.12-x86_64/bin/mysqldump
mysql-8.0.11-linux-glibc2.12-x86_64/bin/mysqlimport
mysql-8.0.11-linux-glibc2.12-x86_64/bin/mys

■既存のサービスを停止して、シンボリックリンクの張り直し

[root@AP01 local]# ls -l 
合計 588896
drwxr-xr-x.  2 root root          6  2月 19 12:04 bin
drwxr-xr-x.  2 root root          6  6月 10  2014 etc
drwxr-xr-x.  2 root root          6  6月 10  2014 games
drwxr-xr-x.  2 root root          6  6月 10  2014 include
drwxr-xr-x.  2 root root          6  6月 10  2014 lib
drwxr-xr-x.  2 root root          6  6月 10  2014 lib64
drwxr-xr-x.  2 root root          6  6月 10  2014 libexec
lrwxrwxrwx.  1 root root         35  2月 19 17:04 mysql -> mysql-5.7.21-linux-glibc2.12-x86_64
drwxr-xr-x. 12 root root       4096  2月 19 17:14 mysql-5.7.21-linux-glibc2.12-x86_64
drwxr-xr-x.  9 root root       4096  4月 23 14:05 mysql-8.0.11-linux-glibc2.12-x86_64
-rw-r--r--.  1 root root  603019898  4月  8 15:30 mysql-8.0.11-linux-glibc2.12-x86_64.tar.gz
drwxr-xr-x.  5 7161 31415        38  4月  9 11:49 mysql-shell-8.0.11-linux-glibc2.12-x86-64bit
lrwxrwxrwx.  1 root root         45  4月 23 14:04 mysqlsh -> mysql-shell-8.0.11-linux-glibc2.12-x86-64bit/
drwxr-xr-x.  2 root root          6  6月 10  2014 sbin
drwxr-xr-x.  5 root root         46 11月 21  2014 share
drwxr-xr-x.  2 root root          6 11月 15 20:51 src
[root@AP01 local]# /etc/init.d/mysql.server stop
Shutting down MySQL.. SUCCESS! 
[root@AP01 local]# rm mysql
rm: シンボリックリンク `mysql' を削除しますか? y
[root@AP01 local]# ln -s mysql-8.0.11-linux-glibc2.12-x86_64 mysql
[root@AP01 local]# ls -l
合計 588896
drwxr-xr-x.  2 root root          6  2月 19 12:04 bin
drwxr-xr-x.  2 root root          6  6月 10  2014 etc
drwxr-xr-x.  2 root root          6  6月 10  2014 games
drwxr-xr-x.  2 root root          6  6月 10  2014 include
drwxr-xr-x.  2 root root          6  6月 10  2014 lib
drwxr-xr-x.  2 root root          6  6月 10  2014 lib64
drwxr-xr-x.  2 root root          6  6月 10  2014 libexec
lrwxrwxrwx.  1 root root         35  4月 23 14:05 mysql -> mysql-8.0.11-linux-glibc2.12-x86_64
drwxr-xr-x. 12 root root       4096  2月 19 17:14 mysql-5.7.21-linux-glibc2.12-x86_64
drwxr-xr-x.  9 root root       4096  4月 23 14:05 mysql-8.0.11-linux-glibc2.12-x86_64
-rw-r--r--.  1 root root  603019898  4月  8 15:30 mysql-8.0.11-linux-glibc2.12-x86_64.tar.gz
drwxr-xr-x.  5 7161 31415        38  4月  9 11:49 mysql-shell-8.0.11-linux-glibc2.12-x86-64bit
lrwxrwxrwx.  1 root root         45  4月 23 14:04 mysqlsh -> mysql-shell-8.0.11-linux-glibc2.12-x86-64bit/
drwxr-xr-x.  2 root root          6  6月 10  2014 sbin
drwxr-xr-x.  5 root root         46 11月 21  2014 share
drwxr-xr-x.  2 root root          6 11月 15 20:51 src
[root@AP01 local]#

■必要なファイルやフォルダーをコピー

[root@AP01 mysql-5.7.21-linux-glibc2.12-x86_64]# ls -l
合計 44
-rw-r--r--.  1  7161 31415 17987 12月 28 12:46 COPYING
-rw-r--r--.  1  7161 31415  2478 12月 28 12:46 README
drwxr-xr-x.  2 root  root   4096  2月 19 17:03 bin
drwxr-x---.  6 mysql mysql  4096  4月 23 14:06 data
drwxr-xr-x.  2 root  root     52  2月 19 17:03 docs
drwxr-xr-x.  3 root  root   4096  2月 19 17:03 include
drwxr-xr-x.  5 root  root   4096  2月 19 17:03 lib
drwxr-xr-x.  2 mysql mysql    60  2月 19 17:14 logs
drwxr-xr-x.  4 root  root     28  2月 19 17:03 man
drwxr-x---.  2 mysql mysql     6  2月 19 17:05 mysql-files
drwxr-xr-x. 28 root  root   4096  2月 19 17:03 share
drwxr-xr-x.  2 root  root     86  2月 19 17:03 support-files
[root@AP01 mysql-5.7.21-linux-glibc2.12-x86_64]# cp -rp data /usr/local/mysql
[root@AP01 mysql-5.7.21-linux-glibc2.12-x86_64]# cp -rp mysql-files /usr/local/mysql
[root@AP01 mysql-5.7.21-linux-glibc2.12-x86_64]#

■ my.cnfに以下の設定を入れておく。

default_authentication_plugin=mysql_native_password

また、mysql8.0に無いパラメータは、削除しておかないとエラーになるので削除しておきましょう。
例）2018-04-23T05:39:57.859413Z 0 [ERROR] [MY-011071] [Server] unknown variable ‘query_cache_type=0’

パラメータのチェックに関しては、こちらのページが便利です。
https://tmtm.github.io/mysql-params/?vers=5.7.22,8.0.11&diff=true

■ MySQLの起動とUpgrade


[root@AP01 data]# /etc/init.d/mysql.server start
Starting MySQL... SUCCESS! 
[root@AP01 data]#

[root@AP01 mysql]# mysql_upgrade -u root -p
Enter password: 
Checking if update is needed.
Checking server version.
Running queries to upgrade MySQL server.
Upgrading system table data.
Checking system database.
mysql.columns_priv                                 OK
mysql.component                                    OK
mysql.db                                           OK
mysql.default_roles                                OK
mysql.engine_cost                                  OK
mysql.func                                         OK
mysql.general_log                                  OK
mysql.global_grants                                OK
mysql.gtid_executed                                OK
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK
mysql.innodb_index_stats                           OK
mysql.innodb_table_stats                           OK
mysql.ndb_binlog_index                             OK
mysql.password_history                             OK
mysql.plugin                                       OK
mysql.procs_priv                                   OK
mysql.proxies_priv                                 OK
mysql.role_edges                                   OK
mysql.server_cost                                  OK
mysql.servers                                      OK
mysql.slave_master_info                            OK
mysql.slave_relay_log_info                         OK
mysql.slave_worker_info                            OK
mysql.slow_log                                     OK
mysql.tables_priv                                  OK
mysql.time_zone                                    OK
mysql.time_zone_leap_second                        OK
mysql.time_zone_name                               OK
mysql.time_zone_transition                         OK
mysql.time_zone_transition_type                    OK
mysql.user                                         OK
Found outdated sys schema version 1.5.1.
Upgrading the sys schema.
Checking databases.
APP.T_ChangeLog                                    OK
APP.T_Laravel                                      OK
APP.T_business                                     OK
APP.T_databases                                    OK
APP.T_others                                       OK
APP.T_scripts                                      OK
APP.cars                                           OK
APP.migrations                                     OK
APP.password_resets                                OK
APP.users                                          OK
sys.sys_config                                     OK
Upgrade process completed successfully.
Checking if update is needed.
[root@AP01 mysql]#

■　ファイルが自動的にアップグレードされ、メタデータ関連ファイルが無くなっている事が確認できる。

[root@AP01 mysql]# ls -l
合計 10732
-rw-r-----. 1 mysql mysql       0  2月 19 17:27 columns_priv.MYD
-rw-r-----. 1 mysql mysql    4096  2月 19 17:27 columns_priv.MYI
-rw-r-----. 1 mysql mysql    7763  4月 23 14:36 columns_priv_83.sdi
-rw-r-----. 1 mysql mysql    1464  2月 19 17:28 db.MYD
-rw-r-----. 1 mysql mysql    5120  2月 19 17:28 db.MYI
-rw-r-----. 1 mysql mysql   19285  4月 23 14:36 db_84.sdi
-rw-r-----. 1 mysql mysql  114688  4月 23 14:36 engine_cost.ibd
-rw-r-----. 1 mysql mysql       0  2月 19 17:27 func.MYD
-rw-r-----. 1 mysql mysql    1024  2月 19 17:27 func.MYI
-rw-r-----. 1 mysql mysql    4819  4月 23 14:36 func_87.sdi
-rw-r-----. 1 mysql mysql      35  4月 23 14:31 general_log.CSM
-rw-r-----. 1 mysql mysql       0  2月 19 17:27 general_log.CSV
-rw-r-----. 1 mysql mysql    5520  4月 23 14:36 general_log_88.sdi
-rw-r-----. 1 mysql mysql  114688  4月 23 14:36 gtid_executed.ibd
-rw-r-----. 1 mysql mysql  131072  4月 23 14:36 help_category.ibd
-rw-r-----. 1 mysql mysql  262144  4月 23 14:36 help_keyword.ibd
-rw-r-----. 1 mysql mysql  163840  4月 23 14:36 help_relation.ibd
-rw-r-----. 1 mysql mysql 8388608  4月 23 14:36 help_topic.ibd
-rw-r-----. 1 mysql mysql  114688  4月 23 14:36 innodb_index_stats_backup57.ibd
-rw-r-----. 1 mysql mysql  114688  4月 23 14:36 innodb_table_stats_backup57.ibd
-rw-r-----. 1 mysql mysql       0  2月 19 17:05 ndb_binlog_index.MYD
-rw-r-----. 1 mysql mysql    1024  2月 19 17:05 ndb_binlog_index.MYI
-rw-r-----. 1 mysql mysql   10729  4月 23 14:36 ndb_binlog_index_96.sdi
-rw-r-----. 1 mysql mysql  114688  4月 23 14:36 plugin.ibd
-rw-r-----. 1 mysql mysql       0  2月 19 17:27 procs_priv.MYD
-rw-r-----. 1 mysql mysql    4096  2月 19 17:27 procs_priv.MYI
-rw-r-----. 1 mysql mysql    8960  4月 23 14:36 procs_priv_98.sdi
-rw-r-----. 1 mysql mysql     837  2月 19 17:05 proxies_priv.MYD
-rw-r-----. 1 mysql mysql    9216  2月 19 17:05 proxies_priv.MYI
-rw-r-----. 1 mysql mysql    7813  4月 23 14:36 proxies_priv_99.sdi
-rw-r-----. 1 mysql mysql  114688  4月 23 14:36 server_cost.ibd
-rw-r-----. 1 mysql mysql  114688  4月 23 14:36 servers.ibd
-rw-r-----. 1 mysql mysql  114688  4月 23 14:36 slave_master_info.ibd
-rw-r-----. 1 mysql mysql  114688  4月 23 14:36 slave_relay_log_info.ibd
-rw-r-----. 1 mysql mysql  114688  4月 23 14:36 slave_worker_info.ibd
-rw-r-----. 1 mysql mysql      35  4月 23 14:32 slow_log.CSM
-rw-r-----. 1 mysql mysql  104677  4月 23 14:31 slow_log.CSV
-rw-r-----. 1 mysql mysql   11741  4月 23 14:36 slow_log_105.sdi
-rw-r-----. 1 mysql mysql    1894  2月 19 17:27 tables_priv.MYD
-rw-r-----. 1 mysql mysql    9216  2月 19 17:50 tables_priv.MYI
-rw-r-----. 1 mysql mysql    9379  4月 23 14:36 tables_priv_106.sdi
-rw-r-----. 1 mysql mysql  114688  4月 23 14:36 time_zone.ibd
-rw-r-----. 1 mysql mysql  114688  4月 23 14:36 time_zone_leap_second.ibd
-rw-r-----. 1 mysql mysql  114688  4月 23 14:36 time_zone_name.ibd
-rw-r-----. 1 mysql mysql  114688  4月 23 14:36 time_zone_transition.ibd
-rw-r-----. 1 mysql mysql  114688  4月 23 14:36 time_zone_transition_type.ibd
-rw-r-----. 1 mysql mysql     504  2月 19 17:27 user.MYD
-rw-r-----. 1 mysql mysql    4096  2月 19 17:50 user.MYI
-rw-r-----. 1 mysql mysql   36754  4月 23 14:36 user_112.sdi
[root@AP01 mysql]# 


[root@AP01 mysql]# ls -l
合計 444
-rw-r-----. 1 mysql mysql     35  4月 23 14:43 general_log.CSM
-rw-r-----. 1 mysql mysql      0  4月 23 14:43 general_log.CSV
-rw-r-----. 1 mysql mysql   5520  4月 23 14:43 general_log_365.sdi
-rw-r-----. 1 mysql mysql 114688  4月 23 14:36 innodb_index_stats_backup57.ibd
-rw-r-----. 1 mysql mysql 114688  4月 23 14:36 innodb_table_stats_backup57.ibd
-rw-r-----. 1 mysql mysql     35  4月 23 14:43 slow_log.CSM
-rw-r-----. 1 mysql mysql 109876  4月 23 14:43 slow_log.CSV
-rw-r-----. 1 mysql mysql  11741  4月 23 14:43 slow_log_367.sdi
[root@AP01 mysql]#

必要に応じて、既存データベースやテーブルのCOLLATION(照合順序)を変更しておく

root@localhost [(none)]> select @@version;
+-----------+
| @@version |
+-----------+
| 8.0.11    |
+-----------+
1 row in set (0.00 sec)

root@localhost [APP]> show create database APP\G
*************************** 1. row ***************************
       Database: APP
Create Database: CREATE DATABASE `APP` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci */
1 row in set (0.00 sec)

root@localhost [APP]> alter schema APP DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
Query OK, 1 row affected (0.08 sec)

root@localhost [APP]> show create database APP\G
*************************** 1. row ***************************
       Database: APP
Create Database: CREATE DATABASE `APP` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */
1 row in set (0.00 sec)

root@localhost [APP]> show create table T_ChangeLog\G
*************************** 1. row ***************************
       Table: T_ChangeLog
Create Table: CREATE TABLE `T_ChangeLog` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(100) COLLATE utf8mb4_general_ci NOT NULL,
  `comment` varchar(2048) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.00 sec)

root@localhost [APP]> alter table `T_ChangeLog` convert to character set utf8mb4;
Query OK, 6 rows affected (0.11 sec)
Records: 6  Duplicates: 0  Warnings: 0

root@localhost [APP]> show create table T_ChangeLog\G
*************************** 1. row ***************************
       Table: T_ChangeLog
Create Table: CREATE TABLE `T_ChangeLog` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(100) NOT NULL,
  `comment` varchar(2048) DEFAULT NULL,
  `update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

root@localhost [APP]>

■アプリケーションの接続や必要に応じてStrictモードを変更
メモ：　COMPOSERで最新版のフレームワークにアップグレード後にTRUEに戻しました。
（本番環境では、意図しないエラーが発生することもあるので注意してTRUE,FALSEの設定を行ってください。)

            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_0900_ai_ci',
            'prefix' => '',
            /* 'strict' => true, */
            'strict' => false,

NO_AUTO_CREATE_USER等の、SQLモードが削除されているのでフレームワークによっては、StrictモードがTrueのままだと、
アプリケーションがエラーになる場合があります。

参照：アカウント管理に関連する次の機能は削除されました。
GRANTを使用してユーザーを作成する代わりに、CREATE USERを使用するようになっています。 “NO_AUTO_CREATE_USER” SQLモードはGRANT文にとって重要ではない為、削除されました。
https://dev.mysql.com/doc/refman/8.0/en/mysql-nutshell.html

その他、参考：
https://dev.mysql.com/doc/refman/8.0/en/upgrading-strategies.html

257月 2009

SQL_MODE (IGNORE_SPACE)

by rdbms

IGNORE_SPACEを有効にしているとき、パーサはファンクション名と後続の括弧間に余白が存在
してはいけないという要求を緩和します。このことで、ファンクション呼び出しの記述がより自由に行える
ようになります。例えば、次のどちらのファンクション呼び出しも有効です。

SELECT COUNT(*) FROM mytable;
SELECT COUNT (*) FROM mytable;

mysql> SELECT NOW (); ERROR 1305 (42000): FUNCTION NOW does not exist mysql> SELECT NOW(); +---------------------+ | NOW() | +---------------------+ | 2009-07-25 00:03:31 | +---------------------+ 1 row in set (0.00 sec)


mysql> SELECT NOW ();

ERROR 1305 (42000): FUNCTION NOW does not exist

mysql> SET sql_mode = 'IGNORE_SPACE';

Query OK, 0 rows affected (0.00 sec)
mysql> SELECT NOW ();

+---------------------+

| NOW ()              |

+---------------------+

| 2009-07-25 00:04:13 |

+---------------------+

1 row in set (0.00 sec)
mysql> SELECT NOW();

+---------------------+

| NOW()               |

+---------------------+

| 2009-07-25 00:04:19 |

+---------------------+

1 row in set (0.00 sec)
mysql> select @@sql_mode;

+--------------+

| @@sql_mode   |

+--------------+

| IGNORE_SPACE |

+--------------+

1 row in set (0.00 sec)

mysql>
sql_mode_space

参考サイト
—————————————————————-

8.2.4. 構文解析と解像度のファンクション名

4.2.6. SQL モード

177月 2009

SQL_MODE ANSI_QUOTES

by rdbms

SQL_MODEを変更するとMYSQLの動作は大きく変わってくる。

ANSI_QUOTES

引用文字 ‘`’ のように、‘”’ を識別子として扱う。文字列の引用文字ではない。このモードを有効にした場合には、
‘`’ を識別子として使用できる。ANSI_QUOTES を有効にした場合には、識別子として解釈されるため、
リテラル文字列の引用には二重引用符を使用できなくなる。

mysql> select comment from string_test limit 1; +------------+ | comment | +------------+ | THIS IS LO | +------------+ 1 row in set (0.00 sec)


mysql> select @@SQL_MODE;

+---------------------+

| @@SQL_MODE          |

+---------------------+

| NO_AUTO_CREATE_USER |

+---------------------+

1 row in set (0.00 sec)
mysql> select "comment" from string_test limit 1;

+---------+

| comment |

+---------+

| comment |

+---------+

1 row in set (0.00 sec)
mysql> set SQL_MODE ='ANSI_QUOTES';

Query OK, 0 rows affected (0.00 sec)
mysql> select @@SQL_MODE;

+-------------+

| @@SQL_MODE  |

+-------------+

| ANSI_QUOTES |

+-------------+

1 row in set (0.00 sec)
mysql> select "comment" from string_test limit 1;

+------------+

| comment    |

+------------+

| THIS IS LO |

+------------+

1 row in set (0.00 sec)

mysql>

sql_mode_ansi

————————————————————————-
mysql> SELECT "Let's see whether this works."; +-------------------------------+ | Let's see whether this works. | +-------------------------------+ | Let's see whether this works. | +-------------------------------+ 1 row in set (0.00 sec)


mysql> SET sql_mode = 'ANSI_QUOTES';

Query OK, 0 rows affected (0.00 sec)
mysql> SELECT "Let's see whether this works.";

ERROR 1054 (42S22): Unknown column 'Let's see whether this works.

mysql> SELECT `Let's see whether this works.`;

ERROR 1054 (42S22): Unknown column 'Let's see whether this works.

mysql> SELECT 'Lets see whether this works.';

+------------------------------+

| Lets see whether this works. |

+------------------------------+

| Lets see whether this works. |

+------------------------------+

1 row in set (0.00 sec)

mysql> SELECT 'Let\'s see whether this works.'; +-------------------------------+ | Let's see whether this works. | +-------------------------------+ | Let's see whether this works. | +-------------------------------+ 1 row in set (0.00 sec)

sql_mode
————————————————————————-

4.2.6. SQL モード

127月 2009

sql_modeとInsertの結果の違い

by rdbms

複数行をINSERTした時に列の長さを超えたデータと正常なデータをINSERTした
場合の結果は、SQL_MODEによって違う。

以下の条件によっても結果は違う事に注意

MyISAM
InnoDB
STRICT_ALL_TABLES
STRICT_TRANS_TABLES

mysql> SELECT @@session.sql_mode; +---------------------+ | @@session.sql_mode | +---------------------+ | NO_AUTO_CREATE_USER | +---------------------+ 1 row in set (0.01 sec)

mysql> CREATE TABLE string_test ( -> comment varchar(10) -> ); Query OK, 0 rows affected (0.01 sec)


mysql> desc string_test;

+---------+-------------+------+-----+---------+-------+

| Field   | Type        | Null | Key | Default | Extra |

+---------+-------------+------+-----+---------+-------+

| comment | varchar(10) | YES  |     | NULL    |       |

+---------+-------------+------+-----+---------+-------+

1 row in set (0.00 sec)
mysql> insert into string_test values('THIS IS LONG VALUE FOR VARCHAR(10)'), ('THIS IS OK');

Query OK, 2 rows affected, 1 warning (0.00 sec)

Records: 2  Duplicates: 0  Warnings: 1
mysql> show warnings;

+---------+------+----------------------------------------------+

| Level   | Code | Message                                      |

+---------+------+----------------------------------------------+

| Warning | 1265 | Data truncated for column 'comment' at row 1 |

+---------+------+----------------------------------------------+

1 row in set (0.00 sec)
mysql> select * from string_test;

+------------+

| comment    |

+------------+

| THIS IS LO |

| THIS IS OK |

+------------+

2 rows in set (0.00 sec)

mysql>
string_test

mysql> SET @@session.sql_mode = 'STRICT_ALL_TABLES'; Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @@session.sql_mode; +--------------------+ | @@session.sql_mode | +--------------------+ | STRICT_ALL_TABLES | +--------------------+ 1 row in set (0.00 sec)
mysql>

mysql> insert into string_test values('THIS IS LONG VALUE FOR VARCHAR(10)'), ('THIS IS OK'); ERROR 1406 (22001): Data too long for column 'comment' at row 1 mysql> select * from string_test; +------------+ | comment | +------------+ | THIS IS LO | | THIS IS OK | +------------+ 2 rows in set (0.00 sec)

mysql>

strict

047月 2009

DATE型について確認（２桁の年）

by rdbms

DATE型に以下のように、２文字だけしか年を指定しないでINSERTした場合
MYSQLは自動的に

1970-01-01 ~ 2069-12-31

の間の日付に変換する。
４桁で年を指定すればそのままINSERTされます。

以下検証

mysql> CREATE TABLE `T3` ( -> `BIRTH` DATE NULL -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8; Query OK, 0 rows affected (0.18 sec)


mysql> desc T3;

+-------+------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+------+------+-----+---------+-------+

| BIRTH | date | YES  |     | NULL    |       |

+-------+------+------+-----+---------+-------+

1 row in set (0.00 sec)
mysql> INSERT INTO T3 VALUES ('10-02-08');

Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO T3 VALUES ('69-12-31');

Query OK, 1 row affected (0.02 sec)
mysql> INSERT INTO T3 VALUES ('70-01-01');

Query OK, 1 row affected (0.01 sec)
mysql> select * from  T3;

+------------+

| BIRTH      |

+------------+

| 2010-02-08 |

| 2069-12-31 |

| 1970-01-01 |

+------------+

3 rows in set (0.00 sec)
mysql> INSERT INTO T3 VALUES ('1910-01-01');

Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO T3 VALUES ('2070-01-01');

Query OK, 1 row affected (0.01 sec)
mysql> select * from  T3;

+------------+

| BIRTH      |

+------------+

| 2010-02-08 |

| 2069-12-31 |

| 1970-01-01 |

| 1910-01-01 |

| 2070-01-01 |

+------------+

5 rows in set (0.00 sec)
mysql>

date

======= SQLのモードによっても結果が異なる事があります。 ========
mysql> INSERT INTO T3 VALUES ('12:00:00'); Query OK, 1 row affected (0.01 sec)


mysql> select * from  T3;

+------------+

| BIRTH      |

+------------+

| 2010-02-08 |

| 2069-12-31 |

| 1970-01-01 |

| 1910-01-01 |

| 2070-01-01 |

| 2012-00-00 |

+------------+

6 rows in set (0.01 sec)
mysql> set sql_mode ='NO_ZERO_IN_DATE';

Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO T3 VALUES ('12:00:00');

Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> show warnings;

+---------+------+--------------------------------------------+

| Level   | Code | Message                                    |

+---------+------+--------------------------------------------+

| Warning | 1265 | Data truncated for column 'BIRTH' at row 1 |

+---------+------+--------------------------------------------+

1 row in set (0.00 sec)
mysql> select * from  T3;

+------------+

| BIRTH      |

+------------+

| 2010-02-08 |

| 2069-12-31 |

| 1970-01-01 |

| 1910-01-01 |

| 2070-01-01 |

| 2012-00-00 |

| 0000-00-00 |

+------------+

7 rows in set (0.00 sec)
mysql> set sql_mode ='';

Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO T3 VALUES ('12:00:00');

Query OK, 1 row affected (0.02 sec)

mysql>

074月 2009

MYSQL初期ｱｶｳﾝﾄとｾｷｭﾘﾃｨ

by rdbms

MYSQLｲﾝｽﾄｰﾙ直後のｱｶｳﾝﾄ初期設定

基本的ですが、MYSQLｻｰﾋﾞｽはrootでは動かさない。
MYSQLｻｰﾊﾞｰ用専用ｱｶｳﾝﾄの作成

[mysql@colinux local]$ /etc/init.d/mysql.server start
Starting MySQL… SUCCESS!
[mysql@colinux local]$ ps -ef | grep mysqlroot 2649 2623 0 12:17 tty1 00:00:00 su - mysql mysql 2658 2649 0 12:17 tty1 00:00:00 -bash mysql 2693 1 0 12:18 tty1 00:00:00 /bin/sh ./bin/mysqld_safe --datadir=/usr/local/mysql/data --pid-file=/usr/local/mysql/data/colinux.pid mysql 2821 2693 9 12:18 tty1 00:00:02 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --log-error=/usr/local/mysql/data/colinux.err --pid-file=/usr/local/mysql/data/colinux.pid --socket=/tmp/mysql.sock --port=3306 mysql 2836 2658 0 12:18 tty1 00:00:00 ps -ef mysql 2837 2658 0 12:18 tty1 00:00:00 grep mysql
[mysql@colinux local]$

—————– my.cnf ———————
# The MySQL server
[mysqld]
port = 3306
socket = /tmp/mysql.sock
user = mysql
skip-locking
key_buffer = 16K

mysqlｲﾝｽﾄｰﾙ初期状態でまずはﾊﾟｽﾜｰﾄﾞを設定して不要なｱｶｳﾝﾄを削除

[mysql@colinux local]$mysql -u root mysql -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.1.30-log MySQL Community Server (GPL)


Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> update user set password = PASSWORD('password2009')

    -> where user ='root';

Query OK, 3 rows affected (0.00 sec)

Rows matched: 3  Changed: 3  Warnings: 0
mysql> delete from user where user = 'root' and host ='%';

Query OK, 0 rows affected (0.01 sec)
mysql> delete from user where user ='';

Query OK, 0 rows affected (0.00 sec)
mysql> delete from db where user ='';

Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;

Query OK, 0 rows affected (0.01 sec)

mysql>

init_secure

ﾊﾟｽﾜｰﾄﾞの付いていないｱｶｳﾝﾄがないかﾀﾞﾌﾞﾙﾁｪｯｸ

mysql> select user,host,password from user -> where user = '' or password = ''; Empty set (0.00 sec)
mysql>

今後の為に、ﾊﾟｽﾜｰﾄﾞの付いてないｱｶｳﾝﾄが作成されないように制限する

mysql> select @@global.sql_mode; +-------------------+ | @@global.sql_mode | +-------------------+ | | +-------------------+ 1 row in set (0.00 sec)


mysql> set global sql_mode = 'NO_AUTO_CREATE_USER';

Query OK, 0 rows affected (0.00 sec)
mysql> select @@global.sql_mode;

+---------------------+

| @@global.sql_mode   |

+---------------------+

| NO_AUTO_CREATE_USER |

+---------------------+

1 row in set (0.00 sec)
mysql>

no_auto_create_user

[mysql@colinux local]$ mysql -u root mysql -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.30-log MySQL Community Server (GPL)

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.

mysql> select @@global.sql_mode; +---------------------+ | @@global.sql_mode | +---------------------+ | NO_AUTO_CREATE_USER | +---------------------+ 1 row in set (0.00 sec)

mysql> GRANT select,insert,update,delete on DB001.* to 'no_password_user'; ERROR 1133 (42000): Can't find any matching row in the user table mysql>

上記の”NO_AUTO_CREATE_USER”ﾓｰﾄﾞはMYSQLｻｰﾊﾞｰを再起動したら消えます。

mysql> select @@global.sql_mode; +-------------------+ | @@global.sql_mode | +-------------------+ | | +-------------------+ 1 row in set (0.01 sec)

mysql>

そこでmy.cnfに”sql_mode”の設定を行い再起動しても設定が消えないようにします。

—————————————————————–
抜粋
—————————————————————–
[mysqld]
port = 3306
socket = /tmp/mysql.sock
user = mysql
skip-locking
key_buffer = 16K
max_allowed_packet = 1M
table_cache = 4
sort_buffer_size = 64K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
net_buffer_length = 2K
thread_stack = 64K
default-character-set=utf8
sql_mode =”NO_AUTO_CREATE_USER”
—————————————————————–

上記の設定を行い、mysqlを再起動すればﾊﾟｽﾜｰﾄﾞの無いﾕｰｻﾞｰは
作成出来なくなります。後はﾙｰﾙとして周知すればOK。

sql_mode

Tag Archives: Sql_mode

MySQL5.7からMySQL8.0へのアップグレード

SQL_MODE (IGNORE_SPACE)

SQL_MODE ANSI_QUOTES

sql_modeとInsertの結果の違い

DATE型について確認（２桁の年）

MYSQL初期ｱｶｳﾝﾄとｾｷｭﾘﾃｨ