Merge lp:~percona-toolkit-dev/percona-toolkit/pt-osc-data-loss-bug-1068562 into lp:percona-toolkit/2.1
- pt-osc-data-loss-bug-1068562
- Merge into 2.1
Status: | Merged |
---|---|
Merged at revision: | 451 |
Proposed branch: | lp:~percona-toolkit-dev/percona-toolkit/pt-osc-data-loss-bug-1068562 |
Merge into: | lp:percona-toolkit/2.1 |
Diff against target: |
730 lines (+589/-18) (has conflicts) 5 files modified
bin/pt-online-schema-change (+142/-17) lib/Quoter.pm (+7/-1) t/pt-online-schema-change/find_renamed_cols.t (+225/-0) t/pt-online-schema-change/rename_columns.t (+204/-0) t/pt-online-schema-change/samples/data-loss-bug-1068562.sql (+11/-0) Text conflict in bin/pt-online-schema-change |
To merge this branch: | bzr merge lp:~percona-toolkit-dev/percona-toolkit/pt-osc-data-loss-bug-1068562 |
Related bugs: | |
Related blueprints: |
Reviewer | Review Type | Date Requested | Status |
---|---|---|---|
Brian Fraser (community) | Approve | ||
Daniel Nichter | Approve | ||
Review via email: mp+133309@code.launchpad.net |
This proposal supersedes a proposal from 2012-10-30.
Commit message
Description of the change
Daniel Nichter (daniel-nichter) wrote : Posted in a previous version of this proposal | # |
Brian Fraser (fraserbn) wrote : Posted in a previous version of this proposal | # |
Hah, you just saved us from a bug in 5.18, I think. Currently hash order is semi-randomized -- perl -E 'say join ",", keys %SIG' will always return the same order in your machine with that Perl binary (as long as %SIG doesn't change), although th order might be different in mine.
So it's currently Not A Bug, but it will be soon. About the order bit, there was this comment:
# Col posn (position) is just for looks because user's like
# to see columns listed in their original order, not Perl's
# random hash key sorting.
So I definitely broke that too. I've just pushed a fix for both things.
Daniel Nichter (daniel-nichter) wrote : Posted in a previous version of this proposal | # |
pt-osc-data-loss-bug-1068562$ SLOW_TESTS=1 perl t/pt-online-
1..3
not ok 1 - Limited user (bug 987694): 0 exit
# Failed test 'Limited user (bug 987694): 0 exit'
# at t/pt-online-
# got: '255'
# expected: '0'
not ok 2 - Limited user (bug 987694): altered table
# Failed test 'Limited user (bug 987694): altered table'
# at t/pt-online-
# 'Error creating triggers: Use of uninitialized value in concatenation (.) or string at /Users/
#
# rs...
# Dropped triggers OK.
# Dropping new table...
# Dropped new table OK.
# `pt_osc`.`t` was not altered.
# '
# doesn't match '(?-xism:
---
The downside to SLOW_TESTS (which I implemented) is exactly this: failures creep in. Yet, SLOW_TESTS takes hours off a full test run. In any case, something about these changes is breaking that test; it's probably trivial.
Daniel Nichter (daniel-nichter) wrote : | # |
My changes fixed privs.t:
$ SLOW_TESTS=1 perl privs.t
1..3
ok 1 - Limited user (bug 987694): 0 exit
ok 2 - Limited user (bug 987694): altered table
ok 3 - Sandbox servers
Tested pt-osc on everything and "Original table must have a PK or unique index" in sanity_checks.t is failing because I half-merged bug 1062324. 2.1 has the fixed sanity_checks.t, so this failure a false-positive for now.
"--check-alter error message" in rename_columns.t failed just on Ubuntu 12 with MySQL 5.5.24: https:/
- 429. By Brian Fraser
-
Fixed a bug in full_output
- 430. By Brian Fraser
-
Fixed a bug in Quoter-
>split_ unquote, added several tests for find_renamed_cols
Brian Fraser (fraserbn) wrote : Posted in a previous version of this proposal | # |
A bug* in full_output was masking this:
$ perl t/pt-online-
1..6
ok 1 - Original database must exist
ok 2 - Original table must exist
ok 3 - Original table cannot have triggers
not ok 4 - Original table must have a PK or unique index
# Failed test 'Original table must have a PK or unique index'
# at t/pt-online-
# 'Starting a dry run. `pt_osc`.`t` will not be altered. Specify --execute instead of --dry-run to alter the table.
# Creating new table...
# Created new table pt_osc._t_new OK.
# Dropping new table...
# Dropped new table OK.
# Dry run complete. `pt_osc`.`t` was not altered.
# Dry run complete. `pt_osc`.`t` was not altered.
# (in cleanup) The new table `pt_osc`.`_t_new` does not have a PRIMARY KEY or a unique index which is required for the DELETE trigger.
# '
# doesn't match '(?^:`pt_osc`.`t` does not have a PRIMARY KEY or a unique index)'
ok 5 - Doesn't try forever to find a new table name
ok 6 - Sandbox servers
# Looks like you failed 1 test of 6.
Looking at the code, looks like the test is looking for the wrong table name?
* Nasty luck here: I've fixed this four times now, but for whatever reason we've yet to merge a branch with the fix into trunk.
Brian Fraser (fraserbn) : | # |
Brian Fraser (fraserbn) wrote : | # |
Oh, welp, I just saw your latest message! The Ubuntu 12 failure got fixed with the full_output fix, so I'm marking this as approved since bug 1062324 sovles the bit I was worried about.
Preview Diff
1 | === modified file 'bin/pt-online-schema-change' |
2 | --- bin/pt-online-schema-change 2012-11-07 07:12:37 +0000 |
3 | +++ bin/pt-online-schema-change 2012-11-08 09:20:24 +0000 |
4 | @@ -2352,12 +2352,18 @@ |
5 | |
6 | sub split_unquote { |
7 | my ( $self, $db_tbl, $default_db ) = @_; |
8 | - $db_tbl =~ s/`//g; |
9 | my ( $db, $tbl ) = split(/[.]/, $db_tbl); |
10 | if ( !$tbl ) { |
11 | $tbl = $db; |
12 | $db = $default_db; |
13 | } |
14 | + for ($db, $tbl) { |
15 | + next unless $_; |
16 | + s/\A`//; |
17 | + s/`\z//; |
18 | + s/``/`/; |
19 | + } |
20 | + |
21 | return ($db, $tbl); |
22 | } |
23 | |
24 | @@ -7803,6 +7809,8 @@ |
25 | # Step 2: Alter the new, empty table. This should be very quick, |
26 | # or die if the user specified a bad alter statement. |
27 | # ##################################################################### |
28 | + my %renamed_cols; |
29 | + |
30 | if ( my $alter = $o->get('alter') ) { |
31 | print "Altering new table...\n"; |
32 | my $sql = "ALTER TABLE $new_tbl->{name} $alter"; |
33 | @@ -7814,7 +7822,28 @@ |
34 | if ( $EVAL_ERROR ) { |
35 | die "Error altering new table $new_tbl->{name}: $EVAL_ERROR\n" |
36 | } |
37 | - print "Altered $new_tbl->{name} OK.\n" |
38 | + print "Altered $new_tbl->{name} OK.\n"; |
39 | + |
40 | + # Check for renamed columns. |
41 | + # https://bugs.launchpad.net/percona-toolkit/+bug/1068562 |
42 | + %renamed_cols = find_renamed_cols($alter, $tp); |
43 | + PTDEBUG && _d("Renamed columns (old => new): ", Dumper(\%renamed_cols)); |
44 | + if ( %renamed_cols && $o->get('check-alter') ) { |
45 | + # sort is just for making output consistent for testing |
46 | + my $msg = "--alter appears to rename these columns: " |
47 | + . join(", ", map { "$_ to $renamed_cols{$_}" } |
48 | + sort keys %renamed_cols); |
49 | + if ( $o->get('dry-run') ) { |
50 | + print $msg . "\n" |
51 | + } |
52 | + else { |
53 | + die $msg |
54 | + . ". The tool should handle this correctly, but you should " |
55 | + . "test it first because if it fails the renamed columns' " |
56 | + . "data will be lost! Specify --no-check-alter to disable " |
57 | + . "this check and perform the --alter.\n"; |
58 | + } |
59 | + } |
60 | } |
61 | |
62 | # Get the new table struct. This shouldn't die because |
63 | @@ -7831,18 +7860,33 @@ |
64 | # If the user drops a col, that's easy: just don't copy it. If they |
65 | # add a column, it must have a default value. Other alterations |
66 | # may or may not affect the copy process--we'll know when we try! |
67 | - # Note: we don't want to examine the --alter statement to see if the |
68 | - # cols have changed because that's messy and prone to parsing errors. |
69 | # Col posn (position) is just for looks because user's like |
70 | # to see columns listed in their original order, not Perl's |
71 | # random hash key sorting. |
72 | my $col_posn = $orig_tbl->{tbl_struct}->{col_posn}; |
73 | my $orig_cols = $orig_tbl->{tbl_struct}->{is_col}; |
74 | my $new_cols = $new_tbl->{tbl_struct}->{is_col}; |
75 | - my @common_cols = sort { $col_posn->{$a} <=> $col_posn->{$b} } |
76 | - grep { $new_cols->{$_} } |
77 | + my @common_cols = map { +{ old => $_, new => $renamed_cols{$_} || $_ } } |
78 | + sort { $col_posn->{$a} <=> $col_posn->{$b} } |
79 | + grep { $new_cols->{$_} || $renamed_cols{$_} } |
80 | keys %$orig_cols; |
81 | - PTDEBUG && _d('Common columns', @common_cols); |
82 | + PTDEBUG && _d('Common columns', Dumper(\@common_cols)); |
83 | + |
84 | + # Find a pk or unique index to use for the delete trigger. can_nibble() |
85 | + # above returns an index, but NibbleIterator will use non-unique indexes, |
86 | + # so we have to do this again here. |
87 | + my $indexes = $new_tbl->{tbl_struct}->{keys}; # brevity |
88 | + foreach my $index ( $tp->sort_indexes($new_tbl->{tbl_struct}) ) { |
89 | + if ( $index eq 'PRIMARY' || $indexes->{$index}->{is_unique} ) { |
90 | + PTDEBUG && _d('Delete trigger index:', Dumper($index)); |
91 | + $new_tbl->{del_index} = $index; |
92 | + last; |
93 | + } |
94 | + } |
95 | + if ( !$new_tbl->{del_index} ) { |
96 | + die "The new table $new_tbl->{name} does not have a PRIMARY KEY " |
97 | + . "or a unique index which is required for the DELETE trigger.\n"; |
98 | + } |
99 | |
100 | # Find a pk or unique index to use for the delete trigger. can_nibble() |
101 | # above returns an index, but NibbleIterator will use non-unique indexes, |
102 | @@ -8061,8 +8105,6 @@ |
103 | my (%args) = @_; |
104 | my $tbl = $args{tbl}; |
105 | my $nibble_iter = $args{NibbleIterator}; |
106 | - my $sth = $nibble_iter->statements(); |
107 | - my $boundary = $nibble_iter->boundaries(); |
108 | |
109 | return if $o->get('dry-run'); |
110 | |
111 | @@ -8163,10 +8205,10 @@ |
112 | |
113 | # NibbleIterator combines these two statements and adds |
114 | # "FROM $orig_table->{name} WHERE <nibble stuff>". |
115 | - my $dml = "INSERT LOW_PRIORITY IGNORE INTO $new_tbl->{name} " |
116 | - . "(" . join(', ', map { $q->quote($_) } @common_cols) . ") " |
117 | - . "SELECT"; |
118 | - my $select = join(', ', map { $q->quote($_) } @common_cols); |
119 | + my $dml = "INSERT LOW_PRIORITY IGNORE INTO $new_tbl->{name} " |
120 | + . "(" . join(', ', map { $q->quote($_->{new}) } @common_cols) . ") " |
121 | + . "SELECT"; |
122 | + my $select = join(', ', map { $q->quote($_->{old}) } @common_cols); |
123 | |
124 | # The chunk size is auto-adjusted, so use --chunk-size as |
125 | # the initial value, but then save and update the adjusted |
126 | @@ -8378,6 +8420,53 @@ |
127 | # Subroutines. |
128 | # ############################################################################ |
129 | |
130 | +sub find_renamed_cols { |
131 | + my ($alter, $tp) = @_; |
132 | + |
133 | + my $unquoted_ident = qr/ |
134 | + (?!\p{Digit}+[.\s]) # Not all digits |
135 | + [0-9a-zA-Z_\x{80}-\x{FFFF}\$]+ # As per the spec |
136 | + /x; |
137 | + |
138 | + my $quoted_ident = do { |
139 | + my $quoted_ident_character = qr/ |
140 | + [\x{01}-\x{5F}\x{61}-\x{FFFF}] # Any character but the null byte and ` |
141 | + /x; |
142 | + qr{ |
143 | + # The following alternation is there because something like (?<=.) |
144 | + # would match if this regex was used like /.$re/, |
145 | + # or even more tellingly, would match on "``" =~ /`$re`/ |
146 | + $quoted_ident_character+ # One or more characters |
147 | + (?: `` $quoted_ident_character* )* # possibly followed by `` and |
148 | + # more characters, zero or more times |
149 | + | $quoted_ident_character* # OR, zero or more characters |
150 | + (?: `` $quoted_ident_character* )+ # Followed by `` and maybe more |
151 | + # characters, one or more times. |
152 | + }x |
153 | + }; |
154 | + |
155 | + my $ansi_quotes_ident = qr/ |
156 | + [^"]+ (?: "" [^"]* )* |
157 | + | [^"]* (?: "" [^"]* )+ |
158 | + /x; |
159 | + |
160 | + my $table_ident = qr/$unquoted_ident|`$quoted_ident`|"$ansi_quotes_ident"/; |
161 | + my $alter_change_col_re = qr/\bCHANGE \s+ (?:COLUMN \s+)? |
162 | + ($table_ident) \s+ ($table_ident)/ix; |
163 | + |
164 | + my %renames; |
165 | + while ( $alter =~ /$alter_change_col_re/g ) { |
166 | + my ($orig, $new) = map { $tp->ansi_to_legacy($_) } $1, $2; |
167 | + next unless $orig && $new; |
168 | + my (undef, $orig_tbl) = Quoter->split_unquote($orig); |
169 | + my (undef, $new_tbl) = Quoter->split_unquote($new); |
170 | + # Silly but plausible: CHANGE COLUMN same_name same_name ... |
171 | + next if lc($orig_tbl) eq lc($new_tbl); |
172 | + $renames{$orig_tbl} = $new_tbl; |
173 | + } |
174 | + return %renames; |
175 | +} |
176 | + |
177 | sub nibble_is_safe { |
178 | my (%args) = @_; |
179 | my @required_args = qw(Cxn tbl NibbleIterator OptionParser); |
180 | @@ -8900,6 +8989,7 @@ |
181 | |
182 | # To be safe, the delete trigger must specify all the columns of the |
183 | # primary key/unique index. We use null-safe equals, because unique |
184 | +<<<<<<< TREE |
185 | # unique indexes can be nullable. |
186 | my $tbl_struct = $new_tbl->{tbl_struct}; |
187 | my $del_index = $new_tbl->{del_index}; |
188 | @@ -8908,16 +8998,29 @@ |
189 | my $col = $q->quote($_); |
190 | "$new_tbl->{name}.$col <=> OLD.$col" |
191 | } @{$tbl_struct->{keys}->{$del_index}->{cols}} ); |
192 | +======= |
193 | + # unique indexes can be nullable. Cols are from the new table and |
194 | + # they may have been renamed |
195 | + my %old_col_for = map { $_->{new} => $_->{old} } @$cols; |
196 | + my $tbl_struct = $new_tbl->{tbl_struct}; |
197 | + my $del_index = $new_tbl->{del_index}; |
198 | + my $del_index_cols = join(" AND ", map { |
199 | + my $new_col = $_; |
200 | + my $old_col = $old_col_for{$new_col} || $new_col; |
201 | + my $new_qcol = $q->quote($new_col); |
202 | + my $old_qcol = $q->quote($old_col); |
203 | + "$new_tbl->{name}.$new_qcol <=> OLD.$old_qcol" |
204 | + } @{$tbl_struct->{keys}->{$del_index}->{cols}} ); |
205 | + |
206 | +>>>>>>> MERGE-SOURCE |
207 | my $delete_trigger |
208 | = "CREATE TRIGGER `${prefix}_del` AFTER DELETE ON $orig_tbl->{name} " |
209 | . "FOR EACH ROW " |
210 | . "DELETE IGNORE FROM $new_tbl->{name} " |
211 | . "WHERE $del_index_cols"; |
212 | |
213 | - # The insert and update triggers should only use values for columns |
214 | - # that exist in both tables. |
215 | - my $qcols = join(', ', map { $q->quote($_) } @$cols); |
216 | - my $new_vals = join(', ', map { "NEW.".$q->quote($_) } @$cols); |
217 | + my $qcols = join(', ', map { $q->quote($_->{new}) } @$cols); |
218 | + my $new_vals = join(', ', map { "NEW.".$q->quote($_->{old}) } @$cols); |
219 | my $insert_trigger |
220 | = "CREATE TRIGGER `${prefix}_ins` AFTER INSERT ON $orig_tbl->{name} " |
221 | . "FOR EACH ROW " |
222 | @@ -9472,6 +9575,28 @@ |
223 | |
224 | Sleep time between checks for L<"--max-lag">. |
225 | |
226 | + |
227 | +=item --[no]check-alter |
228 | + |
229 | +default: yes |
230 | + |
231 | +Parses the L<"--alter"> specified and tries to warn of possible unintended |
232 | +behavior. Currently, it checks for: |
233 | + |
234 | +=over |
235 | + |
236 | +=item Column renames |
237 | + |
238 | +In previous versions of the tool, renaming a column with |
239 | +C<CHANGE COLUMN name new_name> would lead to that column's data being lost. |
240 | +The tool now parses the alter statement and tries to catch these cases, so |
241 | +the renamed columns should have the same data as the originals. However, the |
242 | +code that does this is not a full-blown SQL parser, so we recommend that users |
243 | +run the tool with L<"--dry-run"> and check if it's detecting the renames |
244 | +correctly. |
245 | + |
246 | +=back |
247 | + |
248 | =item --[no]check-plan |
249 | |
250 | default: yes |
251 | |
252 | === modified file 'lib/PerconaTest.pm' |
253 | === modified file 'lib/Quoter.pm' |
254 | --- lib/Quoter.pm 2012-08-22 19:19:43 +0000 |
255 | +++ lib/Quoter.pm 2012-11-08 09:20:24 +0000 |
256 | @@ -93,12 +93,18 @@ |
257 | # <join_quote> |
258 | sub split_unquote { |
259 | my ( $self, $db_tbl, $default_db ) = @_; |
260 | - $db_tbl =~ s/`//g; |
261 | my ( $db, $tbl ) = split(/[.]/, $db_tbl); |
262 | if ( !$tbl ) { |
263 | $tbl = $db; |
264 | $db = $default_db; |
265 | } |
266 | + for ($db, $tbl) { |
267 | + next unless $_; |
268 | + s/\A`//; |
269 | + s/`\z//; |
270 | + s/``/`/; |
271 | + } |
272 | + |
273 | return ($db, $tbl); |
274 | } |
275 | |
276 | |
277 | === added file 't/pt-online-schema-change/find_renamed_cols.t' |
278 | --- t/pt-online-schema-change/find_renamed_cols.t 1970-01-01 00:00:00 +0000 |
279 | +++ t/pt-online-schema-change/find_renamed_cols.t 2012-11-08 09:20:24 +0000 |
280 | @@ -0,0 +1,225 @@ |
281 | +#!/usr/bin/env perl |
282 | + |
283 | +BEGIN { |
284 | + die "The PERCONA_TOOLKIT_BRANCH environment variable is not set.\n" |
285 | + unless $ENV{PERCONA_TOOLKIT_BRANCH} && -d $ENV{PERCONA_TOOLKIT_BRANCH}; |
286 | + unshift @INC, "$ENV{PERCONA_TOOLKIT_BRANCH}/lib"; |
287 | +}; |
288 | + |
289 | +use strict; |
290 | +use warnings FATAL => 'all'; |
291 | +use English qw(-no_match_vars); |
292 | +use Test::More; |
293 | + |
294 | +use Data::Dumper; |
295 | +use PerconaTest; |
296 | + |
297 | +require "$trunk/bin/pt-online-schema-change"; |
298 | + |
299 | +my $q = Quoter->new; |
300 | +my $tp = TableParser->new(Quoter => $q); |
301 | + |
302 | +sub test_func { |
303 | + my ($alter, $renamed_cols) = @_; |
304 | + die "No alter arg" unless $alter; |
305 | + die "No renamed_cols arg" unless $renamed_cols; |
306 | + (my $show_alter = $alter) =~ s/\n/\\n/g; |
307 | + |
308 | + my %got_renamed_cols = eval { |
309 | + pt_online_schema_change::find_renamed_cols($alter, $tp); |
310 | + }; |
311 | + if ( $EVAL_ERROR ) { |
312 | + is_deeply( |
313 | + undef, |
314 | + $renamed_cols, |
315 | + $show_alter, |
316 | + ) or diag($EVAL_ERROR); |
317 | + } |
318 | + else { |
319 | + is_deeply( |
320 | + \%got_renamed_cols, |
321 | + $renamed_cols, |
322 | + $show_alter, |
323 | + ) or diag(Dumper(\%got_renamed_cols)); |
324 | + } |
325 | +} |
326 | + |
327 | +# ############################################################################# |
328 | +# Single column alters |
329 | +# ############################################################################# |
330 | + |
331 | +test_func( |
332 | + "change old_column_name new_column_name varchar(255) NULL", |
333 | + { |
334 | + old_column_name => 'new_column_name', |
335 | + }, |
336 | +); |
337 | + |
338 | +# Case-sensitive? |
339 | +test_func( |
340 | + "CHANGE old_column_name new_column_name VARCHAR(255) NULL", |
341 | + { |
342 | + old_column_name => 'new_column_name', |
343 | + }, |
344 | +); |
345 | + |
346 | +# Optional COLUMN? |
347 | +test_func( |
348 | + "CHANGE column old_column_name new_column_name VARCHAR(255) NULL", |
349 | + { |
350 | + old_column_name => 'new_column_name', |
351 | + }, |
352 | +); |
353 | + |
354 | +# Space-sensitive? |
355 | +test_func( |
356 | + "CHANGE a z VARCHAR(255) NULL", |
357 | + { |
358 | + a => 'z', |
359 | + }, |
360 | +); |
361 | + |
362 | +# Backtick-sensitive? |
363 | +test_func( |
364 | + "CHANGE `a` `z` VARCHAR(255) NULL", |
365 | + { |
366 | + a => 'z', |
367 | + }, |
368 | +); |
369 | + |
370 | +# Column named column? |
371 | +test_func( |
372 | + "CHANGE `column` new_column_name VARCHAR(255) NULL", |
373 | + { |
374 | + column => 'new_column_name', |
375 | + }, |
376 | +); |
377 | + |
378 | + |
379 | +# Extended ascii? |
380 | +test_func( |
381 | + "CHANGE `café` `tête-à-tête` INT", |
382 | + { |
383 | + 'café' => 'tête-à-tête', |
384 | + }, |
385 | +); |
386 | + |
387 | +# UTF-8? |
388 | +if( Test::Builder->VERSION < 2 ) { |
389 | + foreach my $method ( qw(output failure_output) ) { |
390 | + binmode Test::More->builder->$method(), ':encoding(UTF-8)'; |
391 | + } |
392 | +} |
393 | +test_func( |
394 | + "CHANGE `\x{30cb}` `\x{30cd}` INT", |
395 | + { |
396 | + "\x{30cb}" => "\x{30cd}", |
397 | + }, |
398 | +); |
399 | +# Mixed backtick-sensitive? |
400 | +test_func( |
401 | + "CHANGE `a` z VARCHAR(255) NULL", |
402 | + { |
403 | + a => 'z', |
404 | + }, |
405 | +); |
406 | + |
407 | +test_func( |
408 | + "CHANGE a `z` VARCHAR(255) NULL", |
409 | + { |
410 | + a => 'z', |
411 | + }, |
412 | +); |
413 | + |
414 | +# Ansi quotes-sensitive? (should matter) |
415 | +test_func( |
416 | + qq{CHANGE "a" "z" VARCHAR(255) NULL}, |
417 | + { |
418 | + a => 'z', |
419 | + }, |
420 | +); |
421 | + |
422 | +# Embedded backticks? |
423 | +test_func( |
424 | + "CHANGE `a``a` z VARCHAR(255) NULL", |
425 | + { |
426 | + 'a`a' => 'z', |
427 | + }, |
428 | +); |
429 | + |
430 | +# Emebedded spaces? |
431 | +test_func( |
432 | + "CHANGE `a yes ` z VARCHAR(255) NULL", |
433 | + { |
434 | + 'a yes ' => 'z', |
435 | + }, |
436 | +); |
437 | + |
438 | +test_func( |
439 | + "CHANGE ` yes ` `\nyes!\na` VARCHAR(255) NULL", |
440 | + { |
441 | + ' yes ' => "\nyes!\na", |
442 | + }, |
443 | +); |
444 | + |
445 | +test_func( |
446 | + "CHANGE ` yes ` `\nyes!\na` VARCHAR(255) NULL", |
447 | + { |
448 | + ' yes ' => "\nyes!\na", |
449 | + }, |
450 | +); |
451 | + |
452 | +# ############################################################################# |
453 | +# Two column alters |
454 | +# ############################################################################# |
455 | + |
456 | +test_func( |
457 | + "CHANGE a z VARCHAR(255) NULL, CHANGE foo bar INT", |
458 | + { |
459 | + a => 'z', |
460 | + foo => 'bar', |
461 | + }, |
462 | +); |
463 | + |
464 | +# Pathological |
465 | +test_func( |
466 | + "CHANGE a `CHANGE a z VARCHAR(255) NOT NULL` VARCHAR(255) NULL, CHANGE foo bar INT", |
467 | + { |
468 | + a => 'CHANGE a z VARCHAR(255) NOT NULL', |
469 | + foo => 'bar', |
470 | + }, |
471 | +); |
472 | + |
473 | + |
474 | +# ############################################################################# |
475 | +# Fake alters |
476 | +# ############################################################################# |
477 | + |
478 | +# Not really renamed. |
479 | +test_func( |
480 | + "CHANGE foo foo FLOAT", |
481 | + { |
482 | + }, |
483 | +); |
484 | + |
485 | +# Not really renamed, should ignore case |
486 | +test_func( |
487 | + "CHANGE foo FOO FLOAT", |
488 | + { |
489 | + }, |
490 | +); |
491 | + |
492 | +TODO: { |
493 | + local $::TODO = "We don't parse the entire alter statement, what looks like a CHANGE COLUMNS"; |
494 | + # Not really an alter, pathological |
495 | + test_func( |
496 | + "MODIFY `CHANGE a z VARCHAR(255) NOT NULL` FLOAT", |
497 | + { |
498 | + }, |
499 | + ); |
500 | +} |
501 | + |
502 | +# ############################################################################# |
503 | +# Done. |
504 | +# ############################################################################# |
505 | +done_testing; |
506 | |
507 | === added file 't/pt-online-schema-change/rename_columns.t' |
508 | --- t/pt-online-schema-change/rename_columns.t 1970-01-01 00:00:00 +0000 |
509 | +++ t/pt-online-schema-change/rename_columns.t 2012-11-08 09:20:24 +0000 |
510 | @@ -0,0 +1,204 @@ |
511 | +#!/usr/bin/env perl |
512 | + |
513 | +BEGIN { |
514 | + die "The PERCONA_TOOLKIT_BRANCH environment variable is not set.\n" |
515 | + unless $ENV{PERCONA_TOOLKIT_BRANCH} && -d $ENV{PERCONA_TOOLKIT_BRANCH}; |
516 | + unshift @INC, "$ENV{PERCONA_TOOLKIT_BRANCH}/lib"; |
517 | +}; |
518 | + |
519 | +use strict; |
520 | +use warnings FATAL => 'all'; |
521 | +use English qw(-no_match_vars); |
522 | +use Test::More; |
523 | + |
524 | +use Data::Dumper; |
525 | +use PerconaTest; |
526 | +use Sandbox; |
527 | + |
528 | +require "$trunk/bin/pt-online-schema-change"; |
529 | + |
530 | +my $dp = new DSNParser(opts=>$dsn_opts); |
531 | +my $sb = new Sandbox(basedir => '/tmp', DSNParser => $dp); |
532 | +my $master_dbh = $sb->get_dbh_for('master'); |
533 | +my $slave_dbh = $sb->get_dbh_for('slave1'); |
534 | + |
535 | +if ( !$master_dbh ) { |
536 | + plan skip_all => 'Cannot connect to sandbox master'; |
537 | +} |
538 | +elsif ( !$slave_dbh ) { |
539 | + plan skip_all => 'Cannot connect to sandbox slave1'; |
540 | +} |
541 | + |
542 | +# The sandbox servers run with lock_wait_timeout=3 and it's not dynamic |
543 | +# so we need to specify --lock-wait-timeout=3 else the tool will die. |
544 | +my $master_dsn = 'h=127.1,P=12345,u=msandbox,p=msandbox'; |
545 | +my @args = (qw(--lock-wait-timeout 3)); |
546 | +my $output; |
547 | +my $exit_status; |
548 | +my $sample = "t/pt-online-schema-change/samples/"; |
549 | + |
550 | +# ############################################################################ |
551 | +# https://bugs.launchpad.net/percona-toolkit/+bug/1068562 |
552 | +# pt-online-schema-change loses data when renaming columns |
553 | +# ############################################################################ |
554 | + |
555 | +$sb->load_file('master', "$sample/data-loss-bug-1068562.sql"); |
556 | + |
557 | +($output, $exit_status) = full_output( |
558 | + sub { pt_online_schema_change::main(@args, |
559 | + "$master_dsn,D=bug1068562,t=simon", |
560 | + "--alter", "change old_column_name new_column_name varchar(255) NULL", |
561 | + qw(--execute)) }, |
562 | +); |
563 | + |
564 | +is( |
565 | + $exit_status, |
566 | + 255, |
567 | + "Die if --execute without --no-check-alter" |
568 | +); |
569 | + |
570 | +like( |
571 | + $output, |
572 | + qr/Specify --no-check-alter to disable this check/, |
573 | + "--check-alter error message" |
574 | +); |
575 | + |
576 | +($output, $exit_status) = full_output( |
577 | + sub { pt_online_schema_change::main(@args, |
578 | + "$master_dsn,D=bug1068562,t=simon", |
579 | + "--alter", "change old_column_name new_column_name varchar(255) NULL", |
580 | + qw(--execute --no-check-alter)) }, |
581 | +); |
582 | + |
583 | +my $rows = $master_dbh->selectall_arrayref("SELECT * FROM bug1068562.simon ORDER BY id"); |
584 | + |
585 | +is_deeply( |
586 | + $rows, |
587 | + [ [qw(1 a)], [qw(2 b)], [qw(3 c)] ], |
588 | + "bug1068562.simon: No data lost" |
589 | +) or diag(Dumper($rows)); |
590 | + |
591 | +# ############################################################################# |
592 | +# Now try with sakila.city. |
593 | +# ############################################################################# |
594 | + |
595 | +my $orig = $master_dbh->selectall_arrayref(q{SELECT city FROM sakila.city}); |
596 | + |
597 | +($output, $exit_status) = full_output( |
598 | + sub { pt_online_schema_change::main(@args, |
599 | + "$master_dsn,D=sakila,t=city", |
600 | + "--alter", "change column `city` `some_cities` varchar(50) NOT NULL", |
601 | + qw(--execute --alter-foreign-keys-method auto --no-check-alter)) }, |
602 | +); |
603 | + |
604 | +is( |
605 | + $exit_status, |
606 | + 0, |
607 | + "sakila.city: Exit status 0", |
608 | +); |
609 | + |
610 | +my $mod = $master_dbh->selectall_arrayref(q{SELECT some_cities FROM sakila.city}); |
611 | + |
612 | +is_deeply( |
613 | + $orig, |
614 | + $mod, |
615 | + "sakila.city: No data missing after first rename" |
616 | +); |
617 | + |
618 | +($output, $exit_status) = full_output( |
619 | + sub { pt_online_schema_change::main(@args, |
620 | + "$master_dsn,D=sakila,t=city", |
621 | + "--alter", "change column `some_cities` city varchar(50) NOT NULL", |
622 | + qw(--execute --alter-foreign-keys-method auto --no-check-alter)) }, |
623 | +); |
624 | + |
625 | +my $mod2 = $master_dbh->selectall_arrayref(q{SELECT city FROM sakila.city}); |
626 | + |
627 | +is_deeply( |
628 | + $orig, |
629 | + $mod2, |
630 | + "sakila.city: No date missing after second rename" |
631 | +); |
632 | + |
633 | + |
634 | +# ############################################################################# |
635 | +# Try with sakila.staff |
636 | +# ############################################################################# |
637 | + |
638 | +$orig = $master_dbh->selectall_arrayref(q{SELECT first_name, last_name FROM sakila.staff}); |
639 | + |
640 | +($output, $exit_status) = full_output( |
641 | + sub { pt_online_schema_change::main(@args, |
642 | + "$master_dsn,D=sakila,t=staff", |
643 | + "--alter", "change column first_name first_name_mod varchar(45) NOT NULL, change column last_name last_name_mod varchar(45) NOT NULL", |
644 | + qw(--execute --alter-foreign-keys-method auto --no-check-alter)) }, |
645 | +); |
646 | + |
647 | +$mod = $master_dbh->selectall_arrayref(q{SELECT first_name_mod, last_name_mod FROM sakila.staff}); |
648 | + |
649 | +is_deeply( |
650 | + $orig, |
651 | + $mod, |
652 | + "sakila.staff: No columns went missing with a double rename" |
653 | +); |
654 | + |
655 | +($output, $exit_status) = full_output( |
656 | + sub { pt_online_schema_change::main(@args, |
657 | + "$master_dsn,D=sakila,t=staff", |
658 | + "--alter", "change column first_name_mod first_name varchar(45) NOT NULL, change column last_name_mod last_name varchar(45) NOT NULL", |
659 | + qw(--execute --alter-foreign-keys-method auto --no-check-alter)) }, |
660 | +); |
661 | + |
662 | +$mod2 = $master_dbh->selectall_arrayref(q{SELECT first_name, last_name FROM sakila.staff}); |
663 | + |
664 | +is_deeply( |
665 | + $orig, |
666 | + $mod2, |
667 | + "sakila.staff: No columns went missing when renaming the columns back" |
668 | +); |
669 | + |
670 | + |
671 | +# ############################################################################# |
672 | +# --dry-run and other stuff |
673 | +# ############################################################################# |
674 | + |
675 | +($output, $exit_status) = full_output( |
676 | + sub { pt_online_schema_change::main(@args, |
677 | + "$master_dsn,D=sakila,t=staff", |
678 | + "--alter", "change column first_name first_name_mod varchar(45) NOT NULL, change column last_name last_name_mod varchar(45) NOT NULL", |
679 | + qw(--dry-run --alter-foreign-keys-method auto)) }, |
680 | +); |
681 | + |
682 | +is( |
683 | + $exit_status, |
684 | + 0, |
685 | + "No error with --dry-run" |
686 | +); |
687 | + |
688 | +like( |
689 | + $output, |
690 | + qr/first_name to first_name_mod, last_name to last_name_mod/ms, |
691 | + "--dry-run warns about renaming columns" |
692 | +); |
693 | + |
694 | +# CHANGE COLUMN same_name same_name |
695 | + |
696 | +($output, $exit_status) = full_output( |
697 | + sub { pt_online_schema_change::main(@args, |
698 | + "$master_dsn,D=sakila,t=staff", |
699 | + "--alter", "change column first_name first_name varchar(45) NOT NULL", |
700 | + qw(--execute --alter-foreign-keys-method auto)) }, |
701 | +); |
702 | + |
703 | +unlike( |
704 | + $output, |
705 | + qr/fist_name to fist_name/, |
706 | + "No warning if CHANGE col col" |
707 | +); |
708 | + |
709 | +# ############################################################################# |
710 | +# Done. |
711 | +# ############################################################################# |
712 | +$sb->wipe_clean($master_dbh); |
713 | +ok($sb->ok(), "Sandbox servers") or BAIL_OUT(__FILE__ . " broke the sandbox"); |
714 | +done_testing; |
715 | |
716 | === added file 't/pt-online-schema-change/samples/data-loss-bug-1068562.sql' |
717 | --- t/pt-online-schema-change/samples/data-loss-bug-1068562.sql 1970-01-01 00:00:00 +0000 |
718 | +++ t/pt-online-schema-change/samples/data-loss-bug-1068562.sql 2012-11-08 09:20:24 +0000 |
719 | @@ -0,0 +1,11 @@ |
720 | +drop database if exists bug1068562; |
721 | +create database bug1068562; |
722 | +use bug1068562; |
723 | + |
724 | +CREATE TABLE `simon` ( |
725 | + `id` int(11) NOT NULL, |
726 | + `old_column_name` varchar(255) DEFAULT NULL, |
727 | + PRIMARY KEY (`id`) |
728 | +) ENGINE=InnoDB; |
729 | + |
730 | +insert into simon values (1,'a'),(2,'b'),(3,'c'); |
67 - . "(" . join(', ', map { $q->quote($_) } @common_cols) . ") "
68 + . "(" . join(', ', map { $q->quote($_) } values %renamed_cols) . ") "
69 . "SELECT";
70 - my $select = join(', ', map { $q->quote($_) } @common_cols);
71 + my $select = join(', ', map { $q->quote($_) } keys %renamed_cols);
149 + my $qcols = join(', ', map { $q->quote($_) } values %$cols); $q->quote( $_) } keys %$cols);
150 + my $new_vals = join(', ', map { "NEW.".
I'm not sure if values %renamed_cols and keys %renamed_cols will be reliable and testable since such operations on hashes can/do return the hash keys/values in random order. So this makes testing hard if we're diff'ing output, then "SELECT col1, col2" won't match "SELECT col2, col1". But moreover, I'm wondering if we do keys %renamed_cols and get "SELECT col2, col1" that that might clash with NibbleIterator making "INSERT INTO foo (col1, col2)". -- In short, we need to always list columns in their table order (this was an issue in the past with other tools, and it generally makes things easier for users to see cols in their table order because that's the order they're familiar with).