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

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

[Anthony Gorecki]

[-- Attachment #1: Type: text/plain, Size: 1613 bytes --]

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

[-- Attachment #2: Type: application/pgp-signature, Size: 828 bytes --]

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

[Zac Medico]

Anthony Gorecki wrote:
> 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?
> 
> 

Have you checked the DBI error status?  See the parts about $DBI::err, $DBI::errstr, and RaiseError here:

http://search.cpan.org/~timb/DBI-1.48/DBI.pm

Zac
-- 
gentoo-dev@gentoo.org mailing list

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

[Anthony Gorecki]

[-- Attachment #1: Type: text/plain, Size: 308 bytes --]

On Sunday, June 26, 2005 9:28 pm, Zac Medico wrote:
> Have you checked the DBI error status?  See the parts about $DBI::err,
> $DBI::errstr, and RaiseError here:

In all cases, DBI::err() returns undefined, indicating no error. It's quite 
perplexing.


-- 
Anthony Gorecki
Ectro-Linux Foundation

[-- Attachment #2: Type: application/pgp-signature, Size: 828 bytes --]

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

[Francesco R]

Anthony Gorecki wrote:

>On Sunday, June 26, 2005 9:28 pm, Zac Medico wrote:
>  
>
>>Have you checked the DBI error status?  See the parts about $DBI::err,
>>$DBI::errstr, and RaiseError here:
>>    
>>
>
>In all cases, DBI::err() returns undefined, indicating no error. It's quite 
>perplexing.
>
>
>  
>
Maybe we are a bit off topic on this list ... but here we are.
Going to give some hints where to search the source of the error,
nothing else, sorry.
a) take a look at mysql logs.
b) re-emerge  =DBD-mysql-2.9007
c) try something like "log = /tmp/mysqld.sql" in "[mysqld_safe]" section
of my.cnf then restart mysql.


-- 
gentoo-dev@gentoo.org mailing list

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

[Anthony Gorecki]

[-- Attachment #1: Type: text/plain, Size: 585 bytes --]

On Sunday, June 26, 2005 11:24 pm, Francesco R wrote:
> Going to give some hints where to search the source of the error,

Thanks for the suggestions; unfortunately, I've already remerged DBI, 
DBD-mysql and PERL. In addition, I've also tried restarting MySQL and spent 
at least three hours delving through reams of Google search results to no 
avail.

If I ultimately can't find the source of the problem, I may just rewrite the 
system to use flat files instead of a database. My apologies for being 
somewhat off-topic.


-- 
Anthony Gorecki
Ectro-Linux Foundation

[-- Attachment #2: Type: application/pgp-signature, Size: 828 bytes --]

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

[Michael Cummings]

[-- Attachment #1: Type: text/plain, Size: 2259 bytes --]

Take this to gentoo-perl@gentoo.org (I'm just getting back from vacation, real 
responses delayed until we finish getting everything back in order at the 
house). First recommendation is of course, don't try to assign something to 
$_ directly, but maybe that's just my personal take (I'd have used another 
var, any other var).

On Sunday 26 June 2005 23:55, Anthony Gorecki wrote:
> 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?

-- 
-----o()o---------------------------------------------
Michael Cummings   |    #gentoo-dev, #gentoo-perl
Gentoo Perl Dev    |    on irc.freenode.net 
-----o()o---------------------------------------------

[-- Attachment #2: Type: application/pgp-signature, Size: 189 bytes --]