Posts Tagged ‘mysql proxy’

Added a Table of Contents

Wednesday, November 9th, 2011

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

Tuesday, November 1st, 2011

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

Friday, October 14th, 2011

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)

Tuesday, September 28th, 2010

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

Friday, August 20th, 2010

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

Wednesday, January 27th, 2010

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

Monday, November 30th, 2009

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

Wednesday, October 28th, 2009

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

Tuesday, October 27th, 2009

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

SHOW PROXY PROCESSLIST : Another Proxy Tutorial Revisited

Wednesday, April 15th, 2009

In a previous post, Another Proxy Tutorial, I outlined how you could find and display the originating IP address of a particular query from within MySQL Proxy.

This was a nice illustration of how to accomplish the above, however, in practice, there were a couple of limitations. For one, the queries were “tagged” with the originating IP address, so the query cache would only work for the same query coming from the same IP address. So if you’re using the query cache, then this is not an optimal solution. Secondly, you could only see the IP address for a current query, and not for an idle connection. Thus when issuing a SHOW PROCESSLIST command, one would only see the IP address only for actual queries that are being executed, and not for any of the other idle connections.

This was bothersome to me, so I worked up a better and more efficient solution that addresses both of these concerns.

In this new solution, I implement a new command called SHOW PROXY PROCESSLIST. This leaves SHOW PROCESSLIST output unchanged, and instead, if you want to see the originating IP address of the every connection listed in the SHOW PROCESSLIST output, then simply issue a SHOW PROXY PROCESSLIST command.

Here is an example of the output when using 3 different connections from differing IP addresses:

mysql> SHOW PROXY PROCESSLIST; SHOW PROCESSLIST;
+------+---------------------+-------------------+
| Id   | IP Address          | Time              |
+------+---------------------+-------------------+
| 2    | 127.0.0.1:49528     | 04/15/09 13:22:14 |
| 3    | 192.168.1.100:1322  | 04/15/09 13:22:23 |
| 4    | 192.168.1.103:59374 | 04/15/09 13:22:44 |
+------+---------------------+-------------------+
3 rows in set (0.00 sec)

+----+------+-----------------+------+---------+------+-------+------------------+
| Id | User | Host            | db   | Command | Time | State | Info             |
+----+------+-----------------+------+---------+------+-------+------------------+
|  1 | root | localhost:49526 | NULL | Sleep   |   13 |       | NULL             |
|  2 | root | localhost:49529 | NULL | Query   |    0 | NULL  | SHOW PROCESSLIST |
|  3 | root | localhost:49533 | NULL | Sleep   |   50 |       | NULL             |
|  4 | root | localhost:49547 | NULL | Sleep   |   18 |       | NULL             |
+----+------+-----------------+------+---------+------+-------+------------------+
4 rows in set (0.00 sec)

Here, you can see I simply called both commands in a single call.

The first output is the SHOW PROXY PROCESSLIST output. In it you can see the “Id” which corresponds to the “Id” in the SHOW PROCESSLIST output. You can also see the corresponding originating IP address along with the client source port, as well as the time stamp of when the connection was created.

To implement this, I simply capture and store the client address information in an internal table. I’m able to capture this from within the read_auth_result() function.

function read_auth_result( auth )
	local state = auth.packet:byte()
	if state == proxy.MYSQLD_PACKET_OK then
		initialize_process_table()
		table.insert( proxy.global.process[proxy.connection.server["thread_id"]],
			{ ip = proxy.connection.client["address"], ts = os.time() } )
	end
end

So when within the function read_auth_result(), if state == proxy.MYSQLD_PACKET_OK, then we know the connection was auth’ed, and hence I log this information at this point.

Note that I’m storing the value of proxy.connection.client["address"] which is the original IP address along with the client source port (e.g., 192.168.1.100:1322). The nice thing about this is you can track down the port information too on the original machine.

(Also note that in MySQL Proxy 0.7.0, there are even more available ports (variables), so you could add more to capture, say the client destination port, or the server source port. For more on this, pelase see Jan Kneschke’s post about this.)

On the remote machine (192.168.1.100), if I run the following command, I can track this connection via netstat:

netstat -an | grep 1322
tcp 0 0 192.168.1.100:1322 192.168.1.101:4050 ESTABLISHED

There is also another internal function to proxy that I use, which is disconnect_client(). This is called when a client disconnects, so if this function is entered for a particular connection, I simply delete that connection entry from the internal table.

function disconnect_client()
	local connection_id = proxy.connection.server.thread_id
	if connection_id then
		-- client has disconnected, set this to nil
		proxy.global.process[connection_id] = nil
	end
end

The rest of the code is basically to display this information in a nice format.

Here is the full code:

function read_query( packet )
	if string.byte(packet) == proxy.COM_QUERY then
		if string.sub(packet, 2):lower() == 'select help' then
			return show_process_help()
		end
		if string.sub(packet, 2):lower() == 'show proxy processlist' then
			return show_process_table()
		end
		--debug output
		--dump_process_table()
		proxy.queries:append(1, packet )
		return proxy.PROXY_SEND_QUERY
	end
end

function make_dataset (header, dataset)
	proxy.response.type = proxy.MYSQLD_PACKET_OK

	proxy.response.resultset = {
		fields = {},
		rows = {}
	}
	for i,v in pairs (header) do
		table.insert(proxy.response.resultset.fields, {type = proxy.MYSQL_TYPE_STRING, name = v})
	end
	for i,v in pairs (dataset) do
		table.insert(proxy.response.resultset.rows, v )
	end
	return proxy.PROXY_SEND_RESULT
end

function initialize_process_table()
	if proxy.global.process == nil then
		proxy.global.process= {}
	end
	if proxy.global.process [proxy.connection.server["thread_id"] ]== nil then
		proxy.global.process[proxy.connection.server["thread_id"] ]= {}
	end
end

function show_process_table()
	initialize_process_table()
	local dataset = {}
	local header = { 'Id', 'IP Address', 'Time' }
	local rows = {}
	for t_i, t_v in pairs (proxy.global.process) do
		for s_i, s_v in pairs ( t_v ) do
			table.insert(rows, { t_i, s_v.ip, os.date('%c',s_v.ts) })
		end
	end
	return make_dataset(header,rows)
end

function show_process_help()
	local dataset = {}
	local header = { 'command',  'description' }
	local rows = {
		{'SELECT HELP',                 'This command.'},
		{'SHOW PROXY PROCESSLIST',      'Show all connections and their true IP Address.'},
	}
	return make_dataset(header,rows)
end

function dump_process_table()
	initialize_process_table()
	print('current contents of process table')
	for t_i, t_v in pairs (proxy.global.process) do
		print ('session id: ', t_i)
		for s_i, s_v in pairs ( t_v ) do
			print ( 't', s_i, s_v.ip, s_v.ts )
		end
	end
	print ('---END PROCESS TABLE---')
end

function read_auth_result( auth )
	local state = auth.packet:byte()
	if state == proxy.MYSQLD_PACKET_OK then
		initialize_process_table()
		table.insert( proxy.global.process[proxy.connection.server["thread_id"]],
			{ ip = proxy.connection.client["address"], ts = os.time() } )
	end
end

function disconnect_client()
	local connection_id = proxy.connection.server.thread_id
	if connection_id then
		-- client has disconnected, set this to nil
		proxy.global.process[connection_id] = nil
	end
end

If you saved this to a file named show-ips.lua, then you would start MySQL Proxy as follows:

mysql-proxy --proxy-lua-script=share/mysql-proxy/show-ips.lua

And then you would connect to the Proxy as follows:

mysql -u root -pmysql -P4040

After that, you’ll be able to issue the commands implemented here such as SHOW PROXY PROCESSLIST and SELECT HELP:

mysql> SELECT HELP;
+------------------------+-------------------------------------------------+
| command                | description                                     |
+------------------------+-------------------------------------------------+
| SELECT HELP            | This command.                                   |
| SHOW PROXY PROCESSLIST | Show all connections and their true IP Address. |
+------------------------+-------------------------------------------------+
2 rows in set (0.00 sec)

mysql> SHOW PROXY PROCESSLIST;
+------+---------------------+-------------------+
| Id   | IP Address          | Time              |
+------+---------------------+-------------------+
| 5    | 127.0.0.1:51128     | 04/15/09 14:09:42 |
| 6    | 192.168.1.100:2341  | 04/15/09 14:10:22 |
| 8    | 192.168.1.103:52722 | 04/15/09 14:18:49 |
+------+---------------------+-------------------+
3 rows in set (0.00 sec)

Lastly, note that you can easily integrate these functions to the quan.lua script that the Query Analyzer Proxy uses (for MySQL Enterprise Monitor).


Period Panties by Period Panteez Menstrual Underwear Menstruation PMS Panty