[[Support] MariaDB Official]

On 7/5/2024 at 4:23 PM, jowi said:

Found a way to change the root password. 

The whole parameter thing mentioned above doesnt work for me.

 

Open a putty or ssh connection to unraid, go to the app data folder for mariadb.

Edit the custom.conf file, add the entry 'skip-grant-tables' to the [mysqld] block.

Save the file & restart the container.

Open a console in unraid, enter: mysql

enter: use mysql;

then: UPDATE user SET `authentication_string` = PASSWORD('myNewPassword') WHERE `User` = 'root';

enter: exit

close the console

remove the line 'skip-grant-tables' from the custom.conf file, save it.

and restart the container

Open a new console, now you can enter mysql using:

mysql -u root -p

enter your password... voila.

Thanks a bunch @jowi, that actually did the trick!

adding the skip-grant-tables to the custom.conf file and restarting the container made it possible to log into the mariadb server from the containers shell. While the UPDATE command you mentioned did give me an err (about the authentication_string column not being updatable) I managed to reset the root password with the good old

FLUSH PRIVILEGES;

followed by a 

ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';

then I continued like you mentioned by removing the skip-grant-tables parameter from the custom.conf again and do a final restart of the container.

BR

tax

[[Support] MariaDB Official]

On 6/10/2021 at 9:33 AM, mgutt said:

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.

I am trying to reset my Maria root password but when I add the mysqld_safe --skip-grant-tables to the post arguments the Maria server dies right after start and therefore the container stopps again. Here's the end of the logfile:

 

2024-06-22  9:30:41 0 [Note] Server socket created on IP: '0.0.0.0'.
2024-06-22  9:30:41 0 [Note] Server socket created on IP: '::'.
2024-06-22  9:30:41 0 [ERROR] Incorrect definition of table mysql.event: expected column 'definer' at position 3 to have type varchar(, found type char(141).
2024-06-22  9:30:41 0 [ERROR] mariadbd: Event Scheduler: An error occurred when initializing system tables. Disabling the Event Scheduler.
2024-06-22  9:30:41 0 [Note] /usr/bin/mariadbd: ready for connections.
Version: '10.11.8-MariaDB-log'  socket: '/run/mysqld/mysqld.sock'  port: 3306  Alpine Linux
2024-06-22  9:30:42 4 [Warning] Aborted connection 4 to db: 'unconnected' user: 'unauthenticated' host: 'localhost' (This connection closed normally without authentication)
2024-06-22  9:30:42 0 [Note] InnoDB: Buffer pool(s) load completed at 240622  9:30:42
2024-06-22  9:30:42 0 [Note] /usr/bin/mariadbd (initiated by: unknown): Normal shutdown
2024-06-22  9:30:42 0 [Note] InnoDB: FTS optimize thread exiting.
2024-06-22  9:30:42 0 [Note] InnoDB: Starting shutdown...
2024-06-22  9:30:42 0 [Note] InnoDB: Dumping buffer pool(s) to /config/databases/ib_buffer_pool
2024-06-22  9:30:42 0 [Note] InnoDB: Restricted to 4048 pages due to innodb_buf_pool_dump_pct=25
2024-06-22  9:30:42 0 [Note] InnoDB: Buffer pool(s) dump completed at 240622  9:30:42
2024-06-22  9:30:43 0 [Note] InnoDB: Removed temporary tablespace data file: "./ibtmp1"
2024-06-22  9:30:43 0 [Note] InnoDB: Shutdown completed; log sequence number 29657073294; transaction id 103877563
2024-06-22  9:30:43 0 [Note] /usr/bin/mariadbd: Shutdown complete

 

While the two ERROR lines hint at an unfinished upgrade process I don't think these are the reasons for the shutdown the follows since right after the Errors there's a "ready for connections" entry.

But I can't figure out why else it would go down like this.

and fixing the Errors is not possible since I would need the root password for that to work.

 

The DB is actually still working (using it for my nextcloud) but I would like to add another user and DB to the server in order to utilize it for a second project

 

Does anyone have an idea how I  can unlock my root account again or are my only options to dump(backup) the Nextcloud DB and transfer it to a newly created instance?

 

thanks in advance for any tips

cheers