cover/mysql.COVER.html

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