DevKinsta Multisite subdirectory error DK0066 importing from live site

MarcoS · June 14, 2024, 9:41am

2024-06-14 at 11:40 UTC+2 CEST Europe/Rome
DevKinsta Version: 2.13.2
OS Version: Microsoft Windows 11 Pro version 10.0.22631 build 22631
Docker Desktop Version: 4.31.1

Were any error codes or messages observed? If so, what were they?
Error DK0066 importing live site from MyKinsta to DevKinsta

From main.log
[2024-06-14 10:45:33.339] [error] [ipcMainStep] Error in operation SITE_CREATION, step rsync: Error - DK0066: IMPORT_DB_DUMP: Error (1): qERROR 1449 (HY000) at line 601980: The user specified as a definer (‘mysql.infoschema’@‘localhost’) does not exist

Also:
[2024-06-14 10:45:38.988] [error] Error: =[31;1mError:[0m Error establishing a database connection.

I’ve read DevKinsta: Failed to import database dump DK0066 - packet bigger than max_allowed_packet - #6 by jackirish
and I’ve updated devkinsta_db with vim changing
from #row_allowed_packet = 1G
to row_allowed_packet = 1G
file /etc/mysql/mariadb.conf.d/50-server.cnf

Maybe problem could comes with MariaDB version: needs to be 10.11.8 and now I have 10.5.17, but if this could be the problem, how could I update version?

ERROR 1449 (HY000): The user specified as a definer (‘mysql.infoschema’@‘localhost’) does not exist:
This error occurs when exporting views/triggers/procedures from one database or server to another as the user that created that object no longer exists.
If I had access to MySQL maybe I can try to resolve with:
myssql > DROP USER ‘mysql.infoschema’@‘localhost’;
mysql> CREATE USER ‘mysql.infoschema’@‘localhost’ IDENTIFIED BY ‘password’;
mysql> GRANT SELECT ON . TO mysql.infoschema@localhost;

Andrew · June 16, 2024, 12:58am

Hi @MarcoS! Welcome to the Kinsta community!

I’m sorry to hear that you are experiencing this database import issue when you attempt to import a live site form MyKinsta to DevKinsta. I understand experiencing an issue like this can be frustrating. We are here to help you with finding a solution!

Is this the first site you have tried to import into DevKinsta? Or, are you able to import other sites without error?

Also, if you could please direct/private message me the full main.log file that will help us in reviewing what is happening prior to the database import error. Additionally, we would like to see if this could be an issue related to the database version mismatch.

We look forward to hearing from you!

MarcoS · June 16, 2024, 4:13pm

Hi Andrew, thanks for your reply.
Yes it is the first import.
How may I send to you main.log in a private message?

Andrew · June 16, 2024, 4:26pm

Thank you for your reply, Marco! You can send a message that contains the main.log file to me by selecting my name above the message and selecting the “Message” option.

Andrew · June 16, 2024, 11:07pm

Thank you for sending the main.log file over, Marco! We are investigating into this further and will be updating you as soon as possible.

If you do have any additional information or questions you would like to share in the meantime feel free to reply!

Best regards

Andrew · June 17, 2024, 2:09am

Hi Marco,

I just wanted to let you know we have escalated this matter internally with our DevKinsta development team to help investigate and see what could be causing this problem to occur. We will update you as soon as possible once we have more details to share.

We appreciate your patience while our team investigates.

Best regards

MarcoS · June 18, 2024, 8:24am

Ok, many thanks Andrew.

Andrew · June 24, 2024, 3:15am

Hi @MarcoS!

I want to followup with you with a possible workaround for this database issue you’ve been experiencing in DevKinsta.

I would like to see if creating the definer user and granting it all permissions on the database helps workaround this problem. Please follow the steps below to create this user and grant it all privileges on the database:

Start a command prompt in Windows
Run: docker exec -u root -it devkinsta_db bash
Once you have entered into the container run: mysql -p You will be prompted for a password. You may copy/paste the database password from the wp-config.php file of your DevKinsta site. This can be from any site as the root password is used.
In the MySQL command prompt run: GRANT ALL ON DB_NAME.* TO ‘mysql.infoschema’’@‘%’ IDENTIFIED BY ‘complex-password’;

In the above step please replace DB_NAME with the database name listed in the wp-config.php file of the site.

Run FLUSH PRIVILEGES;

This should create the definer user MariaDB is looking for and hopefully allow you to import your site from MyKinsta into DevKinsta.

Please let us know if this helps resolve the issue, or if you do continue to experience any difficulty. We are standing by and happy to help!

We look forward to hearing from you!

MarcoS · June 24, 2024, 3:36pm

Hi Andrew,
thanks for your reply.

I tried to access to mysql, but seems user access is deneid.
I used pw from wp-config.php Kinsta live site, but doesn’t works.

Then I tried in another way.
With Docker Desktop I opened devkinsta_adminer container and in “Inspect” I’ve read adminer pw (line 173).
So I opened Devkinsta_db container exec, running grand and flush.

But now problems seems comes from local web \wsl.localhost\docker-desktop-data.…
“the item could not be found”.

I have uninstalled and reinstalled docker and devkinsta (two times), but now when I try to import weyourconsult.com from kinsta I receive the error: “something bad happened”

This from log file:

[2024-06-24 15:04:29.361] [error] [createCertficiate] Creating certificates failed. UNKNOWN: unknown error, open '\\wsl.localhost\docker-desktop-data\data\docker\volumes\DevKinsta\ssl\weyourconsult.local.crt'
[2024-06-24 15:04:29.363] [error] [ipcMainStep] Error in operation SITE_CREATION, step webserver: Error: UNKNOWN: unknown error, open '\\wsl.localhost\docker-desktop-data\data\docker\volumes\DevKinsta\ssl\weyourconsult.local.crt'
[2024-06-24 15:04:29.374] [info]  [containerExec] Command 'cd /www/kinsta/public/weyourconsult && wp --allow-root --skip-themes --skip-plugins core version' on devkinsta_fpm finished with exit code 1
[2024-06-24 15:04:29.467] [info]  [ProgressIndicator] { isFailed: true, isOpen: false }
[2024-06-24 15:04:29.469] [info]  [checkWpVersion] site's current version [31;1mError:[0m This does not seem to be a WordPress installation.Pass --path=`path/to/wordpress` or run `wp core download`.

Many thanks for your support, Marco.

Adrian_L · June 25, 2024, 2:41am

Hi @MarcoS

I’m not certain if you’ve attempted this already, but could you please uninstall and then reinstall Docker Desktop one more time? This time, when reinstalling, please do not check the “Use WSL 2 instead of Hyper-V” option as shown in the screenshot.

Then after, try to import your Kinsta again through the Devkinsta app.

Alternatively, you can also make a downloadable backup through Kinsta and import the backup in DevKinsta using the Add Site >> Custom Site >> Import from backup option.

Let me know how it goes.

MarcoS · June 25, 2024, 2:04pm

Hi Adrian, I tried. Import needs about 90 minutes and at the end I receive error - DK0066 “The user specified as a definer (‘weyourconsult’@‘localhost’) does not exist”.
Now I try to install WSL, UBUNTU and DevKinsta.deb,
I’ll let you know about this scenario.

Adrian_L · June 26, 2024, 3:05am

Hi @MarcoS

I did some research on this and have come up with this information:

The user specified as a definer (‘weyourconsult’@‘localhost’) does not exist." This error arises in MySQL when you’re trying to use a database object (like a view, procedure, or trigger) that was created by a user named ‘weyourconsult’ on the source machine (‘localhost’), but that user no longer exists in the destination server.

In your case, I checked your database and found that you have active Views wherein
"weyourconsult@localhost " is set as the DEFINER.

Unfortunately, it doesn’t look like our script is able to search and replace the database views during the DevKinsta Pull action. Since you have the downloadable backup, you could try running a search and replace method manually in the database file (.sql).

Here is sample ssh command to run search and replace on your SQL file:

And then proceed with the site import from the backup through DevKinsta afterward.

I hope it helps!

MarcoS · June 26, 2024, 7:23am

Hi Adrian,
yes I’ve created custom views in mysql, maybe problems comes from that.
So I have to replace with root’@'devkinsta.db.

But should I change the view in live site?

Now are like:

CREATE 
    ALGORITHM = UNDEFINED 
    DEFINER = `weyourconsult`@`localhost` 
    SQL SECURITY DEFINER

May I change weyourconsult with “root”?
In order to wotk in localhost may I change the definer to 'root'@'localhost' ?

I can try to update views in a staging site and than import it from DevKinsta Desktop.

What do you think? This could resolve the issue?

Many thanks,

MarcoS · June 26, 2024, 9:33am

Sorry Adrian,
I’m not able to change the definer with mysql.

I tried to run command from staging site with WinSPC (Putty or Terminal), but I receive an error:

Sed Command

I’ve created a staging site and downloaded.
Than I’ve extrated weyourconsult.sql in local windows download folder.

From witch app have I to run the sed command?

Adrian_L · June 27, 2024, 12:45am

@MarcoS

May I change weyourconsult with “root”?
In order to wotk in localhost may I change the definer to `'root'@'localhost'` ?

I can try to update views in a staging site and than import it from DevKinsta Desktop.

Sure, if you could change the Definer to “root” from “weyourconsult” of the database Views in your staging, then try the DevKinsta Pull action again. Let’s see if that works.

However, I suggest making a backup of your staging before making the change.

MarcoS · June 27, 2024, 7:38am

Hi Adrian,
if I try to change the view with MySQL WorkBench I receive this error:
ERROR 1227: Access denied; you need (at least one of) the SUPER, SET USER privilege(s) for this operation

SUPER privilege can be granted to the database user directly via MySQL:

mysql> GRANT SUPER ON *.* TO 'weyourconsult'@'localhost';
mysql> FLUSH PRIVILEGES;

If this is possible than I shouldn’t have problem with DevKinsta.
How can do that? Or can you grant super user privileges to weyourconsult?

Many thanks.

Alessandro · June 27, 2024, 1:20pm

Hi @MarcoS
Can you please try to make a manual backup in your staging site and then change the user within your definer via phpMyAdmin? You shouldn’t need any additional privileges do change that
Please let us know if you are able to pull the site from staging after that.

Regards,
Alessandro

MarcoS · June 27, 2024, 5:37pm

Hi Alessandro,
I haven’t understood how to change definer with phpMyAdmin.
I can only see definer in a stored procedure.
I tried to change definer in Staging site, but I receive this error:

Adjust provileges is uncliccable.

I tried also with MySQL Workbench (8.0 Community)
changing
CREATE DEFINER=weyourconsult@localhost
to
CREATE DEFINER=root@localhost

SQL Script:

USE `weyourconsult`;
DROP procedure IF EXISTS `GetTotalPosts`;

USE `weyourconsult`;
DROP procedure IF EXISTS `weyourconsult`.`GetTotalPosts`;
;

DELIMITER $$
USE `weyourconsult`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `GetTotalPosts`()
    SQL SECURITY INVOKER
BEGIN
	DECLARE totalPosts INT DEFAULT 0;
    
	SET @TotalScore = (SELECT AVG(TotPostScore) FROM weyourconsult.wp_stat_economia_aziendale);
    
    SELECT COUNT(*) 
    INTO totalPosts
    FROM weyourconsult.wp_posts;
    
    SELECT totalPosts, @TotalScore;
END$$

DELIMITER ;
;

But I receive the ERROR:

Operation failed: There was an error while applying the SQL script to the database.
ERROR 1227: Access denied; you need (at least one of) the SUPER, SET USER privilege(s) for this operation.

I tried also changing:
USE weyourconsult;
USE weyourconsult$$
to
USE root;
USE root$$

Many thanks.

Adrian_L · June 28, 2024, 4:18am

Hi @MarcoS,

Unfortunately, the only solution I can think of would be to first back up your staging environment, then temporarily drop the View tables on your staging so that the DevKinsta Pull action can proceed.

Once the DevKinsta Pull action successfully completes, you can restore the staging environment from its backup to bring back the View tables.

After that, you can export the view tables separately from the staging environment, open them in your local text editor, manually update the Definer value, and then import them into DevKinsta using the database manager or via WP-CLI if you also need them in the DevKinsta local site.

MarcoS · June 28, 2024, 3:05pm

Hi Adrian, thanks for your reply.

I’ve dropped custom views from staging Kinsta site.
I tried to import staging with DevKinsta, but I received an error about SSL certificate.

[2024-06-28 11:39:07.686] [error] [createCertficiate] Creating certificates failed. UNKNOWN: unknown error, open '\\wsl.localhost\docker-desktop-data\data\docker\volumes\DevKinsta\ssl\prova.local.crt'
[2024-06-28 11:39:07.688] [error] [ipcMainStep] Error in operation SITE_CREATION, step webserver: Error: UNKNOWN: unknown error, open '\\wsl.localhost\docker-desktop-data\data\docker\volumes\DevKinsta\ssl\prova.local.crt'

I tried to create a new site from DevKinsta, but I received the same error.

I’ve found an info about DevKinsta trying to access \wsl.localhost\docker-desktop-data it will not work with Docker Desktop version 4.30.0 or higher.
So I uninstalled Docker Desktop 4.31.1 and installed 4.29.0.

Now I can create a new site with DevKinsta without problem.

Than I’ve imported staging site (cleaned from views), but when I try to open the site on web page I can see only: " Error establishing a database connection".
All instances on Docker are running.

I tried also to disable integration with my default WSL distro in Docker setting → Resources → WSL integration, but with same response.

Many thanks.

Topic		Replies	Views
DevKinsta: Failed to import database dump DK0066 - packet bigger than max_allowed_packet Questions	7	275	June 11, 2024
DK0066 error when importing site from live Bug Reports	1	15	August 13, 2024
Receiving error DK0066 Failed to import database dump Bug Reports	7	970	May 9, 2023
DK0066 Failed to import database dump Bug Reports	7	799	August 24, 2022
Something bad happened on 16% of importing site Bug Reports	8	2112	June 30, 2022

DevKinsta Multisite subdirectory error DK0066 importing from live site

Related Topics