Re: dupe times in Time::HiRes - problem in DBI or DBD::MySQL or MySQL or the SQL Stan

Jonathan,

I'm still not sure I'm describing the problem correctly, as we're not
talking about stored procedures. In fact, we're talking about the
simplest kind of insert possible. The column types are what will
break/fix the database table. In the example below, column type 'TEXT'
works (assumes that TINYTEXT would as well..), and column type DOUBLE
does not. Allow me to illustrate how bad it is with only 10 rows
inserted:

We start with 2 empty tables with the following schemas:

(Forive any wrapping-it isn't my fault!)

mysql> desc rawText;desc rawDouble;
+-------------------+------------------+------+-----+---------+-------------
---+
| Field             | Type             | Null | Key | Default | Extra
|
+-------------------+------------------+------+-----+---------+-------------
---+
| id                | int(11) unsigned |      | PRI | NULL    |
auto_increment |
| timeDateInserted  | timestamp(14)    | YES  |     | NULL    |
|
| timeDateInsertedP | text             | YES  |     | NULL    |
|
+-------------------+------------------+------+-----+---------+-------------
---+
3 rows in set (0.00 sec)

+-------------------+------------------+------+-----+---------+-------------
---+
| Field             | Type             | Null | Key | Default | Extra
|
+-------------------+------------------+------+-----+---------+-------------
---+
| id                | int(11) unsigned |      | PRI | NULL    |
auto_increment |
| timeDateInserted  | timestamp(14)    | YES  |     | NULL    |
|
| timeDateInsertedP | double(21,6)     | YES  |     | NULL    |
|
+-------------------+------------------+------+-----+---------+-------------
---+
3 rows in set (0.00 sec)

Now for our test script. (Purists: I could have done this in less
lines, but I'm not in a contest. This is just to allow you to reproduce
the error yourself.)

#!/usr/bin/perl
use strict;
use DBI;
use Time::HiRes qw(gettimeofday);
use Time::Format qw(%time %strftime %manip);
my $dbName      = "TimeTest";
my $dbUser      = '';
my $dbPass      = '';
my $tableName   = "rawText";
#my $tableName   = "rawDouble";
$| = 1;
my $dbh = connectDB($dbName,$dbUser,$dbPass);
$dbh->{AutoCommit} = 1;
my $prepareSchema = "INSERT INTO $tableName
(timeDateInserted,timeDateInsertedP) VALUES (NULL,?)";
my $sth = $dbh->prepare(qq{$prepareSchema});
my $count = 1;
while ($count < 11){
undef $ptime; # paranoid...
$ptime = getSuperFineTime();
print "This is my row: [$count] and this is the ptime:
[$ptime]\n";
$sth->execute($ptime);
$count++;
}
disconnect($dbh);
sub getSuperFineTime {
my $ptime = $time{'yyyymmddhhmmss.uuuuuu'};
return $ptime;
}

When inserting into rawText, STDOUT shows:

This is my count: [1] and this is the time: [20050403213102.343628]
This is my count: [2] and this is the time: [20050403213102.351197]
This is my count: [3] and this is the time: [20050403213102.351660]
This is my count: [4] and this is the time: [20050403213102.352056]
This is my count: [5] and this is the time: [20050403213102.352458]
This is my count: [6] and this is the time: [20050403213102.352855]
This is my count: [7] and this is the time: [20050403213102.353236]
This is my count: [8] and this is the time: [20050403213102.353622]
This is my count: [9] and this is the time: [20050403213102.354007]
This is my count: [10] and this is the time: [20050403213102.354396]

Correct.

Database:

mysql> SELECT * FROM rawText;
+----+------------------+-----------------------+
| id | timeDateInserted | timeDateInsertedP     |
+----+------------------+-----------------------+
|  1 |   20050403213102 | 20050403213102.343628 |
|  2 |   20050403213102 | 20050403213102.351197 |
|  3 |   20050403213102 | 20050403213102.351660 |
|  4 |   20050403213102 | 20050403213102.352056 |
|  5 |   20050403213102 | 20050403213102.352458 |
|  6 |   20050403213102 | 20050403213102.352855 |
|  7 |   20050403213102 | 20050403213102.353236 |
|  8 |   20050403213102 | 20050403213102.353622 |
|  9 |   20050403213102 | 20050403213102.354007 |
| 10 |   20050403213102 | 20050403213102.354396 |
+----+------------------+-----------------------+
10 rows in set (0.00 sec)

Good matches, no problem. Row for row, all went in the way it should
have, and came out good as well.

Now, we send the exact same code to rawDouble STDOUT shows:

This is my count: [1] and this is the time: [20050403213307.705955]
This is my count: [2] and this is the time: [20050403213307.712696]
This is my count: [3] and this is the time: [20050403213307.713165]
This is my count: [4] and this is the time: [20050403213307.713572]
This is my count: [5] and this is the time: [20050403213307.713969]
This is my count: [6] and this is the time: [20050403213307.714368]
This is my count: [7] and this is the time: [20050403213307.714756]
This is my count: [8] and this is the time: [20050403213307.715150]
This is my count: [9] and this is the time: [20050403213307.715537]
This is my count: [10] and this is the time: [20050403213307.715926]

No biggie, right? Each row, as above, is a unique number.

Hold on though... Lets look at what was actually stored in the
database:

mysql> SELECT * FROM rawDouble;
+----+------------------+-----------------------+
| id | timeDateInserted | timeDateInsertedP     |
+----+------------------+-----------------------+
|  1 |   20050403213307 | 20050403213307.707031 |
|  2 |   20050403213307 | 20050403213307.710938 |
|  3 |   20050403213307 | 20050403213307.714844 |
|  4 |   20050403213307 | 20050403213307.714844 |
|  5 |   20050403213307 | 20050403213307.714844 |
|  6 |   20050403213307 | 20050403213307.714844 |
|  7 |   20050403213307 | 20050403213307.714844 |
|  8 |   20050403213307 | 20050403213307.714844 |
|  9 |   20050403213307 | 20050403213307.714844 |
| 10 |   20050403213307 | 20050403213307.714844 |
+----+------------------+-----------------------+
10 rows in set (0.01 sec)

Incorrect.

pat
:)