Description:
(see: http://groups-beta.google.com/group/comp.lang.perl.modules/browse_thread/thread/1459c498b9...)
I'm not sure if 'serious' is an appropriate prioritization, but we're unable to use column types DOUBLE as a result. You decide. Also, your "Categories" above don't include core functionality bugs liek this one, so forgive me if 'Co
Given:
pat@tcas02:~> mysql -V
mysql Ver 12.22 Distrib 4.0.21, for pc-linux (i686)
cpan> autobundle
Package namespace installed latest in CPAN file
DBD::mysql 2.9005_3 2.9006 R/RU/RUDY/DBD-mysql-2.9006.tar.gz
DBD::mysql::GetInfo undef undef R/RU/RUDY/DBD-mysql-2.9006.tar.gz
DBI::DBD 11.21 11.21 T/TI/TIMB/DBI-1.48.tar.gz
pat@tcas02:~> perl -V
Summary of my perl5 (revision 5.0 version 8 subversion 0) configuration:
Platform:
osname=linux, osvers=2.4.19, archname=i586-linux-thread-multi
uname='linux brooks 2.4.19 #1 thu nov 14 12:14:04 utc 2002 i686 unknown '
config_args='-ds -e -Dprefix=/usr -Dusethreads -Di_db -Di_dbm -Di_ndbm -Di_gdbm -Duseshrplib=true'
hint=recommended, useposix=true, d_sigaction=define
usethreads=define use5005threads=undef useithreads=define usemultiplicity=define
useperlio=define d_sfio=undef uselargefiles=define usesocks=undef
use64bitint=undef use64bitall=undef uselongdouble=undef
usemymalloc=n, bincompat5005=undef
[...]
Characteristics of this binary (from libperl):
Compile-time options: MULTIPLICITY USE_ITHREADS USE_LARGE_FILES PERL_IMPLICIT_CONTEXT
Built under linux
Compiled at Apr 2 2003 21:16:30
@INC:
/usr/lib/perl5/5.8.0/i586-linux-thread-multi
/usr/lib/perl5/5.8.0
/usr/lib/perl5/site_perl/5.8.0/i586-linux-thread-multi
/usr/lib/perl5/site_perl/5.8.0
/usr/lib/perl5/site_perl
.
How to repeat:
Newsgroups: comp.lang.perl.modules
From: "pat" <pat.trai...@gmail.com> - Find messages by this author
Date: 3 Apr 2005 18:39:21 -0700
Local: Sun, Apr 3 2005 6:39 pm
Subject: Re: dupe times in Time::HiRes - problem in DBI or DBD::MySQL or MySQL or the SQL Standard.
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
:)
Suggested fix:
Test with at least the sample script supplied.