[Warning] TIMESTAMP with implicit DEFAULT --explicit_defaults_for_timestamp

urgel_babay發表於2016-02-29
2014.9.25
本想在MySQL上面做點實驗的，可以登入的時候報錯（ps:mysql 服務已經是開啟的）：
window 7 64位，
MySQL 5.7

[Warning] TIMESTAMP with implicit DEFAULT --explicit_defaults_for_timestamp

日誌：

點選(此處)摺疊或開啟

  1. 2014-09-25T04:19:53.971570Z 0 [Note] Plugin \'FEDERATED\' is disabled.
  2. 2014-09-25T04:19:53.974570Z 0 [Warning] InnoDB: 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.
  3. 2014-09-25T04:19:53.977570Z 0 [Note] InnoDB: The InnoDB memory heap is disabled
  4. 2014-09-25T04:19:53.978570Z 0 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions
  5. 2014-09-25T04:19:53.979570Z 0 [Note] InnoDB: Uses system mutexes
  6. 2014-09-25T04:19:53.980570Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.3
  7. 2014-09-25T04:19:53.982571Z 0 [Warning] InnoDB: Adjusting innodb_buffer_pool_instances from 8 to 1 since innodb_buffer_pool_size is less than 1024 MiB
  8. 2014-09-25T04:19:53.987571Z 0 [Note] InnoDB: Number of pools: 1
  9. 2014-09-25T04:19:53.988571Z 0 [Note] InnoDB: Not using CPU crc32 instructions
  10. 2014-09-25T04:19:54.003572Z 0 [Note] InnoDB: Initializing buffer pool, total size = 57.0M, instances = 1
  11. 2014-09-25T04:19:54.012572Z 0 [Note] InnoDB: Completed initialization of buffer pool
  12. 2014-09-25T04:19:54.016573Z 0 [ERROR] InnoDB: .\\ibdata1 must be writable
  13. 2014-09-25T04:19:54.017573Z 0 [ERROR] InnoDB: The system tablespace must be writable
  14. 2014-09-25T04:19:54.218584Z 0 [ERROR] Plugin \'InnoDB\' init function returned error.
  15. 2014-09-25T04:19:54.219584Z 0 [ERROR] Plugin \'InnoDB\' registration as a STORAGE ENGINE failed.
  16. 2014-09-25T04:19:54.220584Z 0 [ERROR] mysqld: unknown option \'-p\'
  17. 2014-09-25T04:19:54.221584Z 0 [ERROR] Aborting

  19. 2014-09-25T04:19:54.222584Z 0 [Note] Binlog end
  20. 2014-09-25T04:19:54.223584Z 0 [Note] Shutting down plugin \'partition\'
  21. 2014-09-25T04:19:54.224584Z 0 [Note] Shutting down plugin \'PERFORMANCE_SCHEMA\'
  22. 2014-09-25T04:19:54.225584Z 0 [Note] Shutting down plugin \'INNODB_SYS_DATAFILES\'
  23. 2014-09-25T04:19:54.227585Z 0 [Note] Shutting down plugin \'INNODB_SYS_TABLESPACES\'
  24. 2014-09-25T04:19:54.228585Z 0 [Note] Shutting down plugin \'INNODB_SYS_FOREIGN_COLS\
但是奇怪的是，這是在cmd下面報錯。我用Navicat for Mysql 連線到MySQL，是可以正常連線的。
網上找了很多文章，有以下幾種解決方案，可能適合你的情況：
1、刪除data目錄下的ib_logfile0和ib_logfile1檔案，重啟MySQL。 
   -----這種多發生在啟動MySQL服務時，不能開啟MySQL服務。但是我的服務可以正常的開啟和關閉：

2、在my.ini mysqld 下新增 explicit_defaults_for_timestamp=true
   -----我新增之後，還是原來的錯誤。
3、手動的在服務裡面，重啟MySQL服務
   -----這種可能性表示懷疑。手動的跟命令列下，應該都一樣吧

中午吃飯回來繼續解決。
可發現，報錯不一樣了
[Warning] TIMESTAMP with implicit DEFAULT --explicit_defaults_for_timestamp
然後又把新增的引數給註釋掉。
再登入的時候，居然可以登入了。

真的百思不得其解。剛剛接觸MySQL，很多問題不會，只能透過度娘以及認識的大牛們。

哈哈 現在要下班了，剛剛在Linux環境下同樣遇見這個問題：

點選(此處)摺疊或開啟

  1. [root@BIDEVEDW_DB bin]# ./mysqld -uroot -p
  2. 2014-09-25 17:29:05 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
  3. 2014-09-25 17:29:05 24368 [Note] Plugin \'FEDERATED\' is disabled.
  4. 2014-09-25 17:29:05 24368 [Note] InnoDB: Using atomics to ref count buffer pool pages
  5. 2014-09-25 17:29:05 24368 [Note] InnoDB: The InnoDB memory heap is disabled
  6. 2014-09-25 17:29:05 24368 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
  7. 2014-09-25 17:29:05 24368 [Note] InnoDB: Compressed tables use zlib 1.2.3
  8. 2014-09-25 17:29:05 24368 [Note] InnoDB: Using Linux native AIO
  9. 2014-09-25 17:29:05 24368 [Note] InnoDB: Using CPU crc32 instructions
  10. 2014-09-25 17:29:05 24368 [Note] InnoDB: Initializing buffer pool, size = 9.0G
  11. 2014-09-25 17:29:07 24368 [Note] InnoDB: Completed initialization of buffer pool
  12. 2014-09-25 17:29:08 24368 [ERROR] InnoDB: Unable to lock ./ibdata1, error: 11
  13. 2014-09-25 17:29:08 24368 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.

但是這樣就可以登入了：

點選(此處)摺疊或開啟

  1. [root@BIDEVEDW_DB bin]# ./mysql -uroot -p
  2. Enter password:
  3. Welcome to the MySQL monitor. Commands end with ; or \\g.
  4. Your MySQL connection id is 19445
  5. Server version: 5.6.19 Source distribution

  7. Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

  9. Oracle is a registered trademark of Oracle Corporation and/or its
  10. affiliates. Other names may be trademarks of their respective
  11. owners.

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

  15. mysql>
度娘還是那些，這次請教大牛。這是與大牛的對話：
[Warning] TIMESTAMP with implicit DEFAULT --explicit_defaults_for_timestamp
[Warning] TIMESTAMP with implicit DEFAULT --explicit_defaults_for_timestamp

總算明白是怎麼會事兒了！
回家！

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/30936525/viewspace-2016618/，如需轉載，請註明出處，否則將追究法律責任。

相關文章