Knowledge Base

Plesk error after upgrade: MySQL query failed: Unknown column ‘external_id’ in ‘field list’

Symptoms

After a failed Plesk upgrade, one of the following errors might be shown in an attempt to access Subscriptions or Domains in Plesk:

PLESK_ERROR: Error: MySQL query failed: Unknown column ‘id’ in ‘field list’

PLESK_ERROR: Error: MySQL query failed: Unknown column ‘external_id’ in ‘field list’

Cause

The Plesk database wasn’t upgraded successfully.

Resolution

Connect to the server via SSH
Back up PSA, APSC and MySQL databases:

# plesk db dump psa apsc mysql > /root/psa_apsc_mysql.backup-`date +%F-%s`.sql
Modify /usr/local/psa/version and /usr/local/psa/core.version files to contain previous Plesk version. For example, if the server was upgraded from Plesk 11.5.30 to 12.5.30 on CentOS 6, the following commands should be executed:

# echo “11.5.30 CentOS 6 00000000.00” > /usr/local/psa/version
# echo “11.5.30 CentOS 6 00000000.00” > /usr/local/psa/core.version
Check where are located the database dumps performed by Plesk:

# grep DUMP_D /etc/psa/psa.conf | grep -v ‘#’
DUMP_D /var/lib/psa/dumps
Go to the directory where the pre-upgrade dumps are located (Step 4) and restore the PSA and APSC databases:

Warning: Always check carefully at versions and timestamp of pre-upgrade dump files. It is important to restore correct pre-upgrade dumps. There might have been several failed attempts to upgrade and some dumps are containing half-upgraded database.

# cd /var/lib/psa/dumps
# zcat mysql.preupgrade.11.5.30-12.5.30.20140723-022543.dump.gz | MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysql -uadmin
# zcat mysql.preupgrade.apsc.11.5.30-12.5.30.20140723-022544.dump.gz | MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysql -uadmin
Run Plesk Bootstrapper utility provided by the new Plesk version with the rerun option. For example, if the server was upgraded to 12.5.30, the following command should be executed:

# /usr/local/psa/bootstrapper/pp12.5.30-bootstrapper/bootstrapper.sh rerun