view release on metacpan or  search on metacpan

lib/DBD/mysql.pm  view on Meta::CPAN

=item auto_reconnects_failed

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

=back

=back

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

=over

=item mysql_auto_reconnect

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 C<mysql_auto_reconnect>
attribute for the $dbh by passing it in the C<\%attr> hash for C<DBI->connect>.

  $dbh->{mysql_auto_reconnect} = 1;

or

  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 L<DBIx::Connector> in fixup mode), this value must be set
to 0.

=item mysql_use_result

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 C<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 C<mysql_use_result> setting on your statement
handle, when creating the statement handle or after it has been created.
See L</"STATEMENT HANDLES">.

=item mysql_enable_utf8

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:
L<http://dev.mysql.com/doc/refman/8.0/en/charset.html>

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 C<SET NAMES utf8> to get the same effect.

This flag's implementation suffers the "Unicode Bug" on passed statements and
input bind parameters, and cannot be fixed for historical reasons. In order to
pass strings with Unicode characters consistently through DBD::mysql, you can
use a "hack" workaround of calling the C<utf8::upgrade()> function on scalars
immediately before passing them to DBD::mysql.  Calling the C<utf8::upgrade()>
function has absolutely no effect on (correctly written) Perl code, but forces
DBD::mysql to interpret it correctly as text data to be encoded.  In the same
way, binary (byte) data can be passed through DBD::mysql without being encoded
as text data by calling the C<utf8::downgrade()> function (it dies on wide
Unicode strings with codepoints above U+FF).  See the following example:

  # check that last name contains LATIN CAPITAL LETTER O WITH STROKE (U+D8)
  my $statement = "SELECT * FROM users WHERE last_name LIKE '%\x{D8}%' AND first_name = ? AND data = ?";

  my $wide_string_param = "Andr\x{E9}"; # Andre with LATIN SMALL LETTER E WITH ACUTE (U+E9)

  my $byte_param = "\x{D8}\x{A0}\x{39}\x{F8}"; # some bytes (binary data)

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

  utf8::upgrade($statement); # UTF-8 fix for DBD::mysql
  my $sth = $dbh->prepare($statement);

  utf8::upgrade($wide_string_param); # UTF-8 fix for DBD::mysql
  $sth->bind_param(1, $wide_string_param);

  utf8::downgrade($byte_param); # byte fix for DBD::mysql
  $sth->bind_param(2, $byte_param, DBI::SQL_BINARY); # set correct binary type

  $sth->execute();

  my $output = $sth->fetchall_arrayref();
  # returned data in $output reference should be already UTF-8 decoded as appropriate

=item mysql_enable_utf8mb4

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

=item mysql_bind_type_guessing

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
C<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 C<mysql_bind_type_guessing>.

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

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

    mysql> 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: https://rt.cpan.org/Ticket/Display.html?id=43822

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