Difference in the same MariaDB version?

[Richard G]

We had a 5.7 mysql running. I tried to upgraded it and SMtalk fixed it for me and it's running fine on MariaDB 10.2 now.

Last night we wanted to do an admin backup/restore to a new server which was ready (but now having some quota issuse).

Alle databases were restored, some with errors, so I rebuild mysql and restored users again, but I keep getting error connecting to the database.
So I had a look via the da_admin user if there were any differences and then I see this.

On the old server it looks like this (I used the xxxx to mask, don't know if they are passwords):

mysql_native_password | *481110441517xxxxx93CC5496xxxxxxxxx
| localhost              | user12_wp1    |

on the new server it looks like this:

| localhost | user12_wp1       | *0108545B2DC7xxxxx7BF6E56EF4xxxxxxxx

I think the issue is here that on the new server the mysql_native_password is not present.

What is causing this issue?
Both are MariaDB 10.2.26

I can do what I want, keep getting this for every user:

ERROR 1045 (28000): Access denied for user 'user12_wp1'@'localhost' (using password: YES)

How can this be fixed?

 

[wattie]

Using the da_admin user:

USE mysql;
SELECT user, password, plugin FROM user;

This will give you the full list of all users, their HASHED passwords and the type of login they are using. Old MariaDB installs use mysql_native_password, new installs use more modern and more secure methods (ed25519).

I see that your password hashes are different. That means that the restore definitely did something to them and (possibly) made them unusable. Updating the password for every single database through DirectAdmin should fix the issue... but it will be painful experience if they are too much.

 

[Wanabo]

Could this work? Copy pasting password hashes directly in the database?

Reading so much database upgrading trouble that I'm hesitating to upgrade MariaDB 5.5.41 to 10.x.
The MariaDB 5.5.41 came standard with CentOS 7 and is not (I think) controlled by CustomBuild.

 

[ikkeben]

Could this work? Copy pasting password hashes directly in the database?

Reading so much database upgrading trouble that I'm hesitating to upgrade MariaDB 5.5.41 to 10.x.
The MariaDB 5.5.41 came standard with CentOS 7 and is not (I think) controlled by CustomBuild.

Don't know.

More things to consider, if your CMS is using with (older yes or no updated) PHP versions to and login / hashing / blowfish whatever system something to old of not compatible to.

YES no innnodb, collisionsset....


Sorry Richard to interfere, i don't think this is with your box so i.m not of any help for you.

Maybe to find out wich password parts are giving when problems. ( is lot of extra work , then you can do the again lot of extra work workarrounds as WATTIE described.

( the way i do test , is making new testdatabase and database user to then import there the backuped databse in DA GUI , then look if you can login after that with phpmyadmin trying to change database password in DA GUi , then if it still works, with copy changed install cms to that database try to use pasword forget function for cms admin if no working anymore and so on...)
( take care then have same collision and yes no innnodb and so on before import)

Describing where php and cms versions changed to the complete password way could be different then before if such was changed once.


Did you contacted support and SMTALK?

( don't know if ssl versions themselves could have make passwords different as hashes) ??????

 

[Richard G]

I see that your password hashes are different.

Maybe not, I just took some lines from different users and presented them as from the same. It was just to show that one database had those "mysql_native_password" lines and the other did not.

I did not contacted support yet, first I'm going to give it another try. Building the server again at this moment, as I also had quota errors. Seems suddenly quota did not work either, no idea why, but also with the help section I did not get that fixed. And since it was recently installed I try again.

I'm just wondering, since it's the same databases and users, if it's not possible to just rsync the complete /var/lib/mysql directory from the old to the new server.

 

[Richard G]

Just had a check on 1 other Centos 7 server we have, and they don't have the mysql_native_password in there. Maybe it needs converting first? I don't know.

 

[wattie]

I'm hesitating to upgrade MariaDB 5.5.41 to 10.x.

On my FreeBSD server during the years I did the following upgrade path: MySQL 5.4 -> MySQL 5.5 -> MariaDB 5.5 -> MariaDB 10.0 -> MariaDB 10.1 -> MariaDB 10.2 -> MariaDB 10.3 -> MariaDB 10.4

It was always flawless and I never experienced issues with any website (I have many ancient websites running together with modern Wordpress/Joomlas/etc).

But who knows.

MariaDB 5.5 will be supported up to 11 Apr 2020. You have few months to prepare, test and upgrade. I would not recommend staying on the 5.5 branch after April the next year because eventual security holes will not be patched since then.

 

[Richard G]

I also did from MariaDB 5.6 to 10.0 without issues. But that's a different ballgame.
From 5.7 you can't use 10.0 you have directly to go to 10.2 which is the first compatible one with mysql 5.7.

I'm not at 5.5 but with 1 server on 5.7, the others all run 10.2 that's why I wanted to change, to be overall compatible and possible to interchange accounts without issues.