|
This example shows how to monitor/log the TCP-communication
between a client application and a database server.
When developing client/server database applications sometimes
"strange errors" occur. Especially when using 3rd
party database drivers (e.g. DLLs) it can be unclear, where
the problem comes from. Is it the application itself, the
driver, the server ... ?
With cb_PMM you can monitor the communication between
the applications (client and server) on TCP-level. So you
know, "what an application really transmits" ...
The described method can be used with any database system,
which communicates using TCP-sockets: MySQL, Interbase/Firebird,
Oracle ... to name just a few.
In this example a database connection for MySQL is monitored.
To set up the test environment the following steps are necessary:
- cb_PMM has to be configured to map a port (in this
case 3307) to the MySQL-server (on port 3306).
- The MySQL-client has to be configured to connect to port
3307 (instead of 3306). This is done by editing the parameter
in the configuration file of MySQL (MY.INI).
(Another way would be to configure the MySQL-server to listen
on another port (e.g. 3308), and configure cb_PMM to
map port 3306 to 3308. In this case the client application/configuration
can remain unchanged.)
The following log of cb_PMM shows the communication
between the client (the MySQL-monitor application) and the
MySQL-server:
-> Login with wrong password
## 21:41:29:561 Channel 7; Incoming connection from
127.0.0.1:3499 to 127.0.0.1:3307. CurrentThreads: 1 MaxThreads:
1 Connects (In): 11 Disconnects (In): 10 Connects (Out): 10
Bytes C->S: 483 Bytes S->C: 3067
## 21:41:29:561 Channel 7; Mapping connection from 127.0.0.1:3499
to 192.168.1.3:3306. CurrentThreads: 1 MaxThreads: 1 Connects
(In): 11 Disconnects (In): 10 Connects (Out): 11 Bytes C->S:
483 Bytes S->C: 3067
## 21:41:29:561 Channel 7; DATA (Server -> Client): Length=
47
2B 00 00 00 0A 33 2E 32 33 2E 34 39 2D 6E 74 00 + 3.23.49-nt
0F 00 00 00 37 6B 37 6D 40 37 34 3E 00 2C 00 08 7k7m@74>
,
02 00 00 00 00 00 00 00 00 00 00 00 00 00 00
## 21:41:29:571 Channel 7; DATA (Client -> Server):
Length= 22
12 00 00 01 05 24 00 00 00 72 6F 6F 74 00 47 5F $ root G_
5B 43 47 52 4B 5C [CGRK\
## 21:41:29:571 Channel 7; DATA (Server -> Client):
Length= 66
3E 00 00 02 FF 15 04 41 63 63 65 73 73 20 64 65 > Access
de
6E 69 65 64 20 66 6F 72 20 75 73 65 72 3A 20 27 nied for user:
'
72 6F 6F 74 40 63 62 2D 70 63 33 27 20 28 55 73 root@cb-pc3'
(Us
69 6E 67 20 70 61 73 73 77 6F 72 64 3A 20 59 45 ing password:
YE
53 29 S)
## 21:41:29:571 Channel 7; DATA (Client -> Server):
Length= 0
## 21:41:29:571 Channel 7; Disconnect (incoming). CurrentThreads:
0 MaxThreads: 1 Connects (In): 11 Disconnects (In): 11 Connects
(Out): 11 Bytes C->S: 505 Bytes S->C: 3180
-> Login with correct password
## 21:42:42:776 Channel 7; Incoming connection from
127.0.0.1:3501 to 127.0.0.1:3307. CurrentThreads: 1 MaxThreads:
1 Connects (In): 12 Disconnects (In): 11 Connects (Out): 11
Bytes C->S: 505 Bytes S->C: 3180
## 21:42:42:776 Channel 7; Mapping connection from 127.0.0.1:3501
to 192.168.1.3:3306. CurrentThreads: 1 MaxThreads: 1 Connects
(In): 12 Disconnects (In): 11 Connects (Out): 12 Bytes C->S:
505 Bytes S->C: 3180
## 21:42:42:786 Channel 7; DATA (Server -> Client): Length=
47
2B 00 00 00 0A 33 2E 32 33 2E 34 39 2D 6E 74 00 + 3.23.49-nt
10 00 00 00 7A 6E 38 6A 31 53 2E 2D 00 2C 00 08 zn8j1S.- ,
02 00 00 00 00 00 00 00 00 00 00 00 00 00 00
## 21:42:42:786 Channel 7; DATA (Client -> Server):
Length= 22
12 00 00 01 05 24 00 00 00 72 6F 6F 74 00 51 46 $ root QF
55 42 56 4C 50 48 UBVLPH
## 21:42:42:786 Channel 7; DATA (Server -> Client):
Length= 7
03 00 00 02 00 00 00
-> Connect to a database
## 21:43:12:750 Channel 7; DATA (Client -> Server):
Length= 5
01 00 00 00 01
## 21:43:12:760 Channel 7; DATA (Client -> Server):
Length= 0
## 21:43:12:760 Channel 7; Disconnect (incoming). CurrentThreads:
0 MaxThreads: 1 Connects (In): 12 Disconnects (In): 12 Connects
(Out): 12 Bytes C->S: 532 Bytes S->C: 3234
## 21:43:12:770 Channel 7; Incoming connection from 127.0.0.1:3503
to 127.0.0.1:3307. CurrentThreads: 1 MaxThreads: 1 Connects
(In): 13 Disconnects (In): 12 Connects (Out): 12 Bytes C->S:
532 Bytes S->C: 3234
## 21:43:12:770 Channel 7; Mapping connection from 127.0.0.1:3503
to 192.168.1.3:3306. CurrentThreads: 1 MaxThreads: 1 Connects
(In): 13 Disconnects (In): 12 Connects (Out): 13 Bytes C->S:
532 Bytes S->C: 3234
## 21:43:12:770 Channel 7; DATA (Server -> Client): Length=
47
2B 00 00 00 0A 33 2E 32 33 2E 34 39 2D 6E 74 00 + 3.23.49-nt
11 00 00 00 33 5A 4E 54 3E 76 59 3D 00 2C 00 08 3ZNT>vY=
,
02 00 00 00 00 00 00 00 00 00 00 00 00 00 00
## 21:43:12:780 Channel 7; DATA (Client -> Server):
Length= 28
18 00 00 01 0D 24 00 00 00 72 6F 6F 74 00 49 40 $ root I@
5E 4E 46 4B 5F 51 00 6D 79 73 71 6C ^NFK_Q mysql
## 21:43:12:780 Channel 7; DATA (Server -> Client):
Length= 7
03 00 00 02 00 00 00
-> Command "SHOW TABLES"
## 21:43:49:883 Channel 7; DATA (Client -> Server):
Length= 16
0C 00 00 00 03 73 68 6F 77 20 74 61 62 6C 65 73 show tables
## 21:43:49:883 Channel 7; DATA (Server -> Client):
Length= 113
01 00 00 01 01 1B 00 00 02 00 0F 54 61 62 6C 65 Table
73 5F 69 6E 5F 6D 79 73 71 6C 03 40 00 00 01 FE s_in_mysql
@
03 01 00 1F 01 00 00 03 FE 0D 00 00 04 0C 63 6F co
6C 75 6D 6E 73 5F 70 72 69 76 03 00 00 05 02 64 lumns_priv
d
62 05 00 00 06 04 66 75 6E 63 05 00 00 07 04 68 b func h
6F 73 74 0C 00 00 08 0B 74 61 62 6C 65 73 5F 70 ost tables_p
72 69 76 05 00 00 09 04 75 73 65 72 01 00 00 0A riv user
FE
-> Command "SELECT * FROM USER"
## 21:44:10:372 Channel 7; DATA (Client -> Server):
Length= 23
13 00 00 00 03 73 65 6C 65 63 74 20 2A 20 66 72 select * fr
6F 6D 20 75 73 65 72 om user
## 21:44:10:382 Channel 7; DATA (Server -> Client):
Length= 765
01 00 00 01 11 14 00 00 02 04 75 73 65 72 04 48 user H
6F 73 74 03 3C 00 00 01 FE 03 83 40 00 14 00 00 ost < @
03 04 75 73 65 72 04 55 73 65 72 03 10 00 00 01 user User
FE 03 83 40 00 18 00 00 04 04 75 73 65 72 08 50 @ user P
61 73 73 77 6F 72 64 03 10 00 00 01 FE 03 81 00 assword
00 1B 00 00 05 04 75 73 65 72 0B 53 65 6C 65 63 user Selec
74 5F 70 72 69 76 03 01 00 00 01 FE 03 01 01 00 t_priv
1B 00 00 06 04 75 73 65 72 0B 49 6E 73 65 72 74 user Insert
5F 70 72 69 76 03 01 00 00 01 FE 03 01 01 00 1B _priv
00 00 07 04 75 73 65 72 0B 55 70 64 61 74 65 5F user Update_
70 72 69 76 03 01 00 00 01 FE 03 01 01 00 1B 00 priv
00 08 04 75 73 65 72 0B 44 65 6C 65 74 65 5F 70 user Delete_p
72 69 76 03 01 00 00 01 FE 03 01 01 00 1B 00 00 riv
09 04 75 73 65 72 0B 43 72 65 61 74 65 5F 70 72 user Create_pr
69 76 03 01 00 00 01 FE 03 01 01 00 19 00 00 0A iv
04 75 73 65 72 09 44 72 6F 70 5F 70 72 69 76 03 user Drop_priv
01 00 00 01 FE 03 01 01 00 1B 00 00 0B 04 75 73 us
65 72 0B 52 65 6C 6F 61 64 5F 70 72 69 76 03 01 er Reload_priv
00 00 01 FE 03 01 01 00 1D 00 00 0C 04 75 73 65 use
72 0D 53 68 75 74 64 6F 77 6E 5F 70 72 69 76 03 r Shutdown_priv
01 00 00 01 FE 03 01 01 00 1C 00 00 0D 04 75 73 us
65 72 0C 50 72 6F 63 65 73 73 5F 70 72 69 76 03 er Process_priv
01 00 00 01 FE 03 01 01 00 19 00 00 0E 04 75 73 us
65 72 09 46 69 6C 65 5F 70 72 69 76 03 01 00 00 er File_priv
01 FE 03 01 01 00 1A 00 00 0F 04 75 73 65 72 0A user
47 72 61 6E 74 5F 70 72 69 76 03 01 00 00 01 FE Grant_priv
03 01 01 00 1F 00 00 10 04 75 73 65 72 0F 52 65 user Re
66 65 72 65 6E 63 65 73 5F 70 72 69 76 03 01 00 ferences_priv
00 01 FE 03 01 01 00 1A 00 00 11 04 75 73 65 72 user
0A 49 6E 64 65 78 5F 70 72 69 76 03 01 00 00 01 Index_priv
FE 03 01 01 00 1A 00 00 12 04 75 73 65 72 0A 41 user A
6C 74 65 72 5F 70 72 69 76 03 01 00 00 01 FE 03 lter_priv
01 01 00 01 00 00 13 FE 3C 00 00 14 09 6C 6F 63 < loc
61 6C 68 6F 73 74 04 72 6F 6F 74 10 35 63 64 34 alhost root
5cd4
36 33 37 63 32 63 33 36 64 63 62 31 01 59 01 59 637c2c36dcb1
Y Y
01 59 01 59 01 59 01 59 01 59 01 59 01 59 01 59 Y Y Y Y Y
Y Y Y
01 59 01 59 01 59 01 59 3C 00 00 15 09 6C 6F 63 Y Y Y Y<
loc
61 6C 68 6F 73 74 04 62 6D 61 6E 10 37 66 31 30 alhost bman
7f10
36 36 34 32 34 31 63 32 66 36 32 39 01 4E 01 4E 664241c2f629
N N
01 4E 01 4E 01 4E 01 4E 01 4E 01 4E 01 4E 01 4E N N N N N
N N N
01 4E 01 4E 01 4E 01 4E 34 00 00 16 01 25 04 72 N N N N4 %
r
6F 6F 74 10 35 63 64 34 36 33 37 63 32 63 33 36 oot 5cd4637c2c36
64 63 62 31 01 59 01 59 01 59 01 59 01 59 01 59 dcb1 Y Y Y
Y Y Y
01 4E 01 4E 01 4E 01 4E 01 4E 01 59 01 59 01 59 N N N N N
Y Y Y
34 00 00 17 01 25 04 62 6D 61 6E 10 37 66 31 30 4 % bman 7f10
36 36 34 32 34 31 63 32 66 36 32 39 01 59 01 59 664241c2f629
Y Y
01 59 01 59 01 59 01 59 01 4E 01 4E 01 4E 01 4E Y Y Y Y N
N N N
01 4E 01 59 01 59 01 59 01 00 00 18 FE N Y Y Y
-> Wrong Command "XXX"
## 21:47:51:250 Channel 7; DATA (Client -> Server):
Length= 8
04 00 00 00 03 78 78 78 xxx
## 21:47:51:250 Channel 7; DATA (Server -> Client):
Length= 64
3C 00 00 01 FF 28 04 59 6F 75 20 68 61 76 65 20 < ( You
have
61 6E 20 65 72 72 6F 72 20 69 6E 20 79 6F 75 72 an error in
your
20 53 51 4C 20 73 79 6E 74 61 78 20 6E 65 61 72 SQL syntax
near
20 27 78 78 78 27 20 61 74 20 6C 69 6E 65 20 31 'xxx' at line
1
-> Client quits session
## 21:48:15:315 Channel 7; DATA (Client -> Server):
Length= 5
01 00 00 00 01
## 21:48:15:325 Channel 7; DATA (Client -> Server):
Length= 0
-> Reconnect/Relogin
## 21:48:15:325 Channel 7; Disconnect (incoming).
CurrentThreads: 0 MaxThreads: 1 Connects (In): 13 Disconnects
(In): 13 Connects (Out): 13 Bytes C->S: 612 Bytes S->C:
4230
## 21:48:50:385 Channel 7; Incoming connection from 127.0.0.1:3509
to 127.0.0.1:3307. CurrentThreads: 1 MaxThreads: 1 Connects
(In): 14 Disconnects (In): 13 Connects (Out): 13 Bytes C->S:
612 Bytes S->C: 4230
## 21:48:50:385 Channel 7; Mapping connection from 127.0.0.1:3509
to 192.168.1.3:3306. CurrentThreads: 1 MaxThreads: 1 Connects
(In): 14 Disconnects (In): 13 Connects (Out): 14 Bytes C->S:
612 Bytes S->C: 4230
## 21:48:50:385 Channel 7; DATA (Server -> Client): Length=
47
2B 00 00 00 0A 33 2E 32 33 2E 34 39 2D 6E 74 00 + 3.23.49-nt
12 00 00 00 61 52 57 3E 6D 2E 38 71 00 2C 00 08 aRW>m.8q
,
02 00 00 00 00 00 00 00 00 00 00 00 00 00 00
## 21:48:50:395 Channel 7; DATA (Client -> Server):
Length= 22
12 00 00 01 05 24 00 00 00 72 6F 6F 74 00 41 43 $ root AC
4B 4C 47 56 42 49 KLGVBI
## 21:48:50:395 Channel 7; DATA (Server -> Client):
Length= 7
03 00 00 02 00 00 00
-> Server is shut down
## 21:49:14:109 Channel 7; DATA (Server -> Client):
Length= 0
## 21:49:14:109 Channel 7; Disconnect (incoming). CurrentThreads:
0 MaxThreads: 1 Connects (In): 14 Disconnects (In): 14 Connects
(Out): 14 Bytes C->S: 634 Bytes S->C: 4284
Interested in more information? Read the cb_PMM
- Description!
|
