a serious date bug you might want to check out

hi there, fellow informix users. lately, we've been having mysterious programs with our server. we'd insert "12/31/1899" (the magic day zero), and about every 200,000th time, informix would insert "01/01/1900" instead. similarly for "12/31/1999". whether other dates are affected, we aren't sure. when we turn off parallel processing, the problem goes away: param orig new setting ------------------------------------------- MULTIPROCESSOR 1 0 NUMCPUVPS 2 1 SINGLE_CPU_VP 0 1 we're running 7.30.TC2 on NT 4, SP 6, 2 processor pentium-2. also, we have a 4-processor running 7.30.TC7, with the same problem. we couldn't find any info regarding this problem here in this newsgroup, or in informix's techinfo center. so apparently nobody has noticed this problem yet except us. since this problem could be silently corrupting your database, we thought we'd let you know, and give you a way to verify whether you are affected or not. first, create the table we will use for the testing: create table datebug ( table_code serial not null, timestamp datetime year to second default current year to second, host char(20), date1 date, date2 date, date3 date, date4 date, date5 date, date6 date, date7 date, date8 date, date9 date, date10 date, date11 date, date12 date, date13 date, date14 date, date15 date, date16 date ); ---- next create a unix script called "Date_test": #!/bin/ksh trap "" HUP db=<your db here> host=`uname -n`.$DBCENTURY errors=errors.$host.$$ errors=/dev/null count=0 while [ $count -lt 10000 ] do query $$ | /usr/informix/bin/orig/isql $db > /dev/null 2>>$errors count=$(($count+1)) done ----- next, create another script called "query": #!/usr/local/bin/bash host=`uname -n`.$DBCENTURY today='"1-31-2000"' hard='"12/31/1999"' file=/tmp/query.$host.$1 if [ -r $file ]; then cat $file < /dev/null exit 0 fi foo() { cat <<! INSERT INTO datebug (table_code, host, date1, date2, date3, date4, date5, date6, date7, date8, date9, date10, date11, date12, date13, date14, date15, date16) VALUES(0,"$host","12/31/1899","12/31/1899", "12/31/1899","12/31/1899",$hard, $hard, 0,0,0,0,15,15,"02/03/1974","02/03/1974",$today,$today);! } count=0 while [ $count -lt 100 ]; do foo >> $file count=$(( $count + 1 )) done cat $file < /dev/null ----- then, fire off about 10 Date_tests: Date_test & Date_test & Date_test & Date_test & Date_test & Date_test & Date_test & Date_test & Date_test & Date_test & ----- then run this script every 100000 inserts to check if there are problems: #!/bin/ksh isql <your db here> <<@@ select * from datebug where date1 <> 0 or date2 <> 0 or date3 <> 0 or date4 <> 0 or date5 <> '12/31/1999' or date6 <> '12/31/1999'@@ ----- good luck! curley Sent via Deja.com http://www.deja.com/ Before you buy.

>Subject: a serious date bug you might want to check out >From: curley_joe@my-deja.com >Date: 10.03.00 04:55 W. Europe Standard Time >Message-id: <8a9rnc$lhv$1@nnrp1.deja.com> > >hi there, fellow informix users. lately, we've been having mysterious >programs with our server. we'd insert "12/31/1899" (the magic day >zero), and about every 200,000th time, informix would insert >"01/01/1900" instead. similarly for "12/31/1999". whether other >dates are affected, we aren't sure. > >when we turn off parallel processing, the problem goes away: > > param orig new setting >------------------------------------------- > MULTIPROCESSOR 1 0 > NUMCPUVPS 2 1 > SINGLE_CPU_VP 0 1> >we're running 7.30.TC2 on NT 4, SP 6, 2 processor pentium-2. >also, we have a 4-processor running 7.30.TC7, with the >same problem. > >we couldn't find any info regarding this problem here in this newsgroup, >or in informix's techinfo center. so apparently nobody has noticed >this problem yet except us. > >since this problem could be silently corrupting your database, we >thought we'd let you know, and give you a way to verify whether you >are affected or not. > >first, create the table we will use for the testing: > >create table datebug > ( > table_code serial not null, > timestamp datetime year to second > default current year to second, > host char(20), > date1 date, date2 date, date3 date, date4 date, > date5 date, date6 date, date7 date, date8 date, > date9 date, date10 date, date11 date, date12 date, > date13 date, date14 date, date15 date, date16 date > );> >---- > >next create a unix script called "Date_test": > >#!/bin/ksh >trap "" HUP >db=<your db here> >host=`uname -n`.$DBCENTURY >errors=errors.$host.$$ >errors=/dev/null > >count=0 > >while [ $count -lt 10000 ] >do > query $$ | /usr/informix/bin/orig/isql $db > /dev/null 2>>$errors > count=$(($count+1)) >done > >----- > >next, create another script called "query": > >#!/usr/local/bin/bash > >host=`uname -n`.$DBCENTURY >today='"1-31-2000"' >hard='"12/31/1999"' >file=/tmp/query.$host.$1 > >if [ -r $file ]; then > cat $file < /dev/null > exit 0 >fi > >foo() { >cat <<! > INSERT INTO datebug (table_code, host, date1, date2, date3, > date4, date5, date6, date7, date8, date9, date10, > date11, date12, date13, date14, date15, date16) > VALUES(0,"$host","12/31/1899","12/31/1899", > "12/31/1899","12/31/1899",$hard, $hard, > 0,0,0,0,15,15,"02/03/1974","02/03/1974",$today,$today);>! >} > >count=0 >while [ $count -lt 100 ]; >do > foo >> $file > count=$(( $count + 1 )) >done > >cat $file < /dev/null > >----- > >then, fire off about 10 Date_tests: > >Date_test & >Date_test & >Date_test & >Date_test & >Date_test & >Date_test & >Date_test & >Date_test & >Date_test & >Date_test & > >----- > >then run this script every 100000 inserts to check if there are >problems: > >#!/bin/ksh > >isql <your db here> <<@@ >select * from datebug >where> date1 <> 0 or date2 <> 0 or date3 <> 0 or date4 <> 0 or > date5 <> '12/31/1999' or date6 <> '12/31/1999' >@@ > >----- > >good luck! > >curley > > >Sent via Deja.com http://www.deja.com/ >Before you buy. > Your bug is just a test environment error, because the 7.30.TC2 is an obsolete version. Please repeat the test with 7.31.TC5. Also check with Tech Support whether or not the SP6 is supported. Nona

> Your bug is just a test environment error, because the 7.30.TC2 is an > obsolete version. Please repeat the test with 7.31.TC5. Also check > with Tech Support whether or not the SP6 is supported. > > Nona see Defect 111712, "CONVERTING DATETIME TO DATE IN A SPL CAN GENERATE WRONG RESULTS" at http://www.informix.com/informix/services/ilink/kb/kb_table.html it exists up until 7.32.TC1 and doesn't seem to have been fixed yet. Curley Sent via Deja.com http://www.deja.com/ Before you buy.