Innodb purging stalls

One of my clients reported an incident where they are getting 'Table Full' error against Innodb table. However, there seem to be ample free Innodb space available. So, when I looked at host server, I found they are running old mysql 5.0 release on 32bit platform, Innodb monitor output show:

History list length 2051529 
..
Main thread process no. 9701, id 1111792528, state: sleeping


Oh Innodb purge thread isn't running, and there is no change in "history length".
Note: Purge thread never progresses when the main thread is in a 'sleep' state.

Related BUG 13847885. Quoting:
 Deleting a huge amount of data from InnoDB tables within a short time could cause the purge operation that removes delete-marked records to stall. This issue could result in unnecessary disk space use, but does not cause any problems with data integrity. If this issue causes a disk space shortage, restart the server to work around it. This issue is only likely to occur on 32-bit platforms
Bug fixed in 5.0.97, 5.1.63, 5.5.23


Solution: Restart MySQL and upgrade MySQL version

MySQL - working with xml data

MySQL (5.1 and onward) provide XML functions; such as ExtractValue() that can be helpful to read required elements from xml; instead of returning entire xml back to the client application we can return one (or more) xml elements containing required data.

ExtractValue(xml_fragment, xpath_expression)

http://dev.mysql.com/doc/refman/5.1/en/xml-functions.html
In the following example, we use ExtractValue() with column content as the first argument. For the second argument, we employ an XPath expression that means “the content of the element having the name attribute "bankName"”:

SELECT ExtractValue(content, '//variable[@name="bankName"][1]') "bankName" from xml_test where id=2;  
 +----------+  
 | bankName |  
 +----------+  
 | xyz      |  
 +----------+  
 1 row in set (0.00 sec)  

 We can read more than one attribute:

SELECT ExtractValue(content, '//variable[@name="bankName"][1]') "bankName", ExtractValue(content, '//variable[@name="productType"][1]') "productType" from xml_test where id=1;  
 +----------+-------------+  
 | bankName | productType |  
 +----------+-------------+  
 | xyz      | cvv=***     |  
 +----------+-------------+  
 1 row in set (0.00 sec)  

Similarly we can extract all attributes of a particular xml element 

 SELECT ExtractValue(content,'//dictionary[@name="client_b"]/*') as "all attributes" from xml_test where id=2;  
 +----------------+  
 | all attributes |  
 +----------------+  
 | abc cvv=***    |  
 +----------------+  
 1 row in set (0.00 sec)