MySQL Bugs: #19186: write wrong DATE in table

Bug #19186	write wrong DATE in table
Submitted:	19 Apr 2006 7:28	Modified:	29 May 2006 13:11
Reporter:	Stanimir Marinov	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:		OS:	Windows (Windows XP)
Assigned to:		CPU Architecture:	Any

Description:
I have table:

CREATE TABLE `stariosigur` (
  `ID` int(10) unsigned NOT NULL auto_increment,
  `DATE` datetime NOT NULL default '0000-00-00 00:00:00',
  `DATE2` datetime NOT NULL default '0000-00-00 00:00:00',
  `KOLA` char(30) NOT NULL default '0',
  `TARIFA` double(11,2) NOT NULL default '0.00',
  PRIMARY KEY  (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

and SQL:
(this is from debuging whit Zend Studio)
insert into stariosigur (`DATE`,DATE2,KOLA,TARIFA) values ('2005-01-01 00:00:00','2006-04-19 09:29:53','Name','10')

If i run this from PHP script in 90% of case in table write:
 (ID, DATE, DATE2, KOLA, TARIFA)
VALUES
  (6, "19.4.2006 09:49:16", "19.4.2006 09:49:31", "NAME", 85);

`DATE` is equal to DATE2 (now) (-20s -30s)

My PHP script:
$existst=mysql_fetch_array(mysql_query('select IFNULL((select max(stariosigur.DATE2) from stariosigur WHERE stariosigur.KOLA=\'NAME\'),0)'),MYSQL_NUM);

mysql_query('insert into stariosigur (`DATE`,DATE2,KOLA,TARIFA) values ('.($existst[0]!='0'?$existst[0]:'2005-01-01 00:00:00').',\'2006-04-19 09:49:31\',\'Name\',\'10\')');

How to repeat:
this problem appear in InnoDB table in 90% ot tests if table is MyISAM no problems.
In debuguging with Zend Studio if i set breakpoint this problem not appear, but without break appear.
In Navicat or EMS.. MySQL Tools no problems..

MySQL server is version- 5.0.18-nt

Thank you for a problem report. Please, try to repeat with a newer version, 5.0.20, and inform about the results.

I install MySQL 5.0.20 and.. no change this appear in InnoDB if I delete record and after 
insert into stariosigur (`DATE`,DATE2,KOLA,TARIFA) values ('2005-01-01
00:00:00','2006-04-19 09:29:53','Name','10')
in column `DATE` write in the place of '2005-01-01 00:00:00' the time of the delete previous record.
I know the `DATE` is word of MySQL but I write `...`

If i write    Date- 2006-04-19 12:11:06     Date2- 2006-04-19 12:26:32
and delete this record
next insert is:
insert into stariosigur (`DATE`,DATE2,KOLA,TARIFA) values ('2005-01-01
00:00:00','2006-04-19 12:27:45','Name','10')
and this write in table:
DATE = 2006-04-19 12:26:32 DATE2=2006-04-19 12:27:45

DATE = DATE2 of the deleted record ??

IF exist i write this in `DATA` 

select max(stariosigur.DATE2) from stariosigur WHERE stariosigur.KOLA='NAME'

but after delete.. my table is empty and write DATE2 of the deleted record

Please, try to repeat with PHP5 and mysqli (not mysql). See http://php.net/manual/en/ref.mysqli.php. Inform about the results.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".