WackoWiki

View source for Re: Error when creating a new user

[1] **The culprit**

  1. The DEFAULT value was always set from the beginning.
    * commit:832a6ccf925202319b704d7327bde046d730d505
  1. However the **backup script** did not stored the DEFAULT in some cases.
    * commit:280f5ae0379ef2113a3f9a797ed30399e84c3818

That is how there was suddenly a missing default value. This was fixed with WackoWiki **!!(green)6.1.10!!**.

[2] **SQL Strict mode**

  * **MySQL** 5.7 made strict mode default, effectively requiring ##DEFAULT## values for ##NOT NULL## columns during ##INSERT##. 
  * **MariaDB** never enables strict mode by default, so ##DEFAULT## is not mandatory unless explicitly configured.
  * **SQLite** 3.37.0 introduced a STRICT tables feature, but does not support SQL Strict modes.

In both databases, the enforcement is governed by the ##sql_mode## setting. If ##STRICT_TRANS_TABLES## or ##STRICT_ALL_TABLES## is active, ##NOT NULL## columns without ##DEFAULT## values must be explicitly included in ##INSERT## statements.

You can change the SQL Strict mode in WackoWiki via the ##sql_mode## in the primary setting.
  * ##0## - server SQL mode (default)
  * ##1## - lax
  * ##2## - strict

class Dbal
%%(php)
<?php

// change the current SQL mode at runtime
$sql_modes = match((int) $this->sql_mode) {
	1		=> SQL_MODE_LAX[$this->db_vendor],
	2		=> SQL_MODE_STRICT[$this->db_vendor],
	default	=> 0, // server SQL mode
};

if ($sql_modes)
{
	$this->db->query("SET SESSION sql_mode = '$sql_modes'");
}
%%

constants.php
%%
const SQL_MODE_STRICT	= [
	'mariadb'	=> 'TRADITIONAL,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY',
	'mysql'		=> 'TRADITIONAL,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY'
];
const SQL_MODE_LAX	= [
	'mariadb'	=> 'NO_ENGINE_SUBSTITUTION,NO_AUTO_CREATE_USER',
	'mysql'		=> 'NO_ENGINE_SUBSTITUTION'
];
%%

config.php
%%
	'sql_mode' => '0',
%%

Thanks for the feedback.