Class: Detector::Addons::MySQL

Inherits:
Base
  • Object
show all
Defined in:
lib/detector/addons/mysql.rb

Direct Known Subclasses

MariaDB

Instance Attribute Summary

Attributes inherited from Base

#keys, #uri

Class Method Summary collapse

Instance Method Summary collapse

Methods inherited from Base

#asn, #connection?, #connection_usage_percentage, #databases?, detect, #geo, #geography, #host, #infrastructure, #initialize, #ip, #kind, #ping, #port, #region, register_addon, #sql?, #summary, #tables?, #tcp_test, #transport?, #udp_test, #valid?, #with_connection

Constructor Details

This class inherits a constructor from Detector::Base

Class Method Details

.capabilities_for(url) ⇒ Object 



10
11
12
 
# File 'lib/detector/addons/mysql.rb', line 10

def self.capabilities_for(url)
  { sql: true, kv: true, url: url, kind: :mysql, databases: true, tables: true }
end

.handles_uri?(uri) ⇒ Boolean

Returns:

  • (Boolean) 


6
7
8
 
# File 'lib/detector/addons/mysql.rb', line 6

def self.handles_uri?(uri)
  uri.scheme.downcase == 'mysql'
end

Instance Method Details

#cli_name ⇒ Object 



143
144
145
 
# File 'lib/detector/addons/mysql.rb', line 143

def cli_name
  "mysql"
end

#close ⇒ Object 



258
259
260
261
262
263
 
# File 'lib/detector/addons/mysql.rb', line 258

def close
  if @conn
    @conn.close rescue nil
    @conn = nil
  end
end

#connection ⇒ Object 



14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
 
# File 'lib/detector/addons/mysql.rb', line 14

def connection
  # Create a new connection each time without caching
  # Handle URI path correctly - strip leading slash if present
  db_name = uri.path ? uri.path.sub(/^\//, '') : nil
  
  begin
    # Try connection with longer timeouts - fix for MariaDB syntax
    Mysql2::Client.new(
      host: host,
      username: uri.user,
      password: uri.password,
      database: db_name,
      port: port,
      connect_timeout: 15,
      read_timeout: 30,
      write_timeout: 30,
      init_command: "SET wait_timeout=900; SET interactive_timeout=900"
    )
  rescue Mysql2::Error => e
    puts "MySQL connection error: #{e.message}" if ENV['DETECTOR_DEBUG']
    nil
  rescue => e
    puts "General connection error: #{e.class} - #{e.message}" if ENV['DETECTOR_DEBUG']
    nil
  end
end

#connection_count ⇒ Object 



103
104
105
106
 
# File 'lib/detector/addons/mysql.rb', line 103

def connection_count
  return nil unless connection
  connection.query("SELECT COUNT(*) AS count FROM information_schema.PROCESSLIST").first['count']
end

#connection_info ⇒ Object 



113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
 
# File 'lib/detector/addons/mysql.rb', line 113

def connection_info
  return nil unless connection
  begin
    user_limit = connection.query("SELECT @@max_user_connections AS `limit`").first['limit'].to_i
    user_count = connection.query("SELECT COUNT(*) AS count FROM information_schema.PROCESSLIST WHERE user = USER()").first['count'].to_i
    global_limit = connection.query("SELECT @@max_connections AS `limit`").first['limit'].to_i
    global_count = connection.query("SELECT COUNT(*) AS count FROM information_schema.PROCESSLIST").first['count'].to_i
    
    # If user limit is 0, it means no specific per-user limit (use global)
    user_limit = global_limit if user_limit == 0
    
    {
      connection_count: { user: user_count, global: global_count },
      connection_limits: { user: user_limit, global: global_limit }
    }
  rescue Mysql2::Error => e
    if e.error_number == 1226 # User has exceeded max_user_connections
      {
        connection_count: { user: "LIMIT EXCEEDED", global: "N/A" },
        connection_limits: { user: "EXCEEDED", global: "N/A" },
        error: "Error: User has exceeded max_user_connections limit"
      }
    else
      nil
    end
  rescue => e
    nil
  end
end

#connection_limit ⇒ Object 



108
109
110
111
 
# File 'lib/detector/addons/mysql.rb', line 108

def connection_limit
  return nil unless connection
  connection.query("SHOW VARIABLES LIKE 'max_connections'").first['Value'].to_i
end

#database_count ⇒ Object 



57
58
59
60
 
# File 'lib/detector/addons/mysql.rb', line 57

def database_count
  return nil unless connection
  @database_count ||= connection.query("SELECT COUNT(*) AS count FROM information_schema.SCHEMATA WHERE schema_name NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys')").first['count']
end

#databases ⇒ Object 



62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
 
# File 'lib/detector/addons/mysql.rb', line 62

def databases
  return [] unless connection
  @databases ||= connection.query("SELECT 
                                schema_name AS name,
                                FORMAT(SUM(data_length + index_length) / 1024 / 1024, 2) AS size_mb,
                                SUM(data_length + index_length) AS raw_size,
                                COUNT(table_name) AS table_count
                                FROM information_schema.SCHEMATA
                                LEFT JOIN information_schema.TABLES ON table_schema = schema_name
                                WHERE schema_name NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys')
                                GROUP BY schema_name
                                ORDER BY raw_size DESC").map do |row|
    { 
      name: row['name'], 
      size: "#{row['size_mb']} MB", 
      raw_size: row['raw_size'].to_i,
      table_count: row['table_count'].to_i
    }
  end
end

#estimated_row_count(table:, database: nil) ⇒ Object 



238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
 
# File 'lib/detector/addons/mysql.rb', line 238

def estimated_row_count(table:, database: nil)
  return nil unless connection
  
  # Use current database if none specified
  db_name = database || info['database']
  return nil unless db_name
  
  begin
    # Query information_schema.tables for the statistics-based row estimate
    result = connection.query("SELECT table_rows AS estimate 
                             FROM information_schema.tables 
                             WHERE table_schema = '#{db_name}' 
                             AND table_name = '#{table}'").first
    
    result ? result['estimate'].to_i : nil
  rescue => e
    nil
  end
end

#info ⇒ Object 



41
42
43
44
 
# File 'lib/detector/addons/mysql.rb', line 41

def info
  return nil unless connection
  @info ||= connection.query("SELECT VERSION() AS version, DATABASE() AS `database`, USER() AS user").first
end

#protocol_type ⇒ Object 



147
148
149
 
# File 'lib/detector/addons/mysql.rb', line 147

def protocol_type
  :tcp
end

#replication_available? ⇒ Boolean

Returns:

  • (Boolean) 


212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
 
# File 'lib/detector/addons/mysql.rb', line 212

def replication_available?
  return nil unless connection
  
  begin
    # Check master status
    master_result = connection.query("SHOW MASTER STATUS")
    return true if master_result.count > 0
    
    # Check if this is a slave
    slave_result = connection.query("SHOW SLAVE STATUS")
    return true if slave_result.count > 0
    
    # Check if replication user exists or if binary logging is enabled
    repl_users = connection.query("SELECT user FROM mysql.user WHERE Repl_slave_priv = 'Y'")
    return true if repl_users.count > 0
    
    # Check if binary logging is enabled (needed for replication)
    binary_log = connection.query("SHOW VARIABLES LIKE 'log_bin'").first
    return true if binary_log && binary_log['Value'] && binary_log['Value'].downcase == 'on'
    
    false
  rescue => e
    nil
  end
end

#table_count(database_name) ⇒ Object 



83
84
85
86
 
# File 'lib/detector/addons/mysql.rb', line 83

def table_count(database_name)
  return nil unless connection
  connection.query("SELECT COUNT(*) AS count FROM information_schema.TABLES WHERE table_schema = '#{database_name}'").first['count']
end

#tables(database_name) ⇒ Object 



88
89
90
91
92
93
94
95
96
97
98
99
100
101
 
# File 'lib/detector/addons/mysql.rb', line 88

def tables(database_name)
  return [] unless connection
  
  @tables ||= {}
  @tables[database_name] ||= connection.query("SELECT table_name AS name, 
                                             FORMAT((data_length + index_length) / 1024 / 1024, 2) AS size_mb,
                                             (data_length + index_length) AS raw_size,
                                             table_rows AS row_count
                                             FROM information_schema.TABLES 
                                             WHERE table_schema = '#{database_name}'
                                             ORDER BY raw_size DESC").map do |row|
    { name: row['name'], size: "#{row['size_mb']} MB", raw_size: row['raw_size'].to_i, row_count: row['row_count'].to_i }
  end
end

#usage ⇒ Object 



51
52
53
54
55
 
# File 'lib/detector/addons/mysql.rb', line 51

def usage
  return nil unless connection && info
  result = connection.query("SELECT ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS size FROM information_schema.TABLES WHERE table_schema = '#{info['database']}'").first
  "#{result['size']} MB"
end

#user_access_level ⇒ Object 



151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
 
# File 'lib/detector/addons/mysql.rb', line 151

def user_access_level
  return nil unless connection
  
  # Get all privileges for current user
  grants = []
  begin
    result = connection.query("SHOW GRANTS FOR CURRENT_USER()")
    result.each do |row|
      grants << row.values.first
    end
  rescue => e
    return "Limited access (unable to check privileges)"
  end
  
  grant_text = grants.join(" ")
  
  # Check for root/admin privileges
  if grant_text =~ /ALL PRIVILEGES ON \*\.\* TO/i
    return "Administrator (all privileges)"
  end
  
  # Check for global privileges
  if grant_text =~ /GRANT .* ON \*\.\*/i
    global_privs = []
    global_privs << "CREATE USER" if grant_text =~ /CREATE USER/i
    global_privs << "PROCESS" if grant_text =~ /PROCESS/i
    global_privs << "SUPER" if grant_text =~ /SUPER/i
    global_privs << "RELOAD" if grant_text =~ /RELOAD/i
    global_privs << "SHUTDOWN" if grant_text =~ /SHUTDOWN/i
    
    if global_privs.include?("CREATE USER") || global_privs.include?("SUPER")
      return "Power user (#{global_privs.join(", ")})"
    elsif !global_privs.empty?
      return "System monitor (#{global_privs.join(", ")})"
    end
  end
  
  # Check for database-level privileges
  db_with_all = []
  if grant_text =~ /ALL PRIVILEGES ON (`[^`]+`|\w+)\./i
    db_name = $1.gsub(/`/, "")
    db_with_all << db_name
  end
  
  if !db_with_all.empty?
    return "Database admin (full access to: #{db_with_all.join(", ")})"
  end
  
  # Check for specific privileges
  can_write = grant_text =~ /INSERT|UPDATE|DELETE|CREATE|ALTER|DROP/i
  can_read = grant_text =~ /SELECT/i
  
  if can_write
    "Write access"
  elsif can_read
    "Read-only access"
  else
    "Limited access"
  end
end

#version ⇒ Object 



46
47
48
49
 
# File 'lib/detector/addons/mysql.rb', line 46

def version
  return nil unless info
  "MySQL #{info['version']} on #{info['database']} (#{info['user']})"
end