There is some collateral damage showing up on FATE due to that indexing solution deployed last week, a side effect that should also be obvious to anyone with one or two computer science courses behind them: Indexing a column of a table means that the index must be updated during each insert which makes the insert operation slower. The net effect is that it increases the opportunity that the FATE page cache generator might erroneously report too few tests corresponding to a build record. A previously yellow box (meaning that one or more tests failed) is green but closer inspection reveals only 21/21 tests succeeded.

What is happening is that, when a FATE build/test cycle is complete for a configuration, the script enters a new build record and, if the build succeeded, proceeds to enter a new test result record for each of the (currently) 111 tests. When the page cache update script kicks in on its usual 15-minute interval, a client script might still be inserting build records, leading to a race condition. I mitigate this possibility with the following algorithm:

  query the test result set corresponding to the a build record
  current_count = items in test result set
  while (1):
    query test results again
    new_count = items in test result set
    if current_count == new_count:
      break
    else:
      current_count = new_count
      wait 4 seconds and try again (up to 10 times, then quit the script;
        it'll try from cron again anyway)

The heuristic actually works quite well. However, sometimes the server is extremely bogged down for one reason or another and the insert operations are occurring more than 4 seconds apart, or perhaps the client lost connection before it could enter all the test results.

The proper solution to this would be database transactions. MySQL is not renowned for its transactional support. True, version 5 is supposed to support them, and I am on version 5. But it requires special configuration that I don’t know how to perform and am not sure if I even have the access privileges to set up. But I have determined empirically that transactions are not supported with my current database and configuration (method: create a new table; insert a new record; start a transaction; update the record; query the record from a different session; the record has been updated, ergo, the START TRANSACTION was not honored).

Idea: Feign a transaction by adding a new field to the build record table, a boolean flag to indicate if the record is complete. When the build record is first entered, the flag is cleared. Only after all the test results have been entered will the flag be manually set true. Using this method, FATE will easily be able to find build records that were completed. This has the downside of leaving specious, “zombie” data in the tables and I will probably need to create a process for periodically cleaning the cruft in lieu of proper transaction/rollback support.

A perfect hack solution, I hope. We make do with what we have because MySQL is just so fast and free.