MySQL 5.7 Invalid default value for 'CREATE_TIME'報錯的解決方法
由於資料庫的升級,今天在執行從MySQL 5.6匯出來的SQL檔案時報錯:
mysql> source cms_user.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected, 1 warning (0.04 sec)
ERROR 1067 (42000): Invalid default value for 'CREATE_TIME'
ERROR 1146 (42S02): Table 'ixinnuo_zxpt.cms_user' doesn't exist
因為MySQL 5.6以後timestamp設定預設值規則改變,不能為”0000 00-00 00:00:00”
解決方法:
檢視sql_mode:
mysql> show session 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.01 sec)
修改sql_mode,去掉NO_ZERO_IN_DATE,NO_ZERO_DATE:
mysql> set 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> source cms_user.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected, 1 warning (0.03 sec)
Query OK, 0 rows affected (0.55 sec)
Query OK, 1 row affected (0.07 sec)
mysql> source cms_user.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected, 1 warning (0.04 sec)
ERROR 1067 (42000): Invalid default value for 'CREATE_TIME'
ERROR 1146 (42S02): Table 'ixinnuo_zxpt.cms_user' doesn't exist
因為MySQL 5.6以後timestamp設定預設值規則改變,不能為”0000 00-00 00:00:00”
解決方法:
檢視sql_mode:
mysql> show session 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.01 sec)
修改sql_mode,去掉NO_ZERO_IN_DATE,NO_ZERO_DATE:
mysql> set 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> source cms_user.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected, 1 warning (0.03 sec)
Query OK, 0 rows affected (0.55 sec)
Query OK, 1 row affected (0.07 sec)
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/15498/viewspace-2136006/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- mysql-timestamp 0000-00-00 00:00:00 Invalid default valueMySql
- python: invalid value encountered in divide以及invalid value encountered in double_scalars報錯PythonIDE
- MySQL5.7 group by新特性報錯1055的解決辦法MySql
- mybatis竟然報"Invalid value for getInt()"MyBatis
- MySQL5.6新增root使用者報錯:Field 'ssl_cipher' doesn't have a default valueMySql
- 幾個報錯的解決方法
- hive使用報錯解決方法Hive
- 資料庫報錯java.sql.SQLException: Field ‘id‘ doesn‘t have a default value資料庫JavaSQLException
- MySQL could not be resolved: Temporary failure in name resolution報錯解決方法MySqlAI
- mysql登入報錯提示:ERROR 1045 (28000)的解決方法MySqlError
- dbfread報錯ValueError錯誤解決方法Error
- java.sql.SQLException: The server time zone value ‘Öйú±ê׼ʱ¼ä‘ is unrecognized...報錯解決JavaSQLExceptionServerZed
- MYSQL中 TYPE=MyISAM 錯誤的解決方法MySql
- ***XAMPP:報錯Unabletoloaddynamiclibrary的解決方法
- SQLyog連線MySQL8.0報2058錯誤的完美解決方法MySql
- MySQL5.7 使用者設定密碼解決方法MySql密碼
- 函式引數的 Default value函式
- MySql5.7及以上 ORDER BY 報錯問題MySql
- Invalid bound statement (not found)錯誤解決
- Mysql5.7錯誤日誌時間不對的解決辦法MySql
- 解決MySql報錯:1130 - Host ‘xxx‘ is not allowed to connect to this MySQL server的方法MySqlServer
- mysql匯入報錯怎麼解決?MySql
- mysql MRG_MyISAM 引擎報錯解決MySql
- mongoDB的db.shutdownServer()報錯的解決方法MongoDBServer
- streamlit run執行報錯,Invalid value: File does not exist: XXX.py
- ORACLE匯出文字到MYSQL 報錯 Incorrect integer value: ''OracleMySql
- MySQL 報錯MySQL server syntax to use near 'OPTION SQL_SELECT_LIMIT=DEFAULT'MySqlServerMIT
- 解決 Incorrect datetime value: '0000-00-00 00:00:00' 報錯
- DBeaver、Navicat、MySQL高頻報錯及解決方法,此文持續更新MySql
- MySQL 5.7 datetime和timestamp欄位設定default 0 插入資料包錯MySql
- ceph叢集安裝報錯解決方法
- RAC: SRVCTL and VIPCA 命令報錯解決方法PCA
- MRTG生成首頁報錯解決方法
- DBMS_METADATA報錯解決方法
- 安裝psutil模組報錯的解決方法
- HBase刪除列族報錯的解決方法
- git拉取程式碼報錯invalid path解決,以及windows的一個坑GitWindows
- MySQL5.7半同步複製報錯案例分析MySql