# MaxScale 22.08 SQL Resource ## SQL Resource ## SQL Resource The SQL resource represents a database connection. * [SQL Resource](#sql-resource) * [SQL Connection Interface](#sql-connection-interface) * [Request Parameters](#request-parameters) * [Get one SQL connection](#get-one-sql-connection) * [Response](#response) * [Get all SQL connections](#get-all-sql-connections) * [Response](#response_1) * [Open SQL connection to server](#open-sql-connection-to-server) * [Request Parameters](#request-parameters_1) * [Response](#response_2) * [Close an opened SQL connection](#close-an-opened-sql-connection) * [Response](#response_3) * [Reconnect an opened SQL connection](#reconnect-an-opened-sql-connection) * [Response](#response_4) * [Clone an existing SQL connection](#clone-an-existing-sql-connection) * [Request Parameters](#request-parameters_2) * [Response](#response_5) * [Execute SQL query](#execute-sql-query) * [Response](#response_6) ### SQL Connection Interface The following endpoints provide a simple REST API interface for executing\ SQL queries on servers and services in MaxScale. This document uses the `:id` value in the URL to represent a connection ID and\ the `:query_id` to represent a query ID. These values do not need to be manually\ added as the relevant links are returned in the request body of each endpoint. The endpoints use JSON Web Tokens to uniquely identify open SQL connections. A\ connection token can be acquired with a `POST /v1/sql` request and can be used\ with the `POST /v1/sql/:id/query`, `GET /v1/sql/:id/results/:query_id` and`DELETE /v1/sql` endpoints. All of these endpoints accept a connection token in\ the `token` parameter of the request: ``` POST /v1/sql/query?token=eyJhbGciOiJIUzI1NiJ9.eyJhdWQiOiJhZG1pbiIsImV4cCI6MTU4MzI1NDE1MSwiaWF0IjoxNTgzMjI1MzUxLCJpc3MiOiJtYXhzY2FsZSJ9.B1BqhjjKaCWKe3gVXLszpOPfeu8cLiwSb4CMIJAoyqw ``` In addition to request parameters, the token can be stored in cookies in which\ case they are automatically used by the REST API. For more information about\ token storage in cookies, see the documentation for `POST /v1/sql`. ### Request Parameters All of the endpoints that operate on a single connection support the following\ request parameters. The `GET /v1/sql` and `GET /v1/sql/:id` endpoints are an\ exception as they ignore the current connection token. * `token` * The connection token to use for the request. If provided, the value is\ unconditionally used even if a cookie with a valid token exists. #### Get one SQL connection ``` GET /v1/sql/:id ``` **Response** Response contains the requested resource. `Status: 200 OK` ``` { "data": { "id": "5", "links": { "related": "http://localhost:8989/v1/sql/5/queries/", "self": "http://localhost:8989/v1/sql/5/" }, "type": "sql" }, "links": { "self": "http://localhost:8989/v1/sql/5/" }, "meta": { "token": "eyJhbGciOiJIUzI1NiJ9.eyJhdWQiOiI1IiwiZXhwIjoxNjIwMjM1Mzc3LCJpYXQiOjE2MjAyMDY1NzcsImlzcyI6Im14cy1xdWVyeSJ9.2CJ8DsEPbGlvs2DrBUC6FJA64VMSU8kbX1U4FSu2-OY" } } ``` #### Get all SQL connections ``` GET /v1/sql ``` **Response** Response contains a resource collection with all the open SQL connections. `Status: 200 OK` ``` { "data": [ { "id": "10", "links": { "related": "http://localhost:8989/v1/sql/10/queries/", "self": "http://localhost:8989/v1/sql/10/" }, "type": "sql" }, { "id": "11", "links": { "related": "http://localhost:8989/v1/sql/11/queries/", "self": "http://localhost:8989/v1/sql/11/" }, "type": "sql" } ], "links": { "self": "http://localhost:8989/v1/sql/" } } ``` #### Open SQL connection to server ``` POST /v1/sql ``` The request body must be a JSON object consisting of the following fields: * `target` * The object in MaxScale to connect to. This is a mandatory value and the\ given value must be the name of a valid server, service or listener in\ MaxScale. * `user` * The username to use when creating the connection. This is a mandatory value. * `password` * The password for the user. This is a mandatory value. * `db` * The default database for the connection. By default the connection will have\ no default database. * `timeout` * Connection timeout in seconds. The default connection timeout is 10\ seconds. This controls how long the SQL connection creation can take before\ an error is returned. Here is an example request body: ``` { "user": "jdoe", "password": "my-s3cret", "target": "server1", "db": "test", "timeout": 15 } ``` The response will contain the new connection with the token stored at`meta.token`. If the request uses the `persist=yes` request parameter, the token\ is stored in cookies instead of the metadata object and the response body will\ not contain the token. The location of the newly created connection will be stored at `links.self` in\ the response body as well as in the `Location` header. The token must be given to all subsequent requests that use the connection. It\ must be either given in the `token` parameter of a request or it must be stored\ in the cookies. If both a `token` parameter and a cookie exist at the same time,\ the `token` parameter will be used instead of the cookie. **Request Parameters** This endpoint supports the following request parameters. * `persist` * Store the connection token in cookies instead of returning it as the response body.\ This parameter expects only one value, `yes`, as its argument. When`persist=yes` is set, the token is stored in the `conn_id_sig_` cookie\ where the `` part is replaced by the ID of the connection. * `max-age` * Sets the connection token maximum age in seconds. The default is`max-age=28800`. Only positive values are accepted and if a non-positive or\ a non-integer value is found, the parameter is ignored. Once the token age\ exceeds the configured maximum value, the token can no longer be used and a\ new connection must be created. **Response** Connection was opened: `Status: 201 Created` ``` { "data": { "id": "5", "attributes": { "thread_id": 12, "seconds_idle": 5 } "links": { // The "related" endpoint is the URL to the query endpoint for this connection. "related": "http://localhost:8989/v1/sql/5/queries/", "self": "http://localhost:8989/v1/sql/5/" }, "type": "sql" }, "links": { "self": "http://localhost:8989/v1/sql/5/" }, "meta": { "token": "eyJhbGciOiJIUzI1NiJ9.eyJhdWQiOiI1IiwiZXhwIjoxNjIwMjM1Mzc3LCJpYXQiOjE2MjAyMDY1NzcsImlzcyI6Im14cy1xdWVyeSJ9.2CJ8DsEPbGlvs2DrBUC6FJA64VMSU8kbX1U4FSu2-OY" } } ``` Missing or invalid payload: `Status: 400 Bad Request` #### Close an opened SQL connection ``` DELETE /v1/sql/:id ``` **Response** Connection was closed: `Status: 204 No Content` Missing or invalid connection token: `Status: 400 Bad Request` #### Reconnect an opened SQL connection ``` POST /v1/sql/:id/reconnect ``` Reconnects an existing connection. This can also be used if the connection to\ the backend server was lost due to a network error. The connection will use the same credentials that were passed to the `POST /v1/sql` endpoint. The new connection will still have the same ID in the REST\ API but will be treated as a new connection by the database. A reconnection\ re-initializes the connection and resets the session state. Reconnections cannot\ take place while a transaction is open. **Response** Reconnection was successful: `Status: 204 No Content` Reconnection failed or connection is already in use: `Status: 503 Service Unavailable` Missing or invalid connection token: `Status: 400 Bad Request` #### Clone an existing SQL connection ``` POST /v1/sql/:id/clone ``` Clones an existing connection. This is done by opening a new connection using\ the credentials and configuration from the given connection. **Request Parameters** This endpoint supports the same request parameters as the `POST /v1/sql`\ endpoint. **Response** The response is identical to the one in the `POST /v1/sql` endpoint. In\ addition, this endpoint can return the following responses. Connection is already in use: `Status: 503 Service Unavailable` Missing or invalid connection token: `Status: 400 Bad Request` #### Execute SQL query ``` POST /v1/sql/:id/queries ``` The request body must be a JSON object with the value of the `sql` field set to\ the SQL to be executed: ``` { "sql": "SELECT * FROM test.t1", "max_rows": 1000 } ``` The request body must be a JSON object consisting of the following fields: * `sql` * The SQL to be executed. If the SQL contain multiple statements, multiple\ results are returned in the response body. * `max_rows` * The maximum number of rows returned in the response. By default this is 1000\ rows. Setting the value to 0 means no limit. Any extra rows in the result\ will be discarded. By default, the complete result is returned in the response body. If the SQL\ query returns more than one result, the `results` array will contain all the\ results. The `results` array can have three types of objects: resultsets, errors, and OK\ responses. * A resultset consists of the `data` field with the result data stored as a two\ dimensional array. The names of the fields are stored in an array in the`fields` field. These types of results will be returned for any operation that\ returns rows (i.e. `SELECT` statements) ``` { "data": { "attributes": { "results": [ { "data": [ [ 1 ], [ 2 ], [ 3 ] ], "fields": [ "id" ] } ], "sql": "select * from t1" }, "id": "9-1", "type": "queries" }, "links": { "self": "http://localhost:8989/v1/sql/9/queries/9-1/" } } ``` * An error consists of an object with the `errno` field set to the MariaDB error\ code, the `message` field set to the human-readable error message and the`sqlstate` field set to the current SQLSTATE of the connection. ``` { "data": { "attributes": { "results": [ { "errno": 1064, "message": "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'table t1' at line 1", "sqlstate": "42000" } ], "sql": "select syntax_error from table t1" }, "id": "4-1", "type": "queries" }, "links": { "self": "http://localhost:8989/v1/sql/4/queries/4-1/" } } ``` * An OK response is returned for any result that completes successfully but not\ return rows (e.g. an `INSERT` or `UPDATE` statement). The `affected_rows`\ field contains the number of rows affected by the operation, the`last_insert_id` contains the auto-generated ID and the `warnings` field\ contains the number of warnings raised by the operation. ``` { "data": { "attributes": { "results": [ { "affected_rows": 0, "last_insert_id": 0, "warnings": 0 } ], "sql": "drop table t1" }, "id": "6-1", "type": "queries" }, "links": { "self": "http://localhost:8989/v1/sql/6/queries/6-1/" } } ``` It is also possible for the fields of the error response to be present in the\ resultset response if the result ended with an error but still generated some\ data. Usually this happens when query execution is interrupted but a partial\ result was generated by the server. **Response** Query successfully executed: `Status: 201 Created` ``` { "data": { "attributes": { "results": [ { "affected_rows": 0, "last_insert_id": 0, "warnings": 0 } ], "sql": "drop table t1" }, "id": "6-1", "type": "queries" }, "links": { "self": "http://localhost:8989/v1/sql/6/queries/6-1/" } } ``` Invalid payload or missing connection token: `Status: 400 Bad Request` Fatal connection error: `Status: 503 Service Unavailable` * If the API returns this response, the connection to the database server was\ lost. The only valid action to take at this point is to close it with the`DELETE /v1/sql/:id` endpoint. CC BY-SA / Gnu FDL