Accidenal Update of mySQL

Submitted by paul.kelly on Sun, 08/08/2010 - 18:31

I thought that I had already posted this, but can't find it - apologies if a duplicate.

I believe accidentally upgraded my mysql to 5.1.48 after I had added a new repository to add a specific piece of software.

Everytime I run a backup, I now get, on some domains:

. dump failed! mysqldump: Couldn't execute 'SHOW FUNCTION STATUS WHERE Db = 'tradecreative_com'': Column count of mysql.proc is wrong. Expected 20, found 16. Created with MySQL 50077, now running 50148. Please use mysql_upgrade to fix this error. (1558)

What is the safest way to revert to the 'approved' VM version?

Status: 
Closed (fixed)

Comments

Submitted by JamieCameron on Sun, 08/08/2010 - 18:36

Do you happen to know which version you upgraded from before? If it wasn't 5.1.something, then downgrading may be difficult, due to changes in the underlying MySQL data file format..

Submitted by paul.kelly on Sun, 08/08/2010 - 18:38

sorry, no (not unless you can point me to a file).

it should be the standard VM version, as I have only used standard repositories.

If I was to run the upgrade mysql instruction, do you see an issue with running hat version?

Joe's picture
Submitted by Joe on Mon, 08/09/2010 - 16:44 Pro Licensee

There is no Virtualmin MySQL version (nor is there a "Virtualmin approved version"). We use whatever MySQL you have installed, which is usually the one provided by your OS vendor (which we do tend to recommend, so I guess that would be the "approved" one, though we support pretty much all MySQL versions).

In this case, upgrading without following the upgrade procedures has led to incompatibilities between your database and the MySQL version installed. It's not a Virtualmin error you're seeing, but a MySQL one.

Virtualmin won't have a problem with running a different version, but I'm always hesitant to move away from the OS-standard packages without really good reason, and to avoid third party repositories except when necessary and then only when also using the include/exclude features to insure you only get the packages you need from that repo rather than whatever packages happen to be in it. Third party repos tend to be a lot less actively maintained, and dramatically less well-tested than the OS packages. It's a matter of several millions of users vs. a few hundred, usually, and several hundred or thousand maintainers vs. maybe a couple of people who do it in their spare time.

I don't know enough about MySQL to know if you could go backward at this point, but I'm kinda thinking the answer is "no". Once the new version has started writing data to the database, it tends to blow away backward compatibility.

Submitted by JamieCameron on Mon, 08/09/2010 - 17:24

You may be able to remove the new repository from your YUM configs and then run something like :

yum install mysql-5.0.22-2.2.el5_1.1 mysql-server-5.0.22-2.2.el5_1.1

However, I can't guarantee that this won't corrupt your data, so I would recommend fully backing up /var/lib/mysql first.

Submitted by paul.kelly on Tue, 08/10/2010 - 02:55

Thanks for the comments.

I thought I read somewhere that during the VM install, it managed what software versions were installed - I obviously dreamt (or misread) that!

Anyway, I have fully backed up and, first, ran mysql_upgrade and that seems to have stopped the errors and allowed backup to continue, without error.

If I see any further errors or corruption I will try to remove the repositiory.

Thanks for the pointers!