S h o r t S t o r i e s

// Tales from software development

Archive for April 2011

Avoiding table locking in MySQL

leave a comment »

As a follow on to the previous post, I needed to change a DATETIME column in a table to a BIGINT column and to convert all the DATETIME values to .NET DateTime serialized binary data.

I wrote a small C# program to execute a SQL SELECT statement to return all the rows in the table, read the DATETIME column values, and then execute a SQL UPDATE command to write the DateTime ToBinary() value into the BIGINT column.

I tested the program successfully on a small test database with a few hundred rows in the table but when I tried to execute it against the live database that had 70,000+ rows in the table it failed when the first UPDATE command timed out.

I guessed it was a locking issue and looked up the syntax of the MySQL SELECT statement. There are locking hints that allow you to modify the locking that MySQL performs but not to turn locking off.

The solution was to use the SQL_BUFFER_RESULT keyword available on the SELECT statement:

SELECT SQL_BUFFER_RESULT <column-list> FROM <table>;

This ensures that the query results are copied to temporary storage and the table is locked for the minimum time possible.

The program ran successfully after this was added to the SELECT statement.

Written by Sea Monkey

April 5, 2011 at 8:00 pm

Posted in Development

Tagged with , ,

MySQL doesn’t store fractions of a second in DATETIME

leave a comment »

I just found out the hard way that MySQL doesn’t store fractions of the seconds component of a date/time in the DATETIME datatype.

It turns out this is a long standing request for the MySQL development team that for various reasons has yet to be addressed.

This is a problem for an interface I wrote last year that retrieves laboratory test results for patients. The host system queues notifications when a patient’s data is updated but the notifications don’t indicate what was updated. So, the interface requests test results based on a date range from the last time that test results were processed for this patient to the current system date/time. When the interface processing is complete the date range end date/time that was used is stored for use as the date range start date/time when a notification for the same patient is next received.

It turned out that there is a problem with this process because the date/time for when a test result is stored in the host system is assigned when the host system receives the test result and begins to process it, not at the end of processing when the test result is stored. This causes a small discrepancy of up to a second between the date/time that is assigned to the test result and when it has actually been stored and is available to be retrieved. This means that it’s possible to request test results for a date range that includes a newly added test result but the test result is not one of the test results returned because it has not yet been stored in the host system. The next time a request is made for test results for the patient, the start of the date range will be after the date/time of this test result and so it will never be processed by the interface.

The solution is to store the date/time for the most recent test result rather than the current date/time. This ensures each time test results are requested the start of the date range is from the date of the last test result that was processed by the interface. However, this requires at least millisecond precision in the specification of the date range start to ensure that previously processed results are not requested again.

The date range start and end values appear in the interface’s log file with the time component formatted as hh:mm:ss. I’d assumed that the date/time values were being stored with the fractional part of the second but when I implemented the change described above to the processing and updated the time formatting of the date range values in the log messages to show the milliseconds value I found that this was always .000. I checked the MySQL documentation and found that MySQL does not store fractions of a second.

Fortunately, in the processing performed by the interface this DATETIME column value is not used in SQL SELECT statement WHERE clause criteria or in SELECT statement date/time functions in the column list. The value is simply retrieved, updated, and stored again.

My first thought was to store the date/time value formatted as a string value including the milliseconds value. Although this would work there is a better option. The date/time value can be stored as a large integer representing milliseconds from a known point in time, e.g. 01/01/0001, 01/01/1900, etc. Normally, this would require a method to encode a date/time value as an integer and another to decode it but these are not necessary if you’re using .NET.

The .NET DateTime type is implemented as a 64bit integer that represents 100 nanosecond intervals, known as Ticks, since 01/01/0001 00:00:00. An instance of a DateTime type can be created from a Ticks value and the type implements a Ticks property. So, it’s easy to save a DateTime instance as a Ticks value to MySQL and also create a DateTime instance from a Ticks value retrieved from MySQL. But there is an even better way…

The DateTime type also implements ToBinary() and FromBinary() methods for serializing DateTime values as 64 bit integers. In addition to the Ticks value stored in the rightmost 62 bits, the first two bits are used to store the DateTimeKind enumeration value associated with the DateTime instance that indicates whether the DateTime value is a local time value (including daylight savings information), a UTC value, or whether this is unspecified.

Using these two methods makes it simple to serialize and deserialize .NET DateTime values to and from a MySQL BIGINT datatype without having to write your own methods to serialize the value and also preserves the DateTimeKind information.

Written by Sea Monkey

April 1, 2011 at 7:00 pm

Posted in Development

Tagged with , , ,