CODEWITHSUNDEEP
mysqlopenshift
Tatyana Milkina
Tatyana Milkina

Reputation: 1

Openshift 3 mysql.user table is damaged. please run mysql_upgrade openshift

Issue

When starting a mysql deployment on OpenShift V3 I get the following exception:

mysql.user table is damaged. Please run mysql_upgrade

I cannot run mysql_upgrade as pod isn't ready.

Questions

I have the following questions

Upvotes: 0

Views: 293

Answers (1)

Jiri Fiala
Jiri Fiala

Reputation: 1400

If the pod won't start, you can mount the volume with your data to another pod and download (oc rsync, interactive tutorial here) what you had mounted in the database pod under /var/lib/mysql/data/. Then, you can try recovering the data from that.

Generally, this could happen if you process an older database dump sql script (created using mysqldump) on a newer MySQL version. In that case, chances are the root user was removed from the table too (if it was not in the old database). If you created such a dump, still have the older dump, and it's "good enough", you should be able to proceed as follows to import the original data again and prevent this situation:

  1. Create a backup copy of the database dump that you have previously created using mysqldump from the older MySQL database version, so that you can always get back to it, if things go south.
  2. Edit the database dump sql file and remove all the content that manipulates the mysql.user table; that is, delete lines under the -- Table structure for table 'user' and -- Dumping data for table 'user' sections and save the modified file. I assume here that you have your user and password specified in environment variables, in the MySQL deployment configuration).
  3. Scale down your database pod to 0 replicas.
  4. Delete your mysql persistent volume claim; this will delete the database that you have hopefully downloaded after mounting the volume to another pod, as mentioned above.
  5. Recreate the PVC, under the same name.
  6. Scale up your MySQL pod to one replica. That will initialize the database and create a user as per the environment variables .
  7. Copy the modified sql dump file created in step 2 (that is, the one not affecting the mysql.user table) to the database pod using oc rsync.
  8. In the MySQL pod, restore the database using the uploaded file, as per this migration guide (step 6).
  9. Grant all privileges to your user on the application database by GRANT ALL PRIVILEGES ON <database> TO '<mysql_user>'@'%' (Replace <database> and <mysql_user> appropriately.)
  10. Exit the MySQL CLI on the pod, and run mysql_upgrade -u root in the shell.

Upvotes: 1

Related Questions