Problems restoring database with Directadmin

Addition, this happened tonight when updading the MySQL version.
Directadmin automatically makes a dump of all dataabase.

Dumping database richard_gr0t
mysqldump: Got error: 1356: View ‘richard_gr0t.wp_osefirewall_detmalware’ references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them when using LOCK TABLES

Please help. For the time being I will uninstall Centrora and advise my customers to do the same or use at their own risk.

Hi rhmgeerts

The issue is caused by the SQL backup script in DirectAdmin where it could not backup VIEWS.

There are a couple of solutions:

Solution 1.
Use the Centrora Backup function to backup the database then restore it, if you compare the file we backed up with other software, you may find others could not backup view correctly, this problem lies into the backup function in the script of Direct Admin

Solution 2.
When making backup / restore / update of the database, please go to Centrora Security –> Configuration –> Uninstall

This way, it will remove all Centrora tables, so the backup / restore / update can go smoothly.

Solution 3.
We will try to remove all views from the database creation function in 3.4.0, that way, your database backup / restore / update function should work properly. We will try to release this next week.

Hope all these help. ??
Best wishes
Helix

I have the same issue, my site backup cron job will not run due to Centrora Security screwing up the database. I was hoping the latest update would have a database fix but clearly not. I too shall be removing this plugin as an inability to create a backup is far more dangerous than anything this plugin is supposed to protect you from.

Hello Helix.

As far as I know, Directadmin can backup views, but only with Centrora it has a rights problem. A super user is needed.
We don’t have this problem when Centrora is not used and I can’t imagine that none of the other databases have views.

The option in Directadmin is a security option.

ERROR 1227 (42000) at line 1926 in file: ‘/home/richard/backups/backup/richard_bl0t.sql’: Access denied; you need (at least one of) the SUPER privilege(s) for this operation

They got this solution:
https://help.directadmin.com/item.php?id=466
where it says:

If you run into the “SUPER privilege” when running a MySQL restore, it would be because the restores do not run with a root/super level privilege, for security reasons.

So it’s a security issue and a good thing that DA is doing it this way.

I don’t know a lot about databases, but could solution 3 also be made so no super user is needed for the restore and backup?

Because last time on the previous version I did not have issues with the backup.
This time on the mysql dump it threw the error 1356.

Thank you for solution 2. However, it would be nice if that would be automatically in the script which removes the plugin.
Most users won’t think of going to the plugin first delete database tables there and remove the plugin afterwards.

Thank you for the quick support!

Best regards,
Richard

Hi Richard and dick_turpin

Thank you for your report. We can get version 3.4.0 that addresses this issue today, if possible, could you please drop me an email to helix[at]centrora.com so that I can send that version to you for testing this issue? If it works fine, we will release it to the public.

Thank you for your cooperation in advance! ??
Best wishes
Helix

BTW, we will use solution 3 to address the issue.

Helix

Hi Richard and dick_turpin

We performed a couple of test on our testing server for the 3.4.0 version, and confirm the backup and restore function works fine now. We will release this version soon. Could you please upgrade to version 3.4.0, then:

1. Go to Configuration –> Uninstall
2. Go to Dashboard –> Reinstall the database

Then

3. Perform a backup in Directadmin / cron job
4. Test the restore in Directadmin

It should work fine now.
Hope this helps. ??
Best wishes
Helix

Hello Helix.

Strangely enough I did not get a notification of your new reply. However I tested it.

I already did uninstall Centrora, zo I installed version 3.4.1. and did the things you told me.

The backup and restore now works fine again without any problems.

Thank you very much!

Best wishes.

Richard.

Hi Richard

Thank you for the update! That’s great! Have a good weekend! ??

Best wishes
Helix