Categories
Programming

How to Get Emojis in MySQL (fixing Incorrect String Value)

So your Rails application with a MySQL database was humming along and all of a sudden it hit this error:

Mysql2::Error: Incorrect string value: '\xC5\x99\xC3\xA1k

What does that mean? Upon inspection you realize that someone has typed into your website the all-too-favorite Millenial communication tool: the emoji. ?✨

MySQL needs a little massaging. For steps 1 & 2 in Rails, create a new database migration and use execute to run these commands directly onto your database.

  1. Make sure you actually alter the database’s character set

ALTER DATABASE <DATABASE NAME> CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

Where <DATABASE NAME> is your database. in Rails, because we have different database names for different environments, try this inside of your migration…

execute "ALTER DATABASE #{ActiveRecord::Base.connection.current_database} CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;"

  1. Make sure you ALTER the TABLE AND FIELD where you want to insert the character set

ALTER TABLE CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE modify TEXT charset utf8mb4;

(If you made a Rails migration, you can do this in the same migration using another execute.)

IMPORTANT: I have assumed above that your field is of type TEXT. If it is not, change your field’s type definition before running this. If you don’t, you’ll convert your existing field to a TEXT field even if you didn’t want to.

  1. Restart your MySQL instance (DO NOT FORGET)
  2. In Rails, you must also specify the encoding & collation in the MySQL adapter which you will find in your config/database.yml file (where you have specified mysql2 as the adapter).

For example:

production:
encoding: utf8mb4
collation: utf8mb4_general_ci

or if you want it in your default configs

default: &default
encoding: utf8mb4
collation: utf8mb4_general_ci

See also…

https://stackoverflow.com/questions/22464011/mysql2error-incorrect-string-value#22464749

https://stackoverflow.com/questions/16350310/mysql-mysql2error-incorrect-string-value/18498210#18498210

https://stackoverflow.com/questions/16350310/mysql-mysql2error-incorrect-string-value/16934647#16934647

Categories
Programming

MySQL Dumping Cheat-Sheet

Make a quick dump of a mysql database:

mysql_dump db_name > backup-file.sql

You can then read that dump file back into the server like this

mysql db_name < backup-file.sql

or like this:

mysql -e “source /path/to/backup/backup-file.sql” db_name

Installing Rails & MySQL On Mac OS X

First, isntall MySQL package installer from

http://dev.mysql.com/downloads/mysql/5.1.html#downloads

DON’T DOWNLOAD THE 64-BIT VERSION!!!
You must download the 32-bit version even if you have a 64-bit machine.

Then I was able to run this. Note: it is important to give the gem installer the -with-mysql-config flag, and point it to the file mysql_config inside the bin/ directory

sudo gem install mysql – -with-mysql-config=/usr/local/mysql/bin/mysql_config

UPDATE: Me thinks the 64-bit version now works with Snow Leopard 10.6 (haven’t tested under 10.5). But you have to set the flags to use 64-bit:

Prior to Leopard:
sudo gem install mysql – -with-mysql-dir=/usr/local/mysql

Leopard on a PPC machine:
sudo env ARCHFLAGS=”-arch ppc” gem install mysql – -with-mysql-config=/usr/local/mysql/bin/mysql_config

Leopard on an Intel machine:
sudo env ARCHFLAGS=”-arch i386″ gem install mysql – -with-mysql-config=/usr/local/mysql/bin/mysql_config

Snow Leopard (only on Intel):
sudo env ARCHFLAGS=”-arch x86_64″ gem install mysql – -with-mysql-config=/usr/local/mysql/bin/mysql_config

If you don’t run this with the right architecture RCHFLAGS, you get this when you try to launch your app:

unitialized constant MysqlCompat::MysqlRes

http://techliberty.blogspot.com/2009/12/dealing-with-rake-aborted-uninitialized.html
http://weblog.rubyonrails.org/2009/8/30/upgrading-to-snow-leopard
http://stackoverflow.com/questions/991708/rails-mysql-and-snow-leopard

I had a real hard time with this one on a recent Snow Leopard upgrade, be sure to rebuild all your port installs too:
http://trac.macports.org/wiki/Migration

And finally do this:

cd /tmp
wget http://www.tmtm.org/en/ruby/mysql/ruby-mysql-0.2.6.tar.gz
tar xvfz ruby-mysql-0.2.6.tar.gz
cd ruby-mysql-0.2.6
sudo ruby install.rb