Skip to content

# How common_schema installs itself

MySQL common_schema Installation QueryScript September 11, 2012September 11, 2012

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!

- Twitter
- LinkedIn
- WhatsApp
- Email
- Facebook
- Print

###
3
Leave a Reply

3 Comment threads
0 Thread replies
0 Followers
Most reacted comment
Hottest comment thread
3 Comment authors
Recent comment authors

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Subscribe
Notify of
Guest
Justin Swanhart

Another way, that I’ve been experimenting with is to create a temporary stored procedure that contains the installation logic, then dropping the stored procedure after installation is completed.

Author
shlomi

@Justin,
Cool. Well, since I wrote a scripting interpreter, I might as well use it!

Guest
Database Scene

Retweeted on https://twitter.com/Database_Scene
Good Job Shlomi!

wpDiscuz
Send to Email Address Your Name Your Email Address Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.