Tag: mysql

MySQL 8.0: Preview @ PHPWorld

MySQL 8.0: Preview @ PHPWorld

These are the slides for my MySQL 8.0 Preview: What is coming? At PHPWorld 2017.

Abstract:

Yes, you read it correctly, we are jumping from 5.7 to 8.0 (that sounds familiar, doesn’t it?). The new version doesn’t only change the number but also changes how you write SQL. Recursive queries will allow you to generate series and work with hierarchical data. New JSON functions and performance improvements were also added to 8.0 to help you work on non-relational data. Expect to see what is new and improved in this talk to power up your application even more.

Please share your feedback at Joind.in: https://joind.in/talk/0e88f

I would like to thank Digital Ocean for enabling my research for this talk with their droplets, so Thank you!

Using Active Record migrations beyond SQLite

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.

From MySQL 8.0.0 to MySQL 8.0.1 – or any other dev milestone

Disclaimer: This post is aimed to you, the curious developer, sys-admin, technologist, whatever-title-you-use. DO NOT run the following lines on production. Not even in a stable environment, do this if you don’t care about the outcome of the current data.

If you want to keep up with the newest MySQL developer milestones I have news for you: there is no upgrade available for milestone versions. The way to go is to remove old version and install new one, according to their website:

Upgrades between milestone releases (or from a milestone release to a GA release) are not supported. For example, upgrading from 8.0.0 to 8.0.1 is not supported, as neither are GA status releases.

So if you, like me, had the 8.0.0 version and want to test the 8.0.1 (alhtough 8.0.3 milestone is already in development) you need to do something like the following (tutorial based on Debian/Ubuntu servers).

Stop your service:

$ sudo service mysql stop

Download Oracle’s repository and install it, as of now this is the current version, you can get the new package here:

$ wget https://dev.mysql.com/get/mysql-apt-config_0.8.6-1_all.deb
$ sudo dpkg -i mysql-apt-config_0.8.6-1_all.deb

Clean your old install, you will lose all the data. Be careful, back up is on you!

$ sudo apt-get remove --purge mysql-server mysql-client mysql-common
$ sudo apt autoremove
$ sudo apt-get autoclean
$ sudo apt-get install mysql-server

This is the way to go to test the new features such as Descending Indexes and others. Remember, the new default encoding was changed from latin1 to utf8mb4.

Short feature list:

The complete list is available here.