Help with MySQL_Proxy iApp

Question

I'm using the MySQL_Proxy iApp to load balance traffic between two MySQL servers. The associated iRule is generating error when clients attempt to connect to the virtual server. Would someone be able to assist me in determining the cause of the error message?
Here's the error message I'm seeing.
TCL error: mysql_irule  - can't read "from_client_password": no such variable while executing "if { $from_client_xor_result eq $from_client_password } { log local0.debug "BIG-IP MySQL Proxy -- mysql client: good authentication. ..."

Here's a snippet of the iRule involed from its beginning to where the error message appears to originate.
when CLIENT_ACCEPTED {
    array set mysql_pools {
        "read" "mysql-slave.3306.pool"
        "write" "mysql-master.3306.pool"
    }
    array set mysql_pools_reverse {
        "mysql-slave.3306.pool" "read"
        "mysql-master.3306.pool" "write"
    }

array set authenticated_state {
        "client" 0
        "read" 0
        "write" 0
    }
    array set replication {
        "time" 5
        "max_time" 30
    }

log local0.debug "BIG-IP MySQL Proxy -- clientside initial connection"

set salt [b64encode [md5 [expr { int(100000000 * rand()) }]]]
    set client_scramble_buff1 [string range $salt 0 7]
    set client_scramble_buff2 [string range $salt 8 19]
    set clientside_proxied_payload ""
    set clientside_proxied_payload_replayed 0
    set clientside_set_command_payload ""
    set serverside_current_pool "read"

log local0.debug "BIG-IP MySQL Proxy -- clientside responding with server WELCOME packet"
    set server_welcome_formatting "c1A19x1i1a8x1B16c1B16x13a12x1"
    set server_welcome_args [list]
    lappend server_welcome_args 10 "BIG-IP MySQL Proxy" 1 $client_scramble_buff1 0000110010100010 48 0000001000000000 $client_scramble_buff2
    set server_welcome_length [string length [eval "binary format $server_welcome_formatting $server_welcome_args"]]
    set server_welcome_packet "binary format s1x1c1$server_welcome_formatting $server_welcome_length 0 $server_welcome_args"

TCP::respond [eval $server_welcome_packet]
    TCP::collect
}

when CLIENT_DATA {
    if { $authenticated_state(client) != 1 } {
        log local0.debug "BIG-IP MySQL Proxy -- clientside authenticated flag not set"
        set clientside_payload [TCP::payload]
        binary scan $clientside_payload c3cH2a* clientside_mysql_len clientside_mysql_num clientside_mysql_cmd clientside_mysql_arg

if { $clientside_mysql_num == 1 &amp;&amp; $clientside_mysql_cmd eq "8f" } {
            log local0.debug "BIG-IP MySQL Proxy -- mysql client: attempting to authenticate"
            binary scan $clientside_mysql_arg @31A* from_client_user_payload
            set from_client_user [substr $from_client_user_payload 0 "\000"]
            binary scan [findstr $from_client_user_payload "\000" 3] A20A* from_client_password from_client_database

set from_client_hash_stage $client_scramble_buff1
            append from_client_hash_stage $client_scramble_buff2 [sha1 [binary format H40 [class lookup $from_client_user mysql.app_data_group]]]
            binary scan [binary format H40 [class lookup $from_client_user mysql.app_data_group]] c* from_client_xor_lhs
            binary scan [sha1 $from_client_hash_stage] c* from_client_xor_rhs
            set from_client_xor_buffer [list]
            foreach l $from_client_xor_lhs r $from_client_xor_rhs {
                lappend from_client_xor_buffer [expr { $l ^ $r }]
            }
            set from_client_xor_result [binary format c* $from_client_xor_buffer]

if { $from_client_xor_result eq $from_client_password } {
                log local0.debug "BIG-IP MySQL Proxy -- mysql client: good authentication. "
                set authenticated_state(client) 1

fred_slater_856 · Answer

I think you want $from_client_password, not from_client_password in your findstr statement.&nbsp;

lmend · Answer

I don't think so. It looks like the from_client_password variable is a part of the binary scan statement outside of the findstr statement. The findstr statement ends before the reference to A20A*. I think the binary scan statement is what is actually setting the from_client_password variable.

fred_slater_856 · Answer

Agreed.  Is it possible that your findstr statement is returning null, in turn causing the binary scan to exit without assigning a value to 'from_client_password'?

lmend · Answer

I adding some debugging. It's not null, but I don't think the binary scan is returning the expected values. Instead of returning both from_client_password and from_client_database, it only seems to be returning the data needed for from_client_database.

lmend · Answer

I've made some progress on this. I discovered one of the binary scans statements was selected a starting character that came after the user info of the MySQL payload.
I was able to get the client connection working by changing this line from the below
binary scan $clientside_mysql_arg @31A* from_client_user_payload

to this
binary scan $clientside_mysql_arg @4A* from_client_user_payload

Unfortunately, I fixed one problem only to find another. The iRule is not completing the three-way handshake with the actual DB server. It sends the initial syn, the DB servers replies with a syn ack, and then nothing. The F5 never responds to the syn ack and the DB server retransmists the syn ack.
The iRule is erroring out at the "TCP::respond [eval $client_auth_packet]" statement in this section of the iRule.
 when SERVER_DATA {
    if { $authenticated_state($serverside_current_pool) != 1 } {
            log local0.debug "BIG-IP MySQL Proxy -- serverside authenticated flag not set"
            set serverside_payload [TCP::payload]
            binary scan $serverside_payload c3cH2a* serverside_mysql_len serverside_mysql_num serverside_mysql_cmd serverside_mysql_arg

if { $serverside_mysql_num == 0 &amp;&amp; $serverside_mysql_cmd eq "0a" } {
                    log local0.debug "BIG-IP MySQL Proxy -- mysql server: received WELCOME packet"
                    binary scan [findstr $serverside_mysql_arg "\000" 2] i1a8x1B16c1B16x13a12x1 welcome_thread_id welcome_scramble_buff1 welcome_capabilities welcome_language welcome_status welcome_scramble_buff2

set welcome_hash_stage $welcome_scramble_buff1
                    append welcome_hash_stage $welcome_scramble_buff2 [sha1 [binary format H40 [class lookup $from_client_user mcom-db-qa.app_data_group]]]
                    binary scan [binary format H40 [class lookup $from_client_user mcom-db-qa.app_data_group]] c* welcome_xor_lhs
                    binary scan [sha1 $welcome_hash_stage] c* welcome_xor_rhs
                    set welcome_xor_buffer [list]
                    foreach l $welcome_xor_lhs r $welcome_xor_rhs {
                            lappend welcome_xor_buffer [expr { $l ^ $r }]
                    }

http://forge.mysql.com/wiki/MySQL_Internals_ClientServer_Protocol
                    log local0.debug "BIG-IP MySQL Proxy -- serverside responding with client AUTH packet"
                    set client_auth_formatting "B16B16i1c1x23A*x1c1c*A*x1"
                    set client_auth_args [list]
                    lappend client_auth_args 1000111110100010 0000001000000000 1073741824 48 $from_client_user 20 $welcome_xor_buffer $from_client_database
                    set client_auth_length [string length [eval "binary format $client_auth_formatting $client_auth_args"]]
                    set client_auth_packet "binary format s1x1c1$client_auth_formatting $client_auth_length 1 $client_auth_args"

TCP::respond [eval $client_auth_packet]
                    TCP::payload replace 0 [TCP::payload length] ""
                    TCP::collect
            }

Forum Discussion

Help with MySQL_Proxy iApp

5 Replies

Recent Discussions

Issue on license renewal

Viprion files on blades.

redirect not working

active/active Support Declarative Onboarding

Reliable resources for identifying IP addresses

Related Content

MySQL Proxy iApp