Why does MySQL insert appear to work but not insert anything?

[Dormilich]

I can add from my experience, that MySQL (maybe PHP) may not immediately show newly inserted entries. I had some cases where I had to wait for some minutes, before the new entry came up in the output.

[dinesh1440]

Hi there,

This is driving me crazy! I have a mysql_query call to insert a simple record:

$update = mysql_query("INSERT INTO `my_db`.`record_collection` (`record_id`, `collection_id`, `sortorder`, `active`) VALUES ('$id', $new_collection_id, 0, 1)");

When echoed out, this generates a nice little query such as:

INSERT INTO `my_db`.`record_collection` (`record_id`, `collection_id`, `sortorder`, `active`) VALUES ('A1208856984', 41, 0, 1)

Pasting this query into the MySQL command line, or into phpMyAdmin works just fine - the record is inserted. However, the above mysql_query() call doesn't seem to insert the record.

Even more bizarrely, making another mysql_query() call to try inserting the same thing again comes up with a duplicate key error, as if the record *had* been inserted. I can even do a select query immediately after the insert and pull back the instered data but it's not there when I look in the datanase or try to pull the record out on a different page.

It's as is the insert is not properly committed, but there are no errors reported via mysql_error().

Any ideas?

Hello friend ,,
The syntax is not "insert into tablename (....);

But is "insert into tablename values(....);

[dinesh1440]

I am not sure about the problem but, one thing i would suggest not to avoid
your code below:

mysql_query("INSERT INTO `my_db`.`record_collection` (`record_id`, `collection_id`, `sortorder`, `active`) VALUES ('$id', $new_collection_id, 0, 1)");

suggested

mysql_query("INSERT INTO `my_db`.`record_collection` (`record_id`, `collection_id`, `sortorder`, `active`) VALUES ('$id', $new_collection_id, 0, 1)",$conn);

And I dont think this is the reason but give it a try

Oh.. Sorry didn't see it well...

Don have any idea.

[code green]

This one threw me

INSERT INTO `my_db`.`record_collection` SET `record_id`='$id',...

But seems that is acceptable in MySQL

[Isborg]

3 years later, here is the solution:

The table was probably recycled, try deleting all the rows, not dropping the table, deleting the rows.

[graul123]

I have same issue is not sintax, I am fixing the table but more than one table corrupt?. I suspect if phpMyAdmin works, we should respct alternative answer:INSERT INTO `my_db`.`record _collection` SET `record_id`='$i d',... and I do get no errors, neither bad intentional sql. Have tried with connection as well. Just data is not inserted. Fixing may work after an attack or something but if you are on innodb you just run the the query alter table t1 force; If nothing works I am changing into insert delayed or insert ignore to test if it changes some instruction.

[graul123]

Do not know have to test all options until something work.

[graul123]

Maybe is an empty value on an index or something but that gives an error. Possitive responses values, I have seen different behaviours before an error. For example if you use plain insert, it should give an error on a repeated index, but I am getting nothing on response.