Articles in this section

See more

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

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

Applicable to:

  • Plesk for Windows

Symptoms

  • Backup is finished with warnings. The 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)

  • Plesk Migrator website transfer from a source server that uses MySQL 5.6 or lower 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)

Cause

Unsupported MySQL version (5.6 or lower) is used on the server, and generation_expression column from the warning message is supported from MySQL 5.7.

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

As workaround:

Install MariaDB 10.11 or later on the affected server

  1. Create a full server snapshot of the server before proceeding to ensure that there is a restore point in case anything goes wrong. Contact System Administrator for details.

  2. Go to Plesk > Tools & Settings > Updates > Add/Remove Components > MySQL Server menu

  3. Select the MariaDB 10.11 server and press Continue.

    Warning: this operation will change the DB engine from MySQL 5.6 to the latest MariaDB 10.11 and will cause downtime of your server. We suggest performing it 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.

For reference: How to switch database server from MySQL to MariaDB on Plesk for Windows

Install MariaDB 10.10 or lower on source and use its "mysqldump.exe" to mi 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

Comments

5 comments

Sort by 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.

Have more questions? Submit a request

Related articles