Field 'tx_generic_gallery_predefined' doesn't have a default value

[yeezy69]

After change/upgrade from mysql-5.5 to mariadb-10 i had following problem:

When i try to insert new typo3-content, i became this message:

SQL error: 'Field 'tx_generic_gallery_predefined' doesn't have a default value' (tt_content:NEW589a3ba62f968221060887) (msg#1.1.12)

Default sql_mode in Ubuntu 16.04 Server for mariadb-10 is STRICT_TRANS_TABLES. BLOB and TEXT columns cannot have DEFAULT values. The BLOB and TEXT fields of generic-gallery are wrongly NOT NULL.

Example:

CREATE TABLE tbl (
    a int(11) NOT NULL,
    b tinytext NOT NULL
)

The tinytext field b has no default value. This is no problem, when the insert command has some value for b.

INSERT INTO tbl (a, b) VALUES (1, "hi");
INSERT INTO tbl (a, b) VALUES (2, "");

But while run insert without field b, an error occours.

INSERT INTO tbl (a) VALUES (3);

--> SQL error: Field 'b' doesn't have a default value

Workaround is to remove STRICT_TRANS_TABLES from sql_mode in /etc/mysql/my.cnf (or set only sql_mode=NO_ENGINE_SUBSTITUTION).

Real solution: I modified your ext_tables.sql for running with strict-trans-tables is active. I removed "NOT NULL" from any tinytext, mediumtext and blob field. Then default value is NULL.

#
# Table structure for table 'tx_generic_gallery_pictures'
#
CREATE TABLE tx_generic_gallery_pictures (
    uid int(11) NOT NULL auto_increment,
    pid int(11) DEFAULT '0' NOT NULL,
    tstamp int(11) DEFAULT '0' NOT NULL,
    crdate int(11) DEFAULT '0' NOT NULL,
    cruser_id int(11) DEFAULT '0' NOT NULL,
    sorting int(10) DEFAULT '0' NOT NULL,
    deleted tinyint(4) DEFAULT '0' NOT NULL,
    hidden tinyint(4) DEFAULT '0' NOT NULL,
    starttime int(11) DEFAULT '0' NOT NULL,
    endtime int(11) DEFAULT '0' NOT NULL,
    tt_content_id int(11) DEFAULT '0' NOT NULL,
    title tinytext,
    link tinytext,
    images blob,
    contents int(11) DEFAULT '0' NOT NULL,

    PRIMARY KEY (uid),
    KEY parent (pid)
);

#
# Table structure for table 'tx_generic_gallery_content'
#
CREATE TABLE tx_generic_gallery_content (
    uid int(11) NOT NULL auto_increment,
    pid int(11) DEFAULT '0' NOT NULL,
    tstamp int(11) DEFAULT '0' NOT NULL,
    crdate int(11) DEFAULT '0' NOT NULL,
    cruser_id int(11) DEFAULT '0' NOT NULL,
    deleted tinyint(4) DEFAULT '0' NOT NULL,
    hidden tinyint(4) DEFAULT '0' NOT NULL,
    starttime int(11) DEFAULT '0' NOT NULL,
    endtime int(11) DEFAULT '0' NOT NULL,
    pictures_id int(11) DEFAULT '0' NOT NULL,
    bodytext mediumtext,
    position tinytext,
    width tinytext,

    PRIMARY KEY (uid),
    KEY parent (pid)
);

#
# Table structure for table 'pages'
#
CREATE TABLE tt_content (
    tx_generic_gallery_items int(11) DEFAULT '0' NOT NULL,
    tx_generic_gallery_predefined tinytext,
    tx_generic_gallery_images blob,
    tx_generic_gallery_collection int(11) DEFAULT '0' NOT NULL,
);

[fnagel]

Thanks for reporting! TYPO3 in general does not support STRICT_TRANS_TABLES but I'm willing to fix this anyway. More like something for the next major or at least minor version. And needs some testing in all supported versions.

Did you notice these? https://forge.typo3.org/issues/54883 https://forge.typo3.org/issues/68704 https://forge.typo3.org/issues/70629

[fnagel]

@yeezy69 Any feedback on this issue?