troubleshooting Question

An UPDATE gives warnings, but mysql_warning_count is empty.

Avatar of StevenMiles
StevenMiles asked on
MySQL Server
2 Comments1 Solution478 ViewsLast Modified:
Hi,
I'm trying to see mysql warnings as they happen,with mysql being called from perl programs.

From this URL:
http://use.perl.org/~Smylers/journal/34246
I found the following code:
-----begin quote from use.perl.org-----------
use Lingua::EN::Inflect qw<inflect>;
my $insert = $db->prepare(q[INSERT INTO game SET shape = 'rock']);
$insert->execute;
if ($insert->{mysql_warning_count})
{
  warn inflect "NUM($insert->{mysql_warning_count}) PL_N(warning):\n";
  my $warning_query = $db->prepare(q[SHOW WARNINGS]);
  $warning_query->execute;
  while (my $warning = $warning_query->fetchrow_hashref)
  {
    warn "$warning->{Message}\n";
  }
}
which yields this output:
1 warning:
Data truncated for column 'shape' at row 1
------end quote from use.perl.org------------

So I made a couple of slight modifications, and came up with this for my program (the field "Cancel_date" is a date field):

$sth = $dbh->prepare ("UPDATE my_users SET Cancel_date='lskdifjlkijef' WHERE id_number=33");
$numUpdated = $sth->execute;
if ($sth->{mysql_warning_count})
{
  my $warning_query = $dbh->prepare(q[SHOW WARNINGS]);
  $warning_query->execute;
  while (my $warning = $warning_query->fetchrow_hashref)
       {print STDERR "$warning->{Message}\n"}
}
else
{print STDERR "no warnings\n"}

I expected to see the warning "Data truncated for column 'Cancel_date' at row 1" which is the warning that I see if I run the poorly-formed update statement from a telnet mysql command line.
But I get "no warnings" instead.

I finally tracked it down to the initial "if" statement.  $sth->{mysql_warning_count} is empty in this line:
if ($sth->{mysql_warning_count})
... so I don't get the warnings printed to STDERR.  If I leave out that opening "if" statement and instead just run this code:
$sth = $dbh->prepare ("UPDATE my_users SET Cancel_date='lskdifjlkijef' WHERE id_number=33");
$numUpdated = $sth->execute;
  my $warning_query = $dbh->prepare(q[SHOW WARNINGS]);
  $warning_query->execute;
  while (my $warning = $warning_query->fetchrow_hashref)
       {print STDERR "$warning->{Message}\n"}

... then the expected warning gets printed to STDERR just fine.

I'd like to have one "if" statement that will tell me whether there are any warnings, so if there are, I can process them in the block below the "if" statement, and if there are no warnings, I don't have to do the processing in the block.  Seems like mysql_warning_count is tailor made for this, but it's not working for me.

What am I doing wrong here -- why can't I get mysql_warning_count to work?
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 2 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros