OnlineDDL/Helper/MySQL.pm

filter: Statementstmt 15.4% 33 / 214 Branchbran 0.0% 0 / 40 Conditioncond 0.0% 0 / 38 MC/DCmcdc 0.0% 0 / 19 Subroutinesub 23.6% 9 / 38 Podpod 100% 18 / 18 totaltotal 16.3% 60 / 367 CC 39 SCAR 38.9
CC
39
Cov
11%
post_fk_add_cleanup_stmts
156.0
add_triggers_back_to_table
90.0
foreign_key_info
90.0
CRAP 48.9
« OnlineDDL.pm ↑ Summary OnlineDDL/Helper/Base.pm »
1 package DBIx::OnlineDDL::Helper::MySQL;
2  
3 1 our $AUTHORITY = 'cpan:GSG';
4 # ABSTRACT: Private OnlineDDL helper for MySQL-specific code
5 1 ▶use version;
Subroutine BEGIN: called CC=1 SCAR=0.0
6 1 our $VERSION = 'v1.1.2'; # VERSION
7  
8 1 ▶use v5.10;
Subroutine BEGIN: called CC=1 SCAR=0.0
9 1 ▶use Moo;
Subroutine BEGIN: called CC=1 SCAR=0.0
10  
11 1 extends 'DBIx::OnlineDDL::Helper::Base';
12  
13 1 ▶use Types::Standard qw( Bool );
Subroutine BEGIN: called CC=1 SCAR=0.0
14  
15 1 ▶use DBI::Const::GetInfoType;
Subroutine BEGIN: called CC=1 SCAR=0.0
16 1 ▶use DBIx::ParseError::MySQL;
Subroutine BEGIN: called CC=1 SCAR=0.0
17 1 ▶use List::Util qw( first );
Subroutine BEGIN: called CC=1 SCAR=0.0
18 1 ▶use Sub::Util qw( set_subname );
Subroutine BEGIN: called CC=1 SCAR=0.0
19  
20 1 ▶use namespace::clean; # don't export the above
Subroutine BEGIN: called CC=1 SCAR=0.0
21  
22 #pod =encoding utf8
23 #pod
24 #pod =head1 DESCRIPTION
25 #pod
26 #pod This is a private helper module for any MySQL-specific code. B<As a private module, any
27 #pod methods or attributes here are subject to change.>
28 #pod
29 #pod You should really be reading documentation for L<DBIx::OnlineDDL>. Or, if you want to
30 #pod create a helper module for a different RDBMS, read the docs for
31 #pod L<DBIx::OnlineDDL::Helper::Base>.
32 #pod
33 #pod =cut
34  
35 0 ▶sub dbms_uses_global_fk_namespace { 1 }
Subroutine dbms_uses_global_fk_namespace: not called CC=1 SCAR=6.9
Pod documented
36 0 ▶sub child_fks_need_adjusting { 1 }
Subroutine child_fks_need_adjusting: not called CC=1 SCAR=6.9
Pod documented
37 0 ▶sub null_safe_equals_op { '<=>' }
Subroutine null_safe_equals_op: not called CC=1 SCAR=6.9
Pod documented
38  
39 0 ▶sub current_catalog_schema {
Subroutine current_catalog_schema: not called CC=1 SCAR=6.9
Pod documented
40 0 my $self = shift;
41  
42 0 my ($schema) = $self->dbh->selectrow_array('SELECT DATABASE()');
43 0 return (undef, $schema);
44 }
45  
46 1 has is_galera_cluster => (
47 is => 'lazy',
48 isa => Bool,
49 );
50  
51 0 ▶sub _build_is_galera_cluster {
Subroutine _build_is_galera_cluster: not called CC=3 SCAR=24.8
52 0 my $self = shift;
53  
54 0 my ($name, $val) = $self->dbh->selectrow_array("SHOW GLOBAL STATUS LIKE 'wsrep_cluster_size'");
55 0 ▶ return $val && int($val) && $val > 1;
Condition0%0/6
$val && int $val && $val > 1
!ll&&!rl&&r
000
$val and int $val
!ll&&!rl&&r
000
MC/DC0%0/3
$val && int $val && $val > 1
$val int $val $val > 1
Truth table0%0/4
A and B && C
A= $valB= int $valC= $val > 1
ABCresult
0XX0
10X0
1100
1111
56 }
57  
58 0 ▶sub insert_select_stmt {
Subroutine insert_select_stmt: not called CC=1 SCAR=6.9
Pod documented
59 0 my ($self, $column_list_str) = @_;
60  
61 0 my $dbh = $self->dbh;
62  
63 0 my $id_name_quote = $dbh->quote_identifier( $self->copy_opts->{id_name} );
64  
65 0 my $orig_table_name_quote = $dbh->quote_identifier($self->table_name);
66 0 my $new_table_name_quote = $dbh->quote_identifier($self->new_table_name);
67  
68 # Use INSERT IGNORE to ignore dupe key errors. The LOCK IN SHARE MODE write-locks
69 # the source rows until they are copied. If anything needs to make any changes after
70 # that, the triggers will cover those.
71 0 return join("\n",
72 "INSERT IGNORE INTO $new_table_name_quote",
73 " ($column_list_str)",
74 "SELECT",
75 " $column_list_str",
76 "FROM $orig_table_name_quote",
77 "WHERE $id_name_quote BETWEEN ? AND ?",
78 "LOCK IN SHARE MODE"
79 );
80 }
81  
82 0 ▶sub post_connection_stmts {
Subroutine post_connection_stmts: not called CC=1 SCAR=6.9
Pod documented
83 0 my $self = shift;
84  
85 # Initialize these cached attributes
86 0 $self->mmver;
87 0 $self->is_galera_cluster;
88  
89 0 my $db_timeouts = $self->db_timeouts;
90 return (
91 # Use the right database, just in case it's not in the DSN.
92 "USE ".$self->dbh->quote_identifier($self->vars->{schema}),
93  
94 # Foreign key constraints should not interrupt the process. Nor should they be
95 # checked when trying to add or remove them. This would cause a simple FK DDL
96 # to turn into a long-running operation on pre-existing tables.
97 'SET SESSION foreign_key_checks=0',
98  
99 # DB timeouts
100 'SET SESSION wait_timeout='.$db_timeouts->{session},
101 'SET SESSION lock_wait_timeout='.$db_timeouts->{lock_db},
102 'SET SESSION innodb_lock_wait_timeout='.$db_timeouts->{lock_row},
103 0 );
104 }
105  
106 0 ▶sub is_error_retryable {
Subroutine is_error_retryable: not called CC=1 SCAR=6.9
Pod documented
107 0 my ($self, $error) = @_;
108 0 return DBIx::ParseError::MySQL->new($error)->is_transient;
109 }
110  
111 0 ▶sub create_table_sql {
Subroutine create_table_sql: not called CC=1 SCAR=6.9
Pod documented
112 0 my ($self, $table_name) = @_;
113  
114 0 my $table_name_quote = $self->dbh->quote_identifier($table_name);
115  
116 0 ▶ return $self->dbh_runner(run => set_subname '_create_table_sql', sub {
Subroutine __ANON__: not called CC=1 SCAR=6.9
117 # SHOW CREATE TABLE reports two columns, so might as well pull it out by name
118 0 local $_->{FetchHashKeyName} = 'NAME_uc';
119 0 $_->selectrow_hashref("SHOW CREATE TABLE $table_name_quote")->{'CREATE TABLE'};
120 0 });
121 }
122  
123 0 ▶sub rename_fks_in_table_sql {
Subroutine rename_fks_in_table_sql: not called CC=3 SCAR=24.8
Pod documented
124 0 my ($self, $table_name, $table_sql) = @_;
125  
126 0 my $dbh = $self->dbh;
127  
128 # Since MySQL uses a global namespace for foreign keys, these will have to be renamed
129 0 ▶ my $iq_chars = $dbh->get_info( $GetInfoType{SQL_IDENTIFIER_QUOTE_CHAR} ) || '`';
Condition0%0/2
$dbh->get_info($DBIx::OnlineDDL::Helper::MySQL::GetInfoType{'SQL_IDENTIFIER_QUOTE_CHAR'}) || '`'
l!l
00
MC/DC0%0/1
$dbh->get_info($DBIx::OnlineDDL::Helper::MySQL::GetInfoType{'SQL_IDENTIFIER_QUOTE_CHAR'}) || '`'
$dbh->get_info($DBIx::OnlineDDL::Helper::MySQL::GetInfoType{'SQL_IDENTIFIER_QUOTE_CHAR'})
Truth table0%0/2
A
A= $dbh->get_info($DBIx::OnlineDDL::Helper::MySQL::GetInfoType{'SQL_IDENTIFIER_QUOTE_CHAR'})
Aresult
11
00
130 0 $iq_chars .= '"'; # include ANSI quotes
131  
132 0 my $iqre = '['.quotemeta($iq_chars).']';
133 0 my $noniq_re = '[^'.quotemeta($iq_chars).'\s]';
134  
135 0 my @fk_names = ($table_sql =~ /CONSTRAINT ${iqre}(${noniq_re}+)${iqre} FOREIGN KEY/ig);
136  
137 0 foreach my $fk_name (@fk_names) {
138 my $new_fk_name = $self->find_new_identifier(
139 0 ▶ "_${fk_name}" => set_subname '_fk_name_finder', sub {
Subroutine __ANON__: not called CC=1 SCAR=6.9
140 0 $_[0]->selectrow_array(
141 'SELECT table_name FROM information_schema.key_column_usage WHERE '.join(' AND ',
142 'constraint_schema = DATABASE()',
143 'constraint_name = ?',
144 # This is required for MySQL 5 server table cache optimization in the EXPLAIN plan
145 'table_schema = DATABASE()',
146 ), undef, $_[1]
147 );
148 },
149 0 );
150 0 $self->vars->{foreign_keys}{orig_names}{"$table_name.$new_fk_name"} = $fk_name;
151  
152 0 my $fk_name_re = quotemeta $fk_name;
153 0 $table_sql =~ s/(?<=CONSTRAINT ${iqre})$fk_name_re(?=${iqre} FOREIGN KEY)/$new_fk_name/;
154 }
155  
156 0 return $table_sql;
157 }
158  
159 0 ▶sub has_conflicting_triggers_on_table {
Subroutine has_conflicting_triggers_on_table: not called CC=2 SCAR=17.9
Pod documented
160 0 my ($self, $table_name) = @_;
161 0 my $mmver = $self->mmver;
162  
163 # Multiple triggers aren't allowed in MySQL 5.6
164 0 ▶ if ($mmver < 5.007) {
Branch: 1 branch
BranchTrueFalseTotal
if ($mmver < 5.007) { } 0 0 0
165 0 ▶ return $self->dbh_runner(run => set_subname '_has_triggers_on_table', sub {
Subroutine __ANON__: not called CC=1 SCAR=6.9
166 0 $_->selectrow_array(
167 'SELECT trigger_name FROM information_schema.triggers WHERE event_object_schema = DATABASE() AND event_object_table = ?',
168 undef, $table_name
169 );
170 0 });
171 }
172 # MySQL 5.7+ allows them, so look for anything that looks like a leftover OnlineDDL
173 # trigger name.
174 else {
175 0 ▶ return $self->dbh_runner(run => set_subname '_has_onlineddl_triggers_on_table', sub {
Subroutine __ANON__: not called CC=1 SCAR=6.9
176 0 $_->selectrow_array(
177 'SELECT trigger_name FROM information_schema.triggers WHERE '.join(' AND ',
178 'event_object_schema = DATABASE()',
179 'event_object_table = ?',
180 'trigger_name LIKE ?',
181 ), undef, $table_name, "\%${table_name}\\_onlineddl\\_\%"
182 );
183 0 });
184 }
185 }
186  
187 0 ▶sub has_triggers_on_table_to_be_copied {
Subroutine has_triggers_on_table_to_be_copied: not called CC=2 SCAR=17.9
Pod documented
188 0 my ($self, $table_name) = @_;
189 0 my $mmver = $self->mmver;
190 # Multiple triggers aren't allowed on MySQL 5.6, so there's nothing to copy
191 0 ▶ return 0 if $mmver < 5.007;
Branch: 1 branch
BranchTrueFalseTotal
if $mmver < "5.007" 0 0 0
192  
193 # Look for anything that isn't a leftover OnlineDDL trigger name.
194 0 ▶ return $self->dbh_runner(run => set_subname '_has_non_onlineddl_triggers_on_table', sub {
Subroutine __ANON__: not called CC=1 SCAR=6.9
195 0 $_->selectrow_array(
196 'SELECT trigger_name FROM information_schema.triggers WHERE '.join(' AND ',
197 'event_object_schema = DATABASE()',
198 'event_object_table = ?',
199 'trigger_name NOT LIKE ?',
200 ), undef, $table_name, "\%${table_name}\\_onlineddl\\_\%"
201 );
202 0 });
203 }
204  
205 0 ▶sub find_new_trigger_identifier {
Subroutine find_new_trigger_identifier: not called CC=1 SCAR=6.9
Pod documented
206 0 my ($self, $trigger_name) = @_;
207  
208 return $self->find_new_identifier(
209 0 ▶ $trigger_name => sub {
Subroutine __ANON__: not called CC=1 SCAR=6.9
210 0 $_[0]->selectrow_array(
211 'SELECT trigger_name FROM information_schema.triggers WHERE event_object_schema = DATABASE() AND trigger_name = ?',
212 undef, $_[1]
213 );
214 },
215 0 );
216 }
217  
218 0 ▶sub modify_trigger_dml_stmts {
Subroutine modify_trigger_dml_stmts: not called CC=1 SCAR=6.9
Pod documented
219 0 my ($self, $stmts) = @_;
220  
221 # Ignore errors
222 0 $stmts->{delete_for_update} =~ s/^DELETE/DELETE IGNORE/;
223 0 $stmts->{delete_for_delete} =~ s/^DELETE/DELETE IGNORE/;
224 }
225  
226 # Keep Base->analyze_table
227  
228 0 ▶sub swap_tables {
Subroutine swap_tables: not called CC=3 SCAR=24.8
Pod documented
229 0 my ($self, $new_table_name, $orig_table_name, $old_table_name) = @_;
230 0 my $dbh = $self->dbh;
231 0 my $mmver = $self->mmver;
232  
233 # Galera MySQL 8 has the potential to enter a bad MDL BF-BF conflict state, if the
234 # cluster is under enough load to make synchronous replication fall a bit behind and
235 # a RENAME TABLE statement is ran. This workaround makes a broad assumption that the
236 # bug is some sort of race condition between the statements that make updates to the
237 # table, and the RENAME TABLE statement. Thus, we give replication a chance to catch
238 # up first before we execute the RENAME TABLE statement.
239 #
240 # More info: https://perconadev.atlassian.net/browse/PXC-4512
241  
242 0 ▶ if ($mmver >= 8 && $self->is_galera_cluster) {
Branch: 1 branch
BranchTrueFalseTotal
if ($mmver >= 8 and $self->is_galera_cluster) 0 0 0
Condition0%0/3
$mmver >= 8 and $self->is_galera_cluster
!ll&&!rl&&r
000
MC/DC0%0/2
$mmver >= 8 and $self->is_galera_cluster
$mmver >= 8 $self->is_galera_cluster
Truth table0%0/3
A and B
A= $mmver >= 8B= $self->is_galera_cluster
ABresult
0X0
100
111
243 0 $self->progress->message(" Sleeping for 5 seconds");
244 0 sleep 5;
245 }
246  
247 0 my $new_table_name_quote = $dbh->quote_identifier($new_table_name);
248 0 my $orig_table_name_quote = $dbh->quote_identifier($orig_table_name);
249 0 my $old_table_name_quote = $dbh->quote_identifier($old_table_name);
250  
251 0 $self->dbh_runner_do(
252 "RENAME TABLE $orig_table_name_quote TO $old_table_name_quote, $new_table_name_quote TO $orig_table_name_quote"
253 );
254 }
255  
256 0 ▶sub get_trigger_data {
Subroutine get_trigger_data: not called CC=2 SCAR=17.9
Pod documented
257 0 my ($self, $table_name) = @_;
258 0 my $mmver = $self->mmver;
259 # Multiple triggers aren't allowed on MySQL 5.6, so there's nothing to copy
260 0 ▶ return 0 if $mmver < 5.007;
Branch: 1 branch
BranchTrueFalseTotal
if $mmver < "5.007" 0 0 0
261  
262 # Look for anything that isn't a leftover OnlineDDL trigger name.
263 0 ▶ $self->dbh_runner(run => set_subname '_non_onlineddl_triggers_on_table', sub {
Subroutine __ANON__: not called CC=1 SCAR=6.9
264 0 local $_->{FetchHashKeyName} = 'NAME_lc';
265 0 $self->vars->{existing_triggers} = $_->selectall_hashref(
266 'SELECT * FROM information_schema.triggers WHERE '.join(' AND ',
267 'event_object_schema = DATABASE()',
268 'event_object_table = ?',
269 'trigger_name NOT LIKE ?',
270 ), 'trigger_name', undef, $table_name, "\%${table_name}\\_onlineddl\\_\%"
271 );
272 0 });
273 }
274  
275 0 ▶sub add_triggers_back_to_table {
Subroutine add_triggers_back_to_table: not called CC=9 SCAR=45.0
Pod documented
276 0 my ($self, $table_name) = @_;
277 0 my $dbh = $self->dbh;
278  
279 0 ▶ my $triggers = $self->vars->{existing_triggers} // return;
Condition0%0/2
$self->vars->{"existing_triggers"} // (return)
l!l
00
MC/DC0%0/1
$self->vars->{"existing_triggers"} // (return)
$self->vars->{"existing_triggers"}
Truth table0%0/2
A
A= $self->vars->{"existing_triggers"}
Aresult
11
00
280  
281 0 my $table_name_quote = $dbh->quote_identifier($table_name);
282  
283 # The action_statement stores SQL text with escaping rules from the original session
284 # context (eg backslash escaping in string literals depends on sql_mode). We need to
285 # save/restore these MySQL-specific session variables around each trigger re-creation.
286 0 my @trigger_context_vars = qw< character_set_client collation_connection sql_mode >;
287  
288 0 my @stmts;
289 0 foreach my $trigger_name (
290 sort {
291 ( $triggers->{$a}{action_timing} cmp $triggers->{$b}{action_timing} ) ||
292 ( $triggers->{$a}{event_manipulation} cmp $triggers->{$b}{event_manipulation} ) ||
293 0 ▶ (($triggers->{$a}{action_order} // 0) <=> ($triggers->{$b}{action_order} // 0)) ||
Condition0%0/13
$triggers->{$a}{'action_timing'} cmp $triggers->{$b}{'action_timing'} || $triggers->{$a}{'event_manipulation'} cmp $triggers->{$b}{'event_manipulation'} || ($triggers->{$a}{'action_order'} // 0) <=> ($triggers->{$b}{'action_order'} // 0) || $a cmp $b
l!l&&r!l&&!r
000
$triggers->{$a}{"action_timing"} cmp $triggers->{$b}{"action_timing"} or $triggers->{$a}{"event_manipulation"} cmp $triggers->{$b}{"event_manipulation"} or ($triggers->{$a}{"action_order"} // 0) <=> ($triggers->{$b}{"action_order"} // 0)
l!l&&r!l&&!r
000
$triggers->{$a}{"action_timing"} cmp $triggers->{$b}{"action_timing"} or $triggers->{$a}{"event_manipulation"} cmp $triggers->{$b}{"event_manipulation"}
l!l&&r!l&&!r
000
$triggers->{$a}{"action_order"} // 0
l!l
00
$triggers->{$b}{"action_order"} // 0
l!l
00
MC/DC0%0/4
$triggers->{$a}{'action_timing'} cmp $triggers->{$b}{'action_timing'} || $triggers->{$a}{'event_manipulation'} cmp $triggers->{$b}{'event_manipulation'} || ($triggers->{$a}{'action_order'} // 0) <=> ($triggers->{$b}{'action_order'} // 0) || $a cmp $b
$triggers->{$a}{"action_timing"} cmp $triggers->{$b}{"action_timing"} $triggers->{$a}{"event_manipulation"} cmp $triggers->{$b}{"event_manipulation"} $triggers->{$a}{"action_order"} $a cmp $b
MC/DC0%0/1
$triggers->{$b}{"action_order"} // 0
$triggers->{$b}{"action_order"}
Truth table0%0/5
A or B or C || D
A= $triggers->{$a}{"action_timing"} cmp $triggers->{$b}{"action_timing"}B= $triggers->{$a}{"event_manipulation"} cmp $triggers->{$b}{"event_manipulation"}C= $triggers->{$a}{"action_order"}D= $a cmp $b
ABCDresult
1XXX1
01XX1
001X1
00011
00000
Truth table0%0/2
A
A= $triggers->{$b}{"action_order"}
Aresult
11
00
294 ($a cmp $b)
295 }
296 keys %$triggers
297 ) {
298 0 my $trigger = $triggers->{$trigger_name};
299 0 my @active_vars = grep { defined $trigger->{$_} } @trigger_context_vars;
300  
301 # Save then set the trigger's original session context
302 0 push @stmts, map { "SET \@_oddl_$_ = \@\@SESSION.$_" } @active_vars;
303 0 push @stmts, map { "SET SESSION $_ = ".$dbh->quote($trigger->{$_}) } @active_vars;
304  
305 0 my $trigger_name_quote = $dbh->quote_identifier($trigger_name);
306 0 push @stmts, "DROP TRIGGER IF EXISTS $trigger_name_quote";
307  
308 0 my $sql = 'CREATE ';
309 0 ▶ if ($trigger->{definer}) {
Branch: 1 branch
BranchTrueFalseTotal
if ($trigger->{"definer"}) 0 0 0
310 my $definer_quote =
311 join '@',
312 0 map { $dbh->quote_identifier($_) }
313 0 split(/\@/, $trigger->{definer}, 2)
314 ;
315 0 $sql .= "DEFINER = $definer_quote ";
316 }
317 0 $sql .= "TRIGGER $trigger_name_quote ";
318 $sql .= join(' ',
319 $trigger->{action_timing},
320 $trigger->{event_manipulation},
321 'ON', $table_name_quote,
322 'FOR EACH', $trigger->{action_orientation},
323 0 );
324 0 $sql .= "\n".$trigger->{action_statement};
325  
326 0 push @stmts, $sql;
327  
328 # Restore session context (reverse order)
329 0 push @stmts, map { "SET SESSION $_ = \@_oddl_$_" } reverse @active_vars;
330 }
331  
332 0 return @stmts;
333 }
334  
335 ### NOTE: The typical SQL in DBD::mysql is badly optimized for MySQL 5 and very large sets
336 ### of databases/table/column combos. Furthermore, the kludgy join to TABLE_CONSTRAINTS
337 ### is entirely unnecessary. See also: https://github.com/perl5-dbi/DBD-mysql/issues/326
338 0 ▶sub foreign_key_info {
Subroutine foreign_key_info: not called CC=9 SCAR=45.0
Pod documented
339 0 my $self = shift;
340 0 my $dbh = $self->dbh;
341 0 my $mmver = $self->mmver;
342  
343 # MySQL 8's information_schema implementation isn't a complete dumpster fire of
344 # in-memory jank, and totes won't send your precious DB server into a horrible
345 # OOM death. So, skip this noise in that case.
346 #
347 # More info: https://mysqlserverteam.com/mysql-8-0-improvements-to-information_schema/
348 0 ▶ return $dbh->foreign_key_info(@_) if $mmver >= 8;
Branch: 1 branch
BranchTrueFalseTotal
if $mmver >= 8 0 0 0
349 0 ▶ return if $mmver < 5; # not supported by OnlineDDL, anyway
Branch: 1 branch
BranchTrueFalseTotal
if $mmver < 5 0 0 0
350  
351 my (
352 0 $pk_catalog, $pk_schema, $pk_table,
353 $fk_catalog, $fk_schema, $fk_table,
354 ) = @_;
355  
356 0 my $sql = <<'EOF';
357 SELECT
358 NULL AS PKTABLE_CAT,
359 REFERENCED_TABLE_SCHEMA AS PKTABLE_SCHEM,
360 REFERENCED_TABLE_NAME AS PKTABLE_NAME,
361 REFERENCED_COLUMN_NAME AS PKCOLUMN_NAME,
362 TABLE_CATALOG AS FKTABLE_CAT,
363 TABLE_SCHEMA AS FKTABLE_SCHEM,
364 TABLE_NAME AS FKTABLE_NAME,
365 COLUMN_NAME AS FKCOLUMN_NAME,
366 ORDINAL_POSITION AS KEY_SEQ,
367 NULL AS UPDATE_RULE,
368 NULL AS DELETE_RULE,
369 CONSTRAINT_NAME AS FK_NAME,
370 NULL AS PK_NAME,
371 NULL AS DEFERABILITY,
372 NULL AS UNIQUE_OR_PRIMARY
373 FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
374 EOF
375  
376 ### XXX: This breaks OnlineDDL's re-applying of cross-database FKs in foreign child
377 ### tables. But, without this fix, MySQL has to scan all databases to find them.
378 ### Again, this is no longer a problem with MySQL 8.
379 0 ▶ $fk_schema //= $pk_schema;
Condition0%0/3
$fk_schema //= $pk_schema
l!l&&r!l&&!r
000
MC/DC0%0/2
$fk_schema //= $pk_schema
$fk_schema $pk_schema
Truth table0%0/3
A //= B
A= $fk_schemaB= $pk_schema
ABresult
1X1
011
000
380  
381 0 my @where;
382 my @bind;
383  
384 0 ▶ if (defined $pk_schema) {
Branch: 1 branch
BranchTrueFalseTotal
if (defined $pk_schema) 0 0 0
385 0 push @where, 'REFERENCED_TABLE_SCHEMA = ?';
386 0 push @bind, $pk_schema;
387 }
388  
389 0 ▶ if (defined $pk_table) {
Branch: 1 branch
BranchTrueFalseTotal
if (defined $pk_table) 0 0 0
390 0 push @where, 'REFERENCED_TABLE_NAME = ?';
391 0 push @bind, $pk_table;
392 }
393  
394 0 ▶ if (defined $fk_schema) {
Branch: 1 branch
BranchTrueFalseTotal
if (defined $fk_schema) 0 0 0
395 0 push @where, 'TABLE_SCHEMA = ?';
396 0 push @bind, $fk_schema;
397 }
398  
399 0 ▶ if (defined $fk_table) {
Branch: 1 branch
BranchTrueFalseTotal
if (defined $fk_table) 0 0 0
400 0 push @where, 'TABLE_NAME = ?';
401 0 push @bind, $fk_table;
402 }
403  
404 0 ▶ if (@where) {
Branch: 1 branch
BranchTrueFalseTotal
if (@where) 0 0 0
405 0 $sql .= "\nWHERE ";
406 0 $sql .= join ' AND ', @where;
407 }
408 0 $sql .= "\nORDER BY TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION";
409  
410 # NOTE: This FetchHashKeyName setting is consistent with how DBI's *_info methods column names work.
411 0 local $dbh->{FetchHashKeyName} = 'NAME_uc';
412 0 my $sth = $dbh->prepare($sql);
413 0 $sth->execute(@bind);
414  
415 0 return $sth;
416 }
417  
418 # MySQL uses 'FOREIGN KEY' on DROPs, for some reason
419 0 ▶around 'remove_fks_from_child_tables_stmts' => sub {
Subroutine __ANON__: not called CC=1 SCAR=6.9
420 0 my $orig = shift;
421 0 my @stmts = $orig->(@_);
422  
423 0 return map { s/ DROP CONSTRAINT / DROP FOREIGN KEY /; $_ } @stmts;
424 1 };
425  
426 0 ▶around 'rename_fks_back_to_original_stmts' => sub {
Subroutine __ANON__: not called CC=1 SCAR=6.9
427 0 my $orig = shift;
428 0 my @stmts = $orig->(@_);
429  
430 0 return map { s/ DROP CONSTRAINT / DROP FOREIGN KEY /; $_ } @stmts;
431 1 };
432  
433 # Keep Base->add_fks_back_to_child_tables_stmts (no DROPs on those)
434  
435 # Look for default FK-created indexes that get mysteriously renamed after the FKs are
436 # recreated. (SM-3039)
437 0 ▶sub post_fk_add_cleanup_stmts {
Subroutine post_fk_add_cleanup_stmts: not called CC=12 SCAR=50.5
Pod documented
438 0 my $self = shift;
439 0 my $dbh = $self->dbh;
440 0 my $mmver = $self->mmver;
441 0 my $vars = $self->vars;
442 0 my $catalog = $vars->{catalog};
443 0 my $schema = $vars->{schema};
444 0 my $idx_hash = $vars->{indexes}{definitions};
445  
446 0 my @stmts;
447 0 foreach my $table_name (sort keys %$idx_hash) {
448 0 my %old_idx_data = %{ $idx_hash->{$table_name} };
449 0 my %new_idx_data = %{ $self->get_idx_hash($table_name) };
450  
451 0 foreach my $index_name (sort keys %old_idx_data) {
452 0 ▶ next if $index_name eq 'PRIMARY';
Branch: 1 branch
BranchTrueFalseTotal
if $index_name eq "PRIMARY" 0 0 0
453  
454 0 my $old_idx = $old_idx_data{$index_name};
455 0 my $new_idx = $new_idx_data{$index_name};
456 0 my $old_col_str = join ', ', @{$old_idx->{columns}};
457  
458 next if $new_idx &&
459 0 $old_col_str eq join(', ', @{$new_idx->{columns}}) &&
460 $old_idx->{unique} == $new_idx->{unique}
461 0 ▶ ;
Branch: 1 branch
BranchTrueFalseTotal
if $new_idx and $old_col_str eq join(", ", @{$new_idx->{"columns"};}) and $old_idx->{"unique"} == $new_idx->{"unique"} 0 0 0
Condition0%0/6
$new_idx and $old_col_str eq join(", ", @{$new_idx->{"columns"};}) and $old_idx->{"unique"} == $new_idx->{"unique"}
!ll&&!rl&&r
000
$new_idx and $old_col_str eq join(", ", @{$new_idx->{"columns"};})
!ll&&!rl&&r
000
MC/DC0%0/3
$new_idx and $old_col_str eq join(", ", @{$new_idx->{"columns"};}) and $old_idx->{"unique"} == $new_idx->{"unique"}
$new_idx $old_col_str eq join(", ", @{$new_idx->{"columns"};}) $old_idx->{"unique"} == $new_idx->{"unique"}
Truth table0%0/4
A and B and C
A= $new_idxB= $old_col_str eq join(", ", @{$new_idx->{"columns"};})C= $old_idx->{"unique"} == $new_idx->{"unique"}
ABCresult
0XX0
10X0
1100
1111
462  
463 # It failed one of the other checks?
464 0 ▶ if ($new_idx) {
Branch: 1 branch
BranchTrueFalseTotal
if ($new_idx) 0 0 0
465 my $conditional =
466 $old_idx->{unique} != $new_idx->{unique} ?
467 ($new_idx->{unique} ? "it is now UNIQUE" : "it is no longer UNIQUE") :
468 0 ▶ "its columns have changed (".join(', ', @{$new_idx->{columns}}).")"
Branch: 2 branches
BranchTrueFalseTotal
$old_idx->{'unique'} != $new_idx->{'unique'} ? : 0 0 0
$new_idx->{'unique'} ? : 0 0 0
469 ;
470  
471 0 $self->progress->message( join "\n",
472 '',
473 "WARNING: Found index $table_name.$index_name ($old_col_str), but $conditional!",
474 "Please double-check that the indexes on the table are what you expect!",
475 '',
476 );
477 0 next;
478 }
479  
480 # It looks like we have a mismatch at this point. Try to find the renamed index.
481 0 ▶ $new_idx = first {
Subroutine __ANON__: not called CC=3 SCAR=24.8
482 $_->{name} ne 'PRIMARY' &&
483 0 $old_col_str eq join(', ', @{$_->{columns}}) &&
484 $old_idx->{unique} == $_->{unique}
485 0 ▶ } values %new_idx_data;
Branch: 1 branch
BranchTrueFalseTotal
$_->{"name"} ne "PRIMARY" and $old_col_str eq join(", ", @{$_->{"columns"};}) and $old_idx->{"unique"} == $_->{"unique"} 0 0 0
Condition0%0/3
$_->{"name"} ne "PRIMARY" and $old_col_str eq join(", ", @{$_->{"columns"};})
!ll&&!rl&&r
000
MC/DC0%0/2
$_->{"name"} ne "PRIMARY" and $old_col_str eq join(", ", @{$_->{"columns"};})
$_->{"name"} ne "PRIMARY" $old_col_str eq join(", ", @{$_->{"columns"};})
Truth table0%0/3
A and B
A= $_->{"name"} ne "PRIMARY"B= $old_col_str eq join(", ", @{$_->{"columns"};})
ABresult
0X0
100
111
486  
487 # It disappeared?
488 0 ▶ unless ($new_idx) {
Branch: 1 branch
BranchTrueFalseTotal
unless ($new_idx) 0 0 0
489 0 $self->progress->message( join "\n",
490 '',
491 "WARNING: Found index $table_name.$index_name ($old_col_str), which may have been renamed, but a matching index cannot be found!",
492 "Please double-check that the indexes on the table are what you expect!",
493 '',
494 );
495 0 next;
496 }
497  
498 0 my $new_index_name = $new_idx->{name};
499  
500 # We can only rename in MySQL 5.7
501 0 ▶ if ($mmver < 5.007) {
Branch: 1 branch
BranchTrueFalseTotal
if ($mmver < "5.007") 0 0 0
502 0 $self->progress->message( join "\n",
503 '',
504 "WARNING: Found index $table_name.$index_name ($old_col_str), which was renamed to $new_index_name!",
505 "The index cannot safely renamed in MySQL $mmver, so you may need to DROP/ADD this",
506 "index a different way to correct the problem.",
507 '',
508 );
509 0 next;
510 }
511  
512 # Finally, add a RENAME INDEX statement
513 0 push @stmts, join(' ',
514 "ALTER TABLE",
515 $dbh->quote_identifier( $table_name ),
516 "RENAME INDEX",
517 $dbh->quote_identifier( $new_index_name ),
518 "TO",
519 $dbh->quote_identifier( $index_name ),
520 );
521 }
522 }
523  
524 0 return @stmts;
525 }
526  
527 1 1;
528  
529 __END__
« OnlineDDL.pm ↑ Summary OnlineDDL/Helper/Base.pm »