I work with multiple environments of the same database: A server dedicated to user tests (a stable one) and I have a local environment on my work computer.
There are times where I literally load my own database and have to pull up a backup, which I take from the user testing team.
The problem starts when I finish raising the backup on my local computer, because I receive a "The user specified as a definer ('testUser'@'%') does not exist".
This happens because in the test environment there are 2 "testuser", one for localhost and one for % (any domain), but on my local machine I don't have a "testUser" for any domain.
Although duplicating the user in my local environment solves the issue of the error, is there a way to massively change DEFINER=testUser@%
the user of all the routines in the database to the user I want?
It turns out that after doing some research in the English community, I found that it is possible.
The table
mysql.proc
gives information about all stored procedures in all databases on the server.To confirm I first did a
And indeed, it returned the database routines, among its columns was the one that interested me: 'define'. In the end I just had to do a common and wild update:
It is possible to consult the routines that belong to a specific user filtering by the column
definer
Edit: Restarting my database from MySQL Notifier was required for the change to take effect. I added a query to search for routines by specific user.
I leave here the source of my solution.