[Support] MariaDB Official

[mgutt]

Overview: Support for MariaDB Official docker container

Docker: https://hub.docker.com/_/mariadb

Github: https://github.com/MariaDB/mariadb-docker

 

This is the official MariaDB container which sets by default a secure random root password and the user is able to set the database name, user and password through the container's settings, which allows an easy installation.

 

Donate? 🤗

 

 

Nextcloud Example

 

Note: You find the random root password ONLY after the first boot in the container's logs:

Connect to MariaDB through Terminal

Execute the following and enter your root password:

docker exec -it MariaDB-Official mariadb -u root -p'your_root_password'

 

 

[mgutt]

Set Root Password

 

If you like to manually set your root password remove the word "yes" from "Generate Random Password":

 

Then set your password through "Show more settings":

 

Note: This does not allow to change the password.

 

Reset Root Password

 

Edit the container, enable advanced view and add "mysqld_safe --skip-grant-tables" to the Post Arguments and start the container:

 

Open the cotnainer's console and execute the following commands:

mariadb -u root

 

Now you are connected to MariaDB. Execute this:

FLUSH PRIVILEGES;

 

And finally this:

SET PASSWORD FOR root@'localhost' = PASSWORD('your_new_password');

 

 

Finally edit the container and remove the "mysqld_safe --skip-grant-tables" part again.

[thymon]

Hello !

 

If I need other Database ? I must install other MariaDB docker ?

[mgutt]

9 minutes ago, thymon said:

If I need other Database ?

You mean, if you need two databases? Then install the container twice, change the name and the paths in both containers. This would be the easiest option. For example you could name one container "nextcloud_db" and the other "npm_db" and so on.

[thymon]

ok nice ! That's what I thought.

[obowan]

Hello,

 

I setup this docker container to work with NextCloud. I have been happy with the results until I updated this morning. Reviewing the logs, I see the error "Incorrect definition of table mysql.event: expected column 'definer' at position 3 to have type varchar(, found type char(141)." After doing a little digging, I've determined I likely need to run the "mysql_upgrade" command to resolve this issue. However, I stupidly did not document the random root password generated when I originally installed the container so cannot perform any commands that require root access. Is there any way for me to recover or reset that root password? I've been researching how for hours and can't seem to find the way. Thank you in advance

[mgutt]

2 hours ago, obowan said:

Is there any way for me to recover or reset that root password?

I updated my post:

https://forums.unraid.net/topic/110019-support-mariadb-official/?tab=comments#comment-1004285

 

[obowan]

I now have access to my root user and was able to perform the my_sql upgrade and start MariaDB with no errors. Thank you so much for the quick help!

[Vashot]

Hi, 

 

today I upgraded the MariaDB-Official Container to the latest version. I use it to run Nextcloud and Gitea but after the upgraded it I got the following error in Nextcloud (Gitea just works fine):

SQLSTATE[HY000] [2002]

php_network_getaddresses: getaddrinfo failed: Name does not resolve in /config/www/nextcloud/lib/private/DB/Connection.php:85

 

I changed nothing in my Nextcloud configuration. I connect to the database by using the hostname of the MariaDB-Official container (what still works fine with Gitea).

Both containers are in the same docker network. I already tried to give the MaraiDB-Official container a static IP address in the docker network and connect Nextcloud by this address but this didn't work.

 

Meanwhile I rolled back to MariaDB-Official 10.5.11 and it works fine at the moment.

 

What can I do? I'm a little bit overloaded with this problem because I do not know what else to do.

 

Many thanks in advance.

[mgutt]

On 7/15/2021 at 10:52 AM, SynIQ said:

a static IP address in the docker network and connect Nextcloud by this address but this didn't work.

And which error was returned after you used the IP instead? Because "Name does not resolve" is a really clear error message. But it's strange that it happens only with the new version of the container.

 

On 7/15/2021 at 10:52 AM, SynIQ said:

What can I do?

 

Stop Nextcloud and Gitea. Stop MariaDB. Execute this, to copy the container files:

mkdir /mnt/user/appdata/MariaDB-Copy
cp -a /mnt/user/appdata/MariaDB-Official /mnt/user/appdata/MariaDB-Copy

 

Rename MariaDB to "MariaDB-Live" or whatever. By renaming the container it becomes available for a second installation:

 

Install the container a second time and name it "MariaDB-Copy" and change all paths (!):

 

Now you have a container copy to play with. Upgrade it.

 

Open the Nextcloud console and use "curl name:3306" or "curl ip:3306" to check the connection to this db container. What are the results? And what are the logs of the upgraded mariadb container?

[Vashot]

Hi and thanks for your answer. 

 

I tried it over the ip address but I still got the same error:

 

2021/07/15 10:05:41 [error] 414#414: *51529 FastCGI sent in stderr: "PHP message: PHP Fatal error:  Uncaught Doctrine\DBAL\Exception: Failed to connect to the database: An exception occurred in the driver: SQLSTATE[HY000] [2002] php_network_getaddresses: getaddrinfo failed: Name does not resolve in /config/www/nextcloud/lib/private/DB/Connection.php:85
Stack trace:
#0 /config/www/nextcloud/3rdparty/doctrine/dbal/src/Connection.php(1486): OC\DB\Connection->connect()
#1 /config/www/nextcloud/3rdparty/doctrine/dbal/src/Connection.php(1014): Doctrine\DBAL\Connection->getWrappedConnection()
#2 /config/www/nextcloud/lib/private/DB/Connection.php(226): Doctrine\DBAL\Connection->executeQuery()
#3 /config/www/nextcloud/3rdparty/doctrine/dbal/src/Query/QueryBuilder.php(210): OC\DB\Connection->executeQuery()
#4 /config/www/nextcloud/lib/private/DB/QueryBuilder/QueryBuilder.php(287): Doctrine\DBAL\Query\QueryBuilder->execute()
#5 /config/www/nextcloud/lib/private/AppConfig.php(345): OC\DB\QueryBuilder\QueryBuilder->execute()
#6 /config/www/nextcloud/lib/private/AppConfig.php(1" while reading response header from upstream, client: <Client-IP>, server: _, request: "PROPFIND /remote.php/dav/files/<username>/ HTTP/1.1", upstream: "fastcgi://127.0.0.1:9000", host: "<Host-IP>"

 

I also tried your suggestion but with no success. I got the following error with both, the hostname and the ip address. 

 

 

After I ran the command the logs of MariaDB say the following but the same error appears when I run the command from Nextcloud container to the older working MariaDB container.

 

2021-07-17 20:00:04 4 [Warning] Aborted connection 4 to db: 'unconnected' user: 'unauthenticated' host: '172.18.0.4' (This connection closed normally without authentication)

 

This means that the Nextcloud container can reach the MariaDB container although the errormessage says that Nextcloud can't resolve the hostname?When I try to make an action on the webinterface of nextcloud connected to the latest container of MariaDB the error appears and there is no new record in the log of MariaDB. Thats so confusing... I've never had somthing like this bevor.

 

I tried the same command from another container (phpMyAdmin) and there the command has apparently been successful. 

 

They are all in the same docker network. 

 

Edited July 17, 2021 by SynIQ

[mgutt]

1 hour ago, SynIQ said:

Thats confusing...

For me, too. I installed Nextcloud and MariaDB to test your situation, but I have no problems to connect the DB:

 

It even works in a custom network created as follows:

docker network create --subnet=172.20.0.0/24 nextcloud

 

The alias, too (not sure why those "Expires" appear, but the connection itself is possible):

 

Your updated container runs with MariaDB 10.6.3-focal?

[Vashot]

Thank you very much for testing it again.

 

Until now I used the "latest" tag but I tried the "focal" tag and it didn't work for now.

 

Since there seems to be no fundamental problem I will keep trying to get it work for me. Now it looks like the problem is nextcloud itself.

If I may find the problem I will report it here. 

 

Thank you very very much for your Help  

[mgutt]

24 minutes ago, SynIQ said:

Thank you very much for testing it again.

No problem. Maybe you try the following:

 

Export all databases as sql file:

docker exec MariaDB-Official sh -c 'exec mysqldump --all-databases -uroot -p"YOUR_ROOT_PASSWORD"' > /mnt/user/Backups/all-databases.sql

 

Now remove your MariaDB Offical Docker and rename the container's files:

mv /mnt/user/appdata/MariaDB-Official /mnt/user/appdata/MariaDB-Official-Backup

 

Remove the MariaDB Template through Apps -> Previous Apps

 

Now re-install MariaDB Official with the recent version and import your backup:

docker exec -i MariaDB-Official sh -c 'exec mysql -uroot -p"YOUR_ROOT_PASSWORD"' < /mnt/user/Backups/all-databases.sql

 

Maybe this helps to solve your problem. If not, remove the container again and delete the new container files as follows:

rm -rI /mnt/user/appdata/MariaDB-Official

 

then rename the old files and install the container with the older working version again:

mv /mnt/user/appdata/MariaDB-Official-Backup /mnt/user/appdata/MariaDB-Official

 

[Vashot]

Now I know what's the problem here. 

 

I've tried this and got a new type of error.

20 hours ago, mgutt said:

Export all databases as sql file:

docker exec MariaDB-Official sh -c 'exec mysqldump --all-databases -uroot -p"YOUR_ROOT_PASSWORD"' > /mnt/user/Backups/all-databases.sql

 

Now remove your MariaDB Offical Docker and rename the container's files:

mv /mnt/user/appdata/MariaDB-Official /mnt/user/appdata/MariaDB-Official-Backup

 

Remove the MariaDB Template through Apps -> Previous Apps

 

Now re-install MariaDB Official with the recent version and import your backup:

docker exec -i MariaDB-Official sh -c 'exec mysql -uroot -p"YOUR_ROOT_PASSWORD"' < /mnt/user/Backups/all-databases.sql

 

While importing the file into the new container with the latest version I got this:

ERROR 4047 (HY000) at line 3357: InnoDB refuses to write tables with ROW_FORMAT=COMPRESSED or KEY_BLOCK_SIZE.

 

When I tried to import the same file to a new container running MariaDB 10.5.11 the import was successful.

 

So I startet to research a bit for this error and found some posts in the nextcloud community with similar problems in conjunction with MariaDB 10.6.3. It seems there is a problem with the compression and the charset.

 

The line which was referenced in the error above was the beginning of table containing the nextcloud users. Now I think the error I had (SQLSTATE[HY000] [2002] php_network_getaddresses: getaddrinfo failed: Name does not resolve in /config/www/nextcloud/lib/private/DB/Connection.php:85) referenced not the hostname of MariaDB but the name of the current user in the database table.

 

After setting the charset using 

docker exec -it nextcloud bash
sudo -u abc php /config/www/nextcloud/occ config:system:set mysql.utf8mb4 --type boolean --value="false"

and 

sudo -u abc php /config/www/nextcloud/occ maintenance:repair --include-expensive

 

Nextcloud works with the latest image of MariaDB but warning this configuration may cause issues with 4-byte characters like emojis.

 

Again many thanks for your help @mgutt. Without your help I probably would not have come up with a solution.

Edited July 19, 2021 by SynIQ

[Anigma]

Could it be that the path to /etc/mysql/conf.d should be pointin to /etc/mysql/mariadb.conf.d ? Because the official mariadb image seems to work with the mariadb.conf.d and not with conf.d

At least the conf.d folder is empty in my installation.

[mgutt]

On 8/4/2021 at 10:14 PM, Anigma said:

Because the official mariadb image seems to work with the mariadb.conf.d and not with conf.d

 

Regarding the official description of the container /etc/mysql/conf.d is correct:

Quote

The startup configuration is specified in the file /etc/mysql/my.cnf, and that file in turn includes any files found in the /etc/mysql/conf.d

 

And as you can see both are included:

cat my.cnf 
# The MariaDB configuration file
...
!includedir /etc/mysql/mariadb.conf.d/
!includedir /etc/mysql/conf.d/

 

And as "/etc/mysql/mariadb.conf.d/" contains files by default, it would be a bad idea to mount this path to an empty path on your host.

 

 

[mgutt]

How to solve Nextcloud incompatibility with recent MariaDB version:

 

Create the file "nextcloud.cnf" in the folder "/mnt/user/appdata/mariadb-official/config" with the following content:

[mysqld]
innodb_read_only_compressed = "OFF"

 

Restart the MariaDB container and it should work again. Found here.

 

[Braulio]

@mgutt

I need to change the .cnf file, but I couldn't find it in the folder /etc/mysql. The folder doesn't exist.

Can you help me?

[mgutt]

1 hour ago, Braulio said:

I need to change the .cnf file

You can't permanently change files that are inside of a container. That's why you should create a cnf file in "/mnt/user/appdata/mariadb-official/config" as described in my last post. This path is mounted through the container, so files in this path aren't lost if the container is restarted / updated as it would happen to files in /etc/mysql

 

1 hour ago, Braulio said:

The folder doesn't exist.

It does. You probably used the unRAID Terminal instead if the container's console?!

 

 

[Braulio]

9 hours ago, mgutt said:

You can't permanently change files that are inside of a container. That's why you should create a cnf file in "/mnt/user/appdata/mariadb-official/config" as described in my last post. This path is mounted through the container, so files in this path aren't lost if the container is restarted / updated as it would happen to files in /etc/mysql

 

It does. You probably used the unRAID Terminal instead if the container's console?!

 

 

now i undertood....tks my friend 

[x3n0n]

On 8/16/2021 at 1:24 PM, mgutt said:

How to solve Nextcloud incompatibility with recent MariaDB version:

 

Create the file "nextcloud.cnf" in the folder "/mnt/user/appdata/mariadb-official/config" with the following content:

[mysqld]
innodb_read_only_compressed = "OFF"

 

Restart the MariaDB container and it should work again. Found here.

 

 

chmod of this file needs to be 644, i created it with nano and it wasn't so mariadb ignored it.

 

chmod 644 /mnt/user/appdata/mariadb-official/config/nextcloud.cnf

 

[4n0nyM]

When I sync my data from Nextcloud (data is mounted as external storage in Nextcloud) on my PC I recently get:
(An exception occurred while executing a query: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction)
This is what it looks like in the Nextcloud client:

 

After the Nextcloud client resynchronized itself twice more, the error was gone.
Does anyone know why this error occurred or how I can fix it?

According to the error message, it should be my database or?

 

Edit:

I'm setting up the MariaDB and Nextcloud Docker all over again.

 

Edit:

That' s a pity, unfortunately it didn't help.

Edited October 6, 2021 by 4n0nyM

[mgutt]

8 hours ago, 4n0nyM said:

When I sync my data from Nextcloud (data is mounted as external storage in Nextcloud) on my PC I recently get:
(An exception occurred while executing a query: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction)

I don't know if it's active by default, but try to disable binary logging:

https://geekflare.com/disable-binary-log-mariadb/

 

Regarding my research it can help to solve this issue.

 

Other people solved it by re-installing. Maybe you try to export the database, reinstall and import the database ?!

[4n0nyM]

Thanks for your quick reply 🙂
Found the mariadb.cnf file, but don't know how to edit it. The file cannot be opened with nano. And in Krusader I do not have the /etc folder in the MariaDB directory in the appdata folder.

 

Actually, I had already reinstalled MariaDB today. But maybe I didn't have it deleted properly.

First I had removed the MariaDB Docker, then deleted the created folders, with the plugin "Cleanup Appdata" and after that I checked again with Krusader, if there really is no MariaDB folder left in the appdata folder. Then I installed the official MariaDB Docker again and set up Nextcloud again.

I would try that again, but I have other databases in MariaDB. The easiest way is probably to export each database with phpMyAdmin and once I have reinstalled the MariaDB Docker, import again via phpMyAdmin or?

 

Edited October 6, 2021 by 4n0nyM