{"id":793,"date":"2013-09-13T10:20:51","date_gmt":"2013-09-13T08:20:51","guid":{"rendered":"\/\/www.mcgill.org.za\/stuff\/?p=793"},"modified":"2013-09-13T10:20:51","modified_gmt":"2013-09-13T08:20:51","slug":"rlm_sql_mysql-mysql-error-incorrect-integer-value-for-column-xascenddatarate-at-row-1","status":"publish","type":"post","link":"https:\/\/www.mcgill.org.za\/stuff\/archives\/793","title":{"rendered":"rlm_sql_mysql: MySQL error ‘Incorrect integer value: ” for column ‘XAscendDataRate’ at row 1"},"content":{"rendered":"

If you get this on your favourite radius server:<\/p>\n

rlm_sql_mysql: MySQL error 'Incorrect integer value: '' for column 'XAscendDataRate' at row 1<\/pre>\n
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:<\/p>\n
\r\nINSERT into radonline (\r\n    AcctSessionId, AcctUniqueId, UserName, Realm, NASIPAddress, NASPortId, NASPortType,\r\n    AcctStartTime, AcctSessionTime, AcctAuthentic, ConnectInfo_start, AcctInputOctets,\r\n    AcctOutputOctets, CalledStationId, CallingStationId, ServiceType, FramedProtocol,\r\n    FramedIPAddress, AcctStartDelay, XAscendSessionSvrKey, AcctInputGigawords,\r\n    AcctOutputGigawords, XAscendDataRate,XAscendXmitRate, updated_at, created_at)\r\nVALUES (\r\n    '80b001b3', '37bfe3e37afa39dd', 'user124541@domain', 'somerealm', '196.0.1.60',\r\n    '2159018419', 'Wireless-802.11', DATE_SUB('2013-09-13 09:39:34',INTERVAL (23403 + 238854) SECOND),\r\n    '23403', '', '', '8', '7', 'RhodesPlace', 'FF:FF:FF:63:96:44', '', '',\r\n    '196.22.61.243', '0', '', '0', '0',\r\n    '',\r\n    '',\r\n    from_unixtime(1378819120), now());\r\n<\/pre>\n
The error is:<\/p>\n
\r\nrlm_sql_mysql: MySQL error 'Incorrect integer value: '' for column 'XAscendDataRate' at row 1\r\n<\/pre>\n
This is because the XAscendDataRate<\/code> is defined as<\/p>\n
  `XAscendDataRate` bigint(32) DEFAULT NULL,<\/pre>\n
And ''<\/code> is not really a valid integer.  It never has been an integer.<\/p>\n
However, this used to work.  What has changed?<\/p>\n
This newer version or package of mysql<\/code> creates a file \/usr\/my.cnf<\/code> (I kid you not) which says:<\/p>\n
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES<\/pre>\n
That means that if the engine wants to substitute something meaningful like NULL<\/code> in the place of ''<\/code> it must not.  Comment out this line, restart mysqld<\/code>, and the problem goes away.  Strictly the SQL should be fixed to say NULL<\/code> but this is automatically generated SQL, and I’m not in the mood.<\/p>\n
You can see that mysql is willing to be friendly towards your NULL strings like this:<\/p>\n
mysql> select @@sql_mode;\r\n+------------+\r\n| @@sql_mode |\r\n+------------+\r\n|            |\r\n+------------+\r\n1 row in set (0.00 sec)\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"
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 … Continue reading →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[98,122,119,20,120,111,123,121,124,190],"class_list":["post-793","post","type-post","status-publish","format-standard","hentry","category-stuff","tag-centos","tag-error","tag-freeradius","tag-mysql","tag-null","tag-radius","tag-rhel","tag-sql_mode","tag-strict_trans_tables","tag-stuff"],"_links":{"self":[{"href":"https:\/\/www.mcgill.org.za\/stuff\/wp-json\/wp\/v2\/posts\/793","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.mcgill.org.za\/stuff\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.mcgill.org.za\/stuff\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.mcgill.org.za\/stuff\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.mcgill.org.za\/stuff\/wp-json\/wp\/v2\/comments?post=793"}],"version-history":[{"count":2,"href":"https:\/\/www.mcgill.org.za\/stuff\/wp-json\/wp\/v2\/posts\/793\/revisions"}],"predecessor-version":[{"id":795,"href":"https:\/\/www.mcgill.org.za\/stuff\/wp-json\/wp\/v2\/posts\/793\/revisions\/795"}],"wp:attachment":[{"href":"https:\/\/www.mcgill.org.za\/stuff\/wp-json\/wp\/v2\/media?parent=793"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.mcgill.org.za\/stuff\/wp-json\/wp\/v2\/categories?post=793"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.mcgill.org.za\/stuff\/wp-json\/wp\/v2\/tags?post=793"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}