DB pull from heroku fails with Encoding::CompatibilityError

Question

I'm having some problems performing a db:pull from heroku back to my local development enviroment.

My setup is MySQL (5.1.50) from a MacPorts install on a local Rails 3/Ruby 1.9.2 environment under Mac OS X Snow Leopard via RVM, and I've successfully performed a heroku db:push to the production site on Heroku many times. However, on a secondary development machine with the same setup, when I try and pull the Heroku DB back via heroku db:pull, I get the following error, after it downloads the table and index info for a short while:

/Users/jschuur/.rvm/gems/ruby-1.9.2-p0/gems/sequel-3.15.0/lib/sequel/adapters/shared/mysql.rb:399:in `join': incompatible character encodings: UTF-8 and ASCII-8BIT (Encoding::CompatibilityError)
    from /Users/jschuur/.rvm/gems/ruby-1.9.2-p0/gems/sequel-3.15.0/lib/sequel/adapters/shared/mysql.rb:399:in `multi_insert_sql'

Here's the relevant gems:

ruby-mysql (2.9.3)
sequel (3.15.0)
mysql2 (0.2.4)
taps (0.3.13)
heroku (1.10.6, 1.10.5, 1.10.0)

Full error and gem list here.

The same error happens on my primary production machine BTW (the one where I've been doing the successful db:push to heroku form).

Now I understand that this is basically a UTF8 encoding error. FWIW, my database.yml explicitly references UTF8:

development:
  adapter: mysql2
  encoding: utf8
  database: rails_dev_dbname
  pool: 5
  username: rails
  socket: /tmp/mysql.sock

Now interestingly, if I create an almost blank DB with just a single test entry in it, I can push/pull it fine. So obviously something in my dataset of non UTF8. While there's not a lot of data there yet, it's entirely possible that I copied some text among it that contains a funky quote or a copyright symbol that it's choking on.

So the question is, how do I get around this problem? The Heroku docs do mention appending '?encoding=utf' to your DB URL when you do a push, but I assume that my default heroku db:push that sent the data to Heroku in the first place used database.yml, which already has that set. Either way, I did an explicit push this way, and a subsequent pull still has the same problem.

Answer 1

I switch my ruby version to 1.8.7 when using taps to push and pull when I get errors so you might want to give that a try.

If you don't use this already use RVM to manage your ruby versions