From 67c915aa7094a32b0dfdd4ef60382bb1d3eeb506 Mon Sep 17 00:00:00 2001 From: Simon Rettberg Date: Thu, 21 Feb 2019 11:09:17 +0100 Subject: Fix UUID handling; refactor constraints for machineuuid --- install.php | 84 +++++++++++++++------- .../permissionmanager/install.inc.php | 30 +++----- modules-available/statistics/api.inc.php | 29 ++++---- modules-available/statistics/inc/parser.inc.php | 2 +- modules-available/statistics/install.inc.php | 39 +++++----- modules-available/syslog/install.inc.php | 31 ++++++-- 6 files changed, 126 insertions(+), 89 deletions(-) diff --git a/install.php b/install.php index 88eb82c2..c471f4b6 100644 --- a/install.php +++ b/install.php @@ -50,7 +50,6 @@ define('UPDATE_FAILED', 'UPDATE_FAILED'); // Fatal error occured, retry will not function tableHasColumn($table, $column) { $table = preg_replace('/\W/', '', $table); - $column = preg_replace('/\W/', '', $column); $res = Database::simpleQuery("DESCRIBE `$table`", array(), true); if ($res !== false) { while ($row = $res->fetch(PDO::FETCH_ASSOC)) { @@ -61,6 +60,32 @@ function tableHasColumn($table, $column) return false; } +function tableHasIndex($table, $index) +{ + $table = preg_replace('/\W/', '', $table); + if (!is_array($index)) { + $index = [$index]; + } + $res = Database::simpleQuery("SHOW INDEX FROM `$table`", array(), true); + if ($res !== false) { + $matches = []; + while ($row = $res->fetch(PDO::FETCH_ASSOC)) { + $i = $row['Seq_in_index'] - 1; + if (isset($index[$i]) && $index[$i] === $row['Column_name']) { + if (!isset($matches[$row['Key_name']])) { + $matches[$row['Key_name']] = 0; + } + $matches[$row['Key_name']]++; + } + } + } + foreach ($matches as $m) { + if ($m === count($index)) + return true; + } + return false; +} + function tableDropColumn($table, $column) { $table = preg_replace('/\W/', '', $table); @@ -91,13 +116,13 @@ function tableRename($old, $new) { /** - * Get all constraints from given table+column to another table+column. + * Get all constraint details for given combo. * * @param string $table source table, being constrained * @param string $column source column * @param string $refTable referenced table, dictating the constraints * @param string $refColumn referenced column - * @return false|string[] list of constraints matching the request, false on error + * @return false|string[] false == doesn't exist, assoc array otherwise */ function tableGetConstraints($table, $column, $refTable, $refColumn) { @@ -110,21 +135,12 @@ function tableGetConstraints($table, $column, $refTable, $refColumn) define('CONFIG_SQL_DB', $db); } } - $res = Database::simpleQuery('SELECT `CONSTRAINT_NAME` FROM information_schema.KEY_COLUMN_USAGE' - . ' WHERE `TABLE_SCHEMA` = :db AND `TABLE_NAME` = :table AND `COLUMN_NAME` = :column' - . ' AND `REFERENCED_TABLE_NAME` = :refTable AND `REFERENCED_COLUMN_NAME` = :refColumn', + return Database::queryFirst('SELECT b.CONSTRAINT_NAME, b.UPDATE_RULE, b.DELETE_RULE + FROM information_schema.KEY_COLUMN_USAGE a + INNER JOIN information_schema.REFERENTIAL_CONSTRAINTS b USING (CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME) + WHERE a.TABLE_SCHEMA = :db AND a.TABLE_NAME = :table AND a.COLUMN_NAME = :column + AND a.REFERENCED_TABLE_NAME = :refTable AND a.REFERENCED_COLUMN_NAME = :refColumn', compact('db', 'table', 'column', 'refTable', 'refColumn')); - if ($res === false) - return false; - return $res->fetchAll(PDO::FETCH_COLUMN, 0); -} - -/** - * Because I'm stupid and can't type properly. - */ -function tableGetContraints($table, $column, $refTable, $refColumn) -{ - return tableGetConstraints($table, $column, $refTable, $refColumn); } /** @@ -138,28 +154,42 @@ function tableGetContraints($table, $column, $refTable, $refColumn) * @param string $actions "ON xxx ON yyy" string * @return string UPDATE_* result code */ -function tableAddConstraint($table, $column, $refTable, $refColumn, $actions) +function tableAddConstraint($table, $column, $refTable, $refColumn, $actions, $ignoreError = false) { $test = tableExists($refTable) && tableHasColumn($refTable, $refColumn); if ($test === false) { // Most likely, destination table does not exist yet or isn't up to date return UPDATE_RETRY; } - $test = tableGetConstraints($table, $column, $refTable, $refColumn); - if ($test === false) { - // Should never happen!? - finalResponse(UPDATE_FAILED, 'DB: Cannot query constraints: ' . Database::lastError()); + // TODO: Refactor function, make this two args + $update = 'RESTRICT'; + $delete = 'RESTRICT'; + if (preg_match('/on\s+update\s+(RESTRICT|SET\s+NULL|CASCADE)/ims', $actions, $out)) { + $update = preg_replace('/\s+/ms', ' ', strtoupper($out[1])); } - if (!empty($test)) { - // Already exists - return UPDATE_NOOP; + if (preg_match('/on\s+delete\s+(RESTRICT|SET\s+NULL|CASCADE)/ims', $actions, $out)) { + $delete = preg_replace('/\s+/ms', ' ', strtoupper($out[1])); + } + $test = tableGetConstraints($table, $column, $refTable, $refColumn); + if ($test !== false) { + // Exists, check if same + if ($test['UPDATE_RULE'] === $update && $test['DELETE_RULE'] === $delete) { + // Yep, nothing more to do here + return UPDATE_NOOP; + } + // Kill the old one + $ret = tableDeleteConstraint($table, $test['CONSTRAINT_NAME']); } // Need to create $ret = Database::exec("ALTER TABLE `$table` ADD CONSTRAINT FOREIGN KEY (`$column`) REFERENCES `$refTable` (`$refColumn`) - $actions"); + ON DELETE $delete ON UPDATE $update"); if ($ret === false) { - finalResponse(UPDATE_FAILED, 'DB: Cannot add constraint: ' . Database::lastError()); + if ($ignoreError) { + return UPDATE_FAILED; + } else { + finalResponse(UPDATE_FAILED, 'DB: Cannot add constraint: ' . Database::lastError()); + } } return UPDATE_DONE; } diff --git a/modules-available/permissionmanager/install.inc.php b/modules-available/permissionmanager/install.inc.php index 7f9c80ef..d0f8cdeb 100644 --- a/modules-available/permissionmanager/install.inc.php +++ b/modules-available/permissionmanager/install.inc.php @@ -51,50 +51,40 @@ if (tableHasColumn('role_x_location', 'id')) { if (!tableExists('user') || !tableExists('location')) { finalResponse(UPDATE_RETRY, 'Cannot add constraint yet. Please retry.'); } else { - $c = tableGetContraints('role_x_user', 'userid', 'user', 'userid'); - if ($c === false) - finalResponse(UPDATE_FAILED, 'Cannot get constraints of user table: ' . Database::lastError()); - if (empty($c)) { + $c = tableGetConstraints('role_x_user', 'userid', 'user', 'userid'); + if ($c === false) { $alter = Database::exec('ALTER TABLE role_x_user ADD FOREIGN KEY (userid) REFERENCES user (userid) ON DELETE CASCADE ON UPDATE CASCADE'); if ($alter === false) finalResponse(UPDATE_FAILED, 'Cannot add userid constraint referencing user table: ' . Database::lastError()); $res[] = UPDATE_DONE; } - $c = tableGetContraints('role_x_user', 'roleid', 'role', 'roleid'); - if ($c === false) - finalResponse(UPDATE_FAILED, 'Cannot get constraints of role table: ' . Database::lastError()); - if (empty($c)) { + $c = tableGetConstraints('role_x_user', 'roleid', 'role', 'roleid'); + if ($c === false) { $alter = Database::exec('ALTER TABLE role_x_user ADD FOREIGN KEY (roleid) REFERENCES role (roleid) ON DELETE CASCADE ON UPDATE CASCADE'); if ($alter === false) finalResponse(UPDATE_FAILED, 'Cannot add roleid constraint referencing role table: ' . Database::lastError()); $res[] = UPDATE_DONE; } - $c = tableGetContraints('role_x_location', 'roleid', 'role', 'roleid'); - if ($c === false) - finalResponse(UPDATE_FAILED, 'Cannot get constraints of role table: ' . Database::lastError()); - if (empty($c)) { + $c = tableGetConstraints('role_x_location', 'roleid', 'role', 'roleid'); + if ($c === false) { $alter = Database::exec('ALTER TABLE role_x_location ADD FOREIGN KEY (roleid) REFERENCES role (roleid) ON DELETE CASCADE ON UPDATE CASCADE'); if ($alter === false) finalResponse(UPDATE_FAILED, 'Cannot add roleid constraint referencing role table: ' . Database::lastError()); $res[] = UPDATE_DONE; } - $c = tableGetContraints('role_x_location', 'locationid', 'location', 'locationid'); - if ($c === false) - finalResponse(UPDATE_FAILED, 'Cannot get constraints of location table: ' . Database::lastError()); - if (empty($c)) { + $c = tableGetConstraints('role_x_location', 'locationid', 'location', 'locationid'); + if ($c === false) { $alter = Database::exec('ALTER TABLE role_x_location ADD FOREIGN KEY (locationid) REFERENCES location (locationid) ON DELETE CASCADE ON UPDATE CASCADE'); if ($alter === false) finalResponse(UPDATE_FAILED, 'Cannot add locationid constraint referencing location table: ' . Database::lastError()); $res[] = UPDATE_DONE; } - $c = tableGetContraints('role_x_permission', 'roleid', 'role', 'roleid'); - if ($c === false) - finalResponse(UPDATE_FAILED, 'Cannot get constraints of role table: ' . Database::lastError()); - if (empty($c)) { + $c = tableGetConstraints('role_x_permission', 'roleid', 'role', 'roleid'); + if ($c === false) { $alter = Database::exec('ALTER TABLE role_x_permission ADD FOREIGN KEY (roleid) REFERENCES role (roleid) ON DELETE CASCADE ON UPDATE CASCADE'); if ($alter === false) finalResponse(UPDATE_FAILED, 'Cannot add roleid constraint referencing role table: ' . Database::lastError()); diff --git a/modules-available/statistics/api.inc.php b/modules-available/statistics/api.inc.php index d4b8f346..19ae3cb6 100644 --- a/modules-available/statistics/api.inc.php +++ b/modules-available/statistics/api.inc.php @@ -14,23 +14,20 @@ if (substr($ip, 0, 7) === '::ffff:') $ip = substr($ip, 7); if ($type{0} === '~') { // UUID is mandatory $uuid = Request::post('uuid', '', 'string'); - if (strlen($uuid) !== 36) die("Invalid UUID.\n"); - $macaddr = Request::post('macaddr', '', 'string'); - if (!empty($macaddr) && substr($uuid, 0, 16) === '000000000000001-') { - // Override uuid if the mac is known and unique - $res = Database::simpleQuery('SELECT machineuuid FROM machine WHERE macaddr = :macaddr AND machineuuid <> :uuid', compact('macaddr', 'uuid')); - $override = false; - while ($row = $res->fetch(PDO::FETCH_ASSOC)) { - if ($override !== false) { - $override = false; - break; - } - $override = $row['machineuuid']; - } - if ($override !== false) { - $uuid = $override; + $macaddr = Request::post('macaddr', false, 'string'); + if ($macaddr !== false) { + $macaddr = strtolower(str_replace(':', '-', $macaddr)); + if (strlen($macaddr) !== 17 || $macaddr{2} !== '-') { + $macaddr = false; } } + if ($macaddr !== false && $uuid{8} !== '-' && substr($uuid, 0, 16) === '000000000000001-') { + $uuid = 'baad1d00-9491-4716-b98b-' . str_replace('-', '', $macaddr); + } + if (strlen($uuid) !== 36 || !preg_match('/^[0-9A-F]{8}-[0-9A-F]{4}-[0-9A-F]{4}-[0-9A-F]{4}-[0-9A-F]{12}$/i', $uuid)) { + die("Invalid UUID.\n"); + } + $uuid = strtoupper($uuid); // External mode of operation? $mode = Request::post('mode', false, 'string'); $NOW = time(); @@ -45,7 +42,7 @@ if ($type{0} === '~') { if ($mode === false && $type === '~poweron') { // Poweron & hw stats $uptime = Request::post('uptime', 0, 'integer'); - if (strlen($macaddr) > 17) die("Invalid MAC.\n"); + if ($macaddr === false) die("No/Invalid MAC address.\n"); if ($uptime < 0 || $uptime > 4000000) die("Implausible uptime.\n"); $realcores = Request::post('realcores', 0, 'integer'); if ($realcores < 0 || $realcores > 512) $realcores = 0; diff --git a/modules-available/statistics/inc/parser.inc.php b/modules-available/statistics/inc/parser.inc.php index 0d39079d..8ac3fcf3 100644 --- a/modules-available/statistics/inc/parser.inc.php +++ b/modules-available/statistics/inc/parser.inc.php @@ -90,7 +90,7 @@ class Parser { } } if (empty($row['ramslotcount'])) { - $row['ramslotcount'] = count($row['ramslot']); + $row['ramslotcount'] = isset($row['ramslot']) ? count($row['ramslot']) : 0; } } diff --git a/modules-available/statistics/install.inc.php b/modules-available/statistics/install.inc.php index 84e038a4..a9e8fb7b 100644 --- a/modules-available/statistics/install.inc.php +++ b/modules-available/statistics/install.inc.php @@ -196,32 +196,15 @@ if ($addTrigger) { if (Module::isAvailable('locations')) { if (tableExists('subnet')) { AutoLocation::rebuildAll(); - } else { - finalResponse(UPDATE_RETRY, 'Locations module not installed yet, retry later'); } } $res[] = UPDATE_DONE; } -if ($machineHwCreate === UPDATE_DONE) { - $ret = Database::exec('ALTER TABLE `machine_x_hw` - ADD CONSTRAINT `machine_x_hw_ibfk_1` FOREIGN KEY (`hwid`) REFERENCES `statistic_hw` (`hwid`) ON DELETE CASCADE, - ADD CONSTRAINT `machine_x_hw_ibfk_2` FOREIGN KEY (`machineuuid`) REFERENCES `machine` (`machineuuid`) ON DELETE CASCADE'); - if ($ret === false) { - finalResponse(UPDATE_FAILED, 'Adding constraints to machine_x_hw failed: ' . Database::lastError()); - } - $ret = Database::exec('ALTER TABLE `machine_x_hw_prop` - ADD CONSTRAINT `machine_x_hw_prop_ibfk_1` FOREIGN KEY (`machinehwid`) REFERENCES `machine_x_hw` (`machinehwid`) ON DELETE CASCADE'); - if ($ret === false) { - finalResponse(UPDATE_FAILED, 'Adding constraint to machine_x_hw_prop failed: ' . Database::lastError()); - } - $ret = Database::exec('ALTER TABLE `statistic_hw_prop` - ADD CONSTRAINT `statistic_hw_prop_ibfk_1` FOREIGN KEY (`hwid`) REFERENCES `statistic_hw` (`hwid`) ON DELETE CASCADE'); - if ($ret === false) { - finalResponse(UPDATE_FAILED, 'Adding constraint to statistic_hw_prop failed: ' . Database::lastError()); - } - $res[] = UPDATE_DONE; -} +$res[] = tableAddConstraint('machine_x_hw', 'hwid', 'statistic_hw', 'hwid', 'ON DELETE CASCADE'); +$res[] = tableAddConstraint('machine_x_hw', 'machineuuid', 'machine', 'machineuuid', 'ON DELETE CASCADE ON UPDATE CASCADE'); +$res[] = tableAddConstraint('machine_x_hw_prop', 'machinehwid', 'machine_x_hw', 'machinehwid', 'ON DELETE CASCADE'); +$res[] = tableAddConstraint('statistic_hw_prop', 'hwid', 'statistic_hw', 'hwid', 'ON DELETE CASCADE'); // 2017-11-27: Add state column if (!tableHasColumn('machine', 'state')) { @@ -251,5 +234,19 @@ if (!tableHasColumn('machine', 'live_tmpsize')) { $res[] = UPDATE_DONE; } +// 2019-02-20: Convert bogus UUIDs +$res2 = Database::simpleQuery("SELECT machineuuid, macaddr FROM machine WHERE machineuuid LIKE '000000000000001-%'"); +while ($row = $res2->fetch(PDO::FETCH_ASSOC)) { + $new = strtoupper('baad1d00-9491-4716-b98b-' . preg_replace('/[^0-9a-f]/i', '', $row['macaddr'])); + error_log('Replacing ' . $row['machineuuid'] . ' with ' . $new); + if (strlen($new) === 36) { + if (Database::exec("UPDATE machine SET machineuuid = :new WHERE machineuuid = :old", + ['old' => $row['machineuuid'], 'new' => $new]) === false) { + error_log('Result: ' . Database::lastError()); + Database::exec("DELETE FROM machine WHERE machineuuid = :old", ['old' => $row['machineuuid']]); + } + } +} + // Create response responseFromArray($res); diff --git a/modules-available/syslog/install.inc.php b/modules-available/syslog/install.inc.php index 406a6cc6..9d79c811 100644 --- a/modules-available/syslog/install.inc.php +++ b/modules-available/syslog/install.inc.php @@ -14,7 +14,7 @@ $res[] = $tc = tableCreate('clientlog', " KEY `dateline` (`dateline`), KEY `logtypeid` (`logtypeid`,`dateline`), KEY `clientip` (`clientip`,`dateline`), - KEY `machineuuid` (`machineuuid`,`logid`) + KEY `machineuuidnew` (`machineuuid`,`logid`) "); // Update path @@ -30,9 +30,32 @@ if (!tableHasColumn('clientlog', 'machineuuid')) { } // 2017-11-03: Create proper index for query in statistics module -if ($tc !== UPDATE_DONE) { - Database::exec("ALTER TABLE `openslx`.`clientlog` DROP INDEX `machineuuid` , - ADD INDEX `machineuuid` ( `machineuuid` , `logid` )"); +if (tableHasIndex('clientlog', ['machineuuid'])) { + $r = Database::exec("ALTER TABLE `openslx`.`clientlog` DROP INDEX `machineuuid`"); + $res[] = $r === false ? UPDATE_FAILED : UPDATE_DONE; +} +if (!tableHasIndex('clientlog', ['machineuuid', 'logid'])) { + $r = Database::exec("ALTER TABLE `openslx`.`clientlog` + ADD INDEX `machineuuid` ( `machineuuid` , `logid` )"); + $res[] = $r === false ? UPDATE_FAILED : UPDATE_DONE; +} + +// 2019-02-20: Add constraint for machineuuid +if (tableExists('machine')) { + $rr = tableAddConstraint('clientlog', 'machineuuid', 'machine', 'machineuuid', + 'ON DELETE SET NULL ON UPDATE CASCADE', true); + if ($rr === UPDATE_FAILED) { + // The table might still be populated with orphaned rows + $dups = Database::queryColumnArray("SELECT DISTINCT l.machineuuid FROM clientlog l LEFT JOIN machine m USING (machineuuid) WHERE m.machineuuid IS NULL"); + if (!empty($dups)) { + Database::exec("UPDATE clientlog SET machineuuid = NULL WHERE machineuuid IN (:dups)", ['dups' => $dups]); + $rr = tableAddConstraint('clientlog', 'machineuuid', 'machine', 'machineuuid', + 'ON DELETE SET NULL ON UPDATE CASCADE'); + } + } + $res[] = $rr; +} elseif (Module::get('statistics') !== false) { + $res[] = UPDATE_RETRY; } // Create response for browser -- cgit v1.2.3-55-g7522