Posts Tagged ‘proxy’

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. ;)

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

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

Another MySQL Proxy Tutorial

Thursday, January 22nd, 2009

Since MySQL Proxy 0.7.0 is soon to be released, I thought another brief tutorial would be helpful. Today we ran across a good use case, and so I wanted to pass this along. If you’ve not used Proxy yet, this is a great opportunity to get your feet wet with it and some Lua.

When queries are routed though MySQL Proxy from various servers, the MySQL Server only sees that the query came from the machine Proxy is running on.

So when you are viewing output of SHOW PROCESSLIST, you have no way of telling what server a particular query originated from.

However, this could be very useful information to have, especially to determine which server a particular long-running query is coming from at that particular moment in time.

Output not using Proxy:

mysql> show processlist;
+----+------+-----------------+------+---------+------+-------+------------------+
| Id | User | Host            | db   | Command | Time | State | Info             |
+----+------+-----------------+------+---------+------+-------+------------------+
| 11 | root | localhost:61252 | test | Query   |    0 | NULL  | show processlist |
| 12 | root | 10.1.10.1:61267 | NULL | Query   |   17 | init  | select sleep(30) |
| 13 | root | 10.1.10.2:62047 | NULL | Query   |   18 | init  | select sleep(30) |
| 14 | root | 10.1.10.3:62050 | NULL | Query   |   19 | init  | select sleep(30) |
| 15 | root | 10.1.10.4:62052 | NULL | Query   |   20 | init  | select sleep(30) |
+----+------+-----------------+------+---------+------+-------+------------------+
5 rows in set (0.00 sec)

In the above, you can easily see the IP address of the server the query originated from.

However, now see the output when using Proxy:

mysql> show processlist;
+----+------+-----------------+------+---------+------+-------+------------------+
| Id | User | Host            | db   | Command | Time | State | Info             |
+----+------+-----------------+------+---------+------+-------+------------------+
| 11 | root | localhost:62255 | test | Query   |    0 | NULL  | show processlist |
| 12 | root | localhost:62271 | NULL | Query   |   17 | init  | select sleep(30) |
| 13 | root | localhost:63038 | NULL | Query   |   18 | init  | select sleep(30) |
| 14 | root | localhost:63042 | NULL | Query   |   19 | init  | select sleep(30) |
| 15 | root | localhost:63043 | NULL | Query   |   20 | init  | select sleep(30) |
+----+------+-----------------+------+---------+------+-------+------------------+
5 rows in set (0.00 sec)

Here, every query shows as originating from ‘localhost’. This is accurate, but in this example, we’d like to know the originating server.

So, how do we accomplish this?

A good work-around to this problem is to append the originating host to the query in the form of a query comment. In this way, the comment will get passed through, and will appear in query itself, under the “Info” column of the output. This will also incur very little overhead, as we’re only appending a known value to the query, and then the MySQL parser handles it as it would any other query (which is to execute it while ignoring the comment).

Now, how do we accomplish this with Lua and Proxy?

I started using one of the included sample scripts – tutorial-rewrite.lua.

I chose this file because it already has the stub code included for both the following function:

function read_query( packet )

It is in this function where the query is read, and before it is sent to the MySQL Server. So this is where we can modify the query to append this information.

Here is the resulting function read_query():

function read_query( packet )
  if string.byte(packet) == proxy.COM_QUERY then
    local query = "/* " .. proxy.connection.client["address"] .. " */ " .. string.sub(packet, 2)
    print("we got a normal query: " .. query)
    proxy.queries:append(1, string.char(proxy.COM_QUERY) .. query )
    return proxy.PROXY_SEND_QUERY
  end
end

The first 3 lines are already in that file. However, I did modify the 3rd line to include the comment with the originating server IP.

Here is the new 3rd line:

local query = "/* " .. proxy.connection.client["address"] .. " */ " .. string.sub(packet, 2)

This defines “query” as a local variable, and sets it equal to the comment concatenated with the actual query.

You can see it starts by appending the comment, which is of the form: /* comment */, where comment is the IP address of the server the query came from.

Note that “..” is used to concatenate two strings in Lua.

The IP address can be found in an existing variable, which is:

proxy.connection.client["address"]

And the query itself can be found in an existing variable, which is:

string.sub(packet, 2)

So as long as we’re within function read_query(), then we have access to these variables, and they’ll be properly assigned.

The next statement is the print() statement, which is originally from the sample script, and I left it in for debugging purposes:

print("we got a normal query: " .. query)

And then you just need the following two statements:

proxy.queries:append(1, string.char(proxy.COM_QUERY) .. query )
return proxy.PROXY_SEND_QUERY

The former appends the new query to the query stack to be executed. This must be done so the changes you make to the variable “query” are actually reflected in the packet that gets sent to the server. And in the latter, PROXY_SEND_QUERY is called, which sends the packet (with the modified query) to the MySQL Server to be executed.

So, now we just need to save this function to a file, start up proxy with this script, and then issue a SHOW PROCESSLIST.

I’ll save the above function to a file named ‘retain-hosts.lua’.

In Window #1:

cd "../../Program Files/MySQL/mysql-proxy-0.6.0"
mysql-proxy --proxy-lua-script="C:Program FilesMySQLmysql-proxy-0.6.0retain-hosts.lua"

In Window #2:

mysql -u root -pmysql -h 127.0.0.1 -P 4040 --comments

Note you must pass the –comments option to the MySQL Client, or the comments will get stripped out. This is at least necessary for testing, or if actually passing queries through the command line interface.

Now in Window #2, issue your SHOW PROCESSLIST (note I’ve thrown a few queries at it from some other connections):

mysql> show processlist;
+----+------+-----------------+------+---------+------+-------+----------------------------------------+
| Id | User | Host            | db   | Command | Time | State | Info                                   |
+----+------+-----------------+------+---------+------+-------+----------------------------------------+
| 11 | root | localhost:62252 | test | Query   |    0 | NULL  | /* 127.0.0.1:11251 */ show processlist |
| 12 | root | localhost:62267 | NULL | Query   |   17 | init  | /* 10.1.10.1:15091 */ select sleep(30) |
| 13 | root | localhost:63047 | NULL | Query   |   18 | init  | /* 10.1.10.2:18166 */ select sleep(30) |
| 14 | root | localhost:63050 | NULL | Query   |   19 | init  | /* 10.1.10.3:18934 */ select sleep(30) |
| 15 | root | localhost:63052 | NULL | Query   |   20 | init  | /* 10.1.10.4:19446 */ select sleep(30) |
+----+------+-----------------+------+---------+------+-------+----------------------------------------+
5 rows in set (0.00 sec)

Voila! Now you can see in the IP address of the server the query originates from in the actual query itself, as a comment.

For more information on MySQL Proxy, please see the following links:

http://dev.mysql.com/downloads/mysql-proxy/index.html
http://forge.mysql.com/wiki/MySQL_Proxy
http://forge.mysql.com/wiki/MySQL_Proxy_Cookbook
http://jan.kneschke.de/projects/mysql/mysql-proxy/
http://datacharmer.blogspot.com/


Period Panties by Period Panteez Menstrual Underwear Menstruation PMS Panty