Mysql Strict Mode - Database error Duplicate entry '0' for key [Vanillaforums 3.2 support question]

Hi

I recently upgraded my Vanillaforums instance from 2.0.81 through all major releases until version 3.2. Then I changed webhost, to my own webhost. Essentially a docker container orchestrator, maybe you've heard of it; cloudron. Its got a LAMP stack which works great.

Environment details:

Running on

• PHP version: PHP 7.3.
• Mysql version: Server version: 5.7.29-0ubuntu0.18.04.1

However there is one problem which I haven't been able to address at the data layer in the Mysql engine, and that is Mysql Strict mode. This mode is enabled. Up until Vanillaforums 3.2 the forum threads wouldn't even load, throwing out errors. But I was always sure Strict mode was the culprit. I am trying to continously set the SQL MODE from the Vanillaforums PHP code, which I have solved. However I need to run the query more frequently as it seems to set itself back to STRICT MODE eventually for some reason.

I added a line after line 156 to file library/database/class.database.php to switch the mysql engine SQL MODE to "". This allows the forum to boot up upon load for the end user. Pretty great! My first Prepared Statement in PHP, yey!

It now looks like this:

  /**
   *
   *
   * @param $dsn
   * @param $user
   * @param $password
   * @return PDO
   * @throws Exception
   */
  protected function newPDO($dsn, $user, $password) {
    try {
      $pDO = new PDO(strtolower($this->Engine).':'.$dsn, $user, $password, $this->ConnectionOptions);
      $pDO->setAttribute(PDO::ATTR_EMULATE_PREPARES, 0);
      $pDO->query("set time_zone = '+0:0'");
      $pDO->query("SET sql_mode = ''");

Can I add this line   $pDO->query("SET sql_mode = ''"); anywhere else to have the SQL mode set more frequently? I want to preferably set it at every database call. I don't care if this will waste a few CPU cycles.

It lets me browse the forum and my users can be active now.

But there are snags:

editing own replies and posts doesn't work

Replying to posts works, but generates an error to the user, even though the reply is persisted to the database and shown upon next browser reload of the page.

The error which comes up is this one:

Duplicate entry '0' for key 'PRIMARY'

insert `GDN_Activity` 
(`ActivityUserID`, `RegardingUserID`, `HeadlineFormat`, `Story`, `NotifyUserID`, `Notified`, `Emailed`, `Photo`, `Route`, `Data`, `ActivityTypeID`, `DateInserted`, `InsertUserID`, `InsertIPAddress`, `DateUpdated`) 
values (:ActivityUserID, :RegardingUserID, :HeadlineFormat, :Story, :NotifyUserID, :Notified, :Emailed, :Photo, :Route, :Data, :ActivityTypeID, :DateInserted, :InsertUserID, :InsertIPAddress, :DateUpdated)The error occurred on or near: /app/data/public/library/database/class.database.php
418:                 if (!$message) {
419:                     $message = $ex->getMessage();
420:                 }
421: 
422:                 trigger_error($message, E_USER_ERROR);
423:             }
424: 
425:         }
426: 

Backtrace:

/app/data/public/library/database/class.database.phpPHP::gdn_ErrorHandler();
[/app/data/public/library/database/class.database.php:422] PHP::trigger_error();
[/app/data/public/library/database/class.sqldriver.php:1707] Gdn_Database->query();
[/app/data/public/library/database/class.sqldriver.php:1133] Gdn_SQLDriver->query();
[/app/data/public/applications/dashboard/models/class.activitymodel.php:1606] Gdn_SQLDriver->insert();
[/app/data/public/applications/dashboard/models/class.usermodel.php:2364] ActivityModel->save();
[/app/data/public/applications/dashboard/controllers/class.profilecontroller.php:978] UserModel->save();
[/app/data/public/applications/dashboard/controllers/class.profilecontroller.php:859] ProfileController->saveAvatars();
[/app/data/public/library/core/class.dispatcher.php:862] ProfileController->picture();
[/app/data/public/library/core/class.dispatcher.php:279] Gdn_Dispatcher->dispatchController();
[/app/data/public/index.php:29] Gdn_Dispatcher->dispatch();

Variables in local scope:

[sql] 'insert `GDN_Activity` 
(`ActivityUserID`, `RegardingUserID`, `HeadlineFormat`, `Story`, `NotifyUserID`, `Notified`, `Emailed`, `Photo`, `Route`, `Data`, `ActivityTypeID`, `DateInserted`, `InsertUserID`, `InsertIPAddress`, `DateUpdated`) 
values (:ActivityUserID, :RegardingUserID, :HeadlineFormat, :Story, :NotifyUserID, :Notified, :Emailed, :Photo, :Route, :Data, :ActivityTypeID, :DateInserted, :InsertUserID, :InsertIPAddress, :DateUpdated)'
[inputParameters] 
[options] array (
  'Type' => 'insert',
  'Slave' => NULL,
  'ReturnType' => 'ID',
)
[returnType] 'ID'
[tries] 2
[try] 0
[pDO] array (
)
[pDOStatement] array (
  'queryString' => 'insert `GDN_Activity` 
(`ActivityUserID`, `RegardingUserID`, `HeadlineFormat`, `Story`, `NotifyUserID`, `Notified`, `Emailed`, `Photo`, `Route`, `Data`, `ActivityTypeID`, `DateInserted`, `InsertUserID`, `InsertIPAddress`, `DateUpdated`) 
values (:ActivityUserID, :RegardingUserID, :HeadlineFormat, :Story, :NotifyUserID, :Notified, :Emailed, :Photo, :Route, :Data, :ActivityTypeID, :DateInserted, :InsertUserID, :InsertIPAddress, :DateUpdated)',
)
[state] '00000'
[code] NULL
[message] 'Duplicate entry \'0\' for key \'PRIMARY\'|Gdn_Database|Query|insert `GDN_Activity` 
(`ActivityUserID`, `RegardingUserID`, `HeadlineFormat`, `Story`, `NotifyUserID`, `Notified`, `Emailed`, `Photo`, `Route`, `Data`, `ActivityTypeID`, `DateInserted`, `InsertUserID`, `InsertIPAddress`, `DateUpdated`) 
values (:ActivityUserID, :RegardingUserID, :HeadlineFormat, :Story, :NotifyUserID, :Notified, :Emailed, :Photo, :Route, :Data, :ActivityTypeID, :DateInserted, :InsertUserID, :InsertIPAddress, :DateUpdated)'
[ex] array (
)

Some users see this popup after replying to a thread:

Screenshot:

Why not 3.3? My upgrade of Vanillaforums from 3.2 to 3.3 unfortunately failed, and results in an inoperable system. I didn't look into the error logs yet, but will do and report soon. I'm pretty sure its got to do with the Strict Mode.