Difference between revisions of "UpdateTasks"

(How to see all schemas?)
(Tools to maintain Update Tasks)
Line 15: Line 15:
 
== Tools to maintain Update Tasks ==
 
== Tools to maintain Update Tasks ==
  
$ /opt/open-xchange/sbin/runupdate -n schema
+
=== Run update tasks on all schemas serially ===
  
runs update tasks in specified schema
+
$ /opt/open-xchange/sbin/runallupdate
  
$ /opt/open-xchange/sbin/listExecutedUpdateTasks -n schema
+
=== Run update tasks on one schema ===
  
lists executed update tasks in specified schema
+
$ /opt/open-xchange/sbin/runupdate -n schema
  
=== How to see all schemas? ===
+
=== List executed update tasks for a given schema ===
  
Note: you need to have your account names and passwords at hand.
+
  $ /opt/open-xchange/sbin/listExecutedUpdateTasks -n schema
 
 
run
 
 
 
  $ /opt/open-xchange/sbin/listdatabase -A oxadminmaster -P secret
 
id name      hostname  master mid weight maxctx curctx hlimit max inital
 
  3 oxdatabase localhost true    0    100  1000    12 true  100      0
 
 
 
to check your database schema prefix. In this case it is <tt>oxdatabase</tt>
 
 
 
run
 
 
 
$ echo show databases | mysql -uopenexchange -psecret | grep oxdata
 
oxdatabase_5
 
 
 
so we have just one schema named <tt>oxdatabase_5</tt>.
 
  
Now you could check the status of update tasks on that schema:
+
Sample output:
  
  $ /opt/open-xchange/sbin/listExecutedUpdateTasks -n oxdatabase_5
+
  <nowiki>$ /opt/open-xchange/sbin/listExecutedUpdateTasks -n oxdatabase_5
 
  taskName                                                                                                successful lastModified
 
  taskName                                                                                                successful lastModified
 
  [...]
 
  [...]
Line 63: Line 48:
 
  com.openexchange.contact.storage.rdb.sql.AddFilenameColumnTask                                      true      2014-02-02 11:37:47 MEZ  
 
  com.openexchange.contact.storage.rdb.sql.AddFilenameColumnTask                                      true      2014-02-02 11:37:47 MEZ  
 
  com.openexchange.groupware.update.tasks.GenconfAttributesStringsAddUuidUpdateTask                  true      2014-02-02 11:37:48 MEZ  
 
  com.openexchange.groupware.update.tasks.GenconfAttributesStringsAddUuidUpdateTask                  true      2014-02-02 11:37:48 MEZ  
  [...]
+
  [...]</nowiki>
 +
 
 +
=== How to see all schemas? ===
 +
 
 +
The database schemas are not exposed via OX tooling. You need to read them from the configdb. In principle you're looking for <code>db_schema</code> entries from the <code>context_server2db_pool</code> table and join that with corresponding lines from the <code>db_pool</code> table to get the database instance the schema is living on.
 +
 
 +
Sample query (works in general as of writing this documentation, may break on schema updates in future):
 +
 
 +
<nowiki>SELECT SUBSTRING(hp.host_port, 1, LOCATE(':', hp.host_port)-1) AS host, SUBSTRING(hp.host_port, LOCATE(':', hp.host_port)+1) AS port, a.db_schema FROM db_pool d INNER JOIN (SELECT write_db_pool_id, db_schema FROM context_server2db_pool GROUP BY db_schema) AS a ON d.db_pool_id=a.write_db_pool_id INNER JOIN (SELECT d.db_pool_id as id, REPLACE(SUBSTRING(d.url, 1, LOCATE('/?', d.url)-1), 'jdbc:mysql://', '') AS host_port FROM db_pool d) AS hp ON d.db_pool_id=hp.id;</nowiki>
 +
 
 +
If you also want to see login name and password for the given db hosts, add <code>d.login, d.password</code> to the fields returned by the query.
 +
 
 +
On a lab machine with 10 schemas on one DB instance this looks like:
 +
 
 +
<nowiki>SELECT SUBSTRING(hp.host_port, 1, LOCATE(':', hp.host_port)-1) AS host, SUBSTRING(hp.host_port, LOCATE(':', hp.host_port)+1) AS port, a.db_schema FROM db_pool d INNER JOIN (SELECT write_db_pool_id, db_schema FROM context_server2db_pool GROUP BY db_schema) AS a ON d.db_pool_id=a.write_db_pool_id INNER JOIN (SELECT d.db_pool_id as id, REPLACE(SUBSTRING(d.url, 1, LOCATE('/?', d.url)-1), 'jdbc:mysql://', '') AS host_port FROM db_pool d) AS hp ON d.db_pool_id=hp.id;
 +
+---------+------+-----------+
 +
| host    | port | db_schema |
 +
+---------+------+-----------+
 +
| glb.lan | 5507 | oxdb_9    |
 +
| glb.lan | 5507 | oxdb_8    |
 +
| glb.lan | 5507 | oxdb_7    |
 +
| glb.lan | 5507 | oxdb_6    |
 +
| glb.lan | 5507 | oxdb_5    |
 +
| glb.lan | 5507 | oxdb_14  |
 +
| glb.lan | 5507 | oxdb_13  |
 +
| glb.lan | 5507 | oxdb_12  |
 +
| glb.lan | 5507 | oxdb_11  |
 +
| glb.lan | 5507 | oxdb_10  |
 +
+---------+------+-----------+
 +
10 rows in set (0.00 sec)</nowiki>
  
 
== What if I have Update Tasks that are LOCKED? ==
 
== What if I have Update Tasks that are LOCKED? ==

Revision as of 12:40, 12 December 2017

Update Task management in Open-Xchange

What is an Update Task?

An update task contains one or more operations on the Open-Xchange mysql database. Update tasks take care to apply changes to database tables which are required for new features or bugfixes.

When are Update Tasks applied?

Update tasks are applied when either the first user logs in to the OX UI or when triggered manually. Update tasks will be applied schema based. Per default, Open-Xchange stores 1000 contexts within a single schema.

Tools to maintain Update Tasks

Run update tasks on all schemas serially

$ /opt/open-xchange/sbin/runallupdate

Run update tasks on one schema

$ /opt/open-xchange/sbin/runupdate -n schema

List executed update tasks for a given schema

$ /opt/open-xchange/sbin/listExecutedUpdateTasks -n schema

Sample output:

$ /opt/open-xchange/sbin/listExecutedUpdateTasks -n oxdatabase_5
 taskName                                                                                                successful lastModified
 [...]
 LOCKED                                                                                              true       2014-02-02 11:35:49 MEZ 
 com.openexchange.jslob.storage.db.groupware.DBJSlobCreateTableTask                                  true       2014-02-02 11:35:52 MEZ 
 com.openexchange.groupware.update.tasks.RemoveUnnecessaryIndexes                                    true       2014-02-02 11:35:54 MEZ 
 com.openexchange.groupware.update.tasks.CreateIcalPrincipalPrimaryKeyTask                           true       2014-02-02 11:35:54 MEZ 
 com.openexchange.groupware.update.tasks.MailAccountAddArchiveTask                                   true       2014-02-02 11:35:54 MEZ 
 com.openexchange.groupware.update.tasks.GenconfAttributesBoolsAddUuidUpdateTask                     true       2014-02-02 11:35:54 MEZ 
 com.openexchange.groupware.update.tasks.HeaderCacheDropFKTask                                       true       2014-02-02 11:35:55 MEZ 
 com.openexchange.groupware.update.tasks.ResourceClearDelTablesTask                                  true       2014-02-02 11:35:55 MEZ 
 com.openexchange.groupware.update.tasks.AddUUIDForUpdateTaskTable                                   true       2014-02-02 11:35:55 MEZ 
 com.openexchange.groupware.update.tasks.MailAccountAddReplyToTask                                   true       2014-02-02 11:35:55 MEZ 
 com.openexchange.groupware.tasks.database.TasksModifyCostColumnTask                                 true       2014-02-02 11:35:59 MEZ 
 com.openexchange.groupware.update.tasks.PrgContactsLinkageAddUuidUpdateTask                         true       2014-02-02 11:36:00 MEZ 
 com.openexchange.ajax.requesthandler.converters.preview.cache.groupware.PreviewCacheCreateTableTask true       2014-02-02 11:36:00 MEZ 
 com.openexchange.groupware.update.tasks.InfostoreExtendReservedPathsNameTask                        true       2014-02-02 11:36:00 MEZ 
 com.openexchange.contact.storage.rdb.sql.AddFilenameColumnTask                                      true       2014-02-02 11:37:47 MEZ 
 com.openexchange.groupware.update.tasks.GenconfAttributesStringsAddUuidUpdateTask                   true       2014-02-02 11:37:48 MEZ 
 [...]

How to see all schemas?

The database schemas are not exposed via OX tooling. You need to read them from the configdb. In principle you're looking for db_schema entries from the context_server2db_pool table and join that with corresponding lines from the db_pool table to get the database instance the schema is living on.

Sample query (works in general as of writing this documentation, may break on schema updates in future):

SELECT SUBSTRING(hp.host_port, 1, LOCATE(':', hp.host_port)-1) AS host, SUBSTRING(hp.host_port, LOCATE(':', hp.host_port)+1) AS port, a.db_schema FROM db_pool d INNER JOIN (SELECT write_db_pool_id, db_schema FROM context_server2db_pool GROUP BY db_schema) AS a ON d.db_pool_id=a.write_db_pool_id INNER JOIN (SELECT d.db_pool_id as id, REPLACE(SUBSTRING(d.url, 1, LOCATE('/?', d.url)-1), 'jdbc:mysql://', '') AS host_port FROM db_pool d) AS hp ON d.db_pool_id=hp.id;

If you also want to see login name and password for the given db hosts, add d.login, d.password to the fields returned by the query.

On a lab machine with 10 schemas on one DB instance this looks like:

SELECT SUBSTRING(hp.host_port, 1, LOCATE(':', hp.host_port)-1) AS host, SUBSTRING(hp.host_port, LOCATE(':', hp.host_port)+1) AS port, a.db_schema FROM db_pool d INNER JOIN (SELECT write_db_pool_id, db_schema FROM context_server2db_pool GROUP BY db_schema) AS a ON d.db_pool_id=a.write_db_pool_id INNER JOIN (SELECT d.db_pool_id as id, REPLACE(SUBSTRING(d.url, 1, LOCATE('/?', d.url)-1), 'jdbc:mysql://', '') AS host_port FROM db_pool d) AS hp ON d.db_pool_id=hp.id;
 +---------+------+-----------+
 | host    | port | db_schema |
 +---------+------+-----------+
 | glb.lan | 5507 | oxdb_9    |
 | glb.lan | 5507 | oxdb_8    |
 | glb.lan | 5507 | oxdb_7    |
 | glb.lan | 5507 | oxdb_6    |
 | glb.lan | 5507 | oxdb_5    |
 | glb.lan | 5507 | oxdb_14   |
 | glb.lan | 5507 | oxdb_13   |
 | glb.lan | 5507 | oxdb_12   |
 | glb.lan | 5507 | oxdb_11   |
 | glb.lan | 5507 | oxdb_10   |
 +---------+------+-----------+
 10 rows in set (0.00 sec)

What if I have Update Tasks that are LOCKED?

If the command listExecutedUpdateTasks lists tasks that have the word LOCKED in the taskName row, these tasks could not be completed. This usually happens when Open-Xchange is being stopped while the update tasks are still running.

Do NOT stop Open-Xchange while Update Tasks are running!

If that happened to you, you need to manually remove these locks. In order to do that, you have to remove the rows from the table updateTask in every schema which have taskName set to LOCKED.

mysql> DELETE FROM updateTask WHERE taskName='LOCKED';

If you have multiple schemas, you can list all of them which contain that lock e.g. using this command:

 for i in $(echo show databases | mysql -uopenexchange -psecret | grep oxdatabase); do \
      echo "select taskName from ${i}.updateTask where taskName=\"LOCKED\"" | \
      mysql -uopenexchange -psecret | grep LOCKED > /dev/null && echo "database $i has a LOCK"; done

now for each of these schemas, run the sql query

mysql> DELETE FROM updateTask WHERE taskName='LOCKED';

If you're still having issues with locked tasks (check /var/log/open-xchange/ for logs), you might also need to clear the lock in the version table in the corresponding schema(s).

mysql> SELECT * from version;
+---------+--------+---------------+------------------+--------------+
| version | locked | gw_compatible | admin_compatible | server       |
+---------+--------+---------------+------------------+--------------+
|     200 |      1 |             1 |                1 | oxserver     |
+---------+--------+---------------+------------------+--------------+

if locked is set to 1, run

mysql> UPDATE version SET locked=0;

What if I have Update Tasks that are in status false?

If the command listExecutedUpdateTasks lists tasks that have the word false in the successful row,

taskName successful lastModified
com.openexchange.groupware.update.tasks.PrgDatesPrimaryKeyUpdateTask false 2013-11-19 17:16:32 CET

a reason for that could be database servers that died under the high IO load of updating multiple schemas concurrently. To solve this problem, run the command /opt/open-xchange/sbin/forceupdatetask on the affected schema.

To prevent this situation, we recommend to run the updatetasks during low-traffic times, e.g. in the night on a machine that is not available to customers.