New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

MySQL JDBC adapter fails to support utf8mb4 encoding #481

Closed
jdg opened this Issue Oct 19, 2013 · 12 comments

Comments

Projects
None yet
6 participants
@jdg

jdg commented Oct 19, 2013

Hi -

I need to use utf8mb4 encoding in order for MySQL to support 4 byte characters. A new encoding type, "utf8mb4" was introduced as of MySQL 5.5 to support this. However when I try and use an encoding type of "utf8mb4" in my database.yml, I receive the following exception after the first SQL request:

ActiveRecord::JDBCError: The driver encountered an unknown error: java.sql.SQLException: Unsupported character encoding 'utf8mb4'.

Am I missing something? Shouldn't this just "pass through" and work appropriately?

@jdg

This comment has been minimized.

Show comment
Hide comment
@jdg

jdg Oct 19, 2013

Further research shows that the way to handle utf8mb4 encoding is to specify character_set_server=utf8mb4 on the server. From there, we're supposed to not send a characterEncoding parameter as part of the connection string. This was taken from: http://bugs.mysql.com/bug.php?id=64823

If we choose to remove 'encoding' from the database.yml, it looks like ActiveRecord is by default specifying characterEncoding=utf8.

Suggestions on how to override that?

jdg commented Oct 19, 2013

Further research shows that the way to handle utf8mb4 encoding is to specify character_set_server=utf8mb4 on the server. From there, we're supposed to not send a characterEncoding parameter as part of the connection string. This was taken from: http://bugs.mysql.com/bug.php?id=64823

If we choose to remove 'encoding' from the database.yml, it looks like ActiveRecord is by default specifying characterEncoding=utf8.

Suggestions on how to override that?

@kares

This comment has been minimized.

Show comment
Hide comment
@kares

kares Oct 22, 2013

Member

thanks for the report ... not really - you will need to monkey-patch mysql_connection for now :

ArJdbc::ConnectionMethods.module_eval do
  def mysql_connection(config)
    begin
      require 'jdbc/mysql'
      ::Jdbc::MySQL.load_driver(:require) if defined?(::Jdbc::MySQL.load_driver)
    rescue LoadError # assuming driver.jar is on the class-path
    end

    config[:username] = 'root' unless config.key?(:username)
    # jdbc:mysql://[host][,failoverhost...][:port]/[database]
    # - if the host name is not specified, it defaults to 127.0.0.1
    # - if the port is not specified, it defaults to 3306
    # - alternate fail-over syntax: [host:port],[host:port]/[database]
    unless config[:url]
      host = config[:host]; host = host.join(',') if host.respond_to?(:join)
      url = "jdbc:mysql://#{host}"
      url << ":#{config[:port]}" if config[:port]
      url << "/#{config[:database]}"
      config[:url] = url
    end
    config[:driver] ||= defined?(::Jdbc::MySQL.driver_name) ? ::Jdbc::MySQL.driver_name : 'com.mysql.jdbc.Driver'
    config[:adapter_spec] ||= ::ArJdbc::MySQL
    config[:adapter_class] = ActiveRecord::ConnectionAdapters::MysqlAdapter unless config.key?(:adapter_class)

    properties = ( config[:properties] ||= {} )
    properties['zeroDateTimeBehavior'] ||= 'convertToNull'
    properties['jdbcCompliantTruncation'] ||= 'false'
    properties['useUnicode'] ||= 'true'
    if config.has_key?(:encoding)
      encoding = config[:encoding]
    else
      encoding = 'utf8'
    end
    properties['characterEncoding'] = encoding if encoding

    jdbc_connection(config)
  end
  alias_method :jdbcmysql_connection, :mysql_connection
  alias_method :mysql2_connection, :mysql_connection
end

... and set encoding: false for now - we'll need to investigate this if removing the utf8 default would not cause issues

Member

kares commented Oct 22, 2013

thanks for the report ... not really - you will need to monkey-patch mysql_connection for now :

ArJdbc::ConnectionMethods.module_eval do
  def mysql_connection(config)
    begin
      require 'jdbc/mysql'
      ::Jdbc::MySQL.load_driver(:require) if defined?(::Jdbc::MySQL.load_driver)
    rescue LoadError # assuming driver.jar is on the class-path
    end

    config[:username] = 'root' unless config.key?(:username)
    # jdbc:mysql://[host][,failoverhost...][:port]/[database]
    # - if the host name is not specified, it defaults to 127.0.0.1
    # - if the port is not specified, it defaults to 3306
    # - alternate fail-over syntax: [host:port],[host:port]/[database]
    unless config[:url]
      host = config[:host]; host = host.join(',') if host.respond_to?(:join)
      url = "jdbc:mysql://#{host}"
      url << ":#{config[:port]}" if config[:port]
      url << "/#{config[:database]}"
      config[:url] = url
    end
    config[:driver] ||= defined?(::Jdbc::MySQL.driver_name) ? ::Jdbc::MySQL.driver_name : 'com.mysql.jdbc.Driver'
    config[:adapter_spec] ||= ::ArJdbc::MySQL
    config[:adapter_class] = ActiveRecord::ConnectionAdapters::MysqlAdapter unless config.key?(:adapter_class)

    properties = ( config[:properties] ||= {} )
    properties['zeroDateTimeBehavior'] ||= 'convertToNull'
    properties['jdbcCompliantTruncation'] ||= 'false'
    properties['useUnicode'] ||= 'true'
    if config.has_key?(:encoding)
      encoding = config[:encoding]
    else
      encoding = 'utf8'
    end
    properties['characterEncoding'] = encoding if encoding

    jdbc_connection(config)
  end
  alias_method :jdbcmysql_connection, :mysql_connection
  alias_method :mysql2_connection, :mysql_connection
end

... and set encoding: false for now - we'll need to investigate this if removing the utf8 default would not cause issues

@kares

This comment has been minimized.

Show comment
Hide comment
@kares

kares Oct 22, 2013

Member

interestingly it should have been working as is ... at least that what the change-log for 5.1.13 says :

Connector/J now auto-detects servers configured with character_set_server=utf8mb4 or treats the Java encoding utf-8 passed using characterEncoding=... as utf8mb4 in the SET NAMES= calls it makes when establishing the connection. (Bug #54175)

http://dev.mysql.com/doc/relnotes/connector-j/en/news-5-1-13.html

Member

kares commented Oct 22, 2013

interestingly it should have been working as is ... at least that what the change-log for 5.1.13 says :

Connector/J now auto-detects servers configured with character_set_server=utf8mb4 or treats the Java encoding utf-8 passed using characterEncoding=... as utf8mb4 in the SET NAMES= calls it makes when establishing the connection. (Bug #54175)

http://dev.mysql.com/doc/relnotes/connector-j/en/news-5-1-13.html

@kares

This comment has been minimized.

Show comment
Hide comment
@kares

kares Nov 6, 2013

Member

any news on this ... didn't you happen to be using an old (< 5.1.13) driver version ? regardless of encoding: utf8

Member

kares commented Nov 6, 2013

any news on this ... didn't you happen to be using an old (< 5.1.13) driver version ? regardless of encoding: utf8

@jdg

This comment has been minimized.

Show comment
Hide comment
@jdg

jdg Nov 6, 2013

Hi kares,

I'm using whatever was bundled with the acriverecord-jdbc-mysql-adapter with 1.3.0.rc1. :\

We ended up monkey patching it. Haven't ran into any issues at all with it since then!

jdg commented Nov 6, 2013

Hi kares,

I'm using whatever was bundled with the acriverecord-jdbc-mysql-adapter with 1.3.0.rc1. :\

We ended up monkey patching it. Haven't ran into any issues at all with it since then!

@kares

This comment has been minimized.

Show comment
Hide comment
@kares

kares Nov 7, 2013

Member

Thanks, please use 1.3.2 instead or at least 1.3.0 there's been some fixes since the first RC :)
The jdbcmysql gem is no locking you down with a specific version - check what version jdbc-mysql is locked down.
If you still need the monkey-patch on 1.3.2 and latest jdbc-mysql (5.1.25) I'll get it in ... please if you have some time also try 5.1.24 if acts the same and I shall release 5.1.27 today - although there's no related fixes explicitly mentioned.

Member

kares commented Nov 7, 2013

Thanks, please use 1.3.2 instead or at least 1.3.0 there's been some fixes since the first RC :)
The jdbcmysql gem is no locking you down with a specific version - check what version jdbc-mysql is locked down.
If you still need the monkey-patch on 1.3.2 and latest jdbc-mysql (5.1.25) I'll get it in ... please if you have some time also try 5.1.24 if acts the same and I shall release 5.1.27 today - although there's no related fixes explicitly mentioned.

@jdg

This comment has been minimized.

Show comment
Hide comment
@jdg

jdg Nov 7, 2013

Great! We're going to try 1.3.2 and using jdbc-mysql (5.1.27). I'll report back once we have a chance to run it through our test suite.

jdg commented Nov 7, 2013

Great! We're going to try 1.3.2 and using jdbc-mysql (5.1.27). I'll report back once we have a chance to run it through our test suite.

@kares kares closed this in 790463f Nov 12, 2013

@samandmoore

This comment has been minimized.

Show comment
Hide comment
@samandmoore

samandmoore Oct 31, 2014

hi there, I'm still experiencing this issue and my server encoding is set correctly.

database.yml:

default:
    ...
    encoding: false

running SHOW VARIABLES; in mysql yields:
image

which shows that my character_set_server is correct. any ideas?

samandmoore commented Oct 31, 2014

hi there, I'm still experiencing this issue and my server encoding is set correctly.

database.yml:

default:
    ...
    encoding: false

running SHOW VARIABLES; in mysql yields:
image

which shows that my character_set_server is correct. any ideas?

@peterdk

This comment has been minimized.

Show comment
Hide comment
@peterdk

peterdk Nov 6, 2014

I am converting my MRI Rails 3 app to Jruby, and it fails to start due to this.

ActiveRecord::JDBCError: The driver encountered an unknown error:
 java.sql.SQLException: Unsupported character encoding 'utf8mb4'.
                                       initialize at /Users/user/.rvm/gems/jruby-1.7.16.1/gems/activerecord-jdbc-adapter-1.3.11/lib/arjdbc/jdbc/connection.rb:23

Gems:

activerecord-jdbc-adapter-1.3.11
jdbc-mysql-5.1.33
activerecord-jdbcmysql-adapter-1.3.11

Database.yml

     encoding: utf8mb4

Why does it not just allow utf8mb4? Should I set my encoding to false?

peterdk commented Nov 6, 2014

I am converting my MRI Rails 3 app to Jruby, and it fails to start due to this.

ActiveRecord::JDBCError: The driver encountered an unknown error:
 java.sql.SQLException: Unsupported character encoding 'utf8mb4'.
                                       initialize at /Users/user/.rvm/gems/jruby-1.7.16.1/gems/activerecord-jdbc-adapter-1.3.11/lib/arjdbc/jdbc/connection.rb:23

Gems:

activerecord-jdbc-adapter-1.3.11
jdbc-mysql-5.1.33
activerecord-jdbcmysql-adapter-1.3.11

Database.yml

     encoding: utf8mb4

Why does it not just allow utf8mb4? Should I set my encoding to false?

@kares

This comment has been minimized.

Show comment
Hide comment
@kares

kares Nov 6, 2014

Member

it's probably the official connector-j driver playing smart ... once again encoding: false will detect character_set_server set in my.cnf ... although, just changing it while having an existing DB might not help if your DB was created with utf8 (which seems to be the above case for @samandmoore).

we'll probably default to encoding: false for 1.4 due this, an alternative would be to try out the MariaDB driver (if it's really bothering you guys - let us know). that's pretty much all I know ... hope it's useful!

Member

kares commented Nov 6, 2014

it's probably the official connector-j driver playing smart ... once again encoding: false will detect character_set_server set in my.cnf ... although, just changing it while having an existing DB might not help if your DB was created with utf8 (which seems to be the above case for @samandmoore).

we'll probably default to encoding: false for 1.4 due this, an alternative would be to try out the MariaDB driver (if it's really bothering you guys - let us know). that's pretty much all I know ... hope it's useful!

@sungine

This comment has been minimized.

Show comment
Hide comment
@sungine

sungine Jan 16, 2017

hey guys ! I just find out a way to avoid that issue.
my error version of jdbc.url is
jdbc:mysql://x.x.x.x:3306/db?createDatabaseIfNotExist=true&useUnicode=true&characterEncoding=utf8mb4
while i change the url to
jdbc:mysql://x.x.x.x:3306/db?createDatabaseIfNotExist=true&amp;useUnicode=true&amp;characterEncoding=utf8mb4&amp;

It works!!

sungine commented Jan 16, 2017

hey guys ! I just find out a way to avoid that issue.
my error version of jdbc.url is
jdbc:mysql://x.x.x.x:3306/db?createDatabaseIfNotExist=true&useUnicode=true&characterEncoding=utf8mb4
while i change the url to
jdbc:mysql://x.x.x.x:3306/db?createDatabaseIfNotExist=true&amp;useUnicode=true&amp;characterEncoding=utf8mb4&amp;

It works!!

@MewX

This comment has been minimized.

Show comment
Hide comment
@MewX

MewX Dec 5, 2017

The comment above didnt work for me, I'm now using this:

dbc:mysql://x.x.x.x:3306/db?createDatabaseIfNotExist=true&useUnicode=true&character_set_server=utf8mb4

MewX commented Dec 5, 2017

The comment above didnt work for me, I'm now using this:

dbc:mysql://x.x.x.x:3306/db?createDatabaseIfNotExist=true&useUnicode=true&character_set_server=utf8mb4
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment