Why does this row only show up when running PHP on Windows and not on CentOS?

Question

This has been bothering me for the last two (close to three) hours - and it only manifests itself on one platform.

Here is the schema for an SQL table:

CREATE TABLE cache ( url                 TINYTEXT,
                     data                MEDIUMTEXT,
                     retrieval_timestamp INT,
                     ttl                 INT )

Now, my PHP code uses the SQLite PDO driver to create a database file on disk. The SQL statement above is executed and the table is created. So far so good - all of my test machines successfully execute that statement.

Next, I insert data into the table - again, all of the machines insert the data without error. Because SQLite stores the database in a file, I can merely open it in SQLite Database Browser and verify that the data is inserted.

Here's where the problem occurs: I can't retrieve the data on a CentOS machine running PHP 5.2.

Here is the PHP code that I'm using (and bear in mind that it works on PHP 5.3 on Windows):

$statement = $this->database->prepare('SELECT data FROM cache WHERE url = ? AND retrieval_timestamp + ttl >= ?');

$statement->bindValue(1, $url);
$statement->bindValue(2, time(), PDO::PARAM_INT);

$statement->execute();

On the CentOS machine, the above code executes without returning any errors. But instead of returning the expected rows (that the other machines return with the exact same query) I get nothing - no rows. If I change SELECT data to SELECT data, retrieval_timestamp + ttl, I can actually view the results of the expression and compare it to the current timestamp by hand - and the data does indeed satisfy the condition, so it should be returned in the results.

If I remove the second part of the WHERE clause, the expected data is returned, but of course, that defeats the purpose of the table :)

What am I doing wrong?

---

Update: it gets weirder - when I use query instead of prepare and specify the parameters manually, it works (on the CentOS machine). So it looks to be a problem with prepared statements.

Here is the SQLite file: http://dl.dropbox.com/u/31080052/test.sqlite
Here is the query I'm running on it:

SELECT data FROM cache WHERE url = 'c' AND retrieval_timestamp + ttl >= 1326780275

Answer 1

Have you checked whether the clocks are synchronized on the two machines?

Answer 2

Two possibilities that spring to mind:

• Is the CentOS install 32-bit? Are you attempting to read/write timestamps that don't fit in a 32-bit int?
• Are the timestamps negative? We had a problem a long time ago where Debian seemed not to support negative timestamps and moving a project across from Slackware caused all sorts of problems because of it.