Can't add new bookmarks and "Incorrect default value '0000-00-00 00:00:00' for column 'expires'" PHP error log

[antoineemerit]

After my Debian 11 to Debian 12 server upgrade, when I ty to add a new bookmarks I hav a blank page and the following error in my PHP log :

[Wed Oct 18 23:53:56.534779 2023] [php:error] [pid 1692876] [client 192.168.99.10:45952] PHP Fatal error: Uncaught mysqli_sql_exception: Incorrect default value '0000-00-00 00:00:00' for column 'expires' in /home/public/www/bookmarks.kozodo.com/htdocs/inc/database.inc.php:536\nStack trace:\n#0 /home/public/www/bookmarks.kozodo.com/htdocs/inc/database.inc.php(536): mysqli_query()\n#1 /home/public/www/bookmarks.kozodo.com/htdocs/inc/database.inc.php(109): SB_DatabaseMySQL->raw()\n#2 /home/public/www/bookmarks.kozodo.com/htdocs/inc/faviconcache.inc.php(199): SB_Database->insert()\n#3 /home/public/www/bookmarks.kozodo.com/htdocs/inc/pageparser.inc.php(341): SB_FaviconCache->saveFavicon()\n#4 /home/public/www/bookmarks.kozodo.com/htdocs/command.php(1578): SB_PageParser->getInformation()\n#5 /home/public/www/bookmarks.kozodo.com/htdocs/command.php(1384): SB_CommandWindow->buildProperties()\n#6 /home/public/www/bookmarks.kozodo.com/htdocs/command.php(1297): SB_CommandWindow->buildAddBookmark()\n#7 /home/public/www/bookmarks.kozodo.com/htdocs/command.php(183): SB_CommandWindow->buildGetBookmarkInformation()\n#8 /home/public/www/bookmarks.kozodo.com/htdocs/command.php(171): SB_CommandWindow->handleCommandBuild()\n#9 /home/public/www/bookmarks.kozodo.com/htdocs/command.php(96): SB_CommandWindow->handleCommand()\n#10 /home/public/www/bookmarks.kozodo.com/htdocs/command.php(6462): SB_CommandWindow->__construct()\n#11 {main}\n thrown in /home/public/www/bookmarks.kozodo.com/htdocs/inc/database.inc.php on line 536, referer: https://bookmarks.kozodo.com/command.php?command=Add%20Bookmark&nid_acl=15

[antoineemerit]

This error is caused by fieds defination of the form "datetime NOT NULL DEFAULT '0000-00-00 00:00:00'". A partial fix exist in the sql/upgrade_3.6.1.sql but it's not complete.

Here is my patch :+1:

diff --git a/sql/upgrade_3.6.1.sql b/sql/upgrade_3.6.1.sql
index 2b19cdd..a69a75d 100644
--- a/sql/upgrade_3.6.1.sql
+++ b/sql/upgrade_3.6.1.sql
@@ -1,16 +1,24 @@
 SET @@sql_mode = '';
 
 ALTER TABLE `sitebar_cache` change `created` `created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP;
+ALTER TABLE `sitebar_cache` change `expires` `expires` datetime DEFAULT NULL;
 ALTER TABLE `sitebar_config` change `changed` `changed` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP;
 ALTER TABLE `sitebar_link` change `added` `added` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP;
+ALTER TABLE `sitebar_link` change `changed` `changed` datetime DEFAULT NULL;
+ALTER TABLE `sitebar_link` change `visited` `visited` datetime DEFAULT NULL;
+ALTER TABLE `sitebar_link` change `tested` `tested` datetime DEFAULT NULL;
 ALTER TABLE `sitebar_message` change `sent` `sent` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP;
+ALTER TABLE `sitebar_message` change `expires` `expires` datetime DEFAULT NULL;
 ALTER TABLE `sitebar_session` change `created` `created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP;
 ALTER TABLE `sitebar_token` change `issued` `issued` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP;
+ALTER TABLE `sitebar_visit` change `visited` `visited` datetime DEFAULT NULL;
 
 UPDATE `sitebar_cache` SET `expires` = NULL where date(`expires`) = '0000-00-00 00:00:00';
 UPDATE `sitebar_link` SET `changed` = NULL where date(`changed`) = '0000-00-00 00:00:00';
 UPDATE `sitebar_link` SET `visited` = NULL where date(`visited`) = '0000-00-00 00:00:00';
 UPDATE `sitebar_link` SET `tested` = NULL where date(`tested`) = '0000-00-00 00:00:00';
+UPDATE `sitebar_message` SET `expires` = NULL where date(`expires`) = '0000-00-00 00:00:00';
+UPDATE `sitebar_visit` SET `visited` = NULL where date(`visited`) = '0000-00-00 00:00:00';
 
 UPDATE `sitebar_config`
     SET `release` = '3.6.2';