Ok, I’ve solved this. RankMath, as of now, is incompatible with older WordPress installs from the “pre UTF-8” era. That’s why redirection tables are not correctly created. My wp-config.php was also quite ancient, very short and with no charset definitions at all.
This seems to have a relationship with the activation issue as well, but I’m not sure. After a lot of fiddling with the plugin it seems its keeping the connection. I will update the other ticket accordingly.
What I did: I found a script that converts databases from latin1 to UTF8. Made a copy of the database, run the script, and changed the collating of the tables and database to utf8mb4_unicode_ci. I also updated my wp-config.php to the current version.
I then had to deactivate and reactivate Rank Math so tables would be correctly created. After that, I could create redirections as well as import them from the Redirection plugin.
I’m posting the code I used with no guarantees. You need to enter the database user, password and database name.
<?php
/**
* Requires php >= 5.5
*
* Use this script to convert utf-8 data in utf-8 mysql tables stored via latin1 connection
* This is a PHP port from: https://gist.github.com/njvack/6113127
*
* @link : http://www.ridesidecar.com/2013/07/30/of-databases-and-character-encodings/
*
* BACKUP YOUR DATABASE BEFORE YOU RUN THIS SCRIPT!
*
* Once the script ran over your databases, change your database connection charset to utf8:
*
* $dsn = 'mysql:host=localhost;port=3306;charset=utf8';
*
* DON'T RUN THIS SCRIPT MORE THAN ONCE!
*
* @author hollodotme
*/
header('Content-Type: text/plain; charset=utf-8');
$dsn = 'mysql:host=localhost;port=3306;charset=latin1';
$user = 'DATABASEUSER';
$password = 'PASSWORD';
$options = [
\PDO::ATTR_CURSOR => \PDO::CURSOR_FWDONLY,
\PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true,
\PDO::MYSQL_ATTR_INIT_COMMAND => "SET CHARACTER SET latin1",
];
$dbManager = new \PDO( $dsn, $user, $password, $options );
$databasesToConvert = [ 'DATABASE', /** database3, ... */ ];
$typesToConvert = [ 'char', 'varchar', 'tinytext', 'mediumtext', 'text', 'longtext' ];
foreach ( $databasesToConvert as $database )
{
echo $database, ":\n";
echo str_repeat( '=', strlen( $database ) + 1 ), "\n";
$dbManager->exec( "USE <code>{$database}</code>" );
$tablesStatement = $dbManager->query( "SHOW TABLES" );
while ( ($table = $tablesStatement->fetchColumn()) )
{
echo "Table: {$table}:\n";
echo str_repeat( '-', strlen( $table ) + 8 ), "\n";
$columnsToConvert = [ ];
$columsStatement = $dbManager->query( "DESCRIBE <code>{$table}</code>" );
while ( ($tableInfo = $columsStatement->fetch( \PDO::FETCH_ASSOC )) )
{
$column = $tableInfo['Field'];
echo ' * ' . $column . ': ' . $tableInfo['Type'];
$type = preg_replace( "#\(\d+\)#", '', $tableInfo['Type'] );
if ( in_array( $type, $typesToConvert ) )
{
echo " => must be converted\n";
$columnsToConvert[] = $column;
}
else
{
echo " => not relevant\n";
}
}
if ( !empty($columnsToConvert) )
{
$converts = array_map(
function ( $column )
{
return "<code>{$column}</code> = CONVERT(CAST(CONVERT(<code>{$column}</code> USING latin1) AS binary) USING utf8)";
},
$columnsToConvert
);
$query = "UPDATE <code>{$table}</code> SET " . join( ', ', $converts );
echo "\n", $query, "\n";
$dbManager->exec( $query );
}
echo "\n--\n";
}
echo "\n";
}
