WordPress cloning fails on a database copying in WordPress Toolkit. The following error is displayed in Plesk:
Unable to import database. Error message: ERROR 1227 (42000) at line 1337 in file: ‘/var/www/vhosts/example.com/httpdocs/wp_name.sql’: Access denied; you need (at least one of) the SUPER privilege(s) for this operation
A database dump of source WordPress instance
wp_source.sqlcontains a VIEW with a database user as DEFINER:
# grep DEFINER wp_source.sql
/*DEFINER=`wpuser`@`%` SQL SECURITY DEFINER */
#EXTWPTOOLK-946 "It is now possible to clone websites with defined DEFINER clause in the database dump. If you were ever given issue ID EXTWPTOOLK-946 by our support team for tracking purposes, it’s time to rejoice."
- WordPress Toolkit 5.3.0 15 February 2021
If update is not possible for some reason you may try the following
As a workaround, delete the view containing database user as a definer:
Connect to a Plesk server via SSH.
Retrieve name of the source instance database:
# grep DB_NAME /var/www/vhosts/example.com/httpdocs/wp-config.php
define( ‘DB_NAME’, ‘wp_source’ );
Create a dump of the affected WordPress database from the error:
# plesk db dump wp_source > wp_source.sql
Find the view name:
# grep “view structure” ./wp_source.sql
— Final view structure for view `random_view`
Log in to MySQL and select the affected WordPress database:
# plesk db
mysql> USE wp_source;
Copy the view, so it can be recreated on the source database and on the cloned database after deleting. Save the output of the following command:
mysql> show create table random_viewG
Delete the view:
mysql> DROP VIEW random_view;
Clone the WordPress instance in Plesk.
Log in to MySQL and recreate the view in both original and cloned databases by pasting the output of the command that goes after “Create Table:” in step 6.