[Mysql]Seconds_Behind_Master is sometimes increasing, and then set to null or zero again and again

Seconds_Behind_Master is sometimes increasing, and then set to null or zero again and again

• References
  
  • Mysql Documentation about Seconds_Behind_Master
    
• Description
  The value of Seconds_Behind_Master means the "This field is an indication of how “late” the slave is" in replication slave database. This value is often increased when the synchronization process in slave database is slower than master database's state.
  But sometimes the value of the Sseconds_Behind_Master is increasing, after a few seconds the value of Sescond_Behind_Master is displayed as zero or null quickly and again and again. The value of Slave_IO_Running is sometimes displayed as "No" when the Second_Behind_Master is displayed as Null.
  
• Cause
  When the server_id parameters between slave servers are duplicated, this problem is occured. The server_id value is unique identifier of the slave server.
  When some slave's server_id are duplicated, maybe master server recognize the same server, so one slave server is connecting and communicate with the the master server, other slave server is refused connecting to the master(at this time, slave's Slave_IO_Running status is set to Null because connection refused.)
  
  In our environment, this problem occures when one master database server has some slave servers, and some slave servers connects to the master database server and starts the replication at the same time. In this case, each slave server has to have the different server_id parameter when connecting to the master server.
  

[Grails]Upgraded Split Write Database Connection Plugin for master/slave configuration(Alpha version 0.3)

please refer to http://taapps-javalibs.blogspot.com/2009/04/grailsupgraded-split-write-database.html

[Mysql]some errors occures while compiling the mysql source code manually

Following errors occures while compiling the mysql database source code manually.

termcap error

• Error Message
  checking for termcap functions library... configure: error: No curses/termcap library found
• Solution
  Need to install ncurses-devel before compilation
  execute "yum -y install ncurses-devel"(if you want to install by using yum) and install ncurses-devel package
  

../depcomp: line 571: exec: g++: not found

• Error Message
  ../depcomp: line 571: exec: g++: not found
• Solution
  Need to install gcc-c++ compiler before manual compilation of the mysql
  execute "yum -y install gcc-c++" to install gcc-c++
  

../include/my_global.h:1078: error: redeclaration of C++ built-in type 'bool'

• Error Message
  ../include/my_global.h:1078: error: redeclaration of C++ built-in type 'bool'
  make[1]: *** [my_new.o] Error 1
• Solution
  Need to re-configure after installing the gcc-c++ compiler.
  After installing the gcc-c++ compiler, please execute "./configure" command with the correct options.

unrecognized options error

• Error Message
  configure: WARNING: unrecognized options: --with-innodb --with-partition .....
• Solution
  This "--with-innodb" or other options starts with "--with-" are somtimes invalid. We have to use the "--with-plugins=innobase" instead of using "--with-innodb" option when configuring the mysql compile options.
  
• References
  http://www.mysqlperformanceblog.com/?s=mysql+config+with+plugin+innobase
  http://www.linuxfromscratch.org/blfs/view/svn/server/mysql.html
  http://d.hatena.ne.jp/tomoyamkung/20081225/1230165292(Japanese)
  

[Mysql>Mysql-proxy]not able to send SET commands to the slave database before executing query.

References

• 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
  

Description
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

1. Open the rw-splitting.lua
2. Add followings into the read_query function between debug output and return statement.
3. Change the line "local queryCharset="set character_set_results = latin1".
   Change the sql statement you want to execute before query.
   
4. Restart mysql-proxy process
   

Customized Source code

-- 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

[MySQL]MySQL Query Cache and JDBC PreparedStatement

[Problem]
MySQL has a caching mechanism and functionality for the result of executing sql,
it calls "Query Cache", and this function is very useful and it's helpful for improving the database performance. But in our Web System, Query Cache functionality has some problem as follows.

1. When using JDBC PreparedStatement, Query Cache doesn't work properly, MySQL Database never use the query cache even though I send the same SQL stetement to the Database.
2. when the hit ratio of Query Cache is very low, execution time of query cache functionality is bottlenecked.
   

[Description]
About (1) described above, it is correct that MySQL doesn't work fine when using the PreparedStatement . MySQL Documentation says that Server-Side PreparedStatement never use the query cache. Please refer to http://dev.mysql.com/doc/refman/5.0/en/query-cache.html
「Note: The query cache is not used for server-side prepared statements. If you're using server-side prepared statements consider that these statement won't be satisfied by the query cache. 」
But we can avoid this problem if we use Client-Side PreparedStatement instead of Server-Side
PreparedStatement. If we want to use Client-Side PreparedStatement instead of Server-Side in our System, add the "useServerPrepStmts=false" parameter into the JDBC URL when connecting to your database.
jdbcUrl="jdbc:MySQL://hostname:portnumber/database?useServerPrepStmts=false

About (2) described above, performance is porr if the hit ratio of query cache is very low. because mysql database engine will check whether system always caches the result of executing
sql statement or not before running SQL. hence the performance is slower than not using query cache functionality if the hit ratio of query cache is low.

[Mysql>Mysql-proxy]some error occures while compiling mysql-proxy manually

Troubleshooting compiling mysql-proxy, some error occures while compiling mysql-proxy manually

References

• Mysql-proxy home
  http://forge.mysql.com/wiki/MySQL_Proxy
  
• Compiling from the development source tree
  http://svn.mysql.com/svnpublic/mysql-proxy/trunk/INSTALL
  

Description
These problems won't occure if you install binary-release mysql-proxy(of course), and then any problem won't occure too if you install libevent and lua dependency libraries by using yum.
If we want to install mysql-proxy, libevent and lua libraries manually, we need to use additional configure and compile options. In our environment, we install manually as follows.

   /var/apps
  +-->mysql-5.0 (mysql database engine)
  +-->mysql-proxy-0.6.0 (mysql-proxy)
  +-->libevent-1.3e  (libevent-1.3e)
  +-->lua-5.1.2  (lua-5.1.2)

Problem1

• Description
  While executing mysql-proxy configure command, following error occures.
  

  checking for LUA... checking for LUA... configure: error: Package requirements (lua5.1 >= 5.1) were not met:
  No package 'lua5.1' found
  

• Solution
  System fails to find the lua's dependency files. We need to add following options when executing configure command.
  

  configure \
  LUA_CFLAGS="-I/var/apps/lua-5.1.2/include/" \
  LUA_LIBS="-L/var/apps/lua-5.1.2/lib/ -llua -ldl" \
  .....
  

Problem2

• Description
  While executing mysql-proxy configure command, following error occures.
  

  configure: error: libevent is required
  

• Solution
  This error occures when System fails to find the libevent library files. We need to add following options when executing configure command.
  

  configure \
  ....
  LDFLAGS="-L/var/apps/libevent-1.3e/lib/ -lm" \
  .....
  

Problem3

• Description
  While executing mysql-proxy make command, following error occures.
  

  /var/apps/lua-5.1.2/lib/liblua.a(lvm.o)(.text+0xae2): In function `Arith':
  lvm.c: undefined reference to `pow'
  /var/apps/lua-5.1.2/lib/liblua.a(lvm.o)(.text+0x2053): In function `luaV_execute':
  lvm.c: undefined reference to `pow'
  /var/apps/lua-5.1.2/lib/liblua.a(lcode.o)(.text+0xf99): In function `codearith':
  lcode.c: undefined reference to `pow'
  /var/apps/lua-5.1.2/lib/liblua.a(lmathlib.o)(.text+0x3e): In function `math_sin':
  lmathlib.c: undefined reference to `sin'
  /var/apps/lua-5.1.2/lib/liblua.a(lmathlib.o)(.text+0x6e): In function `math_sinh':
  lmathlib.c: undefined reference to `sinh'
  /var/apps/lua-5.1.2/lib/liblua.a(lmathlib.o)(.text+0x9e): In function `math_cos':
  lmathlib.c: undefined reference to `cos'
  .....
  

• Solution
  We need to add following options when executing configure command. In this case, we need to configure with LDFLAGS option and execute make command again.
  

  configure \
  ....
  LDFLAGS="-lm" \
  ....
  

Problem4

• Description
  While executing mysql-proxy make command, following error occures.
  

  mysql_proxy-network-mysqld-proxy.o(.text+0x7c): In function `proxy_lua_free_script':
  /var/apps/dist/mysql-proxy-0.6.0/src/network-mysqld-proxy.c:215: undefined reference to `lua_type'
  mysql_proxy-network-mysqld-proxy.o(.text+0x8f):/var/apps/dist/mysql-proxy-0.6.0/src/network-mysqld-proxy.c:216: undefined reference to `lua_settop'
  mysql_proxy-network-mysqld-proxy.o(.text+0x97):/var/apps/dist/mysql-proxy-0.6.0/src/network-mysqld-proxy.c:218: undefined reference to `lua_gettop'
  mysql_proxy-network-mysqld-proxy.o(.text+0xaf):/var/apps/dist/mysql-proxy-0.6.0/src/network-mysqld-proxy.c:220: undefined reference to `luaL_unref'
  mysql_proxy-network-mysqld-proxy.o(.text+0xbe):/var/apps/dist/mysql-proxy-0.6.0/src/network-mysqld-proxy.c:225: undefined reference to `lua_gc'
  mysql_proxy-network-mysqld-proxy.o(.text+0x18f): In function `plugin_srv_state_free':
  /var/apps/dist/mysql-proxy-0.6.0/src/network-mysqld-proxy.c:408: undefined reference to `lua_close'
  mysql_proxy-network-mysqld-proxy.o(.text+0x33d): In function `lua_load_script':
  /var/apps/dist/mysql-proxy-0.6.0/src/network-mysqld-proxy.c:651: undefined reference to `luaL_loadfile'
  mysql_proxy-network-mysqld-proxy.o(.text+0x36b):/var/apps/dist/mysql-proxy-0.6.0/src/network-mysqld-proxy.c:664: undefined reference to `lua_type'
  mysql_proxy-network-mysqld-proxy.o(.text+0x3ad): In function `proxy_pool_queue_get':
  /var/apps/dist/mysql-proxy-0.6.0/src/network-mysqld-proxy.c:675: undefined reference to `luaL_checkudata'
  

• Solution
  We need to add "-lua" option in LUA_LIBS when executing configure command. In this case, we need to configure with LUA option and execute make command again.
  

  ./configure \
  ....
  LUA_CFLAGS="-I/var/apps/lua-5.1.2/include/" \
  LUA_LIBS="-L/var/apps/lua-5.1.2/lib/ -llua -ldl" \
  ...
  

Problem5

• Description
  While executing mysql-proxy make command, following error occures.
  

  /usr/bin/ld: cannot find -llua
  collect2: ld returned 1 exit status
  make[2]: *** [mysql-proxy] Error 1
  make[2]: Leaving directory `/var/apps/dist/mysql-proxy-0.6.0/src'
  make[1]: *** [all-recursive] Error 1
  make[1]: Leaving directory `/var/apps/dist/mysql-proxy-0.6.0'
  make: *** [all] Error 2
  

• Solution
  We need to add both -L and -lua options in LUA_LIBS when executing configure command. In this case, we need to configure with LDFLAGS option and execute make command again.
  

  ./configure \
  ...
  LUA_LIBS="-L/var/apps/lua-5.1.2/lib/ -llua -ldl" \
  ...
  

Problem6

• Description
  While executing mysql-proxy configure command, following error occures.
  

  configure: error: mysql_config not exists or not executable, use $ ./configure --with-mysql=/path/to/mysql_config
  

• Solution
  System needs to know the location of the mysql database library files. we need to add --with-mysql option and the vlaue of directory path stored mysql library files.
  

  ./configure \
  ....
  --with-mysql=/var/apps/mysql-5.0 \
  ...
  

Totally, we need following options when executing configure command properly to compile mysql-proxy manually.

./configure \
LUA_CFLAGS="-I/var/apps/lua-5.1.2/include/" \
LUA_LIBS="-L/var/apps/lua-5.1.2/lib/ -llua -ldl" \
LDFLAGS="-L/var/apps/libevent-1.3e/lib/ -lm" \
--prefix=/var/apps/mysql-proxy-0.6.0 \
--with-mysql=/var/apps/mysql-5.0 \
--with-lua

[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