Bug #79589
closed
DB Column SYS_FILE_METADATA.FE_GROUPS
0%
Description
I've started with 7.6.14 - all fine
Two extensions are affected here: a) filemetadata and b) fal_securedownload
When I've upgraded to 7.6.15, Database analyzer wants to execute the following:
ALTER TABLE sys_file_metadata CHANGE fe_groups fe_groups tinytext NOT NULL;
This FAILS ALYWAYS, as files with NULL values in SYS_FILE_METADATA.FE_GROUPS exists.
To workaround this, I've updated all records with NULL values for SYS_FILE_METADATA.FE_GROUPS by setting SYS_FILE_METADATA.FE_GROUPS to '' (empty string instead of NULL value)
Having done this, I was able to perform the database analyzer upgrade to the valid 7.6.15 structure.
But, SIDE EFFECT !, starting from this upgrade NO UPLOADS to a file store are possible, as usual upload from FILE - FILE STORE fails because trying to insert NULL values in SYS_FILE_METADATA.FE_GROUPS.
Seems to be a conceptual problem. As it's technically not possibe to define a default for a TINYTEXT NOT NULL column (error is #1101 - BLOB/TEXT column 'description' can't have a default value), the desired structure change to NOT NULL does not work.
Updated by Morton Jonuschat over 7 years ago
- Status changed from New to Needs Feedback
Hi!
Can you check that there are no other extensions modifying this field? The NOT NULL constraint has been removed from that column a while back (August 2016, before TYPO3 7.6.11 was released), see the following commit and EXT:sys_file_metadata is the only extension in the core providing that field.
Currently I'm not able to reproduce your error with a fresh install of TYPO3 7.6.14, adding some file metadata and then upgrading it to 7.6.15.
Updated by Michael OF over 7 years ago
Hi Morton,
As my (newbie) installation don't have that much extensions, I guess it must be fal_securedownload.
Where could I finde the SQL changes an extensions makes during installation?
I have a very, very similar issue for extension kk_csv2table. This extension creates a NOT NULL column tt_content.tx_kkcsv2table_flex. Which also get's a NULL value for each file upload, so, at least for me, unusable. I'll open a seprate issue for that.
Regards,
Michael
Updated by Michael OF over 7 years ago
Hi Morton,
SOLVED.
I've checked: Both extensions fal_securedownload and e.g. kk_csv2table are adding NOT NULL columns to TYPO3 core tables. fal_securedownload adds, as reported here, SYS_FILE_METADATA.FE_GROUPS TINYTEXT NOT NULL.
The problem was, that my new TYPO3 installation uses an also new MySQL/MariaDB installation which itself uses STRICT SQL mode as default, as all newer MySQL/MariaDB are doing worldwide: https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html
But both extensions and so TYPO3 are "trusting" on the previously set NON-STRICT mode: They add NOT-NULL columns to TYPO3 core/system tables, SYS_FILE_METADATA or TT_CONTENT. As TYPO3 core is, of course, not "aware" of that, all INSERT from TYPO3 core operations be the same as before extension installation and don't add any values to these "unknown" columns. As they are defined as NOT NULL, and MySQl/MariaDB does not allow DEFAULT values on TEXT/TINYTEXT/MEDIUMTEXT columns, "trusting" on "NON-STRICT" mode means that NON-STRICT "allows" inserting a NULL value into a NOT-NULL column, in fact inserting an empty string instead of a NULL value.
"Trusting" on this make it easy for TYPO3 an it's extensions to be independent and flexible, but as this issue shows not following (SQL) standards could mean pitfalls in compatibility, we should at least document this very clearly.
Solution to solve this issue was to set $TYPO3_CONF_VARS[SYS] setDBinit = SET SESSION sql_mode='', (re-)enabling NON-STRICT mode.
I recommend doing this isolated for TYPO3's database user's SESSION. NOT on MySQL's instance level. As not always, and definitively not on my own MariaDB instance, only TYPO3 is using the database instance , and other systems might rely on SQL standards.
Updated by Riccardo De Contardi over 7 years ago
- Status changed from Needs Feedback to Closed
Hi Michael,
thank you for your answer and findings. I think I can then safely close this issue, then.
If you think that this is the wrong decision or that there is still work to be done, please reopen it or open a new issue with a reference to this one. Thank you.