MaxScale is GA

MariaDB has announced that MaxScale is now GA.

“MaxScale is an open-source, database-centric proxy that works with MariaDB Enterprise, MariaDB 10 and Oracle MySQL®. Its pluggable architecture is designed to increase flexibility and aid in customization as well as a lightweight, high-speed networking core designed to facilitate throughput.

MaxScale simplifies the process of growth by isolating the application from scaling at the database level. This allows scaling databases while improving DBA productivity and developer agility. Additionally, MariaDB MaxScale provides continuity to applications during database failover, upgrades and faster rollout of new database features – all of which improves your end customer’s experience. Now, you can stay competitive and create new revenue generating services with MariaDB MaxScale’s flexible plugin architecture.”

(I quoted that from the email release, fwiw.)

Features include:

  • MariaDB and MySQL Client and Server protocols
  • MariaDB Galera and MySQL replication cluster monitoring
  • Connection and Statement based load balancing
  • Query Performance Logging
  • Query Statement Modification based on regular expressions
  • Query Duplication to another database, storage engine or application
  • and more

Download MaxScale:
https://mariadb.com/my_portal/download#maxscale

Product Page:
https://mariadb.com/products/mariadb-maxscale

External FAQ:
https://mariadb.com/products/product-faqs?qt-faqs=1#qt-faqs

Datasheet:
https://mariadb.com/sites/default/files/MariaDB_MaxScale_Data_Sheet.pdf

Upcoming MaxScale Webinar (January 15th):
http://info.mariadb.com/webcast/maxscale

Nice Blog on Read Write Splitting and MaxScale (by Vilho Raatikka):
https://mariadb.com/blog/maxscale-and-transparent-session-handling

 

Added a Table of Contents

Not a big deal, but I just added a “Table of Contents” page to my blog to make finding older articles much easier.

I noticed most of my posts are quite lengthy, and it can take a bit of searching/clicking to find an older entry. So unless you happen to recall the ‘month/year’ it was published, which I don’t even remember that, then hopefully this will help.

Really simple, and looks just like this:

My hopes are that this will aid in making some posts easier to find (such as ones about InnoDB Recovery, Recovery with an Individual .ibd, Proxy-related articles, Error-related articles, How-to posts, and so forth).

You can see the full “table of contents” here:

http://www.chriscalender.com/?page_id=399

Happy reading :)

 
 
 

Proxy Trace Revisited with MySQL Proxy 0.8.2

Back in January 2010, I wrote an article titled Proxy Trace : Tracing the Variables and Functions Available within MySQL Proxy.

That post was basically a lua script I posted which traces the steps of Proxy, outputting comments as it traverses through the various functions, and then outputs the names and values of [most of] the available variables available within Proxy.

That script worked fine with Proxy 0.7.2 and 0.8.0. It also works just fine with Proxy 0.8.1 (though you need to specify your admin settings in 0.8.1) and 0.8.2 (admin values not necessary).

However, I did go ahead and build it out a little further.

I did not add too much, but I did add the following (the full script, trace.lua, is provided at the end of this post):

  • Outputs backends.type variable
  • Expanded the rows returned outputs (returns more detailed row information)
  • Enhanced error reporting
  • Outputs 70 internal constants that Proxy tracks (Server Commands, Types, Backend State, Packet State, and Return State codes)

I still only start the proxy with the following command:

mysql-proxy --proxy-lua-script=trace.lua

And then in my client, I simply connect, issue 1 query, and exit:

shell> mysql -uroot -pmysql -P4040
mysql> select * from world.city where countrycode='usa' limit 3;
mysql> exit

And here is the new trace.lua output from this session:

C:\Program Files\MySQL\mysql-proxy-0.8.2\bin>mysql-proxy --proxy-lua-script=trace.lua
/* Entering function connect_server() */
   When the proxy accepts a connection from a MySQL client, the
   connect_server() function is called.  There are no arguments
   to the function, but you can use and if necessary manipulate
   the information in the proxy.connection table, which is unique
   to each client session.

   INTERNAL CONSTANTS:

   //Proxy Return State Constants:
   proxy.PROXY_SEND_QUERY                       = 1
   proxy.PROXY_SEND_RESULT                      = 2
   proxy.PROXY_IGNORE_RESULT                    = 4

   //Packet State Constants:
   proxy.MYSQLD_PACKET_OK                       = 0
   proxy.MYSQLD_PACKET_ERR                      = 255
   proxy.MYSQLD_PACKET_RAW                      = 250

   //Backend State/Type Constants:
   proxy.BACKEND_STATE_UNKNOWN                  = 0
   proxy.BACKEND_STATE_UP                       = 1
   proxy.BACKEND_STATE_DOWN                     = 2
   proxy.BACKEND_TYPE_UNKNOWN                   = 0
   proxy.BACKEND_TYPE_RW                        = 1
   proxy.BACKEND_TYPE_RO                        = 2

   //Server Command Constants:
   proxy.COM_SLEEP                              = 0
   proxy.COM_QUIT                               = 1
   proxy.COM_INIT_DB                            = 2
   proxy.COM_QUERY                              = 3
   proxy.COM_FIELD_LIST                         = 4
   proxy.COM_CREATE_DB                          = 5
   proxy.COM_DROP_DB                            = 6
   proxy.COM_REFRESH                            = 7
   proxy.COM_SHUTDOWN                           = 8
   proxy.COM_STATISTICS                         = 9
   proxy.COM_PROCESS_INFO                       = 10
   proxy.COM_CONNECT                            = 11
   proxy.COM_PROCESS_KILL                       = 12
   proxy.COM_DEBUG                              = 13
   proxy.COM_PING                               = 14
   proxy.COM_TIME                               = 15
   proxy.COM_DELAYED_INSERT                     = 16
   proxy.COM_CHANGE_USER                        = 17
   proxy.COM_BINLOG_DUMP                        = 18
   proxy.COM_TABLE_DUMP                         = 19
   proxy.COM_CONNECT_OUT                        = 20
   proxy.COM_REGISTER_SLAVE                     = 21
   proxy.COM_STMT_PREPARE                       = 22
   proxy.COM_STMT_EXECUTE                       = 23
   proxy.COM_STMT_SEND_LONG_DATA                = 24
   proxy.COM_STMT_CLOSE                         = 25
   proxy.COM_STMT_RESET                         = 26
   proxy.COM_SET_OPTION                         = 27
   proxy.COM_STMT_FETCH                         = 28
   proxy.COM_DAEMON                             = 29

   //MySQL Type Constants:
   proxy.MYSQL_TYPE_DECIMAL                     = 0
   proxy.MYSQL_TYPE_NEWDECIMAL                  = 246
   proxy.MYSQL_TYPE_TINY                        = 1
   proxy.MYSQL_TYPE_SHORT                       = 2
   proxy.MYSQL_TYPE_LONG                        = 3
   proxy.MYSQL_TYPE_FLOAT                       = 4
   proxy.MYSQL_TYPE_DOUBLE                      = 5
   proxy.MYSQL_TYPE_NULL                        = 6
   proxy.MYSQL_TYPE_TIMESTAMP                   = 7
   proxy.MYSQL_TYPE_LONGLONG                    = 8
   proxy.MYSQL_TYPE_INT24                       = 9
   proxy.MYSQL_TYPE_DATE                        = 10
   proxy.MYSQL_TYPE_TIME                        = 11
   proxy.MYSQL_TYPE_DATETIME                    = 12
   proxy.MYSQL_TYPE_YEAR                        = 13
   proxy.MYSQL_TYPE_NEWDATE                     = 14
   proxy.MYSQL_TYPE_ENUM                        = 247
   proxy.MYSQL_TYPE_SET                         = 248
   proxy.MYSQL_TYPE_TINY_BLOB                   = 249
   proxy.MYSQL_TYPE_MEDIUM_BLOB                 = 250
   proxy.MYSQL_TYPE_LONG_BLOB                   = 251
   proxy.MYSQL_TYPE_BLOB                        = 252
   proxy.MYSQL_TYPE_VAR_STRING                  = 253
   proxy.MYSQL_TYPE_STRING                      = 254
   proxy.MYSQL_TYPE_TINY                        = 1
   proxy.MYSQL_TYPE_ENUM                        = 247
   proxy.MYSQL_TYPE_GEOMETRY                    = 255
   proxy.MYSQL_TYPE_BIT                         = 16

/* Entering function read_handshake() */
   Handshake information is sent by the server to the client
   after the initial connection (through connect_server()) has
   been made.  read_handshake() doesn't receive a parameter
   anymore. Instead all the data is available in the connection
   tables.

/* Entering function read_auth() */
   The read_auth() function is triggered when an authentication
   handshake is initiated by the client.  read_auth() doesn't
   receive a parameter anymore. Instead all the data is
   available in the connection tables.

/* Entering function read_auth_result() */
   The return packet from the server during authentication is
   captured by read_auth_result().

/* Entering function read_query( packet ) */
| o = [backends] userdata: 019136F8 (type=userdata)
[config] table: 01CAE2C0 (type=table)

| Query               = select @@version_comment limit 1
| Time Stamp          = 2011-11-01 19:09:22
| Proxy Version       = 00802
| Lua Version         = Lua 5.1

/* Entering function read_query_result( inj ) */
| os.date                                      = 2011-11-01 19:09:22
| inj.query                                    = ♥select @@version_comment limit 1
| proxy.connection.server.scramble_buffer      = "^PVA!!\"$0d(8#Cg]zrK^"
| proxy.connection.server.mysqld_version       = 50091
| proxy.connection.server.thread_id            = 8
| proxy.connection.client.default_db           =
| proxy.connection.client.username             = root
| proxy.connection.client.scrambled_password   = "╣\\ª≡½bj¼c¬_╫i♫>5úN╡S"
| proxy.connection.client.dst.name             = 127.0.0.1:4040
| proxy.connection.client.dst.address          = 127.0.0.1
| proxy.connection.client.dst.port             = 4040
| proxy.connection.server.dst.name             = 127.0.0.1:3306
| proxy.connection.server.dst.address          = 127.0.0.1
| proxy.connection.server.dst.port             = 3306
| proxy.connection.client.src.name             = 127.0.0.1:50404
| proxy.connection.client.src.address          = 127.0.0.1
| proxy.connection.client.src.port             = 50404
| proxy.connection.server.src.name             = 127.0.0.1:50405
| proxy.connection.server.src.address          = 127.0.0.1
| proxy.connection.server.src.port             = 50405
| inj.query-time                               = 8.366ms
| inj.response-time                            = 8.381ms
| proxy.connection.backend_ndx                 = 1
| #proxy.global.backends                       = 1
| proxy.global.backends[ndx].dst.name          = 127.0.0.1:3306
| proxy.global.backends[ndx].dst.address       = 127.0.0.1
| proxy.global.backends[ndx].dst.port          = 3306
| proxy.global.backends[ndx].connected_clients = 1
| proxy.global.backends[ndx].state             = 1
| proxy.global.backends[ndx].type              = 1
| proxy.global.backends[ndx].pool.max_idle_connections = 0
| proxy.global.backends[ndx].pool.min_idle_connections = 0
| proxy.global.backends[ndx].pool.users[1]     = 0
| result.len                                   = 1
| result.flags.in_trans                        = false
| result.flags.auto_commit                     = true
| result.flags.no_good_index_used              = false
| result.flags.no_index_used                   = false
| result.flags.cursor_exists                   = nil
| result.flags.last_row_sent                   = nil
| result.flags.db_dropped                      = nil
| result.flags.no_backslash_escapes            = nil
| result.flags.metadata_changed                = nil
| result.warning_count                         = 0
| result.query_status                          = 0
| result.fields[1].type                        = 253
| result.fields[1].name                        = @@version_comment
1       MySQL Enterprise Server - Pro Edition (GPL)
| row_count                                    = 1


/* Entering function read_query( packet ) */
| o = [backends] userdata: 019136F8 (type=userdata)
[config] table: 01CAE2C0 (type=table)

| Query               = select * from world.city where countrycode='usa' limit 3
| Time Stamp          = 2011-11-01 19:09:24
| Proxy Version       = 00802
| Lua Version         = Lua 5.1

/* Entering function read_query_result( inj ) */
| os.date                                      = 2011-11-01 19:09:24
| inj.query                                    = ♥select * from world.city where countrycode='usa' limit 3
| proxy.connection.server.scramble_buffer      = "^PVA!!\"$0d(8#Cg]zrK^"
| proxy.connection.server.mysqld_version       = 50091
| proxy.connection.server.thread_id            = 8
| proxy.connection.client.default_db           =
| proxy.connection.client.username             = root
| proxy.connection.client.scrambled_password   = "╣\\ª≡½bj¼c¬_╫i♫>5úN╡S"
| proxy.connection.client.dst.name             = 127.0.0.1:4040
| proxy.connection.client.dst.address          = 127.0.0.1
| proxy.connection.client.dst.port             = 4040
| proxy.connection.server.dst.name             = 127.0.0.1:3306
| proxy.connection.server.dst.address          = 127.0.0.1
| proxy.connection.server.dst.port             = 3306
| proxy.connection.client.src.name             = 127.0.0.1:50404
| proxy.connection.client.src.address          = 127.0.0.1
| proxy.connection.client.src.port             = 50404
| proxy.connection.server.src.name             = 127.0.0.1:50405
| proxy.connection.server.src.address          = 127.0.0.1
| proxy.connection.server.src.port             = 50405
| inj.query-time                               = 8.706ms
| inj.response-time                            = 8.729ms
| proxy.connection.backend_ndx                 = 1
| #proxy.global.backends                       = 1
| proxy.global.backends[ndx].dst.name          = 127.0.0.1:3306
| proxy.global.backends[ndx].dst.address       = 127.0.0.1
| proxy.global.backends[ndx].dst.port          = 3306
| proxy.global.backends[ndx].connected_clients = 1
| proxy.global.backends[ndx].state             = 1
| proxy.global.backends[ndx].type              = 1
| proxy.global.backends[ndx].pool.max_idle_connections = 0
| proxy.global.backends[ndx].pool.min_idle_connections = 0
| proxy.global.backends[ndx].pool.users[1]     = 0
| result.len                                   = 1
| result.flags.in_trans                        = false
| result.flags.auto_commit                     = true
| result.flags.no_good_index_used              = false
| result.flags.no_index_used                   = true
| result.flags.cursor_exists                   = nil
| result.flags.last_row_sent                   = nil
| result.flags.db_dropped                      = nil
| result.flags.no_backslash_escapes            = nil
| result.flags.metadata_changed                = nil
| result.warning_count                         = 0
| result.query_status                          = 0
| result.fields[1].type                        = 3
| result.fields[1].name                        = ID
1       3793
2       New York
3       USA
4       New York
5       8008278
1       3794
2       Los Angeles
3       USA
4       California
5       3694820
1       3795
2       Chicago
3       USA
4       Illinois
5       2896016
| row_count                                    = 3


/* Entering function read_query( packet ) */
| o = [backends] userdata: 019136F8 (type=userdata)
[config] table: 01CAE2C0 (type=table)

/* Entering function disconnect_client() */
Client 8 has disconnected.

Here is the client session for completeness (and perhaps some one may find it useful):

C:\Users\Chris>mysql -uroot -pmysql -P4040
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.0.91-enterprise-gpl-nt MySQL Enterprise Server - Pro Edition (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select * from world.city where countrycode='usa' limit 3;
+------+-------------+-------------+------------+------------+
| ID   | Name        | CountryCode | District   | Population |
+------+-------------+-------------+------------+------------+
| 3793 | New York    | USA         | New York   |    8008278 |
| 3794 | Los Angeles | USA         | California |    3694820 |
| 3795 | Chicago     | USA         | Illinois   |    2896016 |
+------+-------------+-------------+------------+------------+
3 rows in set (0.15 sec)

mysql> exit
Bye

C:\Users\Chris>

And here is the full script, trace.lua:

function connect_server()
	print("/* Entering function connect_server() */")
	print("   When the proxy accepts a connection from a MySQL client, the") 
	print("   connect_server() function is called.  There are no arguments")
	print("   to the function, but you can use and if necessary manipulate")
	print("   the information in the proxy.connection table, which is unique")
	print("   to each client session.\n")
	print("   INTERNAL CONSTANTS:\n")
	print("   //Proxy Return State Constants:")
	print("   proxy.PROXY_SEND_QUERY                       = " 
				.. proxy.PROXY_SEND_QUERY)
	print("   proxy.PROXY_SEND_RESULT                      = " 
				.. proxy.PROXY_SEND_RESULT)
	print("   proxy.PROXY_IGNORE_RESULT                    = " 
				.. proxy.PROXY_IGNORE_RESULT .. "\n")
	print("   //Packet State Constants:")
	print("   proxy.MYSQLD_PACKET_OK                       = " 
				.. proxy.MYSQLD_PACKET_OK)
	print("   proxy.MYSQLD_PACKET_ERR                      = " 
				.. proxy.MYSQLD_PACKET_ERR)
	print("   proxy.MYSQLD_PACKET_RAW                      = " 
				.. proxy.MYSQLD_PACKET_RAW .. "\n")
	print("   //Backend State/Type Constants:")
	print("   proxy.BACKEND_STATE_UNKNOWN                  = " 
				.. proxy.BACKEND_STATE_UNKNOWN)
	print("   proxy.BACKEND_STATE_UP                       = " 
				.. proxy.BACKEND_STATE_UP)
	print("   proxy.BACKEND_STATE_DOWN                     = " 
				.. proxy.BACKEND_STATE_DOWN)	
	print("   proxy.BACKEND_TYPE_UNKNOWN                   = " 
				.. proxy.BACKEND_TYPE_UNKNOWN)
	print("   proxy.BACKEND_TYPE_RW                        = " 
				.. proxy.BACKEND_TYPE_RW)
	print("   proxy.BACKEND_TYPE_RO                        = " 
				.. proxy.BACKEND_TYPE_RO .. "\n")
	print("   //Server Command Constants:")
	print("   proxy.COM_SLEEP                              = " 
				.. proxy.COM_SLEEP)
	print("   proxy.COM_QUIT                               = " 
				.. proxy.COM_QUIT)
	print("   proxy.COM_INIT_DB                            = " 
				.. proxy.COM_INIT_DB)
	print("   proxy.COM_QUERY                              = " 
				.. proxy.COM_QUERY)
	print("   proxy.COM_FIELD_LIST                         = " 
				.. proxy.COM_FIELD_LIST)
	print("   proxy.COM_CREATE_DB                          = " 
				.. proxy.COM_CREATE_DB)
	print("   proxy.COM_DROP_DB                            = " 
				.. proxy.COM_DROP_DB)
	print("   proxy.COM_REFRESH                            = " 
				.. proxy.COM_REFRESH)
	print("   proxy.COM_SHUTDOWN                           = " 
				.. proxy.COM_SHUTDOWN)
	print("   proxy.COM_STATISTICS                         = " 
				.. proxy.COM_STATISTICS)
	print("   proxy.COM_PROCESS_INFO                       = " 
				.. proxy.COM_PROCESS_INFO)
	print("   proxy.COM_CONNECT                            = " 
				.. proxy.COM_CONNECT)
	print("   proxy.COM_PROCESS_KILL                       = " 
				.. proxy.COM_PROCESS_KILL)
	print("   proxy.COM_DEBUG                              = " 
				.. proxy.COM_DEBUG)
	print("   proxy.COM_PING                               = " 
				.. proxy.COM_PING)
	print("   proxy.COM_TIME                               = " 
				.. proxy.COM_TIME)
	print("   proxy.COM_DELAYED_INSERT                     = " 
				.. proxy.COM_DELAYED_INSERT)
	print("   proxy.COM_CHANGE_USER                        = " 
				.. proxy.COM_CHANGE_USER)
	print("   proxy.COM_BINLOG_DUMP                        = " 
				.. proxy.COM_BINLOG_DUMP)
	print("   proxy.COM_TABLE_DUMP                         = " 
				.. proxy.COM_TABLE_DUMP)
	print("   proxy.COM_CONNECT_OUT                        = " 
				.. proxy.COM_CONNECT_OUT)
	print("   proxy.COM_REGISTER_SLAVE                     = " 
				.. proxy.COM_REGISTER_SLAVE)
	print("   proxy.COM_STMT_PREPARE                       = " 
				.. proxy.COM_STMT_PREPARE)
	print("   proxy.COM_STMT_EXECUTE                       = " 
				.. proxy.COM_STMT_EXECUTE)
	print("   proxy.COM_STMT_SEND_LONG_DATA                = " 
				.. proxy.COM_STMT_SEND_LONG_DATA)
	print("   proxy.COM_STMT_CLOSE                         = " 
				.. proxy.COM_STMT_CLOSE)
	print("   proxy.COM_STMT_RESET                         = " 
				.. proxy.COM_STMT_RESET)
	print("   proxy.COM_SET_OPTION                         = " 
				.. proxy.COM_SET_OPTION)
	print("   proxy.COM_STMT_FETCH                         = " 
				.. proxy.COM_STMT_FETCH)
	print("   proxy.COM_DAEMON                             = " 
				.. proxy.COM_DAEMON .. "\n")
	print("   //MySQL Type Constants:")
	print("   proxy.MYSQL_TYPE_DECIMAL                     = " 
				.. proxy.MYSQL_TYPE_DECIMAL)
	print("   proxy.MYSQL_TYPE_NEWDECIMAL                  = " 
				.. proxy.MYSQL_TYPE_NEWDECIMAL)
	print("   proxy.MYSQL_TYPE_TINY                        = " 
				.. proxy.MYSQL_TYPE_TINY)
	print("   proxy.MYSQL_TYPE_SHORT                       = " 
				.. proxy.MYSQL_TYPE_SHORT)
	print("   proxy.MYSQL_TYPE_LONG                        = " 
				.. proxy.MYSQL_TYPE_LONG)
	print("   proxy.MYSQL_TYPE_FLOAT                       = " 
				.. proxy.MYSQL_TYPE_FLOAT)
	print("   proxy.MYSQL_TYPE_DOUBLE                      = " 
				.. proxy.MYSQL_TYPE_DOUBLE)
	print("   proxy.MYSQL_TYPE_NULL                        = " 
				.. proxy.MYSQL_TYPE_NULL)
	print("   proxy.MYSQL_TYPE_TIMESTAMP                   = " 
				.. proxy.MYSQL_TYPE_TIMESTAMP)
	print("   proxy.MYSQL_TYPE_LONGLONG                    = " 
				.. proxy.MYSQL_TYPE_LONGLONG)
	print("   proxy.MYSQL_TYPE_INT24                       = " 
				.. proxy.MYSQL_TYPE_INT24)
	print("   proxy.MYSQL_TYPE_DATE                        = " 
				.. proxy.MYSQL_TYPE_DATE)
	print("   proxy.MYSQL_TYPE_TIME                        = " 
				.. proxy.MYSQL_TYPE_TIME)
	print("   proxy.MYSQL_TYPE_DATETIME                    = " 
				.. proxy.MYSQL_TYPE_DATETIME)
	print("   proxy.MYSQL_TYPE_YEAR                        = " 
				.. proxy.MYSQL_TYPE_YEAR)
	print("   proxy.MYSQL_TYPE_NEWDATE                     = " 
				.. proxy.MYSQL_TYPE_NEWDATE)
	print("   proxy.MYSQL_TYPE_ENUM                        = " 
				.. proxy.MYSQL_TYPE_ENUM)
	print("   proxy.MYSQL_TYPE_SET                         = " 
				.. proxy.MYSQL_TYPE_SET)
	print("   proxy.MYSQL_TYPE_TINY_BLOB                   = " 
				.. proxy.MYSQL_TYPE_TINY_BLOB)
	print("   proxy.MYSQL_TYPE_MEDIUM_BLOB                 = " 
				.. proxy.MYSQL_TYPE_MEDIUM_BLOB)
	print("   proxy.MYSQL_TYPE_LONG_BLOB                   = " 
				.. proxy.MYSQL_TYPE_LONG_BLOB)
	print("   proxy.MYSQL_TYPE_BLOB                        = " 
				.. proxy.MYSQL_TYPE_BLOB)
	print("   proxy.MYSQL_TYPE_VAR_STRING                  = " 
				.. proxy.MYSQL_TYPE_VAR_STRING)
	print("   proxy.MYSQL_TYPE_STRING                      = " 
				.. proxy.MYSQL_TYPE_STRING)
	print("   proxy.MYSQL_TYPE_TINY                        = " 
				.. proxy.MYSQL_TYPE_TINY)
	print("   proxy.MYSQL_TYPE_ENUM                        = " 
				.. proxy.MYSQL_TYPE_ENUM)
	print("   proxy.MYSQL_TYPE_GEOMETRY                    = " 
				.. proxy.MYSQL_TYPE_GEOMETRY)
	print("   proxy.MYSQL_TYPE_BIT                         = " 
				.. proxy.MYSQL_TYPE_BIT .. "\n")
end

function read_handshake()
	print("/* Entering function read_handshake() */")
	print("   Handshake information is sent by the server to the client")
	print("   after the initial connection (through connect_server()) has")
	print("   been made.  read_handshake() doesn't receive a parameter")
	print("   anymore. Instead all the data is available in the connection")
	print("   tables.\n")
end

function read_auth()
	print("/* Entering function read_auth() */")
	print("   The read_auth() function is triggered when an authentication")
	print("   handshake is initiated by the client.  read_auth() doesn't")
	print("   receive a parameter anymore. Instead all the data is")
	print("   available in the connection tables.\n")
end

function read_auth_result()
	print("/* Entering function read_auth_result() */")
	print("   The return packet from the server during authentication is")
	print("   captured by read_auth_result().\n")
end

function read_query( packet )
	print("/* Entering function read_query( packet ) */")
	o = ""     
	for k, v in pairs(proxy.global) do       
		o = o .. (("[%s] %s (type=%s)\n"):format(k, tostring(v), type(v)))
	end
	print("| o = " .. o)
	if string.byte(packet) == proxy.COM_QUERY then
		proxy.queries:append(1, packet, {resultset_is_needed = true} )
        print("| Query               = " 
			.. string.sub(packet, 2))
		print("| Time Stamp          = " 
			.. os.date('%Y-%m-%d %H:%M:%S'))
		print("| Proxy Version       = " 
			.. string.format("%05x", proxy.PROXY_VERSION))
		print("| Lua Version         = " 
			.. _VERSION)
		return proxy.PROXY_SEND_QUERY
	end
end

function read_query_result( inj )
	print("\n/* Entering function read_query_result( inj ) */")
	local res         = assert(inj.resultset)
	local raw_len     = assert(res.raw):len()
	local packet      = assert(inj.query)
	local flags       = res.flags
	local backend_ndx = proxy.connection.backend_ndx
	print("| os.date                                      = " 
		.. os.date('%Y-%m-%d %H:%M:%S'))
	print("| inj.query                                    = " 
		.. inj.query)
	print("| proxy.connection.server.scramble_buffer      = " 
		.. string.format("%q",proxy.connection.server.scramble_buffer)) 
	print("| proxy.connection.server.mysqld_version       = " 
		.. proxy.connection.server.mysqld_version)
	print("| proxy.connection.server.thread_id            = " 
		.. proxy.connection.server.thread_id)
	print("| proxy.connection.client.default_db           = " 
		.. proxy.connection.client.default_db)
	print("| proxy.connection.client.username             = " 
		.. proxy.connection.client.username)
	print("| proxy.connection.client.scrambled_password   = " 
		.. string.format("%q", proxy.connection.client.scrambled_password))
	print("| proxy.connection.client.dst.name             = " 
		.. proxy.connection.client.dst.name)
	print("| proxy.connection.client.dst.address          = " 
		.. proxy.connection.client.dst.address)
	print("| proxy.connection.client.dst.port             = " 
		.. proxy.connection.client.dst.port)
	print("| proxy.connection.server.dst.name             = " 
		.. proxy.connection.server.dst.name)
	print("| proxy.connection.server.dst.address          = " 
		.. proxy.connection.server.dst.address)
	print("| proxy.connection.server.dst.port             = " 
		.. proxy.connection.server.dst.port)
	print("| proxy.connection.client.src.name             = " 
		.. proxy.connection.client.src.name)
	print("| proxy.connection.client.src.address          = " 
		.. proxy.connection.client.src.address)
	print("| proxy.connection.client.src.port             = " 
		.. proxy.connection.client.src.port)
	print("| proxy.connection.server.src.name             = " 
		.. proxy.connection.server.src.name)
	print("| proxy.connection.server.src.address          = " 
		.. proxy.connection.server.src.address)
	print("| proxy.connection.server.src.port             = " 
		.. proxy.connection.server.src.port)
	print("| inj.query-time                               = " 
		.. (inj.query_time / 1000) .. "ms")
	print("| inj.response-time                            = " 
		.. (inj.response_time / 1000) .. "ms")
	print("| proxy.connection.backend_ndx                 = " 
		.. proxy.connection.backend_ndx)
	print("| #proxy.global.backends                       = " 
		.. #proxy.global.backends)
	print("| proxy.global.backends[ndx].dst.name          = " 
		.. proxy.global.backends[backend_ndx].dst.name)
	print("| proxy.global.backends[ndx].dst.address       = " 
		.. proxy.global.backends[backend_ndx].dst.address)
	print("| proxy.global.backends[ndx].dst.port          = " 
		.. proxy.global.backends[backend_ndx].dst.port)
	print("| proxy.global.backends[ndx].connected_clients = " 
		.. proxy.global.backends[backend_ndx].connected_clients)
	print("| proxy.global.backends[ndx].state             = " 
		.. proxy.global.backends[backend_ndx].state)
	print("| proxy.global.backends[ndx].type              = " 
		.. proxy.global.backends[backend_ndx].type)	
	print("| proxy.global.backends[ndx].pool.max_idle_connections = " 
		.. proxy.global.backends[backend_ndx].pool.max_idle_connections)
	print("| proxy.global.backends[ndx].pool.min_idle_connections = " 
		.. proxy.global.backends[backend_ndx].pool.min_idle_connections)
	print("| proxy.global.backends[ndx].pool.users[1]     = " 
		.. proxy.global.backends[backend_ndx].pool.users[1].cur_idle_connections)
	print("| result.len                                   = " 
		.. raw_len)
	print("| result.flags.in_trans                        = " 
		.. tostring(res.flags.in_trans))
	print("| result.flags.auto_commit                     = " 
		.. tostring(res.flags.auto_commit))
	print("| result.flags.no_good_index_used              = " 
		.. tostring(res.flags.no_good_index_used))
	print("| result.flags.no_index_used                   = " 
		.. tostring(res.flags.no_index_used))	
	print("| result.flags.cursor_exists                   = " 
		.. tostring(res.flags.cursor_exists))
	print("| result.flags.last_row_sent                   = " 
		.. tostring(res.flags.last_row_sent))
	print("| result.flags.db_dropped                      = " 
		.. tostring(res.flags.db_dropped))
	print("| result.flags.no_backslash_escapes            = " 
		.. tostring(res.flags.no_backslash_escapes))
	print("| result.flags.metadata_changed                = " 
		.. tostring(res.flags.metadata_changed))
	print("| result.warning_count                         = " 
		.. res.warning_count)
	print("| result.query_status                          = " 
		.. res.query_status)
	if res.affected_rows then
		print("| result.affected_rows                         = " 
			.. res.affected_rows)
		print("| result.insert_id                             = " 
			.. res.insert_id)
	end
	if res.query_status == proxy.MYSQLD_PACKET_ERR then
		print("| result.err.code                              = " 
			.. res.raw:byte(2) + (res.raw:byte(3) * 256))
		print("| result.err.sql_state                         = " 
			.. string.format("%q", res.raw:sub(5, 9)))
		print("| result.err.msg                               = " 
			.. string.format("%q", res.raw:sub(10)))
	end
	if res.query_status == proxy.MYSQLD_PACKET_OK then
		print("| result.fields[1].type                        = " 
			.. res.fields[1].type)
		print("| result.fields[1].name                        = " 
			.. res.fields[1].name)
		local row_count = 0
		for row in inj.resultset.rows do
			for i,v in ipairs(row) do print(i,v) end
			row_count = row_count + 1
		end
		print("| row_count                                    = "
			.. row_count)
	end
	print("\n")
end

function disconnect_client()
	print("/* Entering function disconnect_client() */")
	if proxy.connection.server.thread_id then
		print("Client " .. proxy.connection.server.thread_id 
			.. " has disconnected.")
	end
end

I hope this helps, and happy proxy tracing. ;)

Using MySQL Proxy 0.8.2 on Windows

If you try to start proxy 0.8.2 in Windows, and you receive this error:

The application has failed to start because its side-by-side configuration is incorrect.

Then you need to install the Microsoft Visual C++ runtime libraries (per the 0.8.2 changelogs). Here is the snippet from the changelogs (it just doesn’t mention the error):

The Microsoft Visual C++ runtime libraries are now a requirement for running MySQL Proxy. Users that do not have these libraries must download and install the Microsoft Visual C++ 2008 Service Pack 1 Redistributable Package MFC Security Update. For the current Proxy version, use the following link to obtain the package:

http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=26368

(Bug #12836100)

http://dev.mysql.com/doc/refman/5.5/en/mysql-proxy-news-0-8-2.html

And here is where you can download the Visual C++ runtime libraries:

http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=26368

Download the proper file for your OS: 32-bit, 64-bit, or IA-64 and install it. Installation is simple, quick, and no restart is required.

After that, restart proxy. The error should not arise and the proxy should start up normally.

However, should you still experience this error even after installing the Visual C++ runtime libs, then perhaps add some info to this bug:

http://bugs.mysql.com/bug.php?id=62223

Hope this helps anyone out there searching google for this specific error.

Syntax for Creating a Windows Service for MySQL (when there are 2 paths which both contain spaces)

Numerous times now, I’ve seen people have troubles creating MySQL services on Windows manually (using ‘sc’), whether it be for mysqld itself, MySQL Proxy, or the MySQL Enterprise Monitor and/or Agent.

The proper syntax for ‘sc’ can get tricky when you have spaces in pathnames, which is very common in Windows, and the need for –defaults-file (which means two paths each potentially containing spaces).

So, if you have spaces in both your binpath and your path to –defaults-file, then the following syntax will work for you (all on a single line):

sc create MySQLEnterpriseMonitorAgent 
binpath= ""C:Program FilesMySQLEnterpriseAgentbinmysql-monitor-agent.exe" 
--defaults-file="C:Program FilesMySQLEnterpriseAgentetcmysql-monitor-agent.ini"" 
DisplayName= "MySQL Enterprise Monitor Agent" start= "auto"

Note that you could easily use the exact same syntax to install a Windows service for the MySQL server itself (using mysqld.exe and my.ini instead, along with their correct paths). Similarly, this could be also used to create a Windows service for MySQL Proxy too. Just point binpath to mysql-proxy-svc.exe, and instead of –defaults-file=, use –proxy-lua-script= (in fact, I actually had a post on this close to a year ago, but my example only had one path that contained a space, so the above syntax was not necessary).

At any rate, the above command should output somthing similar to the following:

[SC] CreateService SUCCESS

For reference, here is my terminal session:

C:UsersChris>sc create MySQLEnterpriseMonitorAgent 
binpath= ""C:Program FilesMySQLEnterpriseAgentbinmysql-monitor-agent.exe" 
--defaults-file="C:Program FilesMySQLEnterpriseAgentetcmysql-monitor-agent.ini"" 
DisplayName= "MySQL Enterprise Monitor Agent" start= "auto"
[SC] CreateService SUCCESS

Hope this helps.

Getting LuaSocket to Work with MySQL Proxy

I’ve seen some interest in trying to get LuaSocket working with MySQL Proxy, and most of those interested have run into issues getting this set up, so I want to provide a brief walk-through on how to get this set-up and working properly.

Note I tested this on Windows, so use .so instead of .dll on *nix systems.

1. Download LuaSocket (contains lua/ and lib/ directories)

2. Copy contents of ‘lua/’ into the following directory:

C:Program FilesMySQLmysql-proxy-0.8.0libmysql-proxylua

3. Copy contents of ‘lib/’ into the following directory:

C:Program FilesMySQLmysql-proxy-0.8.0bin

4. In step #3, you should have copied a ‘socket/’ and ‘mime/’ directories into bin/. Rename the directory named “socket” to “lua-socket”. This will get you past one set of errors.

5. Next, in the C:Program FilesMySQLmysql-proxy-0.8.0bin, there is a file named lua51.dll. If you try to run it now, you’ll get an error saying “lua5.1.dll was not found”. So make a *copy* of “lua51.dll” and rename it “lua5.1.dll” (in the same directory).

6. Invoke your proxy script with the correct –lua-cpath and –lua-path options (below is correct relative to my basedir and with regards to where I copied the contents of the LuaSocket):

C:Program FilesMySQLmysql-proxy-0.8.0bin>mysql-proxy.exe --proxy-lua-script=show-vars.lua --lua-cpath=!\lua-?.dll --lua-path=!\..\lib\mysql-proxy\lua\?.lua;!\..\lib\mysql-proxy\lua\socket\?.lua

Proxy should start up fine with the script, it works, and you can access the socket functions within your own lua script.

For instance, my proxy basedir, which is also where my script is located, is:

C:Program FilesMySQLmysql-proxy-0.8.0

show-vars.lua is the name of my lua script.

In it, I have the following 2 lines:

socket = require("socket") 
print(socket._VERSION)

And when I start up the script, and connect to my instance, it outputs the following:

LuaSocket 2.0.1

Proxy Trace : Tracing the Variables and Functions Available within MySQL Proxy

Have you ever wanted a handy reference of all of the various variables available in MySQL Proxy?

I’ve written a Lua script that outputs many of the available variables, which is really useful for coding Lua scripts for MySQL Proxy. In addition to the variables, it also generates output during each main function it enters in the Proxy, so you can follow the execution trace, and then see the value of each variable when it is within a particular function.

I’ve tested this on MySQL Proxy 0.7.2 and the newly available Proxy 0.8.0.

Here is a sample of the output, where I just connect, and then issue a simple query (SELECT * FROM world.city LIMIT 1):

C:Program FilesMySQLmysql-proxy-0.8.0bin>mysql-proxy.exe
 --proxy-lua-script=show-vars.lua

/* Entering function connect_server() */
   When the proxy accepts a connection from a MySQL client, the
   connect_server() function is called.  There are no arguments
   to the function, but you can use and if necessary manipulate
   the information in the proxy.connection table, which is unique
   to each client session.

/* Entering function read_handshake() */
   Handshake information is sent by the server to the client
   after the initial connection (through connect_server()) has
   been made.  read_handshake() doesn't receive a parameter
   anymore. Instead all the data is available in the connection
   tables.

/* Entering function read_auth() */
   The read_auth() function is triggered when an authentication
   handshake is initiated by the client.  read_auth() doesn't
   receive a parameter anymore. Instead all the data is
   available in the connection tables.

/* Entering function read_auth_result() */
   The return packet from the server during authentication is
   captured by read_auth_result().
...

/* Entering function read_query( packet ) */
| o = [backends] userdata: 01AD9298 (type=userdata)
[config] table: 01AD5890 (type=table)

| Query               = select * from world.city limit 1
| Time Stamp          = 2010-01-27 16:14:05
| Proxy Version       = 00800
| Lua Version         = Lua 5.1

/* Entering function read_query_result( inj ) */
| os.date                                      = 2010-01-27 16:14:05
| inj.query                                    = ?select * from world.city limit 1
| proxy.connection.server.scramble_buffer      = "]CvKTD8,r|9B#&3oU>tO"
| proxy.connection.server.mysqld_version       = 50089
| proxy.connection.server.thread_id            = 21
| proxy.connection.client.default_db           = world
| proxy.connection.client.username             = root
| proxy.connection.client.scrambled_password   = "+?í!ò?g~?00h[BNòr?+??"
| proxy.connection.client.dst.name             = 127.0.0.1:4040
| proxy.connection.client.dst.address          = 127.0.0.1
| proxy.connection.client.dst.port             = 4040
| proxy.connection.server.dst.name             = 127.0.0.1:3306
| proxy.connection.server.dst.address          = 127.0.0.1
| proxy.connection.server.dst.port             = 3306
| proxy.connection.client.src.name             = 127.0.0.1:58468
| proxy.connection.client.src.address          = 127.0.0.1
| proxy.connection.client.src.port             = 58468
| proxy.connection.server.src.name             = 127.0.0.1:58469
| proxy.connection.server.src.address          = 127.0.0.1
| proxy.connection.server.src.port             = 58469
| inj.query-time                               = 4.6ms
| inj.response-time                            = 4.613ms
| proxy.connection.backend_ndx                 = 1
| #proxy.global.backends                       = 1
| proxy.global.backends[ndx].dst.name          = 127.0.0.1:3306
| proxy.global.backends[ndx].dst.address       = 127.0.0.1
| proxy.global.backends[ndx].dst.port          = 3306
| proxy.global.backends[ndx].connected_clients = 1
| proxy.global.backends[ndx].state             = 1
| proxy.global.backends[ndx].pool.max_idle_connections = 0
| proxy.global.backends[ndx].pool.min_idle_connections = 0
| proxy.global.backends[ndx].pool.users[1]     = 0
| result.len                                   = 1
| result.flags.in_trans                        = false
| result.flags.auto_commit                     = true
| result.flags.no_good_index_used              = false
| result.flags.no_index_used                   = true
| result.warning_count                         = 0
| result.query_status                          = 0
| result.fields[1].type                        = 3
| result.fields[1].name                        = ID
| query returned                               = 1


/* Entering function read_query( packet ) */
| o = [backends] userdata: 01AD9298 (type=userdata)
[config] table: 01AD5890 (type=table)

/* Entering function disconnect_client() */
Client 21 has disconnected.

C:Program FilesMySQLmysql-proxy-0.8.0bin>

And, here is the full script (named “show-vars.lua” in the above invocation):

function connect_server()
	print("/* Entering function connect_server() */")
	print("   When the proxy accepts a connection from a MySQL client, the") 
	print("   connect_server() function is called.  There are no arguments")
	print("   to the function, but you can use and if necessary manipulate")
	print("   the information in the proxy.connection table, which is unique")
	print("   to each client session.n")
end

function read_handshake()
	print("/* Entering function read_handshake() */")
	print("   Handshake information is sent by the server to the client")
	print("   after the initial connection (through connect_server()) has")
	print("   been made.  read_handshake() doesn't receive a parameter")
	print("   anymore. Instead all the data is available in the connection")
	print("   tables.n")
end

function read_auth()
	print("/* Entering function read_auth() */")
	print("   The read_auth() function is triggered when an authentication")
	print("   handshake is initiated by the client.  read_auth() doesn't")
	print("   receive a parameter anymore. Instead all the data is")
	print("   available in the connection tables.n")
end

function read_auth_result()
	print("/* Entering function read_auth_result() */")
	print("   The return packet from the server during authentication is")
	print("   captured by read_auth_result().n")
end

function read_query( packet )
	print("/* Entering function read_query( packet ) */")
	o = ""     
	for k, v in pairs(proxy.global) do       
		o = o .. (("[%s] %s (type=%s)n"):format(k, tostring(v), type(v)))
	end
	print("| o = " .. o)
	if string.byte(packet) == proxy.COM_QUERY then
		proxy.queries:append(1, packet, {resultset_is_needed = true} )
        print("| Query               = " 
			.. string.sub(packet, 2))
		print("| Time Stamp          = " 
			.. os.date('%Y-%m-%d %H:%M:%S'))
		print("| Proxy Version       = " 
			.. string.format("%05x", proxy.PROXY_VERSION))
		print("| Lua Version         = " 
			.. _VERSION)
		return proxy.PROXY_SEND_QUERY
	end
end

function read_query_result( inj )
	print("n/* Entering function read_query_result( inj ) */")
	local res         = assert(inj.resultset)
	local raw_len     = assert(res.raw):len()
	local packet      = assert(inj.query)
	local flags       = res.flags
	local backend_ndx = proxy.connection.backend_ndx
	print("| os.date                                      = " 
		.. os.date('%Y-%m-%d %H:%M:%S'))
	print("| inj.query                                    = " 
		.. inj.query)
	print("| proxy.connection.server.scramble_buffer      = " 
		.. string.format("%q",proxy.connection.server.scramble_buffer)) 
	print("| proxy.connection.server.mysqld_version       = " 
		.. proxy.connection.server.mysqld_version)
	print("| proxy.connection.server.thread_id            = " 
		.. proxy.connection.server.thread_id)
	print("| proxy.connection.client.default_db           = " 
		.. proxy.connection.client.default_db)
	print("| proxy.connection.client.username             = " 
		.. proxy.connection.client.username)
	print("| proxy.connection.client.scrambled_password   = " 
		.. string.format("%q", proxy.connection.client.scrambled_password))
	print("| proxy.connection.client.dst.name             = " 
		.. proxy.connection.client.dst.name)
	print("| proxy.connection.client.dst.address          = " 
		.. proxy.connection.client.dst.address)
	print("| proxy.connection.client.dst.port             = " 
		.. proxy.connection.client.dst.port)
	print("| proxy.connection.server.dst.name             = " 
		.. proxy.connection.server.dst.name)
	print("| proxy.connection.server.dst.address          = " 
		.. proxy.connection.server.dst.address)
	print("| proxy.connection.server.dst.port             = " 
		.. proxy.connection.server.dst.port)
	print("| proxy.connection.client.src.name             = " 
		.. proxy.connection.client.src.name)
	print("| proxy.connection.client.src.address          = " 
		.. proxy.connection.client.src.address)
	print("| proxy.connection.client.src.port             = " 
		.. proxy.connection.client.src.port)
	print("| proxy.connection.server.src.name             = " 
		.. proxy.connection.server.src.name)
	print("| proxy.connection.server.src.address          = " 
		.. proxy.connection.server.src.address)
	print("| proxy.connection.server.src.port             = " 
		.. proxy.connection.server.src.port)
	print("| inj.query-time                               = " 
		.. (inj.query_time / 1000) .. "ms")
	print("| inj.response-time                            = " 
		.. (inj.response_time / 1000) .. "ms")
	print("| proxy.connection.backend_ndx                 = " 
		.. proxy.connection.backend_ndx)
	print("| #proxy.global.backends                       = " 
		.. #proxy.global.backends)
	print("| proxy.global.backends[ndx].dst.name          = " 
		.. proxy.global.backends[backend_ndx].dst.name)
	print("| proxy.global.backends[ndx].dst.address       = " 
		.. proxy.global.backends[backend_ndx].dst.address)
	print("| proxy.global.backends[ndx].dst.port          = " 
		.. proxy.global.backends[backend_ndx].dst.port)
	print("| proxy.global.backends[ndx].connected_clients = " 
		.. proxy.global.backends[backend_ndx].connected_clients)
	print("| proxy.global.backends[ndx].state             = " 
		.. proxy.global.backends[backend_ndx].state)
	print("| proxy.global.backends[ndx].pool.max_idle_connections = " 
		.. proxy.global.backends[backend_ndx].pool.max_idle_connections)
	print("| proxy.global.backends[ndx].pool.min_idle_connections = " 
		.. proxy.global.backends[backend_ndx].pool.min_idle_connections)
	print("| proxy.global.backends[ndx].pool.users[1]     = " 
		.. proxy.global.backends[backend_ndx].pool.users[1].cur_idle_connections)
	print("| result.len                                   = " 
		.. raw_len)
	print("| result.flags.in_trans                        = " 
		.. tostring(res.flags.in_trans))
	print("| result.flags.auto_commit                     = " 
		.. tostring(res.flags.auto_commit))
	print("| result.flags.no_good_index_used              = " 
		.. tostring(res.flags.no_good_index_used))
	print("| result.flags.no_index_used                   = " 
		.. tostring(res.flags.no_index_used))	
	print("| result.warning_count                         = " 
		.. res.warning_count)
	print("| result.query_status                          = " 
		.. res.query_status)
	if res.affected_rows then
		print("| result.affected_rows                         = " 
			.. res.affected_rows)
		print("| result.insert_id                             = " 
			.. res.insert_id)
	end
	if res.query_status == proxy.MYSQLD_PACKET_ERR then
		print("| result.err.code                              = " 
			.. res.raw:byte(2) + (res.raw:byte(3) * 256))
		print("| result.err.sql_state                         = " 
			.. string.format("%q", res.raw:sub(5, 9)))
		print("| result.err.msg                               = " 
			.. string.format("%q", res.raw:sub(10)))
	end
	if res.query_status == proxy.MYSQLD_PACKET_OK then
		print("| result.fields[1].type                        = " 
			.. res.fields[1].type)
		print("| result.fields[1].name                        = " 
			.. res.fields[1].name)
		for row in inj.resultset.rows do
			print("| query returned                               = " 
				.. row[1])
		end
	end
	print("n")
end

function disconnect_client()
	print("/* Entering function disconnect_client() */")
	if proxy.connection.server.thread_id then
		print("Client " .. proxy.connection.server.thread_id 
			.. " has disconnected.")
	end
end

Writing output to a log file using MySQL Proxy

In my previous post, Capturing Erroneous Queries with MySQL Proxy, I showed how to capture erroneous queries, along with relevant information, that one could not effectively obtain from the MySQL general query log. However, in that post, I simply output the information to the terminal. Therefore, in this example, I want to show how to write this information to a proxy log file.

To change this to log to a log file does not require too many changes, and so this is a useful example on how to log any proxy-related information to a log file.

  1. I perform a check for an existing log file (at least using the variable of name “log_file”), and then assign log_file to the location of where I want the log file to reside (at the beginning of the lua script).
    if (log_file == nil) then
      log_file = "C:/Program Files/MySQL/mysql-proxy-0.7.2/proxy.log"
    end
  2. Directly after that, and a key element, is to then declare a file handle for this file and open it for writing purposes (directly after the above statements):
    local fh = io.open(log_file, "a+")
  3. I create a local variable named “out_string” and assign all output that I want to write to this variable.
  4. Once you have “out_string” fully populated, then just issue the following two commands:
    fh:write(out_string .. "n")
    fh:flush()

Now everything you have stored in the variable “out_string” will be written to the new proxy log file that you have opened.

Here is the full script for reference:

if (log_file == nil) then
  log_file = "C:/Program Files/MySQL/mysql-proxy-0.7.2/proxy.log"
end 
local fh = io.open(log_file, "a+")

function read_query( packet )		
  if string.byte(packet) == proxy.COM_QUERY then
    proxy.queries:append(1, packet, {resultset_is_needed = true} )
    local out_string = "+ ---------- Incoming query ---------- " .. "n" ..
      "+ Query = " .. string.sub(packet, 2) .. "n" ..
      "+ Time  = " .. os.date('%Y-%m-%d %H:%M:%S') .. "n" ..
      "+ ------------------------------------ "
    fh:write(out_string .. "n")
    fh:flush()
    return proxy.PROXY_SEND_QUERY
  end
end

function read_query_result (inj)
  local res = assert(inj.resultset)	
  if res.query_status == proxy.MYSQLD_PACKET_ERR then
    local out_string = "---------- Returning results ----------" .. "n" ..
      "- time stamp = " 
        .. os.date('%Y-%m-%d %H:%M:%S') .. "n" ..
      "- query = " 
        .. inj.query .. "n" ..
      "- result.err.code = " 
        .. res.raw:byte(2) + (res.raw:byte(3) * 256) .. "n" ..
      "- result.err.sql_state = " 
        .. string.format("%q", res.raw:sub(5, 9)) .. "n" ..
      "- result.err.msg = " 
        .. string.format("%q", res.raw:sub(10)) .. "n" ..
      "- proxy.connection.client.default_db = " 
        .. proxy.connection.client.default_db .. "n" ..
      "- proxy.connection.client.username = " 
        .. proxy.connection.client.username .. "n" ..
      "- proxy.connection.client.address = " 
        .. proxy.connection.client.src.name .. "n" ..
      "- proxy.connection.server.thread_id = " 
        .. proxy.connection.server.thread_id .. "n"
    fh:write(out_string .. "n")
    fh:flush()
  end
end

Capturing Erroneous Queries with MySQL Proxy

The MySQL General Query Log can be very beneficial as it records all queries sent to the server. However, there are times you want more specific information, which can be hard, if not impossible, to filter from this log.

For instance, say you want to capture only queries that cause an error. The errors are not included in the general query log, so filtering based on that is not possible. However, we can easily capture this information using MySQL Proxy with a simple Lua script.

To implement this, the key is to examine the query status in the returned packet from the server to see if there is an error or not. Should the query status be that of an error, then we have what we want, and all that remains is to output whatever relevant information we’d like to see.

Code-wise, we want to examine the returned packet in function read_query_result(), and then check the query status to see if it returns an error (i.e., proxy.MYSQLD_PACKET_ERR). So, we have the following:

function read_query_result (inj)
  local res = assert(inj.resultset)	
  if res.query_status == proxy.MYSQLD_PACKET_ERR then
    -- here we have query that returns an error
    print(...)
  end
end

And that is basically all there is to it.

Here is the full script, so you can see it in its entirety:

function read_query( packet )		
  if string.byte(packet) == proxy.COM_QUERY then
    proxy.queries:append(1, packet, {resultset_is_needed = true} )
    return proxy.PROXY_SEND_QUERY
  end
end

function read_query_result (inj)
  local res = assert(inj.resultset)	
  if res.query_status == proxy.MYSQLD_PACKET_ERR then
    local out_string = 	
      "Time Stamp = " .. os.date('%Y-%m-%d %H:%M:%S') .. "n" ..
      "Query      = " .. inj.query .. "n" ..
      "Error Code = " .. res.raw:byte(2)+(res.raw:byte(3)*256) .. "n" ..
      "SQL State  = " .. string.format("%q", res.raw:sub(5, 9)) .. "n" ..
      "Err Msg    = " .. string.format("%q", res.raw:sub(10)) .. "n" ..
      "Default DB = " .. proxy.connection.client.default_db .. "n" ..
      "Username   = " .. proxy.connection.client.username .. "n" ..
      "Address    = " .. proxy.connection.client.src.name .. "n" ..
      "Thread ID  = " .. proxy.connection.server.thread_id .. "n"
    print(out_string .. "n")
  end
end

Notes:

  1. In function read_query(), when I append the query, I also append the “{resultset_is_needed = true}” flag, as you need to set this to “true” in order to get the resultset returned to the proxy.
  2. In function read_query_result(), I assign all information to a variable, and then simply “print()” the variable. This does add a line of code, but in the end, it makes it easier to write this to an output file instead of to the terminal (or to both), if you desire.

Setting up MySQL Proxy as a Service on Windows

In the newer versions of MySQL Proxy (0.7.2 and newer), it is now possible to create a Windows service for MySQL Proxy due to the inclusion of “mysql-proxy-svc.exe”.

Basically, you could use “mysql-proxy-svc.exe” in lieu of “mysql-proxy.exe” from the command line and you would not notice a difference. However, just invoking this will not create a service for MySQL Proxy. Furthermore, you cannot invoke it with the “–install” option as you can with mysqld.

But, you can create a service for MySQL Proxy using the “sc” command with “mysql-proxy-svc.exe”.

Say I want to create a service that starts automatically when my system is started/rebooted. I also want it to load a certain lua script, say capture-errors.lua. Given this, I would use the following command:

sc create "proxy" DisplayName= "MySQL Proxy" start= "auto" 
binPath= "C:Program FilesMySQLmysql-proxy-0.7.2binmysql-proxy-svc.exe 
--proxy-lua-script=capture-errors.lua"

Here is the session from my console:

C:Program FilesMySQLmysql-proxy-0.7.2bin>sc create "proxy" 
DisplayName= "MySQL Proxy" start= "auto" 
binPath= "C:Program FilesMySQLmysql-proxy-0.7.2binmysql-proxy-svc.exe 
--proxy-lua-script=capture-errors.lua"
[SC] CreateService SUCCESS

Once you have this set-up, you can easily start-up or shutdown the proxy. For instance, the following two commands will start and stop the proxy, respectively:

net start proxy
net stop proxy

Now, a couple of notes about the “sc create” statement:

  1. This will create the service with the name of “proxy”.
  2. I used a “start” type of “auto”. This will mean it will start up automatically when you reboot your system. If you remove that option, it will default to “manual”.
  3. You need a space after each equal sign (=).
  4. I had to use double-quotes (“) around each parameter value, as well as the service name, otherwise it wouldn’t create the service properly on my machine.
  5. The lua script, capture-errors.lua, is located in the proxy basedir (C:Program FilesMySQLmysql-proxy-0.7.2) and so it can be read from that location without a full path. If you have this in a different location, you would need to specify the full path.

If for some reason you want to delete this service, you can issue the following command (where “proxy” is the name of the service that you gave it using the above command):

sc delete proxy

And for more details on “sc”, please refer to the following Microsoft KB article:

http://support.microsoft.com/kb/251192