When updating a site setting, ERROR: zero-length delimited identifier

AI-generated summary

A user, nevscope, reports an issue where they cannot update site settings in the admin panel due to a database error: ERROR: zero-length delimited identifier at or near """". The error occurs when trying to update fields marked in orange/yellow, and it persists even in safe mode. supermathie suggests disabling Cloudflare, but the user confirms it’s already disabled. The logs reveal the error stems from an incorrect SQL query where the column name is missing, such as WHERE "site_settings"."" = 1697. Stephen asks about installed plugins, and the user shares a screenshot. The user also confirms the issue prevents migration to another server. After investigation, nevscope resolves the issue manually by fixing the site_settings table index. riking identifies the root cause: the query should have been WHERE "site_settings"."id" = 20, indicating a schema loading issue in Rails. The user concludes that restoring from a backup was necessary to fix the corrupted index.

In the admin I am not able to change any fields which have turned to orange/yellow -so in other words I can only save things once (those not yet updated) - any ideas what the problem could be?

Does the problem continue in safe mode?

yoursiteurl.com/safe-mode

Yes still get error in safe-mode

Are you behind Cloudflare?

Yes we have cloudflare :face_with_raised_eyebrow:

Should this be disabled for the forum somehow?

We have a LONG history of Cloudflare causing problems like this.

Try disabling it and check if the problem goes away.

If you need it, search Meta for how to disable all the CF “optimizations”.

The alias is setup with a dark cloud - does this still cause the problems?

image

No, that means CF is disabled which is :+1:

So it is not CF causing the problem.

btw I tried from both chrome and firefox to make sure it is not a browser issue.

In that case, check the /logs URL path as an admin to see if there’s any errors.

Can you use devtools to see what the actual error is?

Which plugins are installed?

Checked the logs and there are some errors after I tried to reset a setting:

Job exception: PG::SyntaxError: ERROR: zero-length delimited identifier at or near """" LINE 1: ...'2020-04-14 17:11:33.973774' WHERE "site_settings"."" = 1697		7:11 pm
Job exception: PG::SyntaxError: ERROR: zero-length delimited identifier at or near """" LINE 1: ...'2020-04-14 17:27:06.173173' WHERE "site_settings"."" = 1697		7:27 pm
ActiveRecord::StatementInvalid (PG::SyntaxError: ERROR: zero-length delimited identifier at or near """" LINE 1: ...'2020-04-14 17:39:16.098301' WHERE "site_settings"."" = 1700		7:39 pm
Failed to handle exception in exception app middleware : PG::SyntaxError: ERROR: zero-length delimited identifier at or near """" LINE 1: ...'2020-04-14 17:39:16.098301' WHERE "site_settings"."" = 17

It seems this error is now preventing us from migrating to another server.

Any ideas on how we can solve this database problem?

I can confirm the table has an index? Any other ideas?

if we run this following directly in postgres:

UPDATE "site_settings" SET "value" = 'Community Forum 2', "updated_at" = '2020-04-24 07:02:39.023579' WHERE "site_settings"."" = 20)

error is

**SQL error:**

ERROR: zero-length delimited identifier at or near """" LINE 1: ... '2020-04-24 07:02:39.023579' WHERE "site_settings"."" = 20)

Issue has been resolved manually by altering and comparing tables.

That obviously should be WHERE "site_settings"."id" = 20. Something went wrong in Rails loading the schema, or something.

Correct, there was a problem with the index of the site_settings table and was not easy to fix the dump and restore a backup :slight_smile: