Clone operations fail in the WP Toolkit: Unable to import database.

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):
  

  PLESK_ERROR: 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

  PLESK_ERROR: [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

1. Connect to the server via SSH

2. Create a dump for the database of the website that is being cloned:
   

   # plesk db dump wordpress_db > wordpress_db.sql

3. Enter the database server, select the related database and remove the trigger:
   

   # plesk db 

   MYSQL_LIN: mysql > use wordpress_db;

   MYSQL_LIN: mysql > drop trigger after_insert_comment;

4. Perform the upgrade or clone operation and restore the trigger manually (or import the whole original database dump).