MySQLi prepared statements and REPLACE INTO

Question

I have to following code:

http://www.nomorepasting.com/getpaste.php?pasteid=22987

If PHPSESSID is not already in the table the REPLACE INTO query works just fine, however if PHPSESSID exists the call to execute succeeds but sqlstate is set to 'HY000' which isn't very helpful and $_mysqli_session_write->errno and $_mysqli_session_write->error are both empty and the data column doesn't update.

I am fairly certain that the problem is in my script somewhere, as manually executing the REPLACE INTO from mysql works fine regardless of whether of not the PHPSESSID is in the table.

Answer 1

Why are you trying to doing your prepare in the session open function? I don't believe the write function is called more then once during a session, so preparing it in the open doesn't do much for you, you might as well do that in your session write.

Anyway I believe you need some whitespace after the table name, and before the column list. Without the whitespace I believe mysql would act as if you where trying to call the non-existent function named session().

REPLACE INTO session (phpsessid, data) VALUES(?, ?)

---

MySQL sees no difference between 'COUNT ()' and 'COUNT()'

Interesting, when I run the below in the mysql CLI I seem to get a different result.

mysql> select count (*);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '*)' at line 1
mysql> select count(*);
+----------+
| count(*) |
+----------+
|        1 | 
+----------+
1 row in set (0.00 sec)

Answer 2

"REPLACE INTO" executes 2 queries: first a "DELETE" then an "INSERT INTO". (So a new auto_increment is "By Design")

I'm also using the "REPLACE INTO" for my database sessions, but i'm using the MySQLi->query() in combination with MySQLI->real_escape_string() in stead of a MySQLi->prepare()

Answer 3

So as it turns out there are other issues with using REPLACE that I was not aware of:

Bug #10795: REPLACE reallocates new AUTO_INCREMENT (Which according to the comments is not actually a bug but the 'expected' behaviour)

As a result my id field keeps getting incremented so the better solution is to use something along the lines of:

INSERT INTO session(phpsessid, data) VALUES('{$id}', '{$data}') ON DUPLICATE KEY UPDATE data='{$data}'

This also prevents any foreign key constraints from breaking and potential causing data integrity problems.