A schema upgrade refers to any DDL statement run for the database. DDL statements change the database structure and are non-transactional.
DDL statements are processed in two different methods in Galera Cluster. These methods are described in the chapters below.
Note
See also the pt-online-schema-change command in Percona Toolkit for MySQL: http://www.percona.com/software/percona-toolkit.
By default, DDL statements are processed by using the Total Order Isolation (TOI) method. In TOI, the query is replicated to the nodes in a statement form before executing on master. The query waits for all preceding transactions to commit and then gets executed in isolation on all nodes simultaneously. When using the TOI method, the cluster has a part of the database locked for the duration of the DDL processing (in other words, the cluster behaves like a single server).
The isolation can take place at the following levels:
TOI queries have several particularities that must been taken into consideration:
As of wsrep patch 5.5.17-22.3, you can choose whether to use the traditional total order isolation method or the rolling schema upgrade method. You can choose the rolling schema upgrade method by using the global parameter wsrep_OSU_method.
The rolling schema upgrade is a DDL processing method, where the DDL will only be processed locally at the node. The node is desynchronized from the cluster for the duration of the DDL processing in a way that it does not block the rest of the nodes. When the DDL processing is complete, the node applies the delayed replication events and synchronizes back with the cluster.
To upgrade a schema cluster-wide, the DDL must be manually executed at each node in turn. When the rolling schema upgrade proceeds, a part of the cluster will have the old schema structure and a part of the cluster will have the new schema structure.
Warning
While the rolling schema upgrade has the advantage of blocking only one node at a time, it is potentially unsafe, and may fail if the new and old schema definitions are incompatible at the replication event level. Execute operations such as CREATE ATBLE and DROP TABLE in TOI.