# Module mysql

- Description
- Data Types
- Function Index
- Function Details

MySQL client.

## Description

MySQL client.

The `connection()` type is a gen_server reference as described in the documentation for `gen_server:call/2,3`, e.g. the pid or the name if the gen_server is locally registered.

## Data Types

### column_name()

`column_name() = binary()`

### connection()

`connection() = atom() | {Name::atom(), Node::atom()} | {global, GlobalName::term()} | {via, Module::atom(), ViaName::term()} | pid()`

### option()

`option() = {name, ServerName::server_name()} | {host, inet:socket_address() | inet:hostname()} | {port, integer()} | {user, iodata()} | {password, iodata()} | {database, iodata()} | {connect_mode, synchronous | asynchronous | lazy} | {connect_timeout, timeout()} | {allowed_local_paths, [binary()]} | {log_warnings, boolean()} | {log_slow_queries, boolean()} | {keep_alive, boolean() | timeout()} | {prepare, [{StatementName::statement_name(), Statement::query()}]} | {queries, [query()]} | {query_timeout, timeout()} | {found_rows, boolean()} | {query_cache_time, non_neg_integer()} | {tcp_options, [gen_tcp:connect_option()]} | {ssl, term()} | {float_as_decimal, boolean() | non_neg_integer()}`

### query()

`query() = iodata()`

### query_filtermap_fun()

`query_filtermap_fun() = fun((row()) -> query_filtermap_res()) | fun(([column_name()], row()) -> query_filtermap_res())`

### query_filtermap_res()

`query_filtermap_res() = boolean() | {true, term()}`

### query_param()

`query_param() = term()`

### query_result()

`query_result() = ok | {ok, [column_name()], [row()]} | {ok, [{[column_name()], [row()]}, ...]} | {error, server_reason()}`

### row()

`row() = [term()]`

### server_name()

`server_name() = {local, Name::atom()} | {global, GlobalName::term()} | {via, Via::module(), ViaName::term()}`

### server_reason()

`server_reason() = {Code::integer(), SQLState::binary() | undefined, Message::binary()}`

### statement_id()

`statement_id() = integer()`

### statement_name()

`statement_name() = atom()`

### statement_ref()

`statement_ref() = statement_id() | statement_name()`

### transaction_result()

`transaction_result(Result) = {atomic, Result} | {aborted, Reason::term()}`

## Function Index

affected_rows/1 | Returns the number of inserted, updated and deleted rows of the last executed query or prepared statement.
autocommit/1 | Returns true if auto-commit is enabled and false otherwise.
change_user/3 | Equivalent to `change_user(Conn, Username, Password, [])`.
change_user/4 | Changes the user of the active connection without closing and and re-opening it.
encode/2 | Encodes a term as a MySQL literal so that it can be used to inside a query.
execute/3 | Executes a prepared statement with the default query timeout as given to start_link/1.
execute/4 | Executes a prepared statement.
execute/5 | Executes a prepared statement.
in_transaction/1 | Returns true if the connection is in a transaction and false otherwise.
insert_id/1 | Returns the last insert-id.
prepare/2 | Creates a prepared statement from the passed query.
prepare/3 | Creates a prepared statement from the passed query and associates it with the given name.
query/2 | Executes a plain query.
query/3 | Executes a query.
query/4 | Executes a query.
query/5 | Executes a query.
reset_connection/1 |
start_link/1 | Starts a connection gen_server process and connects to a database.
stop/1 |
stop/2 | Stops a connection process and closes the connection.
transaction/2 | This function executes the functional object Fun as a transaction.
transaction/3 | This function executes the functional object Fun as a transaction.
transaction/4 | This function executes the functional object Fun with arguments Args as a transaction.
unprepare/2 | Deallocates a prepared statement.
warning_count/1 | Returns the number of warnings generated by the last query/2 or execute/3 calls.

## Function Details

### affected_rows/1

`affected_rows(Conn::connection()) -> integer()`

Returns the number of inserted, updated and deleted rows of the last executed query or prepared statement. If found_rows is set on the connection, for update operation the return value will equal to the number of rows matched by the query.

### autocommit/1

`autocommit(Conn::connection()) -> boolean()`

Returns true if auto-commit is enabled and false otherwise.

### change_user/3

`change_user(Conn, Username, Password) -> Result`

- `Conn = connection()`
- `Username = iodata()`
- `Password = iodata()`
- `Result = ok`

Equivalent to `change_user(Conn, Username, Password, [])`.

See also: change_user/4.

### change_user/4

`change_user(Conn, Username, Password, Options) -> Result`

- `Conn = connection()`
- `Username = iodata()`
- `Password = iodata()`
- `Options = [Option]`
- `Result = ok`
- `Option = {database, iodata()} | {queries, [query()]} | {prepare, [NamedStatement]}`
- `NamedStatement = {StatementName::statement_name(), Statement::query()}`

Changes the user of the active connection without closing and and re-opening it. The currently active session will be reset (ie, user variables, temporary tables, prepared statements, etc will be lost) independent of whether the operation succeeds or fails.

If change user is called when a transaction is active (ie, neither committed nor rolled back), calling `change_user` will fail with an error exception and `change_user_in_transaction` as the error message.

If the change user operation fails, `{error, Reason}` will be returned. Specifically, if the operation itself fails (eg authentication failure), `change_user_failed` will be returned as the reason, while if the operation itself succeeds but one of the given initial queries or prepares fails, the reason will reflect the cause for the failure. In any case, the connection process will exit with the same reason and cannot be used any longer.

For a description of the `database`, `queries` and `prepare` options, see `start_link/1`.

See also: start_link/1.

### encode/2

`encode(Conn::connection(), Term::term()) -> iodata()`

Encodes a term as a MySQL literal so that it can be used to inside a query. If backslash escapes are enabled, backslashes and single quotes in strings and binaries are escaped. Otherwise only single quotes are escaped.

Note that the preferred way of sending values is by prepared statements or parametrized queries with placeholders.

See also: execute/3, query/3.

### execute/3

`execute(Conn, StatementRef, Params) -> Result | {error, not_prepared}`

- `Conn = connection()`
- `StatementRef = statement_ref()`
- `Params = [query_param()]`
- `Result = query_result()`

Executes a prepared statement with the default query timeout as given to start_link/1.

See also: execute/5, prepare/2, prepare/3, prepare/4.

### execute/4

`execute(Conn, StatementRef, Params, Timeout::FilterMap | Timeout) -> Result | {error, not_prepared}`

- `Conn = connection()`
- `StatementRef = statement_ref()`
- `Params = [query_param()]`
- `FilterMap = query_filtermap_fun()`
- `Timeout = timeout()`
- `Result = query_result()`

Executes a prepared statement.

See also: execute/5, prepare/2, prepare/3, prepare/4.

### execute/5

`execute(Conn, StatementRef, Params, FilterMap, Timeout) -> Result | {error, not_prepared}`

- `Conn = connection()`
- `StatementRef = statement_ref()`
- `Params = [query_param()]`
- `FilterMap = query_filtermap_fun()`
- `Timeout = timeout()`
- `Result = query_result()`

Executes a prepared statement.

The `FilterMap` and `Timeout` arguments are optional.

- If the `FilterMap` argument is the atom `no_filtermap_fun` or is omitted, no row filtering/mapping will be applied and all result rows will be returned unchanged.
- If the `Timeout` argument is the atom `default_timeout` or is omitted, the timeout given in `start_link/1` is used.

See `query/5` for an explanation of the `FilterMap` argument.

See also: prepare/2, prepare/3, prepare/4, query/5.

### in_transaction/1

`in_transaction(Conn::connection()) -> boolean()`

Returns true if the connection is in a transaction and false otherwise. This works regardless of whether the transaction has been started using transaction/2,3 or using a plain `mysql:query(Connection, "BEGIN")`.

See also: transaction/2, transaction/4.

### insert_id/1

`insert_id(Conn::connection()) -> integer()`

Returns the last insert-id.

### prepare/2

`prepare(Conn, Query) -> {ok, StatementId} | {error, Reason}`

- `Conn = connection()`
- `Query = query()`
- `StatementId = statement_id()`
- `Reason = server_reason()`

Creates a prepared statement from the passed query.

See also: prepare/3.

### prepare/3

`prepare(Conn, Name, Query) -> {ok, Name} | {error, Reason}`

- `Conn = connection()`
- `Name = statement_name()`
- `Query = query()`
- `Reason = server_reason()`

Creates a prepared statement from the passed query and associates it with the given name.

See also: prepare/2.

### query/2

`query(Conn, Query) -> Result`

- `Conn = connection()`
- `Query = iodata()`
- `Result = query_result()`

Executes a plain query.

See also: query/5.

### query/3

`query(Conn, Query, Params::Params | FilterMap | Timeout) -> Result`

- `Conn = connection()`
- `Query = query()`
- `Timeout = timeout()`
- `Params = [query_param()]`
- `FilterMap = query_filtermap_fun()`
- `Result = query_result()`

Executes a query.

See also: query/5.

### query/4

`query(Conn, Query, Params, Timeout) -> Result`

- `Conn = connection()`
- `Query = query()`
- `Timeout = timeout()`
- `Params = [query_param()]`
- `Result = query_result()`

`query(Conn, Query, FilterMap, Timeout) -> Result`

- `Conn = connection()`
- `Query = query()`
- `Timeout = timeout()`
- `FilterMap = query_filtermap_fun()`
- `Result = query_result()`

`query(Conn, Query, Params, FilterMap) -> Result`

- `Conn = connection()`
- `Query = query()`
- `Params = [query_param()]`
- `FilterMap = query_filtermap_fun()`
- `Result = query_result()`

Executes a query.

See also: query/5.

### query/5

`query(Conn, Query, Params, FilterMap, Timeout) -> Result`

- `Conn = connection()`
- `Query = query()`
- `Timeout = timeout()`
- `Params = [query_param()]`
- `FilterMap = query_filtermap_fun()`
- `Result = query_result()`

Executes a query.

#### Parameters

`Conn` is identifying a connection process started using `mysql:start_link/1`.

`Query` is the query to execute, as a binary or a list.

`Params`, `FilterMap` and `Timeout` are optional.

If `Params` (a list) is specified, the query is performed as a prepared statement. A prepared statement is created, executed and then cached for a certain time (specified using the option `{query_cache_time, Milliseconds}` to `start_link/1`). If the same query is executed again during this time, it does not need to be prepared again. If `Params` is omitted, the query is executed as a plain query. To force a query without parameters to be executed as a prepared statement, an empty list can be used for `Params`.

If `FilterMap` (a fun) is specified, the function is applied to each row to filter or perform other actions on the rows, in a way similar to how `lists:filtermap/2` works, before the result is returned to the caller. See below for details.

`Timeout` specifies the time to wait for a response from the database. If omitted, the timeout given in `start_link/1` is used.

#### Return value

Results are returned in the form `{ok, ColumnNames, Rows}` if there is one result set. If there are more than one result sets, they are returned in the form `{ok, [{ColumnNames, Rows}, ...]}`. This is typically the case if multiple queries are specified at the same time, separated by semicolons.

For queries that don't return any rows (INSERT, UPDATE, etc.) only the atom `ok` is returned.

#### FilterMap details

If the `FilterMap` argument is used, it must be a function of arity 1 or 2 that returns either `true`, `false`, or `{true, Value}`.

Each result row is handed to the given function as soon as it is received from the server, and only when the function has returned, the next row is fetched. This provides the ability to prevent memory exhaustion. On the other hand, it can cause the server to time out on sending if your function is doing something slow (see the MySQL documentation on `NET_WRITE_TIMEOUT`).

If the function is of arity 1, only the row is passed to it as the single argument, while if the function is of arity 2, the column names are passed in as the first argument and the row as the second.

The value returned is then used to decide if the row is to be included in the result(s) returned from the `query` call (filtering), or if something else is to be included in the result instead (mapping). You may also use this function for side effects, like writing rows to disk or sending them to another process etc.

#### Examples

Here is an example showing some of the things that are possible:

      Query = "SELECT a, b, c FROM foo",
      FilterMap = fun
          %% Include all rows where the first column is < 10.
          ([A|_]) when A < 10 ->
              true;
          %% Exclude all rows where the first column is >= 10 and < 20.
          ([A|_]) when A < 20 ->
              false;
          %% For rows where the first column is >= 20 and < 30, include
          %% the atom 'foo' in place of the row instead.
          ([A|_]) when A < 30 ->
              {true, foo}};
          %% For rows where the first row is >= 30 and < 40, send the
          %% row to a gen_server via call (ie, wait for a response),
          %% and do not include the row in the result.
          (R=[A|_]) when A < 40 ->
              gen_server:call(Pid, R),
              false;
          %% For rows where the first column is >= 40 and < 50, send the
          %% row to a gen_server via cast (ie, do not wait for a reply),
          %% and include the row in the result, also.
          (R=[A|_]) when A < 50 ->
              gen_server:cast(Pid, R),
              true;
          %% Exclude all other rows from the result.
          (_) ->
              false
      end,
      query(Conn, Query, FilterMap).

### reset_connection/1

`reset_connection(Conn) -> ok | {error, Reason}`

- `Conn = connection()`
- `Reason = server_reason()`

### start_link/1

`start_link(Options::[option()]) -> {ok, pid()} | ignore | {error, term()}`

Starts a connection gen_server process and connects to a database. To disconnect use `mysql:stop/1,2`.

Options:

`{name, ServerName}`
  If a name is provided, the gen_server will be registered with this name. For details see the documentation for the first argument of gen_server:start_link/4.
`{host, Host}`
  Hostname of the MySQL database. Since OTP version 19, it is also possible to specify a local (Unix) Socket by specifying `{local, SocketFile}`. Default `"localhost"`.
`{port, Port}`
  Port; default 3306 for non-local or 0 for local (Unix) sockets.
`{user, User}`
  Username.
`{password, Password}`
  Password.
`{database, Database}`
  The name of the database AKA schema to use. This can be changed later using the query `USE <database>`.
`{connect_mode, synchronous | asynchronous | lazy}`
  Specifies how and when the connection process should establish a connection to the MySQL server.
  `synchronous` (default)
    The connection will be established as part of the connection process' start routine, ie the returned connection process will already be connected and ready to use, and any on-connect prepares and queries will have been executed.
  `asynchronous`
    The connection process will be started and returned to the caller before really establishing a connection to the server and executing the on-connect prepares and executes. This will instead be done immediately afterwards as the first action of the connection process.
  `lazy`
    Similar to `asynchronous` mode, but an actual connection will be established and the on-connect prepares and queries executed only when a connection is needed for the first time, eg. to execute a query.
`{connect_timeout, Timeout}`
  The maximum time to spend for start_link/1.
`{allowed_local_paths, [binary()]}`
  This option allows you to specify a list of directories or individual files on the client machine which the server may request, for example when executing a `LOAD DATA LOCAL INFILE` query. Only absolute paths without relative components such as `..` and `.` are allowed. The default is an empty list, meaning the client will not send any local files to the server.
`{log_warnings, boolean()}`
  Whether to fetch warnings and log them using error_logger; default true.
`{log_slow_queries, boolean()}`
  Whether to log slow queries using error_logger; default false. Queries are flagged as slow by the server if their execution time exceeds the value in the `long_query_time` variable.
`{keep_alive, boolean() | timeout()}`
  Send ping when unused for a certain time. Possible values are `true`, `false` and `integer() > 0` for an explicit interval in milliseconds. The default is `false`. For `true` a default ping timeout is used.
`{prepare, NamedStatements}`
  Named prepared statements to be created as soon as the connection is ready.
`{queries, Queries}`
  Queries to be executed as soon as the connection is ready. Any results are discarded. Typically, this is used for setting time zone and other session variables.
`{query_timeout, Timeout}`
  The default time to wait for a response when executing a query or a prepared statement. This can be given per query using `query/3,4` and `execute/4`. The default is `infinity`.
`{found_rows, boolean()}`
  If set to true, the connection will be established with CLIENT_FOUND_ROWS capability. affected_rows/1 will now return the number of found rows, not the number of rows changed by the query.
`{query_cache_time, Timeout}`
  The minimum number of milliseconds to cache prepared statements used for parametrized queries with query/3.
`{tcp_options, Options}`
  Additional options for `gen_tcp:connect/3`. You may want to set `{recbuf, Size}` and `{sndbuf, Size}` if you send or receive more than the default (typically 8K) per query.
`{ssl, Options}`
  Additional options for `ssl:connect/3`.
  The `verify` option, if not given explicitly, defaults to `verify_peer`.
  The `server_name_indication` option, if omitted, defaults to the value of the `host` option if it is a hostname string, otherwise no default value is set.
`{float_as_decimal, boolean() | non_neg_integer()}`
  Encode floats as decimals when sending parameters for parametrized queries and prepared statements to the server. This prevents float rounding and truncation errors from happening on the server side. If a number is specified, the float is rounded to this number of decimals. This is off (false) by default.

### stop/1

`stop(Conn) -> ok`

- `Conn = connection()`

See also: stop/2.

### stop/2

`stop(Conn, Timeout) -> ok`

- `Conn = connection()`
- `Timeout = timeout()`

Stops a connection process and closes the connection. The process calling `stop` will be blocked until the connection process stops or the given timeout expires.

If the connection is not stopped within the given timeout, an exit exception is raised with reason `timeout`.

If the connection process exits with any other reason than `normal`, an exit exception is raised with that reason.

### transaction/2

`transaction(Conn, TransactionFun) -> TransactionResult`

- `Conn = connection()`
- `TransactionFun = fun(() -> Result)`
- `TransactionResult = transaction_result(Result)`

This function executes the functional object Fun as a transaction.

See also: transaction/4.

### transaction/3

`transaction(Conn, TransactionFun, Retries) -> TransactionResult`

- `Conn = connection()`
- `TransactionFun = fun(() -> Result)`
- `Retries = non_neg_integer() | infinity`
- `TransactionResult = transaction_result(Result)`

This function executes the functional object Fun as a transaction.

See also: transaction/4.

### transaction/4

`transaction(Conn, TransactionFun, Args, Retries) -> TransactionResult`

- `Conn = connection()`
- `TransactionFun = fun((...) -> Result)`
- `Args = list()`
- `Retries = non_neg_integer() | infinity`
- `TransactionResult = transaction_result(Result)`

This function executes the functional object Fun with arguments Args as a transaction.

The semantics are as close as possible to mnesia's transactions. Transactions can be nested and are restarted automatically when deadlocks are detected. MySQL's savepoints are used to implement nested transactions.

Fun must be a function and Args must be a list of the same length as the arity of Fun.

If an exception occurs within Fun, the exception is caught and `{aborted, Reason}` is returned. The value of `Reason` depends on the class of the exception.

Note that an error response from a query does not cause a transaction to be rollbacked. To force a rollback on a MySQL error you can trigger a `badmatch` using e.g. `ok = mysql:query(Pid, "SELECT some_non_existent_value")`. An exception to this is the error 1213 "Deadlock", after the specified number of retries, all failed. In this case, the transaction is aborted and the error is retured as the reason for the aborted transaction, along with a stacktrace pointing to where the last deadlock was detected. (In earlier versions, up to and including 1.3.2, transactions where automatically restarted also for the error 1205 "Lock wait timeout". This is no longer the case.)

Some queries such as ALTER TABLE cause an *implicit commit* on the server. If such a query is executed within a transaction, an error on the form `{implicit_commit, Query}` is raised. This means that the transaction has been committed prematurely. This also happens if an explicit COMMIT is executed as a plain query within a managed transaction. (Don't do that!)

Class of exception | Return value
--- | ---
`error` with reason `ErrorReason` | `{aborted, {ErrorReason, Stack}}`
`exit(Term)` | `{aborted, Term}`
`throw(Term)` | `{aborted, {throw, Term}}`

### unprepare/2

`unprepare(Conn, StatementRef) -> ok | {error, Reason}`

- `Conn = connection()`
- `StatementRef = statement_ref()`
- `Reason = server_reason() | not_prepared`

Deallocates a prepared statement.

### warning_count/1

`warning_count(Conn::connection()) -> integer()`

Returns the number of warnings generated by the last query/2 or execute/3 calls.

Generated by EDoc