Skip site navigation (1)Skip section navigation (2)

FreeBSD Manual Pages


home | help
DBD::MariaDB(3)	      User Contributed Perl Documentation      DBD::MariaDB(3)

       DBD::MariaDB - MariaDB and MySQL	driver for the Perl5 Database
       Interface (DBI)

	 use DBI;

	 my $dsn = "DBI:MariaDB:database=$database;host=$hostname;port=$port";
	 my $dbh = DBI->connect($dsn, $user, $password);

	 my $sth = $dbh->prepare(
	     'SELECT id, first_name, last_name FROM authors WHERE last_name = ?'
	 ) or die 'prepare statement failed: ' . $dbh->errstr();
	 $sth->execute('Eggers') or die	'execution failed: ' . $dbh->errstr();
	 print $sth->rows() . "	rows found.\n";
	 while (my $ref	= $sth->fetchrow_hashref()) {
	     print "Found a row: id = $ref->{'id'}, fn = $ref->{'first_name'}\n";


	 use strict;
	 use warnings;
	 use DBI;

	 # Connect to the database.
	 my $dbh = DBI->connect('DBI:MariaDB:database=test;host=localhost',
				'joe', q(joe's password),
				{ RaiseError =>	1, PrintError => 0 });

	 # Drop	table 'foo'. This may fail, if 'foo' doesn't exist
	 # Thus	we put an eval around it.
	 eval {
	     $dbh->do('DROP TABLE foo');
	 } or do {
	     print 'Dropping foo failed: ' . $dbh->errstr() . "\n";

	 # Create a new	table 'foo'. This must not fail, thus we don't
	 # catch errors.
	 $dbh->do('CREATE TABLE	foo (id	INTEGER, name VARCHAR(20))');

	 # INSERT some data into 'foo' using placeholders
	 $dbh->do('INSERT INTO foo VALUES (?, ?)', undef, 2, 'Jochen');

	 # now retrieve	data from the table.
	 my $sth = $dbh->prepare('SELECT * FROM	foo');
	 while (my $ref	= $sth->fetchrow_hashref()) {
	     print "Found a row: id = $ref->{'id'}, name = $ref->{'name'}\n";

	 # Disconnect from the database.

       DBD::MariaDB is the Perl5 Database Interface driver for MariaDB and
       MySQL databases.	In other words:	DBD::MariaDB is	an interface between
       the Perl	programming language and the MariaDB/MySQL programming API
       that comes with the MariaDB/MySQL relational database management
       system. Most functions provided by this programming API are supported.
       Some rarely used	functions are missing, mainly because no-one ever
       requested them. :-)

       In what follows we first	discuss	the use	of DBD::MariaDB, because this
       is what you will	need the most. For installation, see the separate
       document	DBD::MariaDB::INSTALL. See "EXAMPLE" for a simple example

       From perl you activate the interface with the statement

	 use DBI;

       After that you can connect to multiple MariaDB and MySQL	database
       servers and send	multiple queries to any	of them	via a simple object
       oriented	interface. Two types of	objects	are available: database
       handles and statement handles. Perl returns a database handle to	the
       connect method like so:

	 my $dbh = DBI->connect("DBI:MariaDB:database=$db;host=$host",
				$user, $password,
				{ RaiseError =>	1, PrintError => 0 });

       Once you	have connected to a database, you can execute SQL statements

	 $dbh->do('INSERT INTO foo VALUES (?, ?)', undef, $number, $name);

       See DBI do method for details. See also the bind_param method in	DBI.
       See "DATABASE HANDLES" below for	more details on	database handles.

       If you want to retrieve results,	you need to create a so-called
       statement handle	with:

	 my $sth = $dbh->prepare('SELECT * FROM	' . $dbh->quote_identifier($table));

       This statement handle can be used for multiple things. First of all you
       can retrieve a row of data:

	 my $row = $sth->fetchrow_hashref();

       If your table has columns "ID" and "NAME", then $row will be hash ref
       with keys "ID" and "NAME". See "STATEMENT HANDLES" below	for more
       details on statement handles.

       But now for a more formal approach:

   Class Methods
	     use DBI;

	     my	$dsn = "DBI:MariaDB:$database";
	     my	$dsn = "DBI:MariaDB:database=$database;host=$hostname";
	     my	$dsn = "DBI:MariaDB:database=$database;host=$hostname;port=$port";
	     my	$dsn = "DBI:MariaDB:database=$database;mariadb_socket=$socket";

	     my	$dbh = DBI->connect($dsn, $user, $password);

	   The database	is not a required attribute, but please	note that
	   MariaDB and MySQL has no such thing as a default database. If you
	   don't specify the database at connection time your active database
	   will	be null	and you'd need to prefix your tables with the database
	   name; i.e. "SELECT *	FROM mydb.mytable".

	   This	is similar to the behavior of the "mariadb" or "mysql" command
	   line	client.	Also, "SELECT DATABASE()" will return the current
	   database active for the handle.

	       The host, if not	specified or specified as empty	string or
	       "localhost", will default to a MariaDB or MySQL server running
	       on the local machine using the default for the UNIX socket. To
	       connect to a MariaDB or MySQL server on the local machine via
	       TCP, you	must specify the loopback IP address as the

	       Should the MariaDB or MySQL server be running on	a non-standard
	       port number, you	may explicitly state the "port number" to
	       connect to in the host argument,	by concatenating the
	       "hostname" and "port number" together separated by a colon
	       (":") character or by using the port argument.

	       To connect to a MariaDB or MySQL	server on localhost using
	       TCP/IP, you must	specify	the host as with the
	       optional	port, e.g. 3306.

	       When connecting to a MariaDB or MySQL Server with IPv6, a
	       bracketed IPv6 address should be	used. Example DSN:

		 my $dsn = 'DBI:MariaDB:;host=[1a12:2800:6f2:85::f20:8cf];port=3306';

	       Enables (logical	true value) or disables	(logical false value)
	       the flag	"CLIENT_FOUND_ROWS" while connecting to	the MariaDB or
	       MySQL server. This has a	somewhat funny effect. Without
	       mariadb_client_found_rows, if you perform a query like

		 UPDATE	t SET id = 1 WHERE id =	1;

	       then the	MariaDB	or MySQL engine	will always return 0, because
	       no rows have changed. With mariadb_client_found_rows however,
	       it will return the number of rows that have an id 1, as some
	       people are expecting. At	least for compatibility	to other

	       By default mariadb_client_found_rows is enabled.

	       If your DSN contains the	option "mariadb_compression=1",	then
	       the communication between client	and server will	be compressed.

	       If your DSN contains the	option "mariadb_connect_timeout=##",
	       the connect request to the server will timeout if it has	not
	       been successful after the given number of seconds. Zero value
	       means infinite timeout.

	       If your DSN contains the	option "mariadb_write_timeout=##", the
	       write operation to the server will timeout if it	has not	been
	       successful after	the given number of seconds. Zero value	means
	       infinite	timeout.

	       If your DSN contains the	option "mariadb_read_timeout=##", the
	       read operation to the server will timeout if it has not been
	       successful after	the given number of seconds. Zero value	means
	       infinite	timeout.

	       If your DSN contains the	option "mariadb_init_command=SQL",
	       then this "SQL" statement is executed when connecting to	the
	       MariaDB or MySQL	server.	It is automatically re-executed	if
	       reconnection occurs.

	       This option is for older	MySQL databases	that don't have	secure
	       auth set.

	       These options can be used to read a config file like
	       /etc/my.cnf or ~/.my.cnf. By default MariaDB's and MySQL's C
	       client library doesn't use any config files unlike the client
	       programs	(mysql,	mysqladmin, ...) that do, but outside of the C
	       client library. Thus you	need to	explicitly request reading a
	       config file, as in

		 my $dsn = 'DBI:MariaDB:test;mariadb_read_default_file=/home/joe/my.cnf';
		 my $dbh = DBI->connect($dsn, $user, $password);

	       The option mariadb_read_default_group can be used to specify
	       the default group in the	config file: Usually this is the
	       "client"	group, but see the following example:



	       (Note the order of the entries! The example won't work, if you
	       reverse the "[client]" and "[perl]" sections!)

	       If you read this	config file, then you'll be typically
	       connected to "localhost". However, by using

		 my $dsn = 'DBI:MariaDB:test;mariadb_read_default_group=perl;'
			 . 'mariadb_read_default_file=/home/joe/my.cnf';
		 my $dbh = DBI->connect($dsn, $user, $password);

	       you'll be connected to "perlhost". Note that if you specify a
	       default group and do not	specify	a file,	then the default
	       config files will all be	read. See the documentation of the C
	       function	"mysql_options()" for details.

	       It is possible to choose	the Unix socket	that is	used for
	       connecting to the server. This is done, for example, with

		 my $dsn = 'DBI:MariaDB:database=test;'
			 . 'mariadb_socket=/var/run/mysqld/mysqld.sock';

	       Usually there's no need for this	option,	unless you are using
	       another location	for the	socket than that built into the

	       A true value turns on the "CLIENT_SSL" flag when	connecting to
	       the MariaDB or MySQL server and enforce SSL encryption. A false
	       value (which is default)	disable	SSL encryption with the
	       MariaDB or MySQL	server.

	       When enabling SSL encryption you	should set also	other SSL
	       options,	at least mariadb_ssl_ca_file or	mariadb_ssl_ca_path.

		 my $dsn = 'DBI:MariaDB:database=test;host=hostname;port=3306;'
			 . 'mariadb_ssl=1;mariadb_ssl_verify_server_cert=1;'
			 . 'mariadb_ssl_ca_file=/path/to/ca_cert.pem';

	       This means that your communication with the server will be

	       The path	to a file in PEM format	that contains a	list of
	       trusted SSL certificate authorities.

	       When set	MariaDB	or MySQL server	certificate is checked that it
	       is signed by some CA certificate	in the list. Common Name value
	       is not verified unless mariadb_ssl_verify_server_cert is

	       The path	to a directory that contains trusted SSL certificate
	       authority certificates in PEM format.

	       When set	MariaDB	or MySQL server	certificate is checked that it
	       is signed by some CA certificate	in the list. Common Name value
	       is not verified unless mariadb_ssl_verify_server_cert is

	       Please note that	this option is supported only if your MariaDB
	       or MySQL	client was compiled with OpenSSL library, and not with
	       default yaSSL library.

	       Checks the server's Common Name value in	the certificate	that
	       the server sends	to the client. The client verifies that	name
	       against the host	name the client	uses for connecting to the
	       server, and the connection fails	if there is a mismatch.	For
	       encrypted connections, this option helps	prevent	man-in-the-
	       middle attacks.

	       Verification of the host	name is	disabled by default.

	       The name	of the SSL key file in PEM format to use for
	       establishing a secure connection.

	       The name	of the SSL certificate file in PEM format to use for
	       establishing a secure connection.

	       A list of permissible ciphers to	use for	connection encryption.
	       If no cipher in the list	is supported, encrypted	connections
	       will not	work.


	       Setting mariadb_ssl_optional to true disables strict SSL
	       enforcement and makes SSL connection optional. This option
	       opens security hole for man-in-the-middle attacks. Default
	       value is	false which means that mariadb_ssl set to true
	       enforces	SSL encryption.

	       Due to The BACKRONYM <> and The Riddle
	       <> vulnerabilities in libmariadb and
	       libmysqlclient libraries, enforcement of	SSL encryption was not
	       possible	and therefore "mariadb_ssl_optional=1" was effectively
	       set for old DBD::mysql driver prior DBD::MariaDB	fork was
	       created.	DBD::MariaDB with "mariadb_ssl=1" could	refuse
	       connection to MariaDB or	MySQL server if	underlying libmariadb
	       or libmysqlclient library is vulnerable.	Option
	       mariadb_ssl_optional can	be used	to make	SSL connection

	       The "LOCAL" capability for "LOAD	DATA" may be disabled in the
	       MariaDB or MySQL	client library by default. If your DSN
	       contains	the option "mariadb_local_infile=1", "LOAD DATA	LOCAL"
	       will be enabled.	However, this option is	ineffective if the
	       server has also been configured to disallow "LOCAL".

	       Support for multiple statements separated by a semicolon	(";")
	       may be enabled by using this option. Enabling this option may
	       cause problems if server-side prepared statements are also

	       This option is used to enable server side prepared statements.
	       By default prepared statements are not used and placeholder
	       replacement is done by DBD::MariaDB prior to sending SQL
	       statement to MariaDB or MySQL server.

	       This default behavior may change	in the future.

	       To use server side prepared statements, all you need to do is
	       set the variable	mariadb_server_prepare in the connect:

		 my $dbh = DBI->connect(
		     { RaiseError => 1,	PrintError => 0	},


		 my $dbh = DBI->connect(
		     { RaiseError => 1,	PrintError => 0, mariadb_server_prepare	=> 1 },

	       There are many benefits to using	server side prepare
	       statements, mostly if you are using SQL statements with
	       placeholders or performing many inserts because of that fact
	       that a single statement is prepared to accept multiple insert

	       Please note that	MariaDB	or MySQL server	cannot prepare or
	       execute some prepared statements. In this case DBD::MariaDB
	       fallbacks to normal non-prepared	statement and tries again.

	       This option disable fallback to normal non-prepared statement
	       when MariaDB or MySQL server does not support execution of
	       current statement as prepared.

	       Useful when you want to be sure that the	statement is going to
	       be executed as server side prepared. Error message and code in
	       case of failure is propagated back to DBI.

	       This default behavior may change	in the future.

	       The option mariadb_embedded_options can be used to pass command
	       line options to the embedded server. When you want to start and
	       connect embedded	server,	use "host=embedded" in dsn as
	       connection parameter.


		 use DBI;
		 my $datadir = '/var/lib/mysql/';
		 my $langdir = '/usr/share/mysql/english';
		 my $dsn = 'DBI:MariaDB:host=embedded;database=test;'
			 . "mariadb_embedded_options=--datadir=$datadir,--language=$langdir";
		 my $dbh = DBI->connect($dsn, undef, undef);

	       This would start	embedded server	with language directory
	       $langdir, database directory $datadir and connects to database
	       "test". Embedded	server does not	have to	be supported by
	       configured MariaDB or MySQL library. In that case
	       "DBI->connect()"	returns	an error.

	       The option mariadb_embedded_groups can be used to specify the
	       groups in the config file (my.cnf) which	will be	used to	get
	       options for the embedded	server.	If not specified "[server]"
	       and "[embedded]"	groups will be used.


		 my $dsn = 'DBI:MariaDB:host=embedded;database=test;'
			 . 'mariadb_embedded_groups=embedded_server,common';

	       The option mariadb_conn_attrs is	a hash of attribute names and
	       values which can	be used	to send	custom connection attributes
	       to the server. Some attributes like "_os", "_platform",
	       "_client_name" and "_client_version" are	added by libmariadb or

	       You can then later read these attributes	from the performance
	       schema tables which can be quite	helpful	for profiling your
	       database	or creating statistics.	 You'll	have to	use both
	       server and client at least in version MariaDB 10.0.5 or MySQL
	       5.6 to leverage this feature. It	is a good idea to provides
	       additional "program_name" attribute.

		 my $dbh= DBI->connect($dsn, $user, $password, {
		     AutoCommit	=> 0,
		     mariadb_conn_attrs	=> {
			 program_name => $0,
			 foo =>	'bar',
			 wiz =>	'bang'

	       Now you can select the results from the performance schema
	       tables. You can do this in the same session, but	also
	       afterwards. It can be very useful to answer questions like
	       which script sent this query?

		 my $results = $dbh->selectall_hashref(
		     'SELECT * FROM performance_schema.session_connect_attrs',

	       This returns:

		 $result = {
		     '_client_name' => {
			 'ATTR_VALUE'	    => 'libmysql',
			 'ATTR_NAME'	    => '_client_name',
			 'ORDINAL_POSITION' => '1',
			 'PROCESSLIST_ID'   => '3',
		     '_client_version' => {
			 'ATTR_VALUE'	    => '5.6.24',
			 'ATTR_NAME'	    => '_client_version',
			 'ORDINAL_POSITION' => '7',
			 'PROCESSLIST_ID'   => '3',
		     '_os' => {
			 'ATTR_VALUE'	    => 'osx10.8',
			 'ATTR_NAME'	    => '_os',
			 'ORDINAL_POSITION' => '0',
			 'PROCESSLIST_ID'   => '3',
		     '_pid' => {
			 'ATTR_VALUE'	    => '59860',
			 'ATTR_NAME'	    => '_pid',
			 'ORDINAL_POSITION' => '2',
			 'PROCESSLIST_ID'   => '3',
		     '_platform' => {
			 'ATTR_VALUE'	    => 'x86_64',
			 'ATTR_NAME'	    => '_platform',
			 'ORDINAL_POSITION' => '4',
			 'PROCESSLIST_ID'   => '3',
		     'foo' => {
			 'ATTR_NAME'	    => 'foo',
			 'ATTR_VALUE'	    => 'bar',
			 'ORDINAL_POSITION' => '6',
			 'PROCESSLIST_ID'   => '3',
		     'program_name' => {
			 'ATTR_VALUE'	    => './',
			 'ATTR_NAME'	    => 'program_name',
			 'ORDINAL_POSITION' => '5',
			 'PROCESSLIST_ID'   => '3',
		     'wiz' => {
			 'ATTR_VALUE'	    => 'bang',
			 'ATTR_NAME'	    => 'wiz',
			 'ORDINAL_POSITION' => '3',
			 'PROCESSLIST_ID'   => '3',

	     use DBI;
	     my	@dsns =	DBI->data_sources('MariaDB', {
		 host => $hostname,
		 port => $port,
		 user => $username,
		 password => $password,

	   Returns a list of all databases in dsn format suitable for connect
	   method, managed by the MariaDB or MySQL server. It accepts all
	   attributes from connect method.

       The DBD::MariaDB	driver supports	the following attributes of database
       handles (read only):

	 my $errno = $dbh->{'mariadb_errno'};
	 my $error = $dbh->{'mariadb_error'};
	 my $hostinfo =	$dbh->{'mariadb_hostinfo'};
	 my $info = $dbh->{'mariadb_info'};
	 my $insertid =	$dbh->{'mariadb_insertid'};
	 my $protoinfo = $dbh->{'mariadb_protoinfo'};
	 my $serverinfo	= $dbh->{'mariadb_serverinfo'};
	 my $ssl_cipher	= $dbh->{'mariadb_ssl_cipher'};
	 my $stat = $dbh->{'mariadb_stat'};
	 my $thread_id = $dbh->{'mariadb_thread_id'};

       These correspond	to "mysql_errno()", "mysql_error()",
       "mysql_get_host_info()",	"mysql_info()",	"mysql_insert_id()",
       "mysql_get_proto_info()", "mysql_get_server_info()", "mysql_stat()",
       "mysql_get_ssl_cipher()"	and "mysql_thread_id()"	respectively.

       Portable	DBI applications should	not use	them. Instead they should use
       standard	DBI methods: "$dbh->err()" and "$dbh->errstr()"	for error
       number and string, "$dbh->get_info($GetInfoType{SQL_SERVER_NAME})" for
       server host name, "$dbh->get_info($GetInfoType{SQL_DBMS_NAME})" and
       "$dbh->get_info($GetInfoType{SQL_DBMS_VER})" for	server database	name
       and version, "$dbh->last_insert_id()" or	"$sth->last_insert_id()" for
       insert id.

	 List information of the MariaDB or MySQL client library that
	 DBD::MariaDB was built	against:

	   print "$dbh->{mariadb_clientinfo}\n";


	   print "$dbh->{mariadb_clientversion}\n";


	 Portable DBI applications should not be interested in version of
	 underlying client library. DBD::MariaDB is there to hide any possible
	 incompatibility and works correctly with any available	version.

	   print "$dbh->{mariadb_serverversion}\n";


	 Portable DBI applications should use
	 "$dbh->get_info($GetInfoType{SQL_DBMS_NAME})" and
	 "$dbh->get_info($GetInfoType{SQL_DBMS_VER})" for server database name
	 and version instead.

	 Returns the SSL encryption cipher used	for the	given connection to
	 the server.  In case SSL encryption was not enabled with mariadb_ssl
	 or was	not established	returns	"undef".

	   my $ssl_cipher = $dbh->{mariadb_ssl_cipher};
	   if (defined $ssl_cipher) {
	       print "Connection with server is	encrypted with cipher: $ssl_cipher\n";
	   } else {
	       print "Connection with server is	not encrypted\n";

	   my $info_hashref = $dbh->{mariadb_dbd_stats};

	 DBD::MariaDB keeps track of some statistics in	the mariadb_dbd_stats
	 attribute. The	following stats	are being maintained:

		 The number of times that DBD::MariaDB successfully
		 reconnected to	the MariaDB or MySQL server.

		 The number of times that DBD::MariaDB tried to	reconnect to
		 MariaDB or MySQL but failed.

       The DBD::MariaDB	driver also supports the following attributes of
       database	handles	(read/write):

	   This	attribute determines whether DBD::MariaDB will automatically
	   reconnect to	MariaDB	or MySQL server	if the connection be lost.
	   This	feature	defaults to off.  Setting mariadb_auto_reconnect to 1
	   is not advised if "LOCK TABLES" is used because if DBD::MariaDB
	   reconnect to	MariaDB	or MySQL server	all table locks	will be	lost.
	   This	attribute is ignored when AutoCommit is	turned off, and	when
	   AutoCommit is turned	off, DBD::MariaDB will not automatically
	   reconnect to	the server.

	   It is also possible to set the default value	of the
	   mariadb_auto_reconnect attribute for	the $dbh by passing it in the
	   "\%attr" hash for "DBI->connect".

	     $dbh->{mariadb_auto_reconnect} = 1;


	     my	$dbh = DBI->connect($dsn, $user, $password, {
		 mariadb_auto_reconnect	=> 1,

	   Note	that if	you are	using a	module or framework that performs
	   reconnections for you (for example DBIx::Connector in fixup mode),
	   this	value must be set to 0.

	   This	attribute forces the driver to use "mysql_use_result()"	rather
	   than	"mysql_store_result()" library function. The former is faster
	   and less memory consuming, but tends	to block other processes.
	   "mysql_store_result()" is the default due to	that fact storing the
	   result is expected behavior with most applications.

	   It is possible to set the default value of the mariadb_use_result
	   attribute for the $dbh via the DSN:

	     my	$dbh = DBI->connect('DBI:MariaDB:test;mariadb_use_result=1', $user, $pass);

	   You can also	set it after creation of the database handle:

	     $dbh->{mariadb_use_result}	= 0; # disable
	     $dbh->{mariadb_use_result}	= 1; # enable

	   You can also	set or unset the mariadb_use_result setting on your
	   statement handle, when creating the statement handle	or after it
	   has been created. See "STATEMENT HANDLES".

	   This	attribute causes the driver (emulated prepare statements) to
	   attempt to guess if a value being bound is a	numeric	value, and if
	   so, doesn't quote the value.	This was created by Dragonchild	and is
	   one way to deal with	the performance	issue of using quotes in a
	   statement that is inserting or updating a large numeric value.

	   CAVEAT: Even	though you can insert an integer value into a
	   character column, if	this column is indexed,	if you query that
	   column with the integer value not being quoted, it will not use the

	     MariaDB [test]> explain select * from test	where value0 = '3' \G
	     *************************** 1. row	***************************
			id: 1
	       select_type: SIMPLE
		     table: test
		      type: ref
	     possible_keys: value0
		       key: value0
		   key_len: 13
		       ref: const
		      rows: 1
		     Extra: Using index	condition
	     1 row in set (0.00	sec)

	     MariaDB [test]> explain select * from test	where value0 = 3
		 -> \G
	     *************************** 1. row	***************************
			id: 1
	       select_type: SIMPLE
		     table: test
		      type: ALL
	     possible_keys: value0
		       key: NULL
		   key_len: NULL
		       ref: NULL
		      rows: 6
		     Extra: Using where
	     1 row in set (0.00	sec)

	   See bug: <>

	   mariadb_bind_type_guessing can be turned on via

	   - through DSN

	     my	$dbh = DBI->connect('DBI:MariaDB:test',	'username', 'pass', {
		 mariadb_bind_type_guessing => 1

	   - OR	after handle creation

	     $dbh->{mariadb_bind_type_guessing}	= 1;

	   This	attribute causes the driver (emulated prepare statements) will
	   cause any placeholders in comments to be bound. This	is not correct
	   prepared statement behavior,	but some developers have come to
	   depend on this behavior.

	   This	attribute causes the driver to not issue "SET AUTOCOMMIT"
	   either through explicit or using "mysql_autocommit()". This is
	   particularly	useful in the case of using MySQL Proxy.

	   See the bug report:

	   mariadb_no_autocommit_cmd can be turned on when creating the
	   database handle:

	     my	$dbh = DBI->connect('DBI:MariaDB:test',	'username', 'pass', {
		 mariadb_no_autocommit_cmd => 1

	   or using an existing	database handle:

	     $dbh->{mariadb_no_autocommit_cmd} = 1;

	   This	attribute controls the maximum size of one packet, any
	   generated or	intermediate string and	any bind parameter. Default
	   value depends on client MariaDB/MySQL library and should be 1GB.

	     $dbh->{mariadb_max_allowed_packet}	= 32*1024*1024;	# limit	max size to 32MB

       Documentation for some DBD::MariaDB methods of database handles:

	 This can be used to send a ping to the	server.	See DBI	ping.

	   my $rc = $dbh->ping();

	 This method can be used to retrieve information about MariaDB or
	 MySQL server.	See DBI	get_info. Some useful information:
	 "SQL_DBMS_NAME" returns server	database name, either "MariaDB"	or
	 "MySQL". "SQL_DBMS_VER" returns server	database version and
	 "SQL_SERVER_NAME" returns server host name.

	   use DBI::Const::GetInfoType;

	   print $dbh->get_info($GetInfoType{SQL_DBMS_NAME});


	   print $dbh->get_info($GetInfoType{SQL_DBMS_VER});


	   print $dbh->get_info($GetInfoType{SQL_SERVER_NAME});

	   Localhost via UNIX socket

       The statement handles of	DBD::MariaDB support a number of attributes.
       You access these	by using, for example,

	 my $numFields = $sth->{NUM_OF_FIELDS};

       Note, that most attributes are valid only after a successful execute.
       An "undef" value	will returned otherwise. The most important exception
       is the mariadb_use_result attribute.

       To set the mariadb_use_result attribute on statement handle $sth, use
       either of the following:

	 my $sth = $dbh->prepare($sql, { mariadb_use_result => 1});


	 my $sth = $dbh->prepare($sql);
	 $sth->{mariadb_use_result} = 1;

       Column dependent	attributes, for	example	NAME, the column names,	are
       returned	as a reference to an array. The	array indices are
       corresponding to	the indices of the arrays returned by fetchrow and
       similar methods.	For example the	following code will print a header of
       table names together with all rows:

	 my $sth = $dbh->prepare('SELECT * FROM	t')
	     or	die 'Error: ' .	$dbh->errstr() . "\n";

	     or	die 'Error: ' .	$sth->errstr() . "\n";

	 my $names = $sth->{NAME};
	 my $numFields = $sth->{'NUM_OF_FIELDS'} - 1;
	 for my	$i ( 0..$numFields ) {
	     printf('%s%s', $i ? ',' : '', $$names[$i]);
	 print "\n";
	 while (my $ref	= $sth->fetchrow_arrayref()) {
	     for my $i ( 0..$numFields ) {
		 printf('%s%s',	$i ? ',' : '', $$ref[$i]);
	     print "\n";

       For portable applications you should restrict yourself to attributes
       with capitalized	or mixed case names. Lower case	attribute names	are
       private to DBD::MariaDB.	The attribute list includes:

	   This	attribute determines whether a fetchrow	will chop preceding
	   and trailing	blanks off the column values. Chopping blanks does not
	   have	impact on the mariadb_max_length attribute.

	   If the statement you	executed performs an "INSERT", and there is an
	   "AUTO_INCREMENT" column in the table	you inserted in, this
	   attribute holds the value stored into the "AUTO_INCREMENT" column,
	   if that value is automatically generated, by	storing	"NULL" or 0 or
	   was specified as an explicit	value.

	   Typically, you'd access the value via "$sth->{mariadb_insertid}".
	   The value can also be accessed via "$dbh->{mariadb_insertid}" but
	   this	can easily produce incorrect results in	case one database
	   handle is shared.

	   Portable DBI	applications should not	use mariadb_insertid. Instead
	   they	should use DBI method "$dbh->last_insert_id()" or statement
	   DBI method "$sth->last_insert_id()".	Statement method was
	   introduced in DBI version 1.642, but	DBD::MariaDB implements	it
	   also	for older DBI versions.

	   Reference to	an array of boolean values; Logical true value
	   indicates, that the respective column is a blob.

	   Reference to	an array of boolean values; Logical true value
	   indicates, that the respective column is a key.

	   Reference to	an array of boolean values; Logical true value
	   indicates, that the respective column contains numeric values.

	   Reference to	an array of boolean values; Logical true value
	   indicates, that the respective column is a primary key.

	   Reference to	an array of boolean values; Logical true value
	   indicates that the respective column	is an "AUTO_INCREMENT" column.

	   A reference to an array of maximum column sizes. The
	   mariadb_max_length is the maximum physically	present	in the result
	   table, mariadb_length gives the theoretically possible maximum.

	   For string orientated variable types	(char, varchar,	text and
	   similar types) both attributes return value in bytes. If you	are
	   interested in number	of characters then instead of mariadb_length
	   use "COLUMN_SIZE" via standard DBI method column_info and instead
	   of mariadb_max_length issue SQL query "SELECT
	   MAX(CHAR_LENGTH(...))". Example:

	     my	$ci_sth	= $dbh->column_info(undef, undef, $table, $column);
	     my	$ci_ref	= $ci_sth->fetchall_arrayref({});
	     my	$mariadb_char_length = $ci_ref->[0]->{COLUMN_SIZE};

	     my	$mariadb_char_max_length = $dbh->selectrow_array(sprintf(
					       'SELECT MAX(CHAR_LENGTH(%s)) FROM %s',

	   A reference to an array of column names.

	   A reference to an array of boolean values; Logical true value
	   indicates that this column may contain "NULL"'s.

	   Number of fields returned by	a "SELECT" statement. You may use this
	   for checking	whether	a statement returned a result: A zero value
	   indicates a non-"SELECT" statement like "INSERT", "DELETE" or

	   A reference to an array of table names, useful in a "JOIN" result.

	   A reference to an array of column types. The	engine's native	column
	   types are mapped to portable	types like "DBI::SQL_INTEGER()"	or
	   "DBI::SQL_VARCHAR()", as good as possible. Not all native types
	   have	a meaningful equivalent. If you	need the native	column types,
	   use mariadb_type. See below.

	   A reference to an array of MySQL's native column types, for example
	   "DBD::MariaDB::TYPE_SHORT()"	or "DBD::MariaDB::TYPE_STRING()". Use
	   the TYPE attribute, if you want portable types like

	   Similar to mariadb_type, but	type names and not numbers are
	   returned. Whenever possible,	the ANSI SQL name is preferred.

	   The number of warnings generated during execution of	the SQL
	   statement. This attribute is	available on both statement handles
	   and database	handles.

       All string orientated variable types (char, varchar, text and similar
       types) are represented by the DBD::MariaDB as Unicode strings according
       to the standard Perl Unicode model. It means that Perl scalars contain
       Unicode code points and not UTF-8 bytes.	Internally the DBD::MariaDB
       uses the	MySQL's	"utf8mb4" charset for the network communication	with
       MariaDB and MySQL servers. It automatically transforms the network
       MySQL's "utf8mb4" charset to the	Unicode	Perl scalars and vice-versa.

       MySQL's "utf8mb4" charset for the network communication is configured
       by "MYSQL_SET_CHARSET_NAME" libmariadb/libmysqlclient C library API
       which is	a requirement to have working quote method and an emulated
       client side placeholders	replacement.

       Do not try to change network charset (e.g. via SQL command "SET NAMES"
       manually) to anything different then UTF-8 as it	would confuse
       underlying C library and	DBD::MariaDB would misbehave (e.g. would lead
       to broken/insecure quote	method or an emulated client side placeholders

       Using a non-UTF-8 charset for a column, table or	database is fine
       because MariaDB or MySQL	server automatically transforms	the storage
       charset to the charset used by the network protocol ("utf8mb4").	Note
       that when DBD::MariaDB is connecting to the MariaDB or MySQL server it
       calls SQL command "SET character_set_server = 'utf8mb4'"	to ensure that
       the default charset for new databases would be UTF-8. Beware that a
       default charset for new tables is set from a database charset.

       In the case MySQL server	does not support MySQL's "utf8mb4" charset for
       a network protocol then DBD::MariaDB would try to use MySQL's "utf8"
       charset which is	a subset of UTF-8 encoding restricted to the 3 byte
       UTF-8 sequences.	 Support for MySQL's "utf8mb4" charset was introduced
       in MySQL	server version 5.5.3.

   Working with	binary data
       Perl scalars do not distinguish between binary byte orientated buffers
       and Unicode orientated strings. In Perl it is always up to the caller
       and the callee to define	in its API if functions	and methods expect
       byte buffers or Unicode strings.	It is not possible (or rather Perl
       application should not try) to distinguish if Perl scalar contains a
       byte buffer or Unicode string.

       When fetching data from MariaDB and MySQL servers, DBD::MariaDB treats
       all fields marked with MySQL's charset "utf8mb4"	(and also "utf8") as
       Unicode strings.	Everything else	is treated as binary byte oriented
       buffers.	 Therefore, the	only difference	is that	UTF-8 fields are
       automatically decoded to	Unicode. Binary	blob fields remain untouched
       and corresponding Perl scalars would contain just ordinals 0..255
       (classic	sequence of bytes). Unicode string scalars would contain
       sequence	of Unicode code	points.

       There is	a small	problem	with input data, more preciously with SQL
       statements and their bind parameters. By	definition a SQL statement is
       a string	and therefore it is expected and handled by DBD::MariaDB as a
       Unicode string (not byte	oriented buffer). There	is no way to treat a
       SQL statement as	a binary, but this is not a problem. All SQL commands
       are encoded in ASCII and	all ASCII characters are invariants in UTF-8
       (have the same representation as	a sequence of Unicode code points and
       also when UTF-8 encoded in a byte buffer). For the remaining part of a
       SQL statement, placeholders with	bind parameters	can and	should be

   Binary parameters
       Unfortunately, neither MariaDB nor MySQL	server provide any type
       information for prepared	SQL statements;	therefore, DBD::MariaDB	has
       absolutely no way to know if a particular bind parameter	for a
       placeholder should be treated as	Unicode	string or as byte oriented
       buffer. So Perl applications which use DBD::MariaDB must	provide
       information about the correct type.

       Moreover, DBI API for do, execute and all select* methods binds all
       parameters as "SQL_VARCHAR" type. Currently it is an API	limitation
       which does not allow one	to specify the bind type. Varchar is a string
       and so DBD::MariaDB treats all of them as Unicode strings.

       The only	way how	to specify a type in DBI is via	the bind_param method.
       Its third argument takes	"SQL_*"	constant which defines a type for the
       passed bind parameter.

       Following type constants	are treated as binary by DBD::MariaDB:

       This approach of	handling binary	data was implemented in	DBD::MariaDB
       because it does not violate how Perl's Unicode model is working,
       follows exactly DBI API documentation, and, more	importantly, is	how
       other DBI drivers (including DBD::Pg and	DBD::SQLite) in	their recent
       versions	work. This ensures good	compatibility for Perl applications
       which use multiple database backends and	several	DBI drivers.

       Please note that	the old	DBD::mysql driver in version 4.041 works
       differently and has completely broken Unicode support.

       To illustrate the usage,	see the	following example:

	 # Prepare statement
	 my $sth = $dbh->prepare(
	     'INSERT INTO users	(id, name, picture) VALUES (?, ?, ?)'

	 # Bind	number,	7-bit ASCII values are always in Unicode and binary context
	 $sth->bind_param(1, 10);

	 # Bind	name, may contains Unicode character, in this case U+00E9
	 $sth->bind_param(2, "Andr\x{E9}");

	 # Bind	picture, it is a sequence of binary bytes, not Unicode code points
	 $sth->bind_param(3, "\x{D8}\x{A0}\x{39}\x{F8}", DBI::SQL_BINARY);

	 # Execute statement with bind parameters

       Explanation: In this case number	10 and name "Andr\x{E9}" would be
       automatically encoded from Perl Unicode string scalars to MySQL's
       "utf8mb4" network charset and picture would not be touched as it	was
       bound with the "DBI::SQL_BINARY"	type. Note that	7-bit ASCII values are
       invariants in UTF-8, they have the same representations in UTF-8, so
       both the	encoding and decoding operations are just identity functions.

       This is the preferred and safe way how to work with binary data.	It is
       also supported by other DBI drivers, including DBD::Pg and DBD::SQLite
       (see above).

       In DBD::MariaDB,	there's	another	specific way how to create a SQL
       statement with binary data: to call the quote method while specifying a
       binary type. This method	takes a	bind parameter and properly quotes +
       escapes it. For binary types it converts	argument to MySQL's HEX	syntax
       ("X'...'") which	is a pure 7-bit	ASCII and therefore invariant for
       UTF-8. See the following	example:

	 my $param1 = 10;
	 my $param2 = "Andr\x{E9}";
	 my $param3 = "\x{D8}\x{A0}\x{39}\x{F8}";
	 my $query = 'INSERT INTO users	(id, name, picture) VALUES (' .
		       $dbh->quote($param1) . '	,' .
		       $dbh->quote($param2) . '	,' .
		       $dbh->quote($param3, DBI::SQL_BINARY) .

       The first two parameters	are quoted and escaped for a later UTF-8
       encoding	(to MySQL's "utf8mb4" charset) and the third parameter is
       quoted and escaped as a binary buffer to	MySQL's	HEX syntax for binary

       This method is not recommended, because quoting,	escaping and similar
       methods can easily get written incorrectly and lead to SQL injections
       and other security problems.

       The transaction support works as	follows:

       o   By default AutoCommit mode is on, following the DBI specifications.

       o   If you execute

	     $dbh->{AutoCommit}	= 0;


	     $dbh->{AutoCommit}	= 1;

	   then	the driver will	set the	MariaDB	or MySQL server	variable
	   autocommit to 0 or 1, respectively. Switching from 0	to 1 will also
	   issue a "COMMIT", following the DBI specifications.

       o   The methods


	   will	issue the commands "ROLLBACK" and "COMMIT", respectively. A
	   "ROLLBACK" will also	be issued if AutoCommit	mode is	off and	the
	   database handles DESTROY method is called. Again, this is following
	   the DBI specifications.

       Given the above,	you should note	the following:

       o   You should never change the server variable AutoCommit manually,
	   unless you are ignoring DBI's transaction support.

       o   Switching AutoCommit	mode from on to	off or vice versa may fail.
	   You should always check for errors when changing AutoCommit mode.
	   The suggested way of	doing so is using the DBI flag RaiseError. If
	   you don't like RaiseError, you have to use code like	the following:

	     $dbh->{AutoCommit}	= 0;
	     if	($dbh->{AutoCommit}) {
		 # An error occurred!

       o   If you detect an error while	changing the AutoCommit	mode, you
	   should no longer use	the database handle. In	other words, you
	   should disconnect and reconnect again, because the transaction mode
	   is unpredictable.  Alternatively you	may verify the transaction
	   mode	by checking the	value of the server variable autocommit.
	   However, such behaviour isn't portable.

       o   DBD::MariaDB	has a reconnect	feature	that handles the so-called
	   MySQL morning bug: If the server has	disconnected, most probably
	   due to a timeout, then by default the driver	will reconnect and
	   attempt to execute the same SQL statement again. However, this
	   behaviour is	disabled when AutoCommit is off: Otherwise the
	   transaction state would be completely unpredictable after a

       o   The reconnect feature of DBD::MariaDB can be	toggled	by using the
	   mariadb_auto_reconnect attribute. This behaviour should be turned
	   off in code that uses LOCK TABLE because if the database server
	   time	out and	DBD::MariaDB reconnect,	table locks will be lost
	   without any indication of such loss.

       DBD::MariaDB supports multiple result sets, thanks to Guy Harrison!

       The basic usage of multiple result sets is

	 do {
	     while (my @row = $sth->fetchrow_array()) {
		 do stuff;
	 } while ($sth->more_results);

       An example would	be:

	 $dbh->do('drop	procedure if exists someproc')
	     or	print $DBI::errstr;

	 $dbh->do('create procedure someproc() deterministic
		 declare a,b,c,d int;
		 set a=1;
		 set b=2;
		 set c=3;
		 set d=4;
		 select	a, b, c, d;
		 select	d, c, b, a;
		 select	b, a, c, d;
		 select	c, b, d, a;
	 ) or die "$DBI::err: $DBI::errstr";

	 my $sth = $dbh->prepare('call someproc()')
	     or	die "$DBI::err:	$DBI::errstr";

	     or	die "$DBI::err:	$DBI::errstr";

	 my $i=0;
	 do {
	     print "\nRowset ".++$i."\n---------------------------------------\n\n";
	     foreach my	$colno (0..$sth->{NUM_OF_FIELDS}-1) {
		 print $sth->{NAME}->[$colno]."\t";
	     print "\n";
	     while (my @row = $sth->fetchrow_array())  {
		 foreach $field	(0..$#row) {
		     print $row[$field]."\t";
		 print "\n";
	 } while ($sth->more_results);

   Issues with multiple	result sets
       Please be aware there could be issues if	your result sets are jagged,
       meaning the number of columns of	your results vary. Varying numbers of
       columns could result in your script crashing.

       The multithreading capabilities of DBD::MariaDB depend completely on
       the underlying C	libraries. The modules are working with	handle data
       only, no	global variables are accessed or (to the best of my knowledge)
       thread unsafe functions are called. Thus	DBD::MariaDB is	believed to be
       completely thread safe, if the C	libraries are thread safe and you
       don't share handles among threads.

       The obvious question is:	Are the	C libraries thread safe? In the	case
       of MySQL	the answer is yes, since MySQL 5.5 it is. Older	versions C
       library needs to	be compiled with "--with-thread-safe-client" or
       "--enable-thread-safe-client" configure options.

       You can make a single asynchronous query	per MySQL connection; this
       allows you to submit a long-running query to the	server and have	an
       event loop inform you when it's ready. An asynchronous query is started
       by either setting the mariadb_async attribute to	a true value in	the do
       method, or in the prepare method. Statements created with mariadb_async
       set to true in prepare always run their queries asynchronously when
       execute is called. The driver also offers three additional methods:
       "mariadb_async_result()", "mariadb_async_ready()", and
       "mariadb_sockfd()". "mariadb_async_result()" returns what do or execute
       would have; that	is, the	number of rows affected.
       "mariadb_async_ready()" returns true if "mariadb_async_result()"	will
       not block, and zero otherwise. They both	return "undef" if that handle
       was not created with mariadb_async set to true or if an asynchronous
       query was not started yet. "mariadb_sockfd()" returns the file
       descriptor number for the MySQL connection; you can use this in an
       event loop.

       Here's an example of how	to use the asynchronous	query interface:

	 use feature 'say';
	 $dbh->do('SELECT SLEEP(10)', {	mariadb_async => 1 });
	 until($dbh->mariadb_async_ready()) {
	     say 'not ready yet!';
	     sleep 1;
	 my $rows = $dbh->mariadb_async_result();

       See DBD::MariaDB::INSTALL.

       Originally, there was a non-DBI driver, Mysql, which was	much like PHP
       drivers such as mysql and mysqli. The Mysql module was originally
       written by Andreas KA<paragraph>nig ( who still, to
       this day, contributes patches to	DBD::mysql. An emulated	version	of
       Mysql was provided to DBD::mysql	from Jochen Wiedmann, but eventually
       deprecated as it	was another bundle of code to maintain.

       The first incarnation of	DBD::mysql was developed by Alligator
       Descartes, who was also aided and abetted by Gary Shea, Andreas
       KA<paragraph>nig	and Tim	Bunce.

       The current incarnation of DBD::mysql was written by Jochen Wiedmann,
       then numerous changes and bug-fixes were	added by Rudy Lippan. Next,
       prepared	statement support was added by Patrick Galbraith and Alexy
       Stroganov (who also solely added	embedded server	support).

       Since 2004 DBD::mysql has been maintained by Patrick Galbraith
       (,	and since 2013 with the	great help of Michiel Beijen
       (, along with the entire community of Perl
       developers who keep sending patches to help continue improving

       In 2018 unreleased version 4.042_01 of DBD::mysql was forked and
       DBD::MariaDB was	created	to fix long standing Unicode bugs and MariaDB
       support.	Currently it is	developed in GoodData and maintained by	Pali

       Anyone who desires to contribute	to this	project	is encouraged to do
       so.  Currently, the source code for this	project	can be found at


       Either fork this	repository and produce a branch	with your changeset
       that the	maintainer can merge to	his tree, or create a diff with	git.
       The maintainer is more than glad	to take	contributions from the
       community as many features and fixes from DBD::MariaDB have come	from
       the community.

       This module is

       o   Large Portions Copyright (c)	2018 GoodData Corporation

       o   Large Portions Copyright (c)	2015-2017 Pali RohA!r

       o   Large Portions Copyright (c)	2004-2017 Patrick Galbraith

       o   Large Portions Copyright (c)	2013-2017 Michiel Beijen

       o   Large Portions Copyright (c)	2004-2007 Alexey Stroganov

       o   Large Portions Copyright (c)	2003-2005 Rudolf Lippan

       o   Large Portions Copyright (c)	1997-2003 Jochen Wiedmann, with	code

       o   Copyright (c)1994-1997 their	original authors

       This module is released under the same license as Perl itself. See Perl
       Licensing <> for details.

       This module is maintained and supported on a mailing list, dbi-users.

       To subscribe to this list, send an email	to

       "" <>

       Mailing list archives are at


       Additional information on the DBI project can be	found on the World
       Wide Web	at the following URL:


       where documentation, pointers to	the mailing lists and mailing list
       archives	and pointers to	the most current versions of the modules can
       be used.

       Information on the DBI interface	itself can be gained by	typing:

	 perldoc DBI

       Information on DBD::MariaDB specifically	can be gained by typing:

	 perldoc DBD::MariaDB

       (this will display the document you're currently	reading)

       Please report bugs, including all the information needed	such as
       DBD::MariaDB version, MariaDB/MySQL version, OS type/version, etc to
       this link:


       In past for DBD::mysql, MySQL/Sun/Oracle	responded to bugs and assisted
       in fixing bugs which many thanks	should be given	for their help!	This
       driver is outside the realm of the numerous components they support,
       and the maintainer and community	solely support DBD::mysql and

perl v5.32.0			  2019-02-27		       DBD::MariaDB(3)


Want to link to this manual page? Use this URL:

home | help