データベースのインポートでエラー1067 - 'フィールド名'のタイムゾーン問題の無効なデフォルト値（mysql 5.7での新しいエラー解決策）。

データベースインポート時のエラー1067 - 'フィールド名'のデフォルト値が無効です

最近mysqlを5.7にアップグレードしたのですが、wordpressのインポートデータにエラーが発生しました。

comment_date' のデフォルト値が無効です。

その理由は、次のような記述です。

DROP TABLE IF EXISTS `wp_comments`;
CREATE TABLE `wp_comments` (
  `comment_ID` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `comment_post_ID` bigint(20) UNSIGNED NOT NULL DEFAULT 0,
  `comment_author` tinytext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL,
  `comment_author_email` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
  `comment_author_url` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
  `comment_author_IP` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
  `comment_date` datetime(0) NOT NULL DEFAULT '0000-00-00 00:00:00',
  `comment_date_gmt` datetime(0) NOT NULL DEFAULT '0000-00-00 00:00:00',
  `comment_content` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL,
  `comment_karma` int(11) NOT NULL DEFAULT 0,
  `comment_approved` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '1',
  `comment_agent` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
  `comment_type` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
  `comment_parent` bigint(20) UNSIGNED NOT NULL DEFAULT 0,
  `user_id` bigint(20) UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (`comment_ID`) USING BTREE,
  INDEX `comment_post_ID`(`comment_post_ID`) USING BTREE,
  INDEX `comment_approved_date_gmt`(`comment_approved`, `comment_date_gmt`) USING BTREE,
  INDEX `comment_date_gmt`(`comment_date_gmt`) USING BTREE,
  INDEX `comment_parent`(`comment_parent`) USING BTREE,
  INDEX `comment_author_email`(`comment_author_email`(10)) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 35 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_520_ci ROW_FORMAT = Dynamic;

このエラーは、mysqlを5.7にアップグレードしたために、互換性のないデフォルトが原因で発生することがほとんどです。型のデフォルト値が制限されている可能性があると思いますので、sql_modeを確認してみてください。

sql_modeを見る

mysql> show variables like 'sql_mode';
+---------------+----------------------------------------------------------------------------------------------------------------- --------------------------+
| Variable_name | Value |
+---------------+----------------------------------------------------------------------------------------------------------------- --------------------------+
| sql_mode | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ ENGINE_SUBSTITUTION |
+---------------+----------------------------------------------------------------------------------------------------------------- --------------------------+
1 row in set (0.00 sec)

mysql>

一時的な変更です。

mysql> set session
 -> sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>

恒久的な修正。

my.cnfファイルを直接修正することができます。

例：vim /etc/my.cnf

mysqld]の下に、以下のようなものを追加してください。

sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

問題は解決しました。今度は、インポートやテーブルを作成するときに見てみてください

mysql5.7でsql_modeを設定するとエラーになる。

エラーメッセージを表示します。

1.ERROR 1055 (42000): Expression #7 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'students.chengji.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
1. sql_mode in mysql 5.7 has the following values.
2.ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_ SUBSTITUTION

1. My mysql5.7 is deployed on linux, then I use navicat 12 to connect, so how to use navicat 12 to view it
2. use navicat to open the database, then tools - > command column interface (shortcut F6)
3. then enter: SELECT @@sql_mode;
4. see the value of
5.ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_ SUBSTITUTION


1. The mysql runtime configuration I am using is specified as follows.
2./apps/mysql5.7.18/bin/mysqld --defaults-file=/apps/my3306.cnf --basedir=/apps/mysql5.7.18 --datadir=/apps/mysql/data/3306 --plugin- dir=/apps/mysql5.7.18/lib/mysql/plugin --user=mysql --log-error=/apps/mysql/data/3306/BJ-DYC-VM-5-106.err --open-files-limit=8192 -- pid-file=/apps/mysql/data/3306/BJ-DYC-VM-5-106.pid --socket=/tmp/mysql_3306.sock --port=3306
3.
4. then I modified /apps/my3306.cnf, you do not have a problem with it haha
5. find.
6. sql_mode = "ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_ USER,NO_ENGINE_SUBSTITUTION"
7.
8. Modify to.
9. sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_ SUBSTITUTION"
10.
11. Restart mysql because the mysql configuration file was modified
12./apps/mysql5.7.18/bin/mysqld --defaults-file=/apps/my3306.cnf --basedir=/apps/mysql5.7.18 --datadir=/apps/mysql/data/3306 --plugin- dir=/apps/mysql5.7.18/lib/mysql/plugin --user=mysql --log-error=/apps/mysql/data/3306/BJ-DYC-VM-5-106.err --open-files-limit=8192 -- pid-file=/apps/mysql/data/3306/BJ-DYC-VM-5-106.pid --socket=/tmp/mysql_3306.sock --port=3306

1. Go to the command line interface
2. Then type: SELECT @@sql_mode;
3. see the value of
4.STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
5.
6. At this point, you do not have to abide by the following conventions.
7. for GROUP BY aggregation operation, if the column in SELECT, does not appear in GROUP BY, then this SQL is not legal, because the column is not in the GROUP BY clause, so for the database that set this mode, when using group by, you have to use MAX(), SUM(), ANT_VALUE() to complete the GROUP BY aggregation operation.

1. The second method does not need to modify the configuration file, use navicat to modify
2. Go to the command line interface (F6)
3. Enter: SELECT @@GLOBAL.sql_mode;
4. Result: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_ SUBSTITUTION
5.
6. Then let's modify sql_mode
7. Enter: set GLOBAL sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_ SUBSTITUTION ';
8.
9. Run your sql again.
10. SELECT id,ip FROM ip_meta_backup GROUP BY ip HAVING count(ip) > 1
18. ***** Congratulations, it worked *****
21.
22. Then you can run your sql again if it works!
23.
24. SET GLOBAL sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_ SUBSTITUTION';
25.
26. SELECT @@GLOBAL.sql_mode;

1. The third solution does not need to modify the configuration file, use navicat to modify
2. Go to the command line interface (F6)
3. Enter: SELECT @@sql_mode; Note: session is missing here, the full one is: SELECT @@SESSION.sql_mode;
4. Result: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_ SUBSTITUTION
5.
6. Then let's modify sql_mode
7. Enter: set SESSION sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE _SUBSTITUTION ';
8.
9. Run your sql again.
10. SELECT id,ip FROM ip_meta_backup GROUP BY ip HAVING count(ip) > 1
18. ***** still reports only_full_group_by error *****
19.
20. ############## solution ###################
21.
22. set sql_mode before your query statement
23.
24. set SESSION sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_ SUBSTITUTION ';
25. SELECT id,ip FROM ip_meta_backup GROUP BY ip HAVING count(ip) > 1
33. You don't need to add set sql_mode to new queries (including group by) until you close this connection
37.
38. Then you can run your sql again if it works!
39.
40. SET GLOBAL sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_ SUBSTITUTION';
41.
42. SELECT @@GLOBAL.sql_mode;

1. In fact, when talking about the third method, it says SELECT @@sql_mode; In fact, this side of the default session, the complete is: SELECT @@SESSION.sql_mode;
2. SESSION is the meaning of the current session ----> this sentence explains until you close this connection
4. then why in the command line set sql_mode, and then new query or useless?
5. In fact, you just need to enter SELECT @@sql_mode in the new query; you will find that your settings in the command line did not take effect
6. So, we can only re-set sql_mode in the new query of this connection (session), after this connection (session) is no longer needed
7. This language explains the phrase "set sql_mode before the statement you are querying"

1. Both of these set sql_mode methods will revert to the sql-mode option set in my.cnf in the configuration file I specified with the restart of mysql on my linux deployment
2. This sentence is rather roundabout
3. It means that after restarting mysql on linux, the settings you set in navicat will no longer be valid, but will be based on the settings in the configuration file you specified, which is set in the file my.cnf

1. STRICT_TRANS_TABLES: In this mode, if a value cannot be inserted into a transactional table, the current operation is interrupted, with no restrictions on non-transactional tables
3. NO_ZERO_IN_DATE: In strict mode, the date and month are not allowed to be zero
5.NO_ZERO_DATE: set this value, mysql database does not allow inserting zero date, inserting zero date will throw an error instead of a warning.
7. ERROR_FOR_DIVISION_BY_ZERO: If the data is divided by zero during INSERT or UPDATE, then an error is thrown instead of a warning. If the pattern is not given, MySQL returns NULL when the data is divided by zero.
9. NO_AUTO_CREATE_USER: Prevents GRANT from creating a user with an empty password.
11. NO_ENGINE_SUBSTITUTION: Throws an error if the required storage engine is disabled or not compiled. When this value is not set, the default storage engine is used instead and an exception is thrown
13.PIPES_AS_CONCAT: treat "||" as string concatenation operator instead of or operator, which is the same as Oracle database, and similar to the string concatenation function Concat
15.ANSI_QUOTES: When ANSI_QUOTES is enabled, the string cannot be quoted in double quotes, because it is interpreted as an identifier
17. NO_AUTO_VALUE_ON_ZERO: This value affects the insertion of self-growing columns. By default, the insertion of 0 or NULL means that the next self-growing value is generated. This option is useful if the user wants the inserted value to be 0 and the column is self-growing.


1. The other modes use nothing more than changing the configuration file, or changing them independently (i.e. methods 2 and 3)

理由

1. sql_mode in mysql 5.7 has the following values.
2.ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_ SUBSTITUTION

表示方法について

1. My mysql5.7 is deployed on linux, then I use navicat 12 to connect, so how to use navicat 12 to view it
2. use navicat to open the database, then tools - > command column interface (shortcut F6)
3. then enter: SELECT @@sql_mode;
4. see the value of
5.ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_ SUBSTITUTION

解決策1

1. The mysql runtime configuration I am using is specified as follows.
2./apps/mysql5.7.18/bin/mysqld --defaults-file=/apps/my3306.cnf --basedir=/apps/mysql5.7.18 --datadir=/apps/mysql/data/3306 --plugin- dir=/apps/mysql5.7.18/lib/mysql/plugin --user=mysql --log-error=/apps/mysql/data/3306/BJ-DYC-VM-5-106.err --open-files-limit=8192 -- pid-file=/apps/mysql/data/3306/BJ-DYC-VM-5-106.pid --socket=/tmp/mysql_3306.sock --port=3306
3.
4. then I modified /apps/my3306.cnf, you do not have a problem with it haha
5. find.
6. sql_mode = "ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_ USER,NO_ENGINE_SUBSTITUTION"
7.
8. Modify to.
9. sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_ SUBSTITUTION"
10.
11. Restart mysql because the mysql configuration file was modified
12./apps/mysql5.7.18/bin/mysqld --defaults-file=/apps/my3306.cnf --basedir=/apps/mysql5.7.18 --datadir=/apps/mysql/data/3306 --plugin- dir=/apps/mysql5.7.18/lib/mysql/plugin --user=mysql --log-error=/apps/mysql/data/3306/BJ-DYC-VM-5-106.err --open-files-limit=8192 -- pid-file=/apps/mysql/data/3306/BJ-DYC-VM-5-106.pid --socket=/tmp/mysql_3306.sock --port=3306

バリデートする。

1. Go to the command line interface
2. Then type: SELECT @@sql_mode;
3. see the value of
4.STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
5.
6. At this point, you do not have to abide by the following conventions.
7. for GROUP BY aggregation operation, if the column in SELECT, does not appear in GROUP BY, then this SQL is not legal, because the column is not in the GROUP BY clause, so for the database that set this mode, when using group by, you have to use MAX(), SUM(), ANT_VALUE() to complete the GROUP BY aggregation operation.

解決策2

1. The second method does not need to modify the configuration file, use navicat to modify
2. Go to the command line interface (F6)
3. Enter: SELECT @@GLOBAL.sql_mode;
4. Result: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_ SUBSTITUTION
5.
6. Then let's modify sql_mode
7. Enter: set GLOBAL sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_ SUBSTITUTION ';
8.
9. Run your sql again.
10. SELECT id,ip FROM ip_meta_backup GROUP BY ip HAVING count(ip) > 1
18. ***** Congratulations, it worked *****
21.
22. Then you can run your sql again if it works!
23.
24. SET GLOBAL sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_ SUBSTITUTION';
25.
26. SELECT @@GLOBAL.sql_mode;

解決策3

1. The third solution does not need to modify the configuration file, use navicat to modify
2. Go to the command line interface (F6)
3. Enter: SELECT @@sql_mode; Note: session is missing here, the full one is: SELECT @@SESSION.sql_mode;
4. Result: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_ SUBSTITUTION
5.
6. Then let's modify sql_mode
7. Enter: set SESSION sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE _SUBSTITUTION ';
8.
9. Run your sql again.
10. SELECT id,ip FROM ip_meta_backup GROUP BY ip HAVING count(ip) > 1
18. ***** still reports only_full_group_by error *****
19.
20. ############## solution ###################
21.
22. set sql_mode before your query statement
23.
24. set SESSION sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_ SUBSTITUTION ';
25. SELECT id,ip FROM ip_meta_backup GROUP BY ip HAVING count(ip) > 1
33. You don't need to add set sql_mode to new queries (including group by) until you close this connection
37.
38. Then you can run your sql again if it works!
39.
40. SET GLOBAL sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_ SUBSTITUTION';
41.
42. SELECT @@GLOBAL.sql_mode;

アプローチ3ソリューションの解説はこちら

1. In fact, when talking about the third method, it says SELECT @@sql_mode; In fact, this side of the default session, the complete is: SELECT @@SESSION.sql_mode;
2. SESSION is the meaning of the current session ----> this sentence explains until you close this connection
4. then why in the command line set sql_mode, and then new query or useless?
5. In fact, you just need to enter SELECT @@sql_mode in the new query; you will find that your settings in the command line did not take effect
6. So, we can only re-set sql_mode in the new query of this connection (session), after this connection (session) is no longer needed
7. This language explains the phrase "set sql_mode before the statement you are querying"

アプローチ2アプローチ3の2つのアプローチの説明

1. Both of these set sql_mode methods will revert to the sql-mode option set in my.cnf in the configuration file I specified with the restart of mysql on my linux deployment
2. This sentence is rather roundabout
3. It means that after restarting mysql on linux, the settings you set in navicat will no longer be valid, but will be based on the settings in the configuration file you specified, which is set in the file my.cnf

その他のモード説明付き

1. STRICT_TRANS_TABLES: In this mode, if a value cannot be inserted into a transactional table, the current operation is interrupted, with no restrictions on non-transactional tables
3. NO_ZERO_IN_DATE: In strict mode, the date and month are not allowed to be zero
5.NO_ZERO_DATE: set this value, mysql database does not allow inserting zero date, inserting zero date will throw an error instead of a warning.
7. ERROR_FOR_DIVISION_BY_ZERO: If the data is divided by zero during INSERT or UPDATE, then an error is thrown instead of a warning. If the pattern is not given, MySQL returns NULL when the data is divided by zero.
9. NO_AUTO_CREATE_USER: Prevents GRANT from creating a user with an empty password.
11. NO_ENGINE_SUBSTITUTION: Throws an error if the required storage engine is disabled or not compiled. When this value is not set, the default storage engine is used instead and an exception is thrown
13.PIPES_AS_CONCAT: treat "||" as string concatenation operator instead of or operator, which is the same as Oracle database, and similar to the string concatenation function Concat
15.ANSI_QUOTES: When ANSI_QUOTES is enabled, the string cannot be quoted in double quotes, because it is interpreted as an identifier
17. NO_AUTO_VALUE_ON_ZERO: This value affects the insertion of self-growing columns. By default, the insertion of 0 or NULL means that the next self-growing value is generated. This option is useful if the user wants the inserted value to be 0 and the column is self-growing.

その他のモードの説明について

1. The other modes use nothing more than changing the configuration file, or changing them independently (i.e. methods 2 and 3)