Few weeks ago I started up a project migrating a development mysql to the production environment.
Doing this change included a lot of tests and updates I wanted to do for a long time.
The short story is that I migrated user defined mysql data (permissions and triggers was vetted before) to Master Slave on docker images and a different user with the entire environment configured via Ansible (yes yes I went overboard with the design – but hey, now I have all the templates I need and every docker and mysql needs and documentation and all orchestrated by ansible)
I’ve needed to create a special docker image for MySQL 5.1 for this project – I will post a followup post with the information about the docker
After all the hard work preparation etc I was ready to do the migration! Finally! I’ve dumped the data, Upload it to the new Master and Slave, Changed the users (Create new Delete the old ones), sync everything and set the backup.
The next day I woke up and checked the backup and found it didn’t work, I was not in a good mood – the backup failed…
So I looked into it the logs and found this weird line:
EXEC: mysqldump --host mysql.host.com --port XXXX --user backup --password=XXXXXXXX--skip-lock-tables --all-databases > /backup/db.dump
mysqldump: Couldn't execute 'show table status like 'VIEW\_OPEN\_EVENTS\_SUMMARY\_CASES'': EXPLAIN/SHOW can not be issued; lacking privileges for underlying table (1345)
I was baffled – I had no idea why the backup failed. I searched the web over 2 hours doing tests and trying permissions host everything I could think of or found online, nothing worked.
After several hours (decided to try again later with fresh head) I’ve asked a friend for help – but for some reason talking to him gave me an idea and I’ve tried to backup in a different method – doing it in the docker itself – then I got another error – that the user that I already deleted was still in play – but where?
Following some searches I’ve found: https://stackoverflow.com/questions/10169960/mysql-error-1449-the-user-specified-as-a-definer-does-not-exist
This helped me to understand the DB have some VIEW tables that the DEFINER in the configuration was the old user before the changes (after the restore)
So how i fixed that? Fortunately the amount of the VIEW tables where small so I used MySQL Workbench to alter them and updated them with the new DEFINER (using command line is a bit trickier).
So in the future? In the future I will edit the dump file before doing the restore to change the user in the file. This should solve the issue and negate the need to create the original user and then delete it afterward – Also any VIEW table or any other entity that need the user would be fixed as well.