Articles in this section

Unable to make database dump in Plesk: Unknown column 'generation_expression' in 'field list' (1054)

Daniel Yordanov
Updated
Plesk for Windows kb: technical ext: migrator

Applicable to:

  • Plesk for Windows

Symptoms

  • When migrating the domain to which the affected database belongs from a source server that uses MySQL 5.6 or lower to a destination server that uses MySQL 5.7 (or later), or MariaDB 10 (or later) version, the Plesk Migrator website transfer attempt fails with an error that is similar to the following:

    PLESK_INFO: Failed to copy content of database 'example_db'
    Migration tools tried to perform operation in 3 attempts: Command execution failed on the source server 'source' (203.0.113.2) with non-zero exit code.
    command: "C:\Program Files (x86)\Parallels\Plesk\MySQL\bin\mysqldump" -h localhost -P 3306 -uadmin -p***hidden*** --default-character-set=utf8mb4 --set-charset example_db --result-file=C:\plesk_migrator\db_backup_example.com_example_db.sql --routines --events
    exit code: 2
    stdout:
    stderr: mysqldump: Couldn't execute 'select column_name, extra, generation_expression, data_type from information_schema.columns where table_schema=database() and table_name='corn_update'': Unknown column 'generation_expression' in 'field list' (1054)

  • Backup attempt finishes with warnings and errors that are similar to following appears in Plesk Backup Manager:

    PLESK_ERROR: Unable to make database dump. Error: Failed to exec mysqldump.exe: Exit code: 2: mysqldump.exe: Couldn't execute 'select column_name, extra, generation_expression, data_type from information_schema.columns where table_schema=database() and table_name='category'': Unknown column 'generation_expression' in 'field list' (1054)

Cause

Unsupported MySQL version (5.6 or lower) is used on the server (source if this is a Plesk Migrator migration case), and generation_expression column from the warning message is supported only on MySQL 5.7 and newer versions.

Because MySQL versions 5.6 and older are not supported, this is not considered a bug. However, internal request #PPPM-14070 was created to check for used MySQL version and give a clearer error.

Resolution

Upgrade the MySQL version (5.6 or lower) to MariaDB 10.11 or later on the server (usually source):

1. Create a full server snapshot of the affected server (usually source) before proceeding with the DB server upgrade attempt in order to ensure that there is a restore point in case anything goes wrong. Contact your System Administrator for details in relation to that.

2. Follow the steps in this article:
How to upgrade MySQL 5.1 to MySQL 5.7 in Plesk Obsidian for Windows?

Warning: this operation will change the DB engine from MySQL to MariaDB and will include downtime for the sites on your server.Due to this, we recommend performing it only during maintenance hours.

Note: In case that the MySQL server (MariaDB) fails to start and cannot be repaired, restore the server to a snapshot created in step 1.

3. Afterwards you would be able to switch database server from MySQL to MariaDB on Plesk for Windows
 

Dirty workaround:

Install MariaDB 10.10 on source and use its "mysqldump.exe" to re-migrate the affected database
  1. Connect to the database source server via RDP.

  2. Install MariaDB 10.10 or lower on the source server separately from outside Plesk.

    Note: There is no need to install the full server package, as only client utilities are required.

  3. Make a copy of original %plesk_dir%\MySQL\bin\mysqldump.exe file.
  4. Copy mysqldump.exe from the installation directory of MariaDB10.10 to %plesk_dir%\MySQL\bin replacing the original one
  5. Try to re-migrate the databases of the subscription of your choice.
  6. Restore the mysqldump.exe copied on step 2 as %plesk_dir%\MySQL\bin\mysqldump.exe
 
Was this article helpful?

Comments

5 comments
Date Votes
  • Avatar
    Nick Hoang

    Hi Anton Kuznetsov could we upgrade MySQL of the existing server from 5.6 to 5.7

    0
  • Avatar
    Anton Kuznetsov

    Hello Nick Hoang,

    It is not possible to install MySQL 5.7 anymore due to some legal aspects and it is recommended to install MariaDB instead:

    https://support.plesk.com/hc/en-us/articles/12388974493975

    1
  • Avatar
    Nick Hoang
    (Edited )

    Thanks for your answer Anton Kuznetsov

    I've installed the MariaDB and migrated all the existing mySql DBs into it but I still receiving the warning about the mySql database as before. Is it possible to stop the mySQLdatabase service to get risk of these warnings?

    1
  • Avatar
    Ashish Sharma

    I want to understand how can we resolve this issue without migrating. Has anyone yet found any solution? Please help.

    0
  • Avatar
    Paul Johnstone

    It looks like that this error is affecting Backup & Restore Manager, Database Export and WordPress upgrades.
    Is there any workaround to this problem besides migrating to MariaDB? Note that this is for Windows-based clients running Plesk Windows, WP & MySQL.
    I’m finding several hosting providers are not providing a solution & are still running 5.5.43 of MySQL, this could be due to your comment above Anton.

    0

Please sign in to leave a comment.