Resolved "Column count of mysql.proc is wrong" : Cannot export database from Plesk

[PeterK900]

Server operating system version

CentOS Linux 7.9.2009

Plesk version and microupdate number

18.051

The Database Export option within Plesk shows this error. Exporting from phpMyAdmin works fine.



Tools and settings > Database Servers shows the MariaDB version correctly as 10.5.22. It was manually updated from the default build version 5 which might well have been 5.05.22 - as per the error message. But the DB was built in 10.5 not 5.05! And MySQL Workbench gives the same error, refusing to display schema and data.

Any thoughts on what I can do to put this right? The Plesk DB export isn't a serious issue because I can use phpMyAdmin. But I really would like to use MySQL Workbench again, like I did when the DB was MySQL.

 

[scsa20]

Have you tried running that mariadb-upgrade command that was noted in the error?

 

[PeterK900]

I'm sure you have the answer. Thanks. I read the message quickly and didn't see the connecting hyphen in mariadb-upgrade and so didn't pick up it was a utility, not an instruction to 'upgrade MariaDB'! mariadb-upgrade is documented here. Compicated, but it looks like most of the parameters are optional []...

mariadb-upgrade [--force] [--user=# --password=#
--host=hostname --port=# --socket=#
--protocol=tcp|socket|pipe|memory
--verbose] [OTHER_OPTIONS]
so # mariadb-upgrade should work if run with root privileges. I just need to find which directory to run the command from.

mariadb-upgrade needs to run with the Mariadb running.

Mariadb 10.5 has an option --check-if-upgrade-is-needed which I'll run first. but I'm sure the answer will come back "Yes". One of the symptoms of the need to run the upgrade utility is "Errors in the error log that some system tables don't have all needed columns." Exactly my problem.

I need to find a testing server before doing this in production. I'll post again with the results!

Thanks again for your help on this.

 

[scsa20]

It shouldn't matter what directory you're in since it will upgrade all databases to to the installed version.

If this is a VPS please make sure you have a snapshot of your server before trying it.

 

[PeterK900]

Thanks scsa20.

When I run mariadb-upgrade as root, I get a fatal error...



Same error when using PuTTY. When I insert the credentials ( user=, password=, host= ) for root or a user the administrator privileges, I get the same error.

The instructions say the user must have write access to the data directory. Is this the problem? I thought root had access to/could do everything on the server? How can I check that write access to the data directory is enabled?

Also, if I start entering parameters in the square brackets, do I need to enter them all? I'm OK with user, password and host but where do I find the others - port, socket, protocol?

I hope I'm not being as dense as I was over not spotting the utility mariadb-update!

 

[Peter Debik]

# mariadb-upgrade -u admin -p

The password be found in /etc/psa/.psa.shadow

 

[PeterK900]

Thanks, Peter.
I have found .psa.shadow but it's a long string and I'm not clear which bit is the admin password. None of the characters correspond to any password I've created. Here's the string...



I've tried the various bits separately but each time I get access denied.

I found this post which I thought might help. It seems that the password is the string after the second $. But these are hashed paswords aren't they? In the article it gives an example with the password abacadabra and then shows a long hashed string. Surely I want abacadabra not the hashed string?

I then created another admin user in Plesk. But this didn't work either.

Sorry if I'm missing something obvious but I'm not sure where to go one this.

 

[Maarten.]

It is a hashed password for security. But it is the password that plesk uses to access the database.

Try this:

# mariadb-upgrade -u admin -p `cat /etc/psa/.psa.shadow`

 

[PeterK900]

Thanks Maarten. And for the hashing explanation. I never realised Plesk had its own, internally generated, db access password.

Your command line looked really hopeful but, sadly, it doesn't pick up the password location, it still prompts for entry.

 

[Peter Debik]

@PeterK900 you can simply copy-paste the content from /etc/psa/.psa.shadow into the password prompt or parameter. You do not need to pipe it through a Linux command.

 

[PeterK900]

Spot on Peter! Pasting the entire contents into the password prompt gets mariadb-upgrade to run successfully. Thank you. And thanks to everyone else who've solved this for me. And increased my Plesk knowledge - never knew about auto-generated admin password or ever looked at .psa.shadow.

 

[scsa20]

Good to hear it's resolved now. I marked the thread as resolved for you. o7