Issue 1330: When adding rights for a group, sql error happens
Status:  Duplicate
Merged:  issue 1273
Owner: ----
Closed:  May 2012
Reported by samu.vou...@gmail.com, Apr 16, 2012
Affected Version: 2.3

What steps will reproduce the problem?
1. Install standard MySQL configured for UTF-8
2. Create database and set its charset to latin1
3. Install Gerrit
4. Go to project management (Admin -> Projects -> [Project] -> Access -> Edit -> Add Reference
5. Try to write the group’s name in Group Name field

What is the expected output? What do you see instead?
Group to be added the privilege.

Following error in error_log:

e
[2012-04-16 11:30:02,487] WARN  / : Error in suggestAccountGroup
com.google.gwtorm.client.OrmException: fetch failure on account_group_names
        at com.google.gwtorm.schema.sql.SqlDialect.convertError(SqlDialect.java:150)
        at com.google.gwtorm.jdbc.JdbcAccess.convertError(JdbcAccess.java:298)
        at com.google.gwtorm.jdbc.JdbcAccess.queryList(JdbcAccess.java:145)
        at com.google.gerrit.reviewdb.AccountGroupName_Access_accountGroupNames_GwtOrm$$11.suggestByName(Unknown Source)
        at com.google.gerrit.httpd.rpc.SuggestServiceImpl.suggestAccountGroup(SuggestServiceImpl.java:229)
        at com.google.gerrit.httpd.rpc.SuggestServiceImpl.access$100(SuggestServiceImpl.java:54)
        at com.google.gerrit.httpd.rpc.SuggestServiceImpl$2.run(SuggestServiceImpl.java:217)
        at com.google.gerrit.httpd.rpc.SuggestServiceImpl$2.run(SuggestServiceImpl.java:215)
        at com.google.gerrit.httpd.rpc.BaseServiceImplementation.run(BaseServiceImplementation.java:63)
        at com.google.gerrit.httpd.rpc.SuggestServiceImpl.suggestAccountGroup(SuggestServiceImpl.java:215)
        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:616)
        at com.google.gwtjsonrpc.server.MethodHandle.invoke(MethodHandle.java:91)
        at com.google.gwtjsonrpc.server.JsonServlet.doService(JsonServlet.java:382)
        at com.google.gwtjsonrpc.server.JsonServlet.service(JsonServlet.java:268)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
        at com.google.inject.servlet.ServletDefinition.doService(ServletDefinition.java:216)
        at com.google.inject.servlet.ServletDefinition.service(ServletDefinition.java:141)
        at com.google.inject.servlet.ManagedServletPipeline.service(ManagedServletPipeline.java:93)
        at com.google.inject.servlet.FilterChainInvocation.doFilter(FilterChainInvocation.java:63)
        at com.google.inject.servlet.FilterDefinition.doFilter(FilterDefinition.java:134)
        at com.google.inject.servlet.FilterChainInvocation.doFilter(FilterChainInvocation.java:59)
        at com.google.inject.servlet.FilterDefinition.doFilter(FilterDefinition.java:134)
        at com.google.inject.servlet.FilterChainInvocation.doFilter(FilterChainInvocation.java:59)
        at com.google.gwtexpui.server.CacheControlFilter.doFilter(CacheControlFilter.java:76)
        at com.google.inject.servlet.FilterDefinition.doFilter(FilterDefinition.java:129)
        at com.google.inject.servlet.FilterChainInvocation.doFilter(FilterChainInvocation.java:59)
        at com.google.gerrit.httpd.RequestCleanupFilter.doFilter(RequestCleanupFilter.java:54)
        at com.google.inject.servlet.FilterDefinition.doFilter(FilterDefinition.java:129)
        at com.google.inject.servlet.FilterChainInvocation.doFilter(FilterChainInvocation.java:59)
        at com.google.inject.servlet.FilterDefinition.doFilter(FilterDefinition.java:134)
        at com.google.inject.servlet.FilterChainInvocation.doFilter(FilterChainInvocation.java:59)
        at com.google.inject.servlet.ManagedFilterPipeline.dispatch(ManagedFilterPipeline.java:122)
        at com.google.inject.servlet.GuiceFilter.doFilter(GuiceFilter.java:110)
        at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1322)
        at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:473)
        at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:921)
        at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:403)
        at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:856)
        at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:117)
        at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:114)
        at org.eclipse.jetty.server.Server.handle(Server.java:352)
        at org.eclipse.jetty.server.HttpConnection.handleRequest(HttpConnection.java:596)
        at org.eclipse.jetty.server.HttpConnection$RequestHandler.content(HttpConnection.java:1069)
        at org.eclipse.jetty.http.HttpParser.parseNext(HttpParser.java:805)
        at org.eclipse.jetty.http.HttpParser.parseAvailable(HttpParser.java:218)
        at org.eclipse.jetty.server.HttpConnection.handle(HttpConnection.java:426)
        at org.eclipse.jetty.io.nio.SelectChannelEndPoint.handle(SelectChannelEndPoint.java:510)
        at org.eclipse.jetty.io.nio.SelectChannelEndPoint.access$000(SelectChannelEndPoint.java:34)
        at org.eclipse.jetty.io.nio.SelectChannelEndPoint$1.run(SelectChannelEndPoint.java:40)
        at org.eclipse.jetty.util.thread.QueuedThreadPool$2.run(QueuedThreadPool.java:450)
        at java.lang.Thread.run(Thread.java:679)
Caused by: java.sql.SQLException: Illegal mix of collations (latin1_bin,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '<='
        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:2648)
        at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2077)
        at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2219)
        at com.google.gwtorm.jdbc.JdbcAccess.queryList(JdbcAccess.java:133)
        ... 51 more

May 15, 2012
#1 sop@google.com
(No comment was entered for this change.)
Status: Duplicate
Mergedinto: 1273
May 16, 2012
#2 noth...@gmail.com
How does this issue relate to #1273?

1273 is talking about key length limitation and they claim to fixed this by switching to InnoDB.

This issue is about "Illegal mix of collations" and the MySQL database is already in InnoDB and also with the latin1 charset set.
May 16, 2012
#3 noth...@gmail.com
Oh initializing the database as utf8 solved the issue......

That is, for the block below:

  CREATE USER 'gerrit2'@'localhost' IDENTIFIED BY 'secret';
  CREATE DATABASE reviewdb;
  ALTER DATABASE reviewdb charset=latin1;
  GRANT ALL ON reviewdb.* TO 'gerrit2'@'localhost';
  FLUSH PRIVILEGES;

Change to(or omit the alter database reviewdb charset line to use the default..?):

  CREATE USER 'gerrit2'@'localhost' IDENTIFIED BY 'secret';
  CREATE DATABASE reviewdb;
  ALTER DATABASE reviewdb charset=utf8;
  GRANT ALL ON reviewdb.* TO 'gerrit2'@'localhost';
  FLUSH PRIVILEGES;


Possible reason:

1. As my MySQL (5.1.48) uses utf8, if the variable binding in a query is then treat as utf8, when creating a table after latin1 is set to the database, perhaps this will cause the 'Illegal mix of collations' problem.

2. The Google gwtorm library may specifically set the variable binding as utf-8 that MySQL expect to match with the table's charset thus raising the error while this isn't the case.

So anyway this is a weird issue of MySQL......
May 17, 2012
#4 samu.vou...@gmail.com
The thing is that database can’t be left to be utf8 (as it is by default), since the  issue 1273  arises.

I messed up with some things and as forced to reinitialize whole database but looks, that I managed to avoid this issue for this time... I took new 2.4-rc0 too though, maybe it did something differently. 
Jun 12, 2012
#5 samu.vou...@gmail.com
Okay, the issue is still there... With 2.4. Same fix applies: changing in table account_group_names the field name from latin1_bin to utf8_bin.