Namespaces

Classes

Interfaces

1: <?php
2: declare(strict_types=1);
3: /**
4: * +------------------------------------------------------------+
5: * | apnscp |
6: * +------------------------------------------------------------+
7: * | Copyright (c) Apis Networks |
8: * +------------------------------------------------------------+
9: * | Licensed under Artistic License 2.0 |
10: * +------------------------------------------------------------+
11: * | Author: Matt Saladna (msaladna@apisnetworks.com) |
12: * +------------------------------------------------------------+
13: */
14:
15: use Daphnie\Collector;
16: use Daphnie\Metrics\Mysql as MysqlMetrics;
17: use Module\Skeleton\Contracts\Tasking;
18: use Module\Support\Sql;
19: use Opcenter\Database\MySQL\DefinerRemap;
20: use Opcenter\Net\IpCommon;
21:
22: /**
23: * MySQL and PostgreSQL operations
24: *
25: * @package core
26: */
27: class Mysql_Module extends Sql implements Tasking
28: {
29: const DEPENDENCY_MAP = [
30: 'siteinfo',
31: 'diskquota'
32: ];
33: const MYSQL_DATADIR = '/var/lib/mysql';
34: // @var int minimum MySQL version to use new API
35: const NEW_API_VERSION = 50720;
36: // @var int default concurrency setting for users
37: const DEFAULT_CONCURRENCY_LIMIT = 10;
38: // @var int maximum concurrent connections per user
39: const MAX_CONCURRENCY_LIMIT = MYSQL_CONCURRENCY_LIMIT ?: DATABASE_CONCURRENCY_LIMIT;
40:
41: // @var string MySQL export command
42: const EXPORT_CMD = '/usr/bin/mysqldump --quick --single-transaction --add-drop-table --add-drop-trigger --triggers --events -q -R';
43:
44: protected $exportedFunctions = array(
45: '*' => PRIVILEGE_SITE,
46: 'version' => PRIVILEGE_ALL,
47: 'get_elevated_password_backend' => PRIVILEGE_ALL | PRIVILEGE_SERVER_EXEC,
48: 'create_database_backend' => PRIVILEGE_SITE | PRIVILEGE_SERVER_EXEC,
49: 'delete_database_backend' => PRIVILEGE_SITE | PRIVILEGE_SERVER_EXEC,
50: 'get_uptime' => PRIVILEGE_ALL,
51: 'assert_permissions' => PRIVILEGE_SITE | PRIVILEGE_SERVER_EXEC,
52: 'set_option' => PRIVILEGE_ALL,
53: 'get_option' => PRIVILEGE_ALL,
54: 'export_pipe_real' => PRIVILEGE_SITE | PRIVILEGE_SERVER_EXEC,
55: 'enabled' => PRIVILEGE_SITE | PRIVILEGE_USER,
56: 'repair_database' => PRIVILEGE_SITE | PRIVILEGE_ADMIN,
57: 'get_prefix' => PRIVILEGE_SITE | PRIVILEGE_USER,
58:
59: // necessary for DB backup routines
60: 'get_database_size' => PRIVILEGE_SITE | PRIVILEGE_ADMIN,
61: 'database_exists' => PRIVILEGE_SITE | PRIVILEGE_ADMIN,
62: '_export_old' => PRIVILEGE_SITE | PRIVILEGE_SERVER_EXEC,
63: 'resolve_site_from_database' => PRIVILEGE_SITE | PRIVILEGE_ADMIN,
64: 'recover_innodb_from_disk' => PRIVILEGE_ADMIN,
65: 'stats' => PRIVILEGE_ALL
66: );
67:
68: public function __destruct()
69: {
70: foreach ($this->_tempUsers as $user) {
71: if (!$this->user_exists($user)) {
72: continue;
73: }
74: $this->_delete_temp_user($user);
75: }
76: }
77:
78: public function user_exists($user, $host = 'localhost')
79: {
80: if (!$user) {
81: return false;
82: }
83: $conn = $this->_connect_root();
84: $prefix = $this->get_prefix();
85: if (!($this->permission_level & PRIVILEGE_ADMIN) && $user !== $this->getServiceValue('mysql', 'dbaseadmin') &&
86: 0 !== strpos($user, $prefix)
87: ) {
88: $user = $prefix . $user;
89: }
90:
91: $q = $conn->query("SELECT user FROM user WHERE user = '" .
92: $conn->escape_string($user) . "' AND host = '" . $conn->escape_string($host) . "'");
93:
94: return !$q || $q->num_rows > 0;
95: }
96:
97: public function get_prefix()
98: {
99: return $this->getServiceValue('mysql', 'dbaseprefix');
100: }
101:
102: /**
103: * Delete a temporary MySQL user
104: *
105: * @warn do not invoke directly, use wrapper _delete_temp_user()
106: * @param string $user
107: * @return bool
108: */
109: private function _delete_temp_user($user)
110: {
111: if (!Opcenter\Database\MySQL::deleteUser($user, 'localhost')) {
112: return false;
113: }
114:
115:
116: $idx = array_search($user, $this->_tempUsers, true);
117: if ($idx !== false) {
118: unset($this->_tempUsers[$idx]);
119: }
120:
121: return true;
122: }
123:
124: /**
125: * bool delete_mysql_user(string, string[, bool = false])
126: * Delete a MySQL user
127: *
128: * @param string $user username
129: * @param string $host hostname
130: */
131: public function delete_user($user, $host)
132: {
133: if ($user === $this->username && !Util_Account_Hooks::is_mode('delete')) {
134: return error('Cannot remove main user');
135: } else if (!$this->user_exists($user, $host)) {
136: return error("user `%s' on `%s' does not exist", $user, $host);
137: }
138: $prefix = $this->get_prefix();
139: if ($user !== $this->getConfig('mysql', 'dbaseadmin') && strpos($user, $prefix) !== 0) {
140: $user = $prefix . $user;
141: }
142: if (\Opcenter\Database\MySQL::version() >= static::NEW_API_VERSION) {
143: return Opcenter\Database\MySQL::deleteUser($user, $host);
144: }
145: $conn = new mysqli('localhost', self::MASTER_USER, $this->_get_elevated_password());
146: $conn->select_db('mysql');
147: $stmt = $conn->prepare('DELETE FROM user WHERE user = ? AND host = ?');
148: $stmt->bind_param('ss', $user, $host);
149: $stmt->execute();
150: if ($stmt->error) {
151: return new MySQLError('Invalid query, ' . $stmt->error);
152: }
153:
154: $stmt2 = $conn->prepare('DELETE FROM db WHERE user = ? AND host = ?');
155: $stmt2->bind_param('ss', $user, $host);
156: $stmt2->execute();
157: if (!$stmt2->error) {
158: $conn->query('FLUSH PRIVILEGES');
159: } else {
160: return new MySQLError('Invalid query, ' . $stmt2->error);
161: }
162:
163: return ($stmt->affected_rows > 0);
164:
165: }
166:
167: /**
168: * bool store_sql_password (string, string)
169: *
170: * @param string $sqlpasswd plaintext password
171: * @return bool
172: */
173: public function store_password($sqlpasswd)
174: {
175: return $this->set_option('password', $sqlpasswd, 'client');
176: }
177:
178: /**
179: * Set MySQL client option
180: *
181: * Reads from ~/.my.cnf and creates if necessary.
182: * Specify a value of null to remove an option.
183: *
184: * @param string $option
185: * @param string $value
186: * @param string $group
187: * @return bool
188: */
189: public function set_option(string $option, string $value = null, string $group = 'client')
190: {
191: if (DEMO_ADMIN_LOCK && posix_getuid()) {
192: return error("Demo may not alter ~/.my.cnf");
193: }
194:
195: if (!IS_CLI) {
196: return $this->query('mysql_set_option', $option, $value, $group);
197: }
198: $home = $this->user_get_user_home();
199: $path = $this->domain_fs_path() . "${home}/.my.cnf";
200: if (is_link($path) && (
201: false === ($link = readlink($path)) ||
202: 0 !== strpos($this->domain_fs_path(), realpath($link)))
203: )
204: {
205: unlink($path);
206: }
207: if (!file_exists($path)) {
208: \Opcenter\Filesystem::touch($path, $this->user_id, $this->group_id, 0600);
209: }
210:
211: return \Opcenter\Database\MySQL::setUserConfigurationField($path, $option, $value, $group);
212: }
213:
214: /**
215: * string retrieve_sql_password (string)
216: *
217: * @return string
218: */
219: public function get_password()
220: {
221: return $this->get_option('password');
222: }
223:
224: /**
225: * Get option from MySQL client/server configuration
226: *
227: * @param string $option option name
228: * @param string $group option group
229: * @return mixed option value, false on failure, null on non-existent value
230: */
231: public function get_option($option, $group = 'client')
232: {
233: if (DEMO_ADMIN_LOCK && ($this->permission_level & PRIVILEGE_ADMIN) && posix_getuid()) {
234: return error("Demo may not alter ~/.my.cnf");
235: }
236:
237: if (!IS_CLI) {
238: return $this->query('mysql_get_option', $option, $group);
239: }
240: $home = $this->user_get_user_home();
241: $paths = [
242: $this->domain_fs_path() . "${home}/.my.cnf",
243: '/etc/my.cnf'
244: ];
245: foreach ($paths as $path) {
246: if (is_link($path) && 0 !== strpos($this->domain_fs_path(), realpath(readlink($path)))) {
247: // naughty!
248: continue;
249: }
250: if (null !== ($val = array_get(\Opcenter\Database\MySQL::getUserConfiguration($path),
251: "${group}.${option}", null))) {
252: return $val;
253: }
254: }
255:
256: return null;
257: }
258:
259: public function get_elevated_password_backend()
260: {
261: if (!IS_CLI) {
262: fatal('needs execution from backend');
263: }
264:
265: return Opcenter\Database\MySQL::rootPassword();
266: }
267:
268: /**
269: * Import a database from a dump
270: *
271: * @see Mysql_Module::export()
272: *
273: * @param string $db database name
274: * @param string $file filename
275: * @return bool
276: */
277: public function import($db, $file)
278: {
279: if (!IS_CLI) {
280: return $this->query('mysql_import', $db, $file);
281: }
282:
283: $prefix = $this->get_prefix();
284: // db name passed without prefix
285: if (strncmp($db, $prefix, strlen($prefix))) {
286: $db = $prefix . $db;
287: }
288:
289: $dbs = $this->list_databases();
290: if (false === array_search($db, $dbs, true)) {
291: return error("database `%s' does not exist", $db);
292: }
293: $unlink = null;
294: if (false === ($realfile = $this->_preImport($file, $unlink))) {
295: return false;
296: }
297:
298: $tempUser = $this->_create_temp_user($db);
299: if (!$tempUser) {
300: $this->_postImport($unlink);
301: return error('unable to import database');
302: }
303:
304: /**
305: * Tricky spot! triggers/events are dumped with definer information, which conflicts
306: * with anonymous user imports. Util_Process doesn't handle callbacks on piped input (@todo)
307: * Perform an intermediate sed on statements with DEFINER, change to current user, outputting previous,
308: * then depending upon whether the user exists (non-prefixed match), apply a second query to translate
309: * all triggers/events/procedures to the new user
310: *
311: * Only one remap is supported at this time
312: */
313:
314: $bulk = [];
315: // @todo refactor to utility class
316: if (filetype($realfile) === 'file') {
317: $ret = Util_Process_Safe::exec('sed -i %s %s', [
318: '{
319: /DEFINER=[^* ]*\(\*\| \)/w /dev/stdout
320: s/DEFINER=[^* ]*\(\*\| \)/\1/g
321: }',
322: $realfile
323: ]);
324:
325: if (!$ret['success']) {
326: return error("Aborting import from `%s'. Failed to perform DEFINER pass: %s", $file, $ret['error']);
327: }
328:
329: // make a "best effort" at determining the new ownership
330: if (preg_match_all(Regex::SQL_MYSQL_DEFINER, $ret['stdout'], $matches, PREG_SET_ORDER)) {
331: // default to admin@localhost
332: $bulkChangeCredentials = [];
333: $prefix = $this->get_prefix();
334: $users = [];
335: foreach ($this->list_users() as $user => $hosts) {
336: if (0 === strpos($user, $prefix)) {
337: $user = substr($user, \strlen($prefix));
338: }
339: $users[$user] = [];
340: foreach (array_keys($hosts) as $host) {
341: $users[$user][$host] = 1;
342: }
343: }
344:
345: foreach ($matches as $gm) {
346: // PROCEDURE/FUNCTION is the same
347: if ($gm['type'] === 'FUNCTION') {
348: $gm['type'] = 'PROCEDURE';
349: }
350:
351: if (false !== ($pos = strpos($gm['user'], '_'))) {
352: $gm['user'] = substr($gm['user'], ++$pos);
353: }
354:
355: if (!isset($users[$gm['user']])) {
356: warn(
357: 'User %s not found in user table. Converting %s grant for %s to %s',
358: $gm['user'], $gm['type'], $gm['name'], $this->getConfig('mysql', 'dbaseadmin')
359: );
360: $gm['user'] = $this->getConfig('mysql', 'dbaseadmin');
361: $gm['host'] = 'localhost';
362: } else if (!isset($users[$gm['user']][$gm['host']])) {
363: warn(
364: 'Host %s not found in user table. Converting %s grant for %s to localhost',
365: $gm['host'], $gm['type'], $gm['user']
366: );
367: $gm['host'] = 'localhost';
368: }
369:
370: if (empty($bulkChangeCredentials)) {
371: $bulkChangeCredentials = [
372: $gm['user'],
373: $gm['host']
374: ];
375: } else if ($bulkChangeCredentials !== [$gm['user'], $gm['host']]) {
376: warn(
377: 'Import only supports 1 DEFINER adjustment. Converting %s (type: %s) from `%s`@`%s` to `%s`@`%s`',
378: $gm['name'], $gm['type'], $gm['user'], $gm['host'], $bulkChangeCredentials[0],
379: $bulkChangeCredentials[1]
380: );
381: }
382: if (empty($bulk[$gm['type']])) {
383: $bulk[$gm['type']] = [];
384: }
385: $bulk[$gm['type']][] = $gm['name'];
386: }
387: // slip prefix back into user if not main
388: if (isset($bulkChangeCredentials[0]) && $bulkChangeCredentials[0] !== $this->getConfig('mysql',
389: 'dbaseadmin')) {
390: $bulkChangeCredentials[0] = $this->getConfig('mysql',
391: 'dbaseprefix') . $bulkChangeCredentials[0];
392: }
393: }
394: }
395:
396:
397: $safe = new Util_Process_Safe();
398: $safe->setDescriptor(0, 'file', [$realfile, 'rb'], null, ['mute_stdin' => false]);
399: $safe->setPriority(19);
400: $safe->addCallback(function() use ($unlink, $tempUser) {
401: $this->_postImport($unlink);
402: $this->_delete_temp_user($tempUser);
403: return true;
404: }, 'close');
405:
406: $status = $safe->run('mysql -u %s %s', $tempUser, $db);
407: if (!$status['success']) {
408: return false;
409: }
410: if ($bulk) {
411: $handler = new DefinerRemap($db, $this->_connect_root(true));
412: // spray and pray
413: $handler->remapAll(...$bulkChangeCredentials);
414: }
415:
416: return $status['success'];
417: }
418:
419: /**
420: * Resolve database from site
421: *
422: * @param string $db
423: * @return string|null
424: */
425: public function resolve_site_from_database(string $db): ?string
426: {
427: $db = strtok($db, '/');
428: $prefix = strtok($db, '_') . '_';
429:
430: if ($this->site) {
431: return $this->database_exists($db) ? $this->site : null;
432: }
433:
434: if (!preg_match(Regex::SQL_DATABASE, $db)) {
435: error("Invalid database name");
436: return null;
437: }
438:
439: if ($db === $prefix) {
440: $path = realpath(self::MYSQL_DATADIR . '/' . $prefix);
441: if (!$path || !str_starts_with($path, FILESYSTEM_VIRTBASE)) {
442: return null;
443: }
444: $site = strtok(substr($path, strlen(FILESYSTEM_VIRTBASE)), '/');
445: } else {
446: $site = Opcenter\Map::read(\Opcenter\Database\MySQL::PREFIX_MAP)[$prefix];
447: }
448:
449: if (!$site && $this->site && !str_starts_with($db, $prefix)) {
450: $db = $this->get_prefix() . $db;
451:
452: return $this->resolve_site_from_database($db);
453: }
454:
455: return $site ?: null;
456: }
457:
458: /**
459: * Recover InnoDB from .ibd files
460: *
461: * Existing .ibd are NOT overwritten.
462: *
463: * @param string $db target database
464: * @param string $srcdir source directory containing .ibd files
465: * @param bool $force overwrite .ibd if it exists
466: * @return bool
467: */
468: public function recover_innodb_from_disk(string $db, string $srcdir, bool $force = false): bool
469: {
470: if (!IS_CLI) {
471: return $this->query('mysql_recover_innodb_from_disk', $db, $srcdir, $force);
472: }
473:
474: if (!\Opcenter\Database\MySQL::databaseExists($db)) {
475: return error("Database `%s' does not exist", $db);
476: }
477: if (!is_dir($srcdir)) {
478: return error("Source path `%s' is not a directory", $srcdir);
479: }
480:
481: $glob = array_map(
482: static function ($file) { return basename($file, '.ibd'); },
483: glob("${srcdir}/*.ibd", GLOB_NOESCAPE)
484: );
485:
486: if (empty($glob)) {
487: return error("No matching *.ibd files found in `%s'", $srcdir);
488: }
489: $tables = \Opcenter\Database\MySQL::getTablesFromDatabase($db);
490:
491: // missing from $dir and cannot be recovered
492: $missingSource = array_values(array_diff($tables, $glob));
493: // present in $dir but missing in database - weird.
494: $missingSchema = array_values(array_diff($glob, $tables));
495:
496: $user = $this->_create_temp_user($db);
497:
498: $datadir = realpath(self::MYSQL_DATADIR . '/' . \Opcenter\Database\MySQL::canonicalize($db));
499: if (!$datadir || !($stat = stat($datadir))) {
500: return error(
501: "Unable to stat `%s'",
502: self::MYSQL_DATADIR . '/' . \Opcenter\Database\MySQL::canonicalize($db)
503: );
504: }
505:
506: $conn = MySQL::stub();
507: $conn->connect('localhost', $user, null, $db);
508: $conn->query('SET FOREIGN_KEY_CHECKS=0');
509:
510: $importer = static function($table) use ($conn, $srcdir, $datadir, $stat): bool {
511: $ex = \Error_Reporter::exception_upgrade(\Error_Reporter::E_FATAL);
512: try {
513: $conn->query("LOCK TABLES `${table}` WRITE");
514: } catch (apnscpException $e) {
515: // pass
516: } finally {
517: \Error_Reporter::exception_upgrade($ex);
518: }
519:
520: $conn->query("ALTER TABLE `${table}` DISCARD TABLESPACE");
521:
522: if (!copy("${srcdir}/${table}.ibd", "${datadir}/${table}.ibd")) {
523: return false;
524: }
525: if (!\Opcenter\Filesystem::chogp("${datadir}/${table}.ibd", $stat['uid'], $stat['gid'], 0600)) {
526: return false;
527: }
528: return (bool)$conn->query("ALTER TABLE `${table}` IMPORT TABLESPACE");
529: };
530:
531: try {
532: // these files have both a .ibd and exist in table schema, EZ
533: foreach (array_intersect($glob, $tables) as $table) {
534: if (!$force && file_exists("${datadir}/${table}.ibd")) {
535: info("InnoDB file `%(path)s/%(table)s.ibd' already exists - skipping %(db)s.%(table)s",
536: ['path' => $datadir, 'table' => $table, 'db' => $db]);
537: continue;
538: }
539: if (!$importer($table)) {
540: warn('Failed to replace table %(table)s in %(db)s',
541: ['table' => $table, 'db' => $db]);
542: } else {
543: info('Restored %(db)s.%(table)s', ['db' => $db, 'table' => $table]);
544: }
545: }
546:
547: // these files exist in the backup, but lack a .frm in data directory - let's try anyway!
548: foreach ($missingSchema as $table) {
549: if (!file_exists("$srcdir/${table}.frm")) {
550: // it's fucked
551: continue;
552: }
553: warn('%s.%s present in backup, .frm present in data directory, but .ibd missing - continuing import', $db, $table);
554: foreach (['ibd', 'frm'] as $ext) {
555: if (!$force && file_exists("${datadir}/${table}.${ext}")) {
556: info("InnoDB file `%s/%s.%s' already exists - skipping %s.%s",
557: $datadir, $table, $ext, $db, $table);
558: continue;
559: }
560: $ret = copy("${srcdir}/${table}.${ext}", "${datadir}/${table}.${ext}") &&
561: \Opcenter\Filesystem::chogp("${datadir}/${table}.${ext}", $stat['uid'], $stat['gid'], 0600);
562:
563: if (!$ret) {
564: warn('Failed to replace table %s in %s', $table, $db);
565: continue 2;
566: }
567: }
568: // @TODO import/discard tablespace?
569: info('Restored %s.%s', $db, $table);
570: }
571:
572: foreach ($missingSource as $table) {
573: if (file_exists("${datadir}/${table}.ibd") || file_exists("${datadir}/${table}.MYI")) {
574: continue;
575: }
576: warn("%(db)s.%(table)s reported in `%(path)s' but no backup in %(srcdir)s",
577: ['db' => $db, 'table' => $table, 'path' => $datadir, 'srcdir' => $srcdir]);
578: }
579: } catch (\Throwable $e) {
580: return error("Failed to restore backup into `%(db)s': %(err)s",
581: ['db' => $db, 'err' => $e->getMessage()]);
582: } finally {
583: $conn->query('SET FOREIGN_KEY_CHECKS=1');
584: }
585: // lock is released anyway
586: $conn->query('UNLOCK TABLES');
587: return true;
588: }
589:
590: /**
591: * array list_mysql_databases ()
592: * Queries the db table in the mysql database for applicable grants
593: *
594: * @return array list of databases
595: */
596: public function list_databases()
597: {
598: $prefix = $this->get_prefix();
599: if (!$prefix) {
600: // compatibility with DTSS behavior in MySQL
601: if (!$this->enabled()) {
602: return [];
603: }
604: report('Prefixless site - bug');
605: return [];
606: }
607: $conn = new mysqli('localhost', self::MASTER_USER, $this->_get_elevated_password());
608: $conn->select_db('mysql');
609: $q = $conn->query("SELECT DISTINCT(REPLACE(db,'\\_','_')) AS db FROM db WHERE db LIKE '" . str_replace('_', '\\_', $prefix) . "%' OR db LIKE '" . str_replace('_', '\\\\_', $prefix) . "%' OR user = '" . $this->username . "'");
610: $dbs = array();
611: while (null !== ($row = $q->fetch_object())) {
612: $dbs[] = $row->db;
613: }
614:
615: $conn->close();
616:
617: return $dbs;
618: }
619:
620: /**
621: * Create a temporary mysql user
622: *
623: * @param string $db
624: * @return string|object
625: */
626: private function _create_temp_user($db)
627: {
628: // allow use by admin
629: $prefix = (string)$this->get_prefix();
630: $maxlen = \Opcenter\Database\MySQL::fieldLength('user') - strlen($prefix);
631: if ($maxlen < 1) {
632: warn('temp mysql user exceeds field length, cannot create user');
633: return false;
634: }
635: $chars = array(
636: 'a',
637: 'b',
638: 'c',
639: 'd',
640: 'e',
641: 'f',
642: '0',
643: '1',
644: '2',
645: '3',
646: '4',
647: '5',
648: '6',
649: '7',
650: '8',
651: '9',
652: );
653: $maxlen = min(8, $maxlen);
654:
655: $user = $prefix;
656: for ($i = 0; $i < $maxlen; $i++) {
657: $n = random_int(0, 15);
658: $user .= $chars[$n];
659: }
660:
661: // could be handled via add_mysql_user()
662: $sqldb = $this->_connect_root();
663: $q = "SELECT user FROM user WHERE user = '" . $user . "'";
664: $rs = $sqldb->query($q);
665: if ($rs->num_rows > 0) {
666: return error('cannot create temp mysql user');
667: }
668: $q = "CREATE USER '" . $user . "'@'localhost' IDENTIFIED BY ''";
669: $rs = $sqldb->query($q);
670: if (!$rs) {
671: return error('failed to create temp mysql user');
672: }
673: $q = 'GRANT ALL ON `' . $db . "`.* to '" . $sqldb->escape_string($user) . "'@localhost";
674: $rs = $sqldb->query($q);
675: if (!$rs) {
676: return error('failed to create temp mysql user');
677: }
678: $q = "GRANT SELECT ON mysql.proc TO '" . $sqldb->escape_string($user) . "'@localhost";
679: $sqldb->query($q);
680: $this->_register_temp_user($user);
681:
682: return $user;
683: }
684:
685: /**
686: * Change account database prefix
687: *
688: * @param string $prefix
689: * @return bool
690: */
691: public function change_prefix($prefix)
692: {
693: return error('use sql_change_prefix');
694: }
695:
696: public function get_sql_prefix()
697: {
698: deprecated('use sql_get_prefix');
699:
700: return $this->get_prefix();
701: }
702:
703: /**
704: * array list_mysql_users ()
705: * Lists all created users for MySQL
706: */
707: public function list_users()
708: {
709: // meta is corrupted, let's bail
710: if (!$prefix = $this->getServiceValue('mysql', 'dbaseprefix')) {
711: return [];
712: }
713:
714: $prefix = str_replace('_', '\_', $prefix);
715: if (!$prefix) {
716: report('Prefixless site - bug');
717:
718: return [];
719: }
720: $conn = new mysqli('localhost', self::MASTER_USER, $this->_get_elevated_password());
721: $conn->select_db('mysql');
722: $q = $conn->query("SELECT host,
723: user,
724: password,
725: ssl_type,
726: ssl_cipher,
727: x509_issuer,
728: x509_subject,
729: max_questions,
730: max_updates,
731: max_connections,
732: max_user_connections FROM user WHERE user = '" . $this->username . "' OR user LIKE '" . $prefix . "%'");
733: $users = array();
734: while (null !== ($row = $q->fetch_array(MYSQLI_ASSOC))) {
735: $row = array_change_key_case($row, CASE_LOWER);
736: $user = $row['user']; $host = $row['host'];
737: $users[$user][$host] = array(
738: 'ssl_type' => $row['ssl_type'],
739: 'ssl_cipher' => $row['ssl_cipher'],
740: 'x509_issuer' => $row['x509_issuer'],
741: 'x509_subject' => $row['x509_subject'],
742: 'max_questions' => (int)$row['max_questions'],
743: 'max_updates' => (int)$row['max_updates'],
744: 'max_user_connections' => (int)$row['max_user_connections'],
745: 'max_connections' => (int)$row['max_connections'],
746: 'password' => $row['password'],
747: );
748: }
749:
750: return $users;
751: }
752:
753: /**
754: * bool add_mysql_user(string, string, string[, int[, int[, int[, string[, string[, string[, string]]]]]]])
755: *
756: * @param $user
757: * @param $host
758: * @param $password
759: * @param int $maxconn
760: * @param int $maxupdates
761: * @param int $maxquery
762: * @param string $ssl
763: * @param string $cipher
764: * @param string $issuer
765: * @param string $subject
766: * @return bool|MySQLError|void
767: */
768: public function add_user(
769: $user,
770: $host,
771: $password,
772: $maxconn = self::DEFAULT_CONCURRENCY_LIMIT,
773: $maxupdates = 0,
774: $maxquery = 0,
775: $ssl = '',
776: $cipher = '',
777: $issuer = '',
778: $subject = ''
779: ) {
780: if (!$this->enabled()) {
781: return error("%(service)s is disabled", 'mysql');
782: }
783:
784: if (!$user) {
785: return error('no username specified');
786: }
787: $dbaseadmin = $this->getConfig('mysql', 'dbaseadmin');
788: if ($user === $dbaseadmin && !IS_SOAP) {
789: return error("cannot name user after primary account user, `%s'", $dbaseadmin);
790: }
791:
792: $ssl = strtoupper($ssl);
793: if (!$maxconn) {
794: $maxconn = self::DEFAULT_CONCURRENCY_LIMIT;
795: }
796: $host = trim($host);
797: if ($host != 'localhost' && !IpCommon::valid($host) && !preg_match(Regex::SQL_MYSQL_IP_WILDCARD, $host)) {
798: return error("rejected host `%s': only numeric IP addresses are permitted, not hostnames", $host);
799: }
800: if (strlen($password) < self::MIN_PASSWORD_LENGTH) {
801: return error('Password must be at least %d characters', self::MIN_PASSWORD_LENGTH);
802: } else if ($ssl !== '' && $ssl != 'ANY' && $ssl != 'X509' && $ssl != 'SPECIFIED') {
803: return error('Invalid SSL type');
804: } else if ($maxconn < 1 || $maxquery < 0 || $maxupdates < 0) {
805: return error('Max connections, queries, and updates must be greater than 0');
806: } else if ($maxconn > static::MAX_CONCURRENCY_LIMIT) {
807: return error('Max concurrent connections cannot exceed %d. ' .
808: 'Contact support if you need more than %d.',
809: static::MAX_CONCURRENCY_LIMIT, static::MAX_CONCURRENCY_LIMIT);
810: } else if ($this->user_exists($user, $host)) {
811: return error("mysql user `$user' on `$host' exists");
812: }
813: $conn = $this->_connect_root();
814: $prefix = $this->get_prefix();
815: if ($user !== $this->getConfig('mysql', 'dbaseadmin') && 0 !== strpos($user, $prefix)) {
816: // add the prefix if prefix is not provided, this is to workaround cases where user
817: // is equal to prefixprefixuser
818: $user = $prefix . $user;
819: }
820: if (Opcenter\Database\MySQL::version() >= static::NEW_API_VERSION) {
821: return \Opcenter\Database\MySQL::createUser(
822: $user,
823: $password,
824: $host,
825: [
826: 'ssl' => $ssl,
827: 'cipher' => $cipher,
828: 'issuer' => $issuer,
829: 'subject' => $subject
830: ],
831: [
832: 'connections' => (int)$maxconn,
833: 'query' => (int)$maxquery,
834: 'updates' => (int)$maxupdates
835: ]
836: ) ?: error("User creation failed on `%s@%s'", $user, $host);
837: }
838: $pwclause = 'password(?)';
839: // password is encrypted in new pw form or old
840: if ((\strlen($password) === 16 && ctype_xdigit($password)) ||
841: ($password[0] == '*' && \strlen($password) === 41
842: && ctype_xdigit(substr($password, 1))))
843: {
844: $pwclause = '?';
845: }
846: $needAuth = $conn->columnExists('authentication_string', 'user');
847: $query = 'INSERT INTO user
848: (host,
849: user,
850: password,
851: ssl_type,
852: ssl_cipher,
853: x509_issuer,
854: x509_subject,
855: max_questions,
856: max_updates,
857: max_user_connections' . ($needAuth ? ', authentication_string' : '') . ')
858: VALUES
859: (?,
860: ?,
861: ' . $pwclause . ',
862: ?,
863: ?,
864: ?,
865: ?,
866: ?,
867: ?,
868: ?' . ($needAuth ? ',""' : '') . ');';
869: $stmt = $conn->prepare($query);
870: $stmt->bind_param('sssssssiii', $host, $user, $password, $ssl, $cipher,
871: $issuer, $subject, $maxquery, $maxupdates, $maxconn);
872: $stmt->execute();
873: if ($stmt->error) {
874: return new MySQLError('Invalid query, ' . $stmt->error);
875: }
876: $conn->query('FLUSH PRIVILEGES;');
877: if ($stmt->affected_rows < 1) {
878: return error("user creation `%s@%s' failed", $user, $host);
879: }
880:
881: return true;
882: }
883:
884: /**
885: * Get charset from database
886: *
887: * @param $db
888: * @return null|string
889: */
890: public function get_database_charset(string $db): ?string
891: {
892: if ($this->permission_level & (PRIVILEGE_USER|PRIVILEGE_SITE)) {
893: $prefix = $this->getServiceValue('mysql', 'dbaseprefix');
894: if (0 !== strpos($db, $prefix)) {
895: $db = $prefix . $db;
896: }
897: }
898: if (!\in_array($db, $this->list_databases(), true)) {
899: error('Invalid database %s', $db);
900: return null;
901: }
902: $conn = $this->_connect_root();
903: $q = "SELECT default_character_set_name FROM information_schema.SCHEMATA
904: WHERE schema_name = '" . $conn->escape_string($db). "';";
905: $rs = $conn->query($q);
906:
907: if (!$rs->num_rows) {
908: return null;
909: }
910: return $rs->fetch_object()->default_character_set_name;
911: }
912:
913: /**
914: * Create a new mysql database
915: *
916: * @param string $db
917: * @param string $charset optional default charset
918: * @param string $collation optional default collation
919: * @return bool creation succeeded
920: */
921:
922: public function create_database($db, $charset = 'latin1', $collation = 'latin1_swedish_ci')
923: {
924: if (!IS_CLI) {
925: return $this->query('mysql_create_database', $db, $charset, $collation);
926: }
927:
928: if (!$this->enabled()) {
929: return error("%(service)s is disabled", 'mysql');
930: }
931:
932: $charset = strtolower($charset);
933: $collation = strtolower($collation);
934:
935: if (!preg_match(Regex::SQL_DATABASE, $db)) {
936: return error("invalid database name `%s'", $db);
937: }
938: if (!$this->charset_valid($charset)) {
939: return error("unrecognized mysql charset `%s'", $charset);
940: }
941: if (!$this->collation_valid($collation)) {
942: return error("invalid mysql collation `%s'", $collation);
943: } else if (!$this->collation_compatible($collation, $charset)) {
944: warn("collation `%s' for charset `%s' not sensible", $collation, $charset);
945: }
946:
947: $prefix = $this->get_prefix();
948:
949: // db name passed without prefix
950: if (0 !== strpos($db, $prefix)) {
951: $db = $prefix . $db;
952: }
953:
954: if ($this->database_exists($db)) {
955: return error("database `$db' exists");
956: }
957:
958: if (null !== ($limit = $this->getConfig('mysql', 'dbasenum', null)) && $limit >= 0) {
959: $count = \count($this->list_databases());
960: if ($count >= $limit) {
961: return error("Database limit `%d' reached - cannot create additional databases", $limit);
962: }
963: }
964:
965: $status = $this->query('mysql_create_database_backend', $db, $charset, $collation);
966: if (!$status) {
967: return $status;
968: }
969:
970: $conn = new mysqli('localhost', self::MASTER_USER, $this->_get_elevated_password());
971: $conn->select_db('mysql');
972: $conn->query('GRANT ALL ON `' . str_replace('_', '\\_', $db) . "`.* to '" . $conn->escape_string($this->username) . "'@localhost;");
973: if ($conn->error) {
974: \Opcenter\Database\MySQL::dropDatabase($db);
975:
976: return error("failed to create db `%s'. Error while applying grants: `%s' " .
977: "- is control user `%s' missing?",
978: $db,
979: $conn->error,
980: $this->username
981: );
982: }
983:
984: return info("created database `%s'", $db);
985:
986: }
987:
988: public function charset_valid($charset): bool
989: {
990: $charset = strtolower($charset);
991: $charsets = $this->get_supported_charsets();
992:
993: return array_key_exists($charset, $charsets);
994: }
995:
996: public function get_supported_charsets(): array
997: {
998: $cache = Cache_Global::spawn();
999: $key = 's:mysql.char';
1000: $charsets = $cache->get($key);
1001: if ($charsets) {
1002: return $charsets;
1003: }
1004: $db = MySQL::initialize();
1005: $rs = $db->query('SELECT CHARACTER_SET_NAME AS charset, DESCRIPTION AS `desc` FROM INFORMATION_SCHEMA.character_sets');
1006: $charsets = array();
1007: while (null !== ($row = $rs->fetch_object())) {
1008: $charsets[$row->charset] = $row->desc;
1009: }
1010: $cache->set($key, $charsets);
1011:
1012: return $charsets;
1013: }
1014:
1015: /**
1016: * Validate collation name
1017: *
1018: * @param string $collation
1019: * @return bool
1020: */
1021: public function collation_valid($collation): bool
1022: {
1023: $collations = $this->get_supported_collations();
1024: $collation = strtolower($collation);
1025: foreach ($collations as $c) {
1026: if ($c['collation'] === $collation) {
1027: return true;
1028: }
1029: }
1030:
1031: return false;
1032: }
1033:
1034: public function get_supported_collations()
1035: {
1036: $cache = Cache_Global::spawn();
1037: $key = 's:mysql.coll';
1038: $collations = $cache->get($key);
1039: if ($collations) {
1040: return $collations;
1041: }
1042: $collations = [];
1043: $db = MySQL::initialize();
1044: $q = 'SELECT collation_name, character_set_name FROM ' .
1045: "INFORMATION_SCHEMA.collations WHERE is_compiled = 'Yes'";
1046: $rs = $db->query($q);
1047: if (!$rs) {
1048: return $collations;
1049: }
1050:
1051: while (null !== ($row = $rs->fetch_object())) {
1052: $collations[] = array(
1053: 'collation' => $row->collation_name,
1054: 'charset' => $row->character_set_name
1055: );
1056: }
1057: $cache->set($key, $collations);
1058:
1059: return $collations;
1060: }
1061:
1062: /**
1063: * Verify collation + charset combination are compatible
1064: *
1065: * @param string $collation
1066: * @param string $charset
1067: *
1068: * @return bool
1069: */
1070: public function collation_compatible($collation, $charset): bool
1071: {
1072: $db = MySQL::initialize();
1073: $q = 'SELECT 1 FROM INFORMATION_SCHEMA.COLLATION_CHARACTER_SET_APPLICABILITY ' .
1074: "WHERE collation_name = '" . $db->escape($collation) . "' AND " .
1075: "character_set_name = '" . $db->escape($charset) . "'";
1076: $rs = $db->query($q);
1077: if (!$rs) {
1078: return false;
1079: }
1080:
1081: return $rs->num_rows > 0;
1082: }
1083:
1084: /**
1085: * Query information_schema for existence of MySQL database
1086: *
1087: * @param string $db database name
1088: * @return bool
1089: */
1090: public function database_exists($db): bool
1091: {
1092: if (!IS_CLI) {
1093: return $this->query('mysql_database_exists', $db);
1094: }
1095: if (!$db) {
1096: return false;
1097: }
1098: $prefix = '';
1099: if ($this->permission_level & (PRIVILEGE_SITE | PRIVILEGE_USER)) {
1100: $sqlroot = $this->domain_shadow_path() . self::MYSQL_DATADIR;
1101: $normal = \Opcenter\Database\MySQL::canonicalize($db);
1102: $prefix = $this->get_prefix();
1103: if (!file_exists($sqlroot . '/' . $normal)) {
1104: // tut-tut. Resolve db with prefix in mind
1105: $db = $prefix . $db;
1106: }
1107: }
1108: if (\Opcenter\Database\MySQL::databaseExists($db)) {
1109: return true;
1110: } else if ($this->permission_level & PRIVILEGE_ADMIN) {
1111: // used by db backup routine, in future the task should be
1112: // removed from backup, but leave this as it is for now
1113: return false;
1114: }
1115: $user = $this->getConfig('mysql', 'dbaseadmin');
1116: // double prefix, remove first prefix, then check one last time
1117: if (0 === strpos($db, $prefix . $prefix)) {
1118: $db = (string)substr($db, strlen($prefix));
1119: }
1120: return \Opcenter\Database\MySQL::databaseExists($db, $user);
1121: }
1122:
1123: /**
1124: * bool create_mysql_database_backend (string)
1125: * {@link create_mysql_database}
1126: */
1127: public function create_database_backend($db, $charset, $collation)
1128: {
1129: $dboptData = 'default-character-set=' . $charset . "\n" .
1130: 'default-collation=' . $collation;
1131: /**
1132: * use shadow/ on OverlayFS platforms too. mysqldump
1133: * communicates with mysqld to dump tables, so there's
1134: * no risk of ghosting as seen if we write directly to shadow/
1135: * and query from the composite path fst/
1136: */
1137: $path = $this->domain_shadow_path();
1138: if (!Opcenter\Database\MySQL::prepBackend($path, $db)) {
1139: return error('Failed to prepare database backend storage');
1140: }
1141: $dbRoot = $path . self::MYSQL_DATADIR . '/' . \Opcenter\Database\MySQL::canonicalize($db);
1142: $fp = fopen($dbRoot . '/db.opt', 'w');
1143: fwrite($fp, $dboptData);
1144: fclose($fp);
1145: \Opcenter\Filesystem::chogp($dbRoot . '/db.opt', 'mysql', (int)$this->group_id, 0640);
1146:
1147: return file_exists(self::MYSQL_DATADIR . '/' . Opcenter\Database\MySQL::canonicalize($db)) && file_exists($path);
1148: }
1149:
1150: /**
1151: * bool add_mysql_user_permissions (string, string, string, array)
1152: *
1153: * @deprecated
1154: * @see Mysql_Module::set_privileges()
1155: *
1156: * @param string $user
1157: * @param string $host
1158: * @param string $db
1159: * @param array $opts
1160: * @return bool
1161: */
1162: public function add_user_permissions($user, $host, $db, array $opts)
1163: {
1164: deprecated_func('use set_mysql_privileges()');
1165:
1166: return $this->set_privileges($user, $host, $db, $opts);
1167: }
1168:
1169: /**
1170: * Set grants for a MySQL user
1171: *
1172: * @param string $user
1173: * @param string $host
1174: * @param string $db name or '%' for all
1175: * @param array $privileges
1176: * @return bool
1177: */
1178: public function set_privileges(string $user, string $host, string $db, array $privileges): bool
1179: {
1180: if (!$host) {
1181: return error("invalid hostname `$host'");
1182: }
1183:
1184: $privileges = array_change_key_case($privileges);
1185: $prefix = $this->get_prefix();
1186: if ($user != $this->getServiceValue('mysql', 'dbaseadmin') &&
1187: strncmp($user, $prefix, strlen($prefix))
1188: ) {
1189: $user = $prefix . $user;
1190: }
1191: if ($user != $this->username && !preg_match('/^' . $prefix . '/', $user)) {
1192: return error("invalid user `%s'", $user);
1193: }
1194: $conn = new mysqli('localhost', self::MASTER_USER, $this->_get_elevated_password());
1195: $conn->select_db('mysql');
1196: /** ignore prefixed dbs as they should have ownership rights */
1197: if ($db === '%') {
1198: if ($user === $this->getConfig('mysql', 'dbaseadmin')) {
1199: return warn('User %s already has permission to all databases - ignoring', $this->getConfig('mysql', 'dbaseadmin'));
1200: }
1201: // _ is escaped later on
1202: $db = $prefix . '%';
1203: } else if (0 === strpos($db, $prefix)) {
1204: $rs = $conn->query("SELECT 1 FROM db WHERE user = '" . $this->username . "' AND (db = '" . $db . "' OR db = '" . str_replace('_', '\\_', $db) . "')");
1205: if ($rs->num_rows < 1) {
1206: return error("No grants found in database on `%s' for user `%s'", $db, $this->username);
1207: }
1208: }
1209:
1210: $valid_opts = array(
1211: 'select' => false,
1212: 'insert' => false,
1213: 'update' => false,
1214: 'delete' => false,
1215: 'create' => false,
1216: 'drop' => false,
1217: 'grant' => false,
1218: 'references' => false,
1219: 'index' => false,
1220: 'alter' => false,
1221: 'create_tmp_table' => false,
1222: 'show_view' => false,
1223: 'create_view' => false,
1224: 'create_routine' => false,
1225: 'alter_routine' => false,
1226: 'lock_tables' => false,
1227: 'execute' => false,
1228: 'event' => false,
1229: 'trigger' => false
1230: );
1231:
1232: if (count($privileges) <= 2 && (isset($privileges['read']) || isset($privileges['write']))) {
1233: // simplified mode
1234: $tmp = array();
1235: if (!empty($privileges['read'])) {
1236: $tmp['select'] = $tmp['show_view'] = $tmp['execute'] = true;
1237: }
1238:
1239: if (!empty($privileges['write'])) {
1240: $write = array_diff(array_keys($valid_opts), array('select', 'show_view', 'execute'));
1241: $tmp2 = array_fill_keys($write, true);
1242: $tmp = array_merge($tmp, $tmp2);
1243: }
1244: $privileges = $tmp;
1245: }
1246:
1247: $opts_copy = $valid_opts;
1248: foreach ($valid_opts as $opt => $enabled) {
1249: if (isset($privileges[($opt)]) && $privileges[$opt]) {
1250: $valid_opts[$opt] = $opt . '_priv';
1251: } else {
1252: unset($valid_opts[$opt]);
1253: }
1254: }
1255:
1256: $revoke_opts = array_diff_key($opts_copy, $valid_opts);
1257: foreach (array_keys($opts_copy) as $name) {
1258: $opts[] = (isset($valid_opts[$name])) ? 'Y' : 'N';
1259: }
1260:
1261: array_walk($opts_copy, static function (&$key, $val) {
1262: $key = $val . '_priv';
1263: });
1264: if ($db !== $prefix . '%') {
1265: $rs = $conn->query("SELECT db FROM db WHERE user = '" . $user . "' AND host = '" . $host . "' AND db = '" . str_replace('_', '\\_', $prefix) . '%' . "'");
1266: if ($rs->num_rows > 0) {
1267: return error('User %s@%s has wildcard grants applied. Per-database grants cannot be applied over wildcard grant. User may only be deleted.', $user, $host);
1268: }
1269: }
1270: // check if old format
1271: $rs = $conn->query("SELECT 1 FROM db where user = '" . $user . "' AND host = '" . $host . "' AND db = '" . str_replace('_',
1272: '\\_', $db) . "'");
1273: if ($db === $prefix . '%' || $rs->num_rows > 0) {
1274: $db = str_replace('_', '\\_', $db);
1275: }
1276:
1277: $conn->query('REPLACE INTO db (' . implode(', ',
1278: $opts_copy) . ", `host`, `db`, `user`) VALUES ('" . implode("', '", $opts) .
1279: "', '" . $host . "', '" . $db . "', '" . $user . "');");
1280: $ar = $conn->affected_rows;
1281:
1282: if ($conn->error) {
1283: return error('Error when applying grants, ' . $conn->error);
1284: }
1285:
1286: $conn->query('FLUSH PRIVILEGES;');
1287:
1288: return $ar > 0;
1289: }
1290:
1291: /**
1292: *
1293: * @deprecated
1294: * @see Mysql_Module::revoke_privileges()
1295: */
1296: public function delete_user_permissions($user, $host, $db)
1297: {
1298: deprecated_func('use revoke_from_mysql_db()');
1299:
1300: return $this->revoke_privileges($user, $host, $db);
1301: }
1302:
1303: /**
1304: * Revoke all privileges on a database from a MySQL user
1305: *
1306: * @param string $user
1307: * @param string $host
1308: * @param string $db
1309: * @return bool
1310: */
1311: public function revoke_privileges($user, $host, $db)
1312: {
1313: $prefix = $this->get_prefix();
1314: if ($user != $this->getServiceValue('mysql', 'dbaseadmin') &&
1315: strncmp($user, $prefix, strlen($prefix))
1316: ) {
1317: $user = $prefix . $user;
1318: }
1319: if ($user != $this->username && !preg_match('/^' . $prefix . '/', $user)) {
1320: return error("invalid user `$user'");
1321: }
1322: $conn = new mysqli('localhost', self::MASTER_USER, $this->_get_elevated_password());
1323: $conn->select_db('mysql');
1324: $stmt = $conn->prepare('DELETE FROM db WHERE user = ? AND host = ? AND (db = ? OR db = ?)');
1325: $canonical = str_replace('_', '\_', $db);
1326: $stmt->bind_param('ssss', $user, $host, $db, $canonical);
1327: $res = $stmt->execute();
1328: if (!$res) {
1329: return error('Query error while dropping permissions, ' . $stmt->error);
1330: }
1331:
1332: $conn->query('FLUSH PRIVILEGES;');
1333:
1334: return $stmt->affected_rows > 0;
1335: }
1336:
1337: // {{{ enabled()
1338:
1339: public function get_user_permissions($user, $host, $db)
1340: {
1341: deprecated_func('use get_privileges()');
1342:
1343: return $this->get_privileges($user, $host, $db);
1344: }
1345:
1346: // }}}
1347:
1348: /**
1349: * Get MySQL grants for a user on a database
1350: *
1351: * @param string $user
1352: * @param string $host
1353: * @param string $db
1354: * @return array
1355: */
1356: public function get_privileges($user, $host, $db)
1357: {
1358: $prefix = $this->get_prefix();
1359: if ($user != $this->getServiceValue('mysql', 'dbaseadmin') &&
1360: strncmp($user, $prefix, strlen($prefix))
1361: ) {
1362: $user = $prefix . $user;
1363: }
1364: $conn = new mysqli('localhost', self::MASTER_USER, $this->_get_elevated_password());
1365: $conn->select_db('mysql');
1366:
1367: $stmt = $conn->prepare('SELECT Select_priv, Insert_priv, Update_priv, DELETE_PRIV,
1368: CREATE_PRIV, DROP_PRIV, GRANT_PRIV, REFERENCES_PRIV,
1369: INDEX_PRIV, ALTER_PRIV, CREATE_TMP_TABLE_PRIV,
1370: LOCK_TABLES_PRIV, CREATE_VIEW_PRIV, SHOW_VIEW_PRIV,
1371: CREATE_ROUTINE_PRIV, ALTER_ROUTINE_PRIV, EXECUTE_PRIV,
1372: EVENT_PRIV, TRIGGER_PRIV FROM db WHERE user = ? AND db IN (?,?,?) AND host = ?');
1373: $dbSafe = str_replace('_', '\\_', $db);
1374: $wcSafe = str_replace('_', '\\_', $prefix . '%');
1375: $stmt->bind_param('sssss', $user, $db, $dbSafe, $wcSafe, $host);
1376: $stmt->execute();
1377: $select = $insert = $update = $delete = $create = $drop = $domain =
1378: $grant = $references = $index = $alter = $create_tmp_table =
1379: $lock_tables = $create_view = $show_view = $create_routine =
1380: $alter_routine = $execute = $event = $trigger = null;
1381: $stmt->bind_result($select, $insert, $update, $delete, $create, $drop, $grant,
1382: $references, $index, $alter, $create_tmp_table, $lock_tables,
1383: $create_view, $show_view, $create_routine, $alter_routine,
1384: $execute, $event, $trigger);
1385:
1386: if ($stmt->fetch()) {
1387: $priv = array(
1388: 'select' => $select,
1389: 'insert' => $insert,
1390: 'update' => $update,
1391: 'delete' => $delete,
1392: 'create' => $create,
1393: 'drop' => $drop,
1394: 'grant' => $grant,
1395: 'references' => $references,
1396: 'index' => $index,
1397: 'alter' => $alter,
1398: 'create_tmp_table' => $create_tmp_table,
1399: 'lock_tables' => $lock_tables,
1400: 'create_view' => $create_view,
1401: 'show_view' => $show_view,
1402: 'create_routine' => $create_routine,
1403: 'alter_routine' => $alter_routine,
1404: 'execute' => $execute,
1405: 'trigger' => $trigger,
1406: 'event' => $event
1407: );
1408: array_walk($priv, static function (&$key, $val) {
1409: $key = $key == 'Y';
1410: });
1411: $stmt->close();
1412: } else {
1413: $priv = array(
1414: 'select' => false,
1415: 'insert' => false,
1416: 'update' => false,
1417: 'delete' => false,
1418: 'create' => false,
1419: 'drop' => false,
1420: 'grant' => false,
1421: 'references' => false,
1422: 'index' => false,
1423: 'alter' => false,
1424: 'create_tmp_table' => false,
1425: 'lock_tables' => false,
1426: 'create_view' => false,
1427: 'show_view' => false,
1428: 'create_routine' => false,
1429: 'alter_routine' => false,
1430: 'execute' => false,
1431: );
1432: }
1433: return $priv;
1434: }
1435:
1436: /**
1437: * Returns the version of the MySQL server as an integer
1438: *
1439: * The form of this version number is
1440: * main_version * 10000 + minor_version * 100 + sub_version
1441: * (i.e. version 4.1.0 is 40100)
1442: *
1443: * @param $pretty bool pretty-print version
1444: *
1445: * @return int|string integer representing the server version or string
1446: */
1447: public function version($pretty = false)
1448: {
1449: $version = \Opcenter\Database\MySQL::version();
1450: if (!$pretty) {
1451: return $version;
1452: }
1453:
1454: $mysqlver = array();
1455: foreach (array('patch', 'minor', 'major') as $v) {
1456: $mysqlver[$v] = (int)$version % 100;
1457: $version /= 100;
1458: }
1459:
1460: return $mysqlver['major'] . '.' . $mysqlver['minor'] . '.' .
1461: $mysqlver['patch'];
1462:
1463: }
1464:
1465: /**
1466: * Delete MySQL database from system
1467: *
1468: * @param string $db database
1469: * @return bool
1470: */
1471: public function delete_database($db)
1472: {
1473: $db = str_replace('\\\\', '\\', $db);
1474: $prefix = $this->get_prefix();
1475: if (!$prefix) {
1476: report('Prefixless site - bug');
1477:
1478: return false;
1479: }
1480: $prefixwc = str_replace('_', '\_', $prefix) . '%';
1481: $conn = new mysqli('localhost', self::MASTER_USER, $this->_get_elevated_password());
1482: $conn->select_db('mysql');
1483: $stmt = $conn->prepare('SELECT user FROM db WHERE (user = ? OR db LIKE ?) AND (db = ? OR db = ?)');
1484: $canonical = str_replace('_', '\_', $db);
1485: $stmt->bind_param('ssss', $this->username, $prefixwc, $db, $canonical);
1486: $stmt->execute();
1487: $stmt->store_result();
1488: if ($stmt->num_rows < 1) {
1489: $stmt->free_result();
1490: // db name passed without prefix, db not found,
1491: // don't coerce db to prefix + db unless db deletion failed
1492: if (strncmp($db, $prefix, strlen($prefix))) {
1493: $db = $prefix . $db;
1494:
1495: return $this->delete_database($db);
1496: }
1497: if (!in_array($db, $this->list_databases(), true)) {
1498: // extra lookup for orphaned databases
1499: return error("Unknown database `%s'", $db);
1500: }
1501: }
1502: $stmt->free_result();
1503: $this->query('mysql_assert_permissions');
1504: \Opcenter\Database\MySQL::dropDatabaseGrants($db);
1505: // FLUSH is necessary, because MySQL will permit a separate CREATE DATABASE
1506: // query by the former owner bypassing this requirement
1507: // (it also bypasses filesystem namespacing + quota attribution)
1508: \Opcenter\Database\MySQL::flush();
1509: \Opcenter\Database\MySQL::dropDatabase($db);
1510:
1511: $this->query('mysql_delete_database_backend', $db);
1512:
1513: if (in_array($db, $this->list_backups(), true)) {
1514: $this->delete_backup($db);
1515: }
1516:
1517:
1518: if ($conn->error) {
1519: return error("error while removing database `$db' - " . $conn->error);
1520: }
1521:
1522: return true;
1523: }
1524:
1525: /**
1526: * Remove MySQL Backup
1527: *
1528: * @param string $db
1529: * @return bool
1530: */
1531: public function delete_backup($db)
1532: {
1533: return parent::delete_backup_real('mysql', $db);
1534: }
1535:
1536: /**
1537: * Ensure that /var/lib/mysql/ has mysql:<group id> ownership
1538: */
1539: public function assert_permissions()
1540: {
1541: if (!file_exists($this->domain_fs_path() . self::MYSQL_DATADIR)) {
1542: return false;
1543: }
1544:
1545: chown($this->domain_fs_path() . self::MYSQL_DATADIR, 'mysql');
1546: chgrp($this->domain_fs_path() . self::MYSQL_DATADIR, $this->group_id);
1547:
1548: return true;
1549: }
1550:
1551: public function delete_database_backend($db)
1552: {
1553: $db = \Opcenter\Database\MySQL::canonicalize($db);
1554: if (is_link(self::MYSQL_DATADIR . '/' . $db)) {
1555: @unlink(self::MYSQL_DATADIR . '/' . $db);
1556: }
1557:
1558: return true;
1559:
1560: }
1561:
1562: /**
1563: * bool edit_mysql_user(string, string, array)
1564: * Note when calling through SOAP, all options must be given, otherwise
1565: * the will default to server preferences.
1566: *
1567: * @param string $user user
1568: * @param string $host hostname
1569: * @param array $opts array of options, valid indexes:
1570: * host
1571: * password
1572: * max_user_connections
1573: * max_updates
1574: * max_questions
1575: * use_ssl
1576: * ssl_type
1577: * ssl_cipher
1578: * x509_subject
1579: * x509_issuer
1580: * @return bool query succeeded
1581: */
1582: public function edit_user(string $user, string $host, array $opts): bool
1583: {
1584: $prefix = $this->get_prefix();
1585: if ($user !== $this->getServiceValue('mysql', 'dbaseadmin') && 0 !== strpos($user, $prefix)) {
1586: $user = $prefix . $user;
1587: }
1588: if (!is_array($opts)) {
1589: return error('Options must be an array');
1590: }
1591: if (isset($opts['ssl_type'])) {
1592: $opts['ssl_type'] = strtoupper($opts['ssl_type']);
1593: if ($opts['ssl_type'] != '' && $opts['ssl_type'] != 'ANY' &&
1594: $opts['ssl_type'] != 'SPECIFIED' && $opts['ssl_type'] != 'X509'
1595: ) {
1596: return error('Invalid cipher type');
1597: }
1598: }
1599:
1600: if (isset($opts['host']) && $opts['host'] != 'localhost') {
1601:
1602: if (!IpCommon::valid($opts['host']) && !preg_match(Regex::SQL_MYSQL_IP_WILDCARD, $opts['host'])) {
1603: return error("rejected host `%s': only numeric IP addresses are permitted, not hostnames",
1604: $opts['host']);
1605: }
1606: }
1607:
1608: $defaults = array(
1609: 'host' => $host,
1610: 'password' => null,
1611: 'max_user_connections' => self::DEFAULT_CONCURRENCY_LIMIT,
1612: 'max_updates' => 0,
1613: 'max_questions' => 0,
1614: 'use_ssl' => false,
1615: 'ssl_type' => '',
1616: 'ssl_cipher' => '',
1617: 'x509_subject' => '',
1618: 'x509_issuer' => ''
1619: );
1620:
1621: // make copy to check for max_user_connections change
1622: // later in event of password update
1623: $mergeopts = $opts;
1624: foreach ($defaults as $def_nam => $def_val) {
1625: if (!isset($mergeopts[$def_nam])) {
1626: $mergeopts[$def_nam] = $def_val;
1627: }
1628: }
1629: /** if we're not using SSL, blank it out */
1630: if (!$mergeopts['use_ssl']) {
1631: $mergeopts['x509_subject'] = $mergeopts['x509_issuer'] = $mergeopts['ssl_cipher'] = $mergeopts['ssl_type'] = '';
1632: } else if (!isset($mergeopts['ssl_type'])) {
1633: $mergeopts['ssl_type'] = 'ANY';
1634: }
1635:
1636: if ($mergeopts['max_user_connections'] < 1) {
1637: $mergeopts['max_user_connections'] = self::DEFAULT_CONCURRENCY_LIMIT;
1638: }
1639:
1640: if ($mergeopts['max_questions'] < 0 || $mergeopts['max_updates'] < 0) {
1641: return error('Max queries and updates must be greater than 0');
1642: }
1643: if (isset($opts['max_user_connections']) && $opts['max_user_connections'] > static::MAX_CONCURRENCY_LIMIT) {
1644: return error('Max connection limit %d. Must file a ticket justifying need. ' .
1645: 'Check index placements first.', static::MAX_CONCURRENCY_LIMIT);
1646: }
1647: if (!is_null($mergeopts['password']) && strlen($mergeopts['password']) < self::MIN_PASSWORD_LENGTH) {
1648: return error('password must be at least %d characters long', self::MIN_PASSWORD_LENGTH);
1649: }
1650:
1651: if (!empty($mergeopts['use_ssl']) && $user === $this->getServiceValue('mysql',
1652: 'dbaseadmin') && $host === 'localhost') {
1653: warn("Enabling SSL for localhost will break phpMyAdmin access on this account");
1654: }
1655:
1656: $conn = $this->_connect_root();
1657:
1658: $stmt = $conn->prepare('SELECT user FROM user WHERE user = ? AND host = ?');
1659: $stmt->bind_param('ss', $user, $host);
1660: $stmt->execute();
1661: $stmt->store_result();
1662: if ($stmt->num_rows < 1) {
1663: $stmt->free_result();
1664: return error('invalid user@host specified: %s@%s', $user, $host);
1665: }
1666: $stmt->free_result();
1667:
1668: if ($host === 'localhost' &&
1669: $user === $this->getServiceValue('mysql','dbaseadmin') &&
1670: ($mergeopts['host'] ?: $host) !== $host)
1671: {
1672: return error("Cannot modify the hostname attribute for %(user)s on %(host)s", ['user' => $user, 'host' => $host]);
1673: }
1674:
1675: if (Opcenter\Database\MySQL::version() >= self::NEW_API_VERSION) {
1676: $params = [
1677: $user,
1678: $host,
1679: [
1680: 'ssl' => $mergeopts['use_ssl'] ?: false,
1681: 'subject' => $mergeopts['x509_subject'] ?: null,
1682: 'issuer' => $mergeopts['x509_issuer'] ?: null,
1683: 'ssl_type' => $mergeopts['ssl_type'] ?: null,
1684: 'password' => $mergeopts['password'] ?: null,
1685: 'connections' => (int)($mergeopts['max_user_connections'] ?: 0),
1686: 'updates' => (int)($mergeopts['max_updates'] ?: 0),
1687: 'query' => (int)($mergeopts['max_questions'] ?: 0),
1688: 'host' => $mergeopts['host'] ?: null
1689: ]
1690: ];
1691: if (!Opcenter\Database\MySQL::alterUser(...$params)) {
1692: return false;
1693: }
1694: } else {
1695: $stmt = $conn->prepare('UPDATE user
1696: SET
1697: host = ?,
1698: ssl_type = ?,
1699: ssl_cipher = ?,
1700: x509_issuer = ? ,
1701: x509_subject = ?,
1702: max_questions = ?,
1703: max_updates = ?,
1704: max_user_connections = ?
1705: WHERE
1706: user = ?
1707: AND
1708: host = ?');
1709:
1710: $stmt->bind_param('sssssiiiss',
1711: $mergeopts['host'],
1712: $mergeopts['ssl_type'],
1713: $mergeopts['ssl_cipher'],
1714: $mergeopts['x509_issuer'],
1715: $mergeopts['x509_subject'],
1716: $mergeopts['max_questions'],
1717: $mergeopts['max_updates'],
1718: $mergeopts['max_user_connections'],
1719: $user,
1720: $host
1721: );
1722: $stmt->execute();
1723: if ($stmt->error) {
1724: return new MySQLError('Invalid query, ' . $stmt->error);
1725: }
1726:
1727: if ($mergeopts['host'] !== $defaults['host']) {
1728: $stmt = $conn->prepare('UPDATE db SET host = ? WHERE user = ? AND host = ?');
1729: $stmt->bind_param('sss', $mergeopts['host'], $user, $defaults['host']);
1730: $stmt->execute();
1731: if ($stmt->error) {
1732: return error('error while updating DB grants, %s', $stmt->error);
1733: }
1734: }
1735: /** finally update the password if changed */
1736: if ($mergeopts['password']) {
1737: $pwclause = 'password(?)';
1738: $password = $mergeopts['password'];
1739: // password is encrypted in new pw form or old
1740: if ($password[0] == '*' && strlen($password) == 41
1741: && ctype_xdigit(substr($password, 1)) ||
1742: /** only accept old-style passwords on platforms <v6 */
1743: strlen($password) == 16 && ctype_xdigit($password) && version_compare(platform_version(), '6',
1744: '<')
1745: ) {
1746: $pwclause = '?';
1747: }
1748: $stmt2 = $conn->prepare('UPDATE user SET password = ' . $pwclause . ' WHERE user = ? AND host = ?;');
1749:
1750: $stmt2->bind_param('sss', $password, $user, $mergeopts['host']);
1751: $stmt2->execute();
1752: if ($stmt2->error) {
1753: return new MySQLError('Query error while updating password, ' . $stmt2->error);
1754: }
1755: }
1756: }
1757:
1758: $conn->query('FLUSH PRIVILEGES');
1759:
1760: if ($user === $this->username && $mergeopts['password']) {
1761: $this->set_option('user', $this->username, 'client');
1762: $this->set_option('password',
1763: str_replace(array('"'), array('\"'), $mergeopts['password']),
1764: 'client'
1765: );
1766: }
1767:
1768: return true;
1769: }
1770:
1771: /**
1772: * bool service_enabled (string)
1773: *
1774: * Checks to see if a service is enabled
1775: *
1776: * @deprecated
1777: * @see Mysql_Module::enabled()
1778: * @return bool
1779: */
1780: public function service_enabled()
1781: {
1782: deprecated('use enabled()');
1783:
1784: return $this->enabled();
1785: }
1786:
1787: /**
1788: * MySQL/PostgreSQL service enabled on account
1789: *
1790: * Checks to see if either MySQL or PostgreSQL is enabled on an account
1791: *
1792: * @return bool
1793: */
1794: public function enabled()
1795: {
1796: return parent::svc_enabled('mysql');
1797: }
1798:
1799: public function truncate_database($db)
1800: {
1801:
1802: return $this->_mysql_empty_truncate_wrapper($db, 'truncate');
1803: }
1804:
1805: private function _mysql_empty_truncate_wrapper($db, $mode)
1806: {
1807: if ($mode != 'truncate' && $mode != 'empty') {
1808: return error("unknown mode `%s'", $mode);
1809: }
1810: if ($mode == 'empty') {
1811: // semantically more correct
1812: $mode = 'drop';
1813: }
1814:
1815: $prefix = $this->getServiceValue('mysql', 'dbaseprefix');
1816: if (0 !== strpos($db, $prefix)) {
1817: $db = $prefix . $db;
1818: }
1819:
1820: if (!$this->database_exists($db)) {
1821: return error("unknown database, `%s'", $db);
1822: }
1823:
1824: $user = $this->_create_temp_user($db);
1825: if (!$user) {
1826: return error("failed to %s db `%s'", $mode, $db);
1827: }
1828: $conn = new mysqli('localhost', $user);
1829: $conn->set_charset('utf8mb4');
1830: if (!$conn->select_db($db)) {
1831: return error("unable to establish db connection for user `%s' on db `%s'", $user, $db);
1832: }
1833:
1834: $conn->query('SET FOREIGN_KEY_CHECKS=0');
1835:
1836: $q = "SELECT CONCAT('" . strtoupper($mode) . " TABLE ','`', table_schema,'`','.','`',TABLE_NAME,'`', ';')
1837: FROM INFORMATION_SCHEMA.TABLES where table_schema in ('" . $conn->escape_string($db) . "');";
1838: $res = $conn->query($q);
1839: while (null !== ($rs = $res->fetch_row())) {
1840: if (!$conn->query($rs[0])) {
1841: warn("failed to %s table `%s'", $mode, $rs[0]);
1842: }
1843: }
1844:
1845: $conn->query('SET @@FOREIGN_KEY_CHECKS=1;');
1846: if (!$res) {
1847: return error("%s failed on database `%s': `%s'", $mode, $db, $conn->error);
1848: }
1849: $this->_delete_temp_user($user);
1850:
1851: return true;
1852: }
1853:
1854: /**
1855: * Database is empty
1856: *
1857: * @param $db
1858: * @return bool
1859: */
1860: public function empty($db) {
1861: if (!$this->database_exists($db)) {
1862: return true;
1863: }
1864:
1865: return \count(\Opcenter\Database\MySQL::getTablesFromDatabase($db)) === 0;
1866: }
1867:
1868: public function empty_database($db)
1869: {
1870: return $this->_mysql_empty_truncate_wrapper($db, 'empty');
1871: }
1872:
1873: /**
1874: * Export a MySQL database
1875: *
1876: * @param string $db
1877: * @param string|null $file optional filename
1878: * @return mixed path of export or false on failure
1879: */
1880: public function export($db, $file = null)
1881: {
1882: if (!IS_CLI) {
1883: return $this->query('mysql_export', $db, $file);
1884: }
1885:
1886: if (is_null($file)) {
1887: $file = $db . '.sql';
1888: }
1889: if (!in_array($db, $this->list_databases(), true)) {
1890: return error('Invalid database ' . $db);
1891: }
1892: if ($file[0] !== '/' && $file[0] !== '.' && $file[0] !== '~') {
1893: $file = '/tmp/' . $file;
1894: }
1895: $pdir = dirname($file);
1896: if (!$this->file_exists($pdir) && !$this->file_create_directory($pdir, 0755, true)) {
1897: return error("failed to create parent directory, `%s'", $pdir);
1898: }
1899: $path = $this->file_make_path($file);
1900: if (!$path) {
1901: return error("invalid file `%s'", $file);
1902: }
1903:
1904: if (file_exists($path) &&
1905: (filesize($path) > 0 || realpath($path) !== $path || fileowner($path) < USER_MIN_UID))
1906: {
1907: return error('%s: file exists, cannot overwrite', $file);
1908: }
1909: $user = $this->_create_temp_user($db);
1910: $cmd = 'umask 077 && ' . self::EXPORT_CMD . ' -u %s %s > %s';
1911: if (!$user) {
1912: $user = self::MASTER_USER;
1913: $rootpw = escapeshellarg($this->_get_elevated_password());
1914: $cmd = str_replace('-u %s', '-u %s -p' . $rootpw, $cmd);
1915: }
1916:
1917: $fsizelimit = Util_Ulimit::get('fsize');
1918: if ($this->get_database_size($db) > $fsizelimit / self::DB_BIN2TXT_MULT) {
1919: // make sure ulimit accommodates the db dump
1920: Util_Ulimit::set('fsize', 'unlimited');
1921: } else {
1922: // no need to change this then
1923: $fsizelimit = null;
1924: }
1925: $proc = new Util_Process_Safe();
1926: // gid must come first
1927: $proc->setSgid($this->group_id);
1928: $proc->setSuid($this->user_id);
1929: $proc->setPriority(19);
1930:
1931: $status = $proc->run($cmd,
1932: $user,
1933: $db,
1934: $path
1935: );
1936:
1937: if ($user != self::MASTER_USER) {
1938: $this->_delete_temp_user($user);
1939: }
1940:
1941: if (!is_null($fsizelimit)) {
1942: Util_Ulimit::set('fsize', $fsizelimit);
1943: }
1944:
1945: if (!$status['success'] || !file_exists($path)) {
1946: return error('export failed: %s', $status['stderr']);
1947: }
1948:
1949: if (!$status['success']) {
1950: return error('export failed: %s', $status['stderr']);
1951: }
1952:
1953: return $this->file_unmake_path($path);
1954: }
1955:
1956: /**
1957: * Clone a database
1958: *
1959: * @param string $from source database
1960: * @param string $to target database
1961: * @return bool
1962: */
1963: public function clone(string $from, string $to): bool
1964: {
1965: if ($this->database_exists($to) && !$this->empty($to)) {
1966: return error("Database `%s' already exists", $to);
1967: }
1968: if (!$this->database_exists($from)) {
1969: return error("Database `%s' does not exist", $from);
1970: }
1971: if (!$this->database_exists($to) && !$this->create_database($to)) {
1972: return false;
1973: }
1974: $pipe = $this->export_pipe($from);
1975: defer($_, static function () use ($pipe) {
1976: if (file_exists($pipe)) {
1977: unlink($pipe);
1978: }
1979: });
1980: return $this->import($to, $this->file_unmake_path($pipe));
1981: }
1982:
1983: // {{{ delete_mysql_backup()
1984:
1985: /**
1986: * Get disk space occupied by database
1987: *
1988: * @param string $db database name
1989: * @return int storage in bytes
1990: */
1991: public function get_database_size($db)
1992: {
1993: if (!IS_CLI) {
1994: $resp = $this->query('mysql_get_database_size', $db);
1995:
1996: return (int)$resp;
1997: }
1998:
1999: // mysql type
2000: $dir = self::MYSQL_DATADIR . '/' . \Opcenter\Database\MySQL::canonicalize($db);
2001: // database created as directory in /var/lib/mysql
2002: // instead of under fst
2003: if (($this->permission_level & (PRIVILEGE_SITE | PRIVILEGE_USER)) && !is_link($dir)) {
2004: $this->_move_db($db);
2005: }
2006: if (!file_exists($dir) || (is_link($dir) && !readlink($dir))) {
2007: warn($db . ': database does not exist');
2008:
2009: return 0;
2010: }
2011:
2012: $space = 0;
2013: $dh = opendir($dir);
2014: if (!$dh) {
2015: error("failed to open database directory `%s'", $dir);
2016:
2017: return 0;
2018: }
2019: while (($file = readdir($dh)) !== false) {
2020: if ($file == '.' || $file == '..') {
2021: continue;
2022: }
2023: $space += filesize($dir . '/' . $file);
2024: }
2025: closedir($dh);
2026:
2027: return $space;
2028: }
2029:
2030: // }}}
2031:
2032: private function _move_db($db)
2033: {
2034:
2035: /**
2036: * make sure the DB is accessed by the correct user
2037: * otherwise the DB will be relocated under the caller's fs
2038: */
2039: $prefix = $this->getServiceValue('mysql', 'dbaseadmin');
2040: if (strncmp($db, $prefix, strlen($prefix))) {
2041: return true;
2042: }
2043: $dbfs = \Opcenter\Database\MySQL::canonicalize($db);
2044: $src = self::MYSQL_DATADIR . '/' . $dbfs;
2045: $dest = $this->domain_fs_path() . self::MYSQL_DATADIR . '/' . $dbfs;
2046: if (is_dir($dest)) {
2047: report('dest db exists - %s', $dest);
2048:
2049: return false;
2050: }
2051: \Opcenter\Filesystem::mkdir($dest, 'mysql', $this->group_id, 02750);
2052: /* a read lock should be used in this situation to ensure
2053: * absolutely no data is lost in the process
2054: * only a global flush tables would work, but this can
2055: * cause a 15+ second hang while tables are flushed
2056: * as a consequence, forgo the read lock and just move the files
2057: *
2058: * this process is also quicker than an export/import routine
2059: */
2060:
2061: $dh = opendir($src);
2062: if (!$dh) {
2063: return error("could not relocate database `%s'", $db);
2064: }
2065:
2066: while (false !== ($file = readdir($dh))) {
2067: if ($file == '.' || $file == '..') {
2068: continue;
2069: }
2070: rename($src . '/' . $file, $dest . '/' . $file);
2071: chown($dest . '/' . $file, 'mysql');
2072: chgrp($dest . '/' . $file, $this->group_id);
2073: }
2074:
2075: rmdir($src);
2076: symlink($dest, $src);
2077: warn("database `%s' relocated under account filesystem root", $db);
2078: $db = $this->_connect_root();
2079: $db->query('FLUSH TABLES');
2080:
2081: return true;
2082: }
2083: // }}}
2084:
2085: // {{{ get_mysql_backup_config()
2086:
2087: /**
2088: * Export a db to a named pipe for immediate download
2089: *
2090: * @param $db
2091: * @return bool|void
2092: */
2093: public function export_pipe($db)
2094: {
2095: if (version_compare(platform_version(), '4.5', '<=')) {
2096: return error('platform version too old to support download feature');
2097: }
2098:
2099: $dbs = $this->list_databases();
2100: if (!\in_array($db, $dbs, true)) {
2101: $originalDb = $db;
2102: $db = $this->get_prefix() . $db;
2103: if (!\in_array($db, $dbs, true)) {
2104: return error('Invalid database %s', $originalDb);
2105: }
2106: }
2107:
2108: $user = $this->_create_temp_user($db);
2109:
2110: return $this->query('mysql_export_pipe_real', $db, $user);
2111: }
2112:
2113: // }}}
2114:
2115: // {{{ get_mysql_backup_config()
2116:
2117: /**
2118: * Export a database to a named pipe
2119: *
2120: * Differs from export_pipe in that it may only be called internally
2121: * or from backend, no API access
2122: *
2123: * @param $db
2124: * @param $user if empty use superuser
2125: * @return bool|string|void
2126: */
2127: public function export_pipe_real($db, $user)
2128: {
2129: if (!IS_CLI) {
2130: return $this->query('mysql_export_pipe_real', $db, $user);
2131: }
2132:
2133:
2134: // automatically cleaned up on exit()/destruct
2135:
2136: $cmd = self::EXPORT_CMD . ' -u %s %s -r%s';
2137: if (!$user) {
2138: $user = self::MASTER_USER;
2139: $rootpw = escapeshellarg($this->_get_elevated_password());
2140: $cmd = str_replace('-u %s', '-u %s --password=' . $rootpw, $cmd);
2141: }
2142:
2143: // @XXX potential race condition
2144: $fifo = tempnam($this->domain_fs_path('/tmp'), 'id-' . $this->site);
2145: unlink($fifo);
2146: if (!posix_mkfifo($fifo, 0600)) {
2147: return error('failed to ready pipe for export');
2148: }
2149: chown($fifo, File_Module::UPLOAD_UID);
2150: $proc = new Util_Process_Fork();
2151:
2152: // lowest priority
2153: $proc->setPriority(19);
2154: $proc->setUser(APNSCP_SYSTEM_USER);
2155:
2156: $status = $proc->run($cmd,
2157: $user,
2158: $db,
2159: $fifo
2160: );
2161:
2162: if (!$status['success'] || !file_exists($fifo)) {
2163: return error('export failed: %s', $status['stderr']);
2164: }
2165: register_shutdown_function(static function () use ($fifo) {
2166: if (file_exists($fifo)) {
2167: unlink($fifo);
2168: }
2169:
2170: });
2171:
2172: return $fifo;
2173: }
2174:
2175: // }}}
2176:
2177: /**
2178: * int get_mysql_uptime
2179: *
2180: * @return int time in seconds
2181: */
2182: public function get_uptime(): int
2183: {
2184: $db = MySQL::initialize();
2185:
2186: return $db->query("SHOW status LIKE 'uptime'")->fetch_object()->value;
2187:
2188: }
2189:
2190: // {{{ mysql_database_exists()
2191:
2192: public function add_backup(
2193: string $db,
2194: string $extension = DATABASE_BACKUP_EXTENSION,
2195: int $span = DATABASE_BACKUP_SPAN,
2196: int $preserve = DATABASE_BACKUP_PRESERVE,
2197: $email = ''
2198: )
2199: {
2200: return parent::add_backup_real('mysql', $db, $extension, $span, $preserve, $email);
2201: }
2202:
2203: public function edit_backup(
2204: string $db,
2205: string $extension = DATABASE_BACKUP_EXTENSION,
2206: int $span = DATABASE_BACKUP_SPAN,
2207: int $preserve = DATABASE_BACKUP_PRESERVE,
2208: $email = ''
2209: )
2210: {
2211: return $this->edit_backup_real('mysql', $db, $extension, $span, $preserve, $email);
2212: }
2213:
2214: public function list_backups()
2215: {
2216: return parent::list_backups_real('mysql');
2217: }
2218:
2219: /**
2220: * Fetch MySQL backup task information
2221: *
2222: * span => (integer) days between backups
2223: * hold => (integer) number of backups to preserve
2224: * next => (integer) unix timestamp of next backup
2225: * ext => (string) extension of backup
2226: * email => (string) notify address after backup
2227: *
2228: * @param string $db database name
2229: * @return array
2230: */
2231: public function get_backup_config($db)
2232: {
2233: return parent::get_backup_config_real('mysql', $db);
2234: }
2235:
2236: public function repair_database($db)
2237: {
2238: if (!IS_CLI) {
2239: return $this->query('mysql_repair_database', $db);
2240: }
2241:
2242: if (!$this->database_exists($db)) {
2243: return error("unknown database `%s'", $db);
2244: }
2245:
2246: $sqlroot = $this->domain_fs_path() . self::MYSQL_DATADIR;
2247: if (!file_exists($sqlroot . '/' . $db)) {
2248: // tut-tut. Resolve db with prefix in mind
2249: $prefix = $this->get_prefix();
2250: $db = $prefix . $db;
2251: }
2252: // make sure there are tables in this database to actually check...
2253: $files = glob($sqlroot . '/' . $db . '/*');
2254: if (count($files) < 2) {
2255: return true;
2256: }
2257:
2258: // negotiate to use mysqlcheck or myisamchk
2259: $quota = $this->site_get_account_quota();
2260: $conn = $this->_connect_root();
2261: $q = 'SELECT MAX(Data_length) AS max FROM ' .
2262: "information_schema.tables WHERE table_schema = '" .
2263: $conn->real_escape_string($db) . "'";
2264: $rs = $conn->query($q);
2265: $row = $rs->fetch_object();
2266: $tblsz = $row->max / 1024 * 1.25; //working room
2267:
2268: $qfree = $quota['qhard'] - $quota['qused'];
2269: $cmd = 'env HOME=/root mysqlcheck --auto-repair %s';
2270: if ($tblsz > $qfree) {
2271: warn('not enough storage to safely use mysqlcheck (need %d KB have %d KB free): reverting to direct IO',
2272: $tblsz, $qfree
2273: );
2274: $cmd = 'myisamchk -r -c ' . $sqlroot . '/%s/*.MYI';
2275: }
2276: $fsizelimit = Util_Ulimit::get('fsize');
2277: Util_Ulimit::set('fsize', 'unlimited');
2278: $ret = Util_Process_Safe::exec($cmd, array($db), ['mute_stderr' => true]);
2279: Util_Ulimit::set('fsize', $fsizelimit);
2280: if (!$ret['success'] && false === strpos($ret['stderr'], "doesn't exist")) {
2281: return error("`%s' repair failed:\n%s", $db, $ret['stderr']);
2282: }
2283:
2284: return info("`%s' repair succeeded:\n%s", $db, $ret['output']);
2285: }
2286:
2287: /**
2288: * Kill a mysql connection
2289: *
2290: * @link mysql_processlist
2291: * @param integer $id
2292: * @return bool
2293: */
2294: public function kill($id)
2295: {
2296: $db = $this->_connect_root();
2297: $id = intval($id);
2298: $procs = $this->get_processlist();
2299: $found = 0;
2300: foreach ($procs as $p) {
2301: if ($p['id'] == $id) {
2302: $found = 1;
2303: break;
2304: }
2305: }
2306: if (!$found) {
2307: return error("`%d': invalid query id specified", $id);
2308: }
2309: $q = "KILL $id";
2310: $rs = $db->query($q);
2311:
2312: return (bool)$rs;
2313: }
2314:
2315: /**
2316: * Get active mysql connections
2317: *
2318: * Array
2319: * (
2320: * [0] => Array
2321: * (
2322: * [id] => 11024
2323: * [user] => debug
2324: * [host] => localhost
2325: * [db] => debug
2326: * [command] => Query
2327: * [state] => User sleep
2328: * [info] => select sleep(1000)
2329: * )
2330: * )
2331: *
2332: * @return array
2333: */
2334: public function get_processlist()
2335: {
2336: $conns = array();
2337: $db = $this->_connect_root();
2338: $user = $this->username;
2339: $prefix = $this->get_prefix();
2340: $q = 'SELECT id, user, host, db, command, time, state, info FROM ' .
2341: "information_schema.processlist WHERE user = '" .
2342: $db->real_escape_string($user) . "' OR user LIKE '" . $db->real_escape_string($prefix) . "%'";
2343: $rs = $db->query($q);
2344: while (null != ($row = $rs->fetch_object())) {
2345: $conns[] = array(
2346: 'id' => $row->id,
2347: 'user' => $row->user,
2348: 'host' => $row->host,
2349: 'db' => $row->db,
2350: 'command' => $row->command,
2351: 'state' => $row->state,
2352: 'info' => $row->info
2353: );
2354: }
2355:
2356: return $conns;
2357: }
2358:
2359: /**
2360: * Get basic MySQL server statistics
2361: *
2362: * @return array
2363: */
2364: public function stats(): array {
2365: $conn = \MySQL::initialize();
2366: $vars = [
2367: 'Queries',
2368: 'Uptime',
2369: 'Connections',
2370: 'Questions',
2371: 'TABLE_OPEN_CACHE_HITS',
2372: 'MAX_USED_CONNECTIONS',
2373: 'TABLE_OPEN_CACHE_MISSES',
2374: 'OPENED_FILES',
2375: 'OPENED_TABLES',
2376: 'THREADS_RUNNING',
2377: 'THREADS_CREATED'
2378: ];
2379:
2380: $fields = implode(',', array_map(static function ($var) use ($conn) {
2381: return "'$var'";
2382: }, $vars));
2383: $rs = $conn->query("SELECT LOWER(variable_name) AS name, variable_value AS val FROM information_schema.global_status WHERE variable_name IN($fields);");
2384:
2385: $items = array_build($rs->fetch_all(\MYSQLI_ASSOC), static function ($k, $v) {
2386: return [$v['name'], (int)$v['val']];
2387: });
2388:
2389: $items['observed_queries_per_second'] = null;
2390: $items['queries_per_second'] = round($items['queries']/$items['uptime'], 4);
2391:
2392: $key = 'mysql.qpslocf';
2393: $cache = \Cache_Global::spawn();
2394: if (false !== ($locf = $cache->get($key))) {
2395: if ($items['uptime'] > $locf[0]) {
2396: // check if server reset
2397: $items['observed_queries_per_second'] = round(($items['queries'] - $locf[1]) / ($items['uptime'] - $locf[0]), 4);
2398: $cache->del($key);
2399: }
2400: return $items;
2401: } else {
2402: $cache->set($key, [$items['uptime'], $items['queries']]);
2403: }
2404:
2405:
2406: return $items;
2407: }
2408:
2409: public function _cron(Cronus $c)
2410: {
2411: if (!TELEMETRY_ENABLED) {
2412: return;
2413: }
2414:
2415: $collector = new Collector(PostgreSQL::pdo());
2416: $status = $this->stats();
2417: foreach (MysqlMetrics::getAttributeMap() as $attr => $metric) {
2418: if (!isset($status[$metric])) {
2419: // MariaDB 10.3.20 lacks table cache hits
2420: continue;
2421: }
2422: $val = $status[$metric];
2423:
2424: if ($val instanceof Closure) {
2425: $val = $val($status);
2426: }
2427: $collector->add("mysql-${attr}", null, $val);
2428: }
2429:
2430: }
2431:
2432: /**
2433: * Get max length of a column in mysql schema
2434: *
2435: * @param string $field
2436: * @return int|null
2437: */
2438: public function schema_column_maxlen($field): ?int
2439: {
2440: if (!IS_CLI) {
2441: return $this->query('mysql_schema_column_maxlen', $field);
2442: }
2443: if ($field !== 'user' && $field !== 'db') {
2444: error("unsupported field `%s' requested", $field);
2445: return null;
2446: }
2447: return \Opcenter\Database\MySQL::fieldLength($field);
2448: }
2449: }
ApisCP Modules API documentation generated by ApiGen v7.0.0-alpha.6