I'm attempting to install naflm-2.0-Spike10.0. When I attempt to setup the DB, I get a failure creating the tours table. Below, it says that it's most likely because I do not have the CREATE ROUTINE privilege. When I check my grants, it looks like I have all the privileges required:
Grants for 'root'@'localhost'
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON . TO 'root'@'localhost' WITH GRANT OPTION
RESULTS:
Creating core tables...
OK — coaches
OK — teams
OK — players
OK — memberships
OK — players_skills
OK — races
OK — leagues
OK — divisions
FAILED — tours
OK — matches
OK — match_data
OK — texts
OK — game_data_players
OK — game_data_stars
OK — game_data_skills
OK — mv_players
OK — mv_teams
OK — mv_coaches
OK — mv_races
OK — mv_es_players
OK — mv_es_teams
OK — mv_es_coaches
OK — mv_es_races
OK — match_data_es
Creating module tables...
OK — wanted
OK — hof
OK — prizes
OK — log
OK — conferences
OK — conference_teams
OK — league_prefs
OK — cemetery
OK — famousteams
Other tasks...
OK — Synchronize game data with database
FAILED — could not apply one more more table indexes
FATAL ERROR: One or more OBBLM MySQL functions/procedures could not be created. This error is most likely due to your database user NOT having the "CREATE ROUTINE" privilege. Some web hosts are willing to help you work around this problem by running this install/upgrade script for you. If not, you will have to find another web host allowing "CREATE ROUTINE".
MySQL error (errno 1418):
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)
The function/procedure SQL code that failed was:
CREATE FUNCTION syncELOMatch(trid MEDIUMINT UNSIGNED, mid MEDIUMINT SIGNED) RETURNS BOOLEAN NOT DETERMINISTIC CONTAINS SQL BEGIN DECLARE Rt1_0, Rt2_0, Rc1_0, Rc2_0, Rt1, Rt2, Rc1, Rc2 FLOAT; DECLARE Et1, Et2, Ec1, Ec2 FLOAT; DECLARE S1, S2 FLOAT; DECLARE tid1, tid2 MEDIUMINT UNSIGNED; DECLARE cid1, cid2 MEDIUMINT UNSIGNED; SELECT team1_id, team2_id, t1.owned_by_coach_id, t2.owned_by_coach_id, IF(team1_score = team2_score, 0.5, IF(team1_score > team2_score,1,0)), IF(team1_score = team2_score, 0.5, IF(team1_score < team2_score,1,0)) INTO tid1, tid2, cid1, cid2, S1, S2 FROM matches, teams AS t1, teams AS t2 WHERE match_id = mid AND team1_id = t1.team_id AND team2_id = t2.team_id; IF trid IS NULL THEN SELECT IF(IFNULL((SELECT SUM(played) FROM mv_teams WHERE f_tid = tid1 ),FALSE) AND IFNULL(teams.elo,FALSE), teams.elo, 200) INTO Rt1_0 FROM teams WHERE team_id = tid1; SELECT IF(IFNULL((SELECT SUM(played) FROM mv_teams WHERE f_tid = tid2 ),FALSE) AND IFNULL(teams.elo,FALSE), teams.elo, 200) INTO Rt2_0 FROM teams WHERE team_id = tid2; SELECT IF(IFNULL((SELECT SUM(played) FROM mv_coaches WHERE f_cid = cid1 ),FALSE) AND IFNULL(coaches.elo,FALSE), coaches.elo, 200) INTO Rc1_0 FROM coaches WHERE coach_id = cid1; SELECT IF(IFNULL((SELECT SUM(played) FROM mv_coaches WHERE f_cid = cid2 ),FALSE) AND IFNULL(coaches.elo,FALSE), coaches.elo, 200) INTO Rc2_0 FROM coaches WHERE coach_id = cid2; ELSE SELECT IF(IFNULL((SELECT SUM(played) FROM mv_teams WHERE f_tid = tid1 AND f_trid = trid),FALSE) AND IFNULL(teams.elo,FALSE), teams.elo, 200) INTO Rt1_0 FROM teams WHERE team_id = tid1; SELECT IF(IFNULL((SELECT SUM(played) FROM mv_teams WHERE f_tid = tid2 AND f_trid = trid),FALSE) AND IFNULL(teams.elo,FALSE), teams.elo, 200) INTO Rt2_0 FROM teams WHERE team_id = tid2; SELECT IF(IFNULL((SELECT SUM(played) FROM mv_coaches WHERE f_cid = cid1 AND f_trid = trid),FALSE) AND IFNULL(coaches.elo,FALSE), coaches.elo, 200) INTO Rc1_0 FROM coaches WHERE coach_id = cid1; SELECT IF(IFNULL((SELECT SUM(played) FROM mv_coaches WHERE f_cid = cid2 AND f_trid = trid),FALSE) AND IFNULL(coaches.elo,FALSE), coaches.elo, 200) INTO Rc2_0 FROM coaches WHERE coach_id = cid2; END IF; SET Et1 = ELO_E(Rt1_0, Rt2_0); SET Et2 = ELO_E(Rt2_0, Rt1_0); SET Ec1 = ELO_E(Rc1_0, Rc2_0); SET Ec2 = ELO_E(Rc2_0, Rc1_0); SET Rt1 = ELO_R(Rt1_0, S1, Et1); SET Rt2 = ELO_R(Rt2_0, S2, Et2); SET Rc1 = ELO_R(Rc1_0, S1, Ec1); SET Rc2 = ELO_R(Rc2_0, S2, Ec2); IF trid IS NULL THEN UPDATE teams SET elo = Rt1 WHERE team_id = tid1; UPDATE teams SET elo = Rt2 WHERE team_id = tid2; UPDATE coaches SET elo = Rc1 WHERE coach_id = cid1; UPDATE coaches SET elo = Rc2 WHERE coach_id = cid2; ELSE UPDATE mv_teams SET elo = Rt1 WHERE f_trid = trid AND f_tid = tid1; UPDATE mv_teams SET elo = Rt2 WHERE f_trid = trid AND f_tid = tid2; UPDATE mv_coaches SET elo = Rc1 WHERE f_trid = trid AND f_cid = cid1; UPDATE mv_coaches SET elo = Rc2 WHERE f_trid = trid AND f_cid = cid2; END IF; RETURN TRUE; END
jacopocanovi
commented
9 months ago
I'm attempting to install naflm-2.0-Spike10.0. When I attempt to setup the DB, I get a failure creating the tours table. Below, it says that it's most likely because I do not have the CREATE ROUTINE privilege. When I check my grants, it looks like I have all the privileges required:
Grants for 'root'@'localhost' | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON . TO 'root'@'localhost' WITH GRANT OPTION
RESULTS:
Creating core tables... OK — coaches OK — teams OK — players OK — memberships OK — players_skills OK — races OK — leagues OK — divisions FAILED — tours OK — matches OK — match_data OK — texts OK — game_data_players OK — game_data_stars OK — game_data_skills OK — mv_players OK — mv_teams OK — mv_coaches OK — mv_races OK — mv_es_players OK — mv_es_teams OK — mv_es_coaches OK — mv_es_races OK — match_data_es Creating module tables... OK — wanted OK — hof OK — prizes OK — log OK — conferences OK — conference_teams OK — league_prefs OK — cemetery OK — famousteams Other tasks... OK — Synchronize game data with database FAILED — could not apply one more more table indexes
FATAL ERROR: One or more OBBLM MySQL functions/procedures could not be created. This error is most likely due to your database user NOT having the "CREATE ROUTINE" privilege. Some web hosts are willing to help you work around this problem by running this install/upgrade script for you. If not, you will have to find another web host allowing "CREATE ROUTINE".
MySQL error (errno 1418): 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)
The function/procedure SQL code that failed was: CREATE FUNCTION syncELOMatch(trid MEDIUMINT UNSIGNED, mid MEDIUMINT SIGNED) RETURNS BOOLEAN NOT DETERMINISTIC CONTAINS SQL BEGIN DECLARE Rt1_0, Rt2_0, Rc1_0, Rc2_0, Rt1, Rt2, Rc1, Rc2 FLOAT; DECLARE Et1, Et2, Ec1, Ec2 FLOAT; DECLARE S1, S2 FLOAT; DECLARE tid1, tid2 MEDIUMINT UNSIGNED; DECLARE cid1, cid2 MEDIUMINT UNSIGNED; SELECT team1_id, team2_id, t1.owned_by_coach_id, t2.owned_by_coach_id, IF(team1_score = team2_score, 0.5, IF(team1_score > team2_score,1,0)), IF(team1_score = team2_score, 0.5, IF(team1_score < team2_score,1,0)) INTO tid1, tid2, cid1, cid2, S1, S2 FROM matches, teams AS t1, teams AS t2 WHERE match_id = mid AND team1_id = t1.team_id AND team2_id = t2.team_id; IF trid IS NULL THEN SELECT IF(IFNULL((SELECT SUM(played) FROM mv_teams WHERE f_tid = tid1 ),FALSE) AND IFNULL(teams.elo,FALSE), teams.elo, 200) INTO Rt1_0 FROM teams WHERE team_id = tid1; SELECT IF(IFNULL((SELECT SUM(played) FROM mv_teams WHERE f_tid = tid2 ),FALSE) AND IFNULL(teams.elo,FALSE), teams.elo, 200) INTO Rt2_0 FROM teams WHERE team_id = tid2; SELECT IF(IFNULL((SELECT SUM(played) FROM mv_coaches WHERE f_cid = cid1 ),FALSE) AND IFNULL(coaches.elo,FALSE), coaches.elo, 200) INTO Rc1_0 FROM coaches WHERE coach_id = cid1; SELECT IF(IFNULL((SELECT SUM(played) FROM mv_coaches WHERE f_cid = cid2 ),FALSE) AND IFNULL(coaches.elo,FALSE), coaches.elo, 200) INTO Rc2_0 FROM coaches WHERE coach_id = cid2; ELSE SELECT IF(IFNULL((SELECT SUM(played) FROM mv_teams WHERE f_tid = tid1 AND f_trid = trid),FALSE) AND IFNULL(teams.elo,FALSE), teams.elo, 200) INTO Rt1_0 FROM teams WHERE team_id = tid1; SELECT IF(IFNULL((SELECT SUM(played) FROM mv_teams WHERE f_tid = tid2 AND f_trid = trid),FALSE) AND IFNULL(teams.elo,FALSE), teams.elo, 200) INTO Rt2_0 FROM teams WHERE team_id = tid2; SELECT IF(IFNULL((SELECT SUM(played) FROM mv_coaches WHERE f_cid = cid1 AND f_trid = trid),FALSE) AND IFNULL(coaches.elo,FALSE), coaches.elo, 200) INTO Rc1_0 FROM coaches WHERE coach_id = cid1; SELECT IF(IFNULL((SELECT SUM(played) FROM mv_coaches WHERE f_cid = cid2 AND f_trid = trid),FALSE) AND IFNULL(coaches.elo,FALSE), coaches.elo, 200) INTO Rc2_0 FROM coaches WHERE coach_id = cid2; END IF; SET Et1 = ELO_E(Rt1_0, Rt2_0); SET Et2 = ELO_E(Rt2_0, Rt1_0); SET Ec1 = ELO_E(Rc1_0, Rc2_0); SET Ec2 = ELO_E(Rc2_0, Rc1_0); SET Rt1 = ELO_R(Rt1_0, S1, Et1); SET Rt2 = ELO_R(Rt2_0, S2, Et2); SET Rc1 = ELO_R(Rc1_0, S1, Ec1); SET Rc2 = ELO_R(Rc2_0, S2, Ec2); IF trid IS NULL THEN UPDATE teams SET elo = Rt1 WHERE team_id = tid1; UPDATE teams SET elo = Rt2 WHERE team_id = tid2; UPDATE coaches SET elo = Rc1 WHERE coach_id = cid1; UPDATE coaches SET elo = Rc2 WHERE coach_id = cid2; ELSE UPDATE mv_teams SET elo = Rt1 WHERE f_trid = trid AND f_tid = tid1; UPDATE mv_teams SET elo = Rt2 WHERE f_trid = trid AND f_tid = tid2; UPDATE mv_coaches SET elo = Rc1 WHERE f_trid = trid AND f_cid = cid1; UPDATE mv_coaches SET elo = Rc2 WHERE f_trid = trid AND f_cid = cid2; END IF; RETURN TRUE; END