Skip Menu |
 

This queue is for tickets about the DBD-ODBC CPAN distribution.

Report information
The Basics
Id: 19773
Status: rejected
Priority: 0/
Queue: DBD-ODBC

People
Owner: Nobody in particular
Requestors: david.brewer [...] gmail.com
Cc:
AdminCc:

Bug Information
Severity: Important
Broken in: 1.13
Fixed in: (no value)



Subject: Memory leak when using table variables in MSSQL via DBD-ODBC
Download (untitled) / with headers
text/plain 1.5k
I have identified what looks like a substantial memory leak that occurs under a somewhat complicated set of circumstances. I am guessing the leak occurs in DBD::ODBC but don't know for sure. I was unable to reproduce it using DBD::ADO as an alternate driver. I have attached a test script for reproducing the leak. The details of the leak and the usage of the script are documented in the script, but I will also also describe the behavior in detail here. Overview: If you perform a particular type of query using MSSQL via DBI and DBD::ODBC, a substantial amount of memory seems to get leaked on each repetition of the query. The amount of memory leaked varies with the number of long text columns that you are selecting. Details: In order to reproduce the leak, you must meet four criteria: 1) In your query, you must create a table variable. 2) You must insert at least one row into the table variable. After that, you can completely ignore the table variable! 3) You must select from a table that contains 'text' columns. The more text columns you select, the larger the leak. Note that you don't actually have to select any rows to cause the leak. 4) After executing the query, you must do something which triggers the lookup of column information. I've found that either $sth->{NAME} or $sth->fetchrow_hashref() will cause the leak, for instance. In addition, the 'LongReadLen' property of the database handle is somehow related because the higher this number, the greater the leak. Modules involved: DBI 1.50, DBD::ODBC 1.13 Perl version: ActiveState ActivePerl 5.8.7 Database: MSSQL 2000 SP3. OS: Windows XP SP2.
Subject: mssql_leak.t
Download mssql_leak.t
text/x-perl 5.5k
############################################################################ # mssql_leak.t # # Script for demonstrating substantial memory leak with DBI, DBD::ODBC, and # a MSSQL 2000 database. # # To use this script you must have DBI 1.50, DBD::ODBC 1.13, and owner # access to an MSSQL database. A table called 'leak_test' will be created # and destroyed in the database. See the CONFIGURATION section below to set # up the desired connection. # # In order to reproduce the leak, you must meet four criteria: # # 1) In your query, you must create a table variable. # 2) You must insert at least one row into the table variable. After that, # you can completely ignore the table variable! # 3) You must select from a table that contains 'text' columns. The more # text columns you select, the larger the leak. Note that you don't # actually have to select any rows to cause the leak! # 4) After executing the query, you must do something which triggers the # lookup of column information. I've found that either $sth->{NAME} or # $sth->fetchrow_hashref() will cause the leak, for instance. # # In addition, the 'LongReadLen' property of the database handle is somehow # related because the higher this number, the greater the leak. # # Once you've verified that the leak is occuring using the code below, you # can try commenting out various lines to see how it affects things. # The two clearest examples are commenting out the insertion into the # table variable, and commenting out the $sth->{NAME} line. # # I wasn't able to figure out how to get at the memory usage of perl # programmatically on Windows, so rather than writing tests to test the # memory usage, I just made a loop that demonstrates the leak. It pauses # after the first iteration and then again at the end so you can observe the # memory used by perl using the task manager or 'Process Explorer' from # sysinternals.com. # # Author: David Brewer, Second Story Interactive # Date: June 7, 2006 ############################################################################ use strict; use warnings; ############################################################################ # CONFIGURATION ############################################################################ # Database connection my $dsn = "DBI:ODBC:driver={SQL Server};server=localhost;database=test;"; my $user = "test"; my $pass = "test"; # number of times to perform the leaky query in the loop my $leak_iterations = 1200; # seconds to pause at beginning and end of loop to permit memory observation my $pause = 7; # LongReadLen value to use on the database handle; this is somehow related # to the leak because the greater this value, the greater the leak. my $LongReadLen = 20000; ############################################################################ # SETUP ############################################################################ use Test::More tests => 8; # Verify we have the correct versions of the modules BEGIN { use_ok( 'DBI 1.50' ); use_ok( 'DBD::ODBC 1.13' ); } # Prepare the database by creating the test table. Drop it first if it # already exists. my $create_sql = q{ IF OBJECT_ID('leak_test','U')IS NOT NULL DROP TABLE leak_test; CREATE TABLE [dbo].[leak_test] ( ID int NOT NULL, LongText1 text, LongText2 text, LongText3 text, LongText4 text, LongText5 text, PRIMARY KEY (ID) ) }; my $dbh = DBI->connect($dsn, $user, $pass); isa_ok($dbh, 'DBI::db'); ok($dbh->do($create_sql), 'Created leak_test table'); ok($dbh->disconnect, 'Disconnected from database'); ############################################################################ # THE TEST ############################################################################ my $leaky_sql = q{ DECLARE @table_variable TABLE ( TestInteger int DEFAULT(0) ); -- Commenting the line below prevents the leak! INSERT INTO @table_variable (TestInteger) VALUES (1); SELECT TOP 0 * FROM leak_test; }; my $iterations = 1200; print(qq{ We will run the leaky query $leak_iterations times, pausing $pause seconds after the first run so you can observe the memory usage. }); $dbh = DBI->connect($dsn, $user, $pass); $dbh->{LongReadLen} = $LongReadLen; for my $i (1..$leak_iterations) { # print a dot for each run through the query print "\n" if ($i % 60 == 1); print "."; # run our leaky query... my $sth = $dbh->prepare($leaky_sql); $sth->execute(); # Getting column names triggers link; you can comment out to verify. my $names = $sth->{NAME}; # clean up $sth->finish; undef $sth; # pause after the first run so you can observe the memory usage sleep($pause) if ($i == 1); } $dbh->disconnect(); undef $dbh; print(qq{ \nPausing $pause seconds so you can observe final memory usage. }); sleep($pause); ############################################################################ # CLEANUP ############################################################################ # Remove the leak_test table my $cleanup_sql = q{ IF OBJECT_ID('leak_test','U') IS NOT NULL DROP TABLE leak_test; }; $dbh = DBI->connect($dsn, $user, $pass); isa_ok($dbh, 'DBI::db'); ok($dbh->do($cleanup_sql), 'Removed leak_test table'); ok($dbh->disconnect, 'Disconnected from database');
Download (untitled) / with headers
text/plain 230b
I have not run this code until today as I did not maintain DBD::ODBC when it was reported. With DBI 1.607 and DBD::ODBC 1.17_2 and the Easysoft SQL Server ODBC Driver I cannot see any leak. Martin -- Martin J. Evans Wetherby, UK
Download (untitled) / with headers
text/plain 401b
On Tue Nov 18 09:19:05 2008, MJEVANS wrote: Show quoted text
> I have not run this code until today as I did not maintain DBD::ODBC > when it was reported. With DBI 1.607 and DBD::ODBC 1.17_2 and the > Easysoft SQL Server ODBC Driver I cannot see any leak. > > Martin
As I've heard nothing on this rt for over a year I am going to close it. If you object to this let me know. Martin -- Martin J. Evans Wetherby, UK


This service is sponsored and maintained by Best Practical Solutions and runs on Perl.org infrastructure.

Please report any issues with rt.cpan.org to rt-cpan-admin@bestpractical.com.