Issue 1149: CREATE FUNCTION nextval_account_id () fails if MySQL binlogs are enabled?
Status:  Accepted
Owner: ----
Reported by agafgf...@gmail.com, Oct 11, 2011
Affected Version:2.2.1

What steps will reproduce the problem?
1.Enable binlogs on MySQL
2.Install Gerrit
3.Watch it explode

Behind reverse proxy           [y/N]? 
Use SSL (https://             [y/N]? 
Listen on address              [*]: 
Listen on port                 [8080]: 

Exception in thread "main" com.google.gwtorm.client.OrmException: Error in mysql_nextval.sql:
CREATE FUNCTION nextval_account_id ()
  RETURNS BIGINT
  LANGUAGE SQL
  NOT DETERMINISTIC
  MODIFIES SQL DATA
BEGIN
  INSERT INTO account_id (s) VALUES (NULL);
  DELETE FROM account_id WHERE s = LAST_INSERT_ID();
  RETURN LAST_INSERT_ID();
END;

        at com.google.gerrit.server.schema.ScriptRunner.run(ScriptRunner.java:55)
        at com.google.gerrit.server.schema.SchemaCreator.create(SchemaCreator.java:126)
        at com.google.gerrit.server.schema.SchemaUpdater.update(SchemaUpdater.java:55)
        at com.google.gerrit.pgm.Init$SiteRun.upgradeSchema(Init.java:181)
        at com.google.gerrit.pgm.Init.run(Init.java:79)
        at com.google.gerrit.pgm.util.AbstractProgram.main(AbstractProgram.java:76)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:60)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:37)
        at java.lang.reflect.Method.invoke(Method.java:611)
        at com.google.gerrit.launcher.GerritLauncher.invokeProgram(GerritLauncher.java:155)
        at com.google.gerrit.launcher.GerritLauncher.mainImpl(GerritLauncher.java:89)
        at com.google.gerrit.launcher.GerritLauncher.main(GerritLauncher.java:47)
        at Main.main(Main.java:25)
Caused by: java.sql.SQLException: 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)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1055)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3558)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3490)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1959)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2109)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2642)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2571)
        at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:782)
        at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:625)
        at com.google.gerrit.server.schema.ScriptRunner.run(ScriptRunner.java:53)
        ... 13 more
Oct 11, 2011
#1 sop@google.com
This function is only a utility for the administrator.

I'm not sure what I can do here, other than tell you not to create that function in your server. MySQL doesn't support functions in binlog mode that impact SQL data. I guess because nobody would ever want to create a function in the database that does some standard operation.
Oct 11, 2011
#2 agafgf...@gmail.com
Perhaps discontinue support for MySQL databases?  At the very least, the docs could mention this incompatibility with binlogs.
Jan 31, 2012
#3 android....@gmail.com
Seems a bit abrupt to suggest discontinuing support - how many methods would need rewriting? Could the nondeterminism and need to write to the database be separated?
Apr 26, 2012
#4 patrik.l...@vismadevtrend.com
I managed to get gerrit running on a MySQL database with binlog enabled by this cumbersome workaround:
- Install gerrit on a MySQL server with binlog DISABLED
- Export gerrit database (make sure to include functions)
- In the exported SQL there is a function called "nextval_account_id", change the string "MODIFIES SQL DATA" to "READS SQL DATA" in the definition of this function

DISCLAIMER: I just managed to get gerrit up and running, I have no idea wether it is really functioning as intended with this change. However, I managed to register, so judging by the name of the function (nextval_account_id) it is not utilized, or they have a fallback mechanism, or worst case user-ids are not binlogged in MySQL.
gerrit2.sql
24.5 KB   View   Download
Apr 26, 2012
#5 sop@google.com
The function is not used by the server, its there only for an admin doing ad-hoc user creation that might need to know how to create an account_id value. So long as you don't call it yourself, installing with this work-around would work.
Status: Accepted
Apr 30, 2012
#6 patrik.l...@vismadevtrend.com
I also had problems with having Gerrit populating a functional MySQL database for me, and I solved this by getting all up and running with the built in H2 database, and then export everything into MySQL and changing the database connection settings. Took about half an hour.

Don't forget that you need to edit both etc/gerrit.config and etc/secure.config to add MySQL configuration when you are done exporting.

I exported the H2 database by SSH:ing to it:
$ ssh -p 29418 user@gerrit-host gerrit gsql
and the executed the following CALL CSVWRITE() calls which will export all tables as CSV in the gerrit root directory. These can then be imported into MySQL.

CALL CSVWRITE('ACCOUNTS.csv', 'SELECT * FROM ACCOUNTS');
CALL CSVWRITE('ACCOUNT_AGREEMENTS.csv', 'SELECT * FROM ACCOUNT_AGREEMENTS');
CALL CSVWRITE('ACCOUNT_DIFF_PREFERENCES.csv', 'SELECT * FROM ACCOUNT_DIFF_PREFERENCES');
CALL CSVWRITE('ACCOUNT_EXTERNAL_IDS.csv', 'SELECT * FROM ACCOUNT_EXTERNAL_IDS');
CALL CSVWRITE('ACCOUNT_GROUPS.csv', 'SELECT * FROM ACCOUNT_GROUPS');
CALL CSVWRITE('ACCOUNT_GROUP_AGREEMENTS.csv', 'SELECT * FROM ACCOUNT_GROUP_AGREEMENTS');
CALL CSVWRITE('ACCOUNT_GROUP_INCLUDES.csv', 'SELECT * FROM ACCOUNT_GROUP_INCLUDES');
CALL CSVWRITE('ACCOUNT_GROUP_INCLUDES_AUDIT.csv', 'SELECT * FROM ACCOUNT_GROUP_INCLUDES_AUDIT');
CALL CSVWRITE('ACCOUNT_GROUP_MEMBERS.csv', 'SELECT * FROM ACCOUNT_GROUP_MEMBERS');
CALL CSVWRITE('ACCOUNT_GROUP_MEMBERS_AUDIT.csv', 'SELECT * FROM ACCOUNT_GROUP_MEMBERS_AUDIT');
CALL CSVWRITE('ACCOUNT_GROUP_NAMES.csv', 'SELECT * FROM ACCOUNT_GROUP_NAMES');
CALL CSVWRITE('ACCOUNT_PATCH_REVIEWS.csv', 'SELECT * FROM ACCOUNT_PATCH_REVIEWS');
CALL CSVWRITE('ACCOUNT_PROJECT_WATCHES.csv', 'SELECT * FROM ACCOUNT_PROJECT_WATCHES');
CALL CSVWRITE('ACCOUNT_SSH_KEYS.csv', 'SELECT * FROM ACCOUNT_SSH_KEYS');
CALL CSVWRITE('APPROVAL_CATEGORIES.csv', 'SELECT * FROM APPROVAL_CATEGORIES');
CALL CSVWRITE('APPROVAL_CATEGORY_VALUES.csv', 'SELECT * FROM APPROVAL_CATEGORY_VALUES');
CALL CSVWRITE('CHANGES.csv', 'SELECT * FROM CHANGES');
CALL CSVWRITE('CHANGE_MESSAGES.csv', 'SELECT * FROM CHANGE_MESSAGES');
CALL CSVWRITE('CONTRIBUTOR_AGREEMENTS.csv', 'SELECT * FROM CONTRIBUTOR_AGREEMENTS');
CALL CSVWRITE('PATCH_COMMENTS.csv', 'SELECT * FROM PATCH_COMMENTS');
CALL CSVWRITE('PATCH_SETS.csv', 'SELECT * FROM PATCH_SETS');
CALL CSVWRITE('PATCH_SET_ANCESTORS.csv', 'SELECT * FROM PATCH_SET_ANCESTORS');
CALL CSVWRITE('PATCH_SET_APPROVALS.csv', 'SELECT * FROM PATCH_SET_APPROVALS');
CALL CSVWRITE('SCHEMA_VERSION.csv', 'SELECT * FROM SCHEMA_VERSION');
CALL CSVWRITE('STARRED_CHANGES.csv', 'SELECT * FROM STARRED_CHANGES');
CALL CSVWRITE('SUBMODULE_SUBSCRIPTIONS.csv', 'SELECT * FROM SUBMODULE_SUBSCRIPTIONS');
CALL CSVWRITE('SYSTEM_CONFIG.csv', 'SELECT * FROM SYSTEM_CONFIG');
CALL CSVWRITE('TRACKING_IDS.csv', 'SELECT * FROM TRACKING_IDS');

After importing this into MySQL you need to manually set dates which are '0000-00-00 00:00:00' to NULL. I found these zero-dates in the ACCOUNTS table.
Apr 30, 2012
#7 patrik.l...@vismadevtrend.com
In reply to mysqlf in @6, it seems this is not working... For some reason I get a blank page when loggin into gerrit after this stunt. Tried to reset cache and manualy set the autoincrement in MySQL, but I kind of lost track now...
MySQL support looks broken to me.
Nov 14, 2012
#8 zh.jli...@gmail.com
Got a tip from https://chanmingman.wordpress.com/2011/08/23/this-function-has-none-of-the-deterministic-no-sql-or-reads-sql-data-in-its-declaration-and-binary-logging-is-enable-you-might-want-to-use-the-less-safe-log_bin_trust_function_creators-variable/

Issue the follow command in MySQL console.
SET GLOBAL log_bin_trust_function_creators  = 1;

After Initialization:
SET GLOBAL log_bin_trust_function_creators  = 0;