Can't set encoding on MySQL database


#1

Hello all,

I’ve got a project I’m working on that’s based on OpsCode’s “Django
Quickstart” Django CMS tutorial. My project needs ‘utf8’ encoding for it’s
MySQL database. The quickstart’s databag, django_cms.json, appears to be
configured for utf8:

“databases”: {
"_default": {
“encoding”: “utf8”,
“username”: “django”,
“adapter”: “mysql”,
“port”: “3306”,
“database”: “django_production”,
“password”: “awesome_password”
}

However, this doesn’t seem to get picked up, as after the server is created
I try this:

ssh ubuntu@my-ip.compute-1.amazonaws.com
mysql -u root -p
mysql> use django_production;
mysql> SHOW VARIABLES LIKE “character_set_database”;
±-----------------------±-------+
| Variable_name | Value |
±-----------------------±-------+
| character_set_database | latin1 |
±-----------------------±-------+
1 row in set (0.00 sec)

I took a look at cookbooks/database/master.rb, and edited as such to try to
get the encoding added in, starting at line 84:


mysql_database “create #{db[‘database’]}” do
host "localhost"
username "root"
password root_pw
database db[‘database’]
encoding db[‘encoding’] # I added this line
action [:create_db]
end

Still nothing. I can SSH into the server and recreate the database as a
workaround, but would love to have this set up properly when I first create
the instance. Thanks in advance.

Kevin Harvey
Web Developer
615-686-8238
kcharvey@gmail.com


#2

Hello!

On Nov 21, 2011, at 6:33 AM, Kevin Harvey wrote:

“databases”: {
"_default": {
“encoding”: “utf8”,
“username”: “django”,
“adapter”: “mysql”,
“port”: “3306”,
“database”: “django_production”,
“password”: “awesome_password”
}

However, this doesn’t seem to get picked up, as after the server is created I try this:

ssh ubuntu@my-ip.compute-1.amazonaws.com

mysql -u root -p
mysql> use django_production;
mysql> SHOW VARIABLES LIKE “character_set_database”;
±-----------------------±-------+
| Variable_name | Value |
±-----------------------±-------+
| character_set_database | latin1 |
±-----------------------±-------+
1 row in set (0.00 sec)

I took a look at cookbooks/database/master.rb, and edited as such to try to get the encoding added in, starting at line 84:


mysql_database “create #{db[‘database’]}” do
host "localhost"
username "root"
password root_pw
database db[‘database’]
encoding db[‘encoding’] # I added this line
action [:create_db]
end

The ‘encoding’ in the application data bag examples is primarily there from the application::rails recipe, and it is not used in the local_settings.py file. It also is not used in the mysql_database resource, which is why adding the encoding db['encoding'] line didn’t do anything. It appears you’re using an older version of the mysql cookbook, where the mysql_database resource was defined. The actual query is:

action :create_db do
  ...
      db.query("create database #{new_resource.database}")
  ...
end

It simply sends the create database query, but does not specify any encoding. FYI, this provider is moved to the database cookbook in newer versions of both. I created a COOK ticket to track this:

http://tickets.opscode.com/browse/COOK-841

For now, you should be able to work around this issue by adding this to cookbooks/mysql/resources/database.rb:

attribute :encoding, :kind_of => String, :default => "utf8"

And modify the db.query line in the cookbooks/mysql/providers/database.rb under the create_db action to:

      db.query("create database #{new_resource.database} CHARACTER SET = '#{new_resource.encoding}'")

This will default to utf8.


Opscode, Inc
Joshua Timberman, Technical Program Manager
IRC, Skype, Twitter, Github: jtimberman


#3

Joshua, thank you so much. I’m working on getting this in my recipe now.
However, I first tried dropping in the brand new v 1.2.1 MySQL cookbook and
got this error: “NoMethodError: undefined method `mysql_database’ for
#Chef::Recipe:0xb6c7bf94

Is there a how to guide for upgrading cookbooks anywhere?


Kevin Harvey
Web Developer
615-686-8238
kcharvey@gmail.com

On Mon, Nov 21, 2011 at 12:49 PM, Joshua Timberman joshua@opscode.comwrote:

Hello!

On Nov 21, 2011, at 6:33 AM, Kevin Harvey wrote:

“databases”: {
"_default": {
“encoding”: “utf8”,
“username”: “django”,
“adapter”: “mysql”,
“port”: “3306”,
“database”: “django_production”,
“password”: “awesome_password”
}

However, this doesn’t seem to get picked up, as after the server is
created I try this:

ssh ubuntu@my-ip.compute-1.amazonaws.com

mysql -u root -p
mysql> use django_production;
mysql> SHOW VARIABLES LIKE “character_set_database”;
±-----------------------±-------+
| Variable_name | Value |
±-----------------------±-------+
| character_set_database | latin1 |
±-----------------------±-------+
1 row in set (0.00 sec)

I took a look at cookbooks/database/master.rb, and edited as such to try
to get the encoding added in, starting at line 84:


mysql_database “create #{db[‘database’]}” do
host "localhost"
username "root"
password root_pw
database db[‘database’]
encoding db[‘encoding’] # I added this line
action [:create_db]
end

The ‘encoding’ in the application data bag examples is primarily there
from the application::rails recipe, and it is not used in the
local_settings.py file. It also is not used in the mysql_database resource,
which is why adding the encoding db['encoding'] line didn’t do anything.
It appears you’re using an older version of the mysql cookbook, where the
mysql_database resource was defined. The actual query is:

action :create_db do

db.query(“create database #{new_resource.database}”)

end

It simply sends the create database query, but does not specify any
encoding. FYI, this provider is moved to the database cookbook in newer
versions of both. I created a COOK ticket to track this:

http://tickets.opscode.com/browse/COOK-841

For now, you should be able to work around this issue by adding this to
cookbooks/mysql/resources/database.rb:

attribute :encoding, :kind_of => String, :default => “utf8”

And modify the db.query line in the cookbooks/mysql/providers/database.rb
under the create_db action to:

     db.query("create database #{new_resource.database} CHARACTER SET

= ‘#{new_resource.encoding}’")

This will default to utf8.


Opscode, Inc
Joshua Timberman, Technical Program Manager
IRC, Skype, Twitter, Github: jtimberman


#4

On Nov 21, 2011, at 12:33 PM, Kevin Harvey wrote:

Joshua, thank you so much. I’m working on getting this in my recipe now. However, I first tried dropping in the brand new v 1.2.1 MySQL cookbook and got this error: “NoMethodError: undefined method `mysql_database’ for #Chef::Recipe:0xb6c7bf94

Is there a how to guide for upgrading cookbooks anywhere?

The mysql_database resource is moved to the database cookbook in the latest version.

If your local Chef Repository is a Git version controlled repository, you should use knife cookbook site install to retrieve new versions.

knife cookbook site install mysql
knife cookbook site install database

This will automatically integrate the cookbooks into your repository, creating branches and revision tags as described here:

http://wiki.opscode.com/display/chef/Working+with+Git+and+Cookbooks

See the section “Tracking upstream changes.”