rlm_sql_mysql: MySQL error ‘Incorrect integer value: ” for column ‘XAscendDataRate’ at row 1

If you get this on your favourite radius server:

rlm_sql_mysql: MySQL error 'Incorrect integer value: '' for column 'XAscendDataRate' at row 1

it is probably because you have upgraded your FreeRadius with MySQL backend system from RHEL/CentOS 5 to RHEL/CentOS 6, and inherited an upgrade to MySQL 5.1.69 or so. This SQL used to work fine, but no longer does:

INSERT into radonline (
    AcctSessionId, AcctUniqueId, UserName, Realm, NASIPAddress, NASPortId, NASPortType,
    AcctStartTime, AcctSessionTime, AcctAuthentic, ConnectInfo_start, AcctInputOctets,
    AcctOutputOctets, CalledStationId, CallingStationId, ServiceType, FramedProtocol,
    FramedIPAddress, AcctStartDelay, XAscendSessionSvrKey, AcctInputGigawords,
    AcctOutputGigawords, XAscendDataRate,XAscendXmitRate, updated_at, created_at)
VALUES (
    '80b001b3', '37bfe3e37afa39dd', 'user124541@domain', 'somerealm', '196.0.1.60',
    '2159018419', 'Wireless-802.11', DATE_SUB('2013-09-13 09:39:34',INTERVAL (23403 + 238854) SECOND),
    '23403', '', '', '8', '7', 'RhodesPlace', 'FF:FF:FF:63:96:44', '', '',
    '196.22.61.243', '0', '', '0', '0',
    '',
    '',
    from_unixtime(1378819120), now());

The error is:

rlm_sql_mysql: MySQL error 'Incorrect integer value: '' for column 'XAscendDataRate' at row 1

This is because the XAscendDataRate is defined as

  `XAscendDataRate` bigint(32) DEFAULT NULL,

And '' is not really a valid integer. It never has been an integer.

However, this used to work. What has changed?

This newer version or package of mysql creates a file /usr/my.cnf (I kid you not) which says:

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

That means that if the engine wants to substitute something meaningful like NULL in the place of '' it must not. Comment out this line, restart mysqld, and the problem goes away. Strictly the SQL should be fixed to say NULL but this is automatically generated SQL, and I’m not in the mood.

You can see that mysql is willing to be friendly towards your NULL strings like this:

mysql> select @@sql_mode;
+------------+
| @@sql_mode |
+------------+
|            |
+------------+
1 row in set (0.00 sec)