Expiry not working - Binary type not compatible with aura/sql fetchOne method

[Omniru]

Binary type not compatible with aura/sql fetchOne method

YOURLS-Expiry/expiry/plugin.php

Line 1328 in 5bf6c72

$table_expiry .= "keyword binary(100) NOT NULL, ";

YOURLS-Expiry/expiry/plugin.php

Line 790 in 5bf6c72

$expiry = $ydb->fetchOne($sql, $binds);

In the latest version 2.4.0 I had issues with the create statement creating the field as a binary type instead of a varchar(200) like is shown in the expiry.sql code. After changing that type to varchar yourl's aura/sql implementation of fetchOne is able to find the record in the database and successfully delete them as expected.

`yourls_add_action( 'activated_expiry/plugin.php', function () {

global $ydb;
// Create the expiry table
$table = YOURLS_DB_PREFIX . 'expiry';
$table_expiry = "CREATE TABLE IF NOT EXISTS ".$table." (";
$table_expiry .= "keyword varchar(200) NOT NULL, ";
$table_expiry .= "type varchar(5) NOT NULL, ";
$table_expiry .= "click varchar(5), ";
$table_expiry .= "timestamp varchar(20), ";
$table_expiry .= "shelflife varchar(20), ";
$table_expiry .= "postexpire varchar(200), ";
$table_expiry .= "PRIMARY KEY (keyword) ";
$table_expiry .= ") ENGINE=InnoDB DEFAULT CHARSET=latin1;";

$tables = $ydb->fetchAffected($table_expiry);
});`

[joshp23]

i pushed this. Thanks for the fix, maybe consider looking into learning about pull requests?

[Omniru]

I am curious if you had it set to a binary type to address issues with using a character set that's a-z A-Z0-6, since the behavior I now see from REPLACE INTO given a latin1 latin1_sweedish_ci collation in mariadb, and likely other databases, will cause it to overwrite different casing and cause links not to expire.

…

On Thu, Jun 17, 2021 at 4:37 PM Josh Panter ***@***.***> wrote: i pushed this. Thanks for the fix, maybe consider looking into learning about pull requests? — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub <#36 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ACR7PUPILDYNAXKUYWFZS2TTTJ2KXANCNFSM46XYU73Q> .

[Omniru]

I ended up formatting the table like this to make it respect casing, modeled after yourls_url table. I am still testing it though.
--I'm also unsure if bigint is an issue for other databases or not as I'm not sure what's normally supported.

// Create tables for this plugin when activated
yourls_add_action('activated_expiry/plugin.php', function () {

    global $ydb;
    // Create the expiry table
    $table        = YOURLS_DB_PREFIX . 'expiry';
    $table_expiry = "CREATE TABLE IF NOT EXISTS `" . $table . "` (";
    $table_expiry .= "keyword varchar(100) COLLATE utf8mb4_bin NOT NULL DEFAULT '', ";
    $table_expiry .= "type varchar(5) NOT NULL, ";
    $table_expiry .= "click varchar(5), ";
    $table_expiry .= "timestamp bigint(20), ";
    $table_expiry .= "shelflife int(11), ";
    $table_expiry .= "postexpire varchar(200), ";
    $table_expiry .= "PRIMARY KEY (keyword) ";
    $table_expiry .= ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;";

    $tables = $ydb->fetchAffected($table_expiry);
});