<?xml version="1.0" encoding='ISO-8859-1'?>
<!DOCTYPE book PUBLIC "-//OASIS//DTD DocBook XML V4.4//EN"
"http://www.oasis-open.org/docbook/xml/4.4/docbookx.dtd" [

<!-- Include general documentation entities -->
<!ENTITY % docentities SYSTEM "../../../../doc/docbook/entities.xml">
%docentities;

]>

<!-- Module User's Guide -->

<chapter>

	<title>&adminguide;</title>

	<section>
	<title>Overview</title>
	<para>
		This is a module which provides MySQL connectivity for Kamailio.
		It implements the DB API defined in Kamailio.
	</para>
	</section>

	<section>
	<title>Dependencies</title>
	<section>
		<title>&kamailio; Modules</title>
		<para>
		The following modules must be loaded before this module:
			<itemizedlist>
			<listitem>
			<para>
				<emphasis>No dependencies on other &kamailio; modules</emphasis>.
			</para>
			</listitem>
			</itemizedlist>
		</para>
	</section>
	<section>
		<title>External Libraries or Applications</title>
		<para>
		The following libraries or applications must be installed before running
		&kamailio; with this module loaded:
			<itemizedlist>
			<listitem>
			<para>
				<emphasis>mysql</emphasis> - the development libraries for the MySQL database. In some Linux distributions named "libmysqlclient-dev".
			</para>
			</listitem>
			</itemizedlist>
		</para>
	</section>
	</section>
	<section>
	<title>Parameters</title>
	<section>
		<title><varname>ping_interval</varname> (integer)</title>
		<para>
		Time interval in seconds to send ping messages to MySQL server in order to keep
		the connection open.
		</para>
		<para>
		<emphasis>
			Default value is 300 (5 min).
		</emphasis>
		</para>
		<example>
		<title>Set <varname>ping_interval</varname> parameter</title>
		<programlisting format="linespecific">
...
modparam("db_mysql", "ping_interval", 600)
...
</programlisting>
		</example>
	</section>
	<section>
		<title><varname>server_timezone</varname> (integer)</title>
		<para>
		Control if kamailio converts a time to string using the local timezone
		or if MySQL will convert it using FROM_UNIXTIME. This option is useful
		if kamailio and the database are configured for different timezones.
		</para>
		<para>
		<emphasis>
			Default value is 0 (use local conversion)
		</emphasis>
		</para>
		<example>
		<title>Set <varname>server_timezone</varname> parameter</title>
		<programlisting format="linespecific">
...
modparam("db_mysql", "server_timezone", 1)
...
</programlisting>
		</example>
	</section>
		<section>
		<title><varname>timeout_interval</varname> (integer)</title>
		<para>
		Time interval (in seconds) after that a connection attempt, read or write request
		is aborted. The value counts three times, as several retries are done
		from the driver before it gives up.
		</para>
		<para>
		The read timeout parameter is ignored on MySQL driver versions prior to
		<quote>5.1.12</quote>, <quote>5.0.25</quote> and <quote>4.1.22</quote>.
		The write timeout parameter is ignored on versions prior to <quote>5.1.12</quote>
		and <quote>5.0.25</quote>, the <quote>4.1</quote> release don't support it at all.
		</para>
		<para>
		<emphasis>
			Default value is 2 (6 sec).
		</emphasis>
		</para>
		<example>
		<title>Set <varname>timeout_interval</varname> parameter</title>
		<programlisting format="linespecific">
...
modparam("db_mysql", "timeout_interval", 2)
...
</programlisting>
		</example>
	</section>
	<section>
		<title><varname>auto_reconnect</varname> (integer)</title>
		<para>
		Configure whether the module should automatically reconnect to MySQL server if the
		connection was lost.
		</para>
		<para>
		<emphasis>
			Default value is 1 (1 - on / 0 - off).
		</emphasis>
		</para>
		<example>
		<title>Set <varname>auto_reconnect</varname> parameter</title>
		<programlisting format="linespecific">
...
modparam("db_mysql", "auto_reconnect", 0)
...
</programlisting>
		</example>
	</section>
	<section id="db_mysql.p.insert_delayed">
		<title><varname>insert_delayed</varname> (integer)</title>
		<para>
		If set to 1, all INSERT SQL queries will be sent to MySQL server as
		INSERT DELAYED.
		</para>
		<para>
		<emphasis>
			Default value is 0 (1 - on / 0 - off).
		</emphasis>
		</para>
		<example>
		<title>Set <varname>insert_delayed</varname> parameter</title>
		<programlisting format="linespecific">
...
modparam("db_mysql", "insert_delayed", 1)
...
</programlisting>
		</example>
	</section>
        <section id="db_mysql.p.update_affected_found">
		<title><varname>update_affected_found</varname> (integer)</title>
		<para>
		If set to 1, all UPDATE SQL queries will return the number of matched rows instead of the number of "updated" rows.
		</para>
		<para>
		<emphasis>
			Default value is 0 (1 - on / 0 - off).
		</emphasis>
		</para>
		<example>
		<title>Set <varname>update_affected_found</varname> parameter</title>
		<programlisting format="linespecific">
...
modparam("db_mysql", "update_affected_found", 1)
...
</programlisting>
		</example>
	</section>
	<section id="db_mysql.p.opt_ssl_mode">
		<title><varname>opt_ssl_mode</varname> (integer)</title>
		<para>
		Control how the connection to MySQL server is done in regards to SSL/TLS.
		If set to 1, SSL/TLS mode is disabled.
		</para>
		<para>
		The 'include/mysql.h' starting with MySQL 5.7.11 defines the values for
		this option as enum: 'mysql_ssl_mode { SSL_MODE_DISABLED=1, SSL_MODE_PREFERRED,
		SSL_MODE_REQUIRED, SSL_MODE_VERIFY_CA, SSL_MODE_VERIFY_IDENTITY}'.
		</para>
		<para>
		Note: if the value of this parameter is 1, it enforces SSL_MODE_DISABLED,
		any other value is passed to the mysql_options(), not checking if it is
		defined.
		</para>
		<para>
		Note: this option is supported only by libmysqlclient, not by libmariadbclient.
		</para>
		<para>
		<emphasis>
			Default value is 0 (0 - off).
		</emphasis>
		</para>
		<example>
		<title>Set <varname>opt_ssl_mode</varname> parameter</title>
		<programlisting format="linespecific">
...
modparam("db_mysql", "opt_ssl_mode", 1)
...
</programlisting>
		</example>
	</section>
	<section id="db_mysql.p.opt_ssl_ca">
		<title><varname>opt_ssl_ca</varname> (string)</title>
		<para>
		Configures the CA certs used to verify the MySQL server cert when
		SSL is enabled.
		</para>
		<para>
		Required when opt_ssl_mode = 4 or 5 and db_mysql is built
		with libmysqlclient.
		</para>
		<para>
		<emphasis>
			Default value is NULL (NULL - not configured).
		</emphasis>
		</para>
		<example>
		<title>Set <varname>opt_ssl_ca</varname> parameter</title>
		<programlisting format="linespecific">
...
modparam("db_mysql", "opt_ssl_ca", "/etc/ssl/certs/mysql-ca.pem")
...
</programlisting>
		</example>
	</section>
	</section>
	<section>
	<title>Functions</title>
		<para>
		No function exported to be used from configuration file.
		</para>
	</section>
	<section>
	<title>Installation</title>
		<para>
		Because it depends on an external library, the mysql module is not
		compiled and installed by default. You can use one of these options.
		</para>
		<itemizedlist>
			<listitem>
			<para>
			- edit the "Makefile" and remove "db_mysql" from "excluded_modules"
			list. Then follow the standard procedure to install &kamailio;:
			"make all; make install".
			</para>
			</listitem>
			<listitem>
			<para>
			- from command line use: 'make all include_modules="db_mysql";
			make install include_modules="db_mysql"'.
			</para>
			</listitem>
		</itemizedlist>
	</section>
	<section>
		<title>Reading configuration from my.cnf</title>
		<para>
		In order to take into account specific mysql client options, a my.cnf config group can be passed using the <emphasis>db_url</emphasis> module parameter. This is done by setting <emphasis>[group]</emphasis> in front of or instead of the host part. The following examples are valid <emphasis>db_url</emphasis> definitions, which include a my.cnf group:
		</para>

		<itemizedlist>
			<listitem>mysql://user:pass@[group]host:port/db</listitem>
			<listitem>mysql://user:pass@[group]:port/db</listitem>
			<listitem>mysql://user:pass@[group]/db</listitem>
			<listitem>mysql://[group]/db</listitem>
		</itemizedlist>
		<example>
		<title>Set a my.cnf group in <varname>db_url</varname> parameter</title>
		<programlisting format="linespecific">
...
modparam("usrloc", "db_url", "mysql://[kamailio]/kamailio)
...
</programlisting>
		</example>
		<example>
		<title>Adding a kamailio group to my.cnf</title>
		<programlisting format="linespecific">
...
[kamailio]
socket = /path/to/mysql.sock
user = kamailiouser
password = kamailiopass
default-character-set = utf8
...
</programlisting>
		</example>
		<para>
		In addition to the given group, also the <emphasis>[client]</emphasis> section is read, in the order given in my.cnf. So if you for example specify a <emphasis>socket</emphasis> in both your specific group and the client group, then the value is taken from the last one.
		</para>
		<example>
		<title>Using [client] and specific group</title>
		<programlisting format="linespecific">
...
[client]
socket = /run/mysql/mysqld.sock

[kamailio]
socket = /path/to/mysqld.sock
user = kamailiouser
password = kamailiopass
default-character-set = utf8
...
</programlisting>
		</example>
		<para>
		In the example given above, the socket <emphasis>/path/to/mysqld.sock</emphasis> is used by &kamailio; because both <emphasis>[kamailio]</emphasis> and <emphasis>[client]</emphasis> define this option, and the latter overwrites the first.
		</para>
	</section>
</chapter>

