How InnoDB initializes AUTO_INCREMENT counters is actually not a bug, but a documented mechanism. There were some complaints and even people who lost data over this. To initialize an auto-increment counter after a server restart, InnoDB executes the equivalent of the following statement on the first insert into a table containing an AUTO_INCREMENT column. SELECT MAX(ai_col) FROM table_name FOR
10 Older Entries »
First, I want to thank everybody for attending the July 14 webinar. The recording and slides for the webinar are available here. Below is the list of your questions that I wasn’t able to answer during the webinar, with responses:
Q: At which isolation level do
copy data from a table?
A: Both tools do not change the server’s default transaction isolation level. Use either
I was recently asked this question by an experienced academic at the NY Oracle Users Group event I presented at.
Does MySQL support ACID? (ACID is a set of properties essential for a relational database to perform transactions, i.e. a discrete unit of work.)
Yes, MySQL fully supports ACID, that is Atomicity, Consistency, Isolation and Duration. (*)
This is contrary to the first Google response found searching this question which for reference states “The standard table handler for MySQL is not ACID compliant because it doesn’t support consistency, isolation, or durability”.
The question is however not a simple Yes/No because it depends on timing …[Read more]
The binary log contains “events” that describe database changes. On a basic installation with default options, it's not turned on. This log is essential for accommodating the possible following requirements:
Replication: the binary log on a master replication server provides a record of the data changes to be sent to slave servers. Point in Time recovery: allow to recover a database from a full
MySQL replication in action - Part 1: GTID &
CoMySQL replication in action - Part 2 - Fan-in
In the previous article, we saw the basics of establishing replication from multiple origins to the same destination. By extending that concept, we can deploy more complex topologies, such as the point-to-point (P2P) all-masters topology, a robust and …
Long running transactions can be problematic for OLTP workloads, particularly where we would expect most to be completed in less than a second. In some cases a transaction staying open just a few seconds can cause behaviour that is entirely unexpected, with the developers at a loss as to why a transaction remained open. There are a number of ways to find long running transactions, luckily versions of MySQL from 5.6 onwards provide some very insightful instrumentation.
Here we will use the information_schema coupled with the …[Read more]
Today I was doing some tests with XA transactions in MySQL
The output of the XA RECOVER command to list transactions was hard to read because of the representation of the data column:
The good news is that 5.7 has transaction information in performance_schema:
mysql> select trx_id, isolation_level, state, xid, xa_state, access_mode[Read more]
-> from performance_schema.events_transactions_current;
| trx_id | isolation_level | state | xid | xa_state | access_mode |
| NULL | REPEATABLE READ | ACTIVE | x-1 | PREPARED | READ WRITE |
| 421476507015704 | REPEATABLE READ | …
In the previous blog posts I've talked about transactions which
block other transactions but don't do anything and about some
In this post I will show you how to get even more information about what is locked by a transaction.
As you might have noticed the information_schema.innodb_locks table doesn't show all locks. This is what the documentation says:
INNODB_LOCKS table contains information
about each lock that an
InnoDB transaction has
requested but not yet acquired, and each lock that a transaction
holds that is blocking another …
In my previous post I talked about a transaction
which blocked other transactions without doing anything. I talked
about finding data from the blocking transaction using SYS and
But what are the possible solutions?
The first solution is to (automatically) kill the blocking transactions. Long running transactions can also stall the purging in InnoDB. See this blog post by Mark Leith about a possible solution.
The second solution would be make the application end the transaction sooner and/or to commit more often. Depending on your application this might or might not work. I consider this the best solution.
Since our initial release last summer, TokuMX has supported fully ACID and MVCC multi-statement transactions. I’d like to take this post to explain exactly what we’ve done and what features are now available to the user.
But before beginning, an important note: we have implemented this for non-sharded clusters only. We do not support distributed transactions across different shards.
At a high level, what have we done?
We have taken MongoDB’s basic transactional behavior, and extended it. MongoDB is transactional with respect to one, and only one, document. MongoDB guarantees single document atomicity. Journaling provides durability for that document. The database …[Read more]
10 Older Entries »