Project

General

Profile

Bug #9281

Allow STRICT_TRANS_TABLES

Added by Nicola Chiapolini 16 days ago. Updated 16 days ago.

Status:
New
Priority:
Normal
Assignee:
-
Target version:
-
Start date:
02.10.2019
Due date:
% Done:

0%

Estimated time:
Discuss:

Description

MariaDB > 10.2.4 (Debian Buster comes with 10.3) has STRICT_TRANS_TABLES set by default. (see https://mariadb.com/kb/en/library/documentation/mariadb-administration/variables-and-modes/sql-mode/)
This breaks QFQ as no defaults are set for text columns in Form or FormElement.

Setting a default for these tables does work but has the ugly side-effect that on load the QFQ-Form Editor shows two single quotes as default value for all text fields...
A better solution is to allow all non-mandatory fields without default values to be NULL.

ALTER TABLE `Form`
CHANGE `noteInternal` `noteInternal` text COLLATE 'utf8_general_ci' NULL AFTER `title`,
CHANGE `multiSql` `multiSql` text COLLATE 'utf8_general_ci' NULL AFTER `multiMode`,
CHANGE `parameter` `parameter` text COLLATE 'utf8_general_ci' NULL AFTER `bsNoteColumns`,
CHANGE `parameterLanguageA` `parameterLanguageA` text COLLATE 'utf8_general_ci' NULL AFTER `parameter`,
CHANGE `parameterLanguageB` `parameterLanguageB` text COLLATE 'utf8_general_ci' NULL AFTER `parameterLanguageA`,
CHANGE `parameterLanguageC` `parameterLanguageC` text COLLATE 'utf8_general_ci' NULL AFTER `parameterLanguageB`,
CHANGE `parameterLanguageD` `parameterLanguageD` text COLLATE 'utf8_general_ci' NULL AFTER `parameterLanguageC`;

ALTER TABLE `FormElement`
CHANGE `modeSql` `modeSql` text COLLATE 'utf8_general_ci' NULL AFTER `mode`,
CHANGE `note` `note` text COLLATE 'utf8_general_ci' NULL AFTER `rowLabelInputNote`,
CHANGE `adminNote` `adminNote` text COLLATE 'utf8_general_ci' NULL AFTER `note`,
CHANGE `value` `value` text COLLATE 'utf8_general_ci' NULL AFTER `placeholder`,
CHANGE `sql1` `sql1` text COLLATE 'utf8_general_ci' NULL AFTER `value`,
CHANGE `parameter` `parameter` text COLLATE 'utf8_general_ci' NULL AFTER `sql1`,
CHANGE `parameterLanguageA` `parameterLanguageA` text COLLATE 'utf8_general_ci' NULL AFTER `parameter`,
CHANGE `parameterLanguageB` `parameterLanguageB` text COLLATE 'utf8_general_ci' NULL AFTER `parameterLanguageA`,
CHANGE `parameterLanguageC` `parameterLanguageC` text COLLATE 'utf8_general_ci' NULL AFTER `parameterLanguageB`,
CHANGE `parameterLanguageD` `parameterLanguageD` text COLLATE 'utf8_general_ci' NULL AFTER `parameterLanguageC`,
CHANGE `clientJs` `clientJs` text COLLATE 'utf8_general_ci' NULL AFTER `parameterLanguageD`;

an alternative workaround is to deactivate STRICT_TRANS_TABLES by setting
[init] => SET names utf8; SET sql_mode = "ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";
in the extension config of qfq (this might also be needed for existing installations as upgrading large databases where no care was used on allowing NULL or setting defaults is a pain)

History

#1 Updated by Nicola Chiapolini 16 days ago

An additional problem: '' is not a valid integer default anymore. This affects inserting rows with active formSubmitLog:

toUser:: SQL error
os:: [ mysqli: 1366 ] Incorrect integer value: '' for column `qfq_studentadmin`.`FormSubmitLog`.`recordId` at row 1
Form:: phd_reg-graduation
Form Element:: 164 / reg_done / Final
Timestamp:: 2019.10.02 11:46:10 +0200
Code:: 2001
Message:: SQL error
Type:: Db Exception
SQL final:: INSERT INTO FormSubmitLog (formData, sipData, clientIp, feUser, userAgent, formId, recordId, pageId, sessionId, created)VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, NOW())
SQL Params:: array (
  0 => [...]
  2 => '130.60.23.141',
  3 => 'testphd',
  4 => 'Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/76.0.3809.100 Safari/537.36',
  5 => '1000',
  6 => '',
  7 => '8',
  8 => '9jfdf7riha8mmsjmq0kdna5a69',
)

I deactivated the submit-log for now, but would be glad to get this fixed in the source. (I might look into this once all other problems from the upgrade have been fixed.)

#2 Updated by Nicola Chiapolini 16 days ago

And of course '' is not a valid datetime either (e.g. to allow STRICT_TRANS_TABLES, when storing a form empty fields must be converted into NULL or a valid values for the relevant column type.)

#3 Updated by Nicola Chiapolini 16 days ago

Just found the existing bug for this problem: #7407. I agree that the immediate fix with setting sql_mode by default is a good idea. However I still think qfq should be made STRICT_TRANS_TABLES-compatible in the long run. So I will keep collecting problems here as I discover them.

Also available in: Atom PDF