0

I'm on the verge of jumping down someone's throat and want to be sure I didn't somehow cause this.

I am building a website on a shared server hosted by a top-three hosting company. Last night, while I was continuing development, large swaths of content just dropped off the PHP-generated pages.

Panicked, I started tracing how the disappearing content was being pulled from the database and discovered that none of my stored procedures were working. Data was expected, data was present, but no data was being returned. None of the server logs I have access to showed any errors or warnings.

In mySQL workbench, I called a stored procedure that had been working a short while ago which had just stopped working. The response was that the user specified as the definer doesn't exist. I tried to look at the definition of the procedure in phpmyadmin and got a nearly identical response.

I executed SHOW PROCEDURE STATUS and saw that the definer for every function and stored procedure in my database was set to the user that does not exist. I looked at my last database backup and the user was the correct one. I pulled a new backup, and each procedure had this new nonexistent user as the definer.

--I tried to update mysql.proc to fix the definer(s), and was told that I didn't have the proper permissions (no surprise, but worth the shot).
--I dropped one procedure and tried to recreate it with the proper user as the definer. I was notified "Error Code: 1227. Access denied; you need (at least one of) the SUPER privilege(s) for this operation."
--I successfully recreated it without specifying the definer with no problem. The definer attached to the newly recreated procedure is the username I logged in as.

I know that I can't create a mySQL user with the same name as the nonexistent user. I can only create users through cPanel, and they must be in the format of [my hosting account username]_[new mysql user name]. The phantom user had a different hosting account username segment.

Am I off base in having the strong suspicion that a DBA working for my hosting company was twiddling around in my database and FUBAR'd it?

Can anyone think of a way that a breach of my code is the source?

I really want to make sure this doesn't happen again. If my code might be the source, I want to know it so I fix it. If this is some noob DBA from my hosting company putzing around in my database, I want to have this fixed too.

UPDATE: I have already calmly and politely addressed this with the hosting company, in a three-hour online chat with support and a handful of emails with technicians well before I posted this question. Their suggested resolution was to drop all functions and procedures and then recreate them. I did so, and they all work now.

The newly recreated functions and procedures have the same username which had evaporated last night. But they work right now... If I execute SELECT_USER() via phpmyadmin, surprisingly, it reports that I'm logged in as the mystery username. (info I didn't have when I originally posted my question)

As time passes and more details are revealed, it appears that it is more appropriate to run the hosting company through the coals. They may have dropped users on their server without properly vetting user accounts, and have since recreated the user without any form of mea culpa for a likely DBA FUBAR resulting in 12 hours of downtime for my site (and possibly other customers).

I'm still asking whether anyone can offer a reasonable scenario where I created this issue, so that I'm not inappropriately chastising my hosting provider. If someone can, I'll humbly accept responsibility and learn something in the process. If no one can offer a scenario and people echo my theory (especially from previous experiences), it is properly determined to be a chastising worth pursuing.

  • 1
    Instead of jumping down someone's throat you will probably be better off if you simply collect the facts that you have described here and calmly contact your provider support, asking them how your SP definers have changed without you being able to do that yourself. You can assign blame and vent your frustration after the issue is resolved. – mustaccio Jul 20 at 21:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.