[gentoo-dev] DBD-mysql select failures with MySQL 4.1

[Anthony Gorecki]

Hello,

I've been encountering an unusual string of errors while attempting to 
retrieve information from a database using DBD-mysql-2.9007 and mysql-4.1.12. 
The database in question is of type InnoDB. Below is an example:

#!/usr/bin/perl -w

use strict;
use DBI;

my $db_conn = DBI->connect('DBI:mysql:infrastructure', 'system', 'readonly');
if (!$db_conn) {
    error('The program was unable to connect to the database.');
}

$_ = $db_conn->selectrow_array("
    SELECT \@user_id := (
        SELECT user_id FROM users WHERE username = 'agorecki' LIMIT 1)
    );
");

if (!defined($_)) {
    $db_conn->disconnect();
    error('It appears that the user account has disappeared since login '.
        'time...'
    );
}


The same query in MySQL will return the session variable, however DBD-mysql 
returns undefined. Similarly, when I run:

SELECT type
FROM svn_access
WHERE 'test' REGEXP REPLACE(path_regex, '\$USERNAME\$', 'agorecki') = 1
    AND (user_id = @user_id
        OR group_id = ANY (SELECT group_id FROM group_memberships WHERE
            user_id = @user_id)
        OR (user_id IS NULL AND group_id IS NULL)
    )
ORDER BY type DESC LIMIT 1;


through selectrow_array (properly encoded for use in a PERL script), I also 
receive an undefined result. The above query correctly returns one row when 
input into a MySQL console and executed.

The only thing that actually managed to execute and return a value 
successfully was:
$_ = $db_conn->selectrow_array("SELECT 'hello';");

Any thoughts?


-- 
Anthony Gorecki
Ectro-Linux Foundation