RDS for MySQL on AWS allows you to restore to any point in time for your backup retention period, minus the last 5 minutes or so. Restoration creates a new instance, it does not overwrite whatever instance you’re restoring. AWS’s use of the word restore is a bit confusing because restore often means “take your production database server and overwrite it with data from a backup”. As far as I can tell, Amazon never means this. When you restore, AWS creates another database server and writes all the data to the new instance, both when you’re using restoring to a point-in-time or from a DB snapshot. If you needed to switch servers, you’d have to point your database to the new instance.
10 Older Entries »
UPDATE: Several people have commented that decimal(10,2) is not correct for money, since sometimes currencies go out to more than 2 decimal places. Others claimed that storing cents (or base unit) as integers make it simpler to perform calculations (thanks, Kevin Farley for your comment). Regardless of what you choose – don’t use floats for money. If you do use integers, I would include the base unit in the name to avoid confusion (AmountInCents).
Data types make all the difference in the world when you’re designing your database. The choices you make now will affect the quality of your data, as well as application performance. I’m going to focus on one issue in this article: why you should always use decimals to represent money. Let’s jump in and see why that’s true.
An example of floats gone wrong
Let’s use a really, really simplified accounting ledger. It’s just three fields, an entry id, …[Read more]
I work for a firm that’s heavily invested in SQL – a team that needs to have developers who know their way around relational databases and MySQL in particular. I want to show you how I run interviews for our development positions.
Everybody has their own methods and opinions on how to conduct technical interviews. I’ve found that I generally dislike interviews that focus either on whiteboard puzzles or obscure technical details, since they don’t really show how well the candidate is at what really matters: building functioning, quality apps. I really like running the interview like we’re talking about the design for a new product. I want to figure out the requirements, mull over the data model, and write some simple queries to make sure we can show the data we need to.
This process should show two things: the candidate has a good enough grip on the MySQL database that they can comfortably build a system …[Read more]
Unless you’ve been living under a rock, you’ve probably heard about driverless cars. Google and the large car makers have been creating cars that doen’t need a human driver. This is tremendous news. Over 30,000 people die in car crashes every year in the US. While not all of those could be prevented by removing the meatbag driver, a lot of them could. Driverless cars are the right way forward and I can’t wait until they come. Every time I see a car wreck or hear about a drunk driver, I wish we had this tech right now.
That said, our teens shouldn’t stop learning to drive defensively, our cops …[Read more]
MySQL has default behavior that’s just plain wrong. I’ve covered some quirks with MySQL not null behavior and offered up using the SQL Mode strict_all_tables as a solution.
As a review, SQL Mode changes the way MySQL executes SQL statements and it’s often used to make MySQL behave. There are several switches that I would turn on by default, like only_full_group_by. You can change settings for yourself (just your connection) or for everybody (change it at the server level).
There’s another SQL Mode option, strict_trans_tables, that’s similar in intent but not in behavior to strict_all_tables. Both strict_all_tables and strict_trans_tables are meant to prevent invalid values from being inserted into your columns (such as preventing null values …[Read more]
Databases store information, right? Well, what if they could store information about your information so you could query it? Good news! Most database engines such as MySQL implement Information Schema, or a set of views that describe your tables and columns. If you’ve ever used a language like Java or C#, you might be familiar with reflection: the ability to read an object’s metadata. This enables you to do meta-programming, or writing logic about the program itself.
The idea of Information Schema is like reflection: they are views in your database that you can use in your programs or scripts when you need to know what the schema looks like or check the health of the instance. Unlike Show Processlist or Desc Table commands, the results are in tabular format.
I’m going to tour through six important views, and I’ll point you to some others at the end. Let’s get started.
Let’s …[Read more]
One of the new features of MySQL 5.6 is the optimizer trace. What does it do? Well, you know Explain, right? Just add Explain before a query and MySQL will print out how it would execute the query. The optimizer trace does that, except it gives you a little more background into to how it makes its decisions. Think of it as “It’s a Wonderful Life” for indexes – it shows you what the world would be like without that primary key or index.
If you ask, why did MySQL choose to run a full table scan vs. an index seek, the optimizer trace can help you get a better picture of the situation. The other big deal with the optimizer trace is that it’ll show you the relative costs of operations. Yeah, MySQL already tells you how many rows it’ll have to read, but now you know how much those cost given the access path (index seek, scan, etc.)
Let’s jump in to see what it looks like. First, enable the optimizer trace …[Read more]
One of the first tasks when setting up a new MySQL server is creating users and granting them permissions, or giving them the ability to do stuff in MySQL. By default, a single user, the root user, is created when you setup MySQL. This user is granted all privileges on the entire system, which means you should create separate logins for administrators and applications. Let’s walk through all the basic steps you need to create a new user and give them permissions.
Create a User
The first command I’ll show you is pretty simple:
Create User bob@'%' Identified By 'Astr0ngPhr@$e';
Before we move on, let’s check that user’s permissions:
Show Grants for bob@'%';
Which should show something like:
+----------------------------------------------------------------------------------------------------+ | Grants for bob@% …[Read more]
In this post, I’m going to briefly cover the signs that you’re doing multi-tenancy wrong. Some of these practices are entrenched in software: there are gems in Ruby on Rails, for instance, use the first anti-pattern to achieve multi-tenancy. Listen, you can drive a car with a flat tire and you can eat yogurt with a fork. People have made these solutions work, but there’s a better way.
Creating tables or schemas per customer
If you find yourself running DDL (Create Table…) for each new company or user that you add to your system, most likely you’re committing a pretty big anti-pattern. Now every time you update the table definition or need to update data across all tables, you’ll have to use a script to generate the SQL for you. Those updates will take longer and it’s much more prone to failure.
If you’re doing this for performance reasons, you have two options in most database systems to …[Read more]
10 Older Entries »