0

Problem

I have a webpage that does the following (the code is much simplified to show only relevant code.

mysql_query("insert into table1 (field1) values ('value')");
$last_id = mysql_insert_id();
$result = mysql_query("select * from table1 t inner join ... where id = $last_id");
write_a_file_using_result($result);

It happened, that the file was created using a different data set than what I found in the table row.

The only explanation I have is:

  • call1: page was called 1. time with data set 1.
  • call1: data set 1 gets inserted for connection 1 but gets not committed to the table.
  • call2: page was called 2. time with data set 2
  • call2: data set 2 gets inserted for connection 2 and mysql_insert_id returns the same value
  • call1: file is generated with date set 1
  • call2: file cannot be written, because it already exists

Result: The file is generated with data set 1 while the table row contains data row 2.

Config

mysql 5.0.51b

The table:

CREATE TABLE `table1` (
  `id` int(11) NOT NULL auto_increment,
  (...)

Question

I know that MyISAM does not support transactions. But I really expect that it is impossible to insert two rows and get twice the same id inserted, so that the row can be overwritten.

Is MyISAM unsafe to this point or is there another explanation that I overlook ?

Note

I know the mysql extension for php is outdated, but I did not yet rewrite the application.

Lorenz Meyer
  • 19,166
  • 22
  • 75
  • 121
  • Concurrency issues should not affect autoincrement ids in any way, and I've __never__ heard of any incident where duplicate autoincrement ids have been allocated... that would be a pretty fundamental problem, and would surely have been experienced by other users before now... the problem is more likely to be somewhere in your own code – Mark Baker Sep 13 '13 at 12:56
  • Auto_increment should not assign the same numbers. You can also add PRIMARY KEY to your id column to make sure that it stays unique. – aynber Sep 13 '13 at 12:59

2 Answers2

1

Is MyISAM unsafe to this point

No. mysql_insert_id guaranteed to return the right value only.

or is there another explanation that I overlook ?

Most likely. Check your code.

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
  • I checked the whole code, and cannot find an update statement that could have modified the data. I also store the data along the md5 hash of the data, an can disproof that way that the data was modified afterwards. – Lorenz Meyer Sep 13 '13 at 15:41
0

Haven't heard about id issues in MyISAM. You can try to set link identifier when calling last_insert_id, for example

$link = mysql_connect(...);    
mysql_query("insert into table1 (field1) values ('value')",$link);
$last_id = mysql_insert_id($link);
$result = mysql_query("select * from table1 t inner join ... where id = $last_id",$link);
write_a_file_using_result($result);
Greenisha
  • 1,417
  • 10
  • 14
  • Do you think that it can give issues when the link identifier is not set ? I have only one connection in each script, so there should not be a possible problem. Or do I miss something ? – Lorenz Meyer Sep 13 '13 at 14:31