[Mysql>Mysql-proxy]Read/Write Splitting Functionality with mysql-proxy doesn't work properly.

Troubleshooting Read/Write splitting with mysql-proxy

References

• Blog About Read/Write Splitting with mysql-proxy.
  http://jan.kneschke.de/2007/8/1/mysql-proxy-learns-r-w-splitting
  http://www.infoq.com/news/2007/10/mysqlproxyrwsplitting
  

Description
I'm using Grails Development Framework when building the web application. Grails is very useful and powerful framework and Grails is Convention over Configuration Framework like Ruby on Rails.
In case of using mysql database, we sometimes use the master-slaves replication architecture to scale out our backend database. But in case of using Grails, it is very difficult for us to split the database connection to Read and Write because Grails(or rails) creates database connections pointing to the single database automatically when starting up.
However, we can scale out our database easily if we use mysql-proxy Read/Write Splitting. (Please read the references above to understand the detail of Mysql Read/Write Splitting.)
When using the mysql-proxy, we had some problems as follows.

Problem1

• Description
  After running mysql-proxy process, following error message is outputted.
  Error message is:
  

  (lua-error) [(path to mysql-proxy)/mysql-proxy-0.6.0/share/mysql-proxy/rw-splitting.lua]
  ...mysql-proxy-0.6.0/share/mysql-proxy/rw-splitting.lua:29: module 'proxy.commands' not found:
  

  
  Read/Write splitting functionality doesn't work fine when this error occures, but we can connect only to the master database even though this error occures. All of the sql statement(insert/update/delete and select) send only to the Master database.(lua-script file doesn't work properly and read-only-backend connections are ignored.)
  
  
• Solution
  Set the "LUA_PATH" environment variable before starting the mysql-proxy process.The value of LUA_PATH should be pointing to the lua script directory and lua files including the "proxy" directory and lua files(commands.lua file is stored under the proxy directory). In our environment, we are installing mysql-proxy under /var/apps/mysql-proxy-0.6.0 directory.In case of this, we should set the LUA_PATH to

  LUA_PATH="/var/apps/mysql-proxy-0.6.0/share/mysql-proxy/?.lua" 

Problem2

• Desciprtion
  After running mysql-proxy process, following error message is outputted.
  Error mesage is :
  

  mysql-proxy: error while loading shared libraries: libevent-1.3e.so.1: cannot open shared object file: No such file or directory

• Solution
  Need to set LD_LIBRARY_PATH to the directory including the libevent-1.3e.so.1 file.
  Normally this file is stored in /usr/local/lib/, it should be set to LD_LIBRARY_PATH=/usr/local/lib/ before starting mysql-proxy.
  

Problem3

• Description
  Sometimes mysql-proxy doesn't work properly, all of sql statement send to the master database. In this case, some following messages are outputted on console window like this.
  

  luaL_loadfile(/var/apps/mysql-proxy-0.6.0/share/rw-splitting.lua) failed
  lua_load_file(/var/apps/mysql-proxy-0.6.0/share/rw-splitting.lua) failed: cannot open /var/apps/mysql-proxy-0.6.0/share/rw-splitting.lua: No such file or directory

  the rw-splitting.lua script file does not work properly when the error message is outputted on console windonw.
  
• Solution
  Please check the value of LUA_PATH environment variable and proxy-lua-script parameter value before starting mysql-proxy process. The value of LUA_PATH should be pointing to the lua script directory and lua files including the "proxy" directory and lua files(commands.lua file is stored under the proxy directory).
  In case of our environment(we are installing mysql-proxy under /var/apps/mysql-proxy-0.6.0 directory), we should set the LUA_PATH and proxy-lua-script parameter to
  

  LUA_PATH="/var/apps/mysql-proxy-0.6.0/share/mysql-proxy/?.lua"
  /var/apps/mysql-proxy-0.6.0/sbin/mysql-proxy \
  .....
  --proxy-lua-script=/var/apps/mysql-proxy-0.6.0/share/mysql-proxy/rw-splitting.lua
  ....
  

Problem 4

• Description
  The information for proxy-read-only-backend-addresses parameter is not displayed when executing the "select * from proxy_config" via mysql-proxy administration address.
  In our environment, the output of the proxy_config is as follows. No proxy-read-only-backend-addresses address information is found.
  

  +----------------------------+----------------------------------------------------------------+
  | option                     | value                                                          |
  +----------------------------+----------------------------------------------------------------+
  | admin.address              | localhost:20102                                                |
  | proxy.address              | localhost:20101                                                |
  | proxy.lua_script           | /var/apps/mysql-proxy-0.6.0/share/mysql-proxy/rw-splitting.lua |
  | proxy.backend_addresses[0] | localhost:10101                                                |
  | proxy.fix_bug_25371        | 0                                                              |
  | proxy.profiling            | 1                                                              |
  +----------------------------+----------------------------------------------------------------+
  

• Solution
  I don't know the reason why this problem occures. I'm investigating about this issue.
  But Read/Write splitting function with mysql-proxy works fine even though this problem occures.
  

In our environment, we use following statements to startup the mysql-proxy process.

export LD_LIBRARY_PATH=/usr/local/lib/
export LUA_PATH="/var/apps/mysql-proxy-0.6.0/share/mysql-proxy/?.lua"
/var/apps/mysql-proxy-0.6.0/sbin/mysql-proxy \
--admin-address=localhost:20102 \
--proxy-address=localhost:20101 \
--proxy-read-only-backend-addresses=localhost:10201 \
--proxy-backend-addresses=localhost:10101 \
--proxy-lua-script=/var/apps/mysql-proxy-0.6.0/share/mysql-proxy/rw-splitting.lua \
--pid-file=/tmp/mysql-proxy.pid