Question Migration from plesk to plesk db error

[LorisMod]

Server operating system version

AlmaLinux 8.9 (Midnight Oncilla)

Plesk version and microupdate number

18.0.60 Update #1

Hello,

CentOS 7.9 source server with:
mysql Ver 15.1 Distrib 10.5.25-MariaDB, for Linux (x86_64) using readline 5.1
Plesk Version 18.0.60 Update #1
Almalinux 8.9 target server with:
mysql Ver 15.1 Distrib 10.3.39-MariaDB, for Linux (x86_64) using readline 5.1
Plesk Version 18.0.60 Update #1

However, I receive an error regarding the DB migration. This error also occurs when trying to import the database dump.
The error I get is the following:

Failed to copy content of database 'prestashop_db'


Migration tools tried to perform operation in 3 attempts: Command execution failed on the local server with non-zero exit code.


command: mysql --defaults-file=/usr/local/psa/var/modules/panel-migrator/sessions/20240518120659/target-server/my_localhost_prestashop_db.cnf -h localhost -P 3306 -uadmin prestashop_db < /usr/local/psa/var/modules/panel-migrator/sessions/20240518120659/target-server/db-dumps/prestashop_db.sql


exit code: 1


stdout:


stderr: ERROR at line 1: Unknown command '\-'.

On the forum I find references to similar errors, but they are all centered on different versions of the DB, in this case they are identical.

 

[Kaspar@Plesk]

This seems to be caused a bug in MariaDB. Which versions of the mysqldump utility are installed on both the source and target server? Version can be checked with: mysqldump --version

 

[LorisMod]

Hello,
Source Server:
mysqldump Ver 10.19 Distrib 10.5.25-MariaDB, for Linux (x86_64)

Target Server:
mysqldump Ver 10.19 Distrib 10.3.39-MariaDB, for Linux (x86_64)

 

[Kaspar@Plesk]

Interesting, same version mysqldump utility. I don't think the difference in MariaDB version matters, but I am not 100% sure. You could try upgrade MaraiaDB 10.3 to version 10.5.

When manually importing dumps you can try to remove the following line from the dump file:

/*!999999\- enable the sandbox mode */

 

[Mark_NLD]

@Kaspar@Plesk We are unfortunately running into the same issue.

Migrating from CentOS 7.9 with MariaDB 10.5.25 to Ubuntu 20 with MariaDB 10.3.39 fails.
Migrating from CentOS 7.9 with MariaDB 10.5.25 to Ubuntu 22 with MariaDB 10.6.16 fails.

Any idea or workarounds? We have some migrations planned for next week..

 

[Mark_NLD]

Duplicate with Issue - MariaDB failed imports - ERROR at line 1: Unknown command '\-'.

From reading MDEV-34183 it seems Plesk should apply the following workaround after creating the dump or before importing the dump:

• you can use mariadb-dump|tail +2 to remove the problematic line when making a backup
• you can use tail +2|mariadb to remove the problematic line when applying a backup

to remove this line: /*!999999\- enable the sandbox mode */

MariaDB is not going to fix this bug: "Unfortunately, there isn't much we can do about it. This change came as a fix for CVE-2024-21096, and security fixes can sometimes break backward compatibility."

 

[Mark_NLD]

Our current workaround to work around "ERROR at line 1: Unknown command '\-' ":

1. Run the Plesk Migrator like normal
2. Run on source:
   

   cd /root && /usr/sbin/plesk db -e "show databases" | grep -v -E "^Database|information_schema|performance_schema|phpmyadmin" > dblist.txt

   • and move dblist.txt to /root/ on target server
3. Run on target server a command to remove the faulty line from all the dump files (/usr/local/psa/var/modules/panel-migrator/sessions/..../target-server/db-dumps).
4. Run on target server:
   

   for i in `cat /root/dblist.txt`; do MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysql -u admin < /root/mysql_dumps_all/"$i".sql; done

 

[Mark_NLD]

For point 3 one might use the script below. The directory looks like /usr/local/psa/var/modules/panel-migrator/sessions/..../target-server/db-dumps

#!/bin/bash

# Specify the directory
directory="/path/to/directory"

# Iterate over each .sql file in the directory
for file in "$directory"/*.sql; do
    if [ -f "$file" ]; then
        # Check if the first line contains the specific text
        if head -n 1 "$file" | grep -q "enable the sandbox mode"; then
            # Use sed to remove the first line and save the changes in place
            sed -i '1d' "$file"
        fi
    fi
done

 

[Kaspar@Plesk]

@Mark_NLD thank you for sharing your solution. Form what I can gather so far the best course of action is make sure that both source and target server are running MariaDB version 10.4 or higher. As these version have received a fix, bit lower version are outside of the support cycle of MariaDB.

EDIT: See next post

 

[Kaspar@Plesk]

Actually, the advised solution is to make sure that MariaDB runs the latest version of the current branch/series. So updating 10.6.16 to 10.6.18 should solve the issue (as longs as the target server also runs the latest branch version too).

 

[si458]

Actually, the advised solution is to make sure that MariaDB runs the latest version of the current branch/series. So updating 10.6.16 to 10.6.18 should solve the issue (as longs as the target server also runs the latest branch version too).

can you please explain HOW to update to the latest version of mariadb?
we have just hit this problem migrating from an old centos 7 container (virtuozzo) to ubuntu 22.04 vm (proxmox)
our new server is still running 10.6.16 and no updates to 10.6.18?
can we use the offical mariadb repos with plesk and no problems?

Download MariaDB Server - MariaDB.org

REST API Release Schedule Reporting Bugs … Continue reading "Download MariaDB Server"

mariadb.org

 

[si458]

ok can confirm using the official mariadb repo in my link above, upgraded to 10.6.18 AND fixed the DB migrate issue!

 

[LorisMod]

Here they explain how to upgrade MariaDB on the target server, upgraded to 10.11.8 and the problem is resolved.

https://support.plesk.com/hc/en-us/articles/23694890755991-Import-or-migration-of-MySQL-databases-fails-in-Plesk-ERROR-at-line-1-Unknown-command