Articles in this section

See more

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

Avatar
Anton Kuznetsov
Updated
Follow
Plesk for Windows kb: technical

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)

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. Open the 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

4 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
    Comment actions Permalink
  • 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
    Comment actions Permalink
  • 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
    Comment actions Permalink
  • Avatar
    Ashish Sharma

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

    0
    Comment actions Permalink

Please sign in to leave a comment.

Have more questions? Submit a request

Related articles