修改Zurmo活跃用户角色触发BIGINT UNSIGNED范围错误，求解决方案

This error pops up because the campaign_read.count column in your Zurmo database is set as BIGINT UNSIGNED—which can’t store negative values. When you try to update an active user’s role, Zurmo attempts to decrement this count by 1. If the count is already 0, this operation results in an invalid negative number, triggering the SQLSTATE[22003] warning.

Here are practical solutions to resolve this issue:

1. Immediate Temporary Fix #

If you need to fix this for a specific user right away, run this SQL query to adjust the problematic row:

UPDATE zurmogt.campaign_read 
SET count = 1 
WHERE count = 0 AND user_id = 96; -- Replace 96 with the ID of the user you're trying to modify

This ensures the decrement operation will result in a valid non-negative value (0) instead of a negative number that breaks the query.

2. Permanent Code Fix #

To stop this from happening for all users, modify the Zurmo code that handles the count decrement. Look for the campaign or user-related model file where this operation is executed, then adjust it to avoid underflow.

Instead of a simple count - 1, use a SQL function to clamp the value to 0:

UPDATE campaign_read SET count = GREATEST(count - 1, 0) WHERE ...;

Or add a conditional check in the PHP code before running the query:

// Example pseudocode matching Zurmo's pattern
$campaignRead = CampaignRead::getByUserIdAndCampaignId($userId, $campaignId);
if ($campaignRead->count > 0) {
    $campaignRead->count -= 1;
    $campaignRead->save();
}

3. Database Schema Adjustment (Use with Caution) #

You can change the count column to a signed integer to allow negative values (though this may not align with Zurmo’s intended logic):

ALTER TABLE zurmogt.campaign_read MODIFY COLUMN count BIGINT NOT NULL DEFAULT 0;

Note: Only do this if you’re certain Zurmo doesn’t depend on the column being unsigned for other operations. Always back up your database before making schema changes.

内容的提问来源于stack exchange，提问作者waleed