#3 SQL Error after login

[qirex]

Immediately after logging in to Church Rota I am presented with the following SQL error:

Error: This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you might want to use the less safe log_bin_trust_function_creators variable), SQL: CREATE FUNCTION getBrowserInfo (user_agent VARCHAR(255)) RETURNS VARCHAR(100) BEGIN DECLARE v_browser,v_os VARCHAR(20); DECLARE v_agent VARCHAR(255); SET v_browser = 'OTHER'; SET v_os = 'OTHER'; select detail3 into v_agent from cr_statistics where detail1='login' order by date desc limit 1; if (user_agent = '-') then SET v_agent = upper(v_agent); else SET v_agent = upper(user_agent); end if; if (instr(v_agent,'IE')>0) then set v_browser= 'IE'; elseif (instr(v_agent,'OPERA')>0) then set v_browser= 'OPERA'; elseif (instr(v_agent,'NETSCAPE')>0) then set v_browser= 'NETSCAPE'; elseif (instr(v_agent,'FIREFOX')>0) then set v_browser= 'FIREFOX'; elseif (instr(v_agent,'FLOCK')>0) then set v_browser= 'FLOCK'; elseif (instr(v_agent,'CHROME')>0) then set v_browser= 'CHROME'; elseif (instr(v_agent,'SAFARI')>0) then set v_browser= 'SAFARI'; elseif (instr(v_agent,'MOZILLA')>0) then set v_browser= 'MOZILLA'; end if; if (instr(v_agent,'WINDOWS')>0) then set v_os= 'WINDOWS'; elseif (instr(v_agent,'IPHONE')>0) then set v_os= 'IPHONE'; elseif (instr(v_agent,'IPAD')>0) then set v_os= 'IPAD'; elseif (instr(v_agent,'ANDROID')>0) then set v_os= 'ANDROID'; elseif (instr(v_agent,'MAC')>0) then set v_os= 'MAC'; elseif (instr(v_agent,'LINUX')>0) then set v_os= 'LINUX'; end if; RETURN CONCAT(v_browser ,' / ',v_os); END;

I have found this info in the MySQL docs which may be of some help:

http://dev.mysql.com/doc/refman/5.0/en/stored-programs-logging.html

(Bullet point 'When you create a stored function, you must declare either that it is deterministic or that it does not modify data. Otherwise, it may be unsafe for data recovery or replication.')

My Environment:

Server OS: Debian 7

[Benjamin Schmitt]

Please tell me the exact version of mysql that you are using.
I am using 5.5.31 productive without problems.

The problem seems to be that you are running mysql with binary logging enabled, i.e. replication or logging. As the message sais try to switch this feature off (set log_bin_trust_function_creators to 1), see http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_log_bin_trust_function_creators

Details on replications http://dev.mysql.com/doc/refman/5.1/en/replication-features.html

I am going to mark that function with the DETERMINISTIC, that should also help avoiding that problem, when log_bin_trust_function_creators=0.

[Benjamin Schmitt]

I've uploaded new version to svn trunk (Rev. 40, V. 2.4.5).
Please check it.

If it works for your situation, then please inform me, so that I can release a new downlod ZIP. Thanks for reporting that issue.

[qirex]

Great thanks, I will try latest svn.

Great open source project BTW : )

[qirex]

Fixed in svn trunk (Rev. 40, V. 2.4.5).

Although one the following mysql variables may need to be set when using replication to avoid the error below:

binlog_format = mixed
log_bin_trust_function_creators = 1

---

Error: You do not have the SUPER privilege and binary logging is enabled (you might want to use the less safe log_bin_trust_function_creators variable), SQL: CREATE FUNCTION getBrowserInfo (user_agent VARCHAR(255)) RETURNS VARCHAR(100) DETERMINISTIC BEGIN DECLARE v_browser,v_os VARCHAR(20); DECLARE v_agent VARCHAR(255); SET v_browser = 'OTHER'; SET v_os = 'OTHER'; select detail3 into v_agent from cr_statistics where detail1='login' order by date desc limit 1; if (user_agent = '-') then SET v_agent = upper(v_agent); else SET v_agent = upper(user_agent); end if; if (instr(v_agent,'IE')>0) then set v_browser= 'IE'; elseif (instr(v_agent,'OPERA')>0) then set v_browser= 'OPERA'; elseif (instr(v_agent,'NETSCAPE')>0) then set v_browser= 'NETSCAPE'; elseif (instr(v_agent,'FIREFOX')>0) then set v_browser= 'FIREFOX'; elseif (instr(v_agent,'FLOCK')>0) then set v_browser= 'FLOCK'; elseif (instr(v_agent,'CHROME')>0) then set v_browser= 'CHROME'; elseif (instr(v_agent,'SAFARI')>0) then set v_browser= 'SAFARI'; elseif (instr(v_agent,'MOZILLA')>0) then set v_browser= 'MOZILLA'; end if; if (instr(v_agent,'WINDOWS')>0) then set v_os= 'WINDOWS'; elseif (instr(v_agent,'IPHONE')>0) then set v_os= 'IPHONE'; elseif (instr(v_agent,'IPAD')>0) then set v_os= 'IPAD'; elseif (instr(v_agent,'ANDROID')>0) then set v_os= 'ANDROID'; elseif (instr(v_agent,'MAC')>0) then set v_os= 'MAC'; elseif (instr(v_agent,'LINUX')>0) then set v_os= 'LINUX'; end if; RETURN CONCAT(v_browser ,' / ',v_os); END;

---