HackerOne users: Testing against this community violates our program's Terms of Service and will result in your bounty being denied.

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

edited June 2020 in Vanilla 3.x Help


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:                 }
422:                 trigger_error($message, E_USER_ERROR);
423:             }
425:         }


[/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)'
[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:


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.


  • Options

    What version of mysql do you have? Have you tried upgrading to a later version?

  • Options
    edited June 2020

    Its running Mysql server version: 5.7.29-0ubuntu0.18.04.1.

    I haven't attempted to update the Mysql version.

  • Options

    PHP error from the logs:

    Jun 08 17:16:08 [Mon Jun 08 15:16:08.922161 2020] [php7:notice] [pid 894] [client] PHP Warning: Cannot assign an empty string to a string offset in /app/data/public/library/core/class.format.php on line 1203, referer: https:
  • Options
    R_JR_J Ex-Fanboy Munich Admin

    That's just a warning and you can ignore that.

    If you create a file /conf/bootstrap.after.php and put that into this file, it will be executed on each request:

    Gdn::database()->query("SET sql_mode = '';");

    I don't think that any code in Vanilla will change your db server to strict mode again, so that should be enough. Hope that helps...

  • Options
    edited June 2020

    @R_J Thanks for your input. Is there somewhere I need to include the /conf/bootstrap.after.php ? Or will Vanilla automatically load it?

    Figured it out, yes it's auto loaded :) .

Sign In or Register to comment.