How do I configure a simple TCP proxy that can rewrite SQL from a client to a MySQL server?

Although this is technically something that could be done with an iRule, using binary scan, it would be a very complex task. You would effectively need to reimplement the MySQL Client/Server protocol in an iRule, both to decode the incoming packet, and to create a new one.

It may be that for your particular needs, you could reduce the requirements to only meet what your application uses, but doing that could also limit your options in the future.

Perhaps this is something that could be implemented by modifying the client application, or using mysql triggers on the database instead ?

In future versions of BigIP, iRulesLX should make this sort of task easier, as you would be able to pull in a mysql client library, but currently this is an Early Access feature only.

Thank you for the answer about the possibility of using an iRule. I had not heard of iRulesLX so I will look into that. Is there any estimate on when iRulesLX will be released?

If we ignore the iRule rewrite portion for the moment, can you please answer how I would go about implementing just the simple TCP proxy portion?

Hi Jminfused,

as Ian has already mentioned, it would be a tough challenge to build an MySQL protocol parser/rewrite module using iRules.

IMHO it would be far easier to use one of the available MySQL proxy solutions to solve your current "on the wire rewrite" requirements. Our developers for an example had recently investigated into the "ProxySQL" project and they where really impressed...

http://www.proxysql.com/

http://de.slideshare.net/renecannao/proxysql-high-performance-and-ha-proxy-for-mysql

Cheers, Kai

Ok, I was able to get it working using the information provided here. First I had to create a Standard Virtual Server with a TCP protocol and profile, with Source Address Translation set to Auto Map. Then I added a pool with the MySQL server in it and gave it a TCP monitor and attached them together with the Virtual Server.

Next comes the iRule, which is below and is correctly adding an index hint to the SQL "on the wire." The code is still a bit ugly with magic numbers and strings, but it works:

when CLIENT_ACCEPTED {
  TCP::release
  TCP::collect
}

when CLIENT_DATA {
  binary scan [TCP::payload] ica* length cmd sql
  set cmd [expr { $cmd & 0xff }]
  if { $cmd eq 3 } {
    set length [expr { $length & 0xffffffff }]
    if { $sql contains "WHERE Contact.IsUser <=> '0' AND `ContactGroupAssign`.`DateCreated`" } {
      set location [string first "FROM ContactGroupAssign INNER" $sql]
      if { $location > 5 } {
        TCP::payload replace [expr $location + 29] 0 "use index (DateCreated) "
        set newLen [expr $length + 24]
        set replacement [binary format i $newLen]
        TCP::payload replace 0 4 $replacement
        log local0.alert "MySQL rewrite: added \"use index (DateCreated) \""
      }
   }
  }
  TCP::release
  TCP::collect
}