I began to write a post on InnoDB transactions, but there was so much background material that I decided first to write a post introducing transactions, and then one on how InnoDB implements them.  If there is good response from these two posts, I will continue with other posts on the major storage engines and their transactional characteristics.

When reading through literature about RDBMSs (Relational Database Management Systems), you will see the terms transaction(s), and ACID-compliance.

A transaction is a sequence of statements that are executed on a RDBMS. For a transaction to be completed and database changes made permanent, a transaction has to be completed in its entirety. If a transaction is not completed for any reason, it is rolled back to the original state of  the database before the transaction began execution. The characteristics that define a transaction are called ACID.  ACID stands for: Atomicity, Consistency, Isolation, and Durability.

According to Wikipedia’s article on ACID, it is “a set of properties that guarantee that database transactions are processed reliably.” It is useful to understand the different parts of ACID, so here they are briefly:

Atomicity

The ability of the database to guarantee that either every part of the transaction is performed or none is performed.

Consistency

The database remaining in a consistent state before the start of a transaction and after the transaction is finished.

Isolation

The ability of the database to make operations in a transaction appear isolated from other concurrent operations.

Durability

The guarantee that once the user has been notified of success, the transaction will persist through all conditions, including system failure.  Durability is frequently accomplished through writing all transactions to a log file.  This log file can be “replayed” in the event of a system failure.  It is only after the transaction is written to this log file that the transaction can be considered safe.

So now you have some background.  Next time, we will take a look at how the InnoDB storage engine implements transactions.

As an editor (of http://www.mysqlzine.net), I cringe at the title of this post. However, it is absolutely accurate.  Recently, we had a situation where we had two servers running Sun Solaris 10 on some high-end Sun hardware.  I don’t remember exactly, but it was one of Sun’s upper-end boxes with AMD procs. Nice boxes, really. The two servers are configured in a master-master circular replication setup.

Here is the problem.  On both servers, the error logs were being created incorrectly.  On one of them, it was creating an error log that was 154 megabytes in size.  FLUSH LOGS worked, but the newly-created error log would be the same size. While there was some data in the file that I could use the cat, head or string command to discern, the majority of the file was not text data.

After working on this for a bit, I logged into the secondary server and discovered that the error log on this server didn’t look right either  — the same characteristics of large size with almost no actual text data.  The only difference is that these error logs were around 20 megabytes in size. I googled around a bit and couldn’t discover anyone with a similar problem.

I can’t figure out what was causing this.  We checked everything we could think of, and during some other maintenance, restarted the mysqld daemon. That didn’t work either — when the server came back online it was experiencing exactly the same problem with the error log. Finally, during hardware maintenance to upgrade the memory, the servers were rebooted.  The next morning, I checked them, and found both error logs working exactly as they should. However, it took that server reboot to fix the problem.

I am at a loss to explain what was wrong.  If anyone has any thoughts or a similar experience, I would love to hear from you!

I just wanted to thank everyone who participated in the survey that Mark Schoonover and I created. My endless thanks goes to Mark who did a lot of work on this.

The results will be coming out in the Summer issue of MySQL Magazine which will be online July the 15th. I am putting together the articles now and it looks like it’s going to be a great one!

Recently, a customer wondered if they should start using the innodb_file_per_table option, or if they should continue to use the large InnoDB tablespace files created by the innodb_data_file_path option in the my.cnf option file.

Many people still use the older innodb_data_file_path option because it is the default for MySQL server. So, what are the benefits of using innodb_file_per_table instead?

The innodb_file_per_table makes for easier-to-manage files. With this option each InnoDB table has its own data and index file under the database directory. As an example, if you had table foo located in database xyz the InnoDB data file for table foo would be /var/lib/mysql/data/xyz/foo.idb. Each table would have its own idb table in the appropriate database directory. This is in contrast to using the innodb_data_file_path option with (typically) one large file in the root of your data directory. For example, it might be /var/lib/mysql/data/ibdata1.idb. All table data and indexes would be stored in this one file, and it can be very large  and unwieldy. I don’t recall the largest ibdata file I have seen, but what do you do if you have a 100 gig InnoDB tablespace file? It can, and does, happen. The file contains what amounts to all the data of all your databases on the server.

(more…)

I have a question I wanted to throw out. The term “GA” gets batted around all the time as meaning, the production-ready version of MySQL server. However, googling for quite a bit, I can’t find a definition for GA (other than what I stated above, i.e. production-ready). What does this mean in terms of bugs? Features? Anything else I might be missing?

I believe it means that there are no known “critical” (whatever that means) bugs and there will definitely be no more features added. Can anyone point me to a good definition?

In case you haven’t heard, on Monday, MySQL released the next RC of 5.1.25. It is available to the community, so download it now and take it for a spin!

http://dev.mysql.com/downloads/mysql/5.1.html.

I finished uploading the backup presentation that I did last Monday at the Boston MySQL User’s Group. It is here: http://www.paragon-cs.com/presentations.

I cover the basics of backup/recovery and disaster planning. Total time is about an hour and three minutes. It was a lot of fun and the Bostonians seem to appreciate it. Thanks, Sheeri for filming and editing!