Tried to upgrade mySQL 5.6 to 5.7; mySQL service and databases appear to be gone

Question

Went through the mySQL upgrade to 5.7 process, but it ran into an error and when I tried to rerun the setup, it couldn't connect to the 5.6 service and I've been unable to view our database.

I've restored the files in the C:\Program Files\MySQL\MySQL Server 5.6\data folder, but mySQL doesn't seem to recognize that our Confluence database is in there.  Is there some other place where the files would be stored?  I did have a .sql file for the database schema, but was getting a large file error when trying to export the actual DB data into an .sql file, so I don't have that available to re-import.

Can someone help me?

Nic Brough -Adaptavist- · Answer

Between us, we do have a lot of MySQL experience, but we're mostly here to talk Atlassian, and a lost MySQL database is a bit off-topic.   Some of us may well have run into this (whether it was an Atlassian database or something else), but it is not the focus of what most people are here for.

I responded because you had been left hanging for a few days and I did not want to let you get the impression the community is ignorant.

A lot of us have read it, but it's probably been missed by the handful who have relevant experience.  Hence the suggestion of a MySQL forum - a much higher proportion of readers are likely to have seen it and be able to help!

But, on to trying to get your Confluence back.  t sounds like you've resuced most things, but some BLOBs are failing - your instinct to run the installer and start clean is a good one in my opinion.  Although you should absolutely back up everything before attempting it.  Ideally, do the lot on a separate server!

Mike Farver · Answer

I understand.  I was hoping someone here had some MySQL experience.

I have managed to migrate the entire Confluence database from our backup, except for the plugindata table.  It can't dump the BLOBs for the various plugins and so can't import them.  The table is about 118MB, and even changing the max packet size to 1GB, it won't allow me to export the sql dump.

Would it be possible to run the installer for Confluence overtop the existing (broken) installation so that it regenerates the base entries in the plugindata table and then I can reinstall the other plugins?  Or could we install Confluence fresh on another system and import our spaces, documents, and attachments somehow?

Nic Brough -Adaptavist- · Answer

I'm afraid you've had no answer here because this isn't really an Atlassian question.

Something has broken in your MySQL upgrade and you seem to have lost a database.  I've always run from database dumps, so when I've had errors like this before, I've been able to restore from them.  I don't know much else about recovering MySQL databases.  Maybe a MySQL forum is a better place to ask?