Avatar of StevenMiles
StevenMiles asked on

An UPDATE gives warnings, but mysql_warning_count is empty.

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?
MySQL Server

Avatar of undefined
Last Comment
StevenMiles

8/22/2022 - Mon
SOLUTION
Guy Hengel [angelIII / a3]

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER CERTIFIED SOLUTION
StevenMiles

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy