MariaDB 10.3 causing mysqld: unknown variable 'innodb-file-format=barracuda’

[dottenbr]

Issue: MariaDB server version 10.3 doesn't start when configured according to documentation. Aborts with error mysqld: unknown variable 'innodb-file-format=barracuda’.

Previous setup instructions for Frappe Bench required to add some variables to /usr/local/etc/my.cnf:

[mysqld]
innodb-file-format=barracuda
innodb-file-per-table=1
innodb-large-prefix=1
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

[mysql]
default-character-set = utf8mb4

This is also still in several documentations:

• https://github.com/frappe/bench/wiki/MariaDB-conf-for-Frappe
• https://github.com/frappe/bench/wiki/Turning-a-Mac-into-a-Frappe-Framework-Developer-Machine

If you are on MariaDB 10.2 this will work just fine but on version 10.3 this will cause the MariaDB server to not start properly with an error message like 2018-06-21 13:05:39 0 [ERROR] /usr/local/Cellar/mariadb/10.3.7/bin/mysqld: unknown variable 'innodb-file-format=barracuda’

According to the MariaDB configuration (https://mariadb.com/kb/en/library/xtradbinnodb-server-system-variables/#innodb_file_format) Barracuda is default for >= MariaDB 10.2.2 so I removed the line. After that the next error that crashes it is 2018-06-21 13:22:02 0 [ERROR] /usr/local/Cellar/mariadb/10.3.7/bin/mysqld: unknown variable 'innodb-large-prefix=1.

I tried searching the forum and didn’t come across similar problems.

So which one of the two is it:

1. I have completely misconfigured my MariaDB Server config files
2. The config file must be changed and the documentation updated

To Reproduce Steps to reproduce the behavior:

1. Edit my.cnf according to https://github.com/frappe/bench/wiki/MariaDB-conf-for-Frappe
2. Run MariaDB Server 10.2 succesfully (probably your current version)
3. Run MariaDB server 10.3 unsuccessfully (brew update)

Related issue is https://github.com/frappe/bench/issues/670

[dottenbr]

If I comment out the following 3 lines MariaDB starts correctly:

[mysqld]
#innodb-file-format=barracuda
#innodb-file-per-table=1
#innodb-large-prefix=1

I am now checking if the values that we are trying to set are the default values.

[dottenbr]

https://mariadb.com/kb/en/library/xtradbinnodb-server-system-variables/#innodb_file_format says:

• innodb_file_format Default Value: Barracuda (>= MariaDB 10.2.2) Antelope (<= MariaDB 10.2.1) Deprecated: MariaDB 10.2, Removed: MariaDB 10.3.1

This means this must be removed and is on by default.

• innodb_file_per_table Default Value: ON (>= MariaDB 5.5), OFF (<= MariaDB 5.3)

This means it should be removed because it is on by default.

• innodb_large_prefix Default Value: ON (>= MariaDB 10.2.2) OFF (<= MariaDB 10.2.1) Deprecated: MariaDB 10.2 Removed: MariaDB 10.3.1

This means this must be removed and is on by default.

Todo:

• [x] Update https://github.com/frappe/bench/wiki/Turning-a-Mac-into-a-Frappe-Framework-Developer-Machine

• [ ] Update https://github.com/frappe/bench/wiki/MariaDB-conf-for-Frappe

• [ ] Update any other documentation and remove those lines

• [ ] Since we can not patch this automatically we must send out an information to the users proactively instead of having them have a failing MariaDB start

[creamdory]

Had the same issue in my machine. Commenting the 3 lines of my config file fixed it.

[codingCoffee]

has been fixed and deployed to hotfix, should soon be merged into master and develop on next release of frappe

[dottenbr]

Thank you, this fixes the installer but not existing systems which will break on mariadb update.

[codingCoffee]

@dottenbr How did you deal with the update on your system ? Did just commenting those 3 lines mitigate the issue? If yes, I don't think writing a script to fix existing systems will be useful, because people save their mariadb configs under different names, so we don't know what file we're looking for!

[dottenbr]

@codingCoffee

Development computers: the way we did it is that I have informed all of our developers about this problem refering to this issue here. Then I asked them to comment out the 3 lines and update their MariaDB to version 10.3.

Servers: here we will run maintenance this weekend with the same solution basically.

I agree with you that you can't patch this properly. This is the problem. However since this is now a known issue we have to make sure the community gets informed and doesn't run into that same problem.

I will write a post on the discussion forum but there has to be a better way of making sure this won't cause headaches or downtime for people.

[dottenbr]

@codingCoffee announced on discuss.erpnext.com: https://discuss.erpnext.com/t/important-mariadb-version-10-3-action-required-before-update/37717/2

[codingCoffee]

@dottenbr thanks for the post!

Also I don't feel anyone running linux systems will face any problem. This is because MariaDB is not upgraded on linux. It stays the same version it was when installed, since we specifically set the version we need, for example in Ubuntu using the command sudo add-apt-repository 'deb [arch=amd64,i386,ppc64el] http://ftp.ubuntu-tw.org/mirror/mariadb/repo/10.2/ubuntu xenial main' we set the version in /etc/apt/sources.list

To upgrade one needs to manually follow a series of steps as listed here: https://mariadb.com/kb/en/library/upgrading-from-mariadb-102-to-mariadb-103/ And usually people don't do it. It's just on the mac that running brew update && brew upgrade will update your MariaDB version.

So I believe we can close this issue :)

[intelliant01]

Also I don't feel anyone running linux systems will face any problem....we set the version in /etc/apt/sources.list

@codingCoffee It impacts new installs on Linux as well because when we follow the instructions such as that at https://github.com/frappe/frappe/wiki/The-Hitchhiker's-Guide-to-Installing-Frapp%C3%A9-on-Linux-OS -

$ sudo apt-get install software-properties-common
$ sudo apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xF1656F24C74CD1D8
$ sudo add-apt-repository 'deb [arch=amd64,i386,ppc64el] http://ftp.ubuntu-tw.org/mirror/mariadb/repo/10.2/ubuntu xenial main'

$ sudo apt-get update
$ sudo apt-get install mariadb-server-10.2

we get a warning that 10.2 is no longer available and we may instead try installing 10.3 . Have updated the instructions in the guide as well.

[codingCoffee]

@intelliant01 thanks for the updating the Hitchhiker's Guide. But I just checked and MariaDB 10.2 is also still available on Ubuntu.

[intelliant01]

@codingCoffee You are right. However, I did get a message recommending an installation of 10.3 instead of 10.2. Can't reproduce that now. It may be noteworthy that I had 10.0.33 pre-installed on the system while I was trying to install 10.2.

However, with 10.3 and the new instructions in the Hitchhiker's Guide, it seems all set for new comers.

[barredterra]

This issue came back somehow. debian_mariadb_config.cnf and mariadb_config.cnf still (or again) contain the offending lines.

[baimard]

Nextcloud 22.0

when you try to :

php occ maintenance:repair

error show by nextcloud :

ERROR: An exception occurred while executing a query: SQLSTATE[HY000]: General error: 4047 InnoDB refuses to write tables with ROW_FORMAT=COMPRESSED or KEY_BLOCK_SIZE.

solved by : in mysql

SET GLOBAL innodb_read_only_compressed=OFF;