How common_schema installs itself

Up till common_schema version 1.1, the user would need to choose from distinct distribution files: an install compatible with MySQL 5.1, one compatible with InnoDB Plugin enabled servers, and one compatible with Percona Server. The difference between the three is the availability of certain INFORMATION_SCHEMA tables.

With 1.1, this is no longer the case: common_schema auto-detects the server and available feature set, and installs accordingly.

Wait, isn’t common_schema just a SQL file?

Yes. It’s not like there’s an installer like InstallShield or anything. Nevertheless, common_schema offers a smart way of conditional handling, which is uses in itself. It’s called QueryScript.

common_schema is installed by importing the SQL file (via SOURCE command; the mysql client; your favorite GUI). This creates your usual tables, views and routines. But some of these routines make for an interpreter for QueryScript. Somewhere along the installation process (remember – it’s just a SQL import), common_schema switches over to executing scripts to manage the installation. In particular, there are a few views which depend on optional tables, such as InnoDB Plugin’s tables for INFORMATION_SCHEMA.

Consider the following CREATE VIEW statement:

CREATE OR REPLACE
ALGORITHM = UNDEFINED
SQL SECURITY INVOKER
VIEW innodb_transactions_summary AS
  SELECT 
    COUNT(*) AS count_transactions,
    IFNULL(SUM(trx_state = 'RUNNING'), 0) AS running_transactions,
    IFNULL(SUM(trx_requested_lock_id IS NOT NULL), 0) AS locked_transactions,
    COUNT(DISTINCT trx_requested_lock_id) AS distinct_locks
  FROM 
    INFORMATION_SCHEMA.INNODB_TRX
  WHERE 
    trx_mysql_thread_id != CONNECTION_ID()
;

Such a statement would fail the import if the underlying tables (INFORMATION_SCHEMA.INNODB_TRX in our example) do not actually exist. Which is why this CREATE statement is not invoked just like that. It is wrapped within a script:

set @script := "
  set @common_schema_innodb_plugin_expected := 0;
  try {
    set @common_schema_innodb_plugin_expected := @common_schema_innodb_plugin_expected + 1; 

    CREATE VIEW ... (as above)

    set @common_schema_innodb_plugin_installed := @common_schema_innodb_plugin_installed + 1;
  }
  catch {
  }
";

call run(@script);

I really want to stress this: this is a try-catch statement running from within your MySQL server. QueryScript accepts this statement and behaves very similarly to the way you are used to with your favorite programming language.

If the CREATE VIEW cannot be fulfilled, an error is generated. But instead of failing the entire script, it is caught by the catchclause; the “set @common_schema_innodb_plugin_installed := …” line is never executed upon such error.

Upon CREATE VIEW failure, @common_schema_innodb_plugin_installed falls short of @common_schema_innodb_plugin_expected and . By the end of installation process, the two are compared, so as to determine the install success:

+-----------------------------------------------------------+
| complete                                                  |
+-----------------------------------------------------------+
| - Base components: installed                              |
| - InnoDB Plugin components: installed                     |
| - Percona Server components: not installed                |
|                                                           |
| Installation complete. Thank you for using common_schema! |
+-----------------------------------------------------------+

Can’t you just use “mysql –force”?

So, one can import a SQL file using mysql –force, which does nto break down on first sight of error, but continues to next statements.

True, but:

• I don’t want to ask that of the user.
• It won’t give me a status on what went wrong.

By using QueryScript‘s try-catch:

• I can take alternate action on failure.
• common_schema can (and does) inform the user at the end of installation process which components are installed and which are not.
• It can (and does) write this info down to a metadata table such that I can ask the user for that information when submitting a bug report.

Eat your own dog food!