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.

---

---

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		&
	<		&lt;
	>		&gt;
	[		&#91;
	]		&#93;

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.