gerrit with mysql

[djph29]

Hi,

I am trying to configure gerrit with mysql (ver 5.7.12), but it fails with the following exception:

It may be related to the sql_mode setting? Please advise if anyone has encountered this.

Thanks

Exception in thread "main" com.google.gwtorm.server.OrmException: Cannot apply SQL

CREATE TABLE account_group_by_id_aud (

added_by INT DEFAULT 0 NOT NULL,

removed_by INT,

removed_on TIMESTAMP NULL DEFAULT NULL,

group_id INT DEFAULT 0 NOT NULL,

include_uuid VARCHAR(255) BINARY DEFAULT '' NOT NULL,

added_on TIMESTAMP NOT NULL

,PRIMARY KEY(group_id,include_uuid,added_on)

)

        at com.google.gwtorm.jdbc.JdbcExecutor.execute(JdbcExecutor.java:44)

        at com.google.gwtorm.jdbc.JdbcSchema.createRelations(JdbcSchema.java:119)

        at com.google.gwtorm.jdbc.JdbcSchema.updateSchema(JdbcSchema.java:89)

        at com.google.gerrit.server.schema.SchemaCreator.create(SchemaCreator.java:78)

        at com.google.gerrit.server.schema.SchemaUpdater.update(SchemaUpdater.java:100)

        at com.google.gerrit.pgm.init.BaseInit$SiteRun.upgradeSchema(BaseInit.java:341)

        at com.google.gerrit.pgm.init.BaseInit.run(BaseInit.java:127)

        at com.google.gerrit.pgm.util.AbstractProgram.main(AbstractProgram.java:64)

        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)

        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

        at java.lang.reflect.Method.invoke(Method.java:606)

        at com.google.gerrit.launcher.GerritLauncher.invokeProgram(GerritLauncher.java:159)

        at com.google.gerrit.launcher.GerritLauncher.mainImpl(GerritLauncher.java:100)

        at com.google.gerrit.launcher.GerritLauncher.main(GerritLauncher.java:56)

        at Main.main(Main.java:25)

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Invalid default value for 'added_on'

        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)

        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)

        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)

        at java.lang.reflect.Constructor.newInstance(Constructor.java:526)

        at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)

        at com.mysql.jdbc.Util.getInstance(Util.java:386)

        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1053)

        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4074)

        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4006)

        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2468)

        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2629)

        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2713)

        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2663)

        at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:888)

        at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:730)

        at com.google.gwtorm.jdbc.JdbcExecutor.execute(JdbcExecutor.java:42)

[djph29]

Anyone has seen this problem? (gerrit version is , os is redhat 6.7 x64).

Any pointers is appreciated.

[Ramon Fried]

I encounterd the same on Ubuntu Server 16.04 on a fresh install of Gerrit 2.12.2.

[djph29]

Solved the problem by setting the global sql mode and removing STRICT_ALL_TABLES, NO_ZERO_DATE from the default list

[Raja Subramanian N]

I got the same problem. I referred, http://www.blogarama.com/en/Programming-Blogs/331217-Devmain-Java-Mobile-Apps-Linux-And-More-Blog/3713087-gerrit-with-mysql-installation-guide (Step 9 and 11). 

[Neil Agarwal]

Same problem. Solved it putting this in /etc/mysql/my.cnf

[mysql]
sql_mode = "ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

Then,

sudo service mysql restart

Later,

Initialise the gerrit again and it should work.

We are basically changing the global sql mode and removing some unncessary stuff.

[林倩如]

It's likely that gerrit has failed to create some of the tables (if not then you can skip this step). Create the missing tables manually in the "reviewdb" database:

mysql> use reviewdb

mysql> CREATE TABLE account_group_by_id_aud (added_by INT DEFAULT 0 NOT NULL, removed_by INT, removed_on TIMESTAMP NULL DEFAULT NULL, group_id INT DEFAULT 0 NOT NULL, include_uuid VARCHAR(255) BINARY DEFAULT '' NOT NULL, added_on TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY(group_id,include_uuid,added_on));

mysql> CREATE TABLE account_group_members_audit (added_by INT DEFAULT 0 NOT NULL, removed_by INT, removed_on TIMESTAMP NULL DEFAULT NULL, account_id INT DEFAULT 0 NOT NULL, group_id INT DEFAULT 0 NOT NULL, added_on TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY(account_id,group_id,added_on));

mysql> CREATE TABLE changes (change_key VARCHAR(60) BINARY DEFAULT '' NOT NULL, created_on TIMESTAMP NOT NULL, last_updated_on TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, owner_account_id INT DEFAULT 0 NOT NULL, dest_project_name VARCHAR(255) BINARY DEFAULT '' NOT NULL, dest_branch_name VARCHAR(255) BINARY DEFAULT '' NOT NULL, status CHAR(1) DEFAULT ' ' NOT NULL, current_patch_set_id INT DEFAULT 0 NOT NULL, subject VARCHAR(255) BINARY DEFAULT '' NOT NULL, topic VARCHAR(255) BINARY, original_subject VARCHAR(255) BINARY, submission_id VARCHAR(255) BINARY, note_db_state TEXT, row_version INT DEFAULT 0 NOT NULL, change_id INT DEFAULT 0 NOT NULL ,PRIMARY KEY(change_id));

djph29於 2016年6月22日星期三 UTC+8下午11時56分00秒寫道：

[Weizhou Zhong]

thanks 林倩如.

[Giuseppe Salvatore]

Yes this fixed it for me. As far as I understand you need a DEFAULT for the timestamp. I ran the configuration tool and every time it was failing I was basically copying the query adding the missing default statement.

ありがとうございます 林倩如-san 

[Yubin Ruan]

This line

sql_mode = "ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

should be put under [mysqld], not [mysql]