MariaDB(MySQL)をアップグレードしたらINSERTに失敗。[SQL_MODE]

MariaDB(MySQL)をアップグレードしたらINSERTに失敗。[SQL_MODE] サーバー

MariaDB(MySQL)をアップグレードしたら昔作ったプログラムでDBへのINSERTが失敗する。

そんなときは現在のSQL_MODEをチェックしましょう。

MariaDBのデフォルトのSQL_MODEが変更になっている可能性があります。

※動作確認環境
CentOS 7.6
MariaDB 10.3.12

SQL_MODEの確認方法

MariaDBにログインします。

# mysql -u root -p

■グローバル設定のSQL_MODEの確認

SELECT @@GLOBAL.sql_mode; で確認できます。

MariaDB [(none)]> SELECT @@GLOBAL.sql_mode;
+-------------------------------------------------------------------------------------------+
| @@GLOBAL.sql_mode                                                                         |
+-------------------------------------------------------------------------------------------+
| STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------+
1 row in set (0.001 sec)

■セッションごとのSQL_MODEの確認

SELECT @@session.sql_mode; で確認できます。

MariaDB [(none)]> SELECT @@session.sql_mode;
+-------------------------------------------------------------------------------------------+
| @@session.sql_mode                                                                        |
+-------------------------------------------------------------------------------------------+
| STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------+
1 row in set (0.001 sec)

各バージョンごとのデフォルト値調査

※全てMariaDB公式サイトのリポジトリを利用してyumにてMariaDBのインストール後にmysql_secure_installationを実行しただけの状態で確認しました。

■MariaDB Repositories
https://downloads.mariadb.org/mariadb/repositories/

MariaDB 5.5系

SQL_MODEの初期設定値は「無し」です。
※MariaDB 5.5.63での調査結果です。

# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 10
Server version: 5.5.63-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]> SELECT @@GLOBAL.sql_mode;
+-------------------+
| @@GLOBAL.sql_mode |
+-------------------+
|                   |
+-------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> SELECT @@session.sql_mode;
+--------------------+
| @@session.sql_mode |
+--------------------+
|                    |
+--------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> quit;
Bye

MariaDB 10.0系

SQL_MODEの初期設定値は「無し」です。
※MariaDB 10.0.38での調査結果です。

# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 10.0.38-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]> SELECT @@GLOBAL.sql_mode;
+-------------------+
| @@GLOBAL.sql_mode |
+-------------------+
|                   |
+-------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> SELECT @@session.sql_mode;
+--------------------+
| @@session.sql_mode |
+--------------------+
|                    |
+--------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> quit;
Bye

MariaDB 10.1系

SQL_MODEの初期設定値は以下の2つです。

  • NO_AUTO_CREATE_USER
  • NO_ENGINE_SUBSTITUTION

※MariaDB 10.1.37での調査結果です。

# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 10
Server version: 10.1.37-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]> SELECT @@GLOBAL.sql_mode;
+--------------------------------------------+
| @@GLOBAL.sql_mode                          |
+--------------------------------------------+
| NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> SELECT @@session.sql_mode;
+--------------------------------------------+
| @@session.sql_mode                         |
+--------------------------------------------+
| NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> quit;
Bye

MariaDB 10.2系

SQL_MODEの初期設定値は以下の4つです。

  • STRICT_TRANS_TABLES
  • ERROR_FOR_DIVISION_BY_ZERO
  • NO_AUTO_CREATE_USER
  • NO_ENGINE_SUBSTITUTION

※MariaDB 10.2.21での調査結果です。

# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 16
Server version: 10.2.21-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]> SELECT @@GLOBAL.sql_mode;
+-------------------------------------------------------------------------------------------+
| @@GLOBAL.sql_mode                                                                         |
+-------------------------------------------------------------------------------------------+
| STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> SELECT @@session.sql_mode;
+-------------------------------------------------------------------------------------------+
| @@session.sql_mode                                                                        |
+-------------------------------------------------------------------------------------------+
| STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> quit;
Bye

MariaDB 10.3系

SQL_MODEの初期設定値は以下の4つです。

  • STRICT_TRANS_TABLES
  • ERROR_FOR_DIVISION_BY_ZERO
  • NO_AUTO_CREATE_USER
  • NO_ENGINE_SUBSTITUTION

※MariaDB 10.3.12での調査結果です。

# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 16
Server version: 10.3.12-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]> SELECT @@GLOBAL.sql_mode;
+-------------------------------------------------------------------------------------------+
| @@GLOBAL.sql_mode                                                                         |
+-------------------------------------------------------------------------------------------+
| STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

MariaDB [(none)]> SELECT @@session.sql_mode;
+-------------------------------------------------------------------------------------------+
| @@session.sql_mode                                                                        |
+-------------------------------------------------------------------------------------------+
| STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

MariaDB [(none)]> quit;
Bye

MariaDB 10.4系（beta）

SQL_MODEの初期設定値は以下の4つです。
※現在10.4系はbetaなので、今後変更される可能性があります。

  • STRICT_TRANS_TABLES
  • ERROR_FOR_DIVISION_BY_ZERO
  • NO_AUTO_CREATE_USER
  • NO_ENGINE_SUBSTITUTION

※MariaDB 10.4.2での調査結果です。

# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 16
Server version: 10.4.2-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]> SELECT @@GLOBAL.sql_mode;
+-------------------------------------------------------------------------------------------+
| @@GLOBAL.sql_mode                                                                         |
+-------------------------------------------------------------------------------------------+
| STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

MariaDB [(none)]> SELECT @@session.sql_mode;
+-------------------------------------------------------------------------------------------+
| @@session.sql_mode                                                                        |
+-------------------------------------------------------------------------------------------+
| STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

MariaDB [(none)]> quit;
Bye

バージョン別SQL_MODE一覧表

バージョン@@GLOBAL.sql_mode@@session.sql_mode
MariaDB 5.5系無し無し
MariaDB 10.0系無し無し
MariaDB 10.1系
（10.1.7以上）		NO_AUTO_CREATE_USER
NO_ENGINE_SUBSTITUTION		NO_AUTO_CREATE_USER
NO_ENGINE_SUBSTITUTION
MariaDB 10.2系
（10.2.4以上）		STRICT_TRANS_TABLES
ERROR_FOR_DIVISION_BY_ZERO
NO_AUTO_CREATE_USER
NO_ENGINE_SUBSTITUTION		STRICT_TRANS_TABLES
ERROR_FOR_DIVISION_BY_ZERO
NO_AUTO_CREATE_USER
NO_ENGINE_SUBSTITUTION
MariaDB 10.3系STRICT_TRANS_TABLES
ERROR_FOR_DIVISION_BY_ZERO
NO_AUTO_CREATE_USER
NO_ENGINE_SUBSTITUTION		STRICT_TRANS_TABLES
ERROR_FOR_DIVISION_BY_ZERO
NO_AUTO_CREATE_USER
NO_ENGINE_SUBSTITUTION
MariaDB 10.4系
（beta）		STRICT_TRANS_TABLES
ERROR_FOR_DIVISION_BY_ZERO
NO_AUTO_CREATE_USER
NO_ENGINE_SUBSTITUTION		STRICT_TRANS_TABLES
ERROR_FOR_DIVISION_BY_ZERO
NO_AUTO_CREATE_USER
NO_ENGINE_SUBSTITUTION

不具合が起きる可能性のあるケース

SQL_MODEのデフォルト値変更に伴い、アップグレード後に不具合が起こる可能性があるケースは以下の2つです。

  • MariaDB 10.1.7未満（5.5系、10.0系も含む）からMariaDB 10.1.7以上にアップグレード
  • MariaDB 10.2.4未満（5.5系、10.0系、10.1系も含む）からMariaDB 10.2.4以上にアップグレード
特にMariaDB 10.2.4以上からデフォルトになった「STRICT_TRANS_TABLES」は、SQL文を厳密にしておかないとINSERT時にエラーが起きやすいので注意しましょう。

SQL_MODEの説明

デフォルトになっている4つのSQL_MODEの説明です。
※より詳しい説明はMariaDB公式サイトで閲覧可能です。

■MariaDB SQL_MODE
https://mariadb.com/kb/en/library/sql-mode/

【STRICT_TRANS_TABLES】

厳密モードです。無効または欠落データを含むステートメントは、無効または欠落データが最初の行ではない複数の行に影響を与える非トランザクションストレージエンジンおよびステートメントの場合を除いて、中止およびロールバックされます。値が欠落している場合は、列のデフォルト値を挿入してください。MariaDB 10.2.4以降のデフォルトです。

【ERROR_FOR_DIVISION_BY_ZERO】

このモードが設定されていない場合、ゼロ除算はNULLを返します。このモードを設定すると警告を返します。MDEV-8319も参照のこと。MariaDB 10.2.4以降のデフォルトです。

【NO_AUTO_CREATE_USER】

GRANTで自動的にユーザーを作成すると1133エラーを生成します。 エラー内容：”ユーザーテーブルに一致する行が見つかりません”。MariaDB 10.1.7以降のデフォルトです。

【NO_ENGINE_SUBSTITUTION】

このモードが設定されていない場合、CREATE TABLEで指定された利用可能なストレージエンジンが利用できない場合には警告が表示され、代わりにデフォルトのストレージエンジンが使用されます。このモードを設定すると、指定したストレージエンジンが利用できない場合にテーブルを作成するときに1286エラーが発生します。enforce_storage_engineも参照してください。MariaDB 10.1.7以降のデフォルトです。

SQL_MODEを変更する方法

昔作ったWebサイトなどでSQLを全部見直すのが大変でSQL_MODEを緩くしたい場合や、他のSQL_MODEを追加したい場合の方法です。

GLOBALのSQL_MODEを変更する場合

/etc/my.cnf.d/server.cnf を編集し、[mariadb]の行の下にsql_modeの設定を書き込みます。
※MySQLの場合は[mysqld]の下に追加してください。

# vi /etc/my.cnf.d/server.cnf

■SQL_MODEを全て無しにする場合
sql_mode='' 又は sql_mode= を追加します。

...省略...
# This group is only read by MariaDB servers, not by MySQL.
# If you use the same .cnf file for MySQL and MariaDB,
# you can put MariaDB-only options here

[mariadb]
sql_mode=''

...省略...

■SQL_MODEを1つだけ設定する場合
sql_mode= の後に設定したいSQL_MODEを記載します。

...省略...
# This group is only read by MariaDB servers, not by MySQL.
# If you use the same .cnf file for MySQL and MariaDB,
# you can put MariaDB-only options here

[mariadb]
sql_mode=STRICT_TRANS_TABLES

...省略...

■SQL_MODEを複数設定したい場合
sql_mode= の後に設定したいSQL_MODEをカンマ区切りで記載します。

...省略...
# This group is only read by MariaDB servers, not by MySQL.
# If you use the same .cnf file for MySQL and MariaDB,
# you can put MariaDB-only options here

[mariadb]
sql_mode=STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER

...省略...

編集が終わったらMariaDBを再起動します。

# systemctl restart mariadb

これでGLOBALのSQL_MODEが変更されました。生成される各セッションもこのGLOBAL設定が引き継がれます。

セッション（session）単体でSQL_MODEを変更したい場合

セッションごとにSET SESSIONにて設定します。

■SQL_MODEを全て無しにする場合

MariaDB [(none)]> SET SESSION sql_mode = '';
Query OK, 0 rows affected (0.00 sec)

確認

MariaDB [(none)]> SELECT @@session.sql_mode;
+--------------------+
| @@session.sql_mode |
+--------------------+
|                    |
+--------------------+
1 row in set (0.00 sec)

■SQL_MODEを設定する場合（複数の場合はカンマで区切ります。）

MariaDB [(none)]> SET SESSION sql_mode = 'STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected (0.00 sec)

確認

MariaDB [(none)]> SELECT @@session.sql_mode;
+--------------------------------------------+
| @@session.sql_mode                         |
+--------------------------------------------+
| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+
1 row in set (0.00 sec)

※セッション（session）単体でSQL_MODEを設定した場合はセッションが切れると設定が無効になるため、接続するたびに再度設定が必要になります。

アプリケーションからMariaDBに接続する場合は、DBへの接続時に「SET SESSION sql_mode = '設定するモード一覧'」を設定すればOKです。
書式は各言語のライブラリによって若干変わるので詳細は各言語のライブラリのヘルプでお調べください。
【PHP（PDO）の例】
$pdo->exec("SET SESSION sql_mode = 'STRICT_TRANS_TABLES'");
【Python（mysql.connector）の例】
db = mysql.connector.connect(host='****', user='****', database='****', sql_mode='STRICT_TRANS_TABLES')
【Perl（DBI）の例】
$db->do("SET SESSION sql_mode = 'STRICT_TRANS_TABLES'");

まとめ

MariaDBやMySQLなどをバージョンアップしたらSQLの処理でエラーが発生したらSQL_MODEのデフォルト値をチェックしましょう。

今後もバージョンが上がった際にSQL_MODEのGLOBALデフォルト値が変更になる可能性があるため、出来るだけアプリケーション側でSQL_MODEを設定しておくのがベストです。

以上で解決です。

タイトルとURLをコピーしました