Symptoms
- Plesk Obsidian running on a Linux-based operating system
- One of the following errors is displayed after an attempt to perform a Smart update or Clone a WP instance (both actions involve a database cloning attempt via a new temporary database user):
Failed to clone the installation: 'Unable to import database. Error message: ERROR 1227 (42000) at line 357 in file: '/var/www/vhosts/example.com/.wp-toolkit/2617/db_2617_rvtbwu.sql': Access denied; you need (at least one of) the SUPER privilege(s) for this operation
[2024-04-02 12:29:44.723] 3686411:660bfa22315c5 ERR [extension/wp-toolkit] ERROR 1142 (42000) at line 94 in file: '/var/www/vhosts/reallygoodmarquees.co.uk/.wp-toolkit/49/db_49_ixb0ml.sql': TRIGGER command denied to user 'wp_bv7iv'@'localhost' for table `wp_test1`.`NcLp1_comments`
Cause
WP Toolkit is using a temporary user to import the database during the cloning process (which is always launched during the Smart Updates procedure) and fails if there is a trigger, which always has a DEFINER specified, because the definer will never match the WPTK temporary user's name.
For example the following could be seen in a database:
# MariaDB [wordpress_db]> show triggers like '%' G;
*************************** 1. row ***************************
Trigger: after_insert_comment
Event: INSERT
Table: wp588_comments
Statement: BEGIN
IF NEW.comment_content LIKE '%are you struggling to get comments on your blog?%' THEN
SET @lastInsertWpUsersId = (SELECT MAX(id) FROM `Sql1490171_1`.`wp588_users`);
SET @nextWpUsersID = @lastInsertWpUsersId + 1;
...
...
END IF;
END
Timing: AFTER
Created: NULL
sql_mode: NO_AUTO_VALUE_ON_ZERO
Definer: jdoe777@localhost
character_set_client: utf8mb4
collation_connection: utf8mb4_general_ci
Database Collation: utf8_general_ci
Resolution
-
Connect to the server via SSH
- Create a dump for the database of the website that is being cloned:
# plesk db dump wordpress_db > wordpress_db.sql
- Enter the database server, select the related database and remove the trigger:
# plesk db
mysql > use wordpress_db;
mysql > drop trigger after_insert_comment;
- Perform the upgrade or clone operation and restore the trigger manually (or import the whole original database dump).
with