- About connection character set for read-only backend server
http://forums.mysql.com/read.php?146,178012,178822 - Mysql Proxy Scripting manual
http://dev.mysql.com/doc/refman/5.1/en/mysql-proxy-scripting.html - Manipulating Results with read_query_result()
http://dev.mysql.com/doc/refman/5.1/en/mysql-proxy-scripting-read-query-result.html
We can split the mysql database connection between master and slave databases by using mysql-proxy. In case of using rw-splitting.lua script, mysql-proxy doesn't send the "SET xxx" commands to the slave databases. According to the http://forums.mysql.com/read.php?146,178012,178822, this is expected behavior of mysql-proxy.
But sometimes we need to send the character set parameters to the slave database before sending the SELECT statement. I customized rw-splitting.lua file and send the "set character_set_results=xxxxx" command to the slave database before executing the SELECT statement.
(But I think it is not the excellent way of customization because the SET command is sent everytime before executing SELECT, If you have any good idea, please let me know.)
Steps to customize rw-splitting.lua
- Open the rw-splitting.lua
- Add followings into the read_query function between debug output and return statement.
- Change the line "local queryCharset="set character_set_results = latin1".
Change the sql statement you want to execute before query. - Restart mysql-proxy process
-- read/write splitting
function read_query( packet )
....
....
-- send to master
if is_debug then
if proxy.connection.backend_ndx > 0 then
local b = proxy.backends[proxy.connection.backend_ndx]
print(" sending to backend : " .. b.address);
print(" is_slave : " .. tostring(b.type == proxy.BACKEND_TYPE_RO));
print(" server default db: " .. s.default_db)
print(" server username : " .. s.username)
end
print(" in_trans : " .. tostring(is_in_transaction))
print(" in_calc_found : " .. tostring(is_in_select_calc_found_rows))
print(" COM_QUERY : " .. tostring(cmd.type == proxy.COM_QUERY))
end
--start customization
--add customization by tatsuya anno(taapps@gmail.com)
--Send "set character_set_results=XXXX" command to the read-only backends
--before executing SELECT statement
if string.byte(packet) == proxy.COM_QUERY then
local query = string.sub(packet, 2)
local f_s, f_e, command = string.find(packet, "^%s*(%w+)", 2)
command=string.lower(command)
local b = proxy.backends[proxy.connection.backend_ndx]
if is_debug then
print("[Ta-Check-03-4] cmd(lower)="..command)
print("[Ta-Check-04-1]sending to backend : " .. b.address);
print("[Ta-Check-04-2]is_slave : " .. tostring(b.type == proxy.BACKEND_TYPE_RO));
end
if command == "select" and b.type == proxy.BACKEND_TYPE_RO then
local queryCharset="set character_set_results = latin1"
if is_debug then
print("[Ta-Check-04-3]queryCharset="..queryCharset)
end
proxy.queries:reset()
proxy.queries:append(999,string.char(proxy.COM_QUERY)..queryCharset)
proxy.queries:append(1,string.char(proxy.COM_QUERY)..query)
end
end
--end of customization logic by tatsuya anno
return proxy.PROXY_SEND_QUERY
end
