I'm experiencing a problem that I can't find discussed in the mailing list archives, so I hope someone can shed some light on this for me.
I'm running the following code, and it appears to succeed. $DBI::errstr is undefined (there's no error messages when the INSERT runs), and $dbh->{'mysql_last_insert_id'} even returns an auto-incremented value as if a record was actually inserted. However when I go back to the mysql shell I see that no record was inserted at all (see below)
mysql> select id from comments; +----+ | id | +----+ | 1 | | 2 | | 3 | | 4 | | 5 | | 6 | | 7 | | 8 | | 9 | | 10 | | 11 | | 12 | | 38 | | 39 | +----+
Strangely, this code worked each time it ran before, up to the time I hit record id 39 in the table. I can't imagine what happened. I didn't change the code.
Perhaps it should also be noted that when I manually enter a record by hand through the mysql shell a record is created as expected:
mysql> insert into comments set comments = 'foo'; Query OK, 1 row affected (0.00 sec)
mysql> select id from comments; +----+ | id | +----+ | 1 | | 2 | | 3 | | 4 | | 5 | | 6 | | 7 | | 8 | | 9 | | 10 | | 11 | | 12 | | 38 | | 39 | | 59 | <--- RECORD CREATED SUCCESSFULLY +----+ 15 rows in set (0.00 sec)
mysql> delete from comments where id = 59; Query OK, 1 row affected (0.01 sec)
The one thing I find even more odd than anything else is that the last record ID in the table remains at 39, but each time the code runs the value of $dbh->{'mysql_last_insert_id'} continues to be incremented as if a record really was placed in the table on the previous run, though there was not. Right now, for example, when I run the code the value of $dbh->{'mysql_last_insert_id'} was just 60. Yet there's no record there with that ID! What is going on?
Here below I provide both the structure of the table in question and the code I'm running.
First, the table structure (displayed vertically so it doesn't get wrapped in your mail reader):
mysql> desc comments\G *************************** 1. row *************************** Field: id Type: int(10) unsigned Null: Key: PRI Default: NULL Extra: auto_increment *************************** 2. row *************************** Field: photologid Type: int(10) unsigned Null: Key: MUL Default: 0 Extra: *************************** 3. row *************************** Field: approved Type: tinyint(3) unsigned Null: Key: MUL Default: 0 Extra: *************************** 4. row *************************** Field: name Type: varchar(255) Null: YES Key: MUL Default: NULL Extra: *************************** 5. row *************************** Field: comments Type: mediumtext Null: YES Key: Default: NULL Extra: *************************** 6. row *************************** Field: comment_date Type: datetime Null: YES Key: MUL Default: NULL Extra: *************************** 7. row *************************** Field: email Type: varchar(255) Null: YES Key: Default: NULL Extra: *************************** 8. row *************************** Field: ipaddr Type: varchar(255) Null: Key: Default: 000.000.000.000 Extra:
And now the relevant parts of the code:
# connect to DB
$dbh = DBI->connect(
q[DBI:mysql:] .
qq[database=$DATABASE;] .
qq[host=$DBHOST;] .
qq[port=$DBPORT],
$DBUSER,
$DBPASS,
{ 'RaiseError' => 0, 'AutoCommit' => 1 }
)
or do {
# gently tell visitor we had an embarrassing uh-oh occur.
cgi_error(<<__ERR__);
<p>An internal error has occurred on the webserver.
The server administrator has been notified of the error. It will be fixed
as soon as possible.</p><p>We appologize for the inconvenience.</p> __ERR__
# try to let administrator know there was a big problem
¬ify_admin('failed to connect to database! ' . $DBI::errstr);
};my($photolog) = $dbh->selectrow_hashref(<<'__SQL__', undef, $plogid)
SELECT *,DATE_FORMAT(created,'%m/%d/%Y') AS fcreated FROM photologs WHERE id = ?
__SQL__
or do {
cgi_error(qq{Couldn't get photolog info for the requested photolog ID.});
notify_admin(<<__ERR__)
Problem getting photolog record id "$plogid"! $DBI::errstr
__ERR__
};
my($name) = $cgi->param('name');
my($email) = $cgi->param('email') || '[not specified]';
my($comments) = $cgi->param('comments'); cgi_error(q{Please go back and provide your comments. (Duh)})
unless defined $comments and length $comments;cgi_error(q{Anonymous comments aren't accepted.
Please go back provide your name this time (or make up a good fake one.)})
unless defined $name and length $name;
$dbh->do(
<<'__SQL__',
INSERT INTO comments (
photologid,
name,
email,
comments,
ipaddr,
comment_date
) VALUES (?,?,?,?,?,NOW())
__SQL__
undef,
$plogid,
$name,
$email,
$comments,
( $ENV{'REMOTE_ADDR'} || '000.000.000.000' )
) or do {
cgi_error(qq{Couldn't add visitor comments. We're sorry!});
notify_admin(<<__ERR__)
Problem adding visitor comments to photolog id "$plogid"!
$DBI::errstr
__ERR__
}; my($msgid) = $dbh->{'mysql_insertid'};...And that's it. Any ideas? I will be most grateful to anyone who can help me out.
-- Tommy Butler
