10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
1 |
<?php
|
14.1.1
by Rob Wultsch
Updates to OSC. |
2 |
// Copyright 2004-present Facebook. All Rights Reserved.
|
3 |
||
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
4 |
/*
|
5 |
Copyright 2010 Facebook. All rights reserved.
|
|
6 |
||
7 |
Redistribution and use in source and binary forms, with or without modification,
|
|
8 |
are permitted provided that the following conditions are met:
|
|
9 |
||
10 |
1. Redistributions of source code must retain the above copyright notice, this
|
|
11 |
list of conditions and the following disclaimer.
|
|
12 |
||
13 |
2. Redistributions in binary form must reproduce the above copyright notice,
|
|
14 |
this list of conditions and the following disclaimer in the documentation
|
|
15 |
and/or other materials provided with the distribution.
|
|
16 |
||
17 |
THIS SOFTWARE IS PROVIDED BY Facebook ``AS IS'' AND ANY EXPRESS OR
|
|
18 |
IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF
|
|
19 |
MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO
|
|
20 |
EVENT SHALL <COPYRIGHT HOLDER> OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT,
|
|
21 |
INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING,
|
|
22 |
BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,
|
|
23 |
DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY
|
|
24 |
OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING
|
|
25 |
NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE,
|
|
26 |
EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
|
|
27 |
||
28 |
The views and conclusions contained in the software and documentation are those
|
|
29 |
of the authors and should not be interpreted as representing official policies,
|
|
30 |
either expressed or implied, of Facebook.
|
|
31 |
||
32 |
The following sections in the code have been taken from
|
|
33 |
http://code.google.com/p/openarkkit/ authored by Shlomi Noach and adapted.
|
|
14.1.1
by Rob Wultsch
Updates to OSC. |
34 |
1. Splitting the scan of the original table into multiple scans based on PK
|
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
35 |
ranges. Refer methods initRangeVariables(), refreshRangeStart(),
|
36 |
assignRangeEndVariables(), getRangeStartCondition().
|
|
37 |
The code taken from http://code.google.com/p/openarkkit/ comes with the
|
|
38 |
following license:
|
|
39 |
Copyright (c) 2008-2009, Shlomi Noach
|
|
40 |
All rights reserved.
|
|
41 |
||
42 |
Redistribution and use in source and binary forms, with or without modification,
|
|
43 |
are permitted provided that the following conditions are met:
|
|
44 |
* Redistributions of source code must retain the above copyright notice, this
|
|
45 |
list of conditions and the following disclaimer.
|
|
46 |
* Redistributions in binary form must reproduce the above copyright notice,
|
|
47 |
this list of conditions and the following disclaimer in the documentation
|
|
48 |
and/or other materials provided with the distribution.
|
|
49 |
* Neither the name of the organization nor the names of its contributors may be
|
|
50 |
used to endorse or promote products derived from this software without
|
|
51 |
specific prior written permission.
|
|
52 |
||
53 |
THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
|
|
54 |
AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
|
|
55 |
IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
|
|
56 |
DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR
|
|
57 |
ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
|
|
58 |
(INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
|
|
59 |
LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON
|
|
60 |
ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
|
|
61 |
(INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
|
|
62 |
SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
|
|
63 |
||
64 |
*/
|
|
65 |
||
66 |
/**
|
|
67 |
* OnlineSchemaChange class encapsulates all the steps needed to do online
|
|
68 |
* schema changes. Only public functions by category are
|
|
69 |
* __construct(), execute(), forceCleanup()
|
|
70 |
* static methods getOscLock(), releaseOscLock(), isOscLockHeld()
|
|
14.1.1
by Rob Wultsch
Updates to OSC. |
71 |
* static methods getCleanupTables(), serverCleanup() and dropTable().
|
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
72 |
*
|
73 |
* execute() returns true/false indicating success or failure. In rare
|
|
74 |
* cases where an exception occurs while cleaning up, it may raise an exception.
|
|
75 |
* In that case, caller needs to run OSC with OSC_FLAGS_FORCE_CLEANUP to clean
|
|
76 |
* up triggers/tables/files created by OSC (calling forceCleanup() method would
|
|
77 |
* run OSC with this flag).
|
|
78 |
*
|
|
14.1.1
by Rob Wultsch
Updates to OSC. |
79 |
* IMPORTANT ASSUMPTION:
|
80 |
* Caller needs to check that there are no foreign keys to/from table being
|
|
81 |
* altered and that there are no triggers already defined on the table.
|
|
82 |
*
|
|
83 |
* @author: nponnekanti with some parts authored by Shlomi Noach as noted
|
|
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
84 |
* in the license section above.
|
85 |
*/
|
|
86 |
||
87 |
// Default value for flags is 0.
|
|
88 |
//
|
|
89 |
// OSC_FLAGS_CHECKSUM causes checksum comparison before switching tables.
|
|
90 |
// Checksum is compared while holding table lock. ALso, it only makes sense
|
|
91 |
// in some cases. For example, when adding new column, checksum will NOT
|
|
92 |
// match by design. However when adding index on existing column, or when
|
|
93 |
// rebuilding table without schema changes, checksum is expected to match.
|
|
94 |
//
|
|
95 |
// OSC_FLAGS_ERRORTEST causes exceptions to be raised randomly when executing
|
|
96 |
// a SQL statement in OSC. Useful for error testing.
|
|
97 |
//
|
|
98 |
// OSC_FLAGS_FORCE_CLEANUP causes ONLY cleanup to be done, i.e no schema
|
|
99 |
// change. It cleans up triggers/tables/outfiles left over from prior OSC
|
|
100 |
// run. It treats errors as warnings and proceeds forward rather than stopping
|
|
101 |
// on 1st error.
|
|
14.1.1
by Rob Wultsch
Updates to OSC. |
102 |
|
103 |
// drops renamed original table
|
|
104 |
define('OSC_FLAGS_DROPTABLE', 0x00000001); |
|
105 |
// delete log files as well
|
|
106 |
define('OSC_FLAGS_DELETELOG', 0x00000002); |
|
107 |
// See above.
|
|
108 |
define('OSC_FLAGS_FORCE_CLEANUP', 0x00000004); |
|
109 |
// don't do cleanup (helps testing)
|
|
110 |
define('OSC_FLAGS_NOCLEANUP', 0x00000008); |
|
111 |
// see note above on this flag
|
|
112 |
define('OSC_FLAGS_CHECKSUM', 0x00000010); |
|
113 |
// bypasses some checks on new PK
|
|
114 |
define('OSC_FLAGS_ACCEPTPK', 0x00000020); |
|
115 |
// Allow a column to be dropped
|
|
116 |
define('OSC_FLAGS_DROPCOLUMN', 0x00000040); |
|
117 |
// Turn off logging except errors
|
|
118 |
define('OSC_FLAGS_NOLOG', 0x00000080); |
|
119 |
// Turn off verbose logging
|
|
120 |
define('OSC_FLAGS_COMPACTLOG', 0x00000100); |
|
121 |
// DON'T USE THIS IN PROD.
|
|
122 |
define('OSC_FLAGS_ERRORTEST', 0x00000200); |
|
123 |
// bypasses version check
|
|
124 |
define('OSC_FLAGS_ACCEPT_VERSION', 0x00000400); |
|
125 |
// use upto 54 char prefix of table name in trigger/table names
|
|
126 |
define('OSC_FLAGS_NAMEPREFIX', 0x00000800); |
|
127 |
// run OSC ignoring long xact
|
|
128 |
define('OSC_FLAGS_LONG_XACT_OK', 0x00001000); |
|
129 |
// disable drop/recreate index(5.1)
|
|
130 |
define('OSC_FLAGS_NOREINDEX', 0x00002000); |
|
131 |
// forces usage of newly added PK
|
|
132 |
define('OSC_FLAGS_USE_NEW_PK', 0x00004000); |
|
133 |
// when adding PK or UNIQUE, delete duplicates
|
|
134 |
define('OSC_FLAGS_ELIMINATE_DUPS', 0x00008000); |
|
135 |
// log to stdout in addition to log files
|
|
136 |
define('OSC_FLAGS_LOG_STDOUT', 0x00010000); |
|
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
137 |
|
138 |
||
139 |
// useful for enclosing column names, index names and table names in quotes
|
|
140 |
function quotify($name) { |
|
141 |
return '`'.$name.'`'; |
|
142 |
}
|
|
143 |
||
144 |
class IndexColumnInfo { |
|
145 |
public $name; |
|
146 |
public $prefix; // used when index is on prefix of a column |
|
147 |
||
148 |
// Note that ascending/descending is not currently supported.
|
|
149 |
public function __construct($input_name, $input_prefix) { |
|
150 |
$this->name = $input_name; |
|
151 |
$this->prefix = $input_prefix; |
|
152 |
}
|
|
153 |
}
|
|
154 |
||
155 |
class IndexInfo { |
|
156 |
||
157 |
// No index type because innodb only allows Btree.
|
|
158 |
// Also note that spatial and fulltext indexes are not available in Innodb.
|
|
159 |
public function __construct($input_tablename, $input_indexname, |
|
160 |
$input_non_unique, $input_is_auto_increment) { |
|
161 |
$this->tablename = $input_tablename; |
|
162 |
$this->indexname = $input_indexname; |
|
163 |
$this->nonUnique = $input_non_unique; |
|
164 |
$this->isAutoIncrement = $input_is_auto_increment; |
|
165 |
$this->columns = array(); |
|
166 |
}
|
|
167 |
||
168 |
||
169 |
public function addColumn($input_name, $input_prefix = '') { |
|
170 |
$column = new IndexColumnInfo($input_name, $input_prefix); |
|
171 |
$this->columns[] = $column; |
|
172 |
}
|
|
173 |
||
174 |
public function getCreateSql() { |
|
175 |
||
176 |
$columnlist = ''; |
|
177 |
||
178 |
$comma = ''; // no comma first time |
|
179 |
foreach ($this->columns as $column) { |
|
180 |
$prefix = ($column->prefix == '' ? '' : sprintf('(%d)', $column->prefix)); |
|
181 |
$columnlist .= $comma.$column->name.$prefix; |
|
182 |
$comma = ','; |
|
183 |
}
|
|
184 |
||
185 |
$unique = ($this->nonUnique ? '' : 'unique'); |
|
186 |
$create = sprintf(' ADD %s index %s (%s) ', |
|
187 |
$unique, $this->indexname, $columnlist); |
|
188 |
return $create; |
|
189 |
}
|
|
190 |
||
191 |
public function isPrimary() { |
|
192 |
return ($this->indexname === quotify('PRIMARY')); |
|
193 |
}
|
|
194 |
||
195 |
public function getDropSql() { |
|
196 |
$drop = sprintf('drop index %s on %s', $this->indexname, $this->tablename); |
|
197 |
return $drop; |
|
198 |
}
|
|
199 |
||
200 |
||
201 |
}
|
|
202 |
||
203 |
class OnlineSchemaChange { |
|
204 |
||
205 |
const IDCOLNAME = '_osc_ID_'; |
|
206 |
const DMLCOLNAME = '_osc_dml_type_'; |
|
207 |
||
208 |
// Note that an update that affects PK is mapped as delete followed by insert
|
|
209 |
const DMLTYPE_INSERT = 1; |
|
210 |
const DMLTYPE_DELETE = 2; |
|
211 |
const DMLTYPE_UPDATE = 3; |
|
212 |
||
213 |
const TEMP_TABLE_IDS_TO_EXCLUDE = '__osc_temp_ids_to_exclude'; |
|
214 |
const TEMP_TABLE_IDS_TO_INCLUDE = '__osc_temp_ids_to_include'; |
|
215 |
||
216 |
// we only retry on timeout or deadlock error
|
|
217 |
const LOCK_MAX_ATTEMPTS = 3; |
|
218 |
||
219 |
// Names (such as table name etc) can be maxlen of 64.
|
|
220 |
const LIMITNAME = 64; |
|
221 |
// A value that is at least LIMITNAME + length of prefix that OSC adds when
|
|
222 |
// generating names for triggers and tables.
|
|
223 |
const NOLIMIT = 100; |
|
224 |
||
225 |
// if set, log success cases only when verbose logging is enabled (i.e
|
|
226 |
// OSC_FLAGS_COMPACTLOG is OFF). By default success cases are logged
|
|
227 |
// unless OSC_FLAGS_NOLOG is set globally.
|
|
228 |
const LOGFLAG_VERBOSE = 0x1; |
|
229 |
||
230 |
// if set, treat failure as warning rather than error.
|
|
231 |
// By default, failures are treated as errors unless OSC_FLAGS_FORCE_CLEANUP
|
|
232 |
// is set globally.
|
|
233 |
const LOGFLAG_WARNING = 0x2; |
|
234 |
||
235 |
// when OSC_FLAGS_ERRORTEST is set, raise errors randomly for 1 in 15 cmds
|
|
236 |
const TEST_ERROR_ODDS = 15; |
|
237 |
||
238 |
// the string that OSC gets lock on
|
|
239 |
const LOCK_STRING = "OnlineSchemaChange"; |
|
240 |
||
241 |
// A static method that can be used by other scripts to check if OSC
|
|
242 |
// lock is held.
|
|
243 |
// Returns connection id of the connection that holds OSC lock if any.
|
|
244 |
public static function isOscLockHeld($conn) { |
|
245 |
$lock_command = sprintf("select is_used_lock('%s') as osc_connection_id", |
|
246 |
self::LOCK_STRING); |
|
247 |
$lock_result = mysql_query($lock_command, $conn); |
|
248 |
if (!$lock_result) { |
|
249 |
Throw new Exception("Failed :".$lock_command.mysql_error($conn)); |
|
250 |
}
|
|
251 |
||
252 |
$row = mysql_fetch_assoc($lock_result); |
|
253 |
||
254 |
if (!$row) { |
|
255 |
Throw new Exception("Returned no rows:".$lock_command); |
|
256 |
}
|
|
257 |
||
258 |
return $row['osc_connection_id']; |
|
259 |
}
|
|
260 |
||
261 |
// Returns array of (dbname, tablename) arrays for which OSC cleanup may be
|
|
262 |
// needed. Basically looks for triggers/tables that may have been left behind
|
|
263 |
// by OSC. It does not look for outfiles though as the main use case is
|
|
264 |
// to cleanup tables/triggers that may have been inadvertantly captured in a
|
|
265 |
// db backup, and hence to restored database.
|
|
266 |
public static function getCleanupTables($conn) { |
|
267 |
$q1 = "(select T.table_schema as db, substr(T.table_name, 11) as obj ". |
|
268 |
" from information_schema.tables T ". |
|
269 |
" where T.table_name like '__osc_%')"; |
|
270 |
$q2 = "(select T.trigger_schema as db, substr(T.trigger_name, 11) as obj ". |
|
271 |
" from information_schema.triggers T ". |
|
272 |
" where T.trigger_name like '__osc_%')"; |
|
273 |
$q = $q1." union distinct ".$q2." order by db, obj"; |
|
274 |
||
275 |
$result = mysql_query($q, $conn); |
|
276 |
if (!$result) { |
|
277 |
Throw new Exception("Failed :".$q.mysql_error($conn)); |
|
278 |
}
|
|
279 |
||
280 |
$db_and_tables = array(); |
|
281 |
while ($row = mysql_fetch_assoc($result)) { |
|
282 |
$db_and_tables[] = array('db' => $row['db'], |
|
283 |
'table' => $row['obj']); |
|
284 |
}
|
|
285 |
||
286 |
return $db_and_tables; |
|
287 |
}
|
|
288 |
||
289 |
// Connects to the server identified by $sock, $user, $password and cleans up
|
|
290 |
// any left over tables/triggers left over by OSC in any database.
|
|
291 |
// Main use case is as follows:
|
|
292 |
// If a db is backed up inadvertantly while OSC is running, it may have some
|
|
293 |
// OSC tables/triggers in it and there is a need to remove them.
|
|
294 |
//
|
|
295 |
// $flags is the same as for __construct documented above,
|
|
296 |
// but the main flags of interest for cleanup are OSC_FLAGS_DELETELOG,
|
|
297 |
// OSC_FLAGS_DROPTABLE.
|
|
298 |
//
|
|
299 |
public static function serverCleanup($sock, $user, $password, $flags=0) { |
|
300 |
$conn = self::openConnectionOnly($sock, $user, $password); |
|
14.1.1
by Rob Wultsch
Updates to OSC. |
301 |
OnlineSchemaChange::releaseOscLock($conn); |
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
302 |
|
303 |
$db_and_tables = self::getCleanupTables($conn); |
|
304 |
||
305 |
foreach ($db_and_tables as $db_and_table) { |
|
306 |
$db = $db_and_table['db']; |
|
307 |
$table = $db_and_table['table']; |
|
308 |
$ddl = ''; // empty alter table command as we don't intend to alter |
|
309 |
$osc = new OnlineSchemaChange($sock, $user, $password, $db, $table, $ddl, |
|
310 |
null, OSC_FLAGS_FORCE_CLEANUP|$flags); |
|
311 |
||
312 |
$osc->forceCleanup(); |
|
313 |
}
|
|
314 |
||
315 |
}
|
|
316 |
||
14.1.1
by Rob Wultsch
Updates to OSC. |
317 |
|
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
318 |
/**
|
319 |
$input_sock - socket to use
|
|
320 |
$input_user - username to use to connect
|
|
321 |
$input_password - password to use to connect
|
|
322 |
$input_dbname - database name
|
|
323 |
$input_tablename - table being altered
|
|
14.1.1
by Rob Wultsch
Updates to OSC. |
324 |
$input_createcmd - alter table DDL. See below.
|
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
325 |
$input_outfile_folder - folder for storing outfiles. See below.
|
326 |
$input_flags - various flags described below
|
|
327 |
$input_batchsize_load - batchsize to use when selecting from table to
|
|
328 |
outfiles. Each outfile generated (except last
|
|
329 |
one) will have this many rows.
|
|
330 |
$input_batchsize_replay - transaction size to use during replay phase.
|
|
331 |
Commit after this many single row
|
|
332 |
insert/update/delete commands are replayed.
|
|
333 |
$input_long_xact_time - If OSC finds a long running xact running for
|
|
334 |
this many seconds, it bails out unless
|
|
335 |
OSC_FLAGS_LONG_XACT_OK is set.
|
|
336 |
$input_logfile_folder - folder for storing logfiles. See below.
|
|
14.1.1
by Rob Wultsch
Updates to OSC. |
337 |
$input_linkdir - symlimk support. End with /. See below.
|
338 |
||
339 |
||
340 |
$input_createcmd:
|
|
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
341 |
OSC works by making a copy of the table, doing schema change on the table,
|
14.1.1
by Rob Wultsch
Updates to OSC. |
342 |
replaying changes and swapping the tables. While this input_createcmd
|
343 |
would create the original table, OSC needs to modify to to affect the
|
|
344 |
copytable.
|
|
345 |
||
346 |
It first replaces 'create table ' (case insensitive and with possible
|
|
347 |
multiple spaces before and after table) with 'CREATE TABLE ' (upper case and
|
|
348 |
with exactly one space before and after TABLE).
|
|
349 |
||
350 |
Then it replaces 'CREATE TABLE <originaltable>' with
|
|
351 |
'CREATE TABLE <copytable>'. This is case sensitive replace since table names
|
|
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
352 |
are case sensitive.
|
353 |
||
354 |
While doing the above replaces, if there is no match or > 1 matches, it
|
|
14.1.1
by Rob Wultsch
Updates to OSC. |
355 |
raises exception. (So if u have comments etc which contain 'create table' or
|
356 |
'create table <tablename>', it may find > 1 match and raise exception.
|
|
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
357 |
|
358 |
$input_outfile_folder (end with /):
|
|
359 |
If a folder name is supplied it is used. The folder must exist. Otherwise
|
|
360 |
invalid outfile folder exception is raised. Otherwise, if @@secure_file_priv
|
|
361 |
is non-null, it is used. Otherwise @@datadir/<dbname> folder is used. (It is
|
|
362 |
assumed that @@datadir is not NULL.)
|
|
363 |
||
364 |
$input_logfile_folder (end with /):
|
|
365 |
Used for storing osc log files.
|
|
14.1.1
by Rob Wultsch
Updates to OSC. |
366 |
|
367 |
$input_linkdir (end with /)
|
|
368 |
This can be used to override the default behavior with respect to symlinks.
|
|
369 |
The default behavior is as follows:
|
|
370 |
(a) If the table is not a symlink (i.e is in @@datadir/<dbname>) then the
|
|
371 |
altered table will also not be a symlink.
|
|
372 |
(b) If the table is a symlink and actually lives in folder foo, then the
|
|
373 |
altered table will also be put in same folder.
|
|
374 |
input_linkdir can be specified to override the behavior as follows:
|
|
375 |
(a) To move a table from @@datadir/<dbname> to input_linkdir/<dbname>
|
|
376 |
(b) To move a table from its current location to @@datadir/<dbname>
|
|
377 |
specify linkdir to be same as @@datadir.
|
|
378 |
||
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
379 |
*/
|
14.1.1
by Rob Wultsch
Updates to OSC. |
380 |
public function __construct($input_sock = '', |
381 |
$input_user, |
|
382 |
$input_password, |
|
383 |
$input_dbname, |
|
384 |
$input_tablename, |
|
385 |
$input_createcmd, |
|
386 |
$input_outfile_folder = null, |
|
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
387 |
$input_flags = 0, |
388 |
$input_batchsize_load = 500000, |
|
389 |
$input_batchsize_replay = 500, |
|
390 |
$input_long_xact_time = 30, |
|
14.1.1
by Rob Wultsch
Updates to OSC. |
391 |
$input_backup_user = "backup", |
392 |
$input_logfile_folder = "/var/tmp/", |
|
393 |
$input_linkdir = null) { |
|
394 |
||
395 |
$this->dbname = trim($input_dbname, '`'); // remove quotes if present |
|
396 |
$this->qdbnameq = quotify($this->dbname); // quoted dbname |
|
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
397 |
$this->tablename = trim($input_tablename, '`'); // remove quotes if present |
398 |
$this->qtablenameq = quotify($this->tablename); // quoted tablename |
|
399 |
$this->flags = $input_flags; |
|
400 |
$this->batchsizeLoad = $input_batchsize_load; |
|
401 |
$this->batchsizeReplay = $input_batchsize_replay; |
|
402 |
$this->outfileFolder = $input_outfile_folder; |
|
403 |
$this->logfileFolder = $input_logfile_folder; |
|
14.1.1
by Rob Wultsch
Updates to OSC. |
404 |
$this->backup_user = $input_backup_user; |
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
405 |
$this->longXactTime = $input_long_xact_time; |
14.1.1
by Rob Wultsch
Updates to OSC. |
406 |
$this->symlinkDir = $input_linkdir; |
407 |
if ($this->symlinkDir) { |
|
408 |
// remove spaces
|
|
409 |
$this->symlinkDir = trim($this->symlinkDir); |
|
410 |
// ensure it ends with / but not two //
|
|
411 |
$this->symlinkDir = rtrim($this->symlinkDir, '/').'/'; |
|
412 |
}
|
|
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
413 |
|
14.1.1
by Rob Wultsch
Updates to OSC. |
414 |
$this->createcmd = $input_createcmd; |
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
415 |
|
416 |
// note that this sets up log files. So any errors raised
|
|
417 |
// before this is done won't be logged.
|
|
418 |
$this->initLogFiles(); |
|
419 |
||
14.1.1
by Rob Wultsch
Updates to OSC. |
420 |
// set to IGNORE or empty to add to queries which manipulate the table
|
421 |
$this->ignoredups = $input_flags & OSC_FLAGS_ELIMINATE_DUPS ? 'IGNORE' : ''; |
|
422 |
||
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
423 |
// In all the tables/triggers that OSC creates, keep the tablename
|
424 |
// starting at exactly 11th character so that it is easy to get the
|
|
425 |
// original tablename from the object (i.e prefix is 10 chars).
|
|
426 |
// Mysql allows only 64 characters in names. Adding prefix can make
|
|
427 |
// it longer and cause failure in mysql. Let it fail by default. If
|
|
428 |
// caller has set OSC_FLAGS_NAMEPREFIX, then use prefix of tablename.
|
|
429 |
// However that makes it impossible to construct original tablename
|
|
430 |
// from the name of the object. So methods like getCleanupTables
|
|
431 |
// may not correctly return tablenames.
|
|
432 |
||
433 |
$limit = (($this->flags & OSC_FLAGS_NAMEPREFIX) ? |
|
434 |
self::LIMITNAME : |
|
435 |
self::NOLIMIT); |
|
436 |
||
437 |
// table to capture deltas
|
|
438 |
$this->deltastable = substr('__osc_chg_'.$this->tablename, 0, $limit); |
|
439 |
||
440 |
||
441 |
// trigger names for insert, delete and update
|
|
442 |
$this->insertTrigger = substr('__osc_ins_'.$this->tablename, 0, $limit); |
|
443 |
$this->deleteTrigger = substr('__osc_del_'.$this->tablename, 0, $limit); |
|
444 |
$this->updateTrigger = substr('__osc_upd_'.$this->tablename, 0, $limit); |
|
445 |
||
446 |
// new table name
|
|
447 |
$this->newtablename = substr('__osc_new_'.$this->tablename, 0, $limit); |
|
448 |
$this->renametable = substr('__osc_old_'.$this->tablename, 0, $limit); |
|
449 |
||
450 |
$this->isSlaveStopped = false; |
|
451 |
||
452 |
// open connection as late as possible
|
|
453 |
$this->sock = $input_sock; |
|
454 |
$this->user = $input_user; |
|
455 |
$this->password = $input_password; |
|
456 |
$this->conn = $this->openAndInitConnection(); |
|
457 |
}
|
|
458 |
||
459 |
// this just opens a connection
|
|
14.1.1
by Rob Wultsch
Updates to OSC. |
460 |
protected static function openConnectionOnly($sock, $user, $password) { |
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
461 |
$host_and_sock = 'localhost'.(empty($sock) ? '' : ':'.$sock); |
14.1.1
by Rob Wultsch
Updates to OSC. |
462 |
$conn = mysql_connect($host_and_sock, $user, $password, true); |
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
463 |
if (!$conn) { |
464 |
$error = "Could not connect to localhost using given socket/user/pwd:"; |
|
465 |
Throw new Exception($error.mysql_error()); |
|
466 |
}
|
|
467 |
return $conn; |
|
468 |
}
|
|
469 |
||
470 |
// this opens connection, switches off binlog, gets OSC lock, does a use db
|
|
14.1.1
by Rob Wultsch
Updates to OSC. |
471 |
protected function openAndInitConnection() { |
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
472 |
$this->conn = $this->openConnectionOnly($this->sock, $this->user, |
473 |
$this->password); |
|
474 |
$this->turnOffBinlog(); |
|
14.1.1
by Rob Wultsch
Updates to OSC. |
475 |
$this->setSqlMode(); |
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
476 |
|
14.1.1
by Rob Wultsch
Updates to OSC. |
477 |
$this->executeSqlNoResult('Selecting database', 'use '.$this->qdbnameq); |
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
478 |
|
479 |
// get this lock as soon as possible as this lock is used to
|
|
480 |
// determine if OSC is running on the server.
|
|
481 |
$this->getOscLock($this->conn); |
|
482 |
||
483 |
return $this->conn; |
|
484 |
}
|
|
485 |
||
486 |
// Gets OSC lock. Used within OSC, and possibly other scripts
|
|
487 |
// to prevent OSC from running. Throws exception if lock cannot
|
|
488 |
// be acquired.
|
|
489 |
public static function getOscLock($conn) { |
|
490 |
$lock_command = sprintf("select get_lock('%s', 0) as lockstatus", |
|
491 |
self::LOCK_STRING); |
|
492 |
$lock_result = mysql_query($lock_command, $conn); |
|
493 |
if (!$lock_result) { |
|
494 |
Throw new Exception("GET_LOCK failed:".mysql_error($conn)); |
|
495 |
}
|
|
496 |
||
497 |
$row = mysql_fetch_assoc($lock_result); |
|
498 |
||
499 |
if (!$row) { |
|
500 |
Throw new Exception("GET_LOCK returned no rows"); |
|
501 |
}
|
|
502 |
||
503 |
if ($row['lockstatus'] != 1) { |
|
504 |
Throw new Exception("GET_LOCK returned ".$row['lockstatus']); |
|
505 |
}
|
|
506 |
||
507 |
}
|
|
508 |
||
509 |
// Releases OSC lock. Does not return anything.
|
|
510 |
// Throws exception if release_lock statement fails, such as if connection
|
|
511 |
// is not valid. However, if lock was not held, it just silently returns.
|
|
512 |
public static function releaseOscLock($conn) { |
|
513 |
$lock_command = sprintf("do release_lock('%s')", |
|
514 |
self::LOCK_STRING); |
|
515 |
$lock_result = mysql_query($lock_command, $conn); |
|
516 |
if (!$lock_result) { |
|
517 |
Throw new Exception("RELEASE_LOCK failed:".mysql_error($conn)); |
|
518 |
}
|
|
14.1.1
by Rob Wultsch
Updates to OSC. |
519 |
if($pid = OnlineSchemaChange::isOscLockHeld($conn)) { |
520 |
$kill_command = sprintf("kill %s", $pid); |
|
521 |
$kill_result = mysql_query($kill_command, $conn); |
|
522 |
if (!$kill_result) { |
|
523 |
Throw new Exception("RELEASE_LOCK failed:".mysql_error($conn)); |
|
524 |
}
|
|
525 |
}
|
|
526 |
}
|
|
527 |
||
528 |
// Connect to the server identified by $sock, $user, $password and drop
|
|
529 |
// table specified by by $table. If the table is partitioned we will drop
|
|
530 |
// a patition at a time in order to avoid performance issues associated with
|
|
531 |
// dropping all partitions at the same time.
|
|
532 |
public static function dropTable($tablename, $conn) { |
|
533 |
$show_query = "SHOW CREATE TABLE `$tablename`"; |
|
534 |
if( !$show_result = @mysql_query($show_query, $conn)) { |
|
535 |
return; |
|
536 |
}
|
|
537 |
$tbl_dfn = mysql_fetch_array($show_result); |
|
538 |
$partitions = array(); |
|
539 |
// Cycle through each partition and delete them one at a time
|
|
540 |
if (preg_match_all("/PARTITION ([^ ]+) VALUES/", $tbl_dfn[1], $partitions)){ |
|
541 |
$partitions = $partitions[1]; |
|
542 |
// length(table) - 1 otherwise we leave a paritioned table with no
|
|
543 |
// partitions, which MySQL errors on.
|
|
544 |
array_pop($partitions); |
|
545 |
foreach ($partitions as $partition) { |
|
546 |
// Intentionally ignoring any issues.
|
|
547 |
$drop_query = "ALTER TABLE `$tablename` DROP PARTITION `$partition`"; |
|
548 |
mysql_query($drop_query,$conn); |
|
549 |
}
|
|
550 |
}
|
|
551 |
// Intentionally ignoring any issues. We sometimes call
|
|
552 |
// drop table unnecessarily.
|
|
553 |
$drop_query = "DROP TABLE `$tablename`"; |
|
554 |
@mysql_query($drop_query,$conn); |
|
555 |
}
|
|
556 |
||
557 |
||
558 |
||
559 |
||
560 |
protected function raiseException($error_prefix, $sqlfailed = true) { |
|
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
561 |
$error = $error_prefix.($sqlfailed ? mysql_error($this->conn) : ''); |
562 |
$errno = ($sqlfailed ? mysql_errno($this->conn) : 0); |
|
563 |
$logtext = sprintf("Exception: errno:-%d, error:-%s\n", $errno, $error); |
|
564 |
$this->logError($logtext); |
|
565 |
Throw new Exception($error, $errno); |
|
566 |
}
|
|
567 |
||
14.1.1
by Rob Wultsch
Updates to OSC. |
568 |
protected function raiseNonSqlException($error_prefix) { |
569 |
$this->raiseException($error_prefix, false); |
|
570 |
}
|
|
571 |
||
572 |
protected function getSlaveStatus() { |
|
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
573 |
$query = 'show slave status'; |
574 |
||
575 |
if (!($result = mysql_query($query, $this->conn))) { |
|
576 |
$this->raiseException('Failed to get slave status'); |
|
577 |
}
|
|
578 |
||
579 |
// if rows are returned, it means we are running on a slave
|
|
580 |
if ($row = mysql_fetch_assoc($result)) { |
|
581 |
return (($row['Slave_IO_Running'] == "Yes") && |
|
582 |
($row['Slave_SQL_Running'] == "Yes")); |
|
583 |
} else { |
|
584 |
// not configured as slave.
|
|
585 |
return false; |
|
586 |
}
|
|
587 |
}
|
|
588 |
||
589 |
// if slave is running, then stop it
|
|
14.1.1
by Rob Wultsch
Updates to OSC. |
590 |
protected function stopSlave() { |
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
591 |
if ($this->getSlaveStatus()) { |
592 |
$this->executeSqlNoResult('stopping slave', 'stop slave'); |
|
593 |
$this->isSlaveStopped = true; |
|
594 |
}
|
|
595 |
}
|
|
596 |
||
597 |
// we start slave only if we stopped it
|
|
14.1.1
by Rob Wultsch
Updates to OSC. |
598 |
protected function startSlave() { |
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
599 |
if ($this->isSlaveStopped) { |
600 |
$this->isSlaveStopped = false; |
|
601 |
$this->executeSqlNoResult('starting slave', 'start slave'); |
|
602 |
}
|
|
603 |
}
|
|
604 |
||
605 |
// wrapper around unlink
|
|
14.1.1
by Rob Wultsch
Updates to OSC. |
606 |
protected function executeUnlink($file, $check_if_exists = false) { |
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
607 |
$this->logCompact("--Deleting file:".$file."\n"); |
608 |
||
609 |
if (($check_if_exists || ($this->flags & OSC_FLAGS_FORCE_CLEANUP)) && |
|
610 |
!file_exists($file)) { |
|
611 |
return true; |
|
612 |
}
|
|
613 |
||
14.1.1
by Rob Wultsch
Updates to OSC. |
614 |
if(!file_exists($file)){ |
615 |
$this->logWarning("File ".$file." does not exist\n"); |
|
616 |
return false; |
|
617 |
}
|
|
618 |
||
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
619 |
if (!unlink($file)) { |
620 |
if ($this->flags & OSC_FLAGS_FORCE_CLEANUP) { |
|
621 |
// log and move on
|
|
622 |
$this->logWarning("Could not delete file:".$file."\n"); |
|
623 |
return false; |
|
624 |
} else { |
|
625 |
$this->raiseException('Could not delete file:'.$file, false); |
|
626 |
}
|
|
627 |
}
|
|
628 |
return true; |
|
629 |
}
|
|
630 |
||
14.1.1
by Rob Wultsch
Updates to OSC. |
631 |
protected function executeShellCmd($description, $cmd) { |
632 |
||
633 |
$cmd = escapeshellcmd($cmd); |
|
634 |
$output = array(); |
|
635 |
$status = 0; |
|
636 |
exec($cmd, $output, $status); |
|
637 |
||
638 |
$this->logCompact("$description cmd=$cmd status = $status\n"); |
|
639 |
foreach ($output as $outputline) { |
|
640 |
$this->logVerbose("$description cmd output line : $outputline\n"); |
|
641 |
}
|
|
642 |
||
643 |
if ($status !== 0) { |
|
644 |
$this->raiseNonSqlException("$description cmd $cmd returned $status\n"); |
|
645 |
}
|
|
646 |
||
647 |
}
|
|
648 |
||
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
649 |
// wrapper around mysql_query
|
650 |
// used for sql commands for which we don't have a resultset
|
|
651 |
// logflags is used to specify:
|
|
652 |
// whether to log always (default) or only in verbose mode (LOGFLAG_VERBOSE)
|
|
653 |
// whether failure is error (default) or warning (LOGFLAG_WARNING)
|
|
14.1.1
by Rob Wultsch
Updates to OSC. |
654 |
protected function executeSqlNoResult($sql_description, $sql, |
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
655 |
$logflags = 0) { |
656 |
$logtext = "--".$sql_description."\n".$sql.";\n"; |
|
657 |
if ($logflags & self::LOGFLAG_VERBOSE) { |
|
658 |
$this->logVerbose($logtext) ; |
|
659 |
} else { |
|
660 |
$this->logCompact($logtext); |
|
661 |
}
|
|
662 |
||
663 |
if ($this->flags & OSC_FLAGS_ERRORTEST) { |
|
664 |
$odds = self::TEST_ERROR_ODDS; |
|
665 |
// reduce odds for commands that are more frequent as indicated by
|
|
666 |
// LOGFLAG_VERBOSE
|
|
667 |
$odds = (($logflags & self::LOGFLAG_VERBOSE) ? pow($odds, 4) : $odds); |
|
668 |
$fail = mt_rand(1, $odds); |
|
669 |
if ($fail === $odds) { |
|
670 |
$this->raiseException("Faking error ".$sql_description.":".$sql, false); |
|
671 |
}
|
|
672 |
}
|
|
673 |
||
14.1.1
by Rob Wultsch
Updates to OSC. |
674 |
if (!@mysql_query($sql, $this->conn)) { |
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
675 |
$error = mysql_error($this->conn); |
676 |
if (($this->flags & OSC_FLAGS_FORCE_CLEANUP) || |
|
677 |
($logflags & self::LOGFLAG_WARNING)) { |
|
678 |
// log error and move on
|
|
679 |
$this->logWarning("WARNING: SQL :-".$sql.". Error :-".$error."\n"); |
|
680 |
return false; |
|
681 |
} else { |
|
682 |
$this->raiseException($sql_description.' failed. SQL:-'.$sql.'.'); |
|
683 |
}
|
|
684 |
}
|
|
685 |
||
686 |
return true; |
|
687 |
}
|
|
688 |
||
14.1.1
by Rob Wultsch
Updates to OSC. |
689 |
protected function turnOffBinlog() { |
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
690 |
$this->executeSqlNoResult('Turning binlog off', 'SET sql_log_bin = 0'); |
691 |
}
|
|
692 |
||
14.1.1
by Rob Wultsch
Updates to OSC. |
693 |
protected function setSqlMode() { |
694 |
$this->executeSqlNoResult('Setting sql_mode to STRICT_ALL_TABLES', |
|
695 |
'SET sql_mode = STRICT_ALL_TABLES'); |
|
696 |
}
|
|
697 |
||
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
698 |
// some header info that is useful to log
|
14.1.1
by Rob Wultsch
Updates to OSC. |
699 |
protected function getOSCHeader() { |
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
700 |
$logtext = "--OSC info: time=%s, db=%s, table=%s, flags=%x\n". |
14.1.1
by Rob Wultsch
Updates to OSC. |
701 |
"--CREATE=%s\n"; |
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
702 |
$logtext = sprintf($logtext, date('c'), $this->dbname, $this->tablename, |
14.1.1
by Rob Wultsch
Updates to OSC. |
703 |
$this->flags, $this->createcmd); |
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
704 |
return $logtext; |
705 |
}
|
|
706 |
||
707 |
// logs stuff only when verbose logging is enabled i.e both
|
|
708 |
// OSC_FLAGS_NOLOG and OSC_FLAGS_COMPACTLOG are OFF.
|
|
709 |
// Use methods logWarning/logError for warnings/errors.
|
|
14.1.1
by Rob Wultsch
Updates to OSC. |
710 |
protected function logVerbose($logtext) { |
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
711 |
if ($this->flags & (OSC_FLAGS_NOLOG|OSC_FLAGS_COMPACTLOG)) { |
712 |
return; |
|
713 |
}
|
|
714 |
$this->logCompact($logtext); |
|
715 |
}
|
|
716 |
||
717 |
// logs stuff unless OSC_FLAGS_NOLOG is set.
|
|
718 |
// Use methods logWarning/logError for warnings/errors.
|
|
14.1.1
by Rob Wultsch
Updates to OSC. |
719 |
protected function logCompact($logtext) { |
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
720 |
if (empty($this->oscLogFP)) { |
721 |
echo "Attempt to log before log file pointer setup:".$logtext."\n!"; |
|
722 |
return; |
|
723 |
}
|
|
14.1.1
by Rob Wultsch
Updates to OSC. |
724 |
$timestamp = date('Y-m-d G:i:s'); |
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
725 |
|
726 |
if ($this->flags & OSC_FLAGS_NOLOG) { |
|
727 |
return; |
|
728 |
}
|
|
14.1.1
by Rob Wultsch
Updates to OSC. |
729 |
fwrite($this->oscLogFP, $timestamp . ' ' . $logtext); |
730 |
||
731 |
if ($this->flags & OSC_FLAGS_LOG_STDOUT) { |
|
732 |
print "$timestamp $logtext\n"; |
|
733 |
}
|
|
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
734 |
}
|
735 |
||
736 |
// used for logging warnings
|
|
14.1.1
by Rob Wultsch
Updates to OSC. |
737 |
protected function logWarning($logtext) { |
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
738 |
if (empty($this->oscWarningFP)) { |
739 |
echo "Attempt to log before warning file pointer setup:".$logtext."\n!"; |
|
740 |
return; |
|
741 |
}
|
|
14.1.1
by Rob Wultsch
Updates to OSC. |
742 |
|
743 |
if ($this->flags & OSC_FLAGS_LOG_STDOUT) { |
|
744 |
print "$logtext\n"; |
|
745 |
}
|
|
746 |
||
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
747 |
// since we don't expect many warnings, put header info in every warning
|
748 |
fwrite($this->oscWarningFP, $this->getOSCHeader()); |
|
749 |
fwrite($this->oscWarningFP, $logtext); |
|
750 |
}
|
|
751 |
||
752 |
// used for logging errors
|
|
14.1.1
by Rob Wultsch
Updates to OSC. |
753 |
protected function logError($logtext) { |
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
754 |
if (empty($this->oscErrorFP)) { |
755 |
echo "Attempt to log before error file pointer setup:".$logtext."\n!"; |
|
756 |
return; |
|
757 |
}
|
|
14.1.1
by Rob Wultsch
Updates to OSC. |
758 |
|
759 |
if ($this->flags & OSC_FLAGS_LOG_STDOUT) { |
|
760 |
print "$logtext\n"; |
|
761 |
}
|
|
762 |
||
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
763 |
// since we don't expect many errors, put header info in every error
|
764 |
fwrite($this->oscErrorFP, $this->getOSCHeader()); |
|
765 |
fwrite($this->oscErrorFP, $logtext); |
|
766 |
}
|
|
767 |
||
14.1.1
by Rob Wultsch
Updates to OSC. |
768 |
|
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
769 |
|
770 |
// Retrieves column names of table being altered and stores in array.
|
|
771 |
// Stores PK columns, non-PK columns and all columns in separate arrays.
|
|
14.1.1
by Rob Wultsch
Updates to OSC. |
772 |
protected function initColumnNameArrays() { |
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
773 |
$this->columnarray = array(); |
774 |
$this->pkcolumnarray = array(); |
|
775 |
$this->nonpkarray = array(); |
|
776 |
$this->nonpkcolumns = ''; |
|
14.1.1
by Rob Wultsch
Updates to OSC. |
777 |
|
778 |
// get list of columns in new table
|
|
779 |
$query = "select column_name ". |
|
780 |
"from information_schema.columns ". |
|
781 |
"where table_name ='%s' and table_schema='%s'"; |
|
782 |
$query = sprintf($query, $this->newtablename, $this->dbname); |
|
783 |
if (!($result = mysql_query($query, $this->conn))) { |
|
784 |
$this->raiseException('Initializing column names failed.'.$query); |
|
785 |
}
|
|
786 |
$newcolumns = array(); |
|
787 |
while ($row = mysql_fetch_assoc($result)) { |
|
788 |
$newcolumns[] = $row['column_name']; |
|
789 |
}
|
|
790 |
||
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
791 |
$query = "select column_name, column_key, extra ". |
792 |
"from information_schema.columns ". |
|
793 |
"where table_name ='%s' and table_schema='%s'"; |
|
794 |
$query = sprintf($query, $this->tablename, $this->dbname); |
|
795 |
||
796 |
if (!($result = mysql_query($query, $this->conn))) { |
|
797 |
$this->raiseException('Initializing column names failed.'.$query); |
|
798 |
}
|
|
799 |
||
800 |
$comma = ''; // no comma first time |
|
801 |
while ($row = mysql_fetch_assoc($result)) { |
|
14.1.1
by Rob Wultsch
Updates to OSC. |
802 |
// column must have been dropped from new table, skip it
|
803 |
if (!in_array($row['column_name'], $newcolumns)) { |
|
804 |
continue; |
|
805 |
}
|
|
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
806 |
$column_name = quotify($row['column_name']); |
807 |
$this->columnarray[] = $column_name; |
|
808 |
// there should be atmost one autoincrement column
|
|
809 |
if (stripos($row['extra'], 'auto_increment') !== false) { |
|
14.1.1
by Rob Wultsch
Updates to OSC. |
810 |
if (isset($this->autoIncrement)) { |
811 |
$err = sprintf("Two auto_increment cols: %s, %s", |
|
812 |
$this->autoIncrement, $column_name); |
|
813 |
$this->raiseException($err, false); |
|
814 |
}
|
|
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
815 |
$this->autoIncrement = $column_name; |
816 |
}
|
|
817 |
if ($row['column_key'] != 'PRI') { |
|
818 |
$this->nonpkarray[] = $column_name; |
|
819 |
$this->nonpkcolumns .= $comma.$column_name; |
|
820 |
$comma = ','; |
|
821 |
}
|
|
822 |
}
|
|
823 |
||
824 |
// for PK columns we need them to be in correct order as well.
|
|
825 |
$query = "select * from information_schema.statistics ". |
|
826 |
"where table_name = '%s' and TABLE_SCHEMA = '%s' ". |
|
827 |
" and INDEX_NAME = 'PRIMARY' ". |
|
828 |
"order by INDEX_NAME, SEQ_IN_INDEX"; |
|
829 |
||
830 |
$query = sprintf($query, $this->tablename, $this->dbname); |
|
831 |
||
832 |
if (!($result = mysql_query($query, $this->conn))) { |
|
833 |
$this->raiseException('Failed to get PK column info '.$query); |
|
834 |
}
|
|
835 |
||
836 |
$this->pkcolumnarray = array(); |
|
837 |
while ($row = mysql_fetch_assoc($result)) { |
|
838 |
$this->pkcolumnarray[] = quotify($row['COLUMN_NAME']); |
|
839 |
}
|
|
840 |
||
14.1.1
by Rob Wultsch
Updates to OSC. |
841 |
if (!($this->flags & OSC_FLAGS_USE_NEW_PK) && |
842 |
count($this->pkcolumnarray) === 0) { |
|
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
843 |
$this->raiseException("No primary key defined on the table!", false); |
844 |
}
|
|
845 |
||
846 |
}
|
|
847 |
||
848 |
// This is dependent on initColumnNameArray().
|
|
849 |
// Uses the array of column names created by the former function
|
|
14.1.1
by Rob Wultsch
Updates to OSC. |
850 |
// to come up with a string of comma separated columns.
|
851 |
// It also builds strings of comma separated columns where each column is
|
|
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
852 |
// prefixed with "NEW." and "OLD.".
|
14.1.1
by Rob Wultsch
Updates to OSC. |
853 |
protected function initColumnNameStrings() { |
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
854 |
$this->columns = ''; |
855 |
$this->oldcolumns = ''; |
|
856 |
$this->newcolumns = ''; |
|
14.1.1
by Rob Wultsch
Updates to OSC. |
857 |
$this->pkcolumns = ''; |
858 |
$this->oldpkcolumns = ''; |
|
859 |
$this->newpkcolumns = ''; |
|
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
860 |
$comma = ''; // no comma at the front |
861 |
||
862 |
foreach ($this->columnarray as $column) { |
|
863 |
$this->columns .= $comma.$column; |
|
864 |
$this->oldcolumns .= $comma.'OLD.'.$column; |
|
865 |
$this->newcolumns .= $comma.'NEW.'.$column; |
|
866 |
$comma = ', '; // add comma from 2nd column |
|
867 |
}
|
|
868 |
||
869 |
$comma = ''; // no comma at the front |
|
870 |
||
871 |
foreach ($this->pkcolumnarray as $column) { |
|
872 |
$this->pkcolumns .= $comma.$column; |
|
873 |
$this->oldpkcolumns .= $comma.'OLD.'.$column; |
|
874 |
$this->newpkcolumns .= $comma.'NEW.'.$column; |
|
875 |
$comma = ', '; // add comma from 2nd column |
|
876 |
}
|
|
877 |
||
878 |
}
|
|
879 |
||
14.1.1
by Rob Wultsch
Updates to OSC. |
880 |
protected function initRangeVariables() { |
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
881 |
$count = count($this->pkcolumnarray); |
882 |
$comma = ''; // no comma first time |
|
883 |
||
884 |
$this->rangeStartVars = ''; |
|
885 |
$this->rangeStartVarsArray = array(); |
|
886 |
$this->rangeEndVars = ''; |
|
887 |
$this->rangeEndVarsArray = array(); |
|
888 |
||
889 |
for ($i = 0; $i < $count; $i++) { |
|
890 |
$startvar = sprintf("@range_start_%d", $i); |
|
891 |
$endvar = sprintf("@range_end_%d", $i); |
|
892 |
$this->rangeStartVars .= $comma.$startvar; |
|
893 |
$this->rangeEndVars .= $comma.$endvar; |
|
894 |
$this->rangeStartVarsArray[] = $startvar; |
|
895 |
$this->rangeEndVarsArray[] = $endvar; |
|
896 |
||
897 |
$comma = ','; |
|
898 |
}
|
|
899 |
}
|
|
900 |
||
14.1.1
by Rob Wultsch
Updates to OSC. |
901 |
protected function refreshRangeStart() { |
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
902 |
$query = sprintf(" SELECT %s INTO %s ", |
903 |
$this->rangeEndVars, $this->rangeStartVars); |
|
904 |
$this->executeSqlNoResult('Refreshing range start', $query); |
|
905 |
}
|
|
906 |
||
14.1.1
by Rob Wultsch
Updates to OSC. |
907 |
protected function initLogFiles() { |
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
908 |
if (!empty($this->logfileFolder) && !file_exists($this->logfileFolder)) { |
909 |
$this->raiseException("Invalid logfile folder ".$this->logfileFolder, |
|
910 |
false); |
|
911 |
}
|
|
912 |
||
913 |
// where the osc log files go
|
|
914 |
$this->oscLogFilePrefix = $this->logfileFolder.'__osclog_'.$this->tablename; |
|
915 |
$this->oscLogFP = fopen($this->oscLogFilePrefix.".log", 'a'); |
|
916 |
$this->oscWarningFP = fopen($this->oscLogFilePrefix.".wrn", 'a'); |
|
917 |
$this->oscErrorFP = fopen($this->oscLogFilePrefix.".err", 'a'); |
|
918 |
fwrite($this->oscLogFP, $this->getOSCHeader()); |
|
919 |
}
|
|
920 |
||
14.1.1
by Rob Wultsch
Updates to OSC. |
921 |
// If non-null input_linkdir was passed to contructor, it gets validated
|
922 |
// here. A new directory for database may also be created with proper
|
|
923 |
// ownership.
|
|
924 |
protected function checkInputSymlink() { |
|
925 |
if (empty($this->symlinkDir)) { |
|
926 |
return; |
|
927 |
}
|
|
928 |
||
929 |
$dbdir = $this->getDataDir().$this->dbname.'/'; |
|
930 |
||
931 |
if (($this->symlinkDir == $this->getDataDir()) || |
|
932 |
($this->symlinkDir == $dbdir)) { |
|
933 |
// we don't need to create a symlink in this case.
|
|
934 |
// They just want to move the table to @@datadir.
|
|
935 |
return; |
|
936 |
}
|
|
937 |
||
938 |
if (!is_dir($this->symlinkDir)) { |
|
939 |
$this->raiseNonSqlException("Invalid symlink dir ".$this->symlinkDir); |
|
940 |
}
|
|
941 |
||
942 |
// Create directory for the database under the symlink folder if it
|
|
943 |
// does not already exist
|
|
944 |
$newdir = $this->symlinkDir.$this->dbname; |
|
945 |
$this->symlinkDir = $newdir.'/'; |
|
946 |
||
947 |
if (is_dir($newdir)) { |
|
948 |
return; |
|
949 |
}
|
|
950 |
||
951 |
$cmd = "sudo -u mysql mkdir $newdir"; |
|
952 |
$this->executeShellCmd("Mkdir", $cmd); |
|
953 |
||
954 |
if (!chmod($newdir, 0700)) { |
|
955 |
$this->raiseNonSqlException("chmod of $newdir to 0700 failed"); |
|
956 |
}
|
|
957 |
||
958 |
}
|
|
959 |
||
960 |
// If null input_linkdir was passed to constructor, this checks if the
|
|
961 |
// table is a symlink and if so, sets up $this->symlinkDir to point to
|
|
962 |
// the dir in which table ibd file actually lives.
|
|
963 |
protected function checkTableSymlink() { |
|
964 |
if (!empty($this->symlinkDir)) { |
|
965 |
return; |
|
966 |
}
|
|
967 |
||
968 |
// we need to figure out if we need to create a symlink by checking if
|
|
969 |
// table is currently a symlink
|
|
970 |
$dbdir = $this->getDataDir().$this->dbname.'/'; |
|
971 |
$tablefile = $dbdir.$this->tablename.".ibd"; |
|
972 |
||
973 |
if (!file_exists($tablefile)) { |
|
974 |
// can't find the file
|
|
975 |
// either IBD in wrong place, or table isn't a separate file
|
|
976 |
$this->logWarning("Could not locate ibd file $tablefile"); |
|
977 |
return; |
|
978 |
}
|
|
979 |
||
980 |
if (!is_link($tablefile)) { |
|
981 |
return; |
|
982 |
}
|
|
983 |
||
984 |
$link = readlink($tablefile); |
|
985 |
if (!$link) { |
|
986 |
$this->raiseNonSqlException("Readlink on $tablefile fails!"); |
|
987 |
}
|
|
988 |
||
989 |
$targetdir = dirname($link); |
|
990 |
||
991 |
if (!$targetdir) { |
|
992 |
$this->raiseNonSqlException("Could not get directory of link $link!"); |
|
993 |
}
|
|
994 |
||
995 |
// add / at the end
|
|
996 |
$targetdir .= "/"; |
|
997 |
$this->symlinkDir = $targetdir; |
|
998 |
}
|
|
999 |
||
1000 |
// Wrapper function to call
|
|
1001 |
// checkInputSymlink() if OSC caller supplied a input_linkdir (this means
|
|
1002 |
// caller wants to move the table to that dir)
|
|
1003 |
// OR
|
|
1004 |
// checkTableSymlink() if OSC caller did not supply input_linkdir (this
|
|
1005 |
// means just preserve table location i.e if newtable will live in same
|
|
1006 |
// dir as current table lives in)
|
|
1007 |
//
|
|
1008 |
// Symlink feature was implemented in 5.1.52 and so do nothing if it is
|
|
1009 |
// earlier version.
|
|
1010 |
protected function checkSymlink() { |
|
1011 |
if ($this->version < "5.1.52") { |
|
1012 |
return; |
|
1013 |
}
|
|
1014 |
||
1015 |
empty($this->symlinkDir) ? |
|
1016 |
$this->checkTableSymlink() : |
|
1017 |
$this->checkInputSymlink(); |
|
1018 |
||
1019 |
$this->logCompact("symlinkDir has been set to $this->symlinkDir\n"); |
|
1020 |
}
|
|
1021 |
||
1022 |
||
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
1023 |
// Initializes names of files (names only and not contents) to be used as
|
1024 |
// OUTFILE targets in SELECT INTO
|
|
14.1.1
by Rob Wultsch
Updates to OSC. |
1025 |
protected function initOutfileNames() { |
1026 |
$this->checkSymlink(); |
|
1027 |
||
1028 |
// create outfiles in symlink dir if it exists
|
|
1029 |
if (empty($this->outfileFolder)) { |
|
1030 |
$this->outfileFolder = $this->symlinkDir; |
|
1031 |
}
|
|
1032 |
||
1033 |
||
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
1034 |
if (!empty($this->outfileFolder) && !file_exists($this->outfileFolder)) { |
1035 |
$this->raiseException("Invalid outfile folder ".$this->outfileFolder, |
|
1036 |
false); |
|
1037 |
}
|
|
1038 |
||
1039 |
// if no folder specified for outfiles use @@secure_file_priv
|
|
1040 |
if (empty($this->outfileFolder)) { |
|
1041 |
||
1042 |
$query = 'select @@secure_file_priv as folder'; |
|
1043 |
||
1044 |
if (!($result = mysql_query($query, $this->conn))) { |
|
1045 |
$this->raiseException('Failed to get secure-file-priv system variable'); |
|
1046 |
}
|
|
1047 |
||
1048 |
// we expect only one row
|
|
1049 |
while ($row = mysql_fetch_assoc($result)) { |
|
1050 |
$this->outfileFolder = $row['folder']; |
|
1051 |
}
|
|
14.1.1
by Rob Wultsch
Updates to OSC. |
1052 |
|
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
1053 |
}
|
1054 |
||
1055 |
// if @@secure_file_priv is also empty, use @@datadir
|
|
1056 |
if (empty($this->outfileFolder)) { |
|
14.1.1
by Rob Wultsch
Updates to OSC. |
1057 |
$this->outfileFolder = $this->getDataDir(); |
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
1058 |
|
1059 |
// Add folder for this database
|
|
1060 |
$this->outfileFolder .= $this->dbname.'/'; |
|
1061 |
||
14.1.1
by Rob Wultsch
Updates to OSC. |
1062 |
} else { |
1063 |
// Make sure it ends with / but don't add two /
|
|
1064 |
$this->outfileFolder = rtrim($this->outfileFolder, '/').'/'; |
|
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
1065 |
}
|
1066 |
||
1067 |
$this->outfileTable = $this->outfileFolder.'__osc_tbl_'.$this->tablename; |
|
1068 |
$this->outfileExcludeIDs = $this->outfileFolder. |
|
1069 |
'__osc_ex_'.$this->tablename; |
|
1070 |
$this->outfileIncludeIDs = $this->outfileFolder. |
|
1071 |
'__osc_in_'.$this->tablename; |
|
1072 |
}
|
|
1073 |
||
1074 |
// this should be called after validateVersion
|
|
14.1.1
by Rob Wultsch
Updates to OSC. |
1075 |
protected function decideIfReindex() { |
1076 |
// If OSC does reindexing optimization and server crashes during OSC,
|
|
1077 |
// mysql bug http://bugs.mysql.com/bug.php?id=53256 causes server restart
|
|
1078 |
// to fail.
|
|
1079 |
// Facebook 5.1.52 has the fix and so do reindexing optimization unless
|
|
14.1.2
by Rob Wultsch
Removed fb internal docs. Not particularly useful outside of the fb environment. |
1080 |
// explicitly disabled.
|
14.1.1
by Rob Wultsch
Updates to OSC. |
1081 |
return (($this->version >= "5.1.52") && |
1082 |
!($this->flags & OSC_FLAGS_NOREINDEX)); |
|
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
1083 |
}
|
1084 |
||
14.1.1
by Rob Wultsch
Updates to OSC. |
1085 |
protected function validateVersion() { |
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
1086 |
$query = 'select version() as version'; |
1087 |
if (!($result = mysql_query($query, $this->conn))) { |
|
1088 |
$this->raiseException('Failed to get version'); |
|
1089 |
}
|
|
1090 |
||
1091 |
// we expect only one row
|
|
1092 |
while ($row = mysql_fetch_assoc($result)) { |
|
1093 |
$version = $row['version']; |
|
1094 |
}
|
|
1095 |
||
1096 |
$version_major = strtok($version, "."); |
|
1097 |
$version_minor = strtok("."); |
|
1098 |
$version_mini = strtok("_"); |
|
1099 |
||
1100 |
$this->version = sprintf("%s.%s.%s", $version_major, $version_minor, |
|
1101 |
$version_mini); |
|
1102 |
||
1103 |
if ((!($this->flags & OSC_FLAGS_ACCEPT_VERSION)) && |
|
14.1.1
by Rob Wultsch
Updates to OSC. |
1104 |
(($this->version < "5.0.84") || ($this->version > "5.1.63"))) { |
1105 |
$error = "OSC has only been tested on versions 5.0.84, 5.1.47, 5.1.50 ". |
|
1106 |
"and 5.1.52. Running on ".$this->version." is not allowed ". |
|
1107 |
"unless OSC_FLAGS_ACCEPT_VERSION flag is set."; |
|
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
1108 |
$this->raiseException($error, false); |
1109 |
}
|
|
1110 |
return $this->version; |
|
1111 |
}
|
|
1112 |
||
1113 |
// checks for long running xact
|
|
14.1.1
by Rob Wultsch
Updates to OSC. |
1114 |
protected function checkLongXact() { |
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
1115 |
if ($this->flags & OSC_FLAGS_LONG_XACT_OK) { |
1116 |
return; |
|
1117 |
}
|
|
1118 |
||
1119 |
$query = "show full processlist"; |
|
1120 |
if (!($result = mysql_query($query, $this->conn))) { |
|
1121 |
$this->raiseException('Failed to get process list info '.$query); |
|
1122 |
}
|
|
1123 |
||
1124 |
$msg = ''; |
|
1125 |
$count = 0; |
|
1126 |
while ($row = mysql_fetch_assoc($result)) { |
|
1127 |
if ((empty($row['Time']) || ($row['Time'] < $this->longXactTime)) || |
|
1128 |
($row['db'] !== $this->dbname) || ($row['Command'] === 'Sleep')) { |
|
1129 |
continue; |
|
1130 |
}
|
|
1131 |
$count++; |
|
1132 |
$buf = "Id=%d,user=%s,host=%s,db=%s,Command=%s,Time=%d,Info=%s\n"; |
|
1133 |
$msg .= sprintf($buf, $row['Id'], $row['User'], $row['Host'], $row['db'], |
|
1134 |
$row['Command'], $row['Time'], $row['Info']) ; |
|
1135 |
}
|
|
1136 |
||
1137 |
if ($count > 0) { |
|
1138 |
$msg = sprintf("%d long running xact(s) found.\n".$msg, $count); |
|
1139 |
$this->raiseException($msg, false); |
|
1140 |
}
|
|
1141 |
||
1142 |
}
|
|
1143 |
||
14.1.1
by Rob Wultsch
Updates to OSC. |
1144 |
protected function init() { |
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
1145 |
|
1146 |
// store pkcolumns, all columns and nonpk columns
|
|
1147 |
$this->initColumnNameArrays(); |
|
1148 |
$this->initColumnNameStrings(); |
|
1149 |
||
1150 |
$this->reindex = $this->decideIfReindex(); |
|
1151 |
||
1152 |
$this->initRangeVariables(); |
|
14.1.1
by Rob Wultsch
Updates to OSC. |
1153 |
$this->initIndexes(); |
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
1154 |
|
1155 |
}
|
|
1156 |
||
1157 |
// creates a table (called deltas table) to capture changes to the table
|
|
1158 |
// being processed during the course of schema change.
|
|
14.1.1
by Rob Wultsch
Updates to OSC. |
1159 |
protected function createDeltasTable() { |
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
1160 |
// deltas has an IDCOLNAME, DMLCOLNAME, and all columns as original table
|
1161 |
$createtable = 'create table %s'. |
|
1162 |
'(%s INT AUTO_INCREMENT, %s INT, primary key(%s)) '. |
|
14.1.1
by Rob Wultsch
Updates to OSC. |
1163 |
'ENGINE=InnoDB '. |
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
1164 |
'as (select %s from %s LIMIT 0)'; |
1165 |
$createtable = sprintf($createtable, $this->deltastable, |
|
1166 |
self::IDCOLNAME, self::DMLCOLNAME, self::IDCOLNAME, |
|
1167 |
$this->columns, $this->qtablenameq); |
|
1168 |
||
1169 |
$this->executeSqlNoResult('Creating deltas table', $createtable); |
|
1170 |
$this->cleanupDeltastable = true; |
|
1171 |
}
|
|
1172 |
||
1173 |
// creates insert trigger to capture all inserts in deltas table
|
|
14.1.1
by Rob Wultsch
Updates to OSC. |
1174 |
protected function createInsertTrigger() { |
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
1175 |
$trigger = 'create trigger %s AFTER INSERT ON %s FOR EACH ROW '. |
1176 |
'insert into %s(%s, %s) '. |
|
1177 |
'values (%d, %s)'; |
|
1178 |
$trigger = sprintf($trigger, $this->insertTrigger, $this->qtablenameq, |
|
1179 |
$this->deltastable, self::DMLCOLNAME, $this->columns, |
|
1180 |
self::DMLTYPE_INSERT, $this->newcolumns); |
|
1181 |
$this->executeSqlNoResult('Creating insert trigger', $trigger); |
|
1182 |
$this->cleanupInsertTrigger = true; |
|
1183 |
}
|
|
1184 |
||
1185 |
// Creates delete trigger to capture all deletes in deltas table
|
|
14.1.1
by Rob Wultsch
Updates to OSC. |
1186 |
// We must dump all columns or else we will encounter issues with
|
1187 |
// columns which are NOT NULL and lack a default
|
|
1188 |
protected function createDeleteTrigger() { |
|
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
1189 |
$trigger = 'create trigger %s AFTER DELETE ON %s FOR EACH ROW '. |
1190 |
'insert into %s(%s, %s) '. |
|
1191 |
'values (%d, %s)'; |
|
1192 |
$trigger = sprintf($trigger, $this->deleteTrigger, $this->qtablenameq, |
|
14.1.1
by Rob Wultsch
Updates to OSC. |
1193 |
$this->deltastable, self::DMLCOLNAME, $this->columns, |
1194 |
self::DMLTYPE_DELETE, $this->oldcolumns); |
|
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
1195 |
$this->executeSqlNoResult('Creating delete trigger', $trigger); |
1196 |
$this->cleanupDeleteTrigger = true; |
|
1197 |
}
|
|
1198 |
||
1199 |
// creates update trigger to capture all updates in deltas table
|
|
14.1.1
by Rob Wultsch
Updates to OSC. |
1200 |
protected function createUpdateTrigger() { |
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
1201 |
// if primary key is updated, map the update to delete followed by insert
|
1202 |
$trigger = 'create trigger %s AFTER UPDATE ON %s FOR EACH ROW '. |
|
1203 |
'IF (%s) THEN '. |
|
1204 |
' insert into %s(%s, %s) '. |
|
1205 |
' values(%d, %s); '. |
|
1206 |
'ELSE '. |
|
1207 |
' insert into %s(%s, %s) '. |
|
1208 |
' values(%d, %s), '. |
|
1209 |
' (%d, %s); '. |
|
1210 |
'END IF'; |
|
1211 |
$trigger = sprintf($trigger, $this->updateTrigger, $this->qtablenameq, |
|
1212 |
$this->getMatchCondition('NEW', 'OLD'), |
|
1213 |
$this->deltastable, self::DMLCOLNAME, $this->columns, |
|
1214 |
self::DMLTYPE_UPDATE, $this->newcolumns, |
|
1215 |
$this->deltastable, self::DMLCOLNAME, $this->columns, |
|
1216 |
self::DMLTYPE_DELETE, $this->oldcolumns, |
|
1217 |
self::DMLTYPE_INSERT, $this->newcolumns); |
|
1218 |
||
1219 |
$this->executeSqlNoResult('Creating update trigger', $trigger); |
|
1220 |
$this->cleanupUpdateTrigger = true; |
|
1221 |
}
|
|
1222 |
||
1223 |
/**
|
|
14.1.1
by Rob Wultsch
Updates to OSC. |
1224 |
* The function exists because if lock table is run against a
|
1225 |
* table being backed up, then the table will be locked until
|
|
1226 |
* the end of the dump. If that happens then Online Schema Change
|
|
1227 |
* is not so online
|
|
1228 |
*/
|
|
1229 |
protected function killSelects($table) { |
|
1230 |
$sql = "SHOW FULL PROCESSLIST "; |
|
1231 |
if (!($result = mysql_query($sql, $this->conn))) { |
|
1232 |
$this->raiseException('Failed to get backup connections'); |
|
1233 |
}
|
|
1234 |
||
1235 |
while ($row = mysql_fetch_array($result) ) { |
|
1236 |
||
1237 |
if( $row['db'] == $this->dbname && |
|
1238 |
$row['User'] == $this->backup_user && |
|
1239 |
stripos($row['Info'], 'SELECT ') !== FALSE && |
|
1240 |
stripos($row['Info'],'INFORMATION_SCHEMA') === FALSE){ |
|
1241 |
$kill = sprintf("KILL %s",$row[0]); |
|
1242 |
// Note, we should not throw an exception if the kill fails.
|
|
1243 |
// The connection might have gone away on its own.
|
|
1244 |
$this->executeSqlNoResult("Killing dump query",$kill, |
|
1245 |
self::LOGFLAG_WARNING); |
|
1246 |
}
|
|
1247 |
}
|
|
1248 |
}
|
|
1249 |
||
1250 |
||
1251 |
/**
|
|
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
1252 |
* Important Assumption: Retrying on deadlock/timeout error assumes
|
1253 |
* that lock tables is the first step in a transaction. Otherwise
|
|
1254 |
* other locks acquired prior to lock tables could be released and it
|
|
1255 |
* won't make sense to just retry lock tables.
|
|
1256 |
*/
|
|
14.1.1
by Rob Wultsch
Updates to OSC. |
1257 |
protected function lockTables($lock_reason, $lock_both_tables) { |
1258 |
$this->killSelects($this->qtablenameq); |
|
1259 |
||
1260 |
if($lock_both_tables) { |
|
1261 |
$this->killSelects($this->newtablename); |
|
1262 |
$lock = sprintf('lock table %s WRITE, %s WRITE', |
|
1263 |
$this->qtablenameq, $this->newtablename); |
|
1264 |
} else { |
|
1265 |
$lock = sprintf('lock table %s WRITE', $this->qtablenameq); |
|
1266 |
}
|
|
1267 |
||
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
1268 |
$i = 0; |
1269 |
$logflags = ((++$i < self::LOCK_MAX_ATTEMPTS) ? self::LOGFLAG_WARNING : 0); |
|
1270 |
while (!$this->executeSqlNoResult($lock_reason, $lock, $logflags)) { |
|
1271 |
$errno = mysql_errno($this->conn); |
|
1272 |
$error = mysql_error($this->conn); |
|
1273 |
// 1205 is timeout and 1213 is deadlock
|
|
1274 |
if (($errno == 1205) || ($errno == 1213) || |
|
1275 |
($this->flags & OSC_FLAGS_ERRORTEST)) { |
|
1276 |
$logflags = ((++$i < self::LOCK_MAX_ATTEMPTS) ? |
|
1277 |
self::LOGFLAG_WARNING : 0); |
|
1278 |
continue; |
|
1279 |
}
|
|
1280 |
// unknown error
|
|
1281 |
$this->raiseException($lock_reason.' failed. SQL:-'.$lock); |
|
1282 |
}
|
|
1283 |
||
1284 |
}
|
|
1285 |
||
14.1.1
by Rob Wultsch
Updates to OSC. |
1286 |
protected function createTriggers() { |
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
1287 |
$this->stopSlave(); |
1288 |
||
1289 |
// without turning off autocommit lock tables is not working
|
|
1290 |
$this->executeSqlNoResult('AUTOCOMMIT OFF', 'set session autocommit=0'); |
|
1291 |
||
1292 |
// In 5.0 version creating a trigger after locking a table causes hang.
|
|
1293 |
// So we will lock a little later.
|
|
14.1.1
by Rob Wultsch
Updates to OSC. |
1294 |
// Refer intern/wiki/index.php/Database/Online_Schema_Change_Testing and
|
1295 |
// search for workaround for more info.
|
|
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
1296 |
if ($this->version !== "5.0.84") { |
1297 |
// false means lock only original table
|
|
1298 |
$this->lockTables('LOCKING table to drain pre-trigger Xacts', false); |
|
1299 |
}
|
|
1300 |
||
1301 |
$this->createInsertTrigger(); |
|
1302 |
||
1303 |
$this->createDeleteTrigger(); |
|
1304 |
||
1305 |
$this->createUpdateTrigger(); |
|
1306 |
||
1307 |
// for other version we have already locked above.
|
|
1308 |
if ($this->version === "5.0.84") { |
|
1309 |
// false means lock only original table
|
|
1310 |
$this->lockTables('LOCKING table to drain pre-trigger Xacts', false); |
|
1311 |
}
|
|
1312 |
||
1313 |
||
1314 |
$this->executeSqlNoResult('COMMITTING', 'COMMIT'); |
|
1315 |
||
1316 |
$this->executeSqlNoResult('Unlocking after triggers', 'unlock tables'); |
|
1317 |
||
1318 |
$this->executeSqlNoResult('AUTOCOMMIT ON', 'set session autocommit=1'); |
|
1319 |
||
1320 |
$this->startSlave(); |
|
1321 |
||
1322 |
}
|
|
1323 |
||
1324 |
||
1325 |
// Used for creating temp tables for IDs to exclude or IDs to include
|
|
14.1.1
by Rob Wultsch
Updates to OSC. |
1326 |
protected function createAndInitTemptable($temptable) { |
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
1327 |
if ($temptable === self::TEMP_TABLE_IDS_TO_EXCLUDE) { |
1328 |
$outfile = $this->outfileExcludeIDs; |
|
1329 |
||
1330 |
$selectinto = "select %s, %s ". |
|
1331 |
"from %s ". |
|
1332 |
"order by %s into outfile '%s' "; |
|
1333 |
$selectinto = sprintf($selectinto, |
|
1334 |
self::IDCOLNAME, self::DMLCOLNAME, |
|
1335 |
$this->deltastable, |
|
1336 |
self::IDCOLNAME, $outfile); |
|
1337 |
||
1338 |
} else if ($temptable === self::TEMP_TABLE_IDS_TO_INCLUDE) { |
|
1339 |
$outfile = $this->outfileIncludeIDs; |
|
1340 |
||
1341 |
// Select from deltastable that are not in TEMP_TABLE_IDS_TO_EXCLUDE.
|
|
1342 |
// Use left outer join rather than 'in' subquery for better perf.
|
|
1343 |
$idcol = $this->deltastable.'.'.self::IDCOLNAME; |
|
1344 |
$dmlcol = $this->deltastable.'.'.self::DMLCOLNAME; |
|
1345 |
$idcol2 = self::TEMP_TABLE_IDS_TO_EXCLUDE.'.'.self::IDCOLNAME; |
|
1346 |
$selectinto = "select %s, %s ". |
|
1347 |
"from %s LEFT JOIN %s ON %s = %s ". |
|
1348 |
"where %s is null order by %s into outfile '%s' "; |
|
1349 |
$selectinto = sprintf($selectinto, $idcol, $dmlcol, |
|
1350 |
$this->deltastable, self::TEMP_TABLE_IDS_TO_EXCLUDE, |
|
1351 |
$idcol, $idcol2, $idcol2, $idcol, $outfile); |
|
1352 |
} else { |
|
14.1.1
by Rob Wultsch
Updates to OSC. |
1353 |
$this->raiseException("Invalid param temptable : $temptable", false); |
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
1354 |
}
|
1355 |
||
1356 |
$this->executeSqlNoResult('Selecting ids from deltas to outfile', |
|
1357 |
$selectinto); |
|
1358 |
||
1359 |
$this->cleanupOutfile = $outfile; |
|
1360 |
||
1361 |
||
14.1.1
by Rob Wultsch
Updates to OSC. |
1362 |
$createtemp = 'create temporary table %s(%s INT, |
1363 |
%s INT,
|
|
1364 |
primary key(%s)) engine=myisam'; |
|
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
1365 |
$createtemp = sprintf($createtemp, $temptable, |
1366 |
self::IDCOLNAME, self::DMLCOLNAME, self::IDCOLNAME); |
|
1367 |
$this->executeSqlNoResult('Creating temp table for ids to exclude', |
|
1368 |
$createtemp); |
|
1369 |
||
1370 |
// read from outfile above into the temp table
|
|
1371 |
$loadsql = sprintf("LOAD DATA INFILE '%s' INTO TABLE %s(%s, %s)", |
|
1372 |
$outfile, $temptable, self::IDCOLNAME, self::DMLCOLNAME); |
|
1373 |
$this->executeSqlNoResult('Loading ids to exclude ', $loadsql); |
|
1374 |
||
1375 |
unset($this->cleanupOutfile); |
|
1376 |
$this->executeUnlink($outfile); |
|
1377 |
}
|
|
1378 |
||
14.1.1
by Rob Wultsch
Updates to OSC. |
1379 |
protected function startSnapshotXact() { |
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
1380 |
|
1381 |
$this->executeSqlNoResult('starting transaction', |
|
1382 |
'START TRANSACTION WITH CONSISTENT SNAPSHOT'); |
|
1383 |
// any deltas captured so far need to be excluded because they would
|
|
1384 |
// already be reflected in our snapshot.
|
|
1385 |
$this->createAndInitTemptable(self::TEMP_TABLE_IDS_TO_EXCLUDE); |
|
1386 |
||
1387 |
}
|
|
1388 |
||
1389 |
// Generates assignment condition of the form
|
|
1390 |
// @var1 := col1, @var2 := col2, ....
|
|
14.1.1
by Rob Wultsch
Updates to OSC. |
1391 |
protected function assignRangeEndVariables($columns, $variables) { |
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
1392 |
if (!$columns) { |
1393 |
return ''; |
|
1394 |
}
|
|
1395 |
$count = count($columns); |
|
1396 |
$comma = ''; // no comma first time |
|
1397 |
$assign = ''; |
|
1398 |
for ($i = 0; $i < $count; $i++) { |
|
1399 |
$assign .= $comma.sprintf('%s := %s', $variables[$i], $columns[$i]); |
|
1400 |
$comma = ','; |
|
1401 |
}
|
|
1402 |
return $assign; |
|
1403 |
}
|
|
1404 |
||
1405 |
/**
|
|
1406 |
Given a list of columns and a list of values (of same length), produce a
|
|
1407 |
'greater than' SQL condition by splitting into multiple conditions.
|
|
1408 |
An example result may look like:
|
|
1409 |
((col1 > val1) OR
|
|
1410 |
((col1 = val1) AND (col2 > val2)) OR
|
|
1411 |
((col1 = val1) AND (col2 = val2) AND (col3 > val3)))
|
|
1412 |
Which stands for (col1, col2, col3) > (val1, val2, val3).
|
|
1413 |
The latter being simple in representation, however MySQL does not utilize
|
|
1414 |
keys properly with this form of condition, hence the splitting into multiple
|
|
1415 |
conditions.
|
|
1416 |
It can also be used for >=, < or <= but we don't need them now.
|
|
1417 |
*/
|
|
14.1.1
by Rob Wultsch
Updates to OSC. |
1418 |
protected function getRangeStartCondition($columns, $values, |
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
1419 |
$comparison_sign = ">") { |
1420 |
$comparison = ''; |
|
1421 |
$count = count($columns); |
|
1422 |
$equality = ''; |
|
1423 |
$range = ''; |
|
1424 |
$and = ''; // no AND first time |
|
1425 |
$or = ''; |
|
1426 |
for ($i = 0; $i < $count; $i++) { |
|
1427 |
// compare condition for this column
|
|
1428 |
$range = sprintf(" %s %s %s ", $columns[$i], $comparison_sign, |
|
1429 |
$values[$i]); |
|
1430 |
||
1431 |
// equality comparison for all previous columns
|
|
1432 |
if ($i > 0) { |
|
1433 |
$equality .= $and.sprintf(" %s = %s ", $columns[$i-1], $values[$i-1]); |
|
1434 |
$and = ' AND '; |
|
1435 |
}
|
|
1436 |
||
1437 |
// Note that when $i is 0, both $equality and $and will be empty
|
|
1438 |
$comparison .= $or.'('.$equality.$and.$range.')'; |
|
1439 |
$or = ' OR '; |
|
1440 |
}
|
|
1441 |
// enclose in ()
|
|
1442 |
return sprintf('(%s)', $comparison); |
|
1443 |
}
|
|
1444 |
||
14.1.1
by Rob Wultsch
Updates to OSC. |
1445 |
protected function selectFullTableIntoOutfile() { |
1446 |
||
1447 |
$selectinto = "select %s ". |
|
1448 |
"FROM %s ". |
|
1449 |
"INTO OUTFILE '%s.1'"; |
|
1450 |
||
1451 |
$selectinto = sprintf($selectinto, $this->columns, |
|
1452 |
$this->qtablenameq, $this->outfileTable); |
|
1453 |
||
1454 |
||
1455 |
$this->executeSqlNoResult('Selecting full table into outfile', |
|
1456 |
$selectinto); |
|
1457 |
||
1458 |
$this->outfileSuffixStart = 1; |
|
1459 |
$this->outfileSuffixEnd = 1; |
|
1460 |
||
1461 |
$this->executeSqlNoResult('Committing after generating outfiles', 'COMMIT'); |
|
1462 |
}
|
|
1463 |
||
1464 |
protected function selectTableIntoOutfile() { |
|
1465 |
// we want to do the full table dump/load since we can't page
|
|
1466 |
if ($this->flags & OSC_FLAGS_USE_NEW_PK) { |
|
1467 |
return $this->selectFullTableIntoOutfile(); |
|
1468 |
}
|
|
1469 |
||
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
1470 |
$whereclause = ''; // first time there is no where clause |
1471 |
||
1472 |
$outfile_suffix = 0; |
|
1473 |
||
1474 |
do { |
|
1475 |
$outfile_suffix++; // start with 1 |
|
1476 |
||
1477 |
$selectinto = "select %s, %s ". |
|
14.1.1
by Rob Wultsch
Updates to OSC. |
1478 |
"FROM %s FORCE INDEX (PRIMARY) %s ". |
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
1479 |
"ORDER BY %s LIMIT %d ". |
1480 |
"INTO OUTFILE '%s.%d'"; |
|
1481 |
||
1482 |
// this gets pk column values into range end variables
|
|
1483 |
$assign = $this->assignRangeEndVariables($this->pkcolumnarray, |
|
1484 |
$this->rangeEndVarsArray); |
|
1485 |
$selectinto = sprintf($selectinto, $assign, $this->nonpkcolumns, |
|
1486 |
$this->qtablenameq, $whereclause, |
|
1487 |
$this->pkcolumns, $this->batchsizeLoad, |
|
1488 |
$this->outfileTable, $outfile_suffix); |
|
1489 |
$this->executeSqlNoResult('Selecting table into outfile', $selectinto); |
|
1490 |
$this->outfileSuffixStart = 1; |
|
1491 |
$this->outfileSuffixEnd = $outfile_suffix; |
|
1492 |
$rowCount = mysql_affected_rows($this->conn); |
|
1493 |
||
1494 |
$this->refreshRangeStart(); |
|
1495 |
$range = $this->getRangeStartCondition($this->pkcolumnarray, |
|
1496 |
$this->rangeStartVarsArray); |
|
1497 |
$whereclause = sprintf(" WHERE %s ", $range); |
|
1498 |
} while ($rowCount >= $this->batchsizeLoad); |
|
1499 |
||
1500 |
$this->executeSqlNoResult('Committing after generating outfiles', 'COMMIT'); |
|
1501 |
}
|
|
1502 |
||
14.1.1
by Rob Wultsch
Updates to OSC. |
1503 |
// gets @@datadir into $this->dataDir and returns it as well
|
1504 |
// ensures that datadir ends with /
|
|
1505 |
protected function getDataDir() { |
|
1506 |
if (!empty($this->dataDir)) { |
|
1507 |
return $this->dataDir; |
|
1508 |
}
|
|
1509 |
||
1510 |
$query = 'select @@datadir as dir'; |
|
1511 |
||
1512 |
if (!($result = mysql_query($query, $this->conn))) { |
|
1513 |
$this->raiseException('Failed to get datadir system variable'); |
|
1514 |
}
|
|
1515 |
||
1516 |
// we expect only one row
|
|
1517 |
while ($row = mysql_fetch_assoc($result)) { |
|
1518 |
// add / at the end but only if it does not already have one
|
|
1519 |
$this->dataDir = $row['dir']; |
|
1520 |
if (empty($this->dataDir)) { |
|
1521 |
$this->raiseException("Datadir is empty", false); |
|
1522 |
} else { |
|
1523 |
// Make sure it ends with / but don't add two /
|
|
1524 |
$this->dataDir = rtrim($this->dataDir, '/').'/'; |
|
1525 |
}
|
|
1526 |
||
1527 |
return $this->dataDir; |
|
1528 |
}
|
|
1529 |
||
1530 |
$this->raiseException("Query to get datadir returned no rows"); |
|
1531 |
}
|
|
1532 |
||
1533 |
// If $this->symlinkDir is set and it is different from datadir, then the
|
|
1534 |
// new table needs to be a symlink with the actual table file living in
|
|
1535 |
// the symlinkDir.
|
|
1536 |
//
|
|
1537 |
// In rare error cases, such as if OSC fails after creating symlink
|
|
1538 |
// but before __osc_new_T is created, the link is not cleaned up.
|
|
1539 |
// That is fine because it does not consume space and next run of
|
|
1540 |
// OSC can handle such links left over from previous runs. (If OSC
|
|
1541 |
// fails after __osc_new_T is created, then it will drop __osc_new_T
|
|
1542 |
// and the link will get cleaned up at that point.)
|
|
1543 |
protected function createSymlinkIfNeeded() { |
|
1544 |
if ($this->version < "5.1.52") { |
|
1545 |
return; |
|
1546 |
}
|
|
1547 |
||
1548 |
$dbdir = $this->getDataDir().$this->dbname.'/'; |
|
1549 |
if (empty($this->symlinkDir) || |
|
1550 |
($this->symlinkDir == $dbdir) || |
|
1551 |
($this->symlinkDir == $this->getDataDir())) { |
|
1552 |
return; |
|
1553 |
}
|
|
1554 |
||
1555 |
$newtablelink = $dbdir.$this->newtablename.".ibd"; |
|
1556 |
if (is_link($newtablelink)) { |
|
1557 |
// Link already seems to exist. Perhaps it did not get cleaned up
|
|
1558 |
// from an earlier run
|
|
1559 |
$this->logWarning("Link $newtablelink already exists!\n"); |
|
1560 |
}
|
|
1561 |
||
1562 |
$newtablefile = $this->symlinkDir.$this->newtablename.".ibd"; |
|
1563 |
$cmd = "sudo -u mysql ln -s $newtablefile $newtablelink"; |
|
1564 |
$this->executeShellCmd("Create Symlink", $cmd); |
|
1565 |
}
|
|
1566 |
||
1567 |
protected function createCopyTable() { |
|
1568 |
$this->createSymlinkIfNeeded(); |
|
1569 |
$this->executeSqlNoResult('Creating copy table', $this->createcmd); |
|
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
1570 |
$this->cleanupNewtable = true; |
14.1.1
by Rob Wultsch
Updates to OSC. |
1571 |
|
1572 |
if (! (bool)($this->flags & OSC_FLAGS_DROPCOLUMN) ) { |
|
1573 |
$query = " SELECT c1.COLUMN_NAME ". |
|
1574 |
" FROM information_schema.columns c1 ". |
|
1575 |
" LEFT join information_schema.columns c2 ON ". |
|
1576 |
" c1.COLUMN_NAME = c2.COLUMN_NAME AND ". |
|
1577 |
" c1.TABLE_SCHEMA = c2.TABLE_SCHEMA AND ". |
|
1578 |
" c2.table_name = '%s' ". |
|
1579 |
" WHERE c1.table_name = '%s' AND ". |
|
1580 |
" c1.TABLE_SCHEMA = '%s' AND ". |
|
1581 |
" c2.COLUMN_NAME IS NULL"; |
|
1582 |
$query = sprintf($query, |
|
1583 |
$this->newtablename, $this->tablename, $this->dbname); |
|
1584 |
$result = mysql_query($query, $this->conn); |
|
1585 |
||
1586 |
while ($row = mysql_fetch_array($result) ) { |
|
1587 |
$this->raiseException( |
|
1588 |
'A column in the existing table is not in newtable.'); |
|
1589 |
}
|
|
1590 |
}
|
|
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
1591 |
}
|
1592 |
||
14.1.1
by Rob Wultsch
Updates to OSC. |
1593 |
// replace 'create table <tablename> ...' with 'create table <copy table> ...
|
1594 |
protected function modifyDDL() { |
|
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
1595 |
$count = 0; |
1596 |
// replace alter table with upper case if it was in lower case
|
|
14.1.1
by Rob Wultsch
Updates to OSC. |
1597 |
$createcopy = preg_replace('/create\s+table\s+/i', |
1598 |
'CREATE TABLE ', |
|
1599 |
$this->createcmd, -1, $count); |
|
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
1600 |
if ($count != 1) { |
14.1.1
by Rob Wultsch
Updates to OSC. |
1601 |
$error = "Found ".$count." matches for 'create table' in ". |
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
1602 |
"the DDL command.\nExpecting exactly 1 match. ". |
14.1.1
by Rob Wultsch
Updates to OSC. |
1603 |
"Please check DDL:\n".$this->createcmd."\n"; |
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
1604 |
$this->raiseException($error, false); |
1605 |
}
|
|
1606 |
||
1607 |
// if the tablename is a reserved word they may have enclosed it in
|
|
1608 |
// backquotes and so try looking for tablename as well as backquoted
|
|
1609 |
// tablename
|
|
1610 |
$count1 = 0; |
|
14.1.1
by Rob Wultsch
Updates to OSC. |
1611 |
$createcopy1 = preg_replace('/CREATE\s+TABLE\s+'.$this->tablename.'/', |
1612 |
'CREATE TABLE '.$this->newtablename, |
|
1613 |
$createcopy, -1, $count1); |
|
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
1614 |
|
1615 |
||
1616 |
$count2 = 0; |
|
14.1.1
by Rob Wultsch
Updates to OSC. |
1617 |
$createcopy2 = preg_replace('/CREATE\s+TABLE\s+'.$this->qtablenameq.'/', |
1618 |
'CREATE TABLE '.$this->newtablename, |
|
1619 |
$createcopy, -1, $count2); |
|
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
1620 |
|
1621 |
$count = $count1 + $count2; |
|
1622 |
if ($count != 1) { |
|
14.1.1
by Rob Wultsch
Updates to OSC. |
1623 |
$error = "Found ".$count." matches for 'CREATE TABLE ".$this->tablename. |
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
1624 |
"' in the DDL command.\nExpecting exactly 1 match. ". |
14.1.1
by Rob Wultsch
Updates to OSC. |
1625 |
"Please check DDL:\n".$createcopy."\n"; |
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
1626 |
$this->raiseException($error, false); |
1627 |
} else if ($count1 == 1) { |
|
14.1.1
by Rob Wultsch
Updates to OSC. |
1628 |
$this->createcmd = $createcopy1; |
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
1629 |
} else { |
14.1.1
by Rob Wultsch
Updates to OSC. |
1630 |
$this->createcmd = $createcopy2; |
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
1631 |
}
|
1632 |
}
|
|
1633 |
||
1634 |
// validates any assumptions about PK after the alter
|
|
14.1.1
by Rob Wultsch
Updates to OSC. |
1635 |
protected function validatePostAlterPK($primary) { |
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
1636 |
if (empty($primary)) { |
1637 |
$this->raiseException("No primary key defined in the new table!", false); |
|
1638 |
}
|
|
1639 |
||
1640 |
if ($this->flags & OSC_FLAGS_ACCEPTPK) { |
|
1641 |
return; |
|
1642 |
}
|
|
1643 |
||
14.1.1
by Rob Wultsch
Updates to OSC. |
1644 |
if ($this->flags & OSC_FLAGS_USE_NEW_PK) { |
1645 |
// for this mode we need to ensure that all columns in the new PK
|
|
1646 |
// are already part of the old version of the table
|
|
1647 |
foreach ($this->pkcolumnarray as $col) { |
|
1648 |
if (!in_array($col, $this->columnarray)) { |
|
1649 |
$error = "You can not create a new PK using new columns. ". |
|
1650 |
"The columns must already exist in the old table."; |
|
1651 |
$this->raiseException($error, false); |
|
1652 |
}
|
|
1653 |
}
|
|
1654 |
}
|
|
1655 |
||
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
1656 |
// check if old PK (available in $this->pkcolumnarry) is a prefix
|
1657 |
// of atleast one index after the alter table.
|
|
1658 |
// Note that if old PK is (a, b) and after alter table there is an
|
|
1659 |
// index on (b, a), that is OK as it supports efficient lookups
|
|
1660 |
// if values of both a and b are provided.
|
|
1661 |
$pkcount = count($this->pkcolumnarray); |
|
1662 |
foreach ($this->indexes as $index) { |
|
1663 |
// get an array of index column names
|
|
1664 |
$colarray = array(); |
|
1665 |
foreach ($index->columns as $column) { |
|
1666 |
$colarray[] = $column->name; |
|
1667 |
}
|
|
1668 |
||
1669 |
// get an array slice of 1st pkcount elements
|
|
1670 |
$prefix = array_slice($colarray, 0, $pkcount); |
|
1671 |
||
1672 |
$diff = array_diff($this->pkcolumnarray, $prefix); |
|
1673 |
||
1674 |
// if A and B are equal size and there are no elements in A
|
|
1675 |
// that are not in B, it means A and B are same.
|
|
1676 |
if ((count($prefix) === $pkcount) && empty($diff)) { |
|
1677 |
return; |
|
1678 |
}
|
|
1679 |
}
|
|
1680 |
||
1681 |
$error = "After alter there is no index on old PK columns. May not ". |
|
1682 |
"support efficient lookups using old PK columns. ". |
|
1683 |
"Not allowed unless OSC_FLAGS_ACCEPTPK is set."; |
|
1684 |
$this->raiseException($error, false); |
|
1685 |
}
|
|
1686 |
||
1687 |
// Retrieves info about indexes on copytable
|
|
14.1.1
by Rob Wultsch
Updates to OSC. |
1688 |
protected function initIndexes() { |
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
1689 |
$query = "select * from information_schema.statistics ". |
1690 |
"where table_name = '%s' and TABLE_SCHEMA = '%s' ". |
|
1691 |
"order by INDEX_NAME, SEQ_IN_INDEX"; |
|
1692 |
||
1693 |
$query = sprintf($query, $this->newtablename, $this->dbname); |
|
1694 |
||
1695 |
if (!($result = mysql_query($query, $this->conn))) { |
|
1696 |
$this->raiseException('Failed to get index info '.$query); |
|
1697 |
}
|
|
1698 |
||
1699 |
// save index info as array
|
|
1700 |
$this->indexes = array(); |
|
1701 |
||
14.1.1
by Rob Wultsch
Updates to OSC. |
1702 |
// we are resetting the PK so that it will be used in later steps
|
1703 |
if ($this->flags & OSC_FLAGS_USE_NEW_PK) { |
|
1704 |
$this->pkcolumnarray = array(); |
|
1705 |
}
|
|
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
1706 |
$prev_index_name = ''; |
1707 |
$index = null; |
|
1708 |
$primary = null; |
|
1709 |
while ($row = mysql_fetch_assoc($result)) { |
|
1710 |
$index_name = quotify($row['INDEX_NAME']); |
|
1711 |
$column_name = quotify($row['COLUMN_NAME']); |
|
1712 |
if ($prev_index_name != $index_name) { |
|
1713 |
// is the 1st column of the index autoincrement column?
|
|
1714 |
$auto = isset($this->autoIncrement) && |
|
1715 |
($column_name === $this->autoIncrement); |
|
1716 |
$index = new IndexInfo($this->newtablename, $index_name, |
|
1717 |
$row['NON_UNIQUE'], $auto); |
|
1718 |
if ($index->isPrimary()) { |
|
1719 |
$primary = $index; |
|
1720 |
}
|
|
1721 |
$this->indexes[] = $index; |
|
1722 |
}
|
|
1723 |
$index->addColumn($column_name, $row['SUB_PART']); |
|
1724 |
||
14.1.1
by Rob Wultsch
Updates to OSC. |
1725 |
if ($this->flags & OSC_FLAGS_USE_NEW_PK && $index->isPrimary()) { |
1726 |
$this->pkcolumnarray[] = $column_name; |
|
1727 |
}
|
|
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
1728 |
$prev_index_name = $index_name; |
1729 |
}
|
|
1730 |
||
14.1.1
by Rob Wultsch
Updates to OSC. |
1731 |
// re-create these strings with new array
|
1732 |
if ($this->flags & OSC_FLAGS_USE_NEW_PK) { |
|
1733 |
$this->initColumnNameStrings(); |
|
1734 |
}
|
|
1735 |
||
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
1736 |
$this->validatePostAlterPK($primary); |
1737 |
$this->joinClauseReplay = $this->getJoinClauseReplay(); |
|
1738 |
}
|
|
1739 |
||
14.1.1
by Rob Wultsch
Updates to OSC. |
1740 |
protected function dropNCIndexes() { |
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
1741 |
if (!$this->reindex) { |
1742 |
return; |
|
1743 |
}
|
|
1744 |
||
1745 |
foreach ($this->indexes as $index) { |
|
14.1.1
by Rob Wultsch
Updates to OSC. |
1746 |
if (( !$index->isPrimary() && !$index->isAutoIncrement ) |
1747 |
&& !($this->flags & OSC_FLAGS_ELIMINATE_DUPS && !$index->nonUnique)) { |
|
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
1748 |
$drop = $index->getDropSql(); |
1749 |
$this->executeSqlNoResult('Dropping index', $drop); |
|
1750 |
}
|
|
1751 |
}
|
|
1752 |
||
1753 |
}
|
|
1754 |
||
14.1.1
by Rob Wultsch
Updates to OSC. |
1755 |
protected function recreateNCIndexes() { |
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
1756 |
if (!$this->reindex) { |
1757 |
return; |
|
1758 |
}
|
|
1759 |
$count = 0; |
|
1760 |
$create = 'Alter table '.$this->newtablename; |
|
1761 |
$comma = ''; // no comma first time |
|
1762 |
foreach ($this->indexes as $index) { |
|
14.1.1
by Rob Wultsch
Updates to OSC. |
1763 |
if (!$index->isPrimary() && !$index->isAutoIncrement |
1764 |
&& !($this->flags & OSC_FLAGS_ELIMINATE_DUPS && !$index->nonUnique)) { |
|
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
1765 |
$create .= $comma.$index->getCreateSql(); |
1766 |
$comma = ','; |
|
1767 |
$count++; |
|
1768 |
}
|
|
1769 |
}
|
|
1770 |
if ($count > 0) { |
|
1771 |
$this->executeSqlNoResult('ReCreating NC indexes', $create); |
|
1772 |
}
|
|
1773 |
}
|
|
1774 |
||
14.1.1
by Rob Wultsch
Updates to OSC. |
1775 |
// Every now and again index stats are wonky after an OSC run
|
1776 |
protected function analyzeTableIndexs() { |
|
1777 |
$analyze = "ANALYZE TABLE ".$this->newtablename; |
|
1778 |
$this->executeSqlNoResult('ANALYZE TABLE', $analyze); |
|
1779 |
}
|
|
1780 |
||
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
1781 |
// loads copy table from outfile
|
14.1.1
by Rob Wultsch
Updates to OSC. |
1782 |
protected function loadCopyTable() { |
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
1783 |
while ($this->outfileSuffixEnd >= $this->outfileSuffixStart) { |
14.1.1
by Rob Wultsch
Updates to OSC. |
1784 |
if ($this->flags & OSC_FLAGS_USE_NEW_PK) { |
1785 |
$loadsql = sprintf("LOAD DATA INFILE '%s.%d' %s INTO TABLE %s(%s)", |
|
1786 |
$this->outfileTable, |
|
1787 |
$this->outfileSuffixStart, |
|
1788 |
$this->ignoredups, |
|
1789 |
$this->newtablename, |
|
1790 |
$this->columns); |
|
1791 |
}
|
|
1792 |
else { |
|
1793 |
$loadsql = sprintf("LOAD DATA INFILE '%s.%d' %s INTO TABLE %s(%s, %s)", |
|
1794 |
$this->outfileTable, |
|
1795 |
$this->outfileSuffixStart, |
|
1796 |
$this->ignoredups, |
|
1797 |
$this->newtablename, |
|
1798 |
$this->pkcolumns, $this->nonpkcolumns); |
|
1799 |
}
|
|
1800 |
// the LOAD might fail if duplicate keys were added in a new PK
|
|
1801 |
if (!$this->executeSqlNoResult('Loading copy table', |
|
1802 |
$loadsql, |
|
1803 |
self::LOGFLAG_WARNING)) { |
|
1804 |
if (mysql_errno($this->conn) == 1062) { |
|
1805 |
$this->raiseException("Duplicate key found while loading table. ". |
|
1806 |
"Most likely a problem with new PK: "); |
|
1807 |
}
|
|
1808 |
$this->raiseException("Error loading data: "); |
|
1809 |
}
|
|
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
1810 |
|
1811 |
// delete file now rather than waiting till cleanup
|
|
1812 |
// as this will free up space.
|
|
1813 |
$filename = sprintf('%s.%d', $this->outfileTable, |
|
1814 |
$this->outfileSuffixStart); |
|
1815 |
$this->outfileSuffixStart++; |
|
1816 |
if (!($this->flags & OSC_FLAGS_NOCLEANUP)) { |
|
1817 |
$this->executeUnlink($filename); |
|
1818 |
}
|
|
1819 |
}
|
|
1820 |
unset($this->outfileSuffixEnd); |
|
1821 |
unset($this->outfileSuffixStart); |
|
1822 |
}
|
|
1823 |
||
1824 |
// Generates condition of the form
|
|
1825 |
// tableA.column1=tableB.column1 AND tableA.column2=tableB.column2 ...
|
|
1826 |
// If null $columns is passed, it uses $this->pkcolumnarray as array.
|
|
14.1.1
by Rob Wultsch
Updates to OSC. |
1827 |
protected function getMatchCondition($tableA, $tableB, $columns = null) { |
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
1828 |
if ($columns === null) { |
1829 |
$columns = $this->pkcolumnarray; |
|
1830 |
}
|
|
1831 |
||
1832 |
$cond = ''; |
|
1833 |
$and = ''; // no AND first time |
|
1834 |
foreach ($columns as $column) { |
|
1835 |
$cond .= $and.sprintf(' %s.%s = %s.%s ', |
|
1836 |
$tableA, $column, $tableB, $column); |
|
1837 |
$and = ' AND '; |
|
1838 |
}
|
|
1839 |
||
1840 |
$cond .= ' '; |
|
1841 |
||
1842 |
return $cond; |
|
1843 |
}
|
|
1844 |
||
1845 |
// Builds the join clause used during replay.
|
|
1846 |
// Join condition of the form A.col1 = B.col1 AND A.col2=B.col2 AND ...
|
|
1847 |
// where A is copytable, B is deltastable AND col1, col2, ... are the
|
|
1848 |
// PK columns before ALTER.
|
|
14.1.1
by Rob Wultsch
Updates to OSC. |
1849 |
protected function getJoinClauseReplay() { |
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
1850 |
return ($this->getMatchCondition($this->newtablename, $this->deltastable)); |
1851 |
}
|
|
1852 |
||
1853 |
// check that replay command has affected exactly one row
|
|
14.1.1
by Rob Wultsch
Updates to OSC. |
1854 |
protected function validateReplay($replay_sql) { |
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
1855 |
$count = mysql_affected_rows($this->conn); |
14.1.1
by Rob Wultsch
Updates to OSC. |
1856 |
if ($count > 1 || |
1857 |
($count == 0 && !($this->flags & OSC_FLAGS_ELIMINATE_DUPS))) { |
|
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
1858 |
$error = sprintf('Replay command [%s] affected %d rows instead of 1 row', |
1859 |
$replay_sql, $count); |
|
1860 |
$this->raiseException($error, false); |
|
1861 |
}
|
|
1862 |
}
|
|
1863 |
||
1864 |
// Row has ID that can be used to look up into deltas table
|
|
1865 |
// to find PK of the row in the newtable to delete
|
|
14.1.1
by Rob Wultsch
Updates to OSC. |
1866 |
protected function replayDeleteRow($row) { |
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
1867 |
$newtable = $this->newtablename; |
1868 |
$deltas = $this->deltastable; |
|
1869 |
$delete = sprintf('delete %s from %s, %s where %s.%s = %d AND %s', |
|
1870 |
$newtable, $newtable, $deltas, $deltas, self::IDCOLNAME, |
|
1871 |
$row[self::IDCOLNAME], |
|
1872 |
$this->joinClauseReplay); |
|
1873 |
$this->executeSqlNoResult('Replaying delete row', $delete, |
|
1874 |
self::LOGFLAG_VERBOSE); |
|
1875 |
$this->validateReplay($delete); |
|
1876 |
}
|
|
1877 |
||
1878 |
// Row has ID that can be used to look up into deltas table
|
|
1879 |
// to find PK of the row in the newtable to update.
|
|
1880 |
// New values for update (only non-PK columns are updated) are
|
|
1881 |
// all taken from deltas table.
|
|
14.1.1
by Rob Wultsch
Updates to OSC. |
1882 |
protected function replayUpdateRow($row) { |
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
1883 |
$assignment = ''; |
1884 |
$comma = ''; // no comma first time |
|
1885 |
foreach ($this->nonpkarray as $column) { |
|
1886 |
$assignment .= $comma.$this->newtablename.'.'.$column.'='. |
|
1887 |
$this->deltastable.'.'.$column; |
|
1888 |
$comma = ', '; |
|
1889 |
}
|
|
1890 |
||
1891 |
$newtable = $this->newtablename; |
|
1892 |
$deltas = $this->deltastable; |
|
14.1.1
by Rob Wultsch
Updates to OSC. |
1893 |
$update = sprintf('update %s %s, %s SET %s where %s.%s = %d AND %s ', |
1894 |
$this->ignoredups, $newtable, $deltas, $assignment, |
|
1895 |
$deltas, self::IDCOLNAME, |
|
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
1896 |
$row[self::IDCOLNAME], |
1897 |
$this->joinClauseReplay); |
|
1898 |
$this->executeSqlNoResult('Replaying update row', $update, |
|
1899 |
self::LOGFLAG_VERBOSE); |
|
1900 |
// if original update had old value same as new value, trigger fires
|
|
1901 |
// and row gets inserted into deltas table. However mysql_affected_rows
|
|
1902 |
// would return 0 for replay update. So this validation is commented out
|
|
1903 |
// for now.
|
|
1904 |
// $this->validateReplay($update);
|
|
1905 |
}
|
|
1906 |
||
1907 |
// Row has ID that can be used to look up into deltas table
|
|
1908 |
// to find the row that needs to be inserted into the newtable.
|
|
14.1.1
by Rob Wultsch
Updates to OSC. |
1909 |
protected function replayInsertRow($row) { |
1910 |
$insert = sprintf('insert %s into %s(%s) '. |
|
1911 |
'select %s from %s where %s.%s = %d', |
|
1912 |
$this->ignoredups, $this->newtablename, $this->columns, |
|
1913 |
$this->columns, $this->deltastable, $this->deltastable, |
|
1914 |
self::IDCOLNAME, $row[self::IDCOLNAME]); |
|
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
1915 |
$this->executeSqlNoResult('Replaying insert row', $insert, |
1916 |
self::LOGFLAG_VERBOSE); |
|
1917 |
$this->validateReplay($insert); |
|
1918 |
}
|
|
1919 |
||
1920 |
// Copies rows from self::TEMP_TABLE_IDS_TO_INCLUDE to
|
|
1921 |
// self::TEMP_TABLE_IDS_TO_EXCLUDE
|
|
14.1.1
by Rob Wultsch
Updates to OSC. |
1922 |
protected function appendToExcludedIDs() { |
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
1923 |
$append = sprintf('insert into %s(%s, %s) select %s, %s from %s', |
1924 |
self::TEMP_TABLE_IDS_TO_EXCLUDE, |
|
1925 |
self::IDCOLNAME, self::DMLCOLNAME, |
|
1926 |
self::IDCOLNAME, self::DMLCOLNAME, |
|
1927 |
self::TEMP_TABLE_IDS_TO_INCLUDE); |
|
1928 |
$this->executeSqlNoResult('Appending to excluded_ids', $append); |
|
1929 |
}
|
|
1930 |
||
14.1.1
by Rob Wultsch
Updates to OSC. |
1931 |
protected function replayChanges($single_xact) { |
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
1932 |
// create temp table for included ids
|
1933 |
$this->createAndInitTemptable(self::TEMP_TABLE_IDS_TO_INCLUDE); |
|
1934 |
||
1935 |
$query = sprintf('select %s, %s from %s order by %s', |
|
1936 |
self::IDCOLNAME, self::DMLCOLNAME, |
|
1937 |
self::TEMP_TABLE_IDS_TO_INCLUDE, self::IDCOLNAME); |
|
1938 |
if (!($result = mysql_query($query, $this->conn))) { |
|
1939 |
$this->raiseException('Query to replay changes failed.'.$query); |
|
1940 |
}
|
|
1941 |
||
1942 |
$i = 0; // iteration count |
|
1943 |
$inserts = 0; |
|
1944 |
$deletes = 0; |
|
1945 |
$updates = 0; |
|
1946 |
||
1947 |
if (!$single_xact) { |
|
1948 |
$this->executeSqlNoResult('Starting batch xact for replay', |
|
1949 |
'START TRANSACTION', self::LOGFLAG_VERBOSE); |
|
1950 |
}
|
|
1951 |
||
1952 |
while ($row = mysql_fetch_assoc($result)) { |
|
1953 |
++$i; |
|
1954 |
if (!$single_xact && ($i % $this->batchsizeReplay == 0)) { |
|
1955 |
$this->executeSqlNoResult('Commiting batch xact for replay', |
|
1956 |
'COMMIT', self::LOGFLAG_VERBOSE); |
|
1957 |
}
|
|
1958 |
||
1959 |
switch ($row[self::DMLCOLNAME]) { |
|
1960 |
case self::DMLTYPE_DELETE : |
|
1961 |
$this->replayDeleteRow($row); |
|
1962 |
$deletes++; |
|
1963 |
break; |
|
1964 |
||
1965 |
case self::DMLTYPE_UPDATE : |
|
1966 |
$this->replayUpdateRow($row); |
|
1967 |
$updates++; |
|
1968 |
break; |
|
1969 |
||
1970 |
case self::DMLTYPE_INSERT : |
|
1971 |
$this->replayInsertRow($row); |
|
1972 |
$inserts++; |
|
1973 |
break; |
|
1974 |
||
1975 |
default : |
|
1976 |
$this->raiseException('Invalid DML type', false); |
|
1977 |
}
|
|
1978 |
}
|
|
1979 |
if (!$single_xact) { |
|
1980 |
$this->executeSqlNoResult('Commiting batch xact for replay', 'COMMIT', |
|
1981 |
self::LOGFLAG_VERBOSE); |
|
1982 |
}
|
|
1983 |
||
1984 |
$this->appendToExcludedIDs(); |
|
1985 |
||
1986 |
$drop = 'DROP TEMPORARY TABLE '.self::TEMP_TABLE_IDS_TO_INCLUDE; |
|
1987 |
$this->executeSqlNoResult('Dropping temp table of included ids', $drop); |
|
1988 |
||
1989 |
$output = sprintf("Replayed %d inserts, %d deletes, %d updates\n", |
|
1990 |
$inserts, $deletes, $updates); |
|
1991 |
$this->logCompact($output); |
|
1992 |
}
|
|
1993 |
||
14.1.1
by Rob Wultsch
Updates to OSC. |
1994 |
protected function checksum() { |
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
1995 |
$query = sprintf("checksum table %s, %s", |
1996 |
$this->newtablename, $this->qtablenameq); |
|
1997 |
||
1998 |
if (!($result = mysql_query($query, $this->conn))) { |
|
1999 |
$this->raiseException('Failed to get checksums: '.$query); |
|
2000 |
}
|
|
2001 |
||
2002 |
// we expect only two rows
|
|
2003 |
$i = 0; |
|
2004 |
$checksum = array(); |
|
2005 |
while ($row = mysql_fetch_assoc($result)) { |
|
2006 |
$checksum[$i++] = $row['Checksum']; |
|
2007 |
}
|
|
2008 |
||
2009 |
if ($checksum[0] != $checksum[1]) { |
|
2010 |
$error = "Checksums don't match.".$checksum[0]."/".$checksum[1]; |
|
2011 |
$this->raiseException($error, false); |
|
2012 |
}
|
|
2013 |
||
2014 |
}
|
|
2015 |
||
14.1.1
by Rob Wultsch
Updates to OSC. |
2016 |
protected function swapTables() { |
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
2017 |
$this->stopSlave(); |
2018 |
||
2019 |
// without turning off autocommit lock tables is not working
|
|
2020 |
$this->executeSqlNoResult('AUTOCOMMIT OFF', 'set session autocommit=0'); |
|
2021 |
||
2022 |
// true means lock both original table and newtable
|
|
2023 |
$this->lockTables('Locking tables for final replay/swap', true); |
|
2024 |
||
2025 |
// any changes that happened after we replayed changes last time.
|
|
2026 |
// true means do them in one transaction.
|
|
2027 |
$this->replayChanges(true); |
|
2028 |
||
2029 |
// at this point tables should be identical if schema is same
|
|
2030 |
if ($this->flags & OSC_FLAGS_CHECKSUM) { |
|
2031 |
$this->checksum(); |
|
2032 |
}
|
|
2033 |
||
2034 |
$rename_original = sprintf('alter table %s rename %s', |
|
2035 |
$this->qtablenameq, $this->renametable); |
|
2036 |
$this->executeSqlNoResult('Renaming original table', $rename_original); |
|
2037 |
$this->cleanupRenametable = true; |
|
2038 |
||
2039 |
// if the above command succeeds and the following command fails,
|
|
2040 |
// we will have:
|
|
2041 |
// $this->cleanupNewtable set and $this->cleanupRenametable set.
|
|
2042 |
// In that case we will rename renametable back to original tablename.
|
|
2043 |
$rename_new = sprintf('alter table %s rename %s', |
|
2044 |
$this->newtablename, $this->qtablenameq); |
|
2045 |
$this->executeSqlNoResult('Renaming new table', $rename_new); |
|
2046 |
unset($this->cleanupNewtable); |
|
2047 |
||
2048 |
$this->executeSqlNoResult('COMMITTING', 'COMMIT'); |
|
2049 |
||
2050 |
$this->executeSqlNoResult('Unlocking tables', 'unlock tables'); |
|
2051 |
||
2052 |
$this->executeSqlNoResult('AUTOCOMMIT ON', 'set session autocommit=1'); |
|
2053 |
||
2054 |
$this->startSlave(); |
|
2055 |
||
2056 |
}
|
|
2057 |
||
14.1.1
by Rob Wultsch
Updates to OSC. |
2058 |
protected function doesTableExist($tablename) { |
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
2059 |
$query = sprintf("show tables like '%s'", $tablename); |
2060 |
||
2061 |
if (!($result = mysql_query($query, $this->conn))) { |
|
2062 |
$this->raiseException('Failed to get table existence info:'.$query); |
|
2063 |
}
|
|
2064 |
||
2065 |
return (mysql_fetch_assoc($result) ? true : false); |
|
2066 |
}
|
|
2067 |
||
14.1.1
by Rob Wultsch
Updates to OSC. |
2068 |
protected function cleanup() { |
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
2069 |
if ($this->flags & OSC_FLAGS_NOCLEANUP) { |
2070 |
return; |
|
2071 |
}
|
|
2072 |
||
2073 |
// if we don't have a connection get it
|
|
2074 |
if (!$this->conn || !mysql_ping($this->conn)) { |
|
2075 |
$this->openAndInitConnection(); |
|
2076 |
}
|
|
2077 |
||
2078 |
$force = $this->flags & OSC_FLAGS_FORCE_CLEANUP; |
|
2079 |
||
2080 |
$this->executeSqlNoResult('Unlock tables just in case', 'unlock tables'); |
|
2081 |
||
2082 |
$this->executeSqlNoResult('Rollback in case we are in xact', 'ROLLBACK'); |
|
2083 |
||
2084 |
// in case we are in autocommit off, turn it on
|
|
2085 |
$this->executeSqlNoResult('AUTOCOMMIT ON', 'set session autocommit=1'); |
|
2086 |
||
2087 |
if ($force) { |
|
2088 |
$this->cleanupInsertTrigger = true; |
|
2089 |
$this->cleanupDeleteTrigger = true; |
|
2090 |
$this->cleanupUpdateTrigger = true; |
|
2091 |
}
|
|
2092 |
if (isset($this->cleanupInsertTrigger)) { |
|
2093 |
$drop = sprintf('drop trigger %s.%s', |
|
14.1.1
by Rob Wultsch
Updates to OSC. |
2094 |
$this->qdbnameq, $this->insertTrigger); |
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
2095 |
$this->executeSqlNoResult('Dropping insert trigger', $drop); |
2096 |
unset($this->cleanupInsertTrigger); |
|
2097 |
}
|
|
2098 |
||
2099 |
if (isset($this->cleanupDeleteTrigger)) { |
|
2100 |
$drop = sprintf('drop trigger %s.%s', |
|
14.1.1
by Rob Wultsch
Updates to OSC. |
2101 |
$this->qdbnameq, $this->deleteTrigger); |
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
2102 |
$this->executeSqlNoResult('Dropping delete trigger', $drop); |
2103 |
unset($this->cleanupDeleteTrigger); |
|
2104 |
}
|
|
2105 |
||
2106 |
if (isset($this->cleanupUpdateTrigger)) { |
|
2107 |
$drop = sprintf('drop trigger %s.%s', |
|
14.1.1
by Rob Wultsch
Updates to OSC. |
2108 |
$this->qdbnameq, $this->updateTrigger); |
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
2109 |
$this->executeSqlNoResult('Dropping update trigger', $drop); |
2110 |
unset($this->cleanupUpdateTrigger); |
|
2111 |
}
|
|
2112 |
||
2113 |
if ($force) { |
|
2114 |
$this->cleanupDeltastable = true; |
|
2115 |
$this->cleanupNewtable = true; |
|
2116 |
||
2117 |
// We need to be careful when dropping renamedtable because
|
|
2118 |
// during previous run, we may have failed AFTER original
|
|
2119 |
// table was renamed. If we drop renamed table, we may lose
|
|
2120 |
// the table.
|
|
2121 |
if ($this->doesTableExist($this->renametable)) { |
|
2122 |
$this->cleanupRenametable = true; |
|
2123 |
}
|
|
2124 |
}
|
|
2125 |
||
2126 |
if (isset($this->cleanupDeltastable)) { |
|
2127 |
$this->executeSqlNoResult('Dropping deltas table', |
|
2128 |
'drop table '.$this->deltastable); |
|
2129 |
unset($this->cleanupDeltastable); |
|
2130 |
}
|
|
2131 |
||
2132 |
// does original table exist
|
|
2133 |
$orig_table_exists = $this->doesTableExist($this->tablename); |
|
2134 |
||
2135 |
if (isset($this->cleanupRenametable) && !$orig_table_exists) { |
|
2136 |
// rename renametable back to original name.
|
|
2137 |
$warning = "Original table does not exist but renamed table exists!. ". |
|
2138 |
"Must have failed AFTER renaming original table!"; |
|
2139 |
$this->logWarning($warning); |
|
2140 |
||
2141 |
$rename = sprintf('alter table %s rename %s', |
|
2142 |
$this->renametable, $this->qtablenameq); |
|
2143 |
$this->executeSqlNoResult('Renaming backup table as original table', |
|
2144 |
$rename); |
|
2145 |
unset($this->cleanupRenametable); |
|
2146 |
} else if (!$orig_table_exists) { |
|
2147 |
// PANIC
|
|
2148 |
$this->raiseException("NEITHER ORIGINAL TABLE EXISTS NOR RENAMED TABLE", |
|
2149 |
false); |
|
2150 |
} else if (isset($this->cleanupRenametable)) { |
|
2151 |
if ($this->flags & OSC_FLAGS_DROPTABLE) { |
|
14.1.1
by Rob Wultsch
Updates to OSC. |
2152 |
$this->dropTable($this->renametable,$this->conn); |
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
2153 |
unset($this->cleanupRenametable); |
2154 |
}
|
|
2155 |
}
|
|
2156 |
||
2157 |
if (isset($this->cleanupNewtable)) { |
|
14.1.1
by Rob Wultsch
Updates to OSC. |
2158 |
$this->dropTable($this->newtablename,$this->conn); |
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
2159 |
unset($this->cleanupNewtable); |
2160 |
}
|
|
2161 |
||
2162 |
// in case we stopped slave, start it
|
|
2163 |
$this->startSlave(); |
|
2164 |
||
2165 |
if (isset($this->cleanupOutfile)) { |
|
2166 |
$outfile = $this->cleanupOutfile; |
|
2167 |
$this->executeUnlink($outfile); |
|
2168 |
unset($this->cleanupOutfile); |
|
2169 |
} else if ($force) { |
|
14.1.1
by Rob Wultsch
Updates to OSC. |
2170 |
if (isset($this->outfileIncludeIDs)) { |
2171 |
$this->executeUnlink($this->outfileIncludeIDs); |
|
2172 |
}
|
|
2173 |
if (isset($this->outfileExcludeIDs)) { |
|
2174 |
$this->executeUnlink($this->outfileExcludeIDs); |
|
2175 |
}
|
|
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
2176 |
}
|
2177 |
||
2178 |
if (isset($this->outfileSuffixEnd) && isset($this->outfileSuffixStart)) { |
|
2179 |
while ($this->outfileSuffixEnd >= $this->outfileSuffixStart) { |
|
2180 |
$filename = sprintf('%s.%d', $this->outfileTable, |
|
2181 |
$this->outfileSuffixStart); |
|
2182 |
$this->executeUnlink($filename); |
|
2183 |
$this->outfileSuffixStart++; |
|
2184 |
}
|
|
2185 |
unset($this->outfileSuffixEnd); |
|
2186 |
unset($this->outfileSuffixStart); |
|
14.1.1
by Rob Wultsch
Updates to OSC. |
2187 |
} else if ($force && isset($this->outfileTable) ) { |
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
2188 |
$files_wildcard = sprintf('%s.*', $this->outfileTable); |
2189 |
$files = glob($files_wildcard); |
|
2190 |
foreach ($files as $file) { |
|
2191 |
$this->executeUnlink($file); |
|
2192 |
}
|
|
2193 |
}
|
|
2194 |
||
2195 |
if ($this->flags & OSC_FLAGS_DELETELOG) { |
|
2196 |
// true means check if file exists
|
|
2197 |
$this->executeUnlink($this->oscLogFilePrefix.".log", true); |
|
2198 |
$this->executeUnlink($this->oscLogFilePrefix.".wrn", true); |
|
2199 |
$this->executeUnlink($this->oscLogFilePrefix.".err", true); |
|
2200 |
}
|
|
2201 |
||
2202 |
// closing connection should drop temp tables
|
|
2203 |
// don't bother checking return status as this is last step anyway.
|
|
2204 |
if ($this->conn) { |
|
2205 |
$this->releaseOscLock($this->conn); // noop if lock not held |
|
2206 |
mysql_close($this->conn); |
|
2207 |
$this->conn = null; |
|
2208 |
}
|
|
2209 |
||
2210 |
}
|
|
2211 |
||
2212 |
public function forceCleanup() { |
|
2213 |
$this->flags |= OSC_FLAGS_FORCE_CLEANUP; |
|
2214 |
$this->flags &= ~OSC_FLAGS_ERRORTEST; |
|
2215 |
return $this->execute(); |
|
2216 |
}
|
|
2217 |
||
2218 |
public function execute() { |
|
14.1.1
by Rob Wultsch
Updates to OSC. |
2219 |
if (!$this->conn || !mysql_ping($this->conn)) { |
2220 |
$this->openAndInitConnection(); |
|
2221 |
}
|
|
2222 |
||
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
2223 |
try { |
14.1.1
by Rob Wultsch
Updates to OSC. |
2224 |
$this->validateVersion(); |
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
2225 |
// outfile names for storing copy of table, and processed IDs
|
2226 |
$this->initOutfileNames(); |
|
2227 |
||
2228 |
if ($this->flags & OSC_FLAGS_FORCE_CLEANUP) { |
|
2229 |
$this->cleanup(); |
|
2230 |
return true; |
|
2231 |
} else { |
|
2232 |
$this->modifyDDL(); |
|
2233 |
if ($this->doesTableExist($this->renametable)) { |
|
2234 |
$error = sprintf("Please cleanup table %s left over from prior run.", |
|
2235 |
$this->renametable); |
|
2236 |
$this->raiseException($error, false); |
|
2237 |
}
|
|
2238 |
||
2239 |
$this->checkLongXact(); |
|
2240 |
}
|
|
2241 |
||
14.1.1
by Rob Wultsch
Updates to OSC. |
2242 |
$this->createCopyTable(); |
2243 |
// we call init() after the create/alter since we need the new columns
|
|
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
2244 |
$this->init(); |
2245 |
$this->createDeltasTable(); |
|
2246 |
$this->createTriggers(); |
|
2247 |
$this->startSnapshotXact(); |
|
2248 |
$this->selectTableIntoOutfile(); |
|
2249 |
$this->dropNCIndexes(); |
|
2250 |
$this->loadCopyTable(); |
|
2251 |
$this->replayChanges(false); // false means not in single xact |
|
2252 |
$this->recreateNCIndexes(); |
|
14.1.1
by Rob Wultsch
Updates to OSC. |
2253 |
$this->analyzeTableIndexs(); |
10
by Mark Callaghan
Online Schema Change aka OSC -- thanks Vamsi. |
2254 |
$this->replayChanges(false); // false means not in single xact |
2255 |
$this->swapTables(); |
|
2256 |
$this->cleanup(); |
|
2257 |
return true; |
|
2258 |
} catch (Exception $e) { |
|
2259 |
// it is possible that we got exception during cleanup().
|
|
2260 |
// that is fine, we will try once more to cleanup remaining
|
|
2261 |
// resources.
|
|
2262 |
$this->logError('Caught exception: '. $e->getMessage(). "\n"); |
|
2263 |
$this->cleanup(); |
|
2264 |
}
|
|
2265 |
return false; |
|
2266 |
}
|
|
2267 |
}
|
|
2268 |
||
2269 |