Flite Careers

MySQL Keywords and Reserved Words

I’ve seen some confusion on what constitutes a keyword or a reserved word in MySQL. The manual defines them, and has a complete list. Reserved words are a special subset of keywords, and you can’t use a reserved word as an identifier unless you quote it with backticks. I discourage the use of backticks to quote identifiers, because it allows you to use lots of words and characters in your identifier that you’ll probably regret later. For example:

1
2
3
4
5
6
7
8
9
10
mysql> create table `You will regret this!` (`(╯°□°)╯︵ ┻━┻` int);
Query OK, 0 rows affected (0.03 sec)

mysql> desc `You will regret this!`;
+--------------------------------+---------+------+-----+---------+-------+
| Field                          | Type    | Null | Key | Default | Extra |
+--------------------------------+---------+------+-----+---------+-------+
| (╯°□°)╯︵ ┻━┻                 | int(11) | YES  |     | NULL    |       |
+--------------------------------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

There is also an exception for table names. You can use a reserved word as a table name without quoting it with backticks as long as you prefix it with the schema name, but again I would discourage that. Here’s an example:

1
2
3
4
mysql> create table table (id int);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'table (id int)' at line 1
mysql> create table test.table (id int);
Query OK, 0 rows affected (0.04 sec)
Read on ✈
Comments

Being Naive About Folder Paths

Recently I worked on a feature that involved displaying a list of Folders using an auto-complete field. A simple enough task, but one that had a bit of complexity behind it. For starters, each organization in our system has their own “folder hierarchy”, or a tree structure of folders, that could be one to many layers deep. The second thing being that the UI we wanted – auto-complete – meant that we would need to “search” through the folders to find a match (based on the user’s input) as well as display the path information (where the folder lives) so that the user can differentiate folders if they happen to have the same or similar name. Since it was auto-complete, the searching also needed to be fast or else the user experience would be annoying.

Read on ✈
Comments

Time Zone Conversion in MySQL

A wise man once said that the safest way to store date and time values in MySQL is to store Unix timestamps in an unsigned INT column. I agree with Baron, but most of us have to deal with DATE, DATETIME, and/or TIMESTAMP values sometimes, and it’s often useful to know how to convert values from one time zone to another.

The CONVERT_TZ() Function

You can convert a temporal value in MySQL from one time zone to another using the CONVERT_TZ() function. As an example, say I want to watch the Boston Marathon in Melbourne, Australia but I’m afraid it may be on after my bed time. The elite men start at 10:25 AM on April 18, 2016. Boston’s time zone will be EDT that day, which is UTC-4. Melbourne’s time zone that day will be AEST, which is UTC+10. The following query will tell me what time the Boston Marathon starts in Melbourne:

1
2
3
4
5
6
7
mysql> select convert_tz('2016-04-18 10:25','-04:00','+10:00');
+--------------------------------------------------+
| convert_tz('2016-04-18 10:25','-04:00','+10:00') |
+--------------------------------------------------+
| 2016-04-19 00:25:00                              |
+--------------------------------------------------+
1 row in set (0.00 sec)

Therefore the marathon starts are 12:25 AM on Tuesday in Melbourne, which is past my bedtime.

It may seem a little silly to use CONVERT_TZ() with offsets like that rather than just adding 14 hours like this:

1
2
3
4
5
6
7
mysql> select '2016-04-18 10:25' + interval 14 hour;
+---------------------------------------+
| '2016-04-18 10:25' + interval 14 hour |
+---------------------------------------+
| 2016-04-19 00:25:00                   |
+---------------------------------------+
1 row in set (0.00 sec)

My argument for preferring CONVERT_TZ() is that it makes the query much more readable. In the first example it’s clear that I am converting a time from UTC-04:00 to UTC+10:00, whereas in the second example there’s no way to tell from the query why I’m adding 14 hours.

Read on ✈
Comments

Announcing MySQL BRO Schema

When MySQL released performance_schema it made a lot of new useful information available, but it wasn’t particularly user-friendly. The introduction of the MySQL SYS schema made it a lot easier for developers and DBAs to get useful insights from performance_schema. I was talking to some brogrammers the other day, and while they admitted that SYS schema is “totally legit”, they wished that it “spoke their language”.

To that end, I am happy to announce MySQL BRO schema. It’s a natural complement to SYS schema, and makes it easier for brogrammers to work with performance_schema.

Read on for a few examples of the BRO schema in action.

Read on ✈
Comments

Simplifying MySQL Partition Management Using Common_schema

Maintaining partitioned tables in MySQL can be tedious. In a reporting database that uses monthly date partitions, I’ve found that I can simplify many frequent partition operations using the common_schema QueryScript language. Below I will describe two specific examples.

Read on ✈
Comments

Google Accelerated Mobile Pages and the <amp-ad>

Google’s new Accelerated Mobile Pages project introduces a lot of new challenges for web page creation and design. It also presents some small challenges to Flite as an ad serving platform. The two ways in which the AMP project and its specfications interfere with our usual way of serving ads is through the restriction of written or external JavaScript and by serving ads through external iframes.

No JavaScript

Our platform, like many others, generally delivers its content to the page via a <script> tag. Google restricts the inclusion of <script> elements on AMP pages in the name of protecting the user experience and improving the loading efficiency of the pages. Instead, they require ads to be loaded via specially provisioned <amp-ad> elements over which the AMP runtime has control and whose contents can be loaded asynchronously based on that runtime’s logic. Accordingly, we had to develop a new way to deliver our ads to the page which fit in with this paradigm. On the face, this is very simple:

1
2
3
4
5
<amp-ad width=320 height=568
    type = "flite"
    data-guid = "aa7bf589-6d51-4194-91f4-d22eef8e3688"
    data-mixins="">
</amp-ad>

This tag looks much different and simpler than our usual ad tags. It works in conjunction with the AMP Project’s ad library. The AMP runtime’s 3p/integration.js module loads in the flite.js module which then controls how our ads are served into AMP pages where an <amp-ad> tag is included.

Read on ✈
Comments

Finding and Deleting Orphaned Rows in MySQL

If you store data with a parent-child relationship in MySQL tables, then you may want to identify and/or delete orphaned rows from the child table. An orphaned row is defined as a child row that references a non-existant parent row. If you enforce the parent-child relationship with a foreign key constraint on the child table then orphans are less likely to occur, but you can still end up with orphans; for example if a user disables foreign_key_checks in a session and deletes a parent row. If you choose not to create foreign keys in your database in order to avoid the performance overhead, then you are at higher risk of having orphaned rows.

Read on ✈
Comments

Connecting VPCs With IPSec and StrongSwan

For many organizations on AWS it is important to deploy applications to multiple regions. It insulates from region-specific outages, and allows traffic to be served closer to the consumer.

A problem emerges however, when applications in a VPC must communicate with applications in another VPC from a different region. At the time of writing, there is no VPC-peering between regions. This means that one or both of the applications must be accessible from the Internet; a condition with which some teams may not want to comply.

Read on ✈
Comments

Dashboards at Flite

We love data here at Flite. Last year the Cosmos team launched the Report Dashboard, which is an awesome tool that allows users to get a quick overview of their organization’s performance. Author and dashboard expert Stephen Few defines a dashboard as “a visual display of the most important information needed to achieve one or more objectives, consolidated and arranged on a single screen so the information can be monitored at a glance”. Flite Report Dashboard is packed with lots of insightful metrics and visualizations that can help our customers do things like determine if ads have been underperforming, track campaigns that are under-pacing, or show which videos are most engaging. The dashboard can also be customized to focus on the key metrics that are the most important to our customers.

Read on ✈
Comments

Preparing Your MySQL Schema for a Character Set Change

Recently I blogged about identifying necessary index changes when changing the character set for a MySQL database. In this follow-up post I want to talk about general schema changes that you should consider when changing your character set from latin1 to utf8, from utf8 to utf8mb4, etc. The main issue is with long VARCHAR columns and columns with columns with length limits that are enforeced in bytes rather than characters.

The manual points out the main areas where you may have trouble:

  • A TINYTEXT column can hold up to 255 bytes, so it can hold up to 85 3-byte or 63 4-byte characters. Suppose that you have a TINYTEXT column that uses utf8 but must be able to contain more than 63 characters. You cannot convert it to utf8mb4 unless you also change the data type to a longer type such as TEXT.

  • Similarly, a very long VARCHAR column may need to be changed to one of the longer TEXT types if you want to convert it from utf8 to utf8mb4.

The point on TINYTEXT is pretty clear. Frankly I’m not sure why anyone uses TINYTEXT to begin with, but if you have TINYTEXT columns in your database and you are changing your character set, this might be a great opportunity to change those columns to a TEXT type that supports longer values, such as MEDIUMTEXT, TEXT, or LONGTEXT.

Long VARCHAR columns can be a problem because MySQL has a row length limit of 65,535 bytes, and one really long VARCHAR or a few relatively long VARCHARs in a multi-byte character set can push your rows past that limit. For example you can have a VARCHAR(20000) in utf8 because it takes up a maximum of 60,000 bytes, but you can’t have a VARCHAR(20000) in utf8mb4 because that requires up to 80,000 bytes of storage.

Here’s my simple recommendation for preparing a MySQL schema for a character set change:

  1. Change all TINYTEXT columns to TEXT, MEDIUMTEXT, or LONGTEXT
  2. Change all long VARCHAR columns to TEXT, MEDIUMTEXT, or LONGTEXT

Read on for sample queries that generate the necessary DDL to modify the relevant columns, and examples that illustrate the problem.

Read on ✈
Comments