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

FreeBSD Manual Pages


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

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

	   use DBI;

	   my $dsn = "DBI:mysql: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:mysql:database=test;host=localhost",
				"joe", "joe's password",
				{'RaiseError' => 1});

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

	 # 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'.	We are using $dbh->quote() for
	 # quoting the name.
	 $dbh->do("INSERT INTO foo VALUES (1, "	. $dbh->quote("Tim") . ")");

	 # same	thing, but using placeholders (recommended!)
	 $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::mysql is the Perl5 Database	Interface driver for the MySQL
       database. In other words: DBD::mysql is an interface between the	Perl
       programming language and	the MySQL programming API that comes with the
       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::mysql, because this is
       what you	will need the most. For	installation, see the separate
       document	DBD::mysql::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 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:

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

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

	 my $query = sprintf("INSERT INTO foo VALUES (%d, %s)",
			     $number, $dbh->quote("name"));

       See DBI for details on the quote	and do methods.	An alternative
       approach	is

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

       in which	case the quote method is executed automatically. 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:

	 $sth =	$dbh->prepare("SELECT *	FROM $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;

	       $dsn = "DBI:mysql:$database";
	       $dsn = "DBI:mysql:database=$database;host=$hostname";
	       $dsn = "DBI:mysql:database=$database;host=$hostname;port=$port";

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

	   The "database" is not a required attribute, but please note that
	   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 mysql	command	line client.
	   Also, 'SELECT DATABASE()' will return the current database active
	   for the handle.

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

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

	       To connect to a MySQL server on localhost using TCP/IP, you
	       must specify the	hostname as (with the	optional

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

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

	       Enables (TRUE value) or disables	(FALSE value) the flag
	       CLIENT_FOUND_ROWS while connecting to the MySQL server. This
	       has a somewhat funny effect: Without mysql_client_found_rows,
	       if you perform a	query like

		 UPDATE	$table SET id =	1 WHERE	id = 1;

	       then the	MySQL engine will always return	0, because no rows
	       have changed.  With mysql_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 engines.)

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

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

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

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

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

	       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 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

		   $dsn	= "DBI:mysql:test;mysql_read_default_file=/home/joe/my.cnf";
		   $dbh	= DBI->connect($dsn, $user, $password)

	       The option mysql_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

		   $dsn	= "DBI:mysql:test;mysql_read_default_group=perl;"
		       . "mysql_read_default_file=/home/joe/my.cnf";
		   $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


	       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 MySQL server	and enforce SSL	encryption.  A false value
	       (which is default) disable SSL encryption with the MySQL

	       When enabling SSL encryption you	should set also	other SSL
	       options,	at least mysql_ssl_ca_file or mysql_ssl_ca_path.

		 mysql_ssl=1 mysql_ssl_verify_server_cert=1 mysql_ssl_ca_file=/path/to/ca_cert.pem

	       This means that your communication with the server will be

	       Please note that	this can only work if you enabled SSL when
	       compiling DBD::mysql; this is the default starting version
	       4.034.  See DBD::mysql::INSTALL for more	details.

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

	       When set	MySQL server certificate is checked that it is signed
	       by some CA certificate in the list.  Common Name	value is not
	       verified	unless "mysql_ssl_verify_server_cert" is enabled.

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

	       When set	MySQL server certificate is checked that it is signed
	       by some CA certificate in the list.  Common Name	value is not
	       verified	unless "mysql_ssl_verify_server_cert" is enabled.

	       Please note that	this option is supported only if your 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 "mysql_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 "mysql_ssl" set to true enforce
	       SSL encryption.

	       This option was introduced in 4.043 version of DBD::mysql.  Due
	       to The BACKRONYM	<> and The Riddle
	       <> vulnerabilities in	libmysqlclient
	       library,	enforcement of SSL encryption was not possbile and
	       therefore "mysql_ssl_optional=1"	was effectively	set for	all
	       DBD::mysql versions prior to 4.043.  Starting with 4.043,
	       DBD::mysql with "mysql_ssl=1" could refuse connection to	MySQL
	       server if underlaying libmysqlclient library is vulnerable.
	       Option "mysql_ssl_optional" can be used to make SSL connection

	       Path to the RSA public key of the server. This is used for the
	       sha256_password and caching_sha2_password authentication

	       Setting "mysql_get_server_pubkey" to true requests the public
	       RSA key of the server.

	       The LOCAL capability for	LOAD DATA may be disabled in the MySQL
	       client library by default. If your DSN contains the option
	       "mysql_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.

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

		 $dbh =	DBI->connect(
		   { RaiseError	=> 1, AutoCommit => 1 }


		 $dbh =	DBI->connect(
		   { RaiseError	=> 1, AutoCommit => 1, mysql_server_prepare => 1 }

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

	       To make sure that the 'make test' step tests whether server
	       prepare works, you just need to export the env variable


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

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

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

	       The option <mysql_embedded_options> can be used to pass
	       'command-line' options to embedded server.


		 use DBI;
		 $dbh =	DBI->connect($testdsn,"a","b");

	       This would cause	the command line help to the embedded MySQL
	       server library to be printed.

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



	       The option <mysql_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
	       libmysqlclient and 'program_name' is added by DBD::mysql.

	       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 a MySQL
	       5.6 server and libmysqlclient or	newer to leverage this

		 my $dbh= DBI->connect($dsn, $user, $password,
		   { AutoCommit	=> 0,
		     mysql_conn_attrs => {
		       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 = {
		   'foo' => {
		       'ATTR_VALUE'	  => 'bar',
		       'PROCESSLIST_ID'	  => '3',
		       'ATTR_NAME'	  => 'foo',
		       'ORDINAL_POSITION' => '6'
		   'wiz' => {
		       'ATTR_VALUE'	  => 'bang',
		       'PROCESSLIST_ID'	  => '3',
		       'ATTR_NAME'	  => 'wiz',
		       'ORDINAL_POSITION' => '3'
		   'program_name' => {
		       'ATTR_VALUE'	  => './',
		       'PROCESSLIST_ID'	  => '3',
		       'ATTR_NAME'	  => 'program_name',
		       'ORDINAL_POSITION' => '5'
		   '_client_name' => {
		       'ATTR_VALUE'	  => 'libmysql',
		       'PROCESSLIST_ID'	  => '3',
		       'ATTR_NAME'	  => '_client_name',
		       'ORDINAL_POSITION' => '1'
		   '_client_version' =>	{
		       'ATTR_VALUE'	  => '5.6.24',
		       'PROCESSLIST_ID'	  => '3',
		       'ATTR_NAME'	  => '_client_version',
		       'ORDINAL_POSITION' => '7'
		   '_os' => {
		       'ATTR_VALUE'	  => 'osx10.8',
		       'PROCESSLIST_ID'	  => '3',
		       'ATTR_NAME'	  => '_os',
		       'ORDINAL_POSITION' => '0'
		   '_pid' => {
		       'ATTR_VALUE'	  => '59860',
		       'PROCESSLIST_ID'	  => '3',
		       'ATTR_NAME'	  => '_pid',
		       'ORDINAL_POSITION' => '2'
		   '_platform' => {
		       'ATTR_VALUE'	  => 'x86_64',
		       'PROCESSLIST_ID'	  => '3',
		       'ATTR_NAME'	  => '_platform',
		       'ORDINAL_POSITION' => '4'

   Private MetaData Methods
	       my $drh = DBI->install_driver("mysql");
	       @dbs = $drh->func("$hostname:$port", '_ListDBs');
	       @dbs = $drh->func($hostname, $port, '_ListDBs');
	       @dbs = $dbh->func('_ListDBs');

	   Returns a list of all databases managed by the MySQL	server running
	   on $hostname, port $port. This is a legacy method.  Instead,	you
	   should use the portable method

	       @dbs = DBI->data_sources("mysql");

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

	 $errno	= $dbh->{'mysql_errno'};
	 $error	= $dbh->{'mysql_error'};
	 $info = $dbh->{'mysql_hostinfo'};
	 $info = $dbh->{'mysql_info'};
	 $insertid = $dbh->{'mysql_insertid'};
	 $info = $dbh->{'mysql_protoinfo'};
	 $info = $dbh->{'mysql_serverinfo'};
	 $info = $dbh->{'mysql_stat'};
	 $threadId = $dbh->{'mysql_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() and
       mysql_thread_id(), respectively.

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

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


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


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


	   $info_hashref = $dbh->{mysql_dbd_stats};

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

		 The number of times that DBD::mysql successfully reconnected
		 to the	mysql server.

		 The number of times that DBD::mysql tried to reconnect	to
		 mysql but failed.

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

	   This	attribute determines whether DBD::mysql	will automatically
	   reconnect to	mysql if the connection	be lost. This feature defaults
	   to off; however, if either the GATEWAY_INTERFACE or MOD_PERL
	   environment variable	is set,	DBD::mysql will	turn
	   mysql_auto_reconnect	on.  Setting mysql_auto_reconnect to on	is not
	   advised if 'lock tables' is used because if DBD::mysql reconnect to
	   mysql all table locks will be lost.	This attribute is ignored when
	   AutoCommit is turned	off, and when AutoCommit is turned off,
	   DBD::mysql will not automatically reconnect to the server.

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

	     $dbh->{mysql_auto_reconnect} = 1;


	     my	$dbh = DBI->connect($dsn, $user, $password, {
		mysql_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.  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 "mysql_use_result"
	   attribute for the $dbh via the DSN:

	     $dbh = DBI->connect("DBI:mysql:test;mysql_use_result=1", "root", "");

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

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

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

	   This	attribute determines whether DBD::mysql	should assume strings
	   stored in the database are utf8.  This feature defaults to off.

	   When	set, a data retrieved from a textual column type (char,
	   varchar, etc) will have the UTF-8 flag turned on if necessary.
	   This	enables	character semantics on that string.  You will also
	   need	to ensure that your database / table / column is configured to
	   use UTF8. See for more information the chapter on character set
	   support in the MySQL	manual:

	   Additionally, turning on this flag tells MySQL that incoming	data
	   should be treated as	UTF-8.	This will only take effect if used as
	   part	of the call to connect().  If you turn the flag	on after
	   connecting, you will	need to	issue the command "SET NAMES utf8" to
	   get the same	effect.

	   This	is similar to mysql_enable_utf8, but is	capable	of handling
	   4-byte UTF-8	characters.

	   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. This
	   was previously called "unsafe_bind_type_guessing" because it	is
	   experimental. I have	successfully run the full test suite with this
	   option turned on, the name can now be simply

	   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:

	   "mysql_bind_type_guessing" can be turned on via

	    - through DSN

	     my	$dbh= DBI->connect('DBI:mysql:test', 'username', 'pass',
	     { mysql_bind_type_guessing	=> 1})

	     - OR after	handle creation

	     $dbh->{mysql_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, so I have made it available	in 4.015

	   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:

	   "mysql_no_autocommit_cmd" can be turned on when creating the
	   database handle:

	     my	$dbh = DBI->connect('DBI:mysql:test', 'username', 'pass',
	     { mysql_no_autocommit_cmd => 1});

	   or using an existing	database handle:

	     $dbh->{mysql_no_autocommit_cmd} = 1;

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

	     $rc = $dbh->ping();

       The statement handles of	DBD::mysql 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 "mysql_use_result" attribute, which forces the driver to use
       mysql_use_result	rather than mysql_store_result.	The former is faster
       and less	memory consuming, but tends to block other processes. (That's
       why mysql_store_result is the default.)

       To set the "mysql_use_result" attribute,	use either of the following:

	 my $sth = $dbh->prepare("QUERY", { mysql_use_result =>	1});


	 my $sth = $dbh->prepare($sql);
	 $sth->{mysql_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	$table") ||
	   die "Error:"	. $dbh->errstr . "\n";

	 $sth->execute ||  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::mysql. 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 max_length attribute.

	   Returns GTID(s) if GTID session tracking is ensabled	in the server
	   via session_track_gtids.

	   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->{mysql_insertid}. The
	   value can also be accessed via $dbh->{mysql_insertid} but this can
	   easily produce incorrect results in case one	database handle	is

	   Reference to	an array of boolean values; TRUE indicates, that the
	   respective column is	a blob.	This attribute is valid	for MySQL

	   Reference to	an array of boolean values; TRUE indicates, that the
	   respective column is	a key. This is valid for MySQL only.

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

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

	   Reference to	an array of boolean values; TRUE indicates that	the
	   respective column is	an AUTO_INCREMENT column.  This	is only	valid
	   for MySQL.

	   A reference to an array of maximum column sizes. The	max_length is
	   the maximum physically present in the result	table, length gives
	   the theoretically possible maximum. max_length is valid for MySQL

	   A reference to an array of column names.

	   A reference to an array of boolean values; TRUE indicates that this
	   column may contain NULL's.

	   Number of fields returned by	a SELECT or LISTFIELDS 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, for	example
	   DBD::mysql::FIELD_TYPE_INTERVAL is mapped to	DBI::SQL_VARCHAR().
	   If you need the native column types,	use mysql_type.	See below.

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

	   Similar to mysql, 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.

       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	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::mysql 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::mysql can be	toggled	by using the
	   mysql_auto_reconnect	attribute. This	behaviour should be turned off
	   in code that	uses LOCK TABLE	because	if the database	server time
	   out and DBD::mysql reconnect, table locks will be lost without any
	   indication of such loss.

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

       The basic usage of multiple result sets is

	   while (@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;
	 end") or print	$DBI::errstr;

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

	 $sth->execute || die DBI::err.": ".$DBI::errstr; $rowset=0;
	 do {
	   print "\nRowset ".++$i."\n---------------------------------------\n\n";
	   foreach $colno (0..$sth->{NUM_OF_FIELDS}-1) {
	     print $sth->{NAME}->[$colno]."\t";
	   print "\n";
	   while (@row=	$sth->fetchrow_array())	 {
	     foreach $field (0..$#row) {
	       print $row[$field]."\t";
	     print "\n";
	 } until (!$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::mysql 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::mysql 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.

       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 'async' attribute to a true value in the
       "do" in DBI method, or in the "prepare" in DBI method.  Statements
       created with 'async' set	to true	in prepare always run their queries
       asynchronously when "execute" in	DBI is called.	The driver also	offers
       three additional	methods: "mysql_async_result", "mysql_async_ready",
       and "mysql_fd".	"mysql_async_result" returns what do or	execute	would
       have; that is, the number of rows affected.  "mysql_async_ready"
       returns true if "mysql_async_result" will not block, and	zero
       otherwise.  They	both return "undef" if that handle was not created
       with 'async' set	to true	or if an asynchronous query was	not started
       yet.  "mysql_fd"	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)', {	async => 1 });
	 until($dbh->mysql_async_ready)	{
	   say 'not ready yet!';
	   sleep 1;
	 my $rows = $dbh->mysql_async_result;

       See DBD::mysql::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).

       For the past nine years DBD::mysql has been maintained by Patrick
       Galbraith (, and recently with the	great help of Michiel
       Beijen (,  along with the entire community of
       Perl developers who keep	sending	patches	to help	continue improving

       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::mysql have come from the

       This module is

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

       o   Large Portions Copyright (c)	2004-2006 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
       <> 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::mysql specifically can be gained by typing:

	   perldoc DBD::mysql

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

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


       Note: until recently, 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

perl v5.32.0			  2019-01-09			 DBD::mysql(3)


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

home | help