`db_host` not used in version 2.19?

[gerundt]

I updated on my shared webhosting to version 2.19. Thank you for the update! 😊

Since I used the following config:

db_type=mysql
db_file=
db_host=localhost
db_database=d1234567
db_username=d1234567

I thought I can enable the localhost-only-mode on the database (MariaDB 10.5.16) but get the following error in the log file:

[2022-10-31 09:00:02] selfoss.ERROR: SQLSTATE[HY000]: General error: 1449 The user specified as a definer ('d1234567'@'%') does not exist {"exception":"[object] (PDOException(code: HY000): SQLSTATE[HY000]: General error: 1449 The user specified as a definer ('d1234567'@'%') does not exist at /www/htdocs/feedreader/src/helpers/DatabaseConnection.php:171)
[stacktrace]
#0 /www/htdocs/feedreader/src/helpers/DatabaseConnection.php(171): PDOStatement->execute()
#1 /www/htdocs/feedreader/src/helpers/DatabaseConnection.php(193): helpers\\DatabaseConnection->execute('UPDATE items SE...', Array)
#2 /www/htdocs/feedreader/src/daos/CommonSqlDatabase.php(27): helpers\\DatabaseConnection->exec('UPDATE items SE...', Array)
#3 /www/htdocs/feedreader/src/daos/mysql/Items.php(205): daos\\mysql\\Database->exec('UPDATE items SE...')
#4 [internal function]: daos\\mysql\\Items->updateLastSeen(Array)
#5 /www/htdocs/feedreader/src/daos/Items.php(54): call_user_func_array(Array, Array)
#6 /www/htdocs/feedreader/src/helpers/ContentLoader.php(310): daos\\Items->__call('updateLastSeen', Array)
#7 /www/htdocs/feedreader/src/helpers/ContentLoader.php(71): helpers\\ContentLoader->fetch(Array)
#8 /www/htdocs/feedreader/src/controllers/Sources/Update.php(37): helpers\\ContentLoader->update()
#9 /www/htdocs/feedreader/index.php(40): controllers\\Sources\\Update->updateAll()
#10 [internal function]: {closure}()
#11 /www/htdocs/feedreader/vendor/bramus/router/src/Bramus/Router/Router.php(458): call_user_func_array(Object(Closure), Array)
#12 /www/htdocs/feedreader/vendor/bramus/router/src/Bramus/Router/Router.php(440): Bramus\\Router\\Router->invoke(Object(Closure), Array)
#13 /www/htdocs/feedreader/vendor/bramus/router/src/Bramus/Router/Router.php(287): Bramus\\Router\\Router->handle(Array, true)
#14 /www/htdocs/feedreader/index.php(170): Bramus\\Router\\Router->run()
#15 {main}
"} 

It looks like selfoss use d1234567@% as login. My hoster use PHP 7.4.30.

Thanks for your help!

[jtojnar]

It sounds like something is wrong with your database. Did you perhaps import the data from another database with different user name?

Looking at https://coderjony.com/blogs/solution-the-user-specified-as-a-definer-root-does-not-exist, it might be possible to fix it by granting access explicitly.

---

It works for me just fine if I use:

db_type=mysql
db_host=localhost
db_database=selfoss_test
db_username=selfoss_test

The database server will correctly complain about password:

[2022-10-31 16:12:56] selfoss.DEBUG: Creating database connection {"dsn":"mysql:host=localhost; dbname=selfoss_test"} 
[2022-10-31 16:12:56] selfoss.ERROR: SQLSTATE[HY000] [1045] Access denied for user 'selfoss_test'@'localhost' (using password: NO) {"exception":"[object] (PDOException(code: 1045): SQLSTATE[HY000] [1045] Access denied for user 'selfoss_test'@'localhost' (using password: NO) at /home/jtojnar/Projects/selfoss/src/helpers/DatabaseConnection.php:39)
[stacktrace]
#0 /home/jtojnar/Projects/selfoss/src/helpers/DatabaseConnection.php(39): PDO->__construct('mysql:host=loca...', 'selfoss_test', '', Array)

If I run ALTER USER 'selfoss_test'@'localhost' IDENTIFIED WITH mysql_native_password;, then it will start working.

[gerundt]

Unfortunately, I cannot changed the permissions since it is a commercial web hoster. I can only enable the localhost-only-mode on the website from the hoster.

I just updated the files from the new selfoss version and use the same database/user from the old version. No migration from a other database.

If I enable the localhost-only-mode I can still login to selfoss and can still read all items. But I get the error when I try to update a item (like mark as read).

[2022-11-02 13:50:56] selfoss.DEBUG: Creating database connection {"dsn":"mysql:host=localhost; dbname=d1234567"} 
[2022-11-02 13:50:56] selfoss.DEBUG: Establishing MySQL database connection  
[2022-11-02 13:50:56] selfoss.ERROR: SQLSTATE[HY000]: General error: 1449 The user specified as a definer ('d1234567'@'%') does not exist {"exception":"[object] (PDOException(code: HY000): SQLSTATE[HY000]: General error: 1449 The user specified as a definer ('d1234567'@'%') does not exist at /www/htdocs/feedreader/src/helpers/DatabaseConnection.php:171)
[stacktrace]
#0 /www/htdocs/feedreader/src/helpers/DatabaseConnection.php(171): PDOStatement->execute()
#1 /www/htdocs/feedreader/src/helpers/DatabaseConnection.php(193): helpers\\DatabaseConnection->execute('UPDATE items SE...', Array)
#2 /www/htdocs/feedreader/src/daos/CommonSqlDatabase.php(27): helpers\\DatabaseConnection->exec('UPDATE items SE...', false)
#3 /www/htdocs/feedreader/src/daos/mysql/Items.php(58): daos\\mysql\\Database->exec('UPDATE items SE...', false)
#4 [internal function]: daos\\mysql\\Items->mark('620313')
#5 /www/htdocs/feedreader/src/daos/Items.php(54): call_user_func_array(Array, Array)
#6 /www/htdocs/feedreader/src/controllers/Items.php(61): daos\\Items->__call('mark', Array)
#7 /www/htdocs/feedreader/index.php(76): controllers\\Items->mark('620313')
#8 [internal function]: {closure}('620313')
#9 /www/htdocs/feedreader/vendor/bramus/router/src/Bramus/Router/Router.php(458): call_user_func_array(Object(Closure), Array)
#10 /www/htdocs/feedreader/vendor/bramus/router/src/Bramus/Router/Router.php(440): Bramus\\Router\\Router->invoke(Object(Closure), Array)
#11 /www/htdocs/feedreader/vendor/bramus/router/src/Bramus/Router/Router.php(287): Bramus\\Router\\Router->handle(Array, true)
#12 /www/htdocs/feedreader/index.php(170): Bramus\\Router\\Router->run()
#13 {main}
"} 

[jtojnar]

It is not clear to me what “localhost-only-mode” is, maybe ask support of your web host.

It sounds to me like switching to it changes the user account so it no longer matches the user under which the triggers were created (see DEFINER in the MySQL docs) and, as a result, the trigger becomes an orphan.

That is not something selfoss can do much about, other than trying to drop and re-create the triggers if it detects that the definer does not match. But I do not think this should be responsibility of an app to try to recover inconsistent database. Instead the web host should migrate all affected database objects over to the new user when modifying users. Or disallow switching the mode for databases containing data and require an manual migration of data into a new database.

[gerundt]

It is not clear to me what “localhost-only-mode” is, maybe ask support of your web host.

On the website from the hoster I can restrict access to the database:

• allow only local access (localhost)
• allow access from everywhere
• allow access only for the following IPs: […]

I think they will change the user from d1234567@localhost to d1234567@% and so one.

It sounds to me like switching to it changes the user account so it no longer matches the user under which the triggers were created (see DEFINER in the MySQL docs) and, as a result, the trigger becomes an orphan.

You are right! I see in phpMyAdmin now the wrong DEFINER in the triggers.

Thank you very much for the tipp!