Create mysqldump backup - innodb_large_prefix problem

ℹ️ Support
1

Hi,

I am creating database backups with cron, the command

/usr/bin/mysqldump --lock-tables -h 127.0.0.1 -u root -**** owncloud > $daily0/owncloud-sqlbkp_date +"%Y%m%d".bak

By starting my script manually, I noticed this error message:

mysqldump: unknown variable ‘innodb_large_prefix=on’

Is something misconfigured? How can I fix this error, the backup is only an empty file.

2

Try to check this:
https://docs.nextcloud.com/server/stable/admin_manual/maintenance/backup.html#mysql-mariadb

3

I tried manually with the commands from the doc, but the error message remains.

4

try this:
sudo -u www-data php occ maintenance:repair
if get error the try to fix it
if there is not try:

if not then try:

or maybe this:

good luck

1 Like
5

Thanks, maintenance:repair could not find any issues.

But I just noticed, the error comes by executing: :roll_eyes:

sudo mysql -u root -p
mysql: unknown variable ‘innodb_large_prefix=on’

What can I do now?

6

For older versions you need to edit the MySQL config file and restart and then manually convert each table.

There is a guide to do that.

7

Where is the guide?
It is mariadb-server-10.3 installed on debian buster.

8

https://docs.nextcloud.com/server/17/admin_manual/configuration_database/mysql_4byte_support.html

1 Like
9

I needed to remove all options, including innodb_file_per_table=1 from the file /etc/mysql/my.cnf

#innodb_large_prefix=on
#innodb_file_format=barracuda
#innodb_file_per_table=1
#innodb_large_prefix=1
The documentation mentioned, that this option should be set, but this is default in MariaDB as far as I know. https://mariadb.com/kb/en/innodb-system-variables/#innodb_file_format

MariaDB [(none)]> show variables like 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.006 sec)

The Database is already utf8mb4

MariaDB [(none)]> show variables like "character_set_database";
+------------------------+---------+
| Variable_name          | Value   |
+------------------------+---------+
| character_set_database | utf8mb4 |
+------------------------+---------+
1 row in set (0.007 sec)

Updated my backup command:
/usr/bin/mysqldump --default-character-set=utf8mb4 --lock-tables --single-transaction -h 127.0.0.1 -u root -p**** owncloud > $daily0/owncloud-sqlbkp_date +"%Y%m%d".bak

I hope anything fine now. :smile: