DietPi-Software | MySQL: Remove also on Jessie with v6.0?

[MichaIng]

@Fourdee
Shall we remove MySQL also on Jessie with v6.0? As users anyway need to install a new image, we can directly force everyone to use MariaDB and bring them on the same stage. This allows us to skip all the annoying tests/adjustments caused by unix_socket authentication just for an oldstable Debian distro. I just faced that, while creating a ownCloud/Nextcloud full migration script to v6.0 😉.

I just tested migrating databases (ownCloud + Nextcloud) from Jessie MySQL to Jessie MariaDB and Stretch MariaDB and the instances work directly flawlessly, same as if you just do apt install mariadb-server on a Jessie MySQL machine to force migration.

[Fourdee]

@MichaIng

Shall we remove MySQL also on Jessie with v6.0? As users anyway need to install a new image, we can directly force everyone to use MariaDB and bring them on the same stage.

Sounds good to me 👍 Perfect time with image resets

[MichaIng]

I started with dietpi-software: https://github.com/Fourdee/DietPi/pull/1398

But more can be done, will go on later.

[MichaIng]

@Fourdee
Done so far. But during testing around with phpMyAdmin I realized the following:

• No web application should be able to use mysql "root" user to login. Thus all of them need their own user with related privileges.
• I don't know all the software titles that used create_mysql_db, but nearly all of them created the database with root user (see PR, where I removed admin password from the command).
• Does this mean the intention so far was, to login as mysql root to all of them? And which of them are web applications (using www-data) or use another unix user than root?
• I guess the idea behind phpMyAdmin is, that you can fully control your database from the web ui. Other than the manual dpkg-configuration says, the default user, created by APT installation does not have root/grant privileges. It is "phpmyadmin" with all privileged access just limited to phpmyadmin.*. I guess users were able to login via "root" "dietpi", because create_mysql_db was overwriting the unix_socket authentication to password "dietpi" before.

If others of our software use mysql root user as different unix user, or from web ui, then we have two options:

• Switch them to use their own database user with the privileges the really need. => Would be the recommended way for security reasons.
• Revert MariaDB to by default use root password authentication and leave all other software using this. => Would be simpler to handle within our scripts.

[MichaIng]

@Fourdee
As I thought, web based software fails with the strict unix_socket of MariaDB. Creating an own database user fixed it. Tested with Ampache and Koel.

As I understand open issues, your plan is anyway to use own users for our software titles (if applicable), as well as switching to dietpi as default user? Ui the latter will need much adjustments 🙈.
But in that case it fits anyway, that also for database access, separate software titles use separate database users. The overhead is minimal anyway. If you agree to do it that way, I will create PR for this. But some settings from http://dietpi.com/downloads/conf/ (Ampache at least) need to be adjusted by you then 😉.

The alternative again would be to rollback using MariaDB root user with password to re-enable access for our software titles as is.

[Fourdee]

@MichaIng

As I understand open issues, your plan is anyway to use own users for our software titles (if applicable), as well as switching to dietpi as default user? Ui the latter will need much adjustments

Yep, plan to create unique users for outstanding root based items in dietpi-software.
dietpi user for desktops etc

The alternative again would be to rollback using MariaDB root user with password to re-enable access for our software titles as is.

For now, probably best?
The permission/user changes across DietPi will take time and much testing, probably best we dedicate a release milestone to it sometime in future, do it all in one pass.

[MichaIng]

@Fourdee
It is relatively easy to change the affected software titles. Just change username/password in create_mysql_db call and change the related configs. Easy to find the packages also due to REQUIRE_MYSQL value.
I will start with PR and do required testing. Already done for two titles, so... And revert also needs a bid effort. And if we do later, than we need to touch the database and configs on update, which is also kind of risky.

If I face unexpected issue somewhere, we will postpone it.

€: Absolutely no issues. In very most cases, users just need to enter the new user name instead of "root" within web ui installer. Just 2 config files need to be adjusted: https://github.com/Fourdee/DietPi/pull/1418