ASCII-8BIT encoding of query results in rails 2.3.2 and ruby 1.9.1

Instead of

nvalue = ActiveSupport::Multibyte::Chars.new(value)
condition_params = [nvalue.downcase]

You can just do

condition_params  = [value.mb_chars.downcase]

Well the problem is that $KCODE is not set in ruby19 so the call to mb_chars doesn't proxy the string. So that why I did the explicit wrapping.

@@@ Ruby activesupport/lib/active_support/core_ext/string/multibyte.rb def mb_chars if ActiveSupport::Multibyte.proxy_class.wants?(self)

ActiveSupport::Multibyte.proxy_class.new(self)

else

self

end end

activesupport/lib/active_support/multibyte/chars.rb def self.wants?(string) $KCODE == 'UTF8' && consumes?(string) end

railties/lib/initializer.rb def initialize_encoding $KCODE='u' if RUBY_VERSION < '1.9' end

Sorry again posting the code

# activesupport/lib/active_support/core_ext/string/multibyte.rb
def mb_chars
  if ActiveSupport::Multibyte.proxy_class.wants?(self)
    ActiveSupport::Multibyte.proxy_class.new(self)
  else
    self
  end
end

# activesupport/lib/active_support/multibyte/chars.rb
def self.wants?(string)
  $KCODE == 'UTF8' && consumes?(string)
end

# railties/lib/initializer.rb 
def initialize_encoding
  $KCODE='u' if RUBY_VERSION < '1.9'
end

Ah, right. I'll try to think of a way to access the proxy in Ruby 1.9.

is there any patch for activerecord or sqlite and postgre-pr gem?

Sorry Hector, but your patch to Ruby-MySQL doesn't fully solve the problem.

@@@ if (fields[i].type == MYSQL_TYPE_BLOB) @@@

The comparison with MYSQL_TYPE_BLOB also includes TEXT (TINYTEXT, MEDIUMTEXT, LONGTEXT) fields. All content stored in these also comes back ASCII-8BIT encoded.

Link: http://github.com/hectoregm/mysq...

I've fixed sqlite3-ruby gem (version 1.2.5) http://github.com/qoobaa/sqlite3-ruby/tree/master. The problem with ASCII-8BIT encoding is in Rack also, I've created a patch to fix it.

I agree with the goal of a database driver that correctly sets the encoding of strings, however hard coding all strings to UTF-8 seems like we'd be shooting ourselves in the foot.

The problem with encoding issues is that often the wrong solution does the right thing 95% of the time. Sure most of us writing web apps these days operate in UTF-8, but when some poor sod rocks up with a UTF-16 encoded database we'd break her data.

I've never used the MySQL C API, but surely there's a way to detect the encoding of the current DB/table/column?

I second James Healy's approach. Blindly setting the encoding of all strings from the database to UTF-8 is short-sighted.

Agreed. I do the same thing in the SQL Server adapter.

James & Brendan, patches are very welcome!

I decided to investigate further and picked MySQL as my guinea pig.

I can see two possible approaches.

1. We patch the MySQL/Ruby driver to return strings marked with an appropriate encoding. To do so it would need to track the value of the character_set_results MySQL variable which indicates the character set MySQL will return results in. I'm not sure what 'tracking' that variable would involve. Since it can be changed at any time, the driver would need to regularly check (and cache?) the value.

2. We patch the ActiveRecord MySQL Adapter. This leaves the driver encoding unaware - it more or less just passes byte arrays between AR and the MySQL server in blissful ignorance of the encoding. If the AR MySQL Adapter notices it has encoding: set in it's config, it can take the ASCII-8BIT/BINARY strings the driver hands it and force the encoding to something appropriate.

Either approach looks achievable without too much work. I'm keen to attempt on of them, but I think I'll mull over the options for a while first.

Any thoughts? Ken - it sounds like you modified the SQL Server Adapter and not the driver?

It took my a little while to get it, but I've got a proposed patch for the MysqlAdaptor on github @ http://github.com/yob/rails/commit/986b8c99331d68087eaa0a703f4121c5....

I took approach (2) from my earlier comment. The Mysql driver remains encoding unaware, and all results are stored in the AR model attributes hash marked as "BINARY" encoding.

Traditionally non string attributes are type cast on demand (converted to ints, dates, etc) and strings are left untouched. This patch adds a type casting process for string attributes that "fixes" the encoding to match what the user has specified in database.yml.

The commit message has a few extra details.

(the correct URL for James patch is http://github.com/yob/rails/commit/986b8c99331d68087eaa0a703f4121c5... )

What's the best way for me to get feedback on my patch?

I'm still in two minds about whether the encoding of strings from the database should be fixed in the DB driver or ActiveRecord, so it would be nice to get some discussion going.

I think ActiveRecord is the right place to handle encoding, though some DB drivers may also be able to help if there is a non-standard way the particular database handles encoding. For databases that do nothing with encoding and just push raw bits, ActiveRecord should provide a configuration option for the encoding and return Strings properly encoded, perhaps even transformed to another encoding if the data is encoded differently than the user wants.

That's my 2¢, anyway.

I wrote a gem to enhance ActiveRecord::Base as described above: http://github.com/cosine/active_record_encoding/tree/master

It assumes the database just pushes bits and doesn't understand its encoding, which is true in my case.

I have not thoroughly tested it, yet. Proceed with caution if you use it. Even so, I would love some feedback.

It assumes the database just pushes bits and doesn't understand its encoding

Unfortunately that's not the case. For instance, when you try to store certain UTF-8 characters in a Latin-1 database you will loose information. The database storage engine and the database protocol are generally encoding aware so we will have to deal with that.

Even so, I would love some feedback.

If you're serious in pursuing this plugin I would recommend writing a lot of tests.

The database storage engine and the database protocol are generally encoding aware so we will have to deal with that. Yes. So the approach (1) is ideal.

I sent a patch for ruby-pg. The next release of ruby-pg will be encoding-aware.
Mysql/Ruby should be fixed as ruby-pg. And I think my article http://yugui.jp/articles/838 can help to fix database drivers.

Hi. I try to fix this issue and there is two moments:
1. First, db driver give us utf string with forced ASCII-8BIT encoding.
2. Some utf views are forced to ASCII-8BIT during the compilation.
So, we have a multi problem.
Let's look:

>> p = "привет"
=> "привет"
>> t = "мир"
=> "мир"
>> t.force_encoding Encoding::ASCII_8BIT
=> "\xD0\xBC\xD0\xB8\xD1\x80"
>> p << t
Encoding::CompatibilityError: incompatible character encodings: UTF-8 and ASCII-8BIT

    from (irb):4
    from /home/pyromaniac/.rvm/ruby-1.9.1-p243/bin/irb:12:in `&lt;main&gt;'</code>

This hack works well for full utf projects

class String
  alias_method(:orig_concat, :concat)
  def concat(value)

orig_concat value.force_encoding(Encoding::UTF_8)

  end
end

I tested ruby-mysql posted by pyromaniac and it seems to work fine.

You can use this little pure Ruby hack to get things working. No modifications to the mysql gem are needed, so it's pretty easy to drop into an existing app:

http://gnuu.org/2009/11/06/ruby19-rails-mysql-utf8/

And now??? I have this exact issue with Rails 3 Beta 2, Ruby 1.9.2-head.

For those who dont want to overwrite String::concat and use HAML for views, here is my solution (http://gist.github.com/358275):

module Haml
  class Buffer
    class UTF8String < String
      def << text; super text.toutf8; end
    end

    alias original_initialize initialize

    def initialize *args
      original_initialize *args
      @buffer = UTF8String.new
    end
  end
end

As for mysql gem, I might take a shot at it ... based od Yugui's article.

I have the impression that there are too many "potential" mysql-ruby sources and most of them are unmaintained.

I'm still investigating, so sorry if I am missing something important.

Here is an interesting summary as to why:

http://github.com/luislavena/mysql-gem/issues/labels/documentation#...

I propose:

• changing the name from 'mysql','ruby-mysql','mysql-win' into something that stands out from the rest, just to avoid confusion
• update the Gemfile and source files to require this recommended new libmysql wrapper.

@Hector - Thanks for the great work with the mysql-ruby fork!

• Should I use it as a base for testing and patches?
• Can we rename it and make it the official gem for Rails?