Percona Toolkit moved to https://jira.percona.com/projects/PT

Merge lp:~percona-toolkit-dev/percona-toolkit/fix-i26211-1058285-821722-implicit-ansi_quotes into lp:percona-toolkit/2.1

  1. fix-i26211-1058285-821722-implicit-ansi_quotes
  2. Merge into 2.1
Proposed by Daniel Nichter
Status: Merged
Approved by: Brian Fraser
Approved revision: 415
Merged at revision: 446
Proposed branch: lp:~percona-toolkit-dev/percona-toolkit/fix-i26211-1058285-821722-implicit-ansi_quotes
Merge into: lp:percona-toolkit/2.1
Prerequisite: lp:~percona-toolkit-dev/percona-toolkit/portable-test-suite
Diff against target: 692 lines (+402/-91)
18 files modified
bin/pt-archiver (+7/-6)
bin/pt-duplicate-key-checker (+7/-6)
bin/pt-find (+7/-6)
bin/pt-heartbeat (+7/-6)
bin/pt-index-usage (+7/-6)
bin/pt-kill (+7/-6)
bin/pt-online-schema-change (+10/-10)
bin/pt-query-advisor (+7/-6)
bin/pt-query-digest (+7/-6)
bin/pt-table-checksum (+7/-6)
bin/pt-table-sync (+7/-6)
bin/pt-table-usage (+7/-6)
bin/pt-upgrade (+7/-6)
lib/TableParser.pm (+13/-8)
t/pt-online-schema-change/ansi_quotes.t (+144/-0)
t/pt-online-schema-change/bugs.t (+0/-1)
t/pt-online-schema-change/samples/issue-26211.sql (+118/-0)
t/pt-online-schema-change/samples/sql-mode-bug-1058285.sql (+33/-0)
To merge this branch: bzr merge lp:~percona-toolkit-dev/percona-toolkit/fix-i26211-1058285-821722-implicit-ansi_quotes
Reviewer Review Type Date Requested Status
Brian Fraser (community) Approve
Daniel Nichter Approve
Review via email: mp+132602@code.launchpad.net

This proposal supersedes a proposal from 2012-10-04.

To post a comment you must log in.
Revision history for this message
Brian Fraser (fraserbn) : Posted in a previous version of this proposal
review: Approve
Revision history for this message
Daniel Nichter (daniel-nichter) wrote : Posted in a previous version of this proposal

I think the customer's particular issue was already fixed in 2.1.4. I've asked someone to verify this.

lp:~percona-toolkit-dev/percona-toolkit/fix-i26211-1058285-821722-implicit-ansi_quotes updated
414. By Daniel Nichter

Test that SQL_MODE isn't changed.

Revision history for this message
Daniel Nichter (daniel-nichter) wrote :

Passes tests.

review: Approve
lp:~percona-toolkit-dev/percona-toolkit/fix-i26211-1058285-821722-implicit-ansi_quotes updated
415. By Brian Fraser

Added a test for the second bug in customer issue 26211

Revision history for this message
Brian Fraser (fraserbn) :
review: Approve

Preview Diff

[H/L] Next/Prev Comment, [J/K] Next/Prev File, [N/P] Next/Prev Hunk
1=== modified file 'bin/pt-archiver'
2--- bin/pt-archiver 2012-10-31 01:12:57 +0000
3+++ bin/pt-archiver 2012-11-01 20:30:30 +0000
4@@ -1564,13 +1564,14 @@
5 my $q = $self->{Quoter};
6
7 my $new_sql_mode
8- = '/*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, '
9- . q{@@SQL_MODE := REPLACE(REPLACE(@@SQL_MODE, 'ANSI_QUOTES', ''), ',,', ','), }
10- . '@OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, '
11- . '@@SQL_QUOTE_SHOW_CREATE := 1 */';
12+ = q{/*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, }
13+ . q{@@SQL_MODE := '', }
14+ . q{@OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, }
15+ . q{@@SQL_QUOTE_SHOW_CREATE := 1 */};
16
17- my $old_sql_mode = '/*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, '
18- . '@@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */';
19+ my $old_sql_mode
20+ = q{/*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, }
21+ . q{@@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */};
22
23 PTDEBUG && _d($new_sql_mode);
24 eval { $dbh->do($new_sql_mode); };
25
26=== modified file 'bin/pt-duplicate-key-checker'
27--- bin/pt-duplicate-key-checker 2012-10-31 09:18:34 +0000
28+++ bin/pt-duplicate-key-checker 2012-11-01 20:30:30 +0000
29@@ -206,13 +206,14 @@
30 my $q = $self->{Quoter};
31
32 my $new_sql_mode
33- = '/*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, '
34- . q{@@SQL_MODE := REPLACE(REPLACE(@@SQL_MODE, 'ANSI_QUOTES', ''), ',,', ','), }
35- . '@OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, '
36- . '@@SQL_QUOTE_SHOW_CREATE := 1 */';
37+ = q{/*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, }
38+ . q{@@SQL_MODE := '', }
39+ . q{@OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, }
40+ . q{@@SQL_QUOTE_SHOW_CREATE := 1 */};
41
42- my $old_sql_mode = '/*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, '
43- . '@@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */';
44+ my $old_sql_mode
45+ = q{/*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, }
46+ . q{@@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */};
47
48 PTDEBUG && _d($new_sql_mode);
49 eval { $dbh->do($new_sql_mode); };
50
51=== modified file 'bin/pt-find'
52--- bin/pt-find 2012-10-31 09:18:34 +0000
53+++ bin/pt-find 2012-11-01 20:30:30 +0000
54@@ -1607,13 +1607,14 @@
55 my $q = $self->{Quoter};
56
57 my $new_sql_mode
58- = '/*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, '
59- . q{@@SQL_MODE := REPLACE(REPLACE(@@SQL_MODE, 'ANSI_QUOTES', ''), ',,', ','), }
60- . '@OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, '
61- . '@@SQL_QUOTE_SHOW_CREATE := 1 */';
62+ = q{/*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, }
63+ . q{@@SQL_MODE := '', }
64+ . q{@OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, }
65+ . q{@@SQL_QUOTE_SHOW_CREATE := 1 */};
66
67- my $old_sql_mode = '/*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, '
68- . '@@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */';
69+ my $old_sql_mode
70+ = q{/*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, }
71+ . q{@@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */};
72
73 PTDEBUG && _d($new_sql_mode);
74 eval { $dbh->do($new_sql_mode); };
75
76=== modified file 'bin/pt-heartbeat'
77--- bin/pt-heartbeat 2012-10-31 09:18:34 +0000
78+++ bin/pt-heartbeat 2012-11-01 20:30:30 +0000
79@@ -2545,13 +2545,14 @@
80 my $q = $self->{Quoter};
81
82 my $new_sql_mode
83- = '/*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, '
84- . q{@@SQL_MODE := REPLACE(REPLACE(@@SQL_MODE, 'ANSI_QUOTES', ''), ',,', ','), }
85- . '@OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, '
86- . '@@SQL_QUOTE_SHOW_CREATE := 1 */';
87+ = q{/*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, }
88+ . q{@@SQL_MODE := '', }
89+ . q{@OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, }
90+ . q{@@SQL_QUOTE_SHOW_CREATE := 1 */};
91
92- my $old_sql_mode = '/*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, '
93- . '@@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */';
94+ my $old_sql_mode
95+ = q{/*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, }
96+ . q{@@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */};
97
98 PTDEBUG && _d($new_sql_mode);
99 eval { $dbh->do($new_sql_mode); };
100
101=== modified file 'bin/pt-index-usage'
102--- bin/pt-index-usage 2012-10-31 09:18:34 +0000
103+++ bin/pt-index-usage 2012-11-01 20:30:30 +0000
104@@ -2805,13 +2805,14 @@
105 my $q = $self->{Quoter};
106
107 my $new_sql_mode
108- = '/*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, '
109- . q{@@SQL_MODE := REPLACE(REPLACE(@@SQL_MODE, 'ANSI_QUOTES', ''), ',,', ','), }
110- . '@OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, '
111- . '@@SQL_QUOTE_SHOW_CREATE := 1 */';
112+ = q{/*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, }
113+ . q{@@SQL_MODE := '', }
114+ . q{@OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, }
115+ . q{@@SQL_QUOTE_SHOW_CREATE := 1 */};
116
117- my $old_sql_mode = '/*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, '
118- . '@@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */';
119+ my $old_sql_mode
120+ = q{/*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, }
121+ . q{@@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */};
122
123 PTDEBUG && _d($new_sql_mode);
124 eval { $dbh->do($new_sql_mode); };
125
126=== modified file 'bin/pt-kill'
127--- bin/pt-kill 2012-10-31 09:18:34 +0000
128+++ bin/pt-kill 2012-11-01 20:30:30 +0000
129@@ -2495,13 +2495,14 @@
130 my $q = $self->{Quoter};
131
132 my $new_sql_mode
133- = '/*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, '
134- . q{@@SQL_MODE := REPLACE(REPLACE(@@SQL_MODE, 'ANSI_QUOTES', ''), ',,', ','), }
135- . '@OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, '
136- . '@@SQL_QUOTE_SHOW_CREATE := 1 */';
137+ = q{/*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, }
138+ . q{@@SQL_MODE := '', }
139+ . q{@OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, }
140+ . q{@@SQL_QUOTE_SHOW_CREATE := 1 */};
141
142- my $old_sql_mode = '/*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, '
143- . '@@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */';
144+ my $old_sql_mode
145+ = q{/*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, }
146+ . q{@@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */};
147
148 PTDEBUG && _d($new_sql_mode);
149 eval { $dbh->do($new_sql_mode); };
150
151=== modified file 'bin/pt-online-schema-change'
152--- bin/pt-online-schema-change 2012-10-31 09:18:34 +0000
153+++ bin/pt-online-schema-change 2012-11-01 20:30:30 +0000
154@@ -2729,13 +2729,14 @@
155 my $q = $self->{Quoter};
156
157 my $new_sql_mode
158- = '/*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, '
159- . q{@@SQL_MODE := REPLACE(REPLACE(@@SQL_MODE, 'ANSI_QUOTES', ''), ',,', ','), }
160- . '@OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, '
161- . '@@SQL_QUOTE_SHOW_CREATE := 1 */';
162+ = q{/*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, }
163+ . q{@@SQL_MODE := '', }
164+ . q{@OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, }
165+ . q{@@SQL_QUOTE_SHOW_CREATE := 1 */};
166
167- my $old_sql_mode = '/*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, '
168- . '@@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */';
169+ my $old_sql_mode
170+ = q{/*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, }
171+ . q{@@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */};
172
173 PTDEBUG && _d($new_sql_mode);
174 eval { $dbh->do($new_sql_mode); };
175@@ -8232,10 +8233,9 @@
176 # the user specified --chunk-size=N on the cmd line, in which
177 # case the max child table size is their specified chunk size
178 # times the fudge factor.
179- my $max_rows
180- = $o->get('dry-run') ? $o->get('chunk-size') * $limit
181- : $chunk_time ? $avg_rate * $chunk_time * $limit
182- : $o->get('chunk-size') * $limit;
183+ my $max_rows = $o->get('dry-run') ? $o->get('chunk-size') * $limit
184+ : $chunk_time && $avg_rate ? $avg_rate * $chunk_time * $limit
185+ : $o->get('chunk-size') * $limit;
186 PTDEBUG && _d('Max allowed child table size:', $max_rows);
187
188 $alter_fk_method = determine_alter_fk_method(
189
190=== modified file 'bin/pt-query-advisor'
191--- bin/pt-query-advisor 2012-10-31 09:18:34 +0000
192+++ bin/pt-query-advisor 2012-11-01 20:30:30 +0000
193@@ -5307,13 +5307,14 @@
194 my $q = $self->{Quoter};
195
196 my $new_sql_mode
197- = '/*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, '
198- . q{@@SQL_MODE := REPLACE(REPLACE(@@SQL_MODE, 'ANSI_QUOTES', ''), ',,', ','), }
199- . '@OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, '
200- . '@@SQL_QUOTE_SHOW_CREATE := 1 */';
201+ = q{/*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, }
202+ . q{@@SQL_MODE := '', }
203+ . q{@OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, }
204+ . q{@@SQL_QUOTE_SHOW_CREATE := 1 */};
205
206- my $old_sql_mode = '/*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, '
207- . '@@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */';
208+ my $old_sql_mode
209+ = q{/*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, }
210+ . q{@@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */};
211
212 PTDEBUG && _d($new_sql_mode);
213 eval { $dbh->do($new_sql_mode); };
214
215=== modified file 'bin/pt-query-digest'
216--- bin/pt-query-digest 2012-10-31 09:18:34 +0000
217+++ bin/pt-query-digest 2012-11-01 20:30:30 +0000
218@@ -8124,13 +8124,14 @@
219 my $q = $self->{Quoter};
220
221 my $new_sql_mode
222- = '/*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, '
223- . q{@@SQL_MODE := REPLACE(REPLACE(@@SQL_MODE, 'ANSI_QUOTES', ''), ',,', ','), }
224- . '@OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, '
225- . '@@SQL_QUOTE_SHOW_CREATE := 1 */';
226+ = q{/*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, }
227+ . q{@@SQL_MODE := '', }
228+ . q{@OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, }
229+ . q{@@SQL_QUOTE_SHOW_CREATE := 1 */};
230
231- my $old_sql_mode = '/*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, '
232- . '@@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */';
233+ my $old_sql_mode
234+ = q{/*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, }
235+ . q{@@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */};
236
237 PTDEBUG && _d($new_sql_mode);
238 eval { $dbh->do($new_sql_mode); };
239
240=== modified file 'bin/pt-table-checksum'
241--- bin/pt-table-checksum 2012-10-31 18:20:54 +0000
242+++ bin/pt-table-checksum 2012-11-01 20:30:30 +0000
243@@ -3709,13 +3709,14 @@
244 my $q = $self->{Quoter};
245
246 my $new_sql_mode
247- = '/*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, '
248- . q{@@SQL_MODE := REPLACE(REPLACE(@@SQL_MODE, 'ANSI_QUOTES', ''), ',,', ','), }
249- . '@OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, '
250- . '@@SQL_QUOTE_SHOW_CREATE := 1 */';
251+ = q{/*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, }
252+ . q{@@SQL_MODE := '', }
253+ . q{@OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, }
254+ . q{@@SQL_QUOTE_SHOW_CREATE := 1 */};
255
256- my $old_sql_mode = '/*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, '
257- . '@@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */';
258+ my $old_sql_mode
259+ = q{/*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, }
260+ . q{@@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */};
261
262 PTDEBUG && _d($new_sql_mode);
263 eval { $dbh->do($new_sql_mode); };
264
265=== modified file 'bin/pt-table-sync'
266--- bin/pt-table-sync 2012-10-30 23:04:22 +0000
267+++ bin/pt-table-sync 2012-11-01 20:30:30 +0000
268@@ -2383,13 +2383,14 @@
269 my $q = $self->{Quoter};
270
271 my $new_sql_mode
272- = '/*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, '
273- . q{@@SQL_MODE := REPLACE(REPLACE(@@SQL_MODE, 'ANSI_QUOTES', ''), ',,', ','), }
274- . '@OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, '
275- . '@@SQL_QUOTE_SHOW_CREATE := 1 */';
276+ = q{/*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, }
277+ . q{@@SQL_MODE := '', }
278+ . q{@OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, }
279+ . q{@@SQL_QUOTE_SHOW_CREATE := 1 */};
280
281- my $old_sql_mode = '/*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, '
282- . '@@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */';
283+ my $old_sql_mode
284+ = q{/*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, }
285+ . q{@@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */};
286
287 PTDEBUG && _d($new_sql_mode);
288 eval { $dbh->do($new_sql_mode); };
289
290=== modified file 'bin/pt-table-usage'
291--- bin/pt-table-usage 2012-10-30 23:04:22 +0000
292+++ bin/pt-table-usage 2012-11-01 20:30:30 +0000
293@@ -5655,13 +5655,14 @@
294 my $q = $self->{Quoter};
295
296 my $new_sql_mode
297- = '/*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, '
298- . q{@@SQL_MODE := REPLACE(REPLACE(@@SQL_MODE, 'ANSI_QUOTES', ''), ',,', ','), }
299- . '@OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, '
300- . '@@SQL_QUOTE_SHOW_CREATE := 1 */';
301+ = q{/*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, }
302+ . q{@@SQL_MODE := '', }
303+ . q{@OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, }
304+ . q{@@SQL_QUOTE_SHOW_CREATE := 1 */};
305
306- my $old_sql_mode = '/*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, '
307- . '@@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */';
308+ my $old_sql_mode
309+ = q{/*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, }
310+ . q{@@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */};
311
312 PTDEBUG && _d($new_sql_mode);
313 eval { $dbh->do($new_sql_mode); };
314
315=== modified file 'bin/pt-upgrade'
316--- bin/pt-upgrade 2012-10-31 01:12:57 +0000
317+++ bin/pt-upgrade 2012-11-01 20:30:30 +0000
318@@ -482,13 +482,14 @@
319 my $q = $self->{Quoter};
320
321 my $new_sql_mode
322- = '/*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, '
323- . q{@@SQL_MODE := REPLACE(REPLACE(@@SQL_MODE, 'ANSI_QUOTES', ''), ',,', ','), }
324- . '@OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, '
325- . '@@SQL_QUOTE_SHOW_CREATE := 1 */';
326+ = q{/*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, }
327+ . q{@@SQL_MODE := '', }
328+ . q{@OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, }
329+ . q{@@SQL_QUOTE_SHOW_CREATE := 1 */};
330
331- my $old_sql_mode = '/*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, '
332- . '@@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */';
333+ my $old_sql_mode
334+ = q{/*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, }
335+ . q{@@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */};
336
337 PTDEBUG && _d($new_sql_mode);
338 eval { $dbh->do($new_sql_mode); };
339
340=== modified file 'lib/TableParser.pm'
341--- lib/TableParser.pm 2012-10-19 23:14:07 +0000
342+++ lib/TableParser.pm 2012-11-01 20:30:30 +0000
343@@ -56,16 +56,21 @@
344 my $q = $self->{Quoter};
345
346 # To ensure a consistent output, we save the current (old) SQL mode,
347- # then set it to the new SQL mode that what we need. When done, even
348- # if an error occurs, we restore the old SQL mode.
349+ # then set it to the new SQL mode that what we need, which is the
350+ # default sql_mode=''. When done, even if an error occurs, we restore
351+ # the old SQL mode. The main thing is that we do not want ANSI_QUOTES
352+ # because there's code all throughout the tools that expect backtick `
353+ # quoted idents, not double-quote " quoted idents. For example:
354+ # https://bugs.launchpad.net/percona-toolkit/+bug/1058285
355 my $new_sql_mode
356- = '/*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, '
357- . q{@@SQL_MODE := REPLACE(REPLACE(@@SQL_MODE, 'ANSI_QUOTES', ''), ',,', ','), }
358- . '@OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, '
359- . '@@SQL_QUOTE_SHOW_CREATE := 1 */';
360+ = q{/*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, }
361+ . q{@@SQL_MODE := '', }
362+ . q{@OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, }
363+ . q{@@SQL_QUOTE_SHOW_CREATE := 1 */};
364
365- my $old_sql_mode = '/*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, '
366- . '@@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */';
367+ my $old_sql_mode
368+ = q{/*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, }
369+ . q{@@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */};
370
371 # Set new SQL mode.
372 PTDEBUG && _d($new_sql_mode);
373
374=== added file 't/pt-online-schema-change/ansi_quotes.t'
375--- t/pt-online-schema-change/ansi_quotes.t 1970-01-01 00:00:00 +0000
376+++ t/pt-online-schema-change/ansi_quotes.t 2012-11-01 20:30:30 +0000
377@@ -0,0 +1,144 @@
378+#!/usr/bin/env perl
379+
380+BEGIN {
381+ die "The PERCONA_TOOLKIT_BRANCH environment variable is not set.\n"
382+ unless $ENV{PERCONA_TOOLKIT_BRANCH} && -d $ENV{PERCONA_TOOLKIT_BRANCH};
383+ unshift @INC, "$ENV{PERCONA_TOOLKIT_BRANCH}/lib";
384+};
385+
386+use strict;
387+use warnings FATAL => 'all';
388+use English qw(-no_match_vars);
389+use Test::More;
390+
391+use Data::Dumper;
392+use PerconaTest;
393+use Sandbox;
394+
395+require "$trunk/bin/pt-online-schema-change";
396+
397+diag(`$trunk/sandbox/stop-sandbox 12348 >/dev/null`);
398+diag(`MODE_ANSI=1 $trunk/sandbox/start-sandbox master 12348 >/dev/null`);
399+
400+my $dp = new DSNParser(opts=>$dsn_opts);
401+my $sb = new Sandbox(basedir => '/tmp', DSNParser => $dp);
402+my $dbh = $sb->get_dbh_for('master1');
403+
404+if ( !$dbh ) {
405+ plan skip_all => 'Cannot connect to sandbox master 12348';
406+}
407+
408+# The sandbox servers run with lock_wait_timeout=3 and it's not dynamic
409+# so we need to specify --lock-wait-timeout=3 else the tool will die.
410+my $master_dsn = 'h=127.1,P=12348,u=msandbox,p=msandbox';
411+my @args = (qw(--lock-wait-timeout 3));
412+my $output;
413+my $exit_status;
414+my $sample = "t/pt-online-schema-change/samples/";
415+
416+# ############################################################################
417+# pt-online-schema-change doesn't work with ANSI_QUOTES + some other sql_modes
418+# https://bugs.launchpad.net/percona-toolkit/+bug/1058285
419+# ############################################################################
420+$sb->load_file('master1', "$sample/sql-mode-bug-1058285.sql");
421+
422+my ($orig_sql_mode) = $dbh->selectrow_array(q{SELECT @@SQL_MODE});
423+is(
424+ $orig_sql_mode,
425+ "REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI,NO_AUTO_VALUE_ON_ZERO",
426+ "SQL_MODE set"
427+);
428+
429+($output, $exit_status) = full_output(
430+ sub { pt_online_schema_change::main(@args,
431+ "$master_dsn,D=issue26211,t=process_model_inst",
432+ "--alter", "ADD COLUMN foo int",
433+ qw(--dry-run --print --alter-foreign-keys-method auto)) },
434+);
435+
436+is(
437+ $exit_status,
438+ 0,
439+ "--dry-run exit 0 (bug 1058285)"
440+);
441+
442+unlike(
443+ $output,
444+ qr/errno: 121/,
445+ "No error 121 (bug 1058285)"
446+);
447+
448+my ($sql_mode) = $dbh->selectrow_array(q{SELECT @@SQL_MODE});
449+is(
450+ $sql_mode,
451+ $orig_sql_mode,
452+ "--dry-run SQL_MODE not changed"
453+);
454+
455+($output, $exit_status) = full_output(
456+ sub { pt_online_schema_change::main(@args,
457+ "$master_dsn,D=issue26211,t=process_model_inst",
458+ "--alter", "ADD COLUMN foo int",
459+ qw(--execute --alter-foreign-keys-method auto)) },
460+);
461+
462+is(
463+ $exit_status,
464+ 0,
465+ "--execute exit 0 (bug 1058285)"
466+);
467+
468+unlike(
469+ $output,
470+ qr/\QI need a max_rows argument/,
471+ "No 'I need a max_rows' error message (bug 1073996)"
472+);
473+
474+# ANSI_QUOTES are on, so it's "foo" not `foo`.
475+my $rows = $dbh->selectrow_arrayref("SHOW CREATE TABLE issue26211.process_model_inst");
476+like(
477+ $rows->[1],
478+ qr/"foo"\s+int/i,
479+ "--alter actually worked (bug 1058285)"
480+);
481+
482+($sql_mode) = $dbh->selectrow_array(q{SELECT @@SQL_MODE});
483+is(
484+ $sql_mode,
485+ $orig_sql_mode,
486+ "--execute SQL_MODE not changed"
487+);
488+
489+# ############################################################################
490+# pt-online-schema-change foreign key error
491+# Customer issue 26211
492+# ############################################################################
493+$sb->load_file('master1', "$sample/issue-26211.sql");
494+
495+my $retval;
496+($output, $retval) = full_output(sub { pt_online_schema_change::main(@args,
497+ '--alter-foreign-keys-method', 'auto',
498+ '--no-check-replication-filters',
499+ '--alter', "ENGINE=InnoDB",
500+ '--execute', "$master_dsn,D=bug_26211,t=prm_inst")});
501+
502+is(
503+ $retval,
504+ 0,
505+ "Issue 26211: Lives ok"
506+) or diag($output);
507+
508+unlike(
509+ $output,
510+ qr/\QI need a max_rows argument/,
511+ "Issue 26211: No error message"
512+);
513+
514+$dbh->do(q{DROP DATABASE IF EXISTS `bug_26211`});
515+
516+# #############################################################################
517+# Done.
518+# #############################################################################
519+diag(`$trunk/sandbox/stop-sandbox 12348 >/dev/null`);
520+ok($sb->ok(), "Sandbox servers") or BAIL_OUT(__FILE__ . " broke the sandbox");
521+done_testing;
522
523=== modified file 't/pt-online-schema-change/bugs.t'
524--- t/pt-online-schema-change/bugs.t 2012-10-11 21:20:53 +0000
525+++ t/pt-online-schema-change/bugs.t 2012-11-01 20:30:30 +0000
526@@ -240,5 +240,4 @@
527 # #############################################################################
528 $sb->wipe_clean($master_dbh);
529 ok($sb->ok(), "Sandbox servers") or BAIL_OUT(__FILE__ . " broke the sandbox");
530-
531 done_testing;
532
533=== added file 't/pt-online-schema-change/samples/issue-26211.sql'
534--- t/pt-online-schema-change/samples/issue-26211.sql 1970-01-01 00:00:00 +0000
535+++ t/pt-online-schema-change/samples/issue-26211.sql 2012-11-01 20:30:30 +0000
536@@ -0,0 +1,118 @@
537+/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
538+/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
539+/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
540+/*!40101 SET NAMES utf8 */;
541+/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
542+/*!40103 SET TIME_ZONE='+00:00' */;
543+/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
544+/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
545+/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
546+/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
547+
548+
549+DROP DATABASE IF EXISTS `bug_26211`;
550+CREATE DATABASE `bug_26211`;
551+USE `bug_26211`;
552+
553+DROP TABLE IF EXISTS `mref`;
554+/*!40101 SET @saved_cs_client = @@character_set_client */;
555+/*!40101 SET character_set_client = utf8 */;
556+CREATE TABLE `mref` (
557+ `M_ID` decimal(10,0) NOT NULL ,
558+ `PR_M_INST_ID` decimal(10,0) NOT NULL ,
559+ KEY `I_M_ID` (`M_ID`),
560+ KEY `I_PR_M_` (`PR_M_INST_ID`),
561+ CONSTRAINT `FK_MREF_REF_PM` FOREIGN KEY (`M_ID`) REFERENCES `pm` (`M_ID`) ON DELETE CASCADE,
562+ CONSTRAINT `FK_MREF_REF_PRMI` FOREIGN KEY (`PR_M_INST_ID`) REFERENCES `prm_inst` (`PR_M_INST_ID`) ON DELETE CASCADE
563+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
564+/*!40101 SET character_set_client = @saved_cs_client */;
565+
566+DROP TABLE IF EXISTS `pm`;
567+/*!40101 SET @saved_cs_client = @@character_set_client */;
568+/*!40101 SET character_set_client = utf8 */;
569+CREATE TABLE `pm` (
570+ `M_ID` decimal(10,0) NOT NULL ,
571+ `P_MR_NUM` varchar(64) NOT NULL ,
572+ `P_NUM` varchar(50) NOT NULL ,
573+ `TYPE` varchar(50) DEFAULT NULL ,
574+ `VERSION` decimal(10,0) NOT NULL ,
575+ `XML` longtext NOT NULL ,
576+ PRIMARY KEY (`M_ID`),
577+ UNIQUE KEY `UK_PM` (`P_MR_NUM`,`P_NUM`),
578+ KEY `I_PM_P_NUM` (`P_NUM`),
579+ CONSTRAINT `FK_PM_REF_P` FOREIGN KEY (`P_NUM`) REFERENCES `p` (`P_NUM`)
580+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
581+/*!40101 SET character_set_client = @saved_cs_client */;
582+
583+DROP TABLE IF EXISTS `p`;
584+/*!40101 SET @saved_cs_client = @@character_set_client */;
585+/*!40101 SET character_set_client = utf8 */;
586+CREATE TABLE `p` (
587+ `P_NUM` varchar(50) NOT NULL ,
588+ `VERSION` decimal(10,0) NOT NULL ,
589+ `TYPE` varchar(32) NOT NULL ,
590+ `PROTECTED` decimal(1,0) NOT NULL ,
591+ `DESCRIPTIONS` varchar(4000) DEFAULT NULL ,
592+ PRIMARY KEY (`P_NUM`)
593+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
594+/*!40101 SET character_set_client = @saved_cs_client */;
595+
596+
597+DROP TABLE IF EXISTS `pr`;
598+/*!40101 SET @saved_cs_client = @@character_set_client */;
599+/*!40101 SET character_set_client = utf8 */;
600+CREATE TABLE `pr` (
601+ `PR_ID` decimal(10,0) NOT NULL ,
602+ `NUM` varchar(64) NOT NULL ,
603+ `HUB_RQD` decimal(1,0) NOT NULL ,
604+ `TP_RQD` decimal(1,0) NOT NULL ,
605+ `TRANS_TYPE_RQD` decimal(1,0) NOT NULL ,
606+ `HUB_LABEL` varchar(255) DEFAULT NULL ,
607+ `TP_LABEL` varchar(255) DEFAULT NULL ,
608+ `TRANS_TYPE_LABEL` varchar(255) DEFAULT NULL ,
609+ `TYPE` varchar(32) NOT NULL ,
610+ `PR_M_FLAG` decimal(1,0) NOT NULL ,
611+ `USER_DEFINED` decimal(1,0) NOT NULL ,
612+ `DESCRIPTIONS` varchar(4000) DEFAULT NULL ,
613+ `SIGNATURE` longtext ,
614+ PRIMARY KEY (`PR_ID`),
615+ KEY `pr_num_index` (`NUM`)
616+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
617+/*!40101 SET character_set_client = @saved_cs_client */;
618+
619+DROP TABLE IF EXISTS `prm`;
620+/*!40101 SET @saved_cs_client = @@character_set_client */;
621+/*!40101 SET character_set_client = utf8 */;
622+CREATE TABLE `prm` (
623+ `PR_M_ID` decimal(10,0) NOT NULL ,
624+ `M_ID` decimal(10,0) NOT NULL ,
625+ `PR_ID` decimal(10,0) DEFAULT NULL ,
626+ `ACTIVE_VERSION` decimal(10,0) DEFAULT NULL ,
627+ `CURRENT_VERSION` decimal(10,0) DEFAULT NULL ,
628+ `ENABLED` decimal(1,0) NOT NULL ,
629+ `NUM` varchar(64) NOT NULL ,
630+ PRIMARY KEY (`PR_M_ID`),
631+ KEY `I_PRM_M_ID` (`M_ID`),
632+ KEY `I_PRM_PR_ID` (`PR_ID`),
633+ KEY `prm_num_indx` (`NUM`),
634+ CONSTRAINT `FK_PMOD_REF_PR` FOREIGN KEY (`PR_ID`) REFERENCES `pr` (`PR_ID`),
635+ CONSTRAINT `FK_PRM_REF_PM` FOREIGN KEY (`M_ID`) REFERENCES `pm` (`M_ID`) ON DELETE CASCADE
636+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
637+/*!40101 SET character_set_client = @saved_cs_client */;
638+
639+DROP TABLE IF EXISTS `prm_inst`;
640+/*!40101 SET @saved_cs_client = @@character_set_client */;
641+/*!40101 SET character_set_client = utf8 */;
642+CREATE TABLE `prm_inst` (
643+ `PR_M_INST_ID` decimal(10,0) NOT NULL,
644+ `VERSION` decimal(10,0) NOT NULL,
645+ `PR_M_ID` decimal(10,0) NOT NULL,
646+ PRIMARY KEY (`PR_M_INST_ID`),
647+ UNIQUE KEY `UK_PRM_INST` (`VERSION`,`PR_M_ID`),
648+ KEY `I_PRM_INST_PR_MODE` (`PR_M_ID`),
649+ CONSTRAINT `FK_PRMI_REF_PRM` FOREIGN KEY (`PR_M_ID`) REFERENCES `prm` (`PR_M_ID`) ON DELETE CASCADE
650+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
651+/*!40101 SET character_set_client = @saved_cs_client */;
652+
653+
654+
655
656=== added file 't/pt-online-schema-change/samples/sql-mode-bug-1058285.sql'
657--- t/pt-online-schema-change/samples/sql-mode-bug-1058285.sql 1970-01-01 00:00:00 +0000
658+++ t/pt-online-schema-change/samples/sql-mode-bug-1058285.sql 2012-11-01 20:30:30 +0000
659@@ -0,0 +1,33 @@
660+DROP DATABASE IF EXISTS `issue26211`;
661+CREATE DATABASE `issue26211`;
662+USE `issue26211`;
663+
664+CREATE TABLE `process_model` (
665+ `PROC_MODEL_ID` decimal(10,0) NOT NULL,
666+ `PKG_MEM_ID` decimal(10,0) NOT NULL,
667+ `PROC_ID` decimal(10,0) DEFAULT NULL,
668+ `ACTIVE_VERSION` decimal(10,0) DEFAULT NULL,
669+ `CURRENT_VERSION` decimal(10,0) DEFAULT NULL,
670+ `ENABLED` decimal(1,0) NOT NULL,
671+ `NAME` varchar(64) NOT NULL,
672+ `COMMENTS` varchar(4000) DEFAULT NULL,
673+ PRIMARY KEY (`PROC_MODEL_ID`),
674+ KEY `I_PROCESS_MODEL_PKG_MEM_ID` (`PKG_MEM_ID`),
675+ KEY `I_PROCESS_MODEL_PROC_ID` (`PROC_ID`),
676+ KEY `procmodel_name_indx` (`NAME`)
677+) ENGINE=InnoDB;
678+
679+CREATE TABLE `process_model_inst` (
680+ `PROC_MODEL_INST_ID` decimal(10,0) NOT NULL,
681+ `VERSION` decimal(10,0) NOT NULL,
682+ `PROC_MODEL_ID` decimal(10,0) NOT NULL,
683+ `DATE_CREATED` datetime NOT NULL,
684+ `CHANGE_NOTE` varchar(4000) DEFAULT NULL,
685+ `XML` longtext NOT NULL,
686+ `AUTHOR` varchar(50) DEFAULT NULL,
687+ `CHECKSUM` varchar(64) DEFAULT NULL,
688+ PRIMARY KEY (`PROC_MODEL_INST_ID`),
689+ UNIQUE KEY `UK_PROCMODEL_INST` (`VERSION`,`PROC_MODEL_ID`),
690+ KEY `I_PROCESS_MODEL_INST_PROC_MODE` (`PROC_MODEL_ID`),
691+ CONSTRAINT `FK_PROCMODINST_REF_PROCMOD` FOREIGN KEY (`PROC_MODEL_ID`) REFERENCES `process_model` (`PROC_MODEL_ID`) ON DELETE CASCADE
692+) ENGINE=InnoDB;

Subscribers

People subscribed via source and target branches