This post focuses on the problem of the InnoDB log sequence number being in the future.

Preface: What is an InnoDB log sequence number?

The Log sequence number (LSN) is an important database parameter used by InnoDB in many places.
The most important use is for crash recovery and buffer pool purge control.

Internally, the InnoDB LSN counter never goes backward.
And, when InnoDB writes 50 bytes to the redo logs, the LSN increases by 50 bytes.
As such we can count LSN in megabytes, gigabytes and etc.

Now for the problem: LSN being in the future!

When you have set innodb_force_recovery like this:

innodb_force_recovery=6

and then issue a data affecting query.

For example, if you are dropping a corrupted table after doing a mysqldump for backup …

The other day I was running pt-duplicate-key-checker on behalf of a customer and noticed some peculiar recommendations on an InnoDB table with an odd structure (no PRIMARY key, but multiple UNIQUE constraints). This got me thinking about how InnoDB promotes UNIQUE constraints to the role of PRIMARY KEYs. The documentation is pretty clear:

[DOCS]
When you define a PRIMARY KEY on your table, InnoDB uses it as the clustered index. Define a primary key for each table that you create. If there is no logical unique and non-null column or set of columns, add a new auto-increment column, whose values are filled in automatically.

If you do not define a PRIMARY KEY for your table, MySQL locates the first UNIQUE index where all the key …

Thanks to everyone who attended last week’s webinar on MySQL security; hopefully you’ve all gone out and set SELinux to enforcing mode if you weren’t already running that way. If you weren’t able to attend, the recording and slides are available for viewing/download. But now, without further ado, here are the questions which we didn’t have time to cover during the presentation.

Q: Do you have a favorite software firewall you recommend that I can run on an EC2 instance in front of my MySQL server?
A: I’d probably just do this with iptables. Any of the other Linux-based software firewall packages are all going to be wrappers around iptables anyway. However, if …

We receive this question about the ibdata1 file in MySQL very often in Percona Support.

The panic starts when the monitoring server sends an alert about the storage of the MySQL server – saying that the disk is about to get filled.

After some research you realize that most of the disk space is used by the InnoDB’s shared tablespace ibdata1. You have innodb_file_per_table enabled, so the question is:

What is stored in ibdata1?

When you have innodb_file_per_table enabled, the tables are stored in their own tablespace but the shared tablespace is still used to store other InnoDB’s internal …

My colleague and teammate Ernie Souhrada will be presenting a webinar on Wednesday, August 21, 2013 at 10 a.m. PDT titled “MySQL Security: Armoring Your Dolphin.”

This is a popular topic with news breaking routinely that yet another Internet company has leaked private data of one form or another. Ernie’s webinar will be a great overview of security MySQL from top to bottom, including changes related to security in the 5.6 release.

Topics to be covered include:

• Basic …

Following our events earlier this year in Raleigh, Montevideo, Buenos Aires, Toronto and Portland, we bring Percona University to …

This is part 3 of a 3 part series covering the new InnoDB full-text search features in MySQL 5.6. To catch up on the previous parts, see part 1 or part 2

Some of you may recall a few months ago that I promised a third part in my InnoDB full-text search (FTS) series, in which I’d actually take a look at the performance of InnoDB FTS in MySQL 5.6 versus traditional MyISAM FTS. I hadn’t planned on quite such a gap between part 2 and part 3, but as they say, better late than never. Recall that we have been working with two data sets, one which I call SEO (8000-keyword-stuffed web pages) and the other which I call DIR (800K directory records), and we are comparing MyISAM FTS in …

The new Percona Server 5.6 is the most manageable, highest performance, and most scalable version of MySQL available. Percona Server 5.6 is the best open source MySQL choice for enterprise-grade applications because it combines new features with the best features of Percona Server 5.5 and MySQL 5.6 to provide unparalleled performance.

Join me tomorrow as I explain how Percona Server 5.6 takes MySQL performance to new heights. In this webinar, aptly titled “Percona Server 5.6: Enterprise Grade MySQL,” I’ll compare Percona Server 5.6 to …

If we have InnoDB pages there are two ways to learn how many records they contain:

• PAGE_N_RECS field in the page header
• Count records while walking over the list of records from infimum to supremum

In some previous revision of the recovery tool a short summary was added to a dump which is produced by the constraints_parser.

But if a page is lost and page_parser hasn’t found it, all records from this page are lost. In other words per-page recovery statistics gives us little idea about whether or not a recovered table is complete.

To cover this flaw a new tool index_check is introduced in …

In writing a recommendation for our Web development team on how to use MySQL, I came up with the following list, which I want to share: What kind of queries are bad for MySQL?

• Any query is bad. Send a query only if you must. (Hint: use caching like memcache or redis)
• Queries that examine many rows are bad. Try instead to use…
  

      SELECT col1 FROM table1 WHERE primary_key_column=SOMETHING

  
  . Or at least
  

  secondary_key_column=SOMETHING

  
  . If it is still not possible, try to make the query examine the least amount of rows possible (zero is ideal, as we come to the first case here)
• Queries with JOINS are bad. Try to denormalize the table to avoid JOINS. Example: original query
  

      SELECT t2.value FROM t2 JOIN t1 ON (t1.id=t2.tid) WHERE t1.orderdate=NOW()

  
  . This can be denormalized by copying the column orderdate from table …