I Didn't Know That - MySQL, MyISAM and Auto-Commit

i have been using MySQL - on and off - for the past four years or so, and have become fairly comfortable with its normal usage scenarios (i.e. usage that does not involve clustering, replication, etc). Hence, i was quite embarrassed to be stumped by a problem which, on hindsight, should be common knowledge to someone familiar with the database.

i had set up a MySQL instance, added a new schema, and created a few tables using the standard DML CREATE statement:

CREATE TABLE TBL_MASTER
(
    MASTER_ID    VARCHAR(15),
    FIELD        VARCHAR(255),
    PRIMARY KEY (MASTER_ID)
);

CREATE TABLE TBL_CHILD1
(
    CHILD1_ID    VARCHAR(15),
    MASTER_ID    VARCHAR(15),
    FIELD        VARCHAR(255),
    PRIMARY KEY (CHILD1_ID),
    FOREIGN KEY (MASTER_ID) REFERENCES TBL_MASTER (MASTER_ID)
);

CREATE TABLE TBL_CHILD2
(
    CHILD2_ID    VARCHAR(15),
    MASTER_ID    VARCHAR(15),
    FIELD        VARCHAR(255),
    PRIMARY KEY (CHILD1_ID),
    FOREIGN KEY (MASTER_ID) REFERENCES TBL_MASTER (MASTER_ID)
);

Then, i wired up the MySQL instance to an Apache Tomcat servlet container by using the MySQL JDBC connector driver and configuring the Tomcat datasource to point to the database schema that was created. Most importantly, i had configured the datasource to set the database connections to auto-commit = false, as i would need a group of separate database update statements to be invoked as a single transaction.

A basic connectivity test (SELECT 1 FROM DUAL) showed that it was working perfectly at this point.

Next, it was time for the web application deployed in Tomcat to actually do something, so i had the application run the following (simplified) Java code fragment. (Note: datasource is the JDBC datasource object obtained from the Tomcat container via JNDI.)

Connection connection = null;
Statement stmtMaster = null;
Statement stmtChild1 = null;
Statement stmtChild2 = null;

try
{
    connection = datasource.getConnection();
   
    System.out.println(connection.getAutoCommit()); // For debugging purposes
   
    stmtMaster = connection.createStatement();
    stmtMaster.executeUpdate("INSERT INTO TBL_MASTER (MASTER_ID, FIELD) VALUES ('000000000000001', NULL)");
   
    stmtChild1 = connection.createStatement();
    stmtChild1.executeUpdate("INSERT INTO TBL_CHILD1 (CHILD1_ID, MASTER_ID, FIELD) VALUES ('000000000000001', NULL)");
   
    stmtChild2 = connection.createStatement();
    stmtChild2.executeUpdate("INSERT INTO TBL_CHILD2 (CHILD2_ID, MASTER_ID, FIELD) VALUES ('000000000000001', NULL)");
   
    connection.commit();
}
catch (final SQLException ex)
{
    if (connection != null)
    {
        try
        {
            connection.rollback();
        }
        catch (final SQLException ignore)
        {
            //
        }
    }
   
    ex.printStackTrace();
}
finally
{
    if (stmtMaster != null)
    {
        try
        {
            stmtMaster.close();
        }
        catch (final SQLException ignore)
        {
            //
        }
    }
   
    if (stmtChild1 != null)
    {
        try
        {
            stmtChild1.close();
        }
        catch (final SQLException ignore)
        {
            //
        }
    }
   
    if (stmtChild2 != null)
    {
        try
        {
            stmtChild2.close();
        }
        catch (final SQLException ignore)
        {
            //
        }
    }
   
    if (connection != null)
    {
        try
        {
            connection.close();
        }
        catch (final SQLException ignore)
        {
            //
        }
    }
}

In the code fragment above, the line

stmtChild1.executeUpdate("INSERT INTO TBL_CHILD1 (CHILD1_ID, MASTER_ID, FIELD) VALUES ('000000000000001', NULL)");

was obviously wrong. The number of column values that i was trying to insert was less than the number of columns declared; i had carelessly missed out one column value. So when the application was run, an exception occurred, and the stack trace correctly pointed out the errant line above as the culprit. And there should be no record added into any of those three tables, since all changes were rolled back as part of the exception handling. Or so i thought.

Looking into the contents of the TBL_MASTER table using the MySQL Query Browser, it appeared that the record ('000000000000001', NULL) had in fact been inserted and committed. But yet, the line

System.out.println(connection.getAutoCommit()); // For debugging purposes

had printed out the value false, indicating that auto-commit has been correctly turned off. So what's going on here?

After some research, i found out that:

1. When i set up the MySQL instance, the default database engine was initialised to MyISAM, and this setting was not changed.

2. When creating the tables, since the storage engine was not explicitly specified, the default engine - in this case MyISAM - was used.

3. MyISAM is a non-transaction-safe storage engine, meaning, all statements are immediately committed, regardless of the auto-commit mode. Hence, rollback would not work here. (See more information on the MyISAM storage engine here, and also, Comparing Transaction and Non-Transaction Engines.) As a comparison, the InnoDB storage engine - another popular storage engine in MySQL - is a transaction-safe storage engine.

Hence, in order to achieve what i want with the code fragment above, i would need those three tables to use InnoDB (or another transaction-safe storage engine). And in order to create a table that uses the InnoDB storage engine (instead of MyISAM), i could do one of three things:

1. Change the default storage engine to InnoDB. This can be done via the MySQL Administrator. Under Health > System Variables > Table Types, change the value of the variable table_type to InnoDB. This will take effect on ALL tables created from this point on, whenever the storage engine is not explicitly declared during table creation.

2. Before executing the DML statement to create the table, set the storage engine by executing

SET storage_engine=InnoDB;

This will take effect on all tables created in the current session (as long as not overridden by (3) below).

3. Declare the storage engine explicitly when creating the table itself, e.g.

CREATE TABLE TBL_MASTER
(
    MASTER_ID VARCHAR(15),
    FIELD VARCHAR(255),
    PRIMARY KEY (MASTER_ID)
) ENGINE = InnoDB;

HTH

Tweet...

If you're keen on the occasional small dose of random tech rambling, feel free to follow me on Twitter at http://twitter.com/edwinlee11.

Ethernet Card Issue When Using AMD 64 Architecture of Linux

i would first like to give a shout out to fellow members of the Slugnet Mailing List - especially Patrick Haller - who helped me to diagnose and figure out the root cause of the problem. It was a real community effort! :-)

Previously, i was running Ubuntu Intrepid Ibex (8.10) i386 and did not encounter any networking issue. When the release candidate for Jaunty Jackalope became available, i decided to try it out (with a fresh install), opting for the AMD64 architecture so as to fully utilise the 4 GB of RAM that i have on my system.

Installation went smoothly, but after starting up, the NetworkManager applet reported that a network connection could not be established (i.e. it could not get a DHCP lease from my router).

Thereafter, a series of diagnosis and troubleshooting ensued and took up the best part of the weekend:

1. ifconfig showed that the ethernet card got detected (eth0), listing the correct HWaddr (MAC address). But of course, there was no IP address for the interface since it failed to get a lease via DHCP.

2. Networking worked fine with the i386 architecture of Jaunty Jackalope (tried using live CD). Furthermore, the NetworkManager settings on the AMD64 run were the same as those on the i386 run.

3. Doing a grep on dmesg showed that the ethernet card was correctly detected, and that the link became ready. The relevant lines were also the same across the AMD64 and the i386 runs.

4. Doing a tcpdump (while the NetworkManager applet was trying to establish a connection) showed that DHCP request packets were being correctly sent out, but with no offer coming back.

5. Statically setting the IP address, netmask, gateway and default route (instead of relying on DHCP) did not work either - regardless of whether they were set via the NetworkManager applet, or via the ifconfig and route commands). i still could not reach my router either by ping, telnet, or using the browser (router configuration web page). Furthermore, arp -an after pinging to the router - unsuccessfully (at 192.168.1.254) gave the response of (192.168.1.254) at <incomplete> on eth0.

6. The same issue surfaced when using the x86_64 architecture of Fedora 10 (again, tried with the live CD).

At that point, suspicions have started to narrow towards the ethernet card itself and/or its driver. In a subsequent post to the mailing list, i mentioned the model of the motherboard that i was using (Asus M2A-MVP) as well as its onboard LAN (Marvell 88E8001). There were apparently problems reported when using it with Ubuntu 7.04 x86_64 (http://hardware4linux.info/component/5811/).

This led to Patrick with his winning entry: https://bugs.launchpad.net/ubuntu/+source/linux/+bug/131965 (in particular, this comment - https://bugs.launchpad.net/ubuntu/+source/linux/+bug/131965/comments/19). Booting up my system with mem=3G (and having a working network connection) showed that it was the same issue as what Patrick had identified. Final confirmation was with Anton's contribution of http://kerneltrap.org/mailarchive/linux-netdev/2009/2/10/4944484.

So that was that! In hindsight i might have done better by searching for reported issues with the particular model of network card, but i suppose i was thrown off the hardware / device driver trail when tcpdump told me that the ethernet card was sending out DHCP requests correctly. For now, i am running my system on 3 GB of memory, but i will probably get a new ethernet card soon.

Recovering a Harddisk Using the Freezer

My portable harddisk had given way over the weekend, and i got an interesting piece of advice from a fellow member of the Slugnet Mailing List. He had suggested that i try and get it started one last time by placing it in a freezer for a few hours, in order to recover any important bits of data left from it.

i gave it a go then, more out of curiousity than anything else (since i had done a backup of the more important data a day ago and was generally satisfied). And what do you know? It worked wondefully! i was able to copy out the less important stuff from it as well.

One point to note is that i had to run the USB cable into the freezer to connect the harddisk so as to avoid having to take the disk out of the freezer just to mount it. Given the humidity of the region, the condensation would definitely have dealt it one final blow before i had a chance to attempt any recovery.