On an Ubuntu 16.04.4 LTS machine running MySQL (Ver 14.14 Distrib 5.7.21, for Linux (x86_64) using EditLine wrapper) we are experiencing some very peculiar behavior.
There is a schema,
mydb, and two users,
mydb_admin@%. The permissions for the two users are as follows:
GRANT ALL PRIVILEGES ON `mydb`.* TO 'mydb_admin'@'%' GRANT ALL PRIVILEGES ON *.* TO ‘root'@'%'
Included in the schema is a stored procedure which both creates and drops tables – not temporary tables or table variables, but real tables.
Using MySQL Workbench, and logged into the server as root, we opened a query window and called the stored procedure. The resulting table is correct (where correct means that the values returned are as they should be). We then logged out as root and logged in as mydb_admin, and the resulting table was incorrect (the values returned in certain columns were not as they should be). Another time, we logged in as root, ran the stored procedure and the results were incorrect. We then made some changes to the stored procedure (which had nothing to do with the incorrect results), ran it again, and the results were correct.
We also have a php script that executes the stored procedure. When we run it, accessing the database as either user, the results are completely incorrect. By completely incorrect, we mean that the columns returned reflect an older version of the stored procedure.
We have been unable to find any repeatable pattern to this behavior and, obviously, we are very concerned as we don’t know if other stored procedure calls are similarly failing. What should we do to resolve this problem?