MySQL Proxy recipes: returning an error

Returning an error message is one of the tasks that may become common when working with MySQL Proxy.
Let's say that you want to prevent users from querying your database at a given time. The most sensible answer that you would send to the client that is requesting a query in the forbidden period is an error message. Not only that, the client must receive an error code and SQL state, as if the error were issued by the database server.
With MySQL Proxy, an error set is a legitimate packet to be sent back to the client, and thus you can return a customized error in answer to any query.

function error_result (msg, code,state)
  proxy.response = {
      type        = proxy.MYSQLD_PACKET_ERR,
      errmsg      = msg,
      errcode     = code,
      sqlstate    = state,
  } 
  return proxy.PROXY_SEND_RESULT
end

proxy.response is a variable type structure. When its type is set to proxy.MYSQLD_PACKET_ERR, you can return the three components of a error packet quite easily. For example, to prevent queries between 13:00 and 14:00, you can use this code:

    local t = os.date('*t')
  if t.hour == 13 then
      return error_result(
         'no queries allowed between 13:00 and 14:00', -- error message
         1314,                                         -- error code
        '!1314'                                        -- SQL state
      )
  end

and the user will receive the error that looks like any other issued by the server:

show tables;
ERROR 1314 (!1314): no queries allowed between 13:00 and 14:00

This post is part of a set of recipes that will eventually become a long article.
Want to hear more? Attend MySQL Proxy - The complete tutorial at the MySQL Users Conference 2008.