Merge lp:~vlad-lesin/percona-server/issue-21170-ssl_connections_counting into lp:percona-server/5.5

[Vlad Lesin]

Issue 21770:

CLIENT_STATISTICS, THREAD_STATISTICS, and USER_STATISTICS should also have a column TOTAL_CONNECTIONS_SSL to distinguish between these two types of connections. This will help us to better track any users that are connecting to the db servers without SSL.

http://jenkins.percona.com/view/Percona%20Server%205.5/job/percona-server-5.5-param/363/
http://jenkins.percona.com/view/Percona%20Server%205.5/job/percona-server-5.5-vlesin/1/

[Alexey Kopytov (akopytov)]

   - please create a blueprint on Launchpad for this task, so we can
     track it, target to milestones, document, etc. You can then
     reference this MP in the revision comment (rather than the issue #)

   - bug #997036 is a one-line fix, so let's fix it in the same worklog
     rather than add workarounds for it in the test?

   - I don't see any problems with having TOTAL_SSL_CONNECTIONS as the
     last field in the corresponding tables, so you don't have to shift
     other fields and change unrelated lines of code, thus increasing
     the chance of merge conflicts when merging other fixes from 5.1,
     for example.

> +SET GLOBAL `userstat`= default;
> +SET GLOBAL `thread_statistics`= default;

   - please save values to e.g. old_userstat and old_thread_statistics
     and then restore them instead of using the default value. Using the
     default will fail when you want to run the test suite with
     non-standard options, e.g. with --mysqld=--userstat for example. In
     such case, the default value will be different from what the
     variable had on startup.

review: Needs Fixing

[Vlad Lesin (vlad-lesin)]

>
> - please create a blueprint on Launchpad for this task, so we can
> track it, target to milestones, document, etc. You can then
> reference this MP in the revision comment (rather than the issue #)
>
> - bug #997036 is a one-line fix, so let's fix it in the same worklog
> rather than add workarounds for it in the test?
>
> - I don't see any problems with having TOTAL_SSL_CONNECTIONS as the
> last field in the corresponding tables, so you don't have to shift
> other fields and change unrelated lines of code, thus increasing
> the chance of merge conflicts when merging other fixes from 5.1,
> for example.
>
> > +SET GLOBAL `userstat`= default;
> > +SET GLOBAL `thread_statistics`= default;
>
> - please save values to e.g. old_userstat and old_thread_statistics
> and then restore them instead of using the default value. Using the
> default will fail when you want to run the test suite with
> non-standard options, e.g. with --mysqld=--userstat for example. In
> such case, the default value will be different from what the
> variable had on startup.

Done.

[Alexey Kopytov (akopytov)]

Approved after merging 2 separate revisions into one.

review: Approve

=== added file 'Percona-Server/mysql-test/r/percona_ssl_connections_count.result'
--- Percona-Server/mysql-test/r/percona_ssl_connections_count.result	1970-01-01 00:00:00 +0000
+++ Percona-Server/mysql-test/r/percona_ssl_connections_count.result	2012-05-14 14:38:21 +0000
@@ -0,0 +1,21 @@
+SELECT TOTAL_CONNECTIONS, TOTAL_SSL_CONNECTIONS FROM
+INFORMATION_SCHEMA.USER_STATISTICS ORDER BY USER;
+TOTAL_CONNECTIONS	TOTAL_SSL_CONNECTIONS
+1	0
+2	1
+2	1
+2	1
+SELECT TOTAL_CONNECTIONS, TOTAL_SSL_CONNECTIONS FROM
+INFORMATION_SCHEMA.CLIENT_STATISTICS;
+TOTAL_CONNECTIONS	TOTAL_SSL_CONNECTIONS
+7	3
+SELECT TOTAL_CONNECTIONS, TOTAL_SSL_CONNECTIONS FROM
+INFORMATION_SCHEMA.THREAD_STATISTICS ORDER BY THREAD_ID;
+TOTAL_CONNECTIONS	TOTAL_SSL_CONNECTIONS
+1	0
+1	1
+1	0
+1	1
+1	0
+1	1
+1	0
 
=== modified file 'Percona-Server/mysql-test/suite/sys_vars/r/thread_statistics_basic.result'
--- Percona-Server/mysql-test/suite/sys_vars/r/thread_statistics_basic.result	2012-04-18 23:26:11 +0000
+++ Percona-Server/mysql-test/suite/sys_vars/r/thread_statistics_basic.result	2012-05-14 14:38:21 +0000
@@ -1,3 +1,6 @@
 SELECT @@global.thread_statistics;
 @@global.thread_statistics
 0
+SET @old_thread_statistics= @@global.thread_statistics;
+SET GLOBAL thread_statistics= ON;
+SET GLOBAL thread_statistics= @old_thread_statistics;
 
=== modified file 'Percona-Server/mysql-test/suite/sys_vars/t/thread_statistics_basic.test'
--- Percona-Server/mysql-test/suite/sys_vars/t/thread_statistics_basic.test	2012-04-18 23:26:11 +0000
+++ Percona-Server/mysql-test/suite/sys_vars/t/thread_statistics_basic.test	2012-05-14 14:38:21 +0000
@@ -1,1 +1,4 @@
 SELECT @@global.thread_statistics;
+SET @old_thread_statistics= @@global.thread_statistics;
+SET GLOBAL thread_statistics= ON;
+SET GLOBAL thread_statistics= @old_thread_statistics;
 
=== added file 'Percona-Server/mysql-test/t/percona_ssl_connections_count.test'
--- Percona-Server/mysql-test/t/percona_ssl_connections_count.test	1970-01-01 00:00:00 +0000
+++ Percona-Server/mysql-test/t/percona_ssl_connections_count.test	2012-05-14 14:38:21 +0000
@@ -0,0 +1,79 @@
+#
+#   Counting ssl connections test.
+#
+#   CLIENT_STATISTICS, THREAD_STATISTICS, and USER_STATISTICS have
+#   a column TOTAL_CONNECTIONS_SSL to distinguish between these two types of
+#   connections.
+#   
+
+--source include/have_ssl_communication.inc
+
+# Save the initial number of concurrent sessions
+--source include/count_sessions.inc
+--disable_query_log
+--disable_result_log
+
+
+SET @old_userstat= @@global.userstat;
+SET @old_thread_statistics= @@global.thread_statistics;
+SET GLOBAL userstat= ON;
+SET GLOBAL thread_statistics= ON;
+
+--let $sessions_count=3
+
+#create users
+--let $i=$sessions_count
+while ($i) {
+--dec $i
+--eval CREATE USER 'user_$i'@'localhost' IDENTIFIED BY 'mypass';
+}
+
+#create ssl and non-ssl connections
+--let $i=$sessions_count
+while ($i) {
+--dec $i
+--connect (connection_ssl_$i,localhost,user_$i,mypass,,,,SSL)
+  SELECT 1;
+--connect (connection_$i,localhost,user_$i,mypass,,,,)
+  SELECT 1;
+}
+
+#get statistics
+--enable_query_log
+--enable_result_log
+--connection default
+
+SELECT TOTAL_CONNECTIONS, TOTAL_SSL_CONNECTIONS FROM
+INFORMATION_SCHEMA.USER_STATISTICS ORDER BY USER;
+
+SELECT TOTAL_CONNECTIONS, TOTAL_SSL_CONNECTIONS FROM
+INFORMATION_SCHEMA.CLIENT_STATISTICS;
+
+SELECT TOTAL_CONNECTIONS, TOTAL_SSL_CONNECTIONS FROM
+INFORMATION_SCHEMA.THREAD_STATISTICS ORDER BY THREAD_ID;
+
+--disable_query_log
+--disable_result_log
+
+#close connections
+--let $i=$sessions_count
+while ($i) {
+--dec $i
+--disconnect connection_$i
+--disconnect connection_ssl_$i
+}
+
+--connection default
+
+--source include/wait_until_count_sessions.inc
+
+#remove users
+--let $i=$sessions_count
+while ($i) {
+--dec $i
+--eval DROP USER 'user_$i'@'localhost';
+}
+
+SET GLOBAL userstat= @old_userstat;
+SET GLOBAL thread_statistics= @old_thread_statistics;
+
 
=== modified file 'Percona-Server/sql/sql_connect.cc'
--- Percona-Server/sql/sql_connect.cc	2012-04-19 16:51:34 +0000
+++ Percona-Server/sql/sql_connect.cc	2012-05-14 14:38:21 +0000
@@ -159,6 +159,7 @@
                      const char *user,
                      const char *priv_user,
                      uint total_connections,
+                     uint total_ssl_connections,
                      uint concurrent_connections,
                      time_t connected_time,
                      double busy_time,
@@ -187,6 +188,7 @@
   strncpy(user_stats->priv_user, priv_user, sizeof(user_stats->priv_user));
 
   user_stats->total_connections=      total_connections;
+  user_stats->total_ssl_connections=  total_ssl_connections;
   user_stats->concurrent_connections= concurrent_connections;
   user_stats->connected_time=         connected_time;
   user_stats->busy_time=              busy_time;
@@ -212,6 +214,7 @@
 void init_thread_stats(THREAD_STATS *thread_stats,
                      my_thread_id id,
                      uint total_connections,
+                     uint total_ssl_connections,
                      uint concurrent_connections,
                      time_t connected_time,
                      double busy_time,
@@ -239,6 +242,7 @@
   thread_stats->id= id;
 
   thread_stats->total_connections=      total_connections;
+  thread_stats->total_ssl_connections=  total_ssl_connections;
   thread_stats->concurrent_connections= concurrent_connections;
   thread_stats->connected_time=         connected_time;
   thread_stats->busy_time=              busy_time;
@@ -477,7 +481,7 @@
     }
 
     init_user_stats(user_stats, name, role_name,
-                    0, 0,      // connections
+                    0, 0, 0,   // connections
                     0, 0, 0,   // time
                     0, 0, 0,   // bytes sent, received and written
                     0, 0, 0,   // rows fetched, updated and read
@@ -495,6 +499,8 @@
     }
   }
   user_stats->total_connections++;
+  if (thd->net.vio->type == VIO_TYPE_SSL)
+    user_stats->total_ssl_connections++;
   return 0;
 }
 
@@ -515,7 +521,7 @@
     }
 
     init_thread_stats(thread_stats, id,
-                    0, 0,      // connections
+                    0, 0, 0,      // connections
                     0, 0, 0,   // time
                     0, 0, 0,   // bytes sent, received and written
                     0, 0, 0,   // rows fetched, updated and read
@@ -533,6 +539,8 @@
     }
   }
   thread_stats->total_connections++;
+  if (thd->net.vio->type == VIO_TYPE_SSL)
+    thread_stats->total_ssl_connections++;
   return 0;
 }
 
 
=== modified file 'Percona-Server/sql/sql_show.cc'
--- Percona-Server/sql/sql_show.cc	2012-05-02 07:37:27 +0000
+++ Percona-Server/sql/sql_show.cc	2012-05-14 14:38:21 +0000
@@ -2467,6 +2467,7 @@
       table->field[18]->store((longlong)user_stats->lost_connections);
       table->field[19]->store((longlong)user_stats->access_denied_errors);
       table->field[20]->store((longlong)user_stats->empty_queries);
+      table->field[21]->store((longlong)user_stats->total_ssl_connections);
       if (schema_table_store_record(thd, table))
       {
 	      DBUG_PRINT("error", ("store record error"));
@@ -2504,6 +2505,7 @@
       table->field[18]->store((longlong)user_stats->lost_connections);
       table->field[19]->store((longlong)user_stats->access_denied_errors);
       table->field[20]->store((longlong)user_stats->empty_queries);
+      table->field[21]->store((longlong)user_stats->total_ssl_connections);
       if (schema_table_store_record(thd, table))
       {
               DBUG_PRINT("error", ("store record error"));
@@ -8082,6 +8084,7 @@
   {"LOST_CONNECTIONS", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, "Lost_connections", SKIP_OPEN_TABLE},
   {"ACCESS_DENIED", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, "Access_denied", SKIP_OPEN_TABLE},
   {"EMPTY_QUERIES", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, "Empty_queries", SKIP_OPEN_TABLE},
+  {"TOTAL_SSL_CONNECTIONS", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, "Total_ssl_connections", SKIP_OPEN_TABLE},
   {0, 0, MYSQL_TYPE_STRING, 0, 0, 0, 0}
 };
 
@@ -8107,7 +8110,8 @@
   {"DENIED_CONNECTIONS", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, "Denied_connections", SKIP_OPEN_TABLE},
   {"LOST_CONNECTIONS", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, "Lost_connections", SKIP_OPEN_TABLE},
   {"ACCESS_DENIED", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, "Access_denied", SKIP_OPEN_TABLE},
-  {"EMPTY_QUERIES", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, "Empty_queries", SKIP_OPEN_TABLE},
+  {"EMPTY_QUERIES", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, "Empty_queries", SKIP_OPEN_TABLE}, 
+  {"TOTAL_SSL_CONNECTIONS", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, "Total_ssl_connections", SKIP_OPEN_TABLE},
   {0, 0, MYSQL_TYPE_STRING, 0, 0, 0, 0}
 };
 
@@ -8134,6 +8138,7 @@
   {"LOST_CONNECTIONS", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, "Lost_connections", SKIP_OPEN_TABLE},
   {"ACCESS_DENIED", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, "Access_denied", SKIP_OPEN_TABLE},
   {"EMPTY_QUERIES", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, "Empty_queries", SKIP_OPEN_TABLE},
+  {"TOTAL_SSL_CONNECTIONS", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, "Total_ssl_connections", SKIP_OPEN_TABLE},
   {0, 0, MYSQL_TYPE_STRING, 0, 0, 0, 0}
 };
 
 
=== modified file 'Percona-Server/sql/sql_yacc.yy'
--- Percona-Server/sql/sql_yacc.yy	2012-04-19 16:51:34 +0000
+++ Percona-Server/sql/sql_yacc.yy	2012-05-14 14:38:21 +0000
@@ -12801,6 +12801,7 @@
         | TEMPTABLE_SYM            {}
         | TEXT_SYM                 {}
         | THAN_SYM                 {}
+        | THREAD_STATS_SYM         {} 
         | TRANSACTION_SYM          {}
         | TRIGGERS_SYM             {}
         | TIMESTAMP                {}
 
=== modified file 'Percona-Server/sql/structs.h'
--- Percona-Server/sql/structs.h	2012-04-18 23:26:01 +0000
+++ Percona-Server/sql/structs.h	2012-05-14 14:38:21 +0000
@@ -225,6 +225,7 @@
   // Otherwise, the same value as user.
   char priv_user[max(USERNAME_LENGTH, LIST_PROCESS_HOST_LEN) + 1];
   uint total_connections;
+  uint total_ssl_connections;
   uint concurrent_connections;
   time_t connected_time;  // in seconds
   double busy_time;       // in seconds
@@ -253,6 +254,7 @@
                 const char *user,
                 const char *priv_user,
                 uint total_connections,
+                uint total_ssl_connections,
                 uint concurrent_connections,
                 time_t connected_time,
                 double busy_time,
@@ -300,6 +302,7 @@
 typedef struct st_thread_stats {
   my_thread_id id;
   uint total_connections;
+  uint total_ssl_connections;
   uint concurrent_connections;
   time_t connected_time;  // in seconds
   double busy_time;       // in seconds
@@ -327,6 +330,7 @@
 init_thread_stats(THREAD_STATS *thread_stats,
                 my_thread_id id,
                 uint total_connections,
+                uint total_ssl_connections,
                 uint concurrent_connections,
                 time_t connected_time,
                 double busy_time,