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