0

On Monday I messed up with a database.

We have an application running on a VPS, using cPanel and phpmyadmin, and I informed the developers I will be doing some queries on the DB to extract information.

So, I did a few large queries using the "Visual Builder" query tool and the web-application got stuck. The queries weren't loading and even refreshing the page did not work. The website wasn't loading and users couldn't log in. So I used WHM to log in as root and kill the queries manually. After I did this, the system was still not running.

Then, the database completely freaked out and I got these error messages:

enter image description here

After doing this, the DB somehow fixed itself and the web application was working again. However, we saw that we could not update some jobs or add new jobs in the system. If you pressed the "SAVE" button on a job, the system just gave an "undefined" message.

The developers had a look and discovered this was causing the issue:

[enter image description here The devs went ahead and added the definer and the issue was resolved. The blacked out "user"@1.0.0.0" is the actual cPanel account username.

However, this did not last as yesterday evening the exact same situation was occurring. The web-application was running fine on Tuesday and most of Wednesday, then all of a sudden users couldn't update their jobs again which means the definer user was removed once again even though nobody did anything in the database.

Has anyone encountered this issue before? I read this thread on the topic and even though what they say makes sense, I believe the developers did this but the error still occurred.

When I log into phpmyadmin via cPanel, I get a weird user called "cpses_234ikjih@localhost.com". Does this perhaps have something to do with this error? I believe before the server went crazy, this user was only the name of the cPanel account (for example: "cPanelAccountName@localhost.com".

MailBlade
  • 339
  • 4
  • 19

2 Answers2

0

To summarize your post, what I'm seeing is that you have a MySQL user, the user disappeared, you recreated the user, and it went away again.

There must be some external factor here. Someone could have access to your database and is deleting the user maliciously or out of misunderstanding, there could be a scheduled job, or it could be something to do with your web host.

I'd start by auditing the database accounts, and restricting access as much as possible. Check any interface that's exposed to the web, such as WordPress, Joomla, or other applications.

You should enable logging, there are several degrees of logging that MySQL can allow. I think the most useful for you would be the audit log, although honestly I've never used that specifically. You'd enable that to log future events. The binary log may contain record of what has already occurred.

Isaac Bennetch
  • 11,830
  • 2
  • 32
  • 43
  • Thank you so much for your reply, really appreciate it. What you mentioned here: `there could be a scheduled job` is what I think is causing it. There are some triggers running on the database and it all seems to go back to this, as there are about 4 large triggers and after a day or two of the system running it resets the users. I will look into what you mentioned regarding auditing the database records and restricting access. If I have two cPanel accounts on the VPS and one account is using Joomla and the other one not, can this have an adverse effect? – MailBlade Apr 10 '20 at 17:06
  • This is exactly right: `To summarize your post, what I'm seeing is that you have a MySQL user, the user disappeared, you recreated the user, and it went away again.` Also, I did not recreate the user. After the server reset the user was gone and it has to be added by the development team every time this error occurs. I will have a look at the MySQL logs thank you. This is how the original error was determined (see my second image for ref.). I saw one post on cPanel forums where one user mentioned changing cPanel account and root password solved this issue, so I will be trying this. – MailBlade Apr 10 '20 at 17:08
  • So even now the server is still removing the user after it has been added again. – MailBlade Apr 10 '20 at 17:09
0

SOLVED

I managed to solve this by changing MySQL database password and cPanel account password.

I read one post by someone saying that there was a session file which perhaps stored an old session and that changing passwords could resolve this. Luckily it did, have not had the error 1449 appearing for 5 days now.

MailBlade
  • 339
  • 4
  • 19