Project

General

Profile

Bug #9281

Updated by Carsten Rose 9 months ago

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. 

 <pre> <pre><code class="sql"> 
 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`; 
 </code></pre> 

 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) 

Back