mysql> select id from comments;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
|  6 |
|  7 |
|  8 |
|  9 |
| 10 |
| 11 |
| 12 |
| 38 |
| 39 |
+----+
[download]

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)
[download]

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):

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:
[download]

And now the relevant parts of the code:

#!/usr/bin/perl -w
use strict; use warnings;

# snip...

# 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 f
+ixed
as soon as possible.</p>

<p>We appologize for the inconvenience.</p>
__ERR__

  # try to let administrator know there was a big problem
  &notify_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 WH
+ERE id = ?
__SQL__
  or do {
     cgi_error(qq{Couldn't get photolog info for the requested photolo
+g 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 o
+ne.)})
     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'};
[download]

...And that's it. Any ideas? I will be most grateful to anyone who can help me out.

--
Tommy Butler, a.k.a. TOMMY