SQL exception: Illegal mix of collations UTF8 on Upgrade from 5.0.3 to 5.1.2

Ran into an issue while trying to upgrade an existing TeamCity deployment.
(A completely new issue from the previous thread, so I felt it was more appropriate to raise a new thread in order to reduce confusion.)

Following these directions "Manual Upgrading on Linux and for WAR Distributions":http://confluence.jetbrains.net/display/TCD5/Upgrade

Original version:5.0.3 (BUILD_10821)
Original db_version: 346

Upgrading to: 5.1.2 (BUILD_13430)
db_version after exception: 364


Expected db_version: 378

Full teamcity-server.log attached

Snippet below for convenience:



[2010-06-25 19:03:48,955]   INFO - Side.versioning.VersionManager - Start applying converter Split agent_type table into agent_type and agent_type_info (364)
[2010-06-25 19:03:48,978]   INFO - Side.versioning.VersionManager - Done applying converter Split agent_type table into agent_type and agent_type_info
[2010-06-25 19:03:48,979]   INFO - Side.versioning.VersionManager - Start applying converter Add agent_type_id column into agent table. Remove obsolete agentT
ypeKey columns (365)
[2010-06-25 19:03:49,005]  ERROR -   jetbrains.buildServer.SERVER - Exception occured while applying converters
jetbrains.buildServer.serverSide.versioning.ConvertException: Converter jetbrains.buildServer.serverSide.versioning.converters.AddAgentTypeIdToAgentsTableConv
erter failed with error: SQL error when doing: DML query
SQL query: insert into agent   (id, name, host_addr, port, status, authorized, registered, registration_timestamp,    last_binding_timestamp, unregistered_rea
son, authorization_token, status_to_restore,    status_restoring_timestamp, agent_type_id  ) select   o.id, o.name, o.host_addr, o.port, o.status, o.authorize
d, o.registered, o.registration_timestamp,   o.last_binding_timestamp, o.unregistered_reason, o.authorization_token, o.status_to_restore,   o.status_restoring
_timestamp, g.agent_type_id   from agent_old o left join agent_type g       on  o.agent_type_cloud_code = g.cloud_code       and o.agent_type_profile_id = g.p
rofile_id       and o.agent_type_type_id = g.image_id
SQL exception: Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '='

Any suggestions?

Is this worth submitting a bug tracker request for?



Attachment(s):
teamcity-server.log.zip
13 comments

I have the same problem. Only with different collation:

SQL exception: Illegal mix of collations (utf8_czech_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '=';

I had this problem also with PHP application and I solved it adding this query after DB connect:

SET NAMES "utf8" COLLATE "utf8_czech_ci"

I only set default collation if is not specified explicitly. This can help also with TeamCity, but I how to do this here.

0

Try to add:

connectionProperties.connectionCollation=utf8_general_ci
or
connectionProperties.connectionCollation=utf8_unicode_ci

to

c:\WINDOWS\system32\config\systemprofile\.BuildServer\config\database.properties

It works for me!

0

Angad,

could you please provide me with your 'database.properties' file (without passwords, of course). Thank you.

P.S. did you change it before migrating?

Leonid

0

Nothing speciall, only connectionCollation added

# Rename this file to database.properties to enable MySQL database
driverName=com.mysql.jdbc.Driver
connectionUrl=jdbc:mysql://localhost/t_teamcity
connectionProperties.user=***
connectionProperties.password=***

maxConnections=50
poolPreparedStatements=true

connectionProperties.useLocalSessionState=true
connectionProperties.elideSetAutoCommits=true
connectionProperties.alwaysSendSetIsolation=false

# uncomment if your database uses utf8 character set
connectionProperties.characterEncoding=UTF-8
connectionProperties.connectionCollation=utf8_czech_ci

0

Before we started the upgrade, we did notice that the database.properties had changed from 5.0.3 to 5.1.2, and we updated the new version of the database.properties with only the connection details, and left the remaining values default. We also added the following directory: .BuildServer/lib/jdbc/mysql-connector-java-5.1.10.jar



# This is a sample configuration file for configuring TeamCity to use external database.
# To make it effective, copy it to the "database.properties" file and modify settings
# according to your environment.
# Do not modify this file, it will be overwritten on TeamCity server startup.
# See documentation at http://www.jetbrains.net/confluence/display/TCD5/Setting+up+an+External+Database


# Database: MySQL


connectionUrl=jdbc:mysql:CONNECTION_URL
connectionProperties.user=USER
connectionProperties.password=PASSWORD


# The maximum number of connections TeamCity can open on database server.
maxConnections=50


# Specifies whether the driver should use Unicode character encodings
# when handling strings. Should only be used when the driver cannot determine
# the character set mapping, or you are trying to 'force' the
# driver to use unicode
#
#connectionProperties.useUnicode=true


# If 'useUnicode' is set to true, the following property determines what
# character encoding should the driver use when dealing with strings
#
#connectionProperties.characterEncoding=UTF-8


# Specifies whether TeamCity should check each jdbc connection before use.
# It reduces the system performace but avoids cases when the system uses
# a connection that was closed by database server due to time out or this
# connection was broken at the previous operation.
#testOnBorrow=true



This did not work, so we then tried to enable the following properties in the database.properties to see if it made any difference but no luck either:


connectionProperties.useUnicode=true
connectionProperties.characterEncoding=UTF-8

testOnBorrow=true



This is what we have now after the rollback using 5.0.3:

driverName=com.mysql.jdbc.Driver
connectionUrl=jdbc:mysql://CONNECTION_URL
connectionProperties.user=USER
connectionProperties.password=PASSWORD


maxConnections=50
#poolPreparedStatements=true


connectionProperties.useLocalSessionState=true
connectionProperties.elideSetAutoCommits=true
connectionProperties.alwaysSendSetIsolation=false


# uncomment if your database uses utf8 character set
#connectionProperties.characterEncoding=UTF-8

Are there any other files that need modifying during the upgrade?


0

I'm going to try this and see if it works for us also, will let you know soon what the results are.

Thanks.

0

I tried to add: connectionProperties.connectionCollation=utf8_czech_ci and connectionProperties.connectionCollation=utf8_unicode_ci to the database.properties (both separate times),  but kept getting the same error. I suspect that the problem is that because the database may be using two different formatting styles, that it is getting stuck on either one of the formatting types. I’m going to see if I can find any information on converting the database to one style only, and then try the update again.
 
I'm not a SQL expert, but does anyone know if this is even possible?

Thanks in advance for the support.

0

I've been looking at our MySQL dump and im seeing the following for some tables:

SET character_set_client = utf8;
ENGINE=MyISAM DEFAULT CHARSET=utf8; (found 63 times in the MySQL dump file)

and other tables i see this:

SET character_set_client = utf8;
ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; (found 26 times in the MySQL dump file)

Could this be whats causing the llegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT)?

Any suggestions on how i can fix this? is there another way to perform the dump so that it format all the tables the same way?

Thanks,

0

Hi Angad,

could you please perform the following SQL query on your database

    select table_schema, table_name, table_collation
    from information_schema.tables;

both times - before updating and when error occured, and provide me with the results?

Thank you,

Leonid

0

Hi Angad,

> Could this be whats causing the llegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT)?

yes, it's the real reason of the problem.

--
Leonid

0

Thanks Leonid, that fixed the problem! I had to update my MySQL dump file to only use "ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci".  After restoring the database with this updated MySQL dump file, it upgraded to 5.1.2 without any issues, all of my configurations showed up. Our TeamCity is back in business!

Cheers,

Angad

0

And did you tried also this: connectionProperties.connectionCollation=utf8_general_ci

0

I tried adding:

connectionProperties.connectionCollation=utf8_general_ci
and
connectionProperties.connectionCollation=utf8_unicode_ci



to the database.properties but they didn't work. I added one at a time, I never tried with both at the same time, not sure if that would work? But we solved the issue after I changed the formatting style of the tables in the database to all use utf8 COLLATE=utf8_unicode_ci; it started up fine and applied all of the converters after that.

Thanks for your help!

Cheers,

Angad
0

Please sign in to leave a comment.