Forum: Managing VoltDB

Post: Performing a live catalog update using the command line

Performing a live catalog update using the command line
Aug 21, 2012
This post describes how to update the catalog of a running database using the command line. It will provide an example of how to execute this from the command line, how to know that it succeeded, and how to know when to use this procedure or when the maintenance window process should be used instead.

VoltDB requires all DDL and stored procedures to be pre-compiled into a catalog file which is passed to VoltDB on database startup. Though you can still interact with VoltDB via ad hoc queries in a conversational manner, pre-compiling the high velocity SQL enables the SQL to be planned and optimized ahead of time for maximum throughput. Should you wish to change your database schema or stored procedures once the database is running, VoltDB has a system procedure that can perform a live update to the catalog of a running database.

Note that these operations are fully automated via our user interface management tool, the VoltDB Enterprise Manager (available with VoltDB Enterprise Edition). Updating the catalog with VoltDB Enterprise Manager can be performed following the steps in the the Management Guide.

Executing a live catalog update
The following example is for a VoltDB database that is managed from the command line or command line scripts.
For a more detailed explanation of this process, see Chapter 7 of Using VoltDB, specifically section 7.2 Updating the Stored Procedures. The command to update a catalog interactively from one of the nodes in the cluster is:

sqlcmdSQL Command :: localhost:212121> exec @UpdateApplicationCatalog /path/to/save/catalog.jar /path/to/deployment.xmlSTATUS ------- 0(1 row(s) affected)SQLCMD is a command line program located in the VoltDB bin directory. You can add this directory to your path for convenience. The example above omitted the servers and port parameters, which default to locahost:21212.
If you want to run this command in a script, you could use one of the following forms. Here, you may want to provide the full parameters.

echo "exec @UpdateApplicationCatalog /path/to/save/catalog.jar /path/to/deployment.xml" | sqlcmd --servers=localhost --port=21211

sqlcmd --servers=localhost --port=21211 <<<"exec @UpdateApplicationCatalog /path/to/save/catalog.jar /path/to/deployment.xml"If this command is successful, you will see the following output:

STATUS ------- 0(1 row(s) affected)If you are trying to make an unsupported online change, the procedure will detect this, the operation will fail and output an error message. For example, if you added an index on an existing table in your DDL, recompiled the catalog and attempted a live update, you would get this message:

The requested catalog change is not a supported change at this time. May not dynamically add/drop: Index{IX_CONTESTANT_NAME}When to perform a live catalog update
Using VoltDB provides a reference for @UpdateApplicationCatalog, and lists online database modifications that are supported, such as adding, removing or modifying stored procedures, and adding or removing tables from the schema. Additional online operations are being enabled in subsequent product releases.

One thing to be careful about is keeping track of the correct catalog files. One of the supported online database modifications is to drop a table, which is done by simply omitting the table definition from the DDL in a catalog update. Tables can be dropped whether they contain data or not. Normally a change like this is intentional, but if you used the wrong catalog file it could lead to unintended consequences. For example, if you ran @UpdateApplicationCatalog with the catalog file from a different application, it probably has none of the same tables or procedures as the existing catalog, and could result in the dropping of all the tables and the creation of new, different tables.

Modifications that aren't supported by the UpdateApplicationCatalog procedure require a maintenance window to stop the database, restart with the new catalog, and restore from a snapshot. Please see this forum post with more information on how to perform that process from the command line.