mysql – mikewilliamson.dev

Scripting MySQL database setup for Rails with Bash

I am working a bash script to set up a complicated application. As part of that setup I need to run a series of SQL commands to create the initial users and databases this app will require.

It turns out you can get mysql to execute arbitrary statements from the command line using the -e switch but that would be both ugly and tedious for a lot of statements. Sounds like a job for heredocs! Lets try it out:

mike@railsdev:~$ read -d ” test <<‘EOT’
create database test;
select 1;
EOT

mike@railsdev:~$ mysql -u root -ppassword -e “$test”
+—+
| 1 |
+—+
| 1 |
+—+

Perfect! I love it when things work the way you expect. On to useful stuff:

read -d ” MYSQL_SETUP <<'EOF'
create database myapp_test;
create database myapp_development;
create database myapp_production;
CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'myapp_password';
grant all privileges on myapp_production.* to appuser@localhost;
grant all privileges on myapp_test.* to appuser@localhost;
grant all privileges on myapp_development.* to appuser@localhost;
EOF

mysql -u root -ppassword -e "$MYSQL_SETUP"

And then use the same trick to write a nice new database.yml for the app to use those credentials:

read -d ” DB_CREDENTIALS <<'EOF'
production:
adapter: mysql
database: myapp_production
pool: 5
username: appuser
password: myapp_password

development:
adapter: mysql
database: myapp_development
pool: 5
username: appuser
password: myapp_password

test:
adapter: mysql
database: myapp_test
pool: 5
username: appuser
password: myapp_password
EOF

read -d '' DB_CREDENTIALS < /var/www/myapp/config/database.yml

Postgres and Rails – for MySQL people

A little while ago I decided to switch from MySQL to Postgres for my development database. Although I love how command line friendly MySQL is and think group_concat is the bomb, I’ve been burned my MySQL’s loosey-goosey nature and had it corrupt some data on me. I decided that it was time to switch to Postgres. The transition was a little aggravating at times but it doesn’t take long to get used to it. Installing Postgres is easy enough on Ubuntu:

mike@sleepycat:~$ sudo aptitude install postgresql postgresql-server-dev-8.4

If you need to start at new Rails project you can specify Postgres right from square one:

mike@sleepycat:~/test$ rails myapp -d postgresql

After installing the database I started to look for the gems that provide the driver. I found myself sorting through a bewildering number of gems. “Postgres”, “postgres-pr”, a few minutes search turned up several options each of which proved to be the wrong thing. The gem you need is actually called ruby-pg and is written by a group of people that includes the actual creator of the Ruby language, Yukihiro Matsumoto. Although it is called ruby-pg if you ever want to find it on the internet, you install it using the name pg:

sudo gem install pg

The next stumbling block for me was my assumption that the naming of the Rails adapter would be similar to MySQL. Since adapter: mysql is what I have been using in my project already, I assumed adapter: postgres would be fine. It’s not.

development:
adapter: postgresql
database: myapp_development
username: postgres
password: password
host: localhost

Coming from MySQL, it took a fair bit of reading to collect the Postgres equivalent of all the commands I use most often. Just to save you some looking here is my “Cole’s Notes” version:

Setting a password for the default user ‘postgres’:

mike@sleepycat:~$ sudo -u postgres psql postgres
[sudo] password for mike:
psql (8.4.2)
Type “help” for help.
postgres=# \password postgres
Enter new password:
Enter it again:

Create a database as user ‘postgres’:

mike@sleepycat:~$ sudo -u postgres createdb myapp_development

Drop a database as user ‘postgres’:

mike@sleepycat:~$ sudo -u postgres dropdb myapp_development

Get to the command line of the database myapp_development as user ‘postgres’:

mike@sleepycat:~$ sudo -u postgres psql myapp_development

Load the file ‘pgdump.sql’ into the myapp_development database as the user ‘postgres’:

mike@sleepycat:~/$ sudo -u postgres psql -f pgdump.sql myapp_development

Dump a Postgres database to a file:

mike@sleepycat:~/$ sudo -u postgres pg_dump myapp_development > ~/myapp_dev_dump.sql

Show tables:

postgres=# \dt

show a table:

postgres=# \d tablename

quit:

postgres=# \q

Thats what worked for me. In general I feel like using postgres is forcing me to write more portable code. Pretty much anything I write in Rails that runs in postgres will run on MySQL, but the reverse is really not true. Little things like using ‘1’ for true. MySQL is fine with that but for Postgres with a real boolean datatype, true is true. 1 is not. I stumbled arcoss that when I was using validates_acceptance_of. I feel like its also forcing me to write better SQL as well. If you’ve written a bunch of “find_by_sql” in an existing app you might not appreciate its pickyness though. However once the initial pain of the switch passes, Postgres is pretty great.

Rails integer handling

Well it certainly is the case that you learn something new every day. After digging into some confusion around how Rails chooses its data types I ended up turning to the API docs. From the API docs:

:limit – Requests a maximum column length. This is number of characters for :string and :text columns and number of bytes for :binary and :integer columns.

:limit => 11 would give us 11 characters in a string column, but I was trying to apply it to an integer. As they said in the docs, when you use :limit with an integer it means the number of bytes. So, for the sake of my own mental clarity here is what you should get when you use the :limit option:

:limit => 1	`TINYINT`	1 byte	-128 to 127
:limit => 2	`SMALLINT`	2 bytes	-32768 to +32767
:limit => 3	`MEDIUMINT`	3 bytes	-8388608 to 8388607
:limit => 4	`INT`	4 bytes	-2147483648 to +2147483647
:limit => 8	`BIGINT`	8 bytes	-9223372036854775808 to 9223372036854775807

I say “should” above because Rails will take the value you give it and map that as best it can to whatever the actual database you are using can provide. So for MySQL it will give you a bigint for any :limit value between 5 and 8. One strange thing is that :limit => 11 maps to a 4 byte int(11) in MySQL. Not sure what thats about. For values that fall outside those, well, I guess I’ll figure that out another day.

Lost in translation: the down side of taps

UPDATE: Both Taps and Sequel have been updated and the problems that caused my issues have been resolved. I am now happily db:pushing and db:pulling. It took a while, but it got fixed in the end. :)

I have been using Heroku for a while now and its definitely great. One of the things that really blew me away when I started using it was being able to send my my entire database data up to the server with a single command:

heroku db:push

Could it be any easier? Thanks to the magic of taps, Schema AND data, transferred between my local MySQL database and Heroku’s Postgres database. At the time I might of stopped for a second to marvel at how big a deal that is, but only for a second. It turns out that its really worth stopping and thinking about what goes on when you run that command; especially when you are developing on MySQL. MySQL and Postgres have different datatypes to store data in and making an finding an exact equivalent from one database to the next is much easier said than done.

I think I will let the data do the talking for me on this. Keep you eye on the terminal_id column:

mysql> desc terminals;
+————-+—————+——+—–+————-+—————-+
| Field       | Type          | Null | Key | Default     | Extra          |
+————-+—————+——+—–+————-+—————-+
| id          | int(11)       | NO   | PRI | NULL        | auto_increment |
| location_id | int(11)       | YES |     | NULL        |                |
| merchant_id | int(11)       | YES |     | NULL        |                |
| terminal_id | decimal(11,0) | YES |     | NULL        |                |
| reference   | varchar(255) | YES |     | NULL        |                |
…
8 rows in set (0.00 sec)

The data:

mysql> select terminal_id from terminals;
+————-+
| terminal_id |
+————-+
| 10792146001 |
| 10152407001 |
| 10392407002 |
| 10152617003 |
| 10184619001 |
…
16 rows in set (0.00 sec)

Pushing it up to the server this suddenly becomes:

CREATE TABLE terminals (
id integer NOT NULL,
location_id integer,
merchant_id integer,
terminal_id integer,
reference character varying(255),
…

After a Heroku db:pull command:

mysql> desc terminals;
+————-+————–+——+—–+————-+—————-+
| Field       | Type         | Null | Key | Default     | Extra          |
+————-+————–+——+—–+————-+—————-+
| id          | int(11)      | NO   | PRI | NULL        | auto_increment |
| location_id | int(11)      | YES |     | NULL        |                |
| merchant_id | int(11)      | YES |     | NULL        |                |
| terminal_id | int(11)      | YES |     | NULL        |                |
| reference   | varchar(255) | YES |     | NULL        |                |
…
8 rows in set (0.00 sec)

The data:

mysql> select terminal_id from terminals;
+————-+
| terminal_id |
+————-+
| 2147483647 |
| 2147483647 |
| 2147483647 |
| 2147483647 |
| 2147483647 |
| 2147483647 |
| 2147483647 |
| 2147483647 |
…

How about that. Now I have a column of maxint values and a lot of very confusing bugs. Nothing like a little integer overflow to make the day go quickly. Worse still is pushing back up to the staging server before noticing, duplicating the corrupted data up there. Good thing it didn’t effect production. I am guessing that had I been developing using Postgres there would be no problem, because taps would not have to convert from one datatype to another. I am going to have to think about doing that even though I am much more familiar with MySQL. Either way the ease of that heroku db:push/pull command definitely belies the magnitude of what that command is doing.

So the takeaway from this? For me:

Backups = good.

Staging server = good.

Taps = use with caution.

Mysql tables – calculating size on disk.

Today I was getting ready to move an application at work over to a staging app that we set up at Heroku. Not wanting to absorb any extra charges for loading up a copy of the entire production database I figured I would empty out the tables that where taking up the most disk space. For that I had to query the information_schema table and add the size of the table to the size of the indexes. All the values are in bytes you have to do a little multiplication to get it displaying in something meaningful to a human like megabytes. Since this is a neat trick that I will likely end up using again I thought I would put it up here as well. Here is the query:

mysql> select table_name, round(((data_length + index_length) / (1024*1024)),2) as “size in megs” from information_schema.tables where table_schema = “myapp_development”;
+———————-+————–+
| table_name           | size in megs |
+———————-+————–+
| messages             |         0.11 |
| organisations        |         0.02 |
| rewards              |         0.02 |
| schema_migrations    |         0.02 |
| selections           |         0.02 |
| senders              |         0.02 |
| sessions             |         0.64 |
| summaries            |        15.52 |
| taggings             |         0.05 |
| tags                 |         0.02 |
| transactions         |         0.03 |
+———————-+————–+
11 rows in set (0.13 sec)

Change the where clause to look for the database you want or remove it completely to see all of them.