saintex has asked for the wisdom of the Perl Monks concerning the following question:

Hello Monks,
I have a problem with a sql query.

The Db is mysql 5.0.22, DBI version is 1.52;

I have simplified my query, in order to debug (not $dbh->prepare, but $dbh->do).

the fields are an enum('Y','N') (it was a tinyint(1) but with same result) and a datetime: 
              
my $update="UPDATE `ml_message` SET `message_send`='N',`message_has_be
+en_sent`='2010-12-06 10:59:20' WHERE `message_id`=3;";

                        
$dbh->do($update)
  or die "problem while updating: $dbh->errstr \n";

[download]
The `message_has_been_sent` has been update, while `message_send` isn't updated.
Why?
Thank you for your help.

UPDATE:

in order to create a debug environement,
I can provide these informations.

I simplified all, in order to debug.

TABLE SCHEMA: 

--

-- Versione MySQL: 5.0.22
-

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--
-- Database: `test`
--

-- --------------------------------------------------------

--
-- Table schema `test_table`
--

CREATE TABLE `test_table` (
  `test_id` int(10) unsigned NOT NULL auto_increment,
  `test_cl_1` tinyint(1) NOT NULL,
  `test_cl_2` datetime default NULL,
  PRIMARY KEY  (`test_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

--
-- Data Dump for table `test_table`
--

INSERT INTO `test_table` (`test_id`, `test_cl_1`, `test_cl_2`) VALUES
(3, 1, '2010-12-07 14:01:28'),
(4, 0, '2010-12-07 14:04:35');

[download]

My test perl script: 
use strict;
use warnings;
use DBI;


use constant DB_HOST=>'localhost';
use constant DB_NAME=>'test';
use constant DB_USR=>'testUser';
use constant DB_PWD=>'';

sub localDate {
    
    my $ora;
    
    unless (@_) {    # se non ci sono parametri
        $ora=time;    # ora e' il timestamp
    } else {    
        
        $ora= shift @_; # altrimenti ora e' il primo parametro
    }

    my ($sec,$min,$hour,$mday,$mon,$year,$wday,
        $yday,$isdst)=localtime(time);

       sprintf "%4d-%02d-%02d %02d:%02d:%02d",
       $year+1900,$mon+1,$mday,$hour,$min,$sec;


}


my    $dbh = DBI->connect( 'DBI:mysql:'.DB_NAME.':'.DB_HOST,DB_USR,DB_
+PWD)
                or die "Connecting : $DBI::errstr\n "; 

my $ora=&localDate;    
print "\n\n";
print 'date: '.$ora;
print "\n\n";
my $update="UPDATE `test_table` SET `test_cl_1`=1,`test_cl_2`='$ora' W
+HERE `test_id`=4;";


print "\n\n";
print $update;
print "\n\n";
                        
$dbh->do($update)
  or die "problem while updating: $dbh->errstr \n";

[download]

If the script runs for the first time I have:

test_cl_1 --> 1
test_cl_2 --> current date


If I try to change the test_cl_1 to 0 value
(trought mysql command line),
and if I re-run the script,
I have:
test_cl_1 --> 0
test_cl_2 --> current date


Finally,
If I try to run the script after a while (at less thirty minutes),
I will have the correct behaviour:
test_cl_1 --> 1
test_cl_2 --> current date


It seems some caching behaviour.

UPDATE: ----------------------------------

The problem was solved, updating DBI to 1.615

Thank you all for your patience.

Replies are listed 'Best First'.
Re: query problem
by Corion (Patriarch) on Dec 07, 2010 at 11:36 UTC

    I would say that the problem likely lies in your database or your data, or in parts of your program that you didn't show.

    Does the SQL statement work when you paste it into the mysql client?

    Does your program connect to the right database?

    You talk about columns message_has_been_sent and message_send in your statement, but your prose mentions columns message_has_been_sent and ml_message. Which is it?

    Why are you sure that message_has_been_sent gets updated? Are you sure it didn't have the same value before you tested?

[reply]
[d/l]
[select]
      I mean exactly what I say:
      The `message_has_been_sent` has been update, while `ml_message` isn't updated.
      In addition:

      Does the SQL statement work when you paste it into the mysql client?

      yes

      Does your program connect to the right database?

      yes

      You talk about columns message_has_been_sent and message_send in your statement, but your prose mentions columns message_has_been_sent and ml_message. Which is it?

      It is a paste and copy error from my side.
      I correct it immediatly.

      Why are you sure that message_has_been_sent gets updated? Are you sure it didn't have the same value before you tested?

      yes.
      because before the value was different.
      I think it is a DBI driver error in the fileds with small values (tinyint, smallint, mediumint, and enum('Y','N'), because with mysql client all works fine, and with the same code with PHP all works fine.
[reply]
Re: query problem
by JavaFan (Canon) on Dec 07, 2010 at 11:50 UTC
    The `message_has_been_sent` has been update, while `ml_message` isn't updated.
    Considering that one is a table, the other a column name, it's not clear what you mean.

    Nor does it seem to be a Perl problem. So, standard questions:

    • What happens if you do this on the command line?
    • What errors did you get?
    • What warnings did you get?
    • How did you actually determine one thing changed, but not the other?
    Now, don't go and actually answer the questions here. Answer them for yourself, and if it still puzzled, ask your friendly local dba, or use a MySQL forum.
[reply]
      please, check the previous answer to corion.
      I have done a type error, while posting my question, but now I correct it:
      The `message_has_been_sent` has been update, while `ml_message` isn't updated.
      It would be:
      The `message_has_been_sent` has been update, while `message_send` isn't updated.

      But, please, "see the moon, not the finger pointing at it".
      before posting this query I checked accuratly some solution.
[reply]

        Please post code that allows us to accurately reproduce your problem. This means, post code that inserts a fresh row and then tries to update the values, and then checks that they have changed. Otherwise, if you do not want us to ask you questions that you are really, really sure do not apply, make sure you have tested them already, and tell us that you have tested them already, and even better, show how you tested these.

        At least from your Perl code, it is still unclear to me why the code you showed would not work, so I assume that the error likely is elsewhere, either in your testing method, in the database or in the code you didn't show.

[reply]
Re: query problem
by locked_user sundialsvc4 (Abbot) on Dec 07, 2010 at 13:51 UTC

    Try to tackle the problem in this way:

    1. Does the problem occur on the command-line?   If an SQL command presented on the command line does not do what it is supposed to do, then you have an SQL server problem... highly unlikely.
    2. My best guess is that the statement is sometimes throwing an error (or that some SQL trigger or integrity check is getting involved), and your code isn’t checking for the problem correctly.   Watch out for do or die, because this checks to see if do is returning a True result.   I usually use defined($DBI::err) although there might be a better way.

Back to Seekers of Perl Wisdom