go.d: mysql: disable session query log and slow query log (#17219)

5 files changed, 114 insertions, 0 deletions

diff --git a/src/go/collectors/go.d.plugin/modules/mysql/collect.go b/src/go/collectors/go.d.plugin/modules/mysql/collect.go
index 796ca22ff2..5f28cd1395 100644
--- a/src/go/collectors/go.d.plugin/modules/mysql/collect.go
+++ b/src/go/collectors/go.d.plugin/modules/mysql/collect.go
@@ -28,6 +28,8 @@ func (m *MySQL) collect() (map[string]int64, error) {
 		m.doUserStatistics = m.isPercona || m.isMariaDB && m.version.GTE(semver.Version{Major: 10, Minor: 1, Patch: 1})
 	}
 
+	m.disableSessionQueryLog()
+
 	mx := make(map[string]int64)
 
 	if err := m.collectGlobalStatus(mx); err != nil {
diff --git a/src/go/collectors/go.d.plugin/modules/mysql/disable_logging.go b/src/go/collectors/go.d.plugin/modules/mysql/disable_logging.go
new file mode 100644
index 0000000000..3a2eea6a12
--- /dev/null
+++ b/src/go/collectors/go.d.plugin/modules/mysql/disable_logging.go
@@ -0,0 +1,58 @@
+// SPDX-License-Identifier: GPL-3.0-or-later
+
+package mysql
+
+const (
+	queryShowSessionVariables = `
+SHOW SESSION VARIABLES 
+WHERE 
+  Variable_name LIKE 'sql_log_off'
+  OR Variable_name LIKE 'slow_query_log';`
+)
+
+const (
+	queryDisableSessionQueryLog     = "SET SESSION sql_log_off='ON';"
+	queryDisableSessionSlowQueryLog = "SET SESSION slow_query_log='OFF';"
+)
+
+func (m *MySQL) disableSessionQueryLog() {
+	q := queryShowSessionVariables
+	m.Debugf("executing query: '%s'", q)
+
+	var sqlLogOff, slowQueryLog string
+	var name string
+	_, err := m.collectQuery(q, func(column, value string, _ bool) {
+		switch column {
+		case "Variable_name":
+			name = value
+		case "Value":
+			switch name {
+			case "sql_log_off":
+				sqlLogOff = value
+			case "slow_query_log":
+				slowQueryLog = value
+			}
+		}
+	})
+	if err != nil {
+		m.Debug(err)
+		return
+	}
+
+	if sqlLogOff == "OFF" && m.doDisableSessionQueryLog {
+		// requires SUPER privileges
+		q = queryDisableSessionQueryLog
+		m.Debugf("executing query: '%s'", q)
+		if _, err := m.collectQuery(q, func(_, _ string, _ bool) {}); err != nil {
+			m.Infof("failed to disable session query log (sql_log_off): %v", err)
+			m.doDisableSessionQueryLog = false
+		}
+	}
+	if slowQueryLog == "ON" {
+		q = queryDisableSessionSlowQueryLog
+		m.Debugf("executing query: '%s'", q)
+		if _, err := m.collectQuery(q, func(_, _ string, _ bool) {}); err != nil {
+			m.Debugf("failed to disable session slow query log (slow_query_log): %v", err)
+		}
+	}
+}
diff --git a/src/go/collectors/go.d.plugin/modules/mysql/mysql.go b/src/go/collectors/go.d.plugin/modules/mysql/mysql.go
index 83055d4733..a73755b3a7 100644
--- a/src/go/collectors/go.d.plugin/modules/mysql/mysql.go
+++ b/src/go/collectors/go.d.plugin/modules/mysql/mysql.go
@@ -43,6 +43,7 @@ func New() *MySQL {
 		addGaleraOnce:                  &sync.Once{},
 		addQCacheOnce:                  &sync.Once{},
 		addTableOpenCacheOverflowsOnce: &sync.Once{},
+		doDisableSessionQueryLog:       true,
 		doSlaveStatus:                  true,
 		doUserStatistics:               true,
 		collectedReplConns:             make(map[string]bool),
@@ -79,6 +80,8 @@ type MySQL struct {
 	isMariaDB bool
 	isPercona bool
 
+	doDisableSessionQueryLog bool
+
 	doSlaveStatus      bool
 	collectedReplConns map[string]bool
 	doUserStatistics   bool
diff --git a/src/go/collectors/go.d.plugin/modules/mysql/mysql_test.go b/src/go/collectors/go.d.plugin/modules/mysql/mysql_test.go
index 6319d12b69..da64507e41 100644
--- a/src/go/collectors/go.d.plugin/modules/mysql/mysql_test.go
+++ b/src/go/collectors/go.d.plugin/modules/mysql/mysql_test.go
@@ -24,6 +24,8 @@ var (
 	dataConfigJSON, _ = os.ReadFile("testdata/config.json")
 	dataConfigYAML, _ = os.ReadFile("testdata/config.yaml")
 
+	dataSessionVariables, _ = os.ReadFile("testdata/session_variables.txt")
+
 	dataMySQLVer8030Version, _                  = os.ReadFile("testdata/mysql/v8.0.30/version.txt")
 	dataMySQLVer8030GlobalStatus, _             = os.ReadFile("testdata/mysql/v8.0.30/global_status.txt")
 	dataMySQLVer8030GlobalVariables, _          = os.ReadFile("testdata/mysql/v8.0.30/global_variables.txt")
@@ -60,6 +62,7 @@ func Test_testDataIsValid(t *testing.T) {
 	for name, data := range map[string][]byte{
 		"dataConfigJSON":                               dataConfigJSON,
 		"dataConfigYAML":                               dataConfigYAML,
+		"dataSessionVariables":                         dataSessionVariables,
 		"dataMySQLVer8030Version":                      dataMySQLVer8030Version,
 		"dataMySQLVer8030GlobalStatus":                 dataMySQLVer8030GlobalStatus,
 		"dataMySQLVer8030GlobalVariables":              dataMySQLVer8030GlobalVariables,
@@ -164,6 +167,9 @@ func TestMySQL_Check(t *testing.T) {
 			wantFail: false,
 			prepareMock: func(t *testing.T, m sqlmock.Sqlmock) {
 				mockExpect(t, m, queryShowVersion, dataMariaVer1084Version)
+				mockExpect(t, m, queryShowSessionVariables, dataSessionVariables)
+				mockExpect(t, m, queryDisableSessionQueryLog, nil)
+				mockExpect(t, m, queryDisableSessionSlowQueryLog, nil)
 				mockExpect(t, m, queryShowGlobalStatus, dataMariaVer1084GlobalStatus)
 				mockExpect(t, m, queryShowGlobalVariables, dataMariaVer1084GlobalVariables)
 				mockExpect(t, m, queryShowAllSlavesStatus, dataMariaVer1084AllSlavesStatusMultiSource)
@@ -181,6 +187,9 @@ func TestMySQL_Check(t *testing.T) {
 			wantFail: true,
 			prepareMock: func(t *testing.T, m sqlmock.Sqlmock) {
 				mockExpect(t, m, queryShowVersion, dataMariaVer1084Version)
+				mockExpect(t, m, queryShowSessionVariables, dataSessionVariables)
+				mockExpect(t, m, queryDisableSessionQueryLog, nil)
+				mockExpect(t, m, queryDisableSessionSlowQueryLog, nil)
 				mockExpectErr(m, queryShowGlobalStatus)
 			},
 		},
@@ -188,6 +197,9 @@ func TestMySQL_Check(t *testing.T) {
 			wantFail: true,
 			prepareMock: func(t *testing.T, m sqlmock.Sqlmock) {
 				mockExpect(t, m, queryShowVersion, dataMariaVer1084Version)
+				mockExpect(t, m, queryShowSessionVariables, dataSessionVariables)
+				mockExpect(t, m, queryDisableSessionQueryLog, nil)
+				mockExpect(t, m, queryDisableSessionSlowQueryLog, nil)
 				mockExpectErr(m, queryShowGlobalStatus)
 			},
 		},
@@ -195,6 +207,9 @@ func TestMySQL_Check(t *testing.T) {
 			wantFail: false,
 			prepareMock: func(t *testing.T, m sqlmock.Sqlmock) {
 				mockExpect(t, m, queryShowVersion, dataMariaVer1084Version)
+				mockExpect(t, m, queryShowSessionVariables, dataSessionVariables)
+				mockExpect(t, m, queryDisableSessionQueryLog, nil)
+				mockExpect(t, m, queryDisableSessionSlowQueryLog, nil)
 				mockExpect(t, m, queryShowGlobalStatus, dataMariaVer1084GlobalStatus)
 				mockExpect(t, m, queryShowGlobalVariables, dataMariaVer1084GlobalVariables)
 				mockExpectErr(m, queryShowAllSlavesStatus)
@@ -206,6 +221,9 @@ func TestMySQL_Check(t *testing.T) {
 			wantFail: false,
 			prepareMock: func(t *testing.T, m sqlmock.Sqlmock) {
 				mockExpect(t, m, queryShowVersion, dataMariaVer1084Version)
+				mockExpect(t, m, queryShowSessionVariables, dataSessionVariables)
+				mockExpect(t, m, queryDisableSessionQueryLog, nil)
+				mockExpect(t, m, queryDisableSessionSlowQueryLog, nil)
 				mockExpect(t, m, queryShowGlobalStatus, dataMariaVer1084GlobalStatus)
 				mockExpect(t, m, queryShowGlobalVariables, dataMariaVer1084GlobalVariables)
 				mockExpect(t, m, queryShowAllSlavesStatus, dataMariaVer1084AllSlavesStatusMultiSource)
@@ -217,6 +235,9 @@ func TestMySQL_Check(t *testing.T) {
 			wantFail: false,
 			prepareMock: func(t *testing.T, m sqlmock.Sqlmock) {
 				mockExpect(t, m, queryShowVersion, dataMariaVer1084Version)
+				mockExpect(t, m, queryShowSessionVariables, dataSessionVariables)
+				mockExpect(t, m, queryDisableSessionQueryLog, nil)
+				mockExpect(t, m, queryDisableSessionSlowQueryLog, nil)
 				mockExpect(t, m, queryShowGlobalStatus, dataMariaVer1084GlobalStatus)
 				mockExpect(t, m, queryShowGlobalVariables, dataMariaVer1084GlobalVariables)
 				mockExpect(t, m, queryShowAllSlavesStatus, dataMariaVer1084AllSlavesStatusMultiSource)
@@ -260,6 +281,9 @@ func TestMySQL_Collect(t *testing.T) {
 			{
 				prepareMock: func(t *testing.T, m sqlmock.Sqlmock) {
 					mockExpect(t, m, queryShowVersion, dataMariaVer5564Version)
+					mockExpect(t, m, queryShowSessionVariables, dataSessionVariables)
+					mockExpect(t, m, queryDisableSessionQueryLog, nil)
+					mockExpect(t, m, queryDisableSessionSlowQueryLog, nil)
 					mockExpect(t, m, queryShowGlobalStatus, dataMariaVer5564GlobalStatus)
 					mockExpect(t, m, queryShowGlobalVariables, dataMariaVer5564GlobalVariables)
 					mockExpect(t, m, queryShowSlaveStatus, nil)
@@ -391,6 +415,9 @@ func TestMySQL_Collect(t *testing.T) {
 			{
 				prepareMock: func(t *testing.T, m sqlmock.Sqlmock) {
 					mockExpect(t, m, queryShowVersion, dataMariaVer1084Version)
+					mockExpect(t, m, queryShowSessionVariables, dataSessionVariables)
+					mockExpect(t, m, queryDisableSessionQueryLog, nil)
+					mockExpect(t, m, queryDisableSessionSlowQueryLog, nil)
 					mockExpect(t, m, queryShowGlobalStatus, dataMariaVer1084GlobalStatus)
 					mockExpect(t, m, queryShowGlobalVariables, dataMariaVer1084GlobalVariables)
 					mockExpect(t, m, queryShowAllSlavesStatus, nil)
@@ -570,6 +597,9 @@ func TestMySQL_Collect(t *testing.T) {
 			{
 				prepareMock: func(t *testing.T, m sqlmock.Sqlmock) {
 					mockExpect(t, m, queryShowVersion, dataMariaVer1084Version)
+					mockExpect(t, m, queryShowSessionVariables, dataSessionVariables)
+					mockExpect(t, m, queryDisableSessionQueryLog, nil)
+					mockExpect(t, m, queryDisableSessionSlowQueryLog, nil)
 					mockExpect(t, m, queryShowGlobalStatus, dataMariaVer1084GlobalStatus)
 					mockExpect(t, m, queryShowGlobalVariables, dataMariaVer1084GlobalVariables)
 					mockExpect(t, m, queryShowAllSlavesStatus, dataMariaVer1084AllSlavesStatusSingleSource)
@@ -751,6 +781,9 @@ func TestMySQL_Collect(t *testing.T) {
 			{
 				prepareMock: func(t *testing.T, m sqlmock.Sqlmock) {
 					mockExpect(t, m, queryShowVersion, dataMariaVer1084Version)
+					mockExpect(t, m, queryShowSessionVariables, dataSessionVariables)
+					mockExpect(t, m, queryDisableSessionQueryLog, nil)
+					mockExpect(t, m, queryDisableSessionSlowQueryLog, nil)
 					mockExpect(t, m, queryShowGlobalStatus, dataMariaVer1084GlobalStatus)
 					mockExpect(t, m, queryShowGlobalVariables, dataMariaVer1084GlobalVariables)
 					mockExpect(t, m, queryShowAllSlavesStatus, dataMariaVer1084AllSlavesStatusMultiSource)
@@ -935,6 +968,9 @@ func TestMySQL_Collect(t *testing.T) {
 			{
 				prepareMock: func(t *testing.T, m sqlmock.Sqlmock) {
 					mockExpect(t, m, queryShowVersion, dataMariaVer1084Version)
+					mockExpect(t, m, queryShowSessionVariables, dataSessionVariables)
+					mockExpect(t, m, queryDisableSessionQueryLog, nil)
+					mockExpect(t, m, queryDisableSessionSlowQueryLog, nil)
 					mockExpect(t, m, queryShowGlobalStatus, dataMariaVer1084GlobalStatus)
 					mockExpect(t, m, queryShowGlobalVariables, dataMariaVer1084GlobalVariables)
 					mockExpectErr(m, queryShowAllSlavesStatus)
@@ -1113,6 +1149,9 @@ func TestMySQL_Collect(t *testing.T) {
 			{
 				prepareMock: func(t *testing.T, m sqlmock.Sqlmock) {
 					mockExpect(t, m, queryShowVersion, dataMariaGaleraClusterVer1084Version)
+					mockExpect(t, m, queryShowSessionVariables, dataSessionVariables)
+					mockExpect(t, m, queryDisableSessionQueryLog, nil)
+					mockExpect(t, m, queryDisableSessionSlowQueryLog, nil)
 					mockExpect(t, m, queryShowGlobalStatus, dataMariaGaleraClusterVer1084GlobalStatus)
 					mockExpect(t, m, queryShowGlobalVariables, dataMariaGaleraClusterVer1084GlobalVariables)
 					mockExpect(t, m, queryShowAllSlavesStatus, nil)
@@ -1307,6 +1346,9 @@ func TestMySQL_Collect(t *testing.T) {
 			{
 				prepareMock: func(t *testing.T, m sqlmock.Sqlmock) {
 					mockExpect(t, m, queryShowVersion, dataMySQLVer8030Version)
+					mockExpect(t, m, queryShowSessionVariables, dataSessionVariables)
+					mockExpect(t, m, queryDisableSessionQueryLog, nil)
+					mockExpect(t, m, queryDisableSessionSlowQueryLog, nil)
 					mockExpect(t, m, queryShowGlobalStatus, dataMySQLVer8030GlobalStatus)
 					mockExpect(t, m, queryShowGlobalVariables, dataMySQLVer8030GlobalVariables)
 					mockExpect(t, m, queryShowReplicaStatus, dataMySQLVer8030ReplicaStatusMultiSource)
@@ -1442,6 +1484,9 @@ func TestMySQL_Collect(t *testing.T) {
 			{
 				prepareMock: func(t *testing.T, m sqlmock.Sqlmock) {
 					mockExpect(t, m, queryShowVersion, dataPerconaVer8029Version)
+					mockExpect(t, m, queryShowSessionVariables, dataSessionVariables)
+					mockExpect(t, m, queryDisableSessionQueryLog, nil)
+					mockExpect(t, m, queryDisableSessionSlowQueryLog, nil)
 					mockExpect(t, m, queryShowGlobalStatus, dataPerconaVer8029GlobalStatus)
 					mockExpect(t, m, queryShowGlobalVariables, dataPerconaVer8029GlobalVariables)
 					mockExpect(t, m, queryShowReplicaStatus, nil)
diff --git a/src/go/collectors/go.d.plugin/modules/mysql/testdata/session_variables.txt b/src/go/collectors/go.d.plugin/modules/mysql/testdata/session_variables.txt
new file mode 100644
index 0000000000..358750607c
--- /dev/null
+++ b/src/go/collectors/go.d.plugin/modules/mysql/testdata/session_variables.txt
@@ -0,0 +1,6 @@
++----------------+-------+
+| Variable_name  | Value |
++----------------+-------+
+| sql_log_off    | OFF   |
+| slow_query_log | ON    |
++----------------+-------+
\ No newline at end of file