net::mysql

[Lee]

I've come across a bug or just one of my own mistakes in the net::mysql
package. After querying, the package keeps the query. When another
query is entered, it is silently rejected and instead the first query
is executed. For instance,

use Net::MySQL;
my $query = "update $table set id=LAST_INSERT_ID(id+1)";
$mysql->query($query);
die $mysql->get_error_message if $mysql->is_error;

$query="SELECT * FROM $table";
$mysql->query($query);
die $mysql->get_error_message if $mysql->is_error;

This code will excecute the update query twice and the select query
none. Has anyone come across something like this? This example is
from a script I've been building that is about 700 lines long and so I
didn't post it all, but if anyone would want to help me, I'd provide
anything.

Any help?

 

[Mark Clements]

I've come across a bug or just one of my own mistakes in the net::mysql
package. After querying, the package keeps the query. When another
query is entered, it is silently rejected and instead the first query
is executed. For instance,

use Net::MySQL;
my $query = "update $table set id=LAST_INSERT_ID(id+1)";
$mysql->query($query);
die $mysql->get_error_message if $mysql->is_error;

$query="SELECT * FROM $table";
$mysql->query($query);
die $mysql->get_error_message if $mysql->is_error;

This code will excecute the update query twice and the select query
none. Has anyone come across something like this? This example is

Are you running under mod_perl? It is possible that you have a closure
issue. Do you have warnings enabled? If you did, you would receive the
warning

Variable "%s" will not stay shared

from a script I've been building that is about 700 lines long and so I
didn't post it all, but if anyone would want to help me, I'd provide
anything.

A single script of 700 lines? It may benefit from a redesign.

 

[Lee]

I'm using activeperl. How would I resolve a closure issue, if I did
have warnings?

Using a slightly different code and using warnings in the subroutine, I
get the output below (it's called twice before an error)

sub crmid{
use warnings;

my ($table) = @_;

die $mysql->get_error_message if $mysql->is_error;
my $query = "update $table set id=LAST_INSERT_ID(id+1)";
$mysql->query($query);
print "update: " . $mysql->get_error_message . "\n";
#since crmentity_seq only has one row, one col, it's ok to just
select it and get a value
#$query = "SELECT LAST_INSERT_ID() FROM $table";
$query="SELECT * FROM $table";
$mysql->query($query);
print "select: " . $mysql->get_error_message . "\n";
my $record_set=$mysql->create_record_iterator;
print "select2: " . $mysql->get_error_message . "\n";
my $result=$record_set->each;
return $result->[0];
}

output:

update: 4¸(Rows matched: 1 Changed: 1 Warnings: 0
select:
select2:
update:
select: 5¸(Rows matched: 1 Changed: 1 Warnings: 0
select2: 5¸(Rows matched: 1 Changed: 1 Warnings: 0

 

[Lee]

Thanks so much for helping!
(by the way, probably 600 lines are subroutines, and I probably will
put them in a package later)

 

[Lee]

oh and the error message is

Can't call method "each" on an undefined value

 

[Mark Clements]

I'm using activeperl. How would I resolve a closure issue, if I did
have warnings?

I was on the wrong track: sorry for misleading you. If it was the issue,
you would get the error message

Variable "%s" will not stay shared

with warnings enabled.

Using a slightly different code and using warnings in the subroutine, I
get the output below (it's called twice before an error)

sub crmid{
use warnings;

my ($table) = @_;

die $mysql->get_error_message if $mysql->is_error;
my $query = "update $table set id=LAST_INSERT_ID(id+1)";
$mysql->query($query);
print "update: " . $mysql->get_error_message . "\n";
#since crmentity_seq only has one row, one col, it's ok to just
select it and get a value
#$query = "SELECT LAST_INSERT_ID() FROM $table";
$query="SELECT * FROM $table";
$mysql->query($query);
print "select: " . $mysql->get_error_message . "\n";
my $record_set=$mysql->create_record_iterator;
print "select2: " . $mysql->get_error_message . "\n";
my $result=$record_set->each;
return $result->[0];
}

I'm not familiar with this module (have just read the docs), but here
are a few thoughts:

you need to run with

use strict;
use warnings;

to catch a whole host of potential errors for you. Put them at the top
of your script, module, or whatever.

From the doc:

$mysql->query(q{
SELECT id, message FROM foo
});
if ($mysql->has_selected_record) {
my $a_record_iterator = $mysql->create_record_iterator;
# ...
}

You aren't checking that there are any results to return before executing:

my $record_set=$mysql->create_record_iterator;

Follow the example above.

I'd guess $record_set is undefined (because there is no data).
This is why you receive the error:

oh and the error message is

Can't call method "each" on an undefined value

though perl should tell you exactly where this error is occurring.

Do you get any data when you run

SELECT * FROM $table

(where $table is whatever argument you are calling your subrouting crmid
with) from the mysql prompt?



Mark

 

[Lee]

I was never able to solve the problem really, but closing and reopening
the connection worked for whatever reason. Problem treated.