Cannot set current _timestamp or now() with PDO on MySQL

Question

I use PDO prepared statements in a Web application I'm building. I have a timestamp column set to current_timestamp as default value.

Using a timestamp should normally assign the current timestamp when the column value is null, same as now() would do. But no matter what I try, I get a NULL value if I set the timestamp column to null and 0000-00-00 00:00:00 if I use now(). Using normal queries work perfectly fine.

I send my parameters as an associative array, then use a foreach with bindValue on the PDOStatement. It's always worked perfectly until this problem. I tried debugDumpParams, but it crashes pretty dramatically. It seems to be a known bug with 5.2.6 and Ubuntu hasn't updated the package yet. I'd rather avoid the trouble of bypassing aptitude and installing php from source since I'm not even sure if debugDumpParams would help...

Answer 1

It sounds like your problem might be that you are trying to use a MySQL function as a parameter value in the prepared query. This fails because the process of preparing the query will turn it into a literal for the SQL statement, including things like quoting strings, which is not what you want. So for setting a field to NOW(), do the assignment in the query itself before you make it prepared, not in any of the parameters.

On the other hand, using a null parameter value should work. But clearly it doesn't, which may be the bug you mentioned. Again, the solution is to do it directly in the SQL. You can still use parameters for other fields.