Yourls: Yourls 1.8 MySQL 8 broken installer

[jonathandhn]

Last upgrade can't update MySQL 8 DB scheme with one click updater on admin page.

To Reproduce

1. Update code base
2. Go to /admin
3. Click on upgrade DB button

Expected behavior

Upgrade DB should works as expected as before ( MySQL 8.0.23 )

Actual behavior

Can't upgrade DB after merging code base to a new one.
SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-cou CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci' at line 1

Versions

1.8

Additional context

lsphp 7.4 \ Openlitespeed \ MySQL server 8.0.23 (delivered by MySQL Oracle repos for Ubuntu)

[ozh]

Damn the first bug report for 1.8 was fast 🤣

[ozh]

The upgrade runs a few SQL queries, see https://github.com/YOURLS/YOURLS/blob/1.8/includes/functions-upgrade.php#L100-L107

Could you manually check (like, using PHPMyAdmin or something similar) which one of the queries doesn't work for you ?

[jonathandhn]

My database is named yourls-cou if the SQL statement is called as it, it fails. If the SQL statement is called between escapes characters as this ones `it works. I did change this code as followingALTER DATABASE %s → ALTER DATABASE MyEscapeCharacter%sMyEscapeCharacter` then run the script gain, and it's done. Thanks you.

/**
 * Update to 506
 *
 */
function yourls_upgrade_to_506() {
    $ydb = yourls_get_db();
    $error_msg = [];

    echo "<p>Updating DB. Please wait...</p>";

    $queries = array(
        'database charset'  => sprintf('ALTER DATABASE `%s` CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;', YOURLS_DB_NAME),
        'options charset'   => sprintf('ALTER TABLE `%s` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;', YOURLS_DB_TABLE_OPTIONS),
        'short URL charset' => sprintf('ALTER TABLE `%s` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;', YOURLS_DB_TABLE_URL),
        'short URL varchar' => sprintf("ALTER TABLE `%s` CHANGE `keyword` `keyword` VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '';", YOURLS_DB_TABLE_URL),
        'short URL type'    => sprintf("ALTER TABLE `%s` CHANGE `url` `url` TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL;", YOURLS_DB_TABLE_URL),
        'short URL type'    => sprintf("ALTER TABLE `%s` CHANGE `title` `title` TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci", YOURLS_DB_TABLE_URL),
    );

PS : I'm sorry I don't know of get the ride of Github locking characters as ` to control the output of this sentence.

[LeoColomb]

@jonathandhn Thanks! (I've done the formatting for you 😉)

@ozh Oh yes, variables in MySQL statement should always always be escaped.

Ref: https://github.com/YOURLS/YOURLS/pull/2690

[LeoColomb]

@jonathandhn By the way, would you aim opening a pull request with this change?

[RezqZiyad]

I merged the last commit. Still I get:

Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1142 UPDATE command denied to user 'omitted'@'omitted' for table 'ypreg8_options' in /app/includes/vendor/aura/sql/src/AbstractExtendedPdo.php:558

Stack trace:
#0 /app/includes/vendor/aura/sql/src/AbstractExtendedPdo.php(558): PDOStatement->execute()
#1 /app/includes/vendor/aura/sql/src/AbstractExtendedPdo.php(232): Aura\Sql\AbstractExtendedPdo->perform()
#2 /app/includes/Database/Options.php(166): Aura\Sql\AbstractExtendedPdo->fetchAffected()
#3 /app/includes/functions-options.php(70): YOURLS\Database\Options->update()
#4 /app/includes/functions-upgrade.php(58): yourls_update_option()
#5 /app/admin/upgrade.php(76): yourls_upgrade()
#6 {main}
thrown in /app/includes/vendor/aura/sql/src/AbstractExtendedPdo.php on line 558 "GET /admin/upgrade.php?step=3 HTTP/1.1" 500 2009

PS. The user has the right permissions.
Might this help?

Edit: I don't know what really changed, but it updated. Sorry for inconvenience!