MySQL Bugs: #19453: mysql_server_prepare=1 + fetchall

Bug #19453	mysql_server_prepare=1 + fetchall_arrayref() yields "Out of memory"
Submitted:	1 May 2006 3:37	Modified:	5 Apr 2012 8:07
Reporter:	Wayne Walker	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connectors: DBD::mysql ( Perl )	Severity:	S3 (Non-critical)
Version:	3.0002, 4.013	OS:	Linux (linux)
Assigned to:		CPU Architecture:	Any

Description:
If the table queried has a longtext column, fetchall_arrayref() dies with out of memory on that column.  

It does not die if mysql_server_prepare is not set to 1.
It does not die if the column is just text instead of longtext.

How to repeat:

testit.pl:

#!/usr/bin/perl

use strict;
use DBI;
DBI->trace(99);

my $dbh = DBI->connect( "DBI:mysql:database=test;host=localhost:mysql_server_prepare=1", "root", "", { RaiseError => 0, AutoCommit => 0});

my $sth = $dbh->prepare("select * from test_table where id = ?");
my $rc = $sth->execute(33);
my $results = $sth->fetchall_hashref("id");

exit;

Now...
create table test_table (id integer, foo longtext);

perl testit.pl dies with:
....
** dbd_describe() **
** dbd_describe() num_fields 2**
col 0
col type 3
col len0
col buf_len11
col 1
col type 252
col len0
col buf_len-1
Out of memory!

create table test_table (id integer, foo text);
perl testit.pl now does not die

Thank you for a problem report. What exact MySQL server version are you working with?

That was against my production server MySQL-server-standard-4.1.18-0.rhel4, but was reproducible on my notebook, mysql-server-5.0.18-2.1 (fc5).

The problem is that mysql_server_prepare seems to make DBD::mysql blindly malloc sizeof(column) and a longtext goes to 4GB.  that means it can not run on any 32 bit linux OS (as no process can be 4 GB in size).

Looks very similar to bug #19008 (those is for PHP, but LONGTEXT is also there). Please, check. Can be a general C API-related problem...

As you can see, it reports the col buflen to be -1.  2^32-1 (4GB) when treated as a signed int would print -1.  my process size jumps by 4 GB on my 64bit machine and dies w/out of memory on my 32 bit machine.  the "bug" is probably that fetchall_arrayref is preallocating space for all columns.  That makes sense but is unworkable if someone has a largeblob or longtext.

It sounds like the 19008 bug may be the same problem, at least Thomas Madej's entry.

Please, try to repeat with a newer version of DBD::mysql, 3.0004, and inform about the results.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

I tested this on with the following DBD::mysql version:
$VERSION = '3.0006_1';
on the following system:
SunOS paminstall 5.9 Generic_118558-11 sun4u sparc SUNW,Ultra-60
and the problem still exists on MySQL version
5.0.18

Anyone still working on this?

I'm currently looking into this issue.

Server side prepare statements have been turned off by default now for some time due to issues in the prepared statement client API. The problem here is in the client API. The "fix" to this problem is to not use server side prepare.

Any news on this bug? I just wasted a few hours because of this bug on a Centos 6 machine.

Is this problem still repeatable with a recent version, 4.020?

I don't know if this is solved in 4.020, but under the latest version of Centos 6, the packaged version available is 4.013 and the bug is there.

I tried to upgrade it to version 4.020 but I could not do it due to an error while compiling:

t/00base.t .................. 1/6 
#   Failed test 'use DBD::mysql;'
#   at t/00base.t line 21.
#     Tried to use 'DBD::mysql'.
#     Error:  Can't load '/root/.cpan/build/DBD-mysql-4.020-OJk7QN/blib/arch/auto/DBD/mysql/mysql.so' for module DBD::mysql: /root/.cpan/build/DBD-mysql-4.020-OJk7QN/blib/arch/auto/DBD/mysql/mysql.so: undefined symbol: clock_gettime at /usr/lib64/perl5/DynaLoader.pm line 200.
#  at (eval 7) line 2
# Compilation failed in require at (eval 7) line 2.
# BEGIN failed--compilation aborted at (eval 7) line 2.
Bailout called.  Further testing stopped:  Unable to load DBD::mysql
FAILED--Further testing stopped: Unable to load DBD::mysql

This is what I see with 4.020:

[openxs@chief 5.5]$ perl bug19453.pl
    DBI 1.616-ithread default trace level set to 0x0/3 (pid 2590 pi 214b010) at bug19453.pl line 5
    Note: perl is running without the recommended perl -w option
    -> DBI->connect(DBI:mysql:database=test;host=127.0.0.1;port=3306:mysql_server_prepare=1, root, ****, HASH(0x214ed80))
    -> DBI->install_driver(mysql) for linux perl=5.012004 pid=2590 ruid=500 euid=500
       install_driver: DBD::mysql version 4.020 loaded from /usr/local/lib64/perl5/DBD/mysql.pm
    <- install_driver= DBI::dr=HASH(0x2244878)
    !! warn: 0 CLEARED by call to connect method
    -> connect for DBD::mysql::dr (DBI::dr=HASH(0x2244878)~0x22447d0 'database=test;host=127.0.0.1;port=3306:mysql_server_prepare=1' 'root' **** HASH(0x22acd28)) thr#214b010
imp_dbh->connect: dsn = database=test;host=127.0.0.1;port=3306:mysql_server_prepare=1, uid = root, pwd =
imp_dbh->my_login : dbname = test, uid = root, pwd = NULL,host = 127.0.0.1, port = 3306
imp_dbh->mysql_dr_connect: host = |127.0.0.1|, port = 3306, uid = root, pwd = NULL
imp_dbh->bind_type_guessing: 0
imp_dbh->use_server_side_prepare: 1
imp_dbh->mysql_dr_connect: client_flags = 514
imp_dbh->mysql_dr_connect: <-    <- connect= ( DBI::db=HASH(0x2243c48) ) [1 items] at DBI.pm line 665
    -> STORE for DBD::mysql::db (DBI::db=HASH(0x2243e10)~INNER 'RaiseError' 0) thr#214b010
    STORE DBI::db=HASH(0x2243e10) 'RaiseError' => 0
    <- STORE= ( 1 ) [1 items] at DBI.pm line 717
    -> STORE for DBD::mysql::db (DBI::db=HASH(0x2243e10)~INNER 'PrintError' 1) thr#214b010
    STORE DBI::db=HASH(0x2243e10) 'PrintError' => 1
    <- STORE= ( 1 ) [1 items] at DBI.pm line 717
    -> STORE for DBD::mysql::db (DBI::db=HASH(0x2243e10)~INNER 'AutoCommit' 0) thr#214b010
    <- STORE= ( 1 ) [1 items] at DBI.pm line 717
    -> STORE for DBD::mysql::db (DBI::db=HASH(0x2243e10)~INNER 'Username' 'root') thr#214b010
    STORE DBI::db=HASH(0x2243e10) 'Username' => 'root'
    <- STORE= ( 1 ) [1 items] at DBI.pm line 720 via  at bug19453.pl line 7
    -> connected in DBD::_::db for DBD::mysql::db (DBI::db=HASH(0x2243c48)~0x2243e10 'DBI:mysql:database=test;host=127.0.0.1;port=3306:mysql_server_prepare=1' 'root' '' HASH(0x214ed80)) thr#214b010
    <- connected= ( undef ) [1 items] at DBI.pm line 727
    <- connect= DBI::db=HASH(0x2243c48)
    -> STORE for DBD::mysql::db (DBI::db=HASH(0x2243e10)~INNER 'dbi_connect_closure' CODE(0x2244518)) thr#214b010
    STORE DBI::db=HASH(0x2243e10) 'dbi_connect_closure' => CODE(0x2244518)
    <- STORE= ( 1 ) [1 items] at DBI.pm line 736 via  at bug19453.pl line 7
    -> prepare for DBD::mysql::db (DBI::db=HASH(0x2243c48)~0x2243e10 'select * from test_table where id = ?' HASH(0x216acb8)) thr#214b010
1   <- _async_check= ( 1 ) [1 items] at mysql.pm line 227 via  at bug19453.pl line 10
        -> dbd_st_prepare MYSQL_VERSION_ID 50160, SQL statement: select * from test_table where id = ?
        >- dbd_st_free_result_sets
        <- dbd_st_free_result_sets RC -1
        <- dbd_st_free_result_sets
>count_params statement select * from test_table where id = ?
        <- dbd_st_prepare
    <- prepare= ( DBI::st=HASH(0x22010a0) ) [1 items] at bug19453.pl line 10
    -> execute for DBD::mysql::st (DBI::st=HASH(0x22010a0)~0x2201058 33) thr#214b010
   Called: dbd_bind_ph
 -> dbd_st_execute for 02314310
        >- dbd_st_free_result_sets
        <- dbd_st_free_result_sets RC -1
        <- dbd_st_free_result_sets
mysql_st_internal_execute MYSQL_VERSION_ID 50160
>parse_params statement select * from test_table where id = ?
Binding parameters: select * from test_table where id = '33'
    <- execute= ( '0E0' ) [1 items] at bug19453.pl line 11
    -> fetchall_hashref for DBD::mysql::st (DBI::st=HASH(0x22010a0)~0x2201058 'id') thr#214b010
1   <- mysql_async_ready= ( '' ) [1 items] at mysql.pm line 784 via  at bug19453.pl line 12
1   <- mysql_async_result= ( 1 ) [1 items] at mysql.pm line 784 via  at bug19453.pl line 12
    -> dbd_st_FETCH_attrib for 023763c8, key NAME_hash
    -> dbd_st_FETCH_attrib for 02314310, key NAME
2   <- FETCH= ( [ 'id' 'foo' ] ) [1 items] at DBI.pm line 2055
    .. FETCH DBI::st=HASH(0x2201058) 'NAME_hash' = HASH(0x2288c50) (cached)
1   <- FETCH= ( HASH(0x2288c50)2keys ) [1 items] at DBI.pm line 2055
    -> dbd_st_FETCH_attrib for 023763c8, key NUM_OF_FIELDS
    .. FETCH DBI::st=HASH(0x2201058) 'NUM_OF_FIELDS' = 2 (cached)
1   <- FETCH= ( 2 ) [1 items] at DBI.pm line 2058
    -> dbd_st_FETCH_attrib for 023763c8, key NAME
1   <- FETCH= ( [ 'id' 'foo' ] ) [1 items] at DBI.pm line 2067
2   <- _async_check= ( 1 ) [1 items] at mysql.pm line 797 via  at bug19453.pl line 12
2   <> FETCH= ( 2 ) [1 items] ('NUM_OF_FIELDS' from cache) at DBI.pm line 1874
3   <- _async_check= ( 1 ) [1 items] at mysql.pm line 797 via  at bug19453.pl line 12
2   <- bind_col= ( 1 ) [1 items] at DBI.pm line 1885
3   <- _async_check= ( 1 ) [1 items] at mysql.pm line 797 via  at bug19453.pl line 12
2   <- bind_col= ( 1 ) [1 items] at DBI.pm line 1885
1   <- bind_columns= ( 1 ) [1 items] at DBI.pm line 2069
        -> dbd_st_fetch
                dbd_st_fetch for 023763c8, chopblanks 0
        dbd_st_fetch result set details
        imp_sth->result=02172460
        mysql_num_fields=2
        mysql_num_rows=1
        mysql_affected_rows=1
        dbd_st_fetch for 023763c8, currow= 1
        <- dbd_st_fetch, 2 cols
1   <- fetch= ( [ '33' 'aaaa' ] ) [1 items] row1 at DBI.pm line 2070
        -> dbd_st_fetch
                dbd_st_fetch for 023763c8, chopblanks 0
        dbd_st_fetch result set details
        imp_sth->result=02172460
        mysql_num_fields=2
        mysql_num_rows=1
        mysql_affected_rows=1
        dbd_st_fetch for 023763c8, currow= 2
        dbd_st_fetch, no more rows to fetch
--> dbd_st_finish
        >- dbd_st_free_result_sets
        <- dbd_st_free_result_sets RC -1
        <- dbd_st_free_result_sets

<-- dbd_st_finish
1   <- fetch= ( undef ) [1 items] row1 at DBI.pm line 2073
    <- fetchall_hashref= ( HASH(0x2288b60)1keys ) [1 items] row1 at bug19453.pl line 12
    <> DESTROY(DBI::st=HASH(0x22010a0)) ignored for outer handle (inner DBI::st=HASH(0x2201058) has ref cnt 1)
    -> DESTROY for DBD::mysql::st (DBI::st=HASH(0x2201058)~INNER) thr#214b010
        Freeing 1 parameters, bind 0 fbind 0
    <- DESTROY= ( undef ) [1 items] at bug19453.pl line 14 via  at bug19453.pl line 14
    <> DESTROY(DBI::db=HASH(0x2243c48)) ignored for outer handle (inner DBI::db=HASH(0x2243e10) has ref cnt 1)
    -> DESTROY for DBD::mysql::db (DBI::db=HASH(0x2243e10)~INNER) thr#214b010
Issuing rollback() due to DESTROY without explicit disconnect() of DBD::mysql::db handle database=test;host=127.0.0.1;port=3306:mysql_server_prepare=1 at bug19453.pl line 14.
imp_dbh->pmysql: 2171ad0
    <- DESTROY= ( undef ) [1 items] at bug19453.pl line 14 via  at bug19453.pl line 14
    -- DBI::END ($@: , $!: )
    -> disconnect_all for DBD::mysql::dr (DBI::dr=HASH(0x2244878)~0x22447d0) thr#214b010
    <- disconnect_all= ( ) [0 items] (not implemented) at DBI.pm line 744 via  at bug19453.pl line 14
!   -> DESTROY in DBD::_::common for DBD::mysql::dr (DBI::dr=HASH(0x22447d0)~INNER) thr#214b010
!   <- DESTROY= ( undef ) [1 items] during global destruction
!   <> DESTROY for DBI::dr=HASH(0x2244878) ignored (inner handle gone)

with a bit modified test case:

[openxs@chief 5.5]$ cat bug19453.pl
#!/usr/bin/env perl

use strict;
use DBI;
DBI->trace(99);

my $dbh = DBI->connect( "DBI:mysql:database=test;host=127.0.0.1;port=3306:mysql_server_prepare=1",
"root", "", { RaiseError => 0, AutoCommit => 0});

my $sth = $dbh->prepare("select * from test_table where id = ?", { async => 1 });
my $rc = $sth->execute(33);
my $results = $sth->fetchall_hashref("id");

exit;