Skip to content

MySQL

Export Configuration#

Exporting a MySQL server via a Tranquil Data peer extends the configuration object with the following fields:

Field Name Type Required Default
database string tranquil_db
endpoint string defaultServiceHost:3306
user string
password string

The value of type must be mysql.

The value of database is the name of the MySQL Database (created via the CREATE DATABASE command) that should be used. All operations will be run on this named database. If the named database does not exist, and createDatabase is set to true, the database will be automatically created.

The value of endpoint is a string of the form HOST:PORT, where the value for PORT is 3306 if absent.

The values of user and password are used to authenticate to the backing datastore.

Context Interaction#

The MySQL record identity is based on the PRIMARY INDEX key. For the InnoDB database engine, this index always exists. The PRIMARY INDEX key is either an explicitly designated PRIMARY KEY, or, if no columns are specified, the first column with UNIQUE values. In the unlikely case where the table schema contains neither PRIMARY KEY nor a UNIQUE column, the associated record will not become part of context.

Tranquil Data supports context formation and policy evaluation on a subset of SQL statement syntax. For INSERT, the following variants are supported:

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY]
[INTO] tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
(col_name [, col_name] ...)
{VALUES | VALUE} (value_list) [, (value_list)] ...

For SELECT, the following varients are supported:

SELECT
    [ALL | DISTINCT | DISTINCTROW ]
      [STRAIGHT_JOIN]
      [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
      [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr [, select_expr ...]
[FROM table_references
      [PARTITION partition_list]
[WHERE where_condition]
[GROUP BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]
[HAVING where_condition]
[ORDER BY {col_name | expr | position}
      [ASC | DESC], ...]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]

For UPDATE, the following varients are supported:

UPDATE [LOW_PRIORITY] [IGNORE] table_reference
SET col_name = {expr | DEFAULT} [, col_name =  {expr | DEFAULT}] ...
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]

Any MySQL operations not called out above, and any of the above operations applied to system or temporary tables, will be passed to the MySQL server for execution outside the context model. This includes:

  • Nested statements, that is INSERT, UPDATE, or SELECT based on the results of a subquery
  • Statements which are based on server-side functions or aggregations
  • Statements that are part of the PREPARE and EXECUTE construct

Note that Tranquil Data does not currently support switching to different databases within an active client connection. All USE <database> commands map to the database specified in the database export parameter. Thus, all client queries will execute within the specified database.