No preferences available.

amordecosmos · 2021-12-07 07:42:45

This is a strange one.

I’m using Digital Ocean’s Managed database, which is MySQL 8.0.26.

I have installed TXP 4.8, 4.8.7, and 4.9-Dev and they all install fine – except the Preferences page. That page is blank except ‘ No preferences available.’

Put it in Debug mode you say? I can’t, because the page is blank.

But because I’m TXP familiar, I do go into the database and change it to debugging. No difference.

I have turned all plugins off.

Any clues?

gaekwad · 2021-12-07 10:29:33

Does the browser console show anything useful?

amordecosmos · 2022-11-20 19:14:22

Almost a year to the day that I come back to this. I still don’t get what is wrong.

Installed 4.8.8 today
Digital Ocean’s Managed database, which is MySQL 8.0.28

And the result I get is the preferences tab is blank except “No preferences available.”

All other tabs work as expected.

The debugging info is:

User_Error "Unknown column '' in 'where clause'"
in /var/www/html/textpattern/lib/txplib_db.php at line 453.

adminErrorHandler()
textpattern/lib/txplib_db.php:453 trigger_error()
textpattern/lib/txplib_db.php:1191 safe_query()
textpattern/lib/txplib_db.php:1039 startRows()
textpattern/include/txp_prefs.php:204 safe_rows_start()
textpattern/include/txp_prefs.php:45 prefs_list()
textpattern/index.php:230 include()

Warning "mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given"
in /var/www/html/textpattern/lib/txplib_db.php at line 1239.

adminErrorHandler()
textpattern/lib/txplib_db.php:1239 mysqli_num_rows()
textpattern/include/txp_prefs.php:218 numRows()
textpattern/include/txp_prefs.php:45 prefs_list()
textpattern/index.php:230 include()

Anyone have a clue?

Update I went in an stuck an echo on the $q to see what the query was.

SELECT *, FIELD(event, 'site','admin','publish','feeds','comments','custom') AS sort_value FROM txp_prefs WHERE event != "" AND type IN(0, 1) AND (user_name = '' OR (user_name = 'admin' AND name NOT IN ( SELECT name FROM txp_prefs WHERE user_name = '' ))) ORDER BY sort_value = 0, sort_value, event, position

EDIT

Well. This is weird.

I edited txplib.db.php at line 434 to add these:

    $var = "event != ''";
           $q = str_replace('event != ""',$var,$q);

Now it works fine. I think somehow a double-quote instead of a single quote is sneaking into the query.

Last edited by amordecosmos (2022-11-20 21:26:21)

jakob · 2022-11-20 21:23:59

If you try executing that SQL query directly in the database via phpMyAdmin or another database manager (adminer/Sequel Ace…) do you get any results?

When I try that query on a database on my local webserver, it outputs the relevant subset of the txp_prefs table.

amordecosmos · 2022-11-20 21:29:46

@Jakob

Have no idea why I never thought of running it on Sequel Ace previously. So I did. EXACT same error.

However, changing the quotes on event != “” results in success.

Is it me? Did I do this?

Update

My guess would be line 192 of txp_pref.php

$sql[] = 'event != "" AND type IN('.PREF_CORE.', '.PREF_PLUGIN.')';

Last edited by amordecosmos (2022-11-20 21:41:57)

jakob · 2022-11-20 22:31:16

Glad you managed to locate the problem.

That said, I copy and pasted straight from your echoed query into Sequel Ace and it worked immediately. And the quoting in your code example in txp_pref.php looks correct to me too.

Wild guess: Could there be something in one of your prefs entries that includes a rogue quote?

amordecosmos · 2022-11-20 22:41:41

jakob wrote #334169:

Wild guess: Could there be something in one of your prefs entries that includes a rogue quote?

Maybe. But I think it may be a quirk of Digital Ocean’s Managed Databases.

1) I have never had this issue before on the many TXP sites I’ve installed
2) These are fresh installs. I didn’t have any prefs to conflict. And just now, I upgraded a 4.7 site to 4.8.8 and, having editing the txplib_pref file, it works fine.

gaekwad · 2022-11-22 15:41:44

amordecosmos wrote #334170:

Maybe. But I think it may be a quirk of Digital Ocean’s Managed Databases.

I’ve made a note to spin up a DO managed database if / when we have our server hosting renewed with them, so another pair of eyes might be able to shed some light on why this happens.

Textpattern CMS

Textpattern CMS support forum

#1 2021-12-07 07:42:45