This is likely caused by the recent change related to timezone handling installer_db_now() vs. db_now(). They handle timestamps differently.

There are several calls in manage_user_page that looks like this:
'' . db_now() . ''

The '' translates to nothing. I wonder if the intention was to use "'" instead of ''.

This issue is like introduced by Paul's checkin below:
b0d0c9dc904fa6ef6e0bc73848f216cd415429b8

This issue is like introduced by Paul's checkin below:
b0d0c9dc904fa6ef6e0bc73848f216cd415429b8

OK, I'll have a look. Can you pls provide steps to reproduce ?

There are several calls in manage_user_page that looks like this:
'' . db_now() . ''

Indeed it used to be "'". This was introduced by an old commit converting DateTimes in DB to integer [1].

I suppose we could remove these as part of a code cleanup commit after making sure that the code handles the type change correctly, as the use of '.' operator changes return value of db_now() from int to string). If string is needed, we could replace '' . db_now() . '' by (string)db_now() which would be clearer.

[1] https://github.com/mantisbt/mantisbt/commit/51051c75143a3cadd312ea68e3a6edc849b21b1e#diff-33

I had a quick look over lunch, and while I was not able to reproduce the problem, I believe the error occurs because MySQL is using unsigned data type for the substraction (date_created column is unsigned).

harden db_helper_compare_days() to handle such case.
For example, before subtracting make sure date1 <= date2 || date1 - date2 <= 7 days.

I don't think that's feasible, because the function needs to handle date columns, not just date values (integers) so the resolution happens at RDBMS level, not in PHP.

We could use a type cast, but implementation would be difficult while maintaining cross-DB compatibility - SQL-92 defines a CAST(value AS type) function, but the available target types are not standardized across all platforms.

I'm thinking that a (probably) better alternative could be to refactor the function to return

date1 [operator] date2 + [number of days] ==> 1419273289 <= date_created + 604800

instead of

date1 - date2 [operator] [number of days] ==> 1419273289 - date_created <= 604800

Please test and review: https://github.com/mantisbt/mantisbt/pull/564

EDIT: I'm not sure that the PR actually fixes the root cause of this issue (date created in the future), just the symptoms (MySQL error), but as I was not able to reproduce the problem...

In all cases, I noticed that we compare against db_now(). Is there a reason why the code can't look like the following pseudo-code:

$t_cutoff_timestamp = time() - $t_days * SECONDS_PER_DAY;
$t_query = "SELECT table WHERE timestamp >= $t_cutoff_timestamp";

That is easier to understand. I think the method is adding unneeded complexity. In terms of code reviews, we should make sure we avoid $t_timestamp1 - $t_timestamp2 in our queries.

We need in any case to use a DB parameter:

'SELECT table WHERE timestamp >= ' . db_param()

This allows the RDBMS to optimize/cache the query and avoids the SQL being re-parsed every time it is executed.

Yep, that is why I called it pseudo code. We also need to use a real table name :)

The point is that we don't really need this method. What do you think about removing it completely?

I've attached a script that reproduces the problem:

Here is the output of the script:

Time = 1419358917 (time())
Bind timestamp = 1419358917 (before Paul's change)
Timezone = America/Los_Angeles
Bind timestamp with zone = 1419387717 (with Paul's change)

The last once is what is used for date_created for the administrator user. It is 8 hours in the future compared to time() which we use later in the code. This issue may reproduce based on the timezone of the machine compared to UTC. My timezone (Los Angeles timezone) is -8, hence UTC is behind local. Your timezone may be different, i.e. local behind UTC.

Basically, it is likely that even though time() returns UTC, the ADODB library assumes it is local time and translates it again to UTC by adding 8 hours.

Basically, it is likely that even though time() returns UTC, the ADODB library
assumes it is local time and translates it again to UTC by adding 8 hours.

I had a look at ADOdb source code, and in fact this is exactly what is happening:

ADOConnection::BindTimestamp ==> ADOConnection::DBTimeStamp ==> ADOConnection::UnixTimeStamp ==> adodb_mktime ==> mktime (NOT gmmktime)

I need to go through the code again and make some tests, but I think we should probably revert grangeway's change to schema.php introduced by https://github.com/mantisbt/mantisbt/commit/b0d0c9dc90.

Updated PR https://github.com/mantisbt/mantisbt/pull/564