NC 12: No FILE_FORMAT in mariaDB (MySQL 4-byte support)

[NicoPrediger]

Hi. I'm trying to enable 4-byte support in mariaDB for NC12 as described here:
https://docs.nextcloud.com/server/12/admin_manual/configuration_database/mysql_4byte_support.html
I added the innodb-settings and issued this command

MariaDB> SELECT NAME, SPACE, FILE_FORMAT FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME like "nextcloud%";

and then I receive this error:

ERROR 1054 (42S22): Unknown column 'FILE_FORMAT' in 'field list'

When using SELECT * FROM.. I can see the following columns in the result: TABLE_ID, SCHEMA, NAME, FLAG, N_COLS and SPACE.

FILE_FORMAT being set to Barracuda seems essential in this guide, so I'm not sure how to carry on now. Did I miss anything? Are my settings not correct? Do I have to add FILE_FORMAT myself? If so, how?

For the sake of completeness, here's the settings I added to /etc/my.cnf:

innodb_large_prefix=true
innodb_file_format=barracuda
innodb_file_per_table=1
And of course, I restarted mariaDB after editing the config.

Any help is appreciated. :-)

[MorrisJobke]

cc @nickvergessen

[nickvergessen]

Which version of mariadb is that and which mysql version does it match

[NicoPrediger]

mysql --version
mysql Ver 15.1 Distrib 5.5.52-MariaDB, for Linux (x86_64) using readline 5.1

[Nibbels]

I got the same problem.
Unknown column 'FILE_FORMAT' in 'field list'

General settings

  Server connection collation utf8mb4_unicode_ci

Database server

Server: Localhost via UNIX socket
Server type: MariaDB
Server version: 5.5.52-MariaDB - MariaDB Server
Protocol version: 10
User: admin@localhost
Server charset: UTF-8 Unicode (utf8)
Web server

nginx/1.11.10
Database client version: libmysql - mysqlnd 5.0.11-dev - 20120503 - $Id: 76b08b24596e12d4553bd41fc93cccd5bac2fe7a $
PHP extension: mysqliDocumentation curlDocumentation mbstringDocumentation
PHP version: 5.6.30

That is the installation done by PLESK 17.5 / Onyx 17.5

Betriebssystem	‪CentOS Linux 7.3.1611 (Core)‬
Produkt	Plesk Onyx Version 17.5.3 Update 19, zuletzt aktualisiert: 27. Aug. 2017 04:27:03

This all started because News App did complain not having 4bytes support so I upgraded ...
It all went fine using
nextcloud/news#176
https://docs.nextcloud.com/server/11/admin_manual/maintenance/mysql_4byte_support.html
Until I wanted to update ocdownloader app.
nextcloud/server#6039

Greetings

[outsinre]

I've exactly the same issue.

nextcloud | 12.0.1
mariadb | 5.5.52
mysql | 15.1
php-fpm | 7.0.21

Following 4-byte support:

MariaDB> SELECT NAME, SPACE, FILE_FORMAT FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME like "nextcloud%";

Gives the following error:

ERROR 1054 (42S22): Unknown column 'FILE_FORMAT' in 'field list'

Further check, I found INFORMATION_SCHEMA.INNODB_SYS_TABLES has the following fields:

TABLE_ID | SCHEMA | NAME | FLAG | N_COLS | SPACE

[michel2323]

Same issue with the exact same versions than @outsinre on Ubuntu 14.04.

[Dennethan]

Hi,

I've just finished the migration to the 4-byte support in a CentOS 7.

It looks like that there is not FILE_FORMAT field because this was introduced in the version 10.0 and the default version in CentOS is the 5.5:
https://mariadb.com/kb/en/library/information-schema-innodb_sys_tables-table/

Then what I did is just install the version 10.2 from the mariaDB repos:
https://mariadb.com/resources/blog/installing-mariadb-10-centos-7-rhel-7

After that I didn't find any problem.

[kaefert]

is there a way to enable 4-byte support without upgrading the database?
I'm on MariaDB 5.5.57 on my Qnap NAS and facing the same problem with no easy way to upgrade the database version.

[MatthiasNeuhaus]

Hi all,

I have the same issue with mariaDB 10.3.
mysql_upgrade is done. So strange the field is not present on my server.

Does somebody have an idea?
Thanks a lot in advance.

[nickvergessen]

Check https://docs.nextcloud.com/server/stable/admin_manual/configuration_database/mysql_4byte_support.html#mariadb-support for the newest doc, has a special vlock for maria 10.3+

[MatthiasNeuhaus]

@nickvergessen thanks a lot and sorry. I did just not read properly to jump to the non-MariaDB == MySQL section 🙄.

[Itchiii]

@kaefert I know your comment is a bit older but did you find any solution? I also run a QNAP with MariaDB 5.5.57

[kaefert]

nope, sorry, I did end up installing debian 10 on my qnap, but not just because of this issue.

[deajan]

@Itchiii Here's what works with MariaDB 5.5.x

Update DB collation

mysql> ALTER DATABASE mynextcloud_db CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

Then make Nextcloud understand database's new collation:

sudo -u apache /opt/rh/rh-php73/root/usr/bin/php /var/www/mynextcloud/ftp/www/occ config:system:set mysql.utf8mb4 --type boolean --value="true"

Then let Nextcloud repair the database

sudo -u apache /opt/rh/rh-php73/root/usr/bin/php /var/www/mynextclouod/ftp/www/occ maintenance:repair

This worked for me under CentOS 7.
Obviously you'll have to change your php path (just use /usr/bin/php if no other version is installed), and your nextcloud installation path.

Cheers.

[Itchiii]

@deajan Thank you very much! Everything went smoothly with the adjustments.