Planet MySQL

Using Active Record migrations beyond SQLite

SQLite is really a good tool to set up quick proof of concepts and small applications; however it’s not the most robust solution on the market for working with relational databases. In the open source community two databases take the top of the list: PostgreSQL and MySQL.

I did a small project for my studies. I was using SQLite as I didn’t need much out of it. Curious, I decided to see how the application would behave on other databases and decided to try PostgreSQL and MySQL. I had two problems to solve, and this post is about the first one: how to deal with the migrations. They were as follows:

Active Record automatically put the field id in all of its tables, that’s why it is omitted on the migrations.

In PostgreSQL it went smoothly, all the migrations ran without any hiccup, except on MySQL, it gave me an error!

StandardError: An error has occurred, all later migrations canceled: Column `artist_id` on table `songs` has a type of `int(11)`. This does not match column `id` on `artists`, which has type `bigint(20)`. To resolve this issue, change the type of the `artist_id` column on `songs` to be :integer. (For example `t.integer artist_id`). Original message: Mysql2::Error: Cannot add foreign key constraint: ALTER TABLE `songs` ADD CONSTRAINT `fk_rails_5ce8fd4cc7` FOREIGN KEY (`artist_id`) REFERENCES `artists` (`id`)

The problem, beyond generating an ineligible name for an index: fk_rails_5ce8fd4cc7, is that artist_id on my table was as INT. The first thing I checked was to see if the artist.id was UNSIGNED and if my foreign key was also unsigned. They weren’t, but since were both signed, it wouldn’t throw an error. Looking more closely to the error message I noticed that the type in my foreign key column did not match the type on the primary key on the other table. Little did I know that Active Record generates the id field not as an INT, but as BIGINT.

I decided to go back and look at PostgreSQL, and to my surprise, and up to now I still am not sure of why, PostgreSQL did allow the column type mismatch where MySQL threw an error.

To fix it, I had to change the migration as follows:

Digging online, I found out how to create a bigint field with AR. According to the post, this would only work on MySQL, which they did, but I found it also worked with PostgreSQL (I tested MySQL 5.7 and Postgres 9.6): t.integer :artist_id, limit: 8.

The limit is used to set a maximum length for string types or number of bytes for numbers.

Why type matching is important

As an INT let’s say you can fit your number inside an espresso cup. Sure you can use the Starbucks Venti size cup to fit your coffee, but the full content of a Venti would never fit an espresso cup.

In the specific domain I am working on if I had a big list of Artists, and happen to have an artist which ID was higher than 2,147,483,647 (signed, and for both PostgreSQL and MySQL), I would get an error when trying to insert it into the Songs table since an Artist id can be up to 8 bytes (9,223,372,036,854,775,807).

Example:

Queen has its Artist id as: 21474836481 (which is a BIGINT)

Trying to insert “We Will Rock you” in the artist_id column for songs:

We get:

********** Error ********** ERROR: integer out of range SQL state: 22003

This is the kind of problem we don’t usually notice in the beginning, and more often than not while the application is in production for even years, but this can happen and will happen if we don’t pay attention to foreign key types.

After that change, all the migrations ran smoothly. And I could actually move forward to the next problem (and post): Filtering a song title or artist name.


Filed under: Article Tagged: activerecord, mysql, postgres, ruby, sqlite

Part of my history inside InfiniDB/ColumnStore

Several years ago there was a fork of the unreleased MySQL 6.0 called Drizzle. It was designed to be a lightweight, cloud/web/UTF8 first database server with a microkernel style core. I worked for a while as one of the core developers of Drizzle until the corporate sponsor I worked for ceased funding its development.

Fast-forward to 2016 and I start working on MariaDB ColumnStore and one of the biggest surprises to me is that it incorporated part of Drizzle! Specifically the BSD licensed MySQL/MariaDB compatible client library called libdrizzle.

ColumnStore’s MariaDB plugin gets the entire query plan tree for a query and passes it on to its internal processes to break it up into parts that can be worked on in parallel. Since this doesn’t happen inside MariaDB server it needs a way to get at data that is not part of ColumnStore (such as InnoDB) for joins and subqueries.

This is where libdrizzle comes in, ColumnStore builds a query which can get the data required from the non-ColumnStore tables, makes a new client connection, executes the query and joins the result internally to the ColumnStore tables.

I suspect libdrizzle was originally used due to its BSD license which would make it easier for Calpont (the InfiniDB company) to have a commercial and Open Source version of InfiniDB. As well as its libmysqlclient-like API and MySQL compatibility.

So, where does my history come in? One of the things I worked on the most when I was working on the Drizzle project is libdrizzle. I had no clue that it was being used in this way which was an interesting surprise!

Unfortunately libdrizzle 2.0 (the version used in ColumnStore) is no longer developed. The only version that is being maintained is a fork of a more recent version Brian Aker and I created whilst we worked for HP called libdrizzle-redux. This was an attempt at simplifying the API and adding support for features that libdrizzle never supported. Development continues on GitHub by sociomantic labs GmbH. I have helped them get off the ground with the project and am happy a part of my work lives on.

Libdrizzle-redux has a very different API so we couldn’t just drop-in replace it. As much as I liked libdrizzle I didn’t want to continue maintaining the 2.0 version (I have fixed some things in the version ColumnStore 1.0 uses). In addition we didn’t really have a need for a BSD licensed connector in ColumnStore so for ColumnStore 1.1 we are switching to use the internal client connector that comes with MariaDB.

It was a little bittersweet removing a large chunk of my history from ColumnStore’s codebase but I’m glad that socimantic have continued to build a community around my legacy.


This Week in Data with Colin Charles #3: More Percona Live Europe!

Join Percona Chief Evangelist Colin Charles as he covers happenings, gives pointers and provides musings on the open source database community.

We are five weeks out to the conference! The tutorials and the sessions have been released, and there’s an added bonus – you can now look at all this in a grid view: tutorials, day one and day two. Now that you can visualize what’s being offered, don’t forget to register.

If you want a discount code, feel free to email me at colin.charles@percona.com.

We have some exciting keynotes as well. Some highlights:

  1. MySQL as a Layered Service: How to Use ProxySQL to Control Traffic and Scale Out, given by René Cannaò, the creator of ProxySQL
  2. Why Open Sourcing Our Database Tooling was the Smart Decision, given by Shlomi Noach, creator of Orchestrator, many other tools, and developer at GitHub (so expect some talk about gh-ost)
  3. MyRocks at Facebook and a Roadmap, given by Yoshinori Matsunobu, shepherd of the MyRocks project at Facebook
  4. Real Time DNS Analytics at CloudFlare with ClickHouse, given by Tom Arnfeld
  5. Prometheus for Monitoring Metrics, given by Brian Brazil, core developer of Prometheus
  6. A Q&A session with Charity Majors and Laine Campbell on Database Reliability Engineering, their new upcoming book!

Let’s not forget the usual State of the Dolphin, an update from Oracle’s MySQL team (representative: Geir Høydalsvik), as well as a keynote by Peter Zaitsev (CEO, Percona) and Continuent. There will also be a couple of Percona customers keynoting, so expect information-packed fun mornings! You can see more details about the keynotes here: day one and day two.

Releases
  • Tarantool 1.7.5 stable. The first in the 1.7 series that comes as stable, and it also comes with its own Log Structured Merge Tree (LSM) engine called Vinyl. They wrote this when they found RocksDB insufficient for them. Slides: Vinyl: why we wrote our own write-optimized storage engine rather than chose RocksDB (and check out the video).
  • MariaDB Server 10.2.8. A– as per my previous column, this build merges TokuDB from Percona Server 5.6.36-82.1 (fixing some bugs). There is also a new InnoDB from MySQL 5.7.19 (current GA release). Have you tried MariaDB Backup yet? There are some GIS compatibility fixes (i.e., to make it behave like MySQL 5.7). One thing that piqued my interest is the CONNECT storage engine (typically used for ETL operations) now has beta support for the MONGO table type. No surprises, it’s meant to read MongoDB tables via the MongoDB C Driver API. Definitely something to try!
Link List Upcoming Appearances

Percona’s website keeps track of community events, so check out where to listen to a Perconian speak. My upcoming appearances are:

  1. db tech show case Tokyo 2017 – 5-7 September 2017, Tokyo, Japan
  2. Open Source Summit North America – 11-14 September 2017, Los Angeles, CA, USA
  3. Percona Live Europe Dublin – 25-27 September 2017, Dublin, Ireland
  4. Velocity Europe – 17-20 October 2017, London, UK
  5. Open Source Summit Europe – 23-26 October 2017, Prague, Czech Republic
Feedback

Bill Bogasky (MariaDB Corporation) says that if you’re looking for commercial support for Riak now that Basho has gone under, you could get it from Erlang Solutions or TI Tokyo. See their announcement: Riak commercial support now available post-Basho. Thanks, Bill!

I look forward to feedback/tips via e-mail at colin.charles@percona.com or on Twitter @bytebot.

Pages