MySQL Identifying Blocking Transactions

Recording this incredibly helpful query from Bill Karwin’s post to SO
You can find the source query that’s blocking your DELETE by using the INFORMATION_SCHEMA.LOCK_WAITS and INNODB_TRX tables.

1
2
3
4
5
6
7
8
9
10
11
SELECT r.trx_id waiting_trx_id,
r.trx_mysql_thread_id waiting_thread,
r.trx_query waiting_query,
b.trx_id blocking_trx_id,
b.trx_mysql_thread_id blocking_thread,
b.trx_query blocking_query
FROM information_schema.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx b ON
b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.innodb_trx r ON
r.trx_id = w.requesting_trx_id;

See more information at http://dev.mysql.com/doc/refman/5.5/en/innodb-information-schema.html#innodb-information-schema-examples, under “Example 14.2 Identifying Blocking Transactions”.

Share

Fix YAML parsing errors in WebGen

After a clean install of Mac OS X Yosemite I’ve had to reinstall much of my development environment. This process was mostly beneficial as I moved to the latest and greatest tooling but several of my static websites leverage the prior major version of Thomas Leitner’s Webgen and getting version 0.5.x up and running has been a challenge.

After working through several Ruby & Gem issues I ran into a particularly opaque error when executing webgen on a project that previously generated without issue: “(): found character that cannot start any token while scanning for the next token”

This error is usually a sign that your content contains tab characters but I’d already ensured that wasn’t the case. Instead of diving deep into the workings of the libraries at play I was able to use this post from Thomas to resolve the issue by reverting to the syck TAML implementation instead of its replacement. Problem solved!

Share

Resolving H2 table lock during JUnitRunner test

After adding a new test to a project that uses Play Framework 1.2.X and thus leverages H2 for Junit functional testing, I began encountering a table lock during setup() when the framework was attempting to delete the contents of an entity table.

I initially blamed the lock on bad model design and wasted a significant amount of time tweaking JPA entity relationships and corresponding CRUD operations in an attempt to resolve it. I ultimately resolved the issue after realizing it was in fact caused by a badly designed test.

I was directly modifying entities within the transaction that wrapped my functional test and then emulating API calls that relied upon the state of those same entities. Long story short, my test was attempting something similar to this:

class FunctionalTest { //Runs entire test in the same transaction

    setup(){
        //reset H2 datastore in an distinct transaction
    }

    atest(){ 
        User u = User.findById(1);
        u.address = new Address();
        u.save();

        //test continues
    }

    btest(){
        Request req = GET("/user/1");

    }
}

In my contrived example above, after Junit runs atest() and just before running btest() - it will call setup() and attempt to reset the database in a separate transaction. But, atest() claimed a lock for insert on User and in H2 that locks the entire table by default thus setup() will be unable to reset the User table and will fail with a lock timeout.

To resolve this - you’ll either need to redesign your test to avoid manipulating entities directly in the testcase or run each test in a distinct transaction.

Share

Moved again ... this time to github.io

Blogger was just too ugly & clumsy so I used a fork of juniorz’s import.rb to migrate from blogger to Octopress. Here’s my fork:

I found MeetDom’s gist helpful while installing all the dependencies required by the import script.

Share

Plesk 11 Upgrade Unable to run Perl scripts in cgi-bin

After an upgrade to Plesk 11, my Perl scripts located under cgi-bin/ for a specific domain were not executing properly. Requests generated HTTP response code 500 and my error logs showed the unhelpful message "Premature end of script headers" I verified all permissions and even disabled / re-enabled cgi-bin support through Plesk which installs a test script under cgi-bin/test/test.cgi Even the test script failed to run. It also generated the message "Premature end of script headers: test.cgi" I eventually resolved the issue with two changes:

  1. The domain's root folder in /var/www/vhosts was not owned by the domain user. So I fixed that with a chown command.
  2. Within the Plesk control panel, I had to disable "Perl" support and enable "CGI" support to get my perl scripts with the ".pl" extension to run under cgi-bin
Share

Using iStat 2.x on Mountain Lion

Shawn Hooton wrote a piece on how to use iStat 2.x on Mountain Lion that I've used several times at this point. Linking to it here for posterity.
Share

Moved to Blogger

I started this Agile Answers blog on jroller back in 2006 and I lost some of the older content in my move to Posterous. Sadly Twitter is killing Posterous so I've had to move again...this time to Google's Blogger. Alas - I've lost all the Posterous comments and a lot of formatting in the process. As if The Sean Cook working there weren't reason enough, I now have one more reason to hate Twitter.


Share

Compressing MySQL client-server communication with Play Framework

FolderGrid provides a RESTful api that often services requests where over 90% of the entire response time is spent pulling data from a MySQL instance. I was recently working to reduce overall response time for some long running transactions in that bucket.

After optimizing my queries and my database caching - I looked at network transfer time from the dedicated MySQL server to the mysql client on the application server.  Simply enabling MySQL zlib compression resulted in a 3x performance boost for many of the larger resultsets. 

To configure your Play Framework connection pool to enable MySQL client-server zlib compression, you'll need to use the explicitly defined "db.url" instead of the shortcut "db" configuration key. Your key should look something like this:

db.url=jdbc:mysql://remotehost/dbname?useUnicode=yes&characterEncoding=UTF-8&connectionCollation=utf8_general_ci&useCompression=true

 

 

Share

When not to front your application with nginx HttpProxyModule

Throwing nginx (or Apache or lighthttpd) in front of other application stacks is so common that we often don't think twice about the possible costs. This bit me over the weekend when I casually threw up nginx for SSL termination and configured HttpProxyModule to proxy to my Play Framework driven application.

I failed to heed the first section in the HttpProxyModule docs. See that note about how "the entire client request will be buffered in nginx before being passed on to the backend proxied servers" ?

That's one note you are going to care about when you're handling large requests and can't figure out why your application is so slow to respond to them.

RTM 

 

 

Share

Supporting multiple file upload runtimes with pupload inside ExtJS 4

The reference client I built for FolderGrid's API is a zero footprint web client created using ExtJS 4. I wanted to support drag & drop file uploads for advanced browsers but degrade nicely all the way back to IE6 for those poor lost souls stuck with tech from the last decade. So I integrated plupload and bound the two together with some jquery glue.

The results are working well in production and the client itself is open & licensed under GPL v3 per the terms of ExtJS 4. So if you are interested in copying & extending - have at it.

 

 

 

 

Share