Project

General

Profile

Actions
Copy link

Bug #9281

open

Allow STRICT_TRANS_TABLES

Added by Nicola Chiapolini almost 4 years ago. Updated 4 months ago.

Status:
New
Priority:
Normal
Assignee:
Carsten Rose
Target version:
23.6.5
Start date:
02.10.2019
Due date:
% Done:

0%

Estimated time:
Discuss:
Prio Planung:
No

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)

Related issues

Related to QFQ - Bug #11667: MySQL mariadb-server-10.3: Incorrect datetime valueNewCarsten Rose30.11.2020

Actions
Actions
Copy link
 #1

Updated by Nicola Chiapolini almost 4 years 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.)
Actions
Copy link
 #2

Updated by Nicola Chiapolini almost 4 years 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.)

Actions
Copy link
 #3

Updated by Nicola Chiapolini almost 4 years 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.

Actions
Copy link
 #4

Updated by Carsten Rose almost 4 years ago

  • Status changed from New to Some day maybe
Actions
Copy link
 #5

Updated by Carsten Rose over 2 years ago

  • Description updated (diff)
  • Assignee set to Carsten Rose
  • Target version set to next6
Actions
Copy link
 #6

Updated by Carsten Rose about 1 year ago

  • Target version changed from next6 to next3
  • Prio Planung set to No
Actions
Copy link
 #7

Updated by Carsten Rose about 1 year ago

  • Status changed from Some day maybe to New
Actions
Copy link
 #8

Updated by Carsten Rose about 1 year ago

Aktuell haben wir noch zu viele 10.0 und 10.1 MariaDB Versionen, die auf TEXT keinen Default kennen (Ubuntu 18.04). Sobald wir ueberall auf min. Ubuntu20.04 sind (MariaDB 10.3) koennen wir die Defaults auf fuer TEXT anpassen.

Actions
Copy link
 #9

Updated by Carsten Rose about 1 year ago

  • Related to Bug #11667: MySQL mariadb-server-10.3: Incorrect datetime value added
Actions
Copy link
 #10

Updated by Carsten Rose 4 months ago

  • Target version changed from next3 to 23.6.5
Actions
Copy link

Also available in: Atom PDF