cover/mysql.COVER.html

1 %% MySQL/OTP – MySQL client library for Erlang/OTP
2 %% Copyright (C) 2014-2015, 2018, 2021 Viktor Söderqvist,
3 %% 2016 Johan Lövdahl
4 %% 2017 Piotr Nosek, Michal Slaski
5 %%
6 %% This file is part of MySQL/OTP.
7 %%
8 %% MySQL/OTP is free software: you can redistribute it and/or modify it under
9 %% the terms of the GNU Lesser General Public License as published by the Free
10 %% Software Foundation, either version 3 of the License, or (at your option)
11 %% any later version.
12 %%
13 %% This program is distributed in the hope that it will be useful, but WITHOUT
14 %% ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or
15 %% FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for
16 %% more details.
17 %%
18 %% You should have received a copy of the GNU Lesser General Public License
19 %% along with this program. If not, see <https://www.gnu.org/licenses/>.
20
21 %% @doc MySQL client.
22 %%
23 %% The `connection()' type is a gen_server reference as described in the
24 %% documentation for `gen_server:call/2,3', e.g. the pid or the name if the
25 %% gen_server is locally registered.
26 -module(mysql).
27
28 -export([start_link/1, stop/1, stop/2,
29 is_connected/1,
30 query/2, query/3, query/4, query/5,
31 execute/3, execute/4, execute/5,
32 prepare/2, prepare/3, unprepare/2,
33 warning_count/1, affected_rows/1, autocommit/1, insert_id/1,
34 encode/2, in_transaction/1,
35 transaction/2, transaction/3, transaction/4,
36 change_user/3, change_user/4, reset_connection/1]).
37
38 -export_type([option/0, connection/0, query/0, statement_name/0,
39 statement_ref/0, query_param/0, query_filtermap_fun/0,
40 query_result/0, transaction_result/1, server_reason/0,
41 decode_decimal/0]).
42
43 %% A connection is a ServerRef as in gen_server:call/2,3.
44 -type connection() :: Name :: atom() |
45 {Name :: atom(), Node :: atom()} |
46 {global, GlobalName :: term()} |
47 {via, Module :: atom(), ViaName :: term()} |
48 pid().
49
50 %% MySQL error with the codes and message returned from the server.
51 -type server_reason() :: {Code :: integer(), SQLState :: binary() | undefined,
52 Message :: binary()}.
53
54 -type column_name() :: binary().
55 -type query() :: iodata().
56 -type row() :: [term()].
57
58 -type query_param() :: term().
59
60 -type query_filtermap_fun() :: fun((row()) -> query_filtermap_res())
61 | fun(([column_name()], row()) -> query_filtermap_res()).
62 -type query_filtermap_res() :: boolean()
63 | {true, term()}.
64
65 -type statement_id() :: integer().
66 -type statement_name() :: atom() | binary().
67 -type statement_ref() :: statement_id() | statement_name().
68
69 -type decode_decimal() :: auto | binary | float | number.
70
71 -type query_result() :: ok
72 | {ok, [column_name()], [row()]}
73 | {ok, [{[column_name()], [row()]}, ...]}
74 | {error, server_reason() | busy}.
75
76 -type transaction_result(Result) :: {atomic, Result} | {aborted, Reason :: term()}.
77
78 -type server_name() :: {local, Name :: atom()}
79 | {global, GlobalName :: term()}
80 | {via, Via :: module(), ViaName :: term()}.
81
82 -type option() :: {name, ServerName :: server_name()}
83 | {host, inet:socket_address() | inet:hostname()} | {port, integer()}
84 | {user, iodata()} | {password, iodata()}
85 | {database, iodata()}
86 | {connect_mode, synchronous | asynchronous | lazy}
87 | {connect_timeout, timeout()}
88 | {allowed_local_paths, [binary()]}
89 | {log_warnings, boolean()}
90 | {log_slow_queries, boolean()}
91 | {keep_alive, boolean() | timeout()}
92 | {prepare, [{StatementName :: statement_name(), Statement :: query()}]}
93 | {queries, [query()]}
94 | {query_timeout, timeout()}
95 | {found_rows, boolean()}
96 | {query_cache_time, non_neg_integer()}
97 | {tcp_options, [gen_tcp:connect_option()]}
98 | {ssl, term()}
99 | {float_as_decimal, boolean() | non_neg_integer()}
100 | {decode_decimal, decode_decimal()}.
101
102 %% @doc Starts a connection gen_server process and connects to a database. To
103 %% disconnect use `mysql:stop/1,2'.
104 %%
105 %% Options:
106 %%
107 %% <dl>
108 %% <dt>`{name, ServerName}'</dt>
109 %% <dd>If a name is provided, the gen_server will be registered with this
110 %% name. For details see the documentation for the first argument of
111 %% gen_server:start_link/4.</dd>
112 %% <dt>`{host, Host}'</dt>
113 %% <dd>Hostname of the MySQL database. Since OTP version 19, it is also
114 %% possible to specify a local (Unix) Socket by specifying
115 %% `{local, SocketFile}'. Default `"localhost"'.</dd>
116 %% <dt>`{port, Port}'</dt>
117 %% <dd>Port; default 3306 for non-local or 0 for local (Unix) sockets.</dd>
118 %% <dt>`{user, User}'</dt>
119 %% <dd>Username.</dd>
120 %% <dt>`{password, Password}'</dt>
121 %% <dd>Password.</dd>
122 %% <dt>`{database, Database}'</dt>
123 %% <dd>The name of the database AKA schema to use. This can be changed later
124 %% using the query `USE <database>'.</dd>
125 %% <dt>`{connect_mode, synchronous | asynchronous | lazy}'</dt>
126 %% <dd>Specifies how and when the connection process should establish a connection
127 %% to the MySQL server.
128 %% <dl>
129 %% <dt>`synchronous' (default)</dt>
130 %% <dd>The connection will be established as part of the connection process'
131 %% start routine, ie the returned connection process will already be
132 %% connected and ready to use, and any on-connect prepares and queries
133 %% will have been executed.</dd>
134 %% <dt>`asynchronous'</dt>
135 %% <dd>The connection process will be started and returned to the caller
136 %% before really establishing a connection to the server and executing
137 %% the on-connect prepares and executes. This will instead be done
138 %% immediately afterwards as the first action of the connection
139 %% process.</dd>
140 %% <dt>`lazy'</dt>
141 %% <dd>Similar to `asynchronous' mode, but an actual connection will be
142 %% established and the on-connect prepares and queries executed only
143 %% when a connection is needed for the first time, eg. to execute a
144 %% query.</dd>
145 %% </dl>
146 %% </dd>
147 %% <dt>`{connect_timeout, Timeout}'</dt>
148 %% <dd>The maximum time to spend for start_link/1.</dd>
149 %% <dt>`{allowed_local_paths, [binary()]}'</dt>
150 %% <dd>This option allows you to specify a list of directories or individual
151 %% files on the client machine which the server may request, for example
152 %% when executing a `LOAD DATA LOCAL INFILE' query. Only absolute paths
153 %% without relative components such as `..' and `.' are allowed.
154 %% The default is an empty list, meaning the client will not send any
155 %% local files to the server.</dd>
156 %% <dt>`{log_warnings, boolean()}'</dt>
157 %% <dd>Whether to fetch warnings and log them using logger; default
158 %% true.</dd>
159 %% <dt>`{log_slow_queries, boolean()}'</dt>
160 %% <dd>Whether to log slow queries using logger; default false. Queries
161 %% are flagged as slow by the server if their execution time exceeds the
162 %% value in the `long_query_time' variable.</dd>
163 %% <dt>`{keep_alive, boolean() | timeout()}'</dt>
164 %% <dd>Send ping when unused for a certain time. Possible values are `true',
165 %% `false' and `integer() > 0' for an explicit interval in milliseconds.
166 %% The default is `false'. For `true' a default ping timeout is used.
167 %% </dd>
168 %% <dt>`{prepare, NamedStatements}'</dt>
169 %% <dd>Named prepared statements to be created as soon as the connection is
170 %% ready.</dd>
171 %% <dt>`{queries, Queries}'</dt>
172 %% <dd>Queries to be executed as soon as the connection is ready. Any results
173 %% are discarded. Typically, this is used for setting time zone and other
174 %% session variables.</dd>
175 %% <dt>`{query_timeout, Timeout}'</dt>
176 %% <dd>The default time to wait for a response when executing a query or a
177 %% prepared statement. This can be given per query using `query/3,4' and
178 %% `execute/4'. The default is `infinity'.</dd>
179 %% <dt>`{found_rows, boolean()}'</dt>
180 %% <dd>If set to true, the connection will be established with
181 %% CLIENT_FOUND_ROWS capability. affected_rows/1 will now return the
182 %% number of found rows, not the number of rows changed by the
183 %% query.</dd>
184 %% <dt>`{query_cache_time, Timeout}'</dt>
185 %% <dd>The minimum number of milliseconds to cache prepared statements used
186 %% for parametrized queries with query/3.</dd>
187 %% <dt>`{tcp_options, Options}'</dt>
188 %% <dd>Additional options for `gen_tcp:connect/3'. You may want to set
189 %% `{recbuf, Size}' and `{sndbuf, Size}' if you send or receive more than
190 %% the default (typically 8K) per query.</dd>
191 %% <dt>`{ssl, Options}'</dt>
192 %% <dd>Additional options for `ssl:connect/3'.<br />
193 %% The `verify' option, if not given explicitly, defaults to
194 %% `verify_peer'.<br />
195 %% The `server_name_indication' option, if omitted, defaults to the value
196 %% of the `host' option if it is a hostname string, otherwise no default
197 %% value is set.</dd>
198 %% <dt>`{float_as_decimal, boolean() | non_neg_integer()}'</dt>
199 %% <dd>Encode floats as decimals when sending parameters for parametrized
200 %% queries and prepared statements to the server. This prevents float
201 %% rounding and truncation errors from happening on the server side. If a
202 %% number is specified, the float is rounded to this number of
203 %% decimals. This is off (false) by default.</dd>
204 %% <dt>`{decode_decimal, auto | float | number | binary}'</dt>
205 %% <dd>When decoding `decimal' columns from the server, force the return the
206 %% value as either a `binary()', `float()`, or `number()' (specified by
207 %% the atoms `binary', `float', `number' respectively). Defaults to
208 %% `auto', which will return a number (`integer()' or `float()') unless
209 %% the conversion to `float()' would result in a loss of precision, in
210 %% which case, `binary()' is returned.</dd>
211 %% </dl>
212 -spec start_link(Options :: [option()]) -> {ok, pid()} | ignore | {error, term()}.
213 start_link(Options) ->
214 48 case proplists:get_value(name, Options) of
215 undefined ->
216 44 gen_server:start_link(mysql_conn, Options, []);
217 ServerName ->
218 4 gen_server:start_link(ServerName, mysql_conn, Options, [])
219 end.
220
221 %% @see stop/2.
222 -spec stop(Conn) -> ok
223 when Conn :: connection().
224 stop(Conn) ->
225 36 stop(Conn, infinity).
226
227 %% @doc Stops a connection process and closes the connection. The
228 %% process calling `stop' will be blocked until the connection
229 %% process stops or the given timeout expires.
230 %%
231 %% If the connection is not stopped within the given timeout,
232 %% an exit exception is raised with reason `timeout'.
233 %%
234 %% If the connection process exits with any other reason than `normal',
235 %% an exit exception is raised with that reason.
236 -spec stop(Conn, Timeout) -> ok
237 when Conn :: connection(),
238 Timeout :: timeout().
239 stop(Conn, Timeout) ->
240 37 gen_server:stop(Conn, normal, Timeout).
241
242 %% @private
243 -spec is_connected(Conn) -> boolean()
244 when Conn :: connection().
245 is_connected(Conn) ->
246 4 gen_server:call(Conn, is_connected).
247
248 %% @doc Executes a plain query.
249 %% @see query/5.
250 -spec query(Conn, Query) -> Result
251 when Conn :: connection(),
252 Query :: iodata(),
253 Result :: query_result().
254 query(Conn, Query) ->
255 476 query_helper(Conn, Query, no_params, no_filtermap_fun, default_timeout).
256
257 %% @doc Executes a query.
258 %% @see query/5.
259 -spec query(Conn, Query, Params | FilterMap | Timeout) -> Result
260 when Conn :: connection(),
261 Query :: query(),
262 Timeout :: timeout(),
263 Params :: [query_param()],
264 FilterMap :: query_filtermap_fun(),
265 Result :: query_result().
266 query(Conn, Query, Params) when Params == no_params;
267 is_list(Params) ->
268 15 query_helper(Conn, Query, Params, no_filtermap_fun, default_timeout);
269 query(Conn, Query, FilterMap) when FilterMap == no_filtermap_fun;
270 is_function(FilterMap, 1);
271 is_function(FilterMap, 2) ->
272 2 query_helper(Conn, Query, no_params, FilterMap, default_timeout);
273 query(Conn, Query, Timeout) when Timeout == default_timeout;
274 is_integer(Timeout);
275 Timeout == infinity ->
276 2 query_helper(Conn, Query, no_params, no_filtermap_fun, Timeout).
277
278 %% @doc Executes a query.
279 %% @see query/5.
280 -spec query(Conn, Query, Params, Timeout) -> Result
281 when Conn :: connection(),
282 Query :: query(),
283 Timeout :: timeout(),
284 Params :: [query_param()],
285 Result :: query_result();
286 (Conn, Query, FilterMap, Timeout) -> Result
287 when Conn :: connection(),
288 Query :: query(),
289 Timeout :: timeout(),
290 FilterMap :: query_filtermap_fun(),
291 Result :: query_result();
292 (Conn, Query, Params, FilterMap) -> Result
293 when Conn :: connection(),
294 Query :: query(),
295 Params :: [query_param()],
296 FilterMap :: query_filtermap_fun(),
297 Result :: query_result().
298 query(Conn, Query, Params, Timeout) when (Params == no_params orelse
299 is_list(Params)) andalso
300 (Timeout == default_timeout orelse
301 is_integer(Timeout) orelse
302 Timeout == infinity) ->
303 1 query_helper(Conn, Query, Params, no_filtermap_fun, Timeout);
304 query(Conn, Query, FilterMap, Timeout) when (FilterMap == no_filtermap_fun orelse
305 is_function(FilterMap, 1) orelse
306 is_function(FilterMap, 2)) andalso
307 (Timeout == default_timeout orelse
308 is_integer(Timeout) orelse
309 Timeout=:=infinity) ->
310 
:-(
 query_helper(Conn, Query, no_params, FilterMap, Timeout);
311 query(Conn, Query, Params, FilterMap) when (Params == no_params orelse
312 is_list(Params)) andalso
313 (FilterMap == no_filtermap_fun orelse
314 is_function(FilterMap, 1) orelse
315 is_function(FilterMap, 2)) ->
316 2 query_helper(Conn, Query, Params, FilterMap, default_timeout).
317
318 %% @doc Executes a query.
319 %%
320 %% === Parameters ===
321 %%
322 %% `Conn' is identifying a connection process started using
323 %% `mysql:start_link/1'.
324 %%
325 %% `Query' is the query to execute, as a binary or a list.
326 %%
327 %% `Params', `FilterMap' and `Timeout' are optional.
328 %%
329 %% If `Params' (a list) is specified, the query is performed as a prepared
330 %% statement. A prepared statement is created, executed and then cached for a
331 %% certain time (specified using the option `{query_cache_time, Milliseconds}'
332 %% to `start_link/1'). If the same query is executed again during this time,
333 %% it does not need to be prepared again. If `Params' is omitted, the query
334 %% is executed as a plain query. To force a query without parameters to be
335 %% executed as a prepared statement, an empty list can be used for `Params'.
336 %%
337 %% If `FilterMap' (a fun) is specified, the function is applied to each row to
338 %% filter or perform other actions on the rows, in a way similar to how
339 %% `lists:filtermap/2' works, before the result is returned to the caller. See
340 %% below for details.
341 %%
342 %% `Timeout' specifies the time to wait for a response from the database. If
343 %% omitted, the timeout given in `start_link/1' is used.
344 %%
345 %% === Return value ===
346 %%
347 %% Results are returned in the form `{ok, ColumnNames, Rows}' if there is one
348 %% result set. If there are more than one result sets, they are returned in the
349 %% form `{ok, [{ColumnNames, Rows}, ...]}'. This is typically the case if
350 %% multiple queries are specified at the same time, separated by semicolons.
351 %%
352 %% For queries that don't return any rows (INSERT, UPDATE, etc.) only the atom
353 %% `ok' is returned.
354 %%
355 %% If this function is called on a connection which is already in transaction
356 %% owned by another process, `{error, busy}` will be returned.
357 %%
358 %% === FilterMap details ===
359 %%
360 %% If the `FilterMap' argument is used, it must be a function of arity 1 or 2
361 %% that returns either `true', `false', or `{true, Value}'.
362 %%
363 %% Each result row is handed to the given function as soon as it is received
364 %% from the server, and only when the function has returned, the next row is
365 %% fetched. This provides the ability to prevent memory exhaustion. On the
366 %% other hand, it can cause the server to time out on sending if your function
367 %% is doing something slow (see the MySQL documentation on `NET_WRITE_TIMEOUT').
368 %%
369 %% If the function is of arity 1, only the row is passed to it as the single
370 %% argument, while if the function is of arity 2, the column names are passed
371 %% in as the first argument and the row as the second.
372 %%
373 %% The value returned is then used to decide if the row is to be included in
374 %% the result(s) returned from the `query' call (filtering), or if something
375 %% else is to be included in the result instead (mapping). You may also use
376 %% this function for side effects, like writing rows to disk or sending them
377 %% to another process etc.
378 %%
379 %% === Examples ===
380 %%
381 %% Here is an example showing some of the things that are possible:
382 %% ```
383 %% Query = "SELECT a, b, c FROM foo",
384 %% FilterMap = fun
385 %% %% Include all rows where the first column is < 10.
386 %% ([A|_]) when A < 10 ->
387 %% true;
388 %% %% Exclude all rows where the first column is >= 10 and < 20.
389 %% ([A|_]) when A < 20 ->
390 %% false;
391 %% %% For rows where the first column is >= 20 and < 30, include
392 %% %% the atom 'foo' in place of the row instead.
393 %% ([A|_]) when A < 30 ->
394 %% {true, foo}};
395 %% %% For rows where the first row is >= 30 and < 40, send the
396 %% %% row to a gen_server via call (ie, wait for a response),
397 %% %% and do not include the row in the result.
398 %% (R=[A|_]) when A < 40 ->
399 %% gen_server:call(Pid, R),
400 %% false;
401 %% %% For rows where the first column is >= 40 and < 50, send the
402 %% %% row to a gen_server via cast (ie, do not wait for a reply),
403 %% %% and include the row in the result, also.
404 %% (R=[A|_]) when A < 50 ->
405 %% gen_server:cast(Pid, R),
406 %% true;
407 %% %% Exclude all other rows from the result.
408 %% (_) ->
409 %% false
410 %% end,
411 %% query(Conn, Query, FilterMap).
412 %% '''
413 -spec query(Conn, Query, Params, FilterMap, Timeout) -> Result
414 when Conn :: connection(),
415 Query :: query(),
416 Timeout :: timeout(),
417 Params :: [query_param()],
418 FilterMap :: query_filtermap_fun(),
419 Result :: query_result().
420 query(Conn, Query, Params, FilterMap, Timeout) ->
421 
:-(
 query_helper(Conn, Query, Params, FilterMap, Timeout).
422
423 -spec query_helper(Conn, Query, Params, FilterMap, Timeout) -> Result
424 when Conn :: connection(),
425 Query :: query(),
426 Timeout :: default_timeout | timeout(),
427 Params :: no_params | [query_param()],
428 FilterMap :: no_filtermap_fun | query_filtermap_fun(),
429 Result :: query_result().
430 query_helper(Conn, Query, no_params, FilterMap, Timeout) ->
431 480 query_call(Conn, {query, Query, FilterMap, Timeout});
432 query_helper(Conn, Query, Params, FilterMap, Timeout) ->
433 18 case mysql_protocol:valid_params(Params) of
434 true ->
435 17 query_call(Conn,
436 {param_query, Query, Params, FilterMap, Timeout});
437 false ->
438 1 error(badarg)
439 end.
440
441 %% @doc Executes a prepared statement with the default query timeout as given
442 %% to start_link/1.
443 %% @see prepare/2
444 %% @see prepare/3
445 %% @see prepare/4
446 %% @see execute/5
447 -spec execute(Conn, StatementRef, Params) -> Result | {error, not_prepared}
448 when Conn :: connection(),
449 StatementRef :: statement_ref(),
450 Params :: [query_param()],
451 Result :: query_result().
452 execute(Conn, StatementRef, Params) ->
453 206 execute_helper(Conn, StatementRef, Params, no_filtermap_fun, default_timeout).
454
455 %% @doc Executes a prepared statement.
456 %% @see prepare/2
457 %% @see prepare/3
458 %% @see prepare/4
459 %% @see execute/5
460 -spec execute(Conn, StatementRef, Params, FilterMap | Timeout) ->
461 Result | {error, not_prepared}
462 when Conn :: connection(),
463 StatementRef :: statement_ref(),
464 Params :: [query_param()],
465 FilterMap :: query_filtermap_fun(),
466 Timeout :: timeout(),
467 Result :: query_result().
468 execute(Conn, StatementRef, Params, Timeout) when Timeout == default_timeout;
469 is_integer(Timeout);
470 Timeout=:=infinity ->
471 1 execute_helper(Conn, StatementRef, Params, no_filtermap_fun, Timeout);
472 execute(Conn, StatementRef, Params, FilterMap) when FilterMap == no_filtermap_fun;
473 is_function(FilterMap, 1);
474 is_function(FilterMap, 2) ->
475 2 execute_helper(Conn, StatementRef, Params, FilterMap, default_timeout).
476
477 %% @doc Executes a prepared statement.
478 %%
479 %% The `FilterMap' and `Timeout' arguments are optional.
480 %% <ul>
481 %% <li>If the `FilterMap' argument is the atom `no_filtermap_fun' or is
482 %% omitted, no row filtering/mapping will be applied and all result rows
483 %% will be returned unchanged.</li>
484 %% <li>If the `Timeout' argument is the atom `default_timeout' or is omitted,
485 %% the timeout given in `start_link/1' is used.</li>
486 %% </ul>
487 %%
488 %% See `query/5' for an explanation of the `FilterMap' argument.
489 %%
490 %% Note that if this function is called on a connection which is already in transaction
491 %% owned by another process, `{error, busy}' will be returned.
492 %%
493 %% @see prepare/2
494 %% @see prepare/3
495 %% @see prepare/4
496 %% @see query/5
497 -spec execute(Conn, StatementRef, Params, FilterMap, Timeout) ->
498 Result | {error, not_prepared}
499 when Conn :: connection(),
500 StatementRef :: statement_ref(),
501 Params :: [query_param()],
502 FilterMap :: query_filtermap_fun(),
503 Timeout :: timeout(),
504 Result :: query_result().
505 execute(Conn, StatementRef, Params, FilterMap, Timeout) ->
506 
:-(
 execute_helper(Conn, StatementRef, Params, FilterMap, Timeout).
507
508 -spec execute_helper(Conn, StatementRef, Params, FilterMap, Timeout) ->
509 Result | {error, not_prepared}
510 when Conn :: connection(),
511 StatementRef :: statement_ref(),
512 Params :: [query_param()],
513 FilterMap :: no_filtermap_fun | query_filtermap_fun(),
514 Timeout :: default_timeout | timeout(),
515 Result :: query_result().
516 execute_helper(Conn, StatementRef, Params, FilterMap, Timeout) ->
517 209 case mysql_protocol:valid_params(Params) of
518 true ->
519 208 query_call(Conn,
520 {execute, StatementRef, Params, FilterMap, Timeout});
521 false ->
522 1 error(badarg)
523 end.
524
525 %% @doc Creates a prepared statement from the passed query.
526 %% @see prepare/3
527 -spec prepare(Conn, Query) -> {ok, StatementId} | {error, Reason}
528 when Conn :: connection(),
529 Query :: query(),
530 StatementId :: statement_id(),
531 Reason :: server_reason().
532 prepare(Conn, Query) ->
533 95 gen_server:call(Conn, {prepare, Query}).
534
535 %% @doc Creates a prepared statement from the passed query and associates it
536 %% with the given name.
537 %% @see prepare/2
538 -spec prepare(Conn, Name, Query) -> {ok, Name} | {error, Reason}
539 when Conn :: connection(),
540 Name :: statement_name(),
541 Query :: query(),
542 Reason :: server_reason().
543 prepare(Conn, Name, Query) ->
544 9 gen_server:call(Conn, {prepare, Name, Query}).
545
546 %% @doc Deallocates a prepared statement.
547 -spec unprepare(Conn, StatementRef) -> ok | {error, Reason}
548 when Conn :: connection(),
549 StatementRef :: statement_ref(),
550 Reason :: server_reason() | not_prepared.
551 unprepare(Conn, StatementRef) ->
552 93 gen_server:call(Conn, {unprepare, StatementRef}).
553
554 %% @doc Returns the number of warnings generated by the last query/2 or
555 %% execute/3 calls.
556 -spec warning_count(connection()) -> integer().
557 warning_count(Conn) ->
558 2 gen_server:call(Conn, warning_count).
559
560 %% @doc Returns the number of inserted, updated and deleted rows of the last
561 %% executed query or prepared statement. If found_rows is set on the
562 %% connection, for update operation the return value will equal to the number
563 %% of rows matched by the query.
564 -spec affected_rows(connection()) -> integer().
565 affected_rows(Conn) ->
566 3 gen_server:call(Conn, affected_rows).
567
568 %% @doc Returns true if auto-commit is enabled and false otherwise.
569 -spec autocommit(connection()) -> boolean().
570 autocommit(Conn) ->
571 3 gen_server:call(Conn, autocommit).
572
573 %% @doc Returns the last insert-id.
574 -spec insert_id(connection()) -> integer().
575 insert_id(Conn) ->
576 3 gen_server:call(Conn, insert_id).
577
578 %% @doc Returns true if the connection is in a transaction and false otherwise.
579 %% This works regardless of whether the transaction has been started using
580 %% transaction/2,3 or using a plain `mysql:query(Connection, "BEGIN")'.
581 %% @see transaction/2
582 %% @see transaction/4
583 -spec in_transaction(connection()) -> boolean().
584 in_transaction(Conn) ->
585 25 gen_server:call(Conn, in_transaction).
586
587 %% @doc This function executes the functional object Fun as a transaction.
588 %% @see transaction/4
589 -spec transaction(Conn, TransactionFun) -> TransactionResult
590 when Conn :: connection(),
591 TransactionFun :: fun(() -> Result),
592 TransactionResult :: transaction_result(Result).
593 transaction(Conn, Fun) ->
594 24 transaction(Conn, Fun, [], infinity).
595
596 %% @doc This function executes the functional object Fun as a transaction.
597 %% @see transaction/4
598 -spec transaction(Conn, TransactionFun, Retries) -> TransactionResult
599 when Conn :: connection(),
600 TransactionFun :: fun(() -> Result),
601 Retries :: non_neg_integer() | infinity,
602 TransactionResult :: transaction_result(Result).
603 transaction(Conn, Fun, Retries) ->
604 4 transaction(Conn, Fun, [], Retries).
605
606 %% @doc This function executes the functional object Fun with arguments Args as
607 %% a transaction.
608 %%
609 %% The semantics are as close as possible to mnesia's transactions. Transactions
610 %% can be nested and are restarted automatically when deadlocks are detected.
611 %% MySQL's savepoints are used to implement nested transactions.
612 %%
613 %% If this function is called on a connection which is already in a transaction
614 %% owned by another process, `{aborted, busy}` is returned. The idea of nested
615 %% transactions is that this function can be called in the Fun, but all within
616 %% the same process.
617 %%
618 %% Fun must be a function and Args must be a list of the same length as the
619 %% arity of Fun.
620 %%
621 %% If an exception occurs within Fun, the exception is caught and `{aborted,
622 %% Reason}' is returned. The value of `Reason' depends on the class of the
623 %% exception.
624 %%
625 %% Note that an error response from a query does not cause a transaction to be
626 %% rollbacked. To force a rollback on a MySQL error you can trigger a `badmatch'
627 %% using e.g. `ok = mysql:query(Pid, "SELECT some_non_existent_value")'. An
628 %% exception to this is the error 1213 "Deadlock", after the specified number
629 %% of retries, all failed. In this case, the transaction is aborted and the
630 %% error is returned as the reason for the aborted transaction, along with a
631 %% stacktrace pointing to where the last deadlock was detected. (In earlier
632 %% versions, up to and including 1.3.2, transactions where automatically
633 %% restarted also for the error 1205 "Lock wait timeout". This is no longer the
634 %% case.)
635 %%
636 %% Some queries such as ALTER TABLE cause an *implicit commit* on the server.
637 %% If such a query is executed within a transaction, an error on the form
638 %% `{implicit_commit, Query}' is raised. This means that the transaction has
639 %% been committed prematurely. This also happens if an explicit COMMIT is
640 %% executed as a plain query within a managed transaction. (Don't do that!)
641 %%
642 %% <table>
643 %% <thead>
644 %% <tr><th>Class of exception</th><th>Return value</th></tr>
645 %% </thead>
646 %% <tbody>
647 %% <tr>
648 %% <td>`error' with reason `ErrorReason'</td>
649 %% <td>`{aborted, {ErrorReason, Stack}}'</td>
650 %% </tr>
651 %% <tr><td>`exit(Term)'</td><td>`{aborted, Term}'</td></tr>
652 %% <tr><td>`throw(Term)'</td><td>`{aborted, {throw, Term}}'</td></tr>
653 %% </tbody>
654 %% </table>
655 -spec transaction(Conn, TransactionFun, Args, Retries) -> TransactionResult
656 when Conn :: connection(),
657 TransactionFun :: fun((...) -> Result),
658 Args :: list(),
659 Retries :: non_neg_integer() | infinity,
660 TransactionResult :: transaction_result(Result).
661 transaction(Conn, Fun, Args, Retries) when is_list(Args),
662 is_function(Fun, length(Args)) ->
663 %% The guard makes sure that we can apply Fun to Args. Any error we catch
664 %% in the try-catch are actual errors that occurred in Fun.
665 28 case gen_server:call(Conn, start_transaction, infinity) of
666 ok ->
667 28 execute_transaction(Conn, Fun, Args, Retries);
668 {error, busy} ->
669 
:-(
 {aborted, busy}
670 end.
671
672 %% @private
673 %% @doc This is a helper for transaction/2,3,4. It performs everything except
674 %% executing the BEGIN statement. It is called recursively when a transaction
675 %% is retried.
676 %%
677 %% "When a transaction rollback occurs due to a deadlock or lock wait timeout,
678 %% it cancels the effect of the statements within the transaction. But if the
679 %% start-transaction statement was START TRANSACTION or BEGIN statement,
680 %% rollback does not cancel that statement."
681 %% (https://dev.mysql.com/doc/refman/5.6/en/innodb-error-handling.html)
682 %%
683 %% This seems to have changed in MySQL 5.7.x though (although the MySQL
684 %% documentation hasn't been updated). Now, also the BEGIN is cancelled, so a
685 %% new BEGIN has to be issued when restarting the transaction. This has no
686 %% effect on older versions, not even a warning.
687 %%
688 %% Lock Wait Timeout:
689 %% "InnoDB rolls back only the last statement on a transaction timeout by
690 %% default. If --innodb_rollback_on_timeout is specified, a transaction timeout
691 %% causes InnoDB to abort and roll back the entire transaction (the same
692 %% behavior as in MySQL 4.1)."
693 %% (https://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html)
694 execute_transaction(Conn, Fun, Args, Retries) ->
695 30 try apply(Fun, Args) of
696 ResultOfFun ->
697 16 ok = gen_server:call(Conn, commit, infinity),
698 16 {atomic, ResultOfFun}
699 catch
700 %% We are at the top level, try to restart the transaction if there are
701 %% retries left
702 throw:{implicit_rollback, 1, _}
703 when Retries == infinity ->
704 %% In MySQL < 5.7 we're not in a transaction here, but in earlier
705 %% versions we are, so we can't use `gen_server:call(Conn,
706 %% start_transaction, infinity)' here.
707 1 ok = query(Conn, <<"BEGIN">>),
708 1 execute_transaction(Conn, Fun, Args, infinity);
709 throw:{implicit_rollback, 1, _}
710 when Retries > 0 ->
711 1 ok = query(Conn, <<"BEGIN">>),
712 1 execute_transaction(Conn, Fun, Args, Retries - 1);
713 throw:{implicit_rollback, 1, Reason}:Stacktrace
714 when Retries == 0 ->
715 %% No more retries. Return 'aborted' along with the deadlock error
716 %% and a the trace to the line where the deadlock occurred.
717 %% In MySQL < 5.7, we are still in a transaction here, but in 5.7+
718 %% we're not. The ROLLBACK executed here has no effect if no
719 %% transaction is ongoing.
720 1 ok = gen_server:call(Conn, rollback, infinity),
721 1 {aborted, {Reason, Stacktrace}};
722 throw:{implicit_rollback, N, Reason}:Stacktrace
723 when N > 1 ->
724 %% Nested transaction. Bubble out to the outermost level.
725 3 erlang:raise(throw, {implicit_rollback, N - 1, Reason},
726 Stacktrace);
727 error:{implicit_commit, _Query} = E:Stacktrace ->
728 %% The called did something like ALTER TABLE which resulted in an
729 %% implicit commit. The server has already committed. We need to
730 %% jump out of N levels of transactions.
731 %%
732 %% Returning 'atomic' or 'aborted' would both be wrong. Raise an
733 %% exception is the best we can do.
734 2 erlang:raise(error, E, Stacktrace);
735 error:change_user_in_transaction = E:Stacktrace ->
736 %% The called tried to change user inside the transaction, which
737 %% is not allowed and a serious mistake. We roll back and raise
738 %% an error.
739 1 ok = gen_server:call(Conn, rollback, infinity),
740 1 erlang:raise(error, E, Stacktrace);
741 error:reset_connection_in_transaction = E:Stacktrace ->
742 %% The called tried to reset connection inside the transaction, which
743 %% is not allowed and a serious mistake. We roll back and raise
744 %% an error.
745 
:-(
 ok = gen_server:call(Conn, rollback, infinity),
746 
:-(
 erlang:raise(error, E, Stacktrace);
747 Class:Reason:Stacktrace ->
748 %% We must be able to rollback. Otherwise let's crash.
749 4 ok = gen_server:call(Conn, rollback, infinity),
750 %% These forms for throw, error and exit mirror Mnesia's behaviour.
751 4 Aborted = case Class of
752 2 throw -> {throw, Reason};
753 1 error -> {Reason, Stacktrace};
754 1 exit -> Reason
755 end,
756 4 {aborted, Aborted}
757 end.
758
759 %% @doc Equivalent to `change_user(Conn, Username, Password, [])'.
760 %% @see change_user/4
761 -spec change_user(Conn, Username, Password) -> Result
762 when Conn :: connection(),
763 Username :: iodata(),
764 Password :: iodata(),
765 Result :: ok.
766 change_user(Conn, Username, Password) ->
767 6 change_user(Conn, Username, Password, []).
768
769 %% @doc Changes the user of the active connection without closing and
770 %% and re-opening it. The currently active session will be reset (ie,
771 %% user variables, temporary tables, prepared statements, etc will
772 %% be lost) independent of whether the operation succeeds or fails.
773 %%
774 %% If change user is called when a transaction is active (ie, neither
775 %% committed nor rolled back), calling `change_user' will fail with
776 %% an error exception and `change_user_in_transaction' as the error
777 %% message.
778 %%
779 %% If the change user operation fails, `{error, Reason}' will be
780 %% returned. Specifically, if the operation itself fails (eg
781 %% authentication failure), `change_user_failed' will be returned as
782 %% the reason, while if the operation itself succeeds but one of
783 %% the given initial queries or prepares fails, the reason will
784 %% reflect the cause for the failure. In any case, the connection
785 %% process will exit with the same reason and cannot be used any longer.
786 %%
787 %% For a description of the `database', `queries' and `prepare'
788 %% options, see `start_link/1'.
789 %%
790 %% @see start_link/1
791 -spec change_user(Conn, Username, Password, Options) -> Result
792 when Conn :: connection(),
793 Username :: iodata(),
794 Password :: iodata(),
795 Options :: [Option],
796 Result :: ok,
797 Option :: {database, iodata()}
798 | {queries, [query()]}
799 | {prepare, [NamedStatement]},
800 NamedStatement :: {StatementName :: statement_name(), Statement :: query()}.
801 change_user(Conn, Username, Password, Options) ->
802 11 case in_transaction(Conn) of
803 2 true -> error(change_user_in_transaction);
804 9 false -> ok
805 end,
806 9 gen_server:call(Conn, {change_user, Username, Password, Options}).
807
808 -spec reset_connection(Conn) -> ok | {error, Reason}
809 when Conn :: connection(),
810 Reason :: server_reason().
811 reset_connection(Conn) ->
812 1 case in_transaction(Conn) of
813 
:-(
 true -> error(reset_connection_in_transaction);
814 1 false -> ok
815 end,
816 1 gen_server:call(Conn, reset_connection).
817
818 %% @doc Encodes a term as a MySQL literal so that it can be used to inside a
819 %% query. If backslash escapes are enabled, backslashes and single quotes in
820 %% strings and binaries are escaped. Otherwise only single quotes are escaped.
821 %%
822 %% Note that the preferred way of sending values is by prepared statements or
823 %% parametrized queries with placeholders.
824 %%
825 %% @see query/3
826 %% @see execute/3
827 -spec encode(connection(), term()) -> iodata().
828 encode(Conn, Term) ->
829 2 Term1 = case (is_list(Term) orelse is_binary(Term)) andalso
830 2 gen_server:call(Conn, backslash_escapes_enabled) of
831 1 true -> mysql_encode:backslash_escape(Term);
832 1 false -> Term
833 end,
834 2 mysql_encode:encode(Term1).
835
836 %% --- Helpers ---
837
838 %% @doc Makes a gen_server call for a query (plain, parametrized or prepared),
839 %% checks the reply and sometimes throws an exception when we need to jump out
840 %% of a transaction.
841 query_call(Conn, CallReq) ->
842 705 case gen_server:call(Conn, CallReq, infinity) of
843 {implicit_commit, _NestingLevel, Query} ->
844 1 error({implicit_commit, Query});
845 {implicit_rollback, _NestingLevel, _ServerReason} = ImplicitRollback ->
846 3 throw(ImplicitRollback);
847 Result ->
848 701 Result
849 end.
Line Hits Source