Plesk is unaccessible with the error: SQLSTATE: Column not found/Base table or view not found : CloudBlue Technical Support

Symptoms

Plesk fails to load after an upgrade. The following errors may appear when attempting to log in:

    Unknown column ipAddress in where clause

Or:

    ERR (3)  [panel]: SQLSTATE[42S02]: Base table or view not found: 1146 
    Table 'psa.table_name' doesn't exist

Or:

 SQLSTATE[42S22]: Column not found: 1054 Unknown column 'DomainId' in 'where clause'

While attempting to open a domain or subscription, the following error may appear:

ERROR: Call to a member function capability() on a non-object (SubscriptionController.php:1144)

Cause

Generally, an upgrade fails due to package dependencies, inconsistencies in the pre-upgrade installation, or previous failed upgrade attempts.

This article provides general instructions on how to re-run the upgrade, so the exact root cause is omitted here. It should be determined based on the log files and upgrade script's output.

Resolution

Here are the steps to start a failed upgrade from the very beginning:

Make a snapshot of the current database - it may be inconsistent (partially upgraded), but if something goes wrong it may be required:
```
# mysqldump -uadmin -p`cat /etc/psa/.psa.shadow` --quote-names --database psa > current.sql
```

Find a consistent pre-upgrade dump of the 'psa' database in the '/var/lib/psa/dumps/' directory:

# ls -l /var/lib/psa/dumps/mysql.preupgrade*
-rw-------  1 root root 78771 Oct 11 16:56 /var/lib/psa/dumps/mysql.preupgrade.dump.gz
-rw-------  1 root root 78452 Oct 11 16:32 /var/lib/psa/dumps/mysql.preupgrade.dump.gz.1
-rw-------  1 root root 78320 Oct 11 16:07 /var/lib/psa/dumps/mysql.preupgrade.dump.gz.2
-rw-------  1 root root 65351 Oct  7 02:09 /var/lib/psa/dumps/mysql.preupgrade.dump.gz.3
-rw-------  1 root root 45632 Aug  3 12:39 /var/lib/psa/dumps/mysql.preupgrade.dump.gz.4
-rw-------  1 root root 43245 Aug  3 00:19 /var/lib/psa/dumps/mysql.preupgrade.dump.gz.5
-rw-------  1 root root 51235 Jun 12 12:19 /var/lib/psa/dumps/mysql.preupgrade.dump.gz.6

The most valid snapshot of the database is usually the oldest dump from the set of dumps which are close to the current date (in our case, this would be '/var/lib/psa/dumps/mysql.preupgrade.dump.gz.2').

Extract the 'psa' database from the dump with the following command:

3.1. If an upgrade was made from a version 10.3 or above, you should restore both the 'psa' and 'apsc' databases:

# zcat /var/lib/psa/dumps/mysql.preupgrade.dump.gz.2 | sed -n '/^CREATE DATABASE.*psa/,/^CREATE DATABASE.*apsc/p' > preupgrade_psa.sql
# zcat /var/lib/psa/dumps/apsc.preupgrade.dump.gz.2 > preupgrade_apsc.sql

3.2. If an upgrade was carried out from a version prior to 10.3, only the 'psa' database is required:

 # zcat /var/lib/psa/dumps/mysql.preupgrade.dump.gz.2 | sed -n '/^CREATE DATABASE.*psa/,/^CREATE DATABASE.*horde/p' > preupgrade.sql

Check the version of the 'psa' and 'plesk-core' packages:
```
rpm -q plesk-core psa
```
If the packages are already upgraded, download and install the pre-upgrade versions from http://autoinstall.plesk.com/PSA_<old_plesk_version>/<os_version_build>/base/ and install them forcibly:
```
rpm -Uvh --force --nodeps <package_name>
```
If there are two 'psa' RPMs installed, remove the new one as follows:
```
# rpm -e --nodeps --noscripts --justdb <package>
```
Correct the following files: '/usr/local/psa/version' and '/usr/local/psa/core.version'.

Set the old versions there. The build digits should be taken from the corresponding bootstraper package. For example: If you install the following Plesk packages: plesk-core-11.5.30-cos6.build115130819.13.x86_64.rpm, psa-11.5.30-cos6.build115130819.13.x86_64.rpm, the 'version' file should contain: 11.5.30 CentOS 6 115140407.17 (Build version taken from the latest bootstraper package: pp11.5.30-bootstrapper-11.5.30-cos6.build115140407.17.x86_64.rpm). The file 'core-version' should hold the version of the core package.

Drop the current 'psa' and 'apsc' databases and restore your pre-upgrade dump:

# echo "DROP DATABASE psa" | mysql -uadmin -p`cat /etc/psa/.psa.shadow`
# cat preupgrade_psa.sql | mysql -uadmin -p`cat /etc/psa/.psa.shadow`
# echo "DROP DATABASE apsc" | mysql -uadmin -p`cat /etc/psa/.psa.shadow`
# cat preupgrade_apsc.sql | mysql -uadmin -p`cat /etc/psa/.psa.shadow`

Fix the problem that caused the upgrade to fail initially. This process is not the subject of this article. However, you may find useful information in the upgrade logs in /tmp.
Download the 'psa' and 'plesk-core' packages of the version you to which you attempted to upgrade and install them with 'rpm -Uvh --force'. This action will initiate the database upgrade. If it fails, you should fix the cause of the failure, roll back everything as per the previous steps, and follow this step again.

Run the 'bootstrapper' utility:

/usr/local/psa/bootstrapper/pp12.0.18-bootstrapper/bootstrapper.sh --prep-install
/usr/local/psa/bootstrapper/pp12.0.18-bootstrapper/bootstrapper.sh --rerun

To restore the configuration files of web, mail, and DNS services, run the following command:
```
plesk bin repair --run
```

Run the following command:

/usr/local/psa/admin/bin/autoinstaller --select-release-current --upgrade-installed-components

Internal content

Link on internal Article