mysql_native_password deprecated

[heyted]

Upon installing the mythtv-backend-master package from the Mythbuntu PPA on Ubuntu 25.10, the connection to the database fails. I confirmed this issue with v35 and v36-master.

v36.0 master.202510190002.7c781b3860~ubuntu25.10.1
mysql 8.4.6-0ubuntu3

From backend log:

E CoreContext mythdbcon.cpp:247 (OpenDatabase) [DBManager0] Unable to connect to database!
E CoreContext mythdbcon.cpp:248 (OpenDatabase) Driver error was [1/1524]:#012QMYSQL: Unable to connect#012Database error was:#012Plugin 'mysql_native_password' is not loaded

Since the mysql_native_password authentication plugin in MySQL has been deprecated, it needs to be replaced with caching_sha2_password. The following workaround applied after installing the mythtv-backend-master package allows the connection to the database:

sudo mysql
CREATE USER 'mythtv'@'localhost';
ALTER USER 'mythtv'@'localhost' IDENTIFIED WITH caching_sha2_password BY '[password from /etc/mythtv/config.xml]';
GRANT ALL PRIVILEGES ON mythconverg.* To 'mythtv'@'localhost';
exit

Searching for mysql_native_password shows line 49 in packaging/deb/debian/mythtv-database.postinst has:
QUERY="CREATE USER IF NOT EXISTS '$mythtv_username'@'%' IDENTIFIED WITH mysql_native_password;
Does the above line need to be changed as shown below?
QUERY="CREATE USER IF NOT EXISTS '$mythtv_username'@'%' IDENTIFIED WITH caching_sha2_password;
Also, does the alter user line need to be changed as shown below?
mysql -e "ALTER USER 'mythtv'@'localhost' IDENTIFIED WITH caching_sha2_password BY '$password';"

[heyted]

Also, see the forum: https://forum.mythtv.org/viewtopic.php?p=29431#p29431

[rcrdnalor]

You probably installed the mythtv-backend-master package against an existing mythconverg database.
Otherwise, one should get this error:

Setting up mythtv-database (2:36.0~master.20250000.000000-0ubuntu0) ...
mysql 'hidden SECURITY_INFO' --execute=CREATE USER IF NOT EXISTS 'mythtv'@'%' IDENTIFIED WITH mysql_native_password; 
ALTER USER 'mythtv'@'%' IDENTIFIED BY 'XXXXXXXX';
GRANT ALL ON mythconverg.* TO 'mythtv'@'%'; mythconverg failed, (incorrect admin username/password or syntax?)
Try:
sudo dpkg-reconfigure mythtv-database

The problem when introducing the caching_sha2_password globally is backward compatibility to all possible combinations of supported MySQL and MariaDB versions, because the caching_sha2_password requires either a secure transport layer or an RSA key pair on the server and remote clients.
And that is a huge matrix for testing, because user reported significant degradation of performance on some systems.

[heyted]

It was a fresh install of Ubuntu 25.10 and v36-master. I first tested this with v35-fixes (v35.0+fixes.202510200603.18163eb520~ubuntu25.10.1). I then wiped the hard disk, installed Ubuntu 25.10, and installed the mythtv-backend-master package, which automatically installs mythtv-database.

/var/log/apt/term.log has:

Setting up mythtv-database (2:36.0-master.202510190002.7c781b3860~ubuntu25.10.1) ...
mysql 'hidden SECURITY_INFO' --execute=CREATE USER IF NOT EXISTS 'mythtv'@'%' IDENTIFIED WITH mysql_native_password;
ALTER USER 'mythtv'@'%' IDENTIFIED BY 'XXXXXXXX';
GRANT ALL ON mythconverg.* TO 'mythtv'@'%'; mythconverg failed, (incorrect admin username/password or syntax?)
Try:
sudo dpkg-reconfigure mythtv-database

[heyted]

It appears that if the mysql_native_password plugin is to be used, a script will need to enable it. Issue MythTV/mythtv#1076 mentioned the mysql_native_password plugin is disabled by default starting with MySQL 8.4.

https://dev.mysql.com/doc/relnotes/mysql/8.4/en/news-8-4-0.html confirms this:

The deprecated mysql_native_password authentication plugin is now disabled by default. It can be enabled by starting MySQL with the new --mysql-native-password=ON server option, or by adding mysql_native_password=ON to the [mysqld] section of your MySQL configuration file.
For more information, see Native Pluggable Authentication. (Bug #36337893)