前言介紹
- 這款 WordPress 外掛「Incorrect Datetime Bug Fix」是 2011-08-22 上架。
- 目前有 60 個安裝啟用數。
- 上一次更新是 2013-01-07，距離現在已有 4499 天。超過一年沒更新，安裝要確認版本是否可用。以及後續維護問題！
- 有 2 人給過評分。
- 還沒有人在論壇上發問，可能目前使用數不多，還沒有什麼大問題。
外掛協作開發者
外掛標籤
sql | mysql | sql_mode | no_zero_date | no_zero_in_date |
內容簡介
這個外掛是為了解決一些 MySQL 配置問題而創建的，會導致出現各種奇怪的症狀，包括但不限於：
無法創建新文章
無法更新文章
關於無效時間戳的錯誤
管理介面回復為最低權限，例如“發布”鈕變成了“提交草稿”鈕
此外，這個問題通常會伴隨著以下錯誤：
WordPress database error: [Incorrect datetime value: '0000-00-00 00:00:00' for column 'post_date_gmt' at row 1]
INSERT INTO 'mg_posts' ('post_author','post_date','post_date_gmt','post_content','post_content_filtered','post_title',
'post_excerpt','post_status','post_type','comment_status','ping_status','post_password','post_name','to_ping','pinged',
'post_modified','post_modified_gmt','post_parent','menu_order','guid') VALUES (’1′,’2011-08-23 03:32:43′,
’0000-00-00 00:00:00′,”,”,’Auto Draft’,”,’auto-draft’,'post’,'closed’,'open’,”,”,”,”,’2011-08-23 03:32:43′,’0000-00-00 00:00:00′,’0′,’0′,”)
無法通過零日期，會破壞實際創建新文章的能力，但它似乎還會讓 nonce（一種安全功能）混淆，可能是因為它們也依賴時間戳。
這個外掛從 @@SESSION 中剝離出以下的 sql_modes，因此消除了問題，但僅影響 WordPress 啟動的數據庫會話，而不是整個數據庫。
問題的解釋和歷史
這個問題的根本原因源於 MySQL 的一種奇怪行為/特性（即將被棄用），這是 WordPress 依賴的。我理解，它源於 MySQL 在某些上下文中使用 MySQL NOW() 函數的問題，因此作為一種 workaround，決定如果在設置為 NOT NULL 的列上輸入所有零日期，則不會拒絕該日期，而是會轉換為當前日期，來模仿 NOW()。
後來這種方法被邊緣化，因為它通常是不好的想法。現在存在一個名為 sql_mode 的設置值，可以關閉此行為，並在向 NOT NULL 字段傳遞 null 值時強行引發錯誤。在有實際的 DBA 負責數據庫的情況下，他們會將這些值分配給生產數據庫，以強制執行 MySQL 之外的正常 SQL 行為。
有 3 種 sql_modes 將觸發錯誤，因為它們禁用了輸入零日期的能力。
NO_ZERO_DATE
NO_ZERO_IN_DATE
TRADITIONAL
值得一提的是，ALLOW_INVALID_DATES 模式並不能解決這個問題，儘管從其名稱看來，它似乎可能會。
解決方案
此外掛解決了問題，對數據庫環境的影響非常小。此插件唯一的作用是檢查所問的 sql_modes 是否設置，如果設置，則刪除它們。
重要的是，這個外掛通過會話更改了 @@SESSION.sql_mode，而不是 @@GLOBAL.sql_mode。通過會話更改 sql_mode，它只對每個具體連接修改，而不是永久更改整個數據庫的 sql_mode。因此，這對於任何同時使用相同數據庫的其他應用程序都沒有任何影響。
其他症狀，替代解決方案。
警告：如果適用，更改生產數據庫之前，請先諮詢您的 DBA。
這個問題可能（我不知道）實際上更常見於安裝期間。錯誤是不同的，雖然我見過它們，但我沒有
原文外掛簡介
This plugin was create to work around a problem caused by certain configurations in MySQL that lead to several odd symptoms including but not limited to:
Loss of ability to create new posts
Loss of ability to update posts
Errors regarding invalid timestamps
Admin interface reverts to lowest permissions ex:‘Publish’ button says ‘Submit Draft’.
In addition, this problem is often accompanied by an error like the following:
WordPress database error: [Incorrect datetime value: '0000-00-00 00:00:00' for column 'post_date_gmt' at row 1]
INSERT INTO 'mg_posts' ('post_author','post_date','post_date_gmt','post_content','post_content_filtered','post_title',
'post_excerpt','post_status','post_type','comment_status','ping_status','post_password','post_name','to_ping','pinged',
'post_modified','post_modified_gmt','post_parent','menu_order','guid') VALUES (’1′,’2011-08-23 03:32:43′,
’0000-00-00 00:00:00′,”,”,’Auto Draft’,”,’auto-draft’,'post’,'closed’,'open’,”,”,”,”,’2011-08-23 03:32:43′,’0000-00-00 00:00:00′,’0′,’0′,”)
The inability to pass zero dates breaks the ability to actually create a new post, but it also seems to have the odd secondary effect of confusing the heck out of nonces (noces are a security feature), probably because they too rely on timestamps.
This plugin strips out the sql_modes listed below from @@SESSION, thereby eliminating the problem while only effecting database sessions WordPress starts and not the whole database.
Explanation and history of the problem
The cause of this problem stems from an old (soon to be deprecated) odd behavior/feature of MySQL which WordPress came to depend on. My understanding is that it stems from problems in MySQL with regard to using the MySQL NOW() function in some contexts – so as a workaround it was decided that if a date of all zeros was entered on a column that was set as NOT NULL, then that date would not be rejected, but instead would be converted to the current date – to mimic NOW().
Subsequently this method was marginalized as generally a bad idea – and now values exist for a setting called sql_mode, which turn this behavior off and force errors when a null value is passed to a NOT NULL field. In many cases when there is an actual DBA in charge of the database, they will assign these values to the production database to force what outside of MySQL would be normal SQL behavior.
There are 3 sql_modes which will trigger the error by disabling the ability to enter zero dates.
NO_ZERO_DATE
NO_ZERO_IN_DATE
TRADITIONAL
It’s worth mentioning that the ALLOW_INVALID_DATES mode does NOT solve the problem, even though by the name of it, it would seem that it might.
The solution
This plugin solves the problem with minimal effect to the database environment. All this plugin does it check to see if the sql_modes in question are set, and if they are, it removes them.
Importantly, this plugin changes @@SESSION.sql_mode and not @@GLOBAL.sql_mode. By changing sql_mode via the session, it only takes effect on each specific connections WordPress makes with the database rather than changing it for the entire database permanently – as such this will have no effect whatsoever on any applications that might happen to be using the same database.
Other manifestations, alternative solutions.
WARNING: Whenever applicable, consult your DBA before making any changes to production databases.
This problem may (I have no idea) in fact be more common during installation. The errors are different, and although I have seen them, I do not have them handy at this time. In any case, if the sql_modes are set during installation – the process will fail from the start – because of this, it is my presumption that anyone encountering the errors as described above – has probably recently migrated to a new database, or perhaps recently acquired an enthusiastic new DBA who has made some changes.
This plugin DOES NOT FIX this problem for those encountering during installation – thats impossible because WordPress ignores all plugins during installation – so there is no way to hack around it via plugin and fix it before installation occurs.
If you are encountering this problem during installation you will need to find an alternative way to remove the modes. If this is a production environment this may require the cooperation of a DBA who may not like the idea. If met with resistance, I suggest the asking for the modes to be temporarily removed for installation, and then set back after its dont – at which point this plugin will handle the problem from then on.
If your encountering this locally, or on a environment whose database you have access to modify, you will want to make as few changes as possible, so go to whatever interface you use (command line, phpMyAdmin, whatever) and run this line of SQL:
SELECT @@GLOBAL.sql_mode;
This will show you all the sql modes currently defined. Copy all them them but if you have NO_ZERO_DATE, NO_ZERO_IN_DATE, or TRADITIONAL remove them. Then update sql_mode with the remaining modes as such:
SET @@GLOBAL.sql_mode = "STRICT_TRANS_TABLES,STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO";
Please don’t simply use the sql_mode definition I have shown above, this is only an example – be sure to use the modes from your database configuration. Remember to keep everything together NO SPACES ALLOWED in the definition.
Finally, if your just working locally and don’t care about these sql_modes, you can just clear them all.
SET @@GLOBAL.sql_mode = "";
This will do the trick but is absolutely not recommended as a fix for production sites that might share environments.
Irrelevant Note: I struggled with naming this plugin, and settled on the terminology seen in the error message.
各版本下載點
- 方法一：點下方版本號的連結下載 ZIP 檔案後，登入網站後台左側選單「外掛」的「安裝外掛」，然後選擇上方的「上傳外掛」，把下載回去的 ZIP 外掛打包檔案上傳上去安裝與啟用。
- 方法二：透過「安裝外掛」的畫面右方搜尋功能，搜尋外掛名稱「Incorrect Datetime Bug Fix」來進行安裝。
（建議使用方法二，確保安裝的版本符合當前運作的 WordPress 環境。
延伸相關外掛（你可能也想知道）
Database Backup for WordPress 》立即備份您的資料庫、透過電子郵件發送備份，或設定備份自動執行。, Database Backup for WordPress 可以讓您快速備份 WordPress 核心資料庫表格，並將備份下...。
WP phpMyAdmin 》[ ✅ 由 Puvox 提供的安全插件 ] :, , • 漏洞研究已驗證。, • 對網站不造成額外負載和減慢速度。, • 不會收集和分享私人資料。, , 外掛描述, 這是著名的...。
Index WP MySQL For Speed 》如何使用這個外掛？, 安裝並啟用這個外掛後，前往「工具」選單下的「索引 MySQL 工具」。從那裡，您可以按下「立即新增索引鍵」的按鈕。如果您有大型資料表，...。
WP-ServerInfo 》狀態標誌, , 開發, https://github.com/lesterchan/wp-serverinfo, 翻譯, https://translate.wordpress.org/projects/wp-plugins/wp-serverinfo, 貢獻者, , ...。
Version Info | Show WP, PHP, MySQL & Web Server Versions in Admin Dashboard 》這個外掛會在管理員頁面的頁尾顯示當前的 WordPress 版本號碼以及以下環境資訊：, , 當前的 WordPress 版本資訊，若有更新，會同時顯示當前版本與最新版本, P...。
Database Manager – WP Adminer 》最佳的資料庫管理工具，為最佳的 CMS 所使用。, 此外掛使用工具Adminer 4.8.1，讓管理員可以直接從儀表板存取資料庫。, 就像前面那句話一樣簡單!, 也適用於 W...。
Database My Admin 》Database My Admin 是一款資料庫瀏覽器或管理工具，適用於 (MySQL 和 MariaDB)，可讓您輕鬆地從管理儀表板對資料庫進行 INSERT、SELECT、UPDATE、DELETE 操作...。
SQL Executioner 》使用 SQL Executioner，您可以在 WordPress 管理後台中運行任意的 SQL 查詢，而無需像 phpMyAdmin 或者 MySQL 命令行客戶端那樣使用外部工具來查看和修改您的...。
UsageDD 》UsageDD 可讓管理員監控他們的 WordPress 安裝資源使用情況。它會在每個頁面的底部中心添加一個小框，僅對管理員可見，顯示 MySQL 查詢數量、頁面程式碼使用...。
Simple Table Manager 》Simple Table Manager 是一個外掛，能通過最簡單的數據庫界面在 WordPress 儀表板上編輯表格記錄並將它們匯出為 CSV 文件。, , 在您的 wp-admin 界面上簡單地...。
Database Toolset 》資料庫工具集可以通過刪除所有孤兒或不需要的條目，例如“短期數據”、“修訂版”、“自動草稿”、“孤立的文章元”等，幫助您保持資料庫的清潔和優化。, 此外，資料...。
SQL Chart Builder 》此外掛可以根據您的 SQL 查詢建立漂亮的圖表，然後您可以在網站的任何部分使用這些圖表。, 您可以在查詢中使用 WordPress（wp）本機和非 wp mysql 表格。, 餅...。
PHP/MySQL CPU performance statistics 》CPU效能測試：, 此外掛會在您的PHP網頁伺服器和MySQL資料庫伺服器上進行各種計算和字串操作。, 為了進一步測試MySQL伺服器，此外掛會在一個自訂的資料庫表格...。
Custom DataBase Tables 》WordPress資料庫易於使用，並具有簡單易懂的提供初始表格結構。但如果您想要處理不符合初始表格結構的資料，或者考慮使用它作為CMS，建議創建一個新的表格。,...。
Convert WP Database to UTF-8 》這個外掛可以將你的 WordPress 資料庫（包括表格和欄位）轉換為 UTF-8 字元集。當你把資料庫從預設 CHARSET 不是 UTF-8 的伺服器移動到另一台時，這個外掛將...。