MySQL Bugs: #27237: PHP ISAPI heap memory crash while running mysql query

Bug #27237	PHP ISAPI heap memory crash while running mysql query
Submitted:	17 Mar 2007 12:43	Modified:	23 Mar 2007 8:19
Reporter:	Thomas Brunnthaler	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S2 (Serious)
Version:	ALL 5.1.x	OS:	Windows (W2K)
Assigned to:		CPU Architecture:	Any
Tags:	PHP HEAP MYSQL LIBRARY

Description:
The table should be used to log hits for a website - i setup time to be UNIQUE but this seems to crash the heap randomly under W32 running PHP 5.2.x as ISAPI.

How to repeat:
CREATE TABLE hit (
  log longtext CHARACTER SET latin1,
  `time` int(15) NOT NULL,
  `file` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  arid varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  sidr varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  ads varchar(40) CHARACTER SET latin1 NOT NULL DEFAULT '',
  customer varchar(255) CHARACTER SET latin1 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

Every time a user hits the page you should insert data into the mysql table.
I have about 1.3mio entrys, may you try to hammer because it will happen randomly after running a few days.

$result=mysql_query("INSERT INTO hit VALUES ('$sometext','".time()."','$sometext','$sometext','$sometext','$sometext','$sometext')");

Suggested fix:
Do not use UNIQUE Index on time field INT(15)

Thank you for a problem report. Can you, please, send a complete PHP script that demonstrate the behaviour described, with the results from it? 

Is it possible that you are trying to INSERT two identical `time` values? What are you doing with $result in your code after that mysql_query call?

The one line with insert is all to do; can be also at the end of a page as single line. It's possible that the php script tries to insert at the same time.

So, as it is possible that you insert the same `time` column value from 2 or more sessions, why not just to check the value of $result in your code? You can get error while INSERTing into UNIQUE column, and you should process that error somehow.

So you're right, i now use nomal indexed column. But anyway, i set this column to unique before and i did not check for double entries neither than watching the final result. 

But it should not affect heap memory at all when user does funny things like that so i assume a memory bug in UNIQUE index or somewhere else.

Please, send a part of MySQL's error log that has information about a crash, if any.

I'm sorry , i can't - the mysql-nt is may causing the crash because of reporting wrong values or something else to the php mysql module. This causes finally the error in php. Maybe PHP'5 zend memory manager is dealing wrong with these return values . Because i cannot make a backtrace it would be hard for me to tell you more. But i can say - if you set UNIQUE to INT(15) field and insert often same values the heap is crashing.

PS: It happens on latest 5.2.1 too and much more often.

Sorry, but if MySQL server does not crash it is, likely, a bug in your application/PHP mysql module/PHP but NOT in server's code. According to what I understand from this report, server correctly informs client application that you can not INSERT successfully as UNIQUE constraint is violated. Client should process this information properly.

There may be a bug in MySQL client library, but then you should be able to demonstrate it with a C code, to get all other components out from consideration.

Ok - your'e right again - i submitted the "bug" already to the PHP developers to see if they can solve what's happen here.

Hi all, if anybody uses PHP as ISAPI, MySQL 5.1.x and any Windows Webserver it may help to know to avoid multiple equal time() entries to a UNIQUE INT(15) database field.

Bug closed because php library seems to be buggy.