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

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.