· 8 years ago · Jan 29, 2018, 12:00 PM
1<?php
2/**
3 * Ce script permet de mettre à our le champ `SubscriberTotal` d'un ou plusieurs segments
4 *
5 * Author : Camille FRAPPIN <cfrappin@iroquois.fr>
6**/
7
8// This file will be executed from shell also. So, check if DOCUMENT_ROOT is set. If not, set it - Start
9if ($_SERVER['DOCUMENT_ROOT'] == '')
10 {
11 /*
12 IMPORTANT:
13 If this CRON script is executed in CLI mode, do not forget that PHP will try to connect your MySQL server
14 through /var/mysql/mysql.sock socket. The path of socket file can not be changed. If mysql.sock is in different
15 location on your server, simply create a symbolic link on /var/mysql/mysql.sock
16
17 This is a limitation of PHP and there's nothing to do. Sleep on it ;)
18 */
19 $_SERVER['DOCUMENT_ROOT'] = str_replace('cli/convert_latin1_to_utf8_table_columns.php', '', __FILE__);
20 }
21// This file will be executed from shell also. So, check if DOCUMENT_ROOT is set. If not, set it - End
22
23$IsCLI = true;
24ini_set('max_execution_time', 0);
25
26// Include main module - Start
27include_once($_SERVER['DOCUMENT_ROOT'].'/data/config.inc.php');
28// Include main module - End
29
30if (Core::RunningFromCLI() == false)
31 {
32 Core::DisplayCLIBrowserError('EMAIL_PIPE');
33 exit;
34 }
35
36
37// Init script variables - Start
38$UpdateColumnEncoding = false; // ACTIVATE TO CAST/CONVERT ALL TEXT FIELDS
39$ConvertMyISAMToInnoDB = true; // ACTIVATE TO CONVERT ALL MyISAM tables to InnoDB engine
40$UpdatePerLoop = 10000; // Total rows copied by each loop
41$SleepValue = 0.1; // [FLOAT] sleep between loops
42$WantedCharset = 'utf8';
43$WantedCollation = 'utf8_unicode_ci';
44$ArrayColumnTypeCheck = array('char', 'varchar', 'text', 'enum', 'set');
45
46$conf_col_size = 100;
47echo str_pad(' Script configuration ', $conf_col_size, '|', STR_PAD_BOTH)."\n";
48echo str_pad(str_pad('DB charset: '.$WantedCharset, $conf_col_size-4, ' ', STR_PAD_BOTH), $conf_col_size, '|', STR_PAD_BOTH)."\n";
49echo str_pad(str_pad('DB collation: '.$WantedCollation, $conf_col_size-4, ' ', STR_PAD_BOTH), $conf_col_size, '|', STR_PAD_BOTH)."\n";
50echo str_pad(str_pad('DB column type for encoding/collation check: '.implode(', ', $ArrayColumnTypeCheck), $conf_col_size-4, ' ', STR_PAD_BOTH), $conf_col_size, '|', STR_PAD_BOTH)."\n";
51echo str_pad('', $conf_col_size, '|', STR_PAD_BOTH)."\n";
52echo str_pad(str_pad('Correct encoding to '.$WantedCharset.': '.($UpdateColumnEncoding ? 'Yes' : 'No'), $conf_col_size-4, ' ', STR_PAD_BOTH), $conf_col_size, '|', STR_PAD_BOTH)."\n";
53echo str_pad('', $conf_col_size, '|', STR_PAD_BOTH)."\n";
54echo str_pad(str_pad('Convert MyISAM tables to InnoDB engine: '.($ConvertMyISAMToInnoDB ? 'Yes' : 'No'), $conf_col_size-4, ' ', STR_PAD_BOTH), $conf_col_size, '|', STR_PAD_BOTH)."\n";
55echo str_pad('', $conf_col_size, '|', STR_PAD_BOTH)."\n";
56echo str_pad(str_pad('Updates per loop: '.$UpdatePerLoop, $conf_col_size-4, ' ', STR_PAD_BOTH), $conf_col_size, '|', STR_PAD_BOTH)."\n";
57echo str_pad(str_pad('Sleep seconds between loops: '.$SleepValue, $conf_col_size-4, ' ', STR_PAD_BOTH), $conf_col_size, '|', STR_PAD_BOTH)."\n";
58echo str_pad('', $conf_col_size, '|', STR_PAD_BOTH)."\n";
59
60echo "Are you sure you want to do this? Type 'yes' to continue: ";
61$handle = fopen ("php://stdin","r");
62$line = fgets($handle);
63if(trim($line) != 'yes')
64 {
65 echo "ABORTING!\n";
66 exit;
67 }
68echo "Thank you, continuing...\n\n";
69// Init script variables - End
70
71// Stopping service execution - Start
72echo "[".date('Y-m-d H:i:s')."] Stopping services\n";
73shell_exec('service send-service stop');
74echo "[".date('Y-m-d H:i:s')."] \"send-service\" stopped\n";
75shell_exec('service transactional-send stop');
76echo "[".date('Y-m-d H:i:s')."] \"transactional-send\" stopped\n";
77shell_exec('service import-pmta-reports stop');
78echo "[".date('Y-m-d H:i:s')."] \"import-pmta-reports\" stopped\n";
79shell_exec('service apache2 stop');
80echo "[".date('Y-m-d H:i:s')."] \"apache2\" stopped\n";
81echo "[".date('Y-m-d H:i:s')."] Services stopped, waiting 1 minute before continuing...\n";
82sleep(60);
83// Stopping service execution - End
84
85// Retrieve all database table - Start
86$ArrayTableNames = array();
87$ArrayTableCollationToCorrect = array();
88$ArrayTableToConvertToInnoDB = array();
89$SQLQuery = 'SHOW TABLE STATUS;';
90$SQLResult = Database::$Interface->ExecuteQuery($SQLQuery);
91while ($EachTable = mysql_fetch_assoc($SQLResult))
92 {
93 if (in_array($EachTable['Engine'], array('InnoDB', 'MyISAM'))) // Don't process temporary tables
94 {
95 $ArrayTableNames[] = $EachTable['Name'];
96 if ($EachTable['Collation'] !== $WantedCollation)
97 {
98 $ArrayTableCollationToCorrect[] = $EachTable['Name'];
99 }
100 if ($EachTable['Engine'] === 'MyISAM')
101 {
102 $ArrayTableToConvertToInnoDB[] = $EachTable['Name'];
103 }
104 }
105 }
106$TotalTables = count($ArrayTableNames);
107echo "[".date('Y-m-d H:i:s')."] ".$TotalTables." tables detected\n";
108// Retrieve all database table - End
109
110// iterate on each table - Start
111$ArrayCollationToCorrect = array();
112$ArrayEncodingToCorrect = array();
113$cpt = 1;
114$zero_pad = strlen(strval($TotalTables));
115$max_log_len = 0;
116foreach ($ArrayTableNames as $EachTableName)
117 {
118 $log = "\r[".date('Y-m-d H:i:s')."] (".str_pad($cpt, $zero_pad, '0', STR_PAD_LEFT)."/$TotalTables) scanning \"$EachTableName\"";
119 $max_log_len = $max_log_len < strlen($log) ? strlen($log) : $max_log_len;
120 echo str_pad($log, $max_log_len);
121
122 $SQLQuery = "SHOW FULL COLUMNS FROM ".$EachTableName.";";
123 $SQLResult = Database::$Interface->ExecuteQuery($SQLQuery);
124 while ($EachColumn = mysql_fetch_assoc($SQLResult))
125 {
126 foreach ($ArrayColumnTypeCheck as $EachColumnType)
127 {
128 if (strpos($EachColumn['Type'], $EachColumnType) !== false)
129 {
130 // Seek for column collation to correct - Start
131 if ($EachColumn['Collation'] !== $WantedCollation)
132 {
133 if (isset($ArrayCollationToCorrect[$EachTableName]) === false) { $ArrayCollationToCorrect[$EachTableName] = array(); }
134 if (isset($ArrayCollationToCorrect[$EachTableName][$EachColumn['Field']]) === false) { $ArrayCollationToCorrect[$EachTableName][$EachColumn['Field']] = $EachColumn['Field']; }
135 }
136 // Seek for column collation to correct - End
137
138 // Encoding need to be redo - Start
139 if (isset($ArrayEncodingToCorrect[$EachTableName]) === false) { $ArrayEncodingToCorrect[$EachTableName] = array(); }
140 if (isset($ArrayEncodingToCorrect[$EachTableName][$EachColumn['Field']]) === false) { $ArrayEncodingToCorrect[$EachTableName][$EachColumn['Field']] = $EachColumn; }
141 // Encoding need to be redo - End
142 }
143 }
144 }
145
146 $cpt++;
147 }
148echo str_pad("\r[".date('Y-m-d H:i:s')."] (".str_pad(--$cpt, $zero_pad, '0', STR_PAD_LEFT)."/$TotalTables) tables scanned", $max_log_len);
149// iterate on each table - End
150
151// Compute table collation stats - Start
152$TotalTableCollationToCorrect = count($ArrayTableCollationToCorrect);
153$TableCollationCorrectionLog = implode("\n", $ArrayTableCollationToCorrect);
154// Compute table collation stats - End
155
156// Compute collation stats - Start
157$TotalCollationTableToCorrect = 0;
158$TotalCollationColumnToCorrect = 0;
159$CollationCorrectionLog = '';
160foreach ($ArrayCollationToCorrect as $EachTableName => $ArrayColumns)
161 {
162 $TotalCollationTableToCorrect++;
163 $TotalCollationColumnToCorrect += count($ArrayColumns);
164 $CollationCorrectionLog .= "$EachTableName => ".implode(', ', $ArrayColumns)."\n";
165 }
166// Compute collation stats - End
167
168// Compute encoding stats - Start
169$TotalEncodingTableToCorrect = 0;
170$TotalEncodingColumnToCorrect = 0;
171$EncodingCorrectionLog = '';
172foreach ($ArrayEncodingToCorrect as $EachTableName => $ArrayColumns)
173 {
174 $TotalEncodingTableToCorrect++;
175 $TotalEncodingColumnToCorrect += count($ArrayColumns);
176 $EncodingCorrectionLog .= "$EachTableName => ".implode(', ', $ArrayColumns)."\n";
177 }
178// Compute encoding stats - End
179
180// Compute convertion stats - Start
181$TotalConvertionTableToCorrect = count($ArrayTableToConvertToInnoDB);
182$TableConvertionCorrectionLog = implode("\n", $ArrayTableToConvertToInnoDB);
183// Compute convertion stats - End
184
185// Correction tables collation - Start
186echo "\n[".date('Y-m-d H:i:s')."] TABLES COLLATION - $TotalTableCollationToCorrect tables to correct\n";
187$cpt = 1;
188$zero_pad = strlen(strval(count($ArrayTableCollationToCorrect)));
189$max_log_len = 0;
190foreach ($ArrayTableCollationToCorrect as $EachTableName)
191 {
192 $log = "\r[".date('Y-m-d H:i:s')."] (".str_pad($cpt, $zero_pad, '0', STR_PAD_LEFT)."/$TotalTableCollationToCorrect) correcting table collation on \"$EachTableName\"";
193 $max_log_len = $max_log_len < strlen($log) ? strlen($log) : $max_log_len;
194 echo str_pad($log, $max_log_len);
195 $SQLQuery = "ALTER TABLE `".$EachTableName."` DEFAULT CHARACTER SET '".$WantedCharset."' COLLATE '".$WantedCollation."';";
196 Database::$Interface->ExecuteQuery($SQLQuery);
197 $cpt++;
198 }
199echo str_pad("\r[".date('Y-m-d H:i:s')."] (".str_pad(--$cpt, $zero_pad, '0', STR_PAD_LEFT)."/$TotalTableCollationToCorrect) table collation corrected", $max_log_len);
200// Correction tables collation - End
201
202// Correction columns collation - Start
203echo "\n[".date('Y-m-d H:i:s')."] COLUMNS COLLATION - $TotalCollationTableToCorrect tables concerned ($TotalCollationColumnToCorrect columns)\n";
204$cpt = 1;
205$zero_pad = strlen(strval(count($ArrayCollationToCorrect)));
206$max_log_len = 0;
207foreach ($ArrayCollationToCorrect as $EachTableName => $ArrayColumns)
208 {
209 $log = "\r[".date('Y-m-d H:i:s')."] (".str_pad($cpt, $zero_pad, '0', STR_PAD_LEFT)."/$TotalCollationTableToCorrect) correcting columns collation on \"$EachTableName\"";
210 $max_log_len = $max_log_len < strlen($log) ? strlen($log) : $max_log_len;
211 echo str_pad($log, $max_log_len);
212 $SQLQuery = "ALTER TABLE `".$EachTableName."` CONVERT TO CHARACTER SET '".$WantedCharset."' COLLATE '".$WantedCollation."';";
213 Database::$Interface->ExecuteQuery($SQLQuery);
214 $cpt++;
215 }
216echo str_pad("\r[".date('Y-m-d H:i:s')."] (".str_pad(--$cpt, $zero_pad, '0', STR_PAD_LEFT)."/$TotalCollationTableToCorrect) table columns collation corrected", $max_log_len);
217echo "\n";
218// Correction columns collation - End
219
220// Correction columns encoding - Start
221if ($UpdateColumnEncoding)
222 {
223 echo "[".date('Y-m-d H:i:s')."] ENCODING - $TotalEncodingTableToCorrect tables to correct ($TotalEncodingColumnToCorrect columns)\n";
224 $cpt = 1;
225 $zero_pad = strlen(strval(count($ArrayEncodingToCorrect)));
226 $max_log_len = 0;
227 foreach ($ArrayEncodingToCorrect as $EachTableName => $ArrayColumns)
228 {
229 // Retrieve table PK - Start
230 $SQLQuery = "SELECT `COLUMN_NAME` FROM `information_schema`.`COLUMNS` WHERE `TABLE_SCHEMA` = 'esp' AND `TABLE_NAME` = '".$EachTableName."' AND `COLUMN_KEY` = 'PRI';";
231 $SQLResult = Database::$Interface->ExecuteQuery($SQLQuery);
232 $FirstRow = mysql_fetch_row($SQLResult);
233 $PK = $FirstRow[0];
234 // Retrieve table PK - End
235
236 // Retrieve max PK index - Start
237 $SQLQuery = "SELECT IFNULL(MAX(".$PK."), 0) AS 'MAX' FROM ".$EachTableName.";";
238 $SQLResult = Database::$Interface->ExecuteQuery($SQLQuery);
239 $Result = mysql_fetch_assoc($SQLResult);
240 $MaxIndex = $Result['MAX'];
241 // Retrieve max PK index - End
242
243 // Forge update query base - Start
244 $UpdateQuery = "UPDATE $EachTableName SET ";
245 $flag = 0;
246 foreach ($ArrayColumns as $FieldName => $EachArrayColumn)
247 {
248 $UpdateQuery .= $flag == 0 ? '' : ',';
249 $UpdateQuery .= "`".$FieldName."` = CONVERT(CAST(CONVERT(`".$FieldName."` using latin1) as binary) using ".$WantedCharset.")";
250 $flag = 1;
251 }
252 // Forge update query base - End
253
254 if (is_null($PK) === false)
255 {
256 // Convert all datas via soft updates - Start
257 $Index = 0;
258 while ($Index < $MaxIndex)
259 {
260 // Log
261 $log = "\r[".date('Y-m-d H:i:s')."] (".str_pad($cpt, $zero_pad, '0', STR_PAD_LEFT)."/$TotalEncodingTableToCorrect) converting columns encoding on \"$EachTableName\" - ".number_format(100.0*$Index/$MaxIndex, 2)."%";
262 $max_log_len = $max_log_len < strlen($log) ? strlen($log) : $max_log_len;
263 echo str_pad($log, $max_log_len);
264 // Update
265 $SQLQuery = $UpdateQuery." WHERE ".$PK." > ".$Index." AND ".$PK." <= ".strval($Index+$UpdatePerLoop).";";
266 $SQLResult = Database::$Interface->ExecuteQuery($SQLQuery);
267 $Index += $UpdatePerLoop;
268 usleep($SleepValue * 1000000);
269
270 // Update max PK - Start
271 $SQLQuery = "SELECT IFNULL(MAX(".$PK."), 0) AS 'MAX' FROM ".$EachTableName.";";
272 $SQLResult = Database::$Interface->ExecuteQuery($SQLQuery);
273 $Result = mysql_fetch_assoc($SQLResult);
274 $MaxIndex = $Result['MAX'];
275 // Update max PK - End
276 }
277 // Convert all datas via soft updates - End
278 }
279 else
280 {
281 // Convert all datas one shot - Start
282 $SQLQuery = $UpdateQuery.";";
283 $SQLResult = Database::$Interface->ExecuteQuery($SQLQuery);
284 // Convert all datas one shot - End
285 }
286 echo str_pad("\r[".date('Y-m-d H:i:s')."] (".str_pad($cpt, $zero_pad, '0', STR_PAD_LEFT)."/$TotalEncodingTableToCorrect) converted columns encoding on \"$EachTableName\" - 100.00%", $max_log_len);
287 echo "\n";
288 usleep($SleepValue * 1000000);
289
290 $cpt++;
291 }
292 }
293// Correction columns encoding - End
294
295// Convert MyISAM Tables to InnoDB tables - Start
296if ($ConvertMyISAMToInnoDB)
297 {
298 echo "[".date('Y-m-d H:i:s')."] CONVERTION - $TotalConvertionTableToCorrect tables to convert to InnoDB engine\n";
299 $cpt = 1;
300 $zero_pad = strlen(strval(count($ArrayTableToConvertToInnoDB)));
301 $max_log_len = 0;
302 foreach ($ArrayTableToConvertToInnoDB as $EachTableName)
303 {
304 // Retrieve table PK - Start
305 $SQLQuery = "SELECT `COLUMN_NAME` FROM `information_schema`.`COLUMNS` WHERE `TABLE_SCHEMA` = 'esp' AND `TABLE_NAME` = '".$EachTableName."' AND `COLUMN_KEY` = 'PRI';";
306 $SQLResult = Database::$Interface->ExecuteQuery($SQLQuery);
307 $FirstRow = mysql_fetch_row($SQLResult);
308 $PK = $FirstRow[0];
309 // Retrieve table PK - End
310
311 // Retrieve max PK index - Start
312 $SQLQuery = "SELECT IFNULL(MAX(".$PK."), 0) AS 'MAX' FROM ".$EachTableName.";";
313 $SQLResult = Database::$Interface->ExecuteQuery($SQLQuery);
314 $Result = mysql_fetch_assoc($SQLResult);
315 $MaxIndex = $Result['MAX'];
316 // Retrieve max PK index - End
317
318 // Create InnoDB temporary table - Start
319 $SQLQuery = "CREATE TABLE IF NOT EXISTS ".$EachTableName."_innodb LIKE ".$EachTableName.";";
320 $SQLResult = Database::$Interface->ExecuteQuery($SQLQuery);
321 $SQLQuery = "ALTER TABLE ".$EachTableName."_innodb ENGINE = InnoDB;";
322 $SQLResult = Database::$Interface->ExecuteQuery($SQLQuery);
323 // Create InnoDB temporary table - End
324
325 // Forge update query base - Start
326 $UpdateQuery = "INSERT INTO ".$EachTableName."_innodb SELECT * FROM ".$EachTableName;
327 // Forge update query base - End
328
329 if (is_null($PK) === false)
330 {
331 // Convert all datas via soft updates - Start
332 $Index = 0;
333 while ($Index < $MaxIndex)
334 {
335 // Log
336 $log = "\r[".date('Y-m-d H:i:s')."] (".str_pad($cpt, $zero_pad, '0', STR_PAD_LEFT)."/$TotalConvertionTableToCorrect) converting \"$EachTableName\" to InnoDB engine - ".number_format(100.0*$Index/$MaxIndex, 2)."%";
337 $max_log_len = $max_log_len < strlen($log) ? strlen($log) : $max_log_len;
338 echo str_pad($log, $max_log_len);
339 // Update
340 $SQLQuery = $UpdateQuery." WHERE ".$PK." > ".$Index." AND ".$PK." <= ".strval($Index+$UpdatePerLoop).";";
341 $SQLResult = Database::$Interface->ExecuteQuery($SQLQuery);
342 $Index += $UpdatePerLoop;
343 usleep($SleepValue * 1000000);
344
345 // Update max PK - Start
346 $SQLQuery = "SELECT IFNULL(MAX(".$PK."), 0) AS 'MAX' FROM ".$EachTableName.";";
347 $SQLResult = Database::$Interface->ExecuteQuery($SQLQuery);
348 $Result = mysql_fetch_assoc($SQLResult);
349 $MaxIndex = $Result['MAX'];
350 // Update max PK - End
351 }
352 // Convert all datas via soft updates - End
353 }
354 else
355 {
356 // Convert all datas one shot - Start
357 $SQLQuery = $UpdateQuery.";";
358 $SQLResult = Database::$Interface->ExecuteQuery($SQLQuery);
359 // Convert all datas one shot - End
360 }
361
362 // Swap InnoDB table with MyISAM then delete MyISAM - Start
363 $SQLQuery = "RENAME TABLE ".$EachTableName." TO tmp_table, ".$EachTableName."_innodb TO ".$EachTableName.", tmp_table TO ".$EachTableName."_innodb;";
364 $SQLResult = Database::$Interface->ExecuteQuery($SQLQuery);
365 $SQLQuery = "DROP TABLE ".$EachTableName."_innodb;";
366 $SQLResult = Database::$Interface->ExecuteQuery($SQLQuery);
367 // Swap InnoDB table with MyISAM then delete MyISAM - End
368
369 echo str_pad("\r[".date('Y-m-d H:i:s')."] (".str_pad($cpt, $zero_pad, '0', STR_PAD_LEFT)."/$TotalConvertionTableToCorrect) \"$EachTableName\" converted to InnoDB engine - 100.00%", $max_log_len);
370 echo "\n";
371 usleep($SleepValue * 1000000);
372
373 $cpt++;
374 }
375 }
376// Convert MyISAM Tables to InnoDB tables - End
377
378// Restarting services - Start
379echo "[".date('Y-m-d H:i:s')."] Restarting services\n";
380shell_exec('service apache2 start');
381echo "[".date('Y-m-d H:i:s')."] \"apache2\" started, waiting 1 minute before continuing...\n";
382sleep(60);
383shell_exec('service send-service start');
384echo "[".date('Y-m-d H:i:s')."] \"send-service\" started\n";
385shell_exec('service transactional-send start');
386echo "[".date('Y-m-d H:i:s')."] \"transactional-send\" started\n";
387shell_exec('service import-pmta-reports start');
388echo "[".date('Y-m-d H:i:s')."] \"import-pmta-reports\" started\n";
389// Restarting service - End
390?>