Troubles implementing mysql backend

[deathbybandaid]

I've been using sqlite for most of the time that I've used selfoss, and was curious if switching to mysql would provide any performance benefit.

Created mysql user/pass, created a database, gave user@localhost permissions for the database.

mysql 8.0.31-0ubuntu0.22.04.1
php 8.1
selfoss 2.20-d45cba4

[2023-01-03T12:43:10.022904-05:00] selfoss.ERROR: SQLSTATE[HY000]: General error: 1360 Trigger does not exist {"exception":"[object] (PDOException(code: HY000): SQLSTATE[HY000]: General error: 1360 Trigger does not exist at /var/www/html/selfoss/src/helpers/DatabaseConnection.php:171)
[stacktrace]
#0 /var/www/html/selfoss/src/helpers/DatabaseConnection.php(171): PDOStatement->execute()
#1 /var/www/html/selfoss/src/helpers/DatabaseConnection.php(193): helpers\\DatabaseConnection->execute()
#2 /var/www/html/selfoss/src/daos/CommonSqlDatabase.php(27): helpers\\DatabaseConnection->exec()
#3 /var/www/html/selfoss/src/daos/mysql/Database.php(241): daos\\mysql\\Database->exec()
#4 [internal function]: daos\\mysql\\Database->__construct()
#5 /var/www/html/selfoss/vendor/level-2/dice/Dice.php(132): ReflectionMethod->invokeArgs()
#6 /var/www/html/selfoss/vendor/level-2/dice/Dice.php(96): Dice\\Dice->Dice\\{closure}()
#7 /var/www/html/selfoss/vendor/level-2/dice/Dice.php(183): Dice\\Dice->create()
#8 /var/www/html/selfoss/vendor/level-2/dice/Dice.php(244): Dice\\Dice->expand()
#9 /var/www/html/selfoss/vendor/level-2/dice/Dice.php(132): Dice\\Dice->Dice\\{closure}()
#10 /var/www/html/selfoss/vendor/level-2/dice/Dice.php(96): Dice\\Dice->Dice\\{closure}()
#11 /var/www/html/selfoss/vendor/level-2/dice/Dice.php(183): Dice\\Dice->create()
#12 /var/www/html/selfoss/vendor/level-2/dice/Dice.php(244): Dice\\Dice->expand()
#13 /var/www/html/selfoss/vendor/level-2/dice/Dice.php(132): Dice\\Dice->Dice\\{closure}()
#14 /var/www/html/selfoss/vendor/level-2/dice/Dice.php(96): Dice\\Dice->Dice\\{closure}()
#15 /var/www/html/selfoss/vendor/level-2/dice/Dice.php(247): Dice\\Dice->create()
#16 /var/www/html/selfoss/vendor/level-2/dice/Dice.php(119): Dice\\Dice->Dice\\{closure}()
#17 /var/www/html/selfoss/vendor/level-2/dice/Dice.php(96): Dice\\Dice->Dice\\{closure}()
#18 /var/www/html/selfoss/index.php(161): Dice\\Dice->create()
#19 [internal function]: {closure}()
#20 /var/www/html/selfoss/vendor/bramus/router/src/Bramus/Router/Router.php(458): call_user_func_array()
#21 /var/www/html/selfoss/vendor/bramus/router/src/Bramus/Router/Router.php(440): Bramus\\Router\\Router->invoke()
#22 /var/www/html/selfoss/vendor/bramus/router/src/Bramus/Router/Router.php(287): Bramus\\Router\\Router->handle()
#23 /var/www/html/selfoss/index.php(170): Bramus\\Router\\Router->run()
#24 {main}
"}

[jtojnar]

That’s weird. Did you try importing the database contents from sqlite or something?

You can try work around this issue by changing DROP TRIGGER to DROP TRIGGER IF EXISTS on the following lines:

selfoss/src/daos/mysql/Database.php

Lines 241 to 242 in d45cba4

	$this->exec('DROP TRIGGER insert_updatetime_trigger');
	$this->exec('DROP TRIGGER update_updatetime_trigger');

[deathbybandaid]

I did not import anything, I exported my opml with the plan of re-importing

I'll try that modification

[deathbybandaid]

That fixed it for me, I did have to drop my database and recreate it, as it was yelling about duplicate tables

[jtojnar]

That sounds like something got broken during the initial db table creation. Does it happen again without IF EXISTS if you drop and re-create the database? Did you see anything else in the logs?

[deathbybandaid]

well, now it's just bizarre, as removing IF EXISTS allows for a working database backend (after dropping)

[jtojnar]

This is so weird. We have another user reporting this.