Oscar Jofre Oscar Jofre - 4 months ago 28
SQL Question

MariaDB BIGINT UNSIGNED value is out of range

I've been reading about this error but can't find how to fix this error.
I do have a schedule every hour of this sentence:

UPDATE radacct SET
radacct.AcctStopTime=NOW(),
radacct.AcctTerminateCause='Stale-Session'
WHERE ((UNIX_TIMESTAMP(NOW()) - (UNIX_TIMESTAMP(radacct.acctstarttime) + radacct.acctsessiontime)) > (60*6+60*6))
AND (AcctStopTime = '0000-00-00 00:00:00' OR AcctStopTime IS NULL)


And some times i'm getting this error:

BIGINT UNSIGNED value is out of range in '(unix_timestamp(now()) - (unix_timestamp(`radius`.`radacct`.`acctstarttime`) + `radius`.`radacct`.`acctsessiontime`))'


This is the create table:

CREATE TABLE `radacct` (
`radacctid` bigint(21) NOT NULL AUTO_INCREMENT,
`acctsessionid` varchar(64) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`acctuniqueid` varchar(32) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`username` varchar(64) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`groupname` varchar(64) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`realm` varchar(64) COLLATE utf8_unicode_ci DEFAULT '',
`nasipaddress` varchar(15) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`nasportid` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL,
`nasporttype` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
`acctstarttime` datetime DEFAULT NULL,
`acctupdatetime` datetime DEFAULT NULL,
`acctstoptime` datetime DEFAULT NULL,
`acctinterval` int(12) DEFAULT NULL,
`acctsessiontime` int(12) unsigned DEFAULT NULL,
`acctauthentic` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
`connectinfo_start` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
`connectinfo_stop` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
`acctinputoctets` bigint(20) DEFAULT NULL,
`acctoutputoctets` bigint(20) DEFAULT NULL,
`calledstationid` varchar(50) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`callingstationid` varchar(50) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`acctterminatecause` varchar(32) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`servicetype` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
`framedprotocol` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
`framedipaddress` varchar(15) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
PRIMARY KEY (`radacctid`),
UNIQUE KEY `acctuniqueid` (`acctuniqueid`),
KEY `username` (`username`),
KEY `framedipaddress` (`framedipaddress`),
KEY `acctsessionid` (`acctsessionid`),
KEY `acctsessiontime` (`acctsessiontime`),
KEY `acctstarttime` (`acctstarttime`),
KEY `acctinterval` (`acctinterval`),
KEY `acctstoptime` (`acctstoptime`),
KEY `nasipaddress` (`nasipaddress`),
KEY `callingstationid` (`callingstationid`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=14270573 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


Added:

SHOW variables LIKE 'sql_mode'

'sql_mode', 'NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

Added 2:

This is the final query working perfect as suggest @Rick James

UPDATE radacct SET
radacct.AcctStopTime=NOW(),
radacct.AcctTerminateCause='Stale-Session'
WHERE ( NOW() - interval (60*6+60*6) second > radacct.acctstarttime + interval radacct.acctsessiontime second )
AND (AcctStopTime = '0000-00-00 00:00:00' OR AcctStopTime IS NULL)


Thanks.

Answer

Avoid subtraction and work only in timestamps:

WHERE  radacct.acctstarttime + INTERVAL radacct.acctsessiontime SECOND
     < NOW() - INTERVAL 12 MINUTE

(And it is more readable, in my opinion)

This 'composite' index might help performance:

INDEX(acctstarttime, acctsessiontime, AcctStopTime)

If you are having performance problems, consider normalizing columns that are wide, but repetitive.

Comments