I recently blogged about diagnosing an auto increment and gap locking problem over at the Ideeli Tech Blog. Check it out!

…

There are four SQL transaction isolation levels supported by InnoDB: REPEATABLE READ, READ COMMITTED, READ UNCOMMITTED, and SERIALIZABLE. Because READ UNCOMMITTED and SERIALIZABLE are rarely used, I am going to outline the distinction between READ COMMITTED and REPEATABLE READ. Perhaps I will follow up with SERIALIZABLE and READ UNCOMMITTED if there is interest.

REPEATABLE READ:
The state of the database is maintained from the start of the transaction. If you retrieve a value in session1, then update that value in session2, retrieving it again in session1 will return the same results. Reads are repeatable. Repeatable Read.

session1> BEGIN;
session1> SELECT firstname FROM names WHERE id = 7;
Aaron

session2> BEGIN;
session2> SELECT firstname FROM names WHERE id = 7;
Aaron
session2> UPDATE …

Again, I have moved to a new hosting provider after my free-tier with Amazon EC2 expired.  As usual I was looking for a good VPS provider with a decent price, providing good support and in particular a provider supporting FreeBSD, my favorite OS for server (for desktop I still prefer GNU/Linux.)

This time I have carefully reviewed many options and have finally settled with RootBSD, one of the reputed VPS hosting providers if you are choosing FreeBSD as your server OS.  One of the prime reasons for choosing FreeBSD is its performance, stability and the FreeBSD ports system.

Although my …

“Legacy MySQL does not scale well on a single node, which forces granular sharding and explicit application code changes to make them sharding-aware and results in low utilization of severs”– Dr. John Busch, Schooner Information Technology A super-set of MySQL suitable for Big Data? On this subject, I have interviewed Dr. John Busch, Founder, Chairman, [...]

In a recent rant on Why I think SQL_MODE is useless…, I wanted to counteract this statement with why we MUST all use SQL_MODE, even with the inherit flaws.

The fundamental principle of a database is to restore and retrieve data. When I can insert data into the database and then I select this data it is different, this is fundamentally wrong. This is a loss of essential data integrity, something a database should NEVER do.

SQL_MODE solves the problem of “silent truncation” in most instances, and produces an all important error. As pointed out, the SQL_MODE has several limitations, however the benefits do out way the risks. Quality control on source code can reduce the limitations, but no amount of coding can stop the CRUD that comes out of the database without some SQL_MODE settings.

I would ask two more …

Tuesday, February 21, 2012
10:30 AM – 3:00 PM
Oracle Office
520 Madison Ave, Suite 3000
New York, 10022

Register Here!

You’re Invited – Oracle’s MySQL Tech Tour Event – New York, NY

Join us for our latest Tech Tour event in New York. Get information and advice direct from the MySQL technical experts. In these sessions, we will demonstrate how to better develop, integrate, secure, and tune MySQL for your application.

Morning Session:

MySQL Fundamentals

Fundamental Capabilities
Deployment options
Development tools
Suggested next steps

Afternoon Session: Best Practices

Deploying Large & Mission Critical MySQL Applications

Architecture
High Availability & …

I will part of a panel discussing “SQL vs NoSQL along with Derick Rethans, Johannes Schlüter and Helgi Thorbjoernsson as the closing session on Saturday the 25th at PHP UK. Johannes and I may have a hard time as MySQL has both SQL and NoSQL support.

…

Good books on databases are rare. So permit me to step out of my normal MySQL-centric role and review a new book that is very good and covers a lot of generic relational database territory while also teaching the basics of Oracle 11g. I would estimate that 10% of the book is 11g and the rest would be valuable to new DBAs of other database systems. Besides, seeing how other databases perform some tasks differently may spur you to improve your own.

OCA Oracle Database 11g: SQL Fundamentals I: A real-world Certification Guide
Author: Steve Reis

Database books are hard to write. Databases are not warm, easy to understand and the many concepts can require a student on the subject to concentrate on many obtuse factors all at one time. Presenting the material in a clear and concise fashion can be hard. Providing examples that show the various concepts without being silly or obtuse is harder. And keeping the book readable …

The March meeting of the North Texas MySQL Users Group will be March 12th from five to seven PM at the Irving office. Pizza will be provided and a special guest speaker is double checking their schedule. Come come network, gather swag, and learn more about MySQL.

Please RSVP below by leaving a comment so we can plan on a) enough pizza, b) any special pizza topping request, and c) have enough meeting space for the pizza.

Oracle Office
6031 Connection Drive
Irving, TX 75039

…

Sometimes, I make mistakes. It’s true. That can be difficult for us Systems Engineering-types to say, but I try to distance myself from my ego and embrace the mistakes because I often learn the most from them. ..Blah, blah, school of hard knocks, blah, blah…. Usually my mistakes aren’t big enough to cause any visible impact, but this one took the site out for 10 minutes during a period of peak traffic due to a confluence of events.

Doh!

Here is how it went down…

We have an issue where MySQL table statistics are occasionally getting out of whack, usually after a batch operation. This causes bad explain plans, which in turn cause impossibly slow queries. An ANALYZE TABLE (or even SHOW CREATE INDEX) resolves the issue immediately, but I prefer not get woken up at 4AM by long running query alerts when my family and I are trying to sleep. As a way to work around the issue, we decided to disable InnoDB automatic …