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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s