After upgrading to 15.0.7: MySQL is used as database but does not support 4-byte characters

Hi there,

Just upgraded to NextCloud 15.0.7, upgrade went fine. New message however: MySQL is used as database but does not support 4-byte characters

This is for me to much to change data bases and still worry that my system will continue to operate. I have come that far that all my files are ā€œAntilopeā€. Then the manual wants me to change them all to barracuda. I really think this is to difficult and therefore to risky to do for meā€¦

Is it possible to give me a script that I can run to update my database?

Any help is appreciated.

Cees Bakker

Just went through the conversion for MariaDB 10.1 without any problems. The instructions does indeed generate a script for the conversion and I had no problem stripping the | characters in ā€˜viā€™.

A positive side effect of the conversion is that this step will enable an easier upgrade to MariaDB 10.3.

Better yetā€¦ call mysql with -s -r and you wonā€™t even get the bars and other formatting, which will present an output, which you can just copy and paste back into the same mysql session.

Dear Budy, would it be possible to show full commands here so I can just execute them within the mariadb environment? Many thanks, Cees.

Hi Cees,

wellā€¦ thereā€™s actually not so much to it. Have you checked, what version your MariaDB is? If your MariaDB is < 10.2 then proceed as stated in the docs for modifying your DB.

Since I donā€™t know the name of your NC instanceā€™s database, I canā€™t provide you with anything more, than stated in the document.

Howeverā€¦ to recapā€¦ you will need to prepare your MariaDB for this change by editing the servers mysql.cnf. Then run mysql like this:

mysql -u root -p -s -r

this will instruct MariaDB to omit all this fancy output stuff, like | and the like. Now issue this into your session:

SELECT CONCAT("ALTER TABLEā€œ, TABLE_SCHEMA,ā€.", TABLE_NAME, "ROW_FORMAT=DYNAMIC;") AS MySQLCMD FROM TABLES WHERE TABLE_SCHEMA = "NextcloudDataBaseName";

and this will spill out a number of sql commands which will convert all of your tables from Antilope to Barracuda. Copy all of that output and paste it back into the session and the conversion will start. Depending on the size and power of our MariaDB server, this might take a while.

Once thatā€™s done, proceed with the remains of the conversion as lined out in the document.

Cheers,
budy

Dear Budy,

Many thanks for your reply. My database is called nextcloud. So I modified yhe command you suggested to:

SELECT CONCAT("ALTER TABLE ā€œ, TABLE_SCHEMA,ā€ . ", TABLE_NAME, " ROW_FORMAT=DYNAMIC;") AS MySQLCMD FROM TABLES WHERE TABLE_SCHEMA = "nextcloud";

this is the output:

afbeelding

Obviously I have no knowledge about working with mariadb. I have checked the version however: mysql Ver 15.1 Distrib 10.1.38-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2 Furhtermore I run with Ubuntu 18.04.2 LTS.

Maybe there is something else to change?

Best regards, Cees

Hi Cees,

after logging into MariaDB, issue a

show databases;

and provide the output.

Cheers,
budy

There you go:
afbeelding

Thanks!

You didnā€™t changed to the proper DB before running, that command. Please do as laid out in the document:

USE INFORMATION_SCHEMA;

before running the SELECT command and just ā€œnextcloudā€ as the database name. That should do it.

Cheers,
budy

Hi Budy,

Really nice I have copied the output which looks like:

ALTER TABLEā€œ, TABLE_SCHEMA,ā€.oc_activityROW_FORMAT=DYNAMIC;
ALTER TABLEā€œ, TABLE_SCHEMA,ā€.oc_activity_mqROW_FORMAT=DYNAMIC;
ALTER TABLEā€œ, TABLE_SCHEMA,ā€.oc_addressbookchangesROW_FORMAT=DYNAMIC;
ALTER TABLEā€œ, TABLE_SCHEMA,ā€.oc_addressbooksROW_FORMAT=DYNAMIC;
ALTER TABLEā€œ, TABLE_SCHEMA,ā€.oc_appconfigROW_FORMAT=DYNAMIC;
ALTER TABLEā€œ, TABLE_SCHEMA,ā€.oc_authtokenROW_FORMAT=DYNAMIC;
ALTER TABLEā€œ, TABLE_SCHEMA,ā€.oc_bruteforce_attemptsROW_FORMAT=DYNAMIC;

for many more lines ofcourse. if I try to use these lines as commands I get the following responce:
MariaDB [INFORMATION_SCHEMA]> ALTER TABLEā€œ, TABLE_SCHEMA,ā€.oc_accountsROW_FORMAT=DYNAMIC;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ā€˜TABLEā€œ, TABLE_SCHEMA,ā€.oc_accountsROW_FORMAT=DYNAMICā€™ at line 1
MariaDB [INFORMATION_SCHEMA]>

Maybe I have to change something in the lines?

Thanks, Cees

You somehow borked the command: in your case it should have been:

SELECT CONCAT(ā€œALTER TABLE ", TABLE_SCHEMA,".", TABLE_NAME, " ROW_FORMAT=DYNAMIC;ā€) AS MySQLCMD FROM TABLES WHERE TABLE_SCHEMA = "nextcloud";

Try thatā€¦

Hey Guys,

This change required to my database really puzzles me. I have gotten quit some help from Stefan above, unfortunately it does not make it possible for me to apply the required changes in a way I feel comfortable. In depth knowledge of MariDB is required.

What really scares me is that after the conversion from Dynamic to Barracuda there are databases left that might need an index table. Where again very special steps are required to create those indexes.

Then I have questions what will happen, if changes from equipment are applied to the databases while I am applying all these adjustments. Is that going to be safe?

Really this kind of change requires a lot more help before I am able to apply it with confidence. Would than complete new installation of NextCloud be advisable?

Best regards, Cees.

Hi @budy

Can you paste your SQL queries as code here? Some characters are removed or displayed differently in this forum and probably some chars get lost when NextCees copies your lines.

It works with three back ticks, the code in the next lines and again three back ticks after the code:

```
Code lines in between
```

@NextCees
Iā€™m not sure which MariaDB version you are running. I suggest you try to determine this information.
Then best would be to follow the documentation:
https://docs.nextcloud.com/server/15/admin_manual/configuration_database/mysql_4byte_support.html

I want to point out the necessity to define a few parameters in the MariaDB config (usually /etc/mysql/my.cnf) depending on the version you are using.

For MariaDB >= 10.3 please make sure you have the following parameters set and defined in your MariaDB config in the [mysqld] block:

innodb_file_per_table=1
innodb_large_prefix=true
innodb_file_format=barracuda
innodb_file_per_table=true
innodb_default_row_format=dynamic

The last one is an additional line which is not mentioned in the NC docs, but I find it useful as I really want this to be the default on my server. New tables are created ā€œcorrectlyā€ then anyway, if not explicitly set otherwise.

Especially for the SQL-Queries, you should copy them from the admin guide. As mentioned above, some characters have been removed here in this forum, when budy pasted them. The query actually contains backticks, which have been removed in his posts above.

USE INFORMATION_SCHEMA;

SELECT CONCAT("ALTER TABLE `", TABLE_SCHEMA,"`.`", TABLE_NAME, "` ROW_FORMAT=DYNAMIC;") AS MySQLCMD FROM TABLES WHERE TABLE_SCHEMA = "nextcloud";

But as mentioned: follow the guide for your own good, you donā€™t want to mess up your database :slight_smile:

Hello Schmu, The installed version is:
mysql Ver 15.1 Distrib 10.1.38-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2
which has been installed August 2018. I Update what Ubuntu tells me to update.

Then the congiguration file my.cnf referres to /etc/mysql/mariadb.conf.d/50-server.cnf on my ubuntu installation I gues. See below:
afbeelding
Hope to hear this correct?
I hope for some posts from Stefan.
Thanks!

In that case you can follow both sections:

  1. https://docs.nextcloud.com/server/15/admin_manual/configuration_database/mysql_4byte_support.html#enabling-mysql-4-byte-support
  2. https://docs.nextcloud.com/server/15/admin_manual/configuration_database/mysql_4byte_support.html#mariadb-10-2-or-earlier

The config file seems right to me.

Hi @Schmu, yeahā€¦ we were already past that. Ceesā€™ DB is <10.2, but the reason maybe, that the forum changes some codesā€¦ As Ceesā€™ NC instanceā€™ DB is simply named nextcloud, the following SQL should output the appropriate comannds to alter the tables:

SELECT CONCAT(ā€œALTER TABLE ", TABLE_SCHEMA,".", TABLE_NAME, " ROW_FORMAT=DYNAMIC;ā€) AS MySQLCMD FROM TABLES WHERE TABLE_SCHEMA = "nextcloud";

Hi to you all,
The first time I have seen how to fix this is on https://www.marksei.com/how-to-install-nextcloud-20-on-ubuntu/ where is described how to come around this issue without conversion afterwards of your database. When you setup your system, before you create your database do the following:

sudo nano /etc/mysql/conf.d/00-innodb.cnf

Paste the following text in the file:
[mysqld]
innodb_large_prefix=true
innodb_file_format=barracuda
innodb_file_per_table=1

Save the file and restart mariadb:

sudo systemctl restart mariadb

To secure the installation of MariaDB do the following:
sudo mysql_secure_installation
give it strong password and answer all the questions with Yes. Then continue with:

sudo mysql -u root -p

CREATE DATABASE nextcloud;
CREATE USER ā€˜ncrootā€™@ā€˜localhostā€™ IDENTIFIED BY ā€˜passwordā€™;
GRANT ALL PRIVILEGES ON nextcloud.* TO ā€˜ncrootā€™@ā€˜localhostā€™;
FLUSH PRIVILEGES;
EXIT;

This way you create the database from the beginning by default from the type Barracuda.

Maybe this helps some people. Regards, Cees