Upgrade error due to missing global permissions

[jdarwood007]

SMF/other/upgrade.php

Line 1995 in 1b52517

'SET GLOBAL innodb_default_row_format=DYNAMIC',

Access denied; you need (at least one of) the SUPER or SYSTEM_VARIABLES_ADMIN privilege(s) for this operation

@Sesquipedalian

[jdarwood007]

Also going to love this one

You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

SMF/other/upgrade.php

Lines 3624 to 3664 in 1b52517

	Db::$db->query(
	'',
	'CREATE FUNCTION IF NOT EXISTS {identifier:db_name}.smf_entity_decode(txt TEXT CHARSET utf8mb4) RETURNS TEXT CHARSET utf8mb4
	NO SQL
	DETERMINISTIC
	BEGIN
	DECLARE tmp TEXT CHARSET utf8mb4 DEFAULT txt;
	DECLARE entity TEXT CHARSET utf8mb4;
	DECLARE pos1 INT DEFAULT 1;
	DECLARE pos2 INT;
	DECLARE codepoint INT;
	IF txt IS NULL THEN
	RETURN NULL;
	END IF;
	LOOP
	SET pos1 = LOCATE("&#", tmp, pos1);
	IF pos1 = 0 THEN
	RETURN tmp;
	END IF;
	SET pos2 = LOCATE(";", tmp, pos1 + 2);
	IF pos2 > pos1 THEN
	SET entity = SUBSTRING(tmp, pos1, pos2 - pos1 + 1);
	IF entity REGEXP "^&#[[:digit:]]+;$" THEN
	SET codepoint = CAST(SUBSTRING(entity, 3, pos2 - pos1 - 2) AS UNSIGNED);
	SET tmp = CONCAT(LEFT(tmp, pos1 - 1), CHAR(codepoint USING utf32), SUBSTRING(tmp, pos2 + 1));
	END IF;
	IF entity REGEXP "^&#x[[:xdigit:]]+;$" THEN
	SET codepoint = CAST(CONV(SUBSTRING(entity, 4, pos2 - pos1 - 3), 16, 10) AS UNSIGNED);
	SET tmp = CONCAT(LEFT(tmp, pos1 - 1), CHAR(codepoint USING utf32), SUBSTRING(tmp, pos2 + 1));
	END IF;
	END IF;
	SET pos1 = pos1 + 1;
	END LOOP;
	END',
	[
	'db_name' => Db::$db->name,
	]
	);
	Db::$db->disableQueryCheck = false;

[Sesquipedalian]

Oof. The first one could be treated as optional and silently skipped if it failed, but the second one is going to be a pain.

I suppose for the second one we could do a check for the necessary DB permissions and fall back to a PHP implementation if we don't have them. That'll be a lot slower, though, due to the repeated queries it will involve.

[jdarwood007]

The weird part is that I don't know why binary logging is enabled. I don't do replication, and none of my backup operations require it. But I suspect we would have more than one user with it, as some hosts may be using it to replicate data or backup operations.

[Oldiesmann]

I reported the first issue in #8599 but didn't know what was causing it. Nice to know we know what's causing that at least.

[Oldiesmann]

We can get around the first issue by checking for the global row format and specifying the ROW_FORMAT for each table we create if it isn't DYNAMIC (which it should be in most cases since that's the default). Also on my server, binary logging isn't enabled so I'm not sure how common of an issue that will be.