Class: Maatkit::DuplicateKeyChecker

Inherits:
Object
  • Object
show all
Defined in:
lib/maatkit-ruby/mk-duplicate-key-checker.rb

Overview

Find duplicate indexes and foreign keys on MySQL tables.

Maatkit::DuplicateKeyChecker.new( array, str, array)

Instance Attribute Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize ⇒ DuplicateKeyChecker

Returns a new DuplicateKeyChecker Object



182
183
 
# File 'lib/maatkit-ruby/mk-duplicate-key-checker.rb', line 182

def initialize()
end

Instance Attribute Details

#all_structs ⇒ Object

Compare indexes with different structs (BTREE, HASH, etc). By default this is disabled, because a BTREE index that covers the same columns as a FULLTEXT index is not really a duplicate, for example.



19
20
21
 
# File 'lib/maatkit-ruby/mk-duplicate-key-checker.rb', line 19

def all_structs
  @all_structs
end

#ask_pass ⇒ Object

Prompt for a password when connecting to MySQL.



23
24
25
 
# File 'lib/maatkit-ruby/mk-duplicate-key-checker.rb', line 23

def ask_pass
  @ask_pass
end

#charset ⇒ Object

short form: -A; type: string Default character set. If the value is utf8, sets Perl’s binmode on STDOUT to utf8, passes the mysql_enable_utf8 option to DBD::mysql, and runs SET NAMES UTF8 after connecting to MySQL. Any other value sets binmode on STDOUT without the utf8 layer, and runs SET NAMES after connecting to MySQL.



30
31
32
 
# File 'lib/maatkit-ruby/mk-duplicate-key-checker.rb', line 30

def charset
  @charset
end

#clustered ⇒ Object

default: yes PK columns appended to secondary key is duplicate. Detects when a suffix of a secondary key is a leftmost prefix of the primary key, and treats it as a duplicate key. Only detects this condition on storage engines whose primary keys are clustered (currently InnoDB and solidDB). Clustered storage engines append the primary key columns to the leaf nodes of all secondary keys anyway, so you might consider it redundant to have them appear in the internal nodes as well. Of course, you may also want them in the internal nodes, because just having them at the leaf nodes won’t help for some queries. It does help for covering index queries, however. Here’s an example of a key that is considered redundant with this option:

PRIMARY KEY  (`a`)
KEY `b` (`b`,`a`)

The use of such indexes is rather subtle. For example, suppose you have the following query:

SELECT ... WHERE b=1 ORDER BY a;

This query will do a filesort if we remove the index on “b,a”. But if we shorten the index on “b,a” to just “b” and also remove the ORDER BY, the query should return the same results. The tool suggests shortening duplicate clustered keys by dropping the key and re-adding it without the primary key prefix. The shortened clustered key may still duplicate another key, but the tool cannot currently detect when this happens without being ran a second time to re-check the newly shortened clustered keys. Therefore, if you shorten any duplicate clusterted keys, you should run the tool again.



54
55
56
 
# File 'lib/maatkit-ruby/mk-duplicate-key-checker.rb', line 54

def clustered
  @clustered
end

#config ⇒ Object

type: Array Read this comma-separated list of config files; if specified, this must be the first option on the command line.



60
61
62
 
# File 'lib/maatkit-ruby/mk-duplicate-key-checker.rb', line 60

def config
  @config
end

#databases ⇒ Object

short form: -d; type: hash Check only this comma-separated list of databases.



65
66
67
 
# File 'lib/maatkit-ruby/mk-duplicate-key-checker.rb', line 65

def databases
  @databases
end

#defaults_file ⇒ Object

short form: -F; type: string Only read mysql options from the given file. You must give an absolute pathname.



70
71
72
 
# File 'lib/maatkit-ruby/mk-duplicate-key-checker.rb', line 70

def defaults_file
  @defaults_file
end

#engines ⇒ Object

short form: -e; type: hash Check only tables whose storage engine is in this comma-separated list.



75
76
77
 
# File 'lib/maatkit-ruby/mk-duplicate-key-checker.rb', line 75

def engines
  @engines
end

#help ⇒ Object

Show help and exit.



79
80
81
 
# File 'lib/maatkit-ruby/mk-duplicate-key-checker.rb', line 79

def help
  @help
end

#host ⇒ Object

short form: -h; type: string Connect to host.



84
85
86
 
# File 'lib/maatkit-ruby/mk-duplicate-key-checker.rb', line 84

def host
  @host
end

#ignore_databases ⇒ Object

type: Hash Ignore this comma-separated list of databases.



89
90
91
 
# File 'lib/maatkit-ruby/mk-duplicate-key-checker.rb', line 89

def ignore_databases
  @ignore_databases
end

#ignore_engines ⇒ Object

type: Hash Ignore this comma-separated list of storage engines.



94
95
96
 
# File 'lib/maatkit-ruby/mk-duplicate-key-checker.rb', line 94

def ignore_engines
  @ignore_engines
end

#ignore_order ⇒ Object

Ignore index order so KEY(a,b) duplicates KEY(b,a).



98
99
100
 
# File 'lib/maatkit-ruby/mk-duplicate-key-checker.rb', line 98

def ignore_order
  @ignore_order
end

#ignore_tables ⇒ Object

type: Hash Ignore this comma-separated list of tables. Table names may be qualified with the database name.



103
104
105
 
# File 'lib/maatkit-ruby/mk-duplicate-key-checker.rb', line 103

def ignore_tables
  @ignore_tables
end

#key_types ⇒ Object

type: string; default: fk Check for duplicate f=foreign keys, k=keys or fk=both.



108
109
110
 
# File 'lib/maatkit-ruby/mk-duplicate-key-checker.rb', line 108

def key_types
  @key_types
end

#password ⇒ Object

short form: -p; type: string Password to use when connecting.



113
114
115
 
# File 'lib/maatkit-ruby/mk-duplicate-key-checker.rb', line 113

def password
  @password
end

#path_to_mk_duplicate_key_checker ⇒ Object

Sets the executable path, otherwise the environment path will be used.



177
178
179
 
# File 'lib/maatkit-ruby/mk-duplicate-key-checker.rb', line 177

def path_to_mk_duplicate_key_checker
  @path_to_mk_duplicate_key_checker
end

#pid ⇒ Object

type: string Create the given PID file. The file contains the process ID of the script. The PID file is removed when the script exits. Before starting, the script checks if the PID file already exists. If it does not, then the script creates and writes its own PID to it. If it does, then the script checks the following: if the file contains a PID and a process is running with that PID, then the script dies; or, if there is no process running with that PID, then the script overwrites the file with its own PID and starts; else, if the file contains no PID, then the script dies.



123
124
125
 
# File 'lib/maatkit-ruby/mk-duplicate-key-checker.rb', line 123

def pid
  @pid
end

#port ⇒ Object

short form: -P; type: int Port number to use for connection.



128
129
130
 
# File 'lib/maatkit-ruby/mk-duplicate-key-checker.rb', line 128

def port
  @port
end

#set_vars ⇒ Object

type: string; default: wait_timeout=10000 Set these MySQL variables. Immediately after connecting to MySQL, this string will be appended to SET and executed.



134
135
136
 
# File 'lib/maatkit-ruby/mk-duplicate-key-checker.rb', line 134

def set_vars
  @set_vars
end

#socket ⇒ Object

short form: -S; type: string Socket file to use for connection.



139
140
141
 
# File 'lib/maatkit-ruby/mk-duplicate-key-checker.rb', line 139

def socket
  @socket
end

#sql ⇒ Object

default: yes Print DROP KEY statement for each duplicate key. By default an ALTER TABLE DROP KEY statement is printed below each duplicate key so that, if you want to remove the duplicate key, you can copy-paste the statement into MySQL. To disable printing these statements, specify –nosql.



147
148
149
 
# File 'lib/maatkit-ruby/mk-duplicate-key-checker.rb', line 147

def sql
  @sql
end

#summary ⇒ Object

default: yes Print summary of indexes at end of output.



152
153
154
 
# File 'lib/maatkit-ruby/mk-duplicate-key-checker.rb', line 152

def summary
  @summary
end

#tables ⇒ Object

short form: -t; type: hash Check only this comma-separated list of tables. Table names may be qualified with the database name.



158
159
160
 
# File 'lib/maatkit-ruby/mk-duplicate-key-checker.rb', line 158

def tables
  @tables
end

#user ⇒ Object

short form: -u; type: string User for login if not current user.



163
164
165
 
# File 'lib/maatkit-ruby/mk-duplicate-key-checker.rb', line 163

def user
  @user
end

#verbose ⇒ Object

short form: -v Output all keys and/or foreign keys found, not just redundant ones.



168
169
170
 
# File 'lib/maatkit-ruby/mk-duplicate-key-checker.rb', line 168

def verbose
  @verbose
end

#version ⇒ Object

Show version and exit.



172
173
174
 
# File 'lib/maatkit-ruby/mk-duplicate-key-checker.rb', line 172

def version
  @version
end

Instance Method Details

#start(options = nil) ⇒ Object

Execute the command



188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
 
# File 'lib/maatkit-ruby/mk-duplicate-key-checker.rb', line 188

def start(options = nil)
  tmp = Tempfile.new('tmp')
  command = option_string() + options.to_s + " 2> " + tmp.path
  success = system(command)
  if success
    begin
      while (line = tmp.readline)
        line.chomp
        selected_string = line
      end
    rescue EOFError
      tmp.close
    end
    return selected_string
  else
    tmp.close!
    return success
  end
end