Feature flags (or flippers or toggles or whatever you like to call them) are a really important piece to any development process, especially if you are employing Continuous Integration (CI) . Even if you’re not quite there with CI, feature flags can still be a very useful tool that can help agile development teams be more productive, produce higher quality products, and work at the speed of business.Read on ✈
Since I recently wrote about both MySQL error 1071 and error 1070 I decided to continue the pattern with a quick note on MySQL error 1069. In case you’ve never seen it before, this is MySQL error 1069:
I can’t think of a valid use case that requires more than 64 indexes on a MySQL table, but it’s possible to get this error by inadvertantly adding lots of duplicate indexes to a table. This can happen if you don’t explicitly name your indexes.
Read on for examples…Read on ✈
As documented in the Reference Manual MySQL supports a maximum of 16 columns per index. That’s more than sufficient for most index use cases, but what about unique constraints? If I create a fact table with more than 16 dimension columns in my star schema, and then try to add an index to enforce a unique constraint across all of the dimension columns, then I’ll get this error:
For multi-column unique indexes, internally MySQL concatenates all of the column values together in a single hyphen-delimited string for comparison. Thus I can simulate a multi-column unique index by adding an extra column that stores the concatenated column values, and adding a unique index on that column.
Read on for details…Read on ✈
Some MySQL DDL commands such as
CREATE TABLE and
DROP TABLE support an
IF [NOT] EXISTS option which allows you to downgrade the error to a warning if you try to create something that already exists or drop something that doesn’t exist.
For example this gives an error:
And this gives a warning:
1 2 3 4
You may also want to use
IF [NOT] EXISTS for column-level changes such as
ADD COLUMN and
DROP COLUMN, but MySQL does not support that.
If you’ve ever tried to add an index that includes a long varchar column to an InnoDB table in MySQL, you may have seen this error:
The character limit depends on the character set you use. For example if you use
latin1 then the largest column you can index is
varchar(767), but if you use
utf8 then the limit is
varchar(255). There is also a separate 3072 byte limit per index. The 767 byte limit is per column, so you can include multiple columns (each 767 bytes or smaller) up to 3072 total bytes per index, but no column longer than 767 bytes. (MyISAM is a little different. It has a 1000 byte index length limit, but no separate column length limit within that).
One workaround for these limits is to only index a prefix of the longer columns, but what if you want to index more than 767 bytes of a column in InnoDB?
In that case you should consider using
innodb_large_prefix, which was introduced in MySQL 5.5.14 and allows you to include columns up to 3072 bytes long in InnoDB indexes. It does not affect the index limit, which is still 3072 bytes as quoted in the manual:
The InnoDB internal maximum key length is 3500 bytes, but MySQL itself restricts this to 3072 bytes. This limit applies to the length of the combined index key in a multi-column index.
Read on for details and examples about
In the past I have used pt-slave-delay when I want to maintain an intentionally delayed replica. Now that I have upgraded to MySQL 5.6 I am switching over to use MASTER_DELAY, which is a built-in feature that does the same thing.
For example I can replace this:
1 2 3
The implementation is similar: the IO thread copies the events to the relay log as fast as normal, but the SQL thread only executes events older than the defined lag. The process to fast-forward the replica should be similar as well.
So far I see a couple of advantages of using
start slavemanually can’t cause the replica to catch up beyond the defined lag, which could happen with
- No need to monitor the daemon (I used monit for this when running
MASTER_DELAY is part of
CHANGE MASTER it is persisted with the other replication configuration data, thus it doesn’t need to (and can’t) be defined in my.cnf, and it survives a reboot.
If you are using FULLTEXT indexes in MySQL and plan to switch from MyISAM to InnoDB then you should review the reference manual section on Fine-Tuning MySQL Full-Text Search to see what configuration changes may be required. As I mentioned in yesterday’s post when comparing query results on my database with FULLTEXT indexes in MyISAM versus InnoDB I got different results. Specifically, the InnoDB tables were returning fewer results for certain queries with short FULLTEXT search terms. Here’s an example of a query that returned fewer results on InnoDB:
1 2 3
The issue was that all of the fine tuning I had done before was limited to MyISAM, so it didn’t affect InnoDB. In the past I configured MySQL FULLTEXT search to index words as short as 1 character (the default is 3), and to index common words (not to use any stopword list). These are the relevant variables I set in in my.cnf:
InnoDB has its own variables to control stopwords and minimum word length, so I needed to set these variables when I changed the tables from MyISAM to InnoDB:
Since those variables are not dynamic, I had to restart MySQL for them to take effect. Furthermore, I needed to rebuild the FULLTEXT indexes on the relevant tables. This is how the manual instructs you to rebuld the indexes:
To rebuild the FULLTEXT indexes for an InnoDB table, use ALTER TABLE with the DROP INDEX and ADD INDEX options to drop and re-create each index.
Rather than drop and recreate the indexes, I just used
ALTER TABLE ... FORCE to rebuild the table (and indexes), like this:
After making those changes I re-ran
pt-upgrade, and now I am getting the same set of rows back from MyISAM and InnoDB. The order of the rows is slightly different in some cases, but as I mentioned yesterday that is expected behavior.
As I prepare to convert some MySQL tables with FULLTEXT indexes from MyISAM to InnoDB I want to verify that running a standard production query set against the tables will return the same results with InnoDB that it did with MyISAM. Since I read Matt Lord’s blog post about the document relevancy rankings used for InnoDB full-text searches I knew to expect some differences when sorting by relevancy, so I want to focus on getting the same set of rows back, mostly ignoring the order in which the rows are returned.
Percona toolkit has a tool called pt-upgrade that works well for this purpose. I used 2 test servers with a copy of my production database. On one of the servers I left the tables in MyISAM, and on the other I converted the tables to InnoDB. I copied a slow query log from a production host running with
long_query_time=0 to get the query set for testing. Since I was only interested in queries on a few tables, rather than running the entire slow query log against the servers I just extracted the specific queries I was interested in and ran them as a raw log.
Here’s the command I used:
1 2 3 4 5
I used the
--read-only flag so
pt-upgrade would only execute
SELECT statements, and not any statements that modify data.
Since I extracted the SQL queries from the slow query log instead of using the full slow query log, I used
--type rawlog instead of the default of
For the two hosts I compared, testdb33 is using FULLTEXT on InnoDB, and testdb47 is using FULLTEXT on MyISAM.
When I ran
pt-upgrade it exposed several significant discrepancies. I will document those discrepancies and how I fixed them in a future post.
I’ve recently run into a scenario where MyISAM auto-repair does not appear to work in MySQL 5.6. Most of my tables are InnoDB, but having recently upgraded from MySQL 5.5 to 5.6 I still have a handful of MyISAM tables in order to use FULLTEXT indexes since FULLTEXT on InnoDB was not supported in MySQL 5.5. These MyISAM tables are essentially copies of InnoDB tables, and are kept in sync via triggers.
myisam_recover_options=FORCE,QUICK so I expect any crashed MyISAM tables to be repaired automatically when mysqld tries to open them, but when one of these MyISAM tables crashes the auto-repair does not happen unless I explicitly interact with the table. For example I’ll see an error like this if replication updates a table that triggers an update on a crashed MyISAM table:
That error stops replication, so I need to repair the relevant MyISAM table before I can resume replication. If I execute an explicit query on that table then mysqld will repair it automatically. For example a query as simple as this leads to auto-repair:
I’m hoping to get rid of these MyISAM tables soon since I can add FULLTEXT indexes on the InnoDB tables now. Once I do that this issue will not affect me any more. Nonetheless it seems to me that auto-repair should handle this scenario, so I entered a bug report.
After upgrading from MySQL 5.5 to 5.6, I started getting some warnings when running queries in common_schema. For example:
1 2 3 4 5 6 7 8 9 10
The common_schema installer is good at accomodating whichever version of MySQL it is being installed on, but if you change that version it makes sense that common_schema needs to be re-installed. As stated in the documentation:
If you should upgrade your MySQL server, or enable features which were turned off during install of common_schema, the new feature set are not automatically available by common_schema, and a re-install of common_schema is required.
A re-install is easy. Here’s what I did:
Most of the warnings went away, but I’m still getting this:
1 2 3 4 5 6
Apparently in MySQL 5.6 the
innodb_index_stats table has moved from
information_schema to the
mysql schema, so I submitted a bug report. So far this isn’t affecting the way I use common_schema, but if it did the common_schema source code is very readable so it would probably be a simple patch.