mysql select语句中的算术 - 内存大小错误

Question

Receiving the following error message for this query when I add the arithmetic in the last select ... it is a pretty simple calculation so not sure what the issue is :

alert.last_email is a utc timestamp (unsigned integer)
alerts.email_rate is value in minutes (unsigned integer)

This is a message queue... I am trying to get sends_in to be a value of seconds in which the message will be sent.

For example using normal time instead of timestamps... last email sent at 7:00pm, rate is 15 minutes, so it would be sent at 7:15. If current time is 7:10 then sends in would be 5 minutes or 300 seconds.

Fatal error: Allowed memory size of 33554432 bytes exhausted (tried to allocate 505364672 bytes) in Unknown on line 0

$stmt = $db->prepare("
    SELECT
        users.username AS username,
        computers.computer_name AS computer_name,
        alerts.last_email AS last_email,
        alerts_queue.alert_id AS alert_id,
        alerts_queue.event_title AS event_title,
        alerts_queue.event_target AS event_target,
        alerts_queue.capture_timestamp AS capture_timestamp,
        alerts.last_email + (alerts.email_rate * 60) - UNIX_TIMESTAMP() AS sends_in
    FROM computers
    INNER JOIN users
        ON users.computer_id = computers.computer_id
    INNER JOIN alerts
        ON alerts.user_id = users.user_id
    INNER JOIN alerts_queue
        ON alerts_queue.user_id = alerts.user_id
    WHERE computers.account_id = :cw_account_id AND computers.status = :cw_status
");

$binding = array(
    'cw_account_id' => $_SESSION['user']['account_id'],
    'cw_status' => 1
);
$stmt->execute($binding);

$results = $stmt->fetchAll(PDO::FETCH_ASSOC);

UPDATE :

Calculations are not working as expected :

TIMESTAMPDIFF(SECOND, UTC_TIMESTAMP(), FROM_UNIXTIME(UNIX_TIMESTAMP() + alerts.email_rate * 60)) AS sends_in

Using this as a test... the difference should be 900 seconds, yet it returns -13500.

TIMESTAMPDIFF(SECOND, UTC_TIMESTAMP(), FROM_UNIXTIME(UNIX_TIMESTAMP() + 15 * 60)) AS sends_in

UPDATE :

error on my part - this returns the correct value :

TIMESTAMPDIFF(SECOND, NOW(), FROM_UNIXTIME(UNIX_TIMESTAMP() + 15 * 60)) AS sends_in

WORKING SOLUTION :

It looks a little sloppy, but this works. TIMESTAMPDIFF uses the current timezone... my last_email value is a unix timestamp which is where the FROM_UNIXTIME comes into play. Because last_email is an unsigned integer the arithmetic will not work properly unless it is converted to a signed integer. The reason I always set my timestamps as unsigned is because there will never be a negative, but in the case of doing arithmetic with MySQL this caused an issue.

TIMESTAMPDIFF(SECOND, NOW(), FROM_UNIXTIME(CONVERT(alerts.last_email,SIGNED) + alerts.email_rate * 60)) AS sends_in

Answer 1

This appears to be a PHP error, not a MySQL error. I suspect this error occurs when the fetchAll function is called; and it's the allocation for the array $results that's causing the error. (I'm just guessing.) According to the error message, there was an attempt to allocate almost 482 MB of memory, but the PHP limit is 32 MB.

(I think there's a setting in PHP that sets this limit, but I don't think you really want to go there... because there doesn't appear to be any reason to need to allocate that much memory.

Part of the problem is the fetchAll, retrieving every row in the resultset into memory at the same time.

But, if this only happens when you add that last expression in the SELECT list, my suspicion is that PDO is reserving an inordinate amount of memory for the result of that last expression. Like, MySQL is reporting a strange datatype in the metadata and/or PDO isn't detecting the correct datatype returned by that expression, and is using some huge allocation to store it.

---

For debugging this, try changing that query to return a literal integer value in place of that last expression, that is, replace this:

alerts.last_email + (alerts.email_rate * 60) - UNIX_TIMESTAMP() AS sends_in

with this:

42 AS sends_in 

And see if PHP croaks with that same error message. If not, I suspect that PDO isn't seeing your original expression as a simple integer type. The next step would be to CAST or CONVERT the result of that expression into integer datatype... CONVERT(expr,UNSIGNED) or CONVERT(expr,SIGNED)

That is, replace that last expression with:

CONVERT(alerts.last_email + (alerts.email_rate * 60) - UNIX_TIMESTAMP(),SIGNED) 
  AS sends_in

And give that a whirl, and see how big of a smoke ball that makes.

In terms of the actual MySQL statement, I think there may be an error raised with an attempt to subtract unsigned integers. (I think there's something in the SQL_MODE that controls this behavior, and I think the behavior depends on the MySQL version.) It may be necessary to CONVERT/CAST the unsigned integers into signed (64-bit) integers, something ugly like this might work:

CONVERT(CONVERT(alerts.last_email,SIGNED) + (CONVERT(alerts.email_rate,SIGNED) * 60) 
  - CONVERT(UNIX_TIMESTAMP(),SIGNED),SIGNED)
  AS sends_in

Personally, I'd prefer to avoid unsigned integer math with datetime and timestamp values, and make use of MySQL datetime functions.

---

I'm not sure if I answered your question. I'm not even sure you asked a question.