Applying the 6.0.15 database update #

This page is for database administrators who apply the database part of an upgrade from 6.0.0 to 6.0.14 (or an earlier checkout of the 6.0.15 line) to 6.0.15. It is the database side of 11.6 From any 6.0.x to today: the code is put in place by whoever deploys the site, the database by you. Follow the steps in order.

Contents #

1. What the update adds
2. Step 1: prepare
3. Step 2: apply the file
4. Step 3: the audit tables
5. Step 4: the trash records
6. Step 5: verify
7. Special cases
8. Checklist

What the update adds #

update/database/<engine>/6.0/dbupdate-6.0.0-6.0.15.sql (MySQL, PostgreSQL, SQLite) records the version 6.0.15stable and adds the tables and columns of the line:

On MySQL and PostgreSQL it also makes, where missing, the three changes of the 5.4 to 6.0.0 file (the wider ezuser.password_hash, ezcontentobject_trash.trashed, and on PostgreSQL the <table>_<column>_seq sequence names), leaving a database that has them as it is.

Step 1: prepare #

php bin/php/maintenance.php on --message="Upgrading" --allow-root-user

Take a backup of the database and of settings/override (Backups per engine). Read the version row; it should be a 6.0 version below 6.0.15stable.

Step 2: apply the file #

mysql -u USER -p DATABASE < update/database/mysql/6.0/dbupdate-6.0.0-6.0.15.sql
psql -U USER -d DATABASE -f update/database/postgresql/6.0/dbupdate-6.0.0-6.0.15.sql
sqlite3 var/storage/sqlite3/exponential.db < update/database/sqlite/6.0/dbupdate-6.0.0-6.0.15.sql

Run the one for your engine, once, whole, in order. If the database already has a column, the statement fails and names it; skip only that statement. The MySQL file has no SET default_storage_engine line, because each of its tables names its engine.

Step 3: the audit tables #

The audit trail is on by default from 6.0.15. Create its index tables and index the audit files written so far:

php update/common/scripts/6.0/createaudittables.php --dry-run
php update/common/scripts/6.0/createaudittables.php

The script works on every engine (MySQL, MariaDB, PostgreSQL, SQLite, Oracle and MongoDB, each through its schema handler) and leaves existing tables alone, so it can run again. --dry-run shows which tables are missing; --no-index creates the tables only. If it stops with "The audit index classes are not in the autoload array", run php bin/php/ezpgenerateautoloads.php -k first.

The same directory holds createapikeytable.php, which creates the table of personal API keys (expapikey) through the schema handler of any engine and leaves an existing table alone. The SQL update files of MySQL, PostgreSQL and SQLite create the same table; on Oracle and MongoDB run the script (--dry-run first) (API keys).

createtransferruntable.php does the same for the run history of the import and export tools (exp_transfer_run): the SQL update files create it on MySQL, MariaDB, PostgreSQL and SQLite, the script on any engine. Until the table exists the tools run as before and write no history (Transfer runs).

Step 4: the trash records #

Until trashed_by and trashed_via exist, the kernel writes who trashed an object to <VarDir>/trash/trashed.json. After the update file, move those records into the table:

php update/common/scripts/6.0/movetrashrecords.php

See the trash view.

Step 5: verify #

php bin/php/console exp:checkdbfiles --no-verify-branches     # prints nothing
php bin/php/ezcache.php --clear-all --allow-root-user

• The version row reads 6.0.15stable.
• Setup > Upgrade check, "Check database consistency", lists no missing table (Checking the schema after an upgrade).
• Sign in, publish a test object, move it to the trash and restore it.
• Maintenance off: php bin/php/maintenance.php off --allow-root-user.

Special cases #

Checklist #

• To do: Maintenance on; backup taken.
• To do: The engine's 6.0.15 file applied once.
• To do: createaudittables.php run (dry run first).
• To do: movetrashrecords.php run.
• To do: Version row 6.0.15stable; upgrade check clean; test publish done.
• To do: Caches cleared; maintenance off.

Related pages #

• Upgrades and the SQL update files
• Upgrading
• Audit
• Free documentation: this page is free to copy, translate and improve under the GNU FDL 1.3.