Error syntax error at or near or sqlstate 42601

@YohDeadfall — I understand that part about it, but this is not script that I am creating or even code that I am creating. This is all created under the hood by Npsql/EntityFramework. My quick guess is that I am extending my DbContext from IdentityDbContext<IdentityUser> which wants to create all of the tables for roles, users, claims, etc. If I change this to just extend from DbContext, then everything works as advertised.

Below is the script that EF is trying to use created from dotnet ef migrations script — please be aware that I have removed my custom part of the script for brevity.

You can see there are two specific calls that are being made where [NormalizedName] and [NormalizedUserName] are being used.

CREATE TABLE IF NOT EXISTS "__EFMigrationsHistory" (
    "MigrationId" varchar(150) NOT NULL,
    "ProductVersion" varchar(32) NOT NULL,
    CONSTRAINT "PK___EFMigrationsHistory" PRIMARY KEY ("MigrationId")
);

CREATE TABLE "AspNetRoles" (
    "Id" text NOT NULL,
    "ConcurrencyStamp" text NULL,
    "Name" varchar(256) NULL,
    "NormalizedName" varchar(256) NULL,
    CONSTRAINT "PK_AspNetRoles" PRIMARY KEY ("Id")
);

CREATE TABLE "AspNetUsers" (
    "Id" text NOT NULL,
    "AccessFailedCount" int4 NOT NULL,
    "ConcurrencyStamp" text NULL,
    "Email" varchar(256) NULL,
    "EmailConfirmed" bool NOT NULL,
    "LockoutEnabled" bool NOT NULL,
    "LockoutEnd" timestamptz NULL,
    "NormalizedEmail" varchar(256) NULL,
    "NormalizedUserName" varchar(256) NULL,
    "PasswordHash" text NULL,
    "PhoneNumber" text NULL,
    "PhoneNumberConfirmed" bool NOT NULL,
    "SecurityStamp" text NULL,
    "TwoFactorEnabled" bool NOT NULL,
    "UserName" varchar(256) NULL,
    CONSTRAINT "PK_AspNetUsers" PRIMARY KEY ("Id")
);

CREATE TABLE "AspNetRoleClaims" (
    "Id" int4 NOT NULL,
    "ClaimType" text NULL,
    "ClaimValue" text NULL,
    "RoleId" text NOT NULL,
    CONSTRAINT "PK_AspNetRoleClaims" PRIMARY KEY ("Id"),
    CONSTRAINT "FK_AspNetRoleClaims_AspNetRoles_RoleId" FOREIGN KEY ("RoleId") REFERENCES "AspNetRoles" ("Id") ON DELETE CASCADE
);

CREATE TABLE "AspNetUserClaims" (
    "Id" int4 NOT NULL,
    "ClaimType" text NULL,
    "ClaimValue" text NULL,
    "UserId" text NOT NULL,
    CONSTRAINT "PK_AspNetUserClaims" PRIMARY KEY ("Id"),
    CONSTRAINT "FK_AspNetUserClaims_AspNetUsers_UserId" FOREIGN KEY ("UserId") REFERENCES "AspNetUsers" ("Id") ON DELETE CASCADE
);

CREATE TABLE "AspNetUserLogins" (
    "LoginProvider" text NOT NULL,
    "ProviderKey" text NOT NULL,
    "ProviderDisplayName" text NULL,
    "UserId" text NOT NULL,
    CONSTRAINT "PK_AspNetUserLogins" PRIMARY KEY ("LoginProvider", "ProviderKey"),
    CONSTRAINT "FK_AspNetUserLogins_AspNetUsers_UserId" FOREIGN KEY ("UserId") REFERENCES "AspNetUsers" ("Id") ON DELETE CASCADE
);

CREATE TABLE "AspNetUserRoles" (
    "UserId" text NOT NULL,
    "RoleId" text NOT NULL,
    CONSTRAINT "PK_AspNetUserRoles" PRIMARY KEY ("UserId", "RoleId"),
    CONSTRAINT "FK_AspNetUserRoles_AspNetRoles_RoleId" FOREIGN KEY ("RoleId") REFERENCES "AspNetRoles" ("Id") ON DELETE CASCADE,
    CONSTRAINT "FK_AspNetUserRoles_AspNetUsers_UserId" FOREIGN KEY ("UserId") REFERENCES "AspNetUsers" ("Id") ON DELETE CASCADE
);

CREATE TABLE "AspNetUserTokens" (
    "UserId" text NOT NULL,
    "LoginProvider" text NOT NULL,
    "Name" text NOT NULL,
    "Value" text NULL,
    CONSTRAINT "PK_AspNetUserTokens" PRIMARY KEY ("UserId", "LoginProvider", "Name"),
    CONSTRAINT "FK_AspNetUserTokens_AspNetUsers_UserId" FOREIGN KEY ("UserId") REFERENCES "AspNetUsers" ("Id") ON DELETE CASCADE
);

CREATE INDEX "IX_AspNetRoleClaims_RoleId" ON "AspNetRoleClaims" ("RoleId");

CREATE UNIQUE INDEX "RoleNameIndex" ON "AspNetRoles" ("NormalizedName") WHERE [NormalizedName] IS NOT NULL;

CREATE INDEX "IX_AspNetUserClaims_UserId" ON "AspNetUserClaims" ("UserId");

CREATE INDEX "IX_AspNetUserLogins_UserId" ON "AspNetUserLogins" ("UserId");

CREATE INDEX "IX_AspNetUserRoles_RoleId" ON "AspNetUserRoles" ("RoleId");

CREATE INDEX "EmailIndex" ON "AspNetUsers" ("NormalizedEmail");

CREATE UNIQUE INDEX "UserNameIndex" ON "AspNetUsers" ("NormalizedUserName") WHERE [NormalizedUserName] IS NOT NULL;

INSERT INTO "__EFMigrationsHistory" ("MigrationId", "ProductVersion")
VALUES ('20180514204732_initial', '2.0.3-rtm-10026');

PostgreSQL — состояние SQL: 42601 синтаксическая ошибка

Я хотел бы знать, как использовать динамический запрос внутри функции. Тем не менее, я пробовал много способов, когда пытаюсь скомпилировать мою функцию, появляется сообщение SQL 42601.

Код, который я использую:

Сообщение об ошибке, которое я получаю:

Я пробовал использовать:

Что не так? Как я могу решить эту проблему?

Ваша функция будет работать следующим образом:

Вы не можете смешивать простой и динамический SQL так, как вы пытались это сделать. Весь оператор является либо динамическим, либо простым SQL. Поэтому я создаю одно динамическое выражение, чтобы сделать эту работу. Вы можете быть заинтересованы в главе о выполнении динамических команд в руководстве.

Совокупная функция count() возвращает bigint , но у вас была определенная rowcount как integer , поэтому для выполнения этой работы вам нужен явный cast ::int

Я использую котировку доллара, чтобы избежать цитирования ада.

Однако, предполагается ли это, что это honeypot для инъекций SQL-инъекций или вы серьезно собираетесь его использовать? Для вашего очень частного и безопасного использования это может быть нормально, хотя я бы даже не доверял себе такой функцией. Если есть доступ к ненадежным пользователям, такая функция является загруженным лапкой. Это невозможно сделать безопасным.

Крейг (заклятый враг инъекции SQL!) Может получить легкий ход, когда он увидит, что вы подделали из своего фрагмента кода в ответ на ваш предыдущий вопрос. 🙂

Сам запрос кажется довольно странным, кстати. Но это не имеет значения.

Источник

PostgreSQL error: PDOException: SQLSTATE[42601]: Syntax error: 7 ERROR: syntax error at or near «SEPARATOR» #193

I’m using the farmos.app web-app, I’ve tried this process on both my handset (Samsung S7) and on my PC and have had the same result.
These are my areas — so there are areas on the map, just to confirm:

But when I try and add areas to the log, I get a «No areas found» error:

I have synced (through the sync now button, and synced new TODO logs through the little cloud) but I still have this error. I suspected it was because I was quite far from the field, but I’ve added some test fields that were much closer, and I still get the same error. Any ideas?
Everything else appears to be working — I can push logs from the phone/PC to the site, and I can pull logs from the site>PC/Phone, equipment/assets are all there, just location/areas

The text was updated successfully, but these errors were encountered:

Oh this might be related to a bug I only recently found and, I believe, fixed. It’s possible the update hasn’t reached your device yet. Can you tell me, is there a version number in the drawer menu, like below?

If you’re on version 0.4.4, then you should have that update. If not, there won’t be any version number visible (this is a feature I just added for this kind of diagnostic purpose). You’ll need to refresh and/or close and reopen the app for the update to install.

If you have received the v0.4.4 update and the problem is persisting, then I guess I’ll have to dig a little deeper.

I’m on 0.4.4 I’m afraid.

I’ve done a fresh install of FarmOS, and still have the same problem. Very odd.

How fast is your network connection? One possibility is its timing out, although that seems unlikely.

I’m testing it now and getting a similar bug with assets. In my case it looks like my token has gone stale and I need to login again (getting a 403 error), which is also preventing general syncing. But you’ve said you’re able to sync from the My Logs menu, correct?

Perhaps you could open the developer console (press ctrl+shift+i) from your PC, then try the «Sync Now» button and see what network errors occur in the console? (We definitely need better error handling so these messages show up in the UI in the first place.)

In my case it looks like my token has gone stale and I need to login again (getting a 403 error)

Related to farmOS/field-kit#219? (I still have to test and respond to your comment over there @jgaehring . )

So, the console message is:
GET https://. example.com/farmOS/taxonomy_term.json?vocabulary=3&&page=0 500 (Service unavailable (with message))
And on the FarmOS side of things:
PDOException: SQLSTATE[42601]: Syntax error: 7 ERROR: syntax error at or near «SEPARATOR» LINE 3: . id ORDER BY ss_log.timestamp DESC, ss_log.id DESC SEPARATOR . ^: SELECT ss_asset_current_log.field_farm_asset_target_id AS asset_id FROM (SELECT ss_fdffa.field_farm_asset_target_id AS field_farm_asset_target_id, SUBSTRING_INDEX(GROUP_CONCAT(ss_log.id ORDER BY ss_log.timestamp DESC, ss_log.id DESC SEPARATOR ‘,’), ‘,’, 1) AS ss_current_log_id FROM ss_log INNER JOIN ss_fdffa ON ss_fdffa.entity_type = ‘log’ AND ss_fdffa.entity_id = ss_log.id AND ss_fdffa.deleted = 0 INNER JOIN ss_fdffm ON ss_fdffm.entity_type = ‘log’ AND ss_fdffm.entity_id = ss_log.id AND ss_fdffm.deleted = 0 WHERE (ss_log.timestamp

I’m hosting FarmOS on Postgresql, could it be a PostGresql error?
I tried the GET url in Postman, and came up with the same error.

That is definitely a PostgreSQL error.

Let’s isolate this from Field Kit and fix that first. Do you get the same issue if you go to https://. example.com/farmOS/taxonomy_term.json?vocabulary=3 directly in your browser?

Yea, and if I run
/farmOS/taxonomy_term.json?bundle=farm_areas I also get the same error

Alright — so let’s move this to farmOS/farmOS and debug that first.

If I add return array(); to the top of the function farm_movement_area_assets_property_get() , which simply disables that function, then it fixes the issue.

The purpose of that function is to add an array of assets to the area JSON, so you can easily see what assets are in an area in the area JSON.

That function calls farm_movement_area_assets() , which in turn calls farm_movement_area_assets_query() , which is where the offending query is built.

Will continue to dig.

Here is the specific line of code in farm_movement_area_assets_query() causing the issue:

Notably, a similar line exists in farm_group_members_query() :

And notably, we have an @todo comment in both functions for merging/abstracting them, because they are so similar. So I might look into that while fixing this.

I actually knew about the issue back when we added the group asset code. See this issue on drupal.org: https://www.drupal.org/project/farm/issues/2935784

This code is a bit complex. It uses two MySQL functions that are not available in PostgreSQL: SUBSTRING_INDEX() and GROUP_CONCAT() .

But maybe we can find alternatives.

. or maybe we can find a better way of doing it so that those functions aren’t necessary.

Was talking about this query with @amorsent and he threw together a reworked query that does the same thing without those functions. Posting this here for future reference. still need to convert it back to PHP using Drupal’s DB API.

Man, I am glad you’re looking at this. I wouldn’t even know where to begin with that query. What even are half the columns?!

The query @mstenta listed above would have been just the subquery part.

Note also — the subquery ends up needing to work out the current location of every asset.

I think it can be done without the subquery, in a more efficient way that does not need to work out the location of every asset.

This version works more or less like this:
Give me all the logs (before a given time) moving any asset into the specified location.
Now exclude ALL future movements of the asset (up to the given time)

Example below is untested.
I have no means to test it as I’m not setup with a dev environment. I have just performed logical operations to the query. I’ve mostly maintained the existing table aliases. However some of them make less sense in this structure and should probably be updated for clarity.

A potential snag is that movements can reference multiple assets and multiple areas. I believe the original query handles this (we need tests!) but the new queries might not? Need to think it through a bit more and run some tests.

Testing this and it seems to be working well.

Here is the (slightly modified) query I’m testing with:

One last comment before turning in for the night, credit to @amorsent:

btw .. you probably could restructure this query to start with the farm_asset table and join in the log table . might arguably make more sense that way
«Assets for which the last move was into the specified location» instead of «logs that were the last log to move an asset into a location»

FYI @Skipper-is (and anyone else trying to use Field Kit with a self-hosted farmOS using PostgreSQL): I won’t have time to pick this up again for a bit — so if you want to make the small edit I mentioned above as a temporary workaround, that will allow you to use the app.

If I add return array(); to the top of the function farm_movement_area_assets_property_get() , which simply disables that function, then it fixes the issue.

So the error I reported here is actually the same error/SQL code as this [42601]. So this is the error message from that:
PDOException: SQLSTATE[42601]: Syntax error: 7 ERROR: syntax error at or near «SEPARATOR» LINE 3: . id ORDER BY ss_log.timestamp DESC, ss_log.id DESC SEPARATOR . ^: SELECT ss_asset_current_log.field_farm_asset_target_id AS asset_id FROM (SELECT ss_fdffa.field_farm_asset_target_id AS field_farm_asset_target_id, SUBSTRING_INDEX(GROUP_CONCAT(ss_log.id ORDER BY ss_log.timestamp DESC, ss_log.id DESC SEPARATOR ‘,’), ‘,’, 1) AS ss_current_log_id FROM ss_log INNER JOIN ss_fdffa ON ss_fdffa.entity_type = ‘log’ AND ss_fdffa.entity_id = ss_log.id AND ss_fdffa.deleted = 0 INNER JOIN ss_fdffm ON ss_fdffm.entity_type = ‘log’ AND ss_fdffm.entity_id = ss_log.id AND ss_fdffm.deleted = 0 WHERE (ss_log.timestamp
This came up when I clicked a specific field on the main dashboard map, other fields worked, but one or 2 produced this error.

Further reference to this issue: Error appears on fields that have had a group moved to it (eg, my goat group, made up of my individual goats). If the group has moved off, there is no error

Источник

PostgreSQL error 42601- How we fix it

by Sijin George | Sep 12, 2019

Syntax errors are quite common while coding.

But, things go for a toss when it results in website errors.

PostgreSQL error 42601 also occurs due to syntax errors in the database queries.

At Bobcares, we often get requests from PostgreSQL users to fix errors as part of our Server Management Services.

Today, let’s check PostgreSQL error in detail and see how our Support Engineers fix it for the customers.

What causes error 42601 in PostgreSQL?

PostgreSQL is an advanced database engine. It is popular for its extensive features and ability to handle complex database situations.

Applications like Instagram, Facebook, Apple, etc rely on the PostgreSQL database.

But what causes error 42601?

PostgreSQL error codes consist of five characters. The first two characters denote the class of errors. And the remaining three characters indicate a specific condition within that class.

Here, 42 in 42601 represent the class “Syntax Error or Access Rule Violation“.

In short, this error mainly occurs due to the syntax errors in the queries executed. A typical error shows up as:

Here, the syntax error has occurred in position 119 near the value “parents” in the query.

How we fix the error?

Now let’s see how our PostgreSQL engineers resolve this error efficiently.

Recently, one of our customers contacted us with this error. He tried to execute the following code,

But, this ended up in PostgreSQL error 42601. And he got the following error message,

Our PostgreSQL Engineers checked the issue and found out the syntax error. The statement in Line 5 was a mix of plain and dynamic SQL. In general, the PostgreSQL query should be either fully dynamic or plain. Therefore, we changed the code as,

This resolved the error 42601, and the code worked fine.

[Need more assistance to solve PostgreSQL error 42601?- We’ll help you.]

Conclusion

In short, PostgreSQL error 42601 occurs due to the syntax errors in the code. Today, in this write-up, we have discussed how our Support Engineers fixed this error for our customers.

PREVENT YOUR SERVER FROM CRASHING!

Never again lose customers to poor server speed! Let us help you.

Our server experts will monitor & maintain your server 24/7 so that it remains lightning fast and secure.

SELECT * FROM long_term_prediction_anomaly WHERE + “‘Timestamp’” + ‘”BETWEEN ‘” +
2019-12-05 09:10:00+ ‘”AND’” + 2019-12-06 09:10:00 + “‘;”)

Hello Joe,
Do you still get PostgreSQL errors? If you need help, we’ll be happy to talk to you on chat (click on the icon at right-bottom).

У меня ошибка drop table exists “companiya”;

CREATE TABLE “companiya” (
“compania_id” int4 NOT NULL,
“fio vladelca” text NOT NULL,
“name” text NOT NULL,
“id_operator” int4 NOT NULL,
“id_uslugi” int4 NOT NULL,
“id_reklama” int4 NOT NULL,
“id_tex-specialist” int4 NOT NULL,
“id_filial” int4 NOT NULL,
CONSTRAINT “_copy_8” PRIMARY KEY (“compania_id”)
);

CREATE TABLE “filial” (
“id_filial” int4 NOT NULL,
“street” text NOT NULL,
“house” int4 NOT NULL,
“city” text NOT NULL,
CONSTRAINT “_copy_5” PRIMARY KEY (“id_filial”)
);

CREATE TABLE “login” (
“id_name” int4 NOT NULL,
“name” char(20) NOT NULL,
“pass” char(20) NOT NULL,
PRIMARY KEY (“id_name”)
);

CREATE TABLE “operator” (
“id_operator” int4 NOT NULL,
“obrabotka obrasheniya” int4 NOT NULL,
“konsultirovanie” text NOT NULL,
“grafick work” date NOT NULL,
CONSTRAINT “_copy_2” PRIMARY KEY (“id_operator”)
);

CREATE TABLE “polsovateli” (
“id_user” int4 NOT NULL,
“id_companiya” int4 NOT NULL,
“id_obrasheniya” int4 NOT NULL,
“id_oshibka” int4 NOT NULL,
CONSTRAINT “_copy_6” PRIMARY KEY (“id_user”)
);

CREATE TABLE “reklama” (
“id_reklama” int4 NOT NULL,
“tele-marketing” text NOT NULL,
“soc-seti” text NOT NULL,
“mobile” int4 NOT NULL,
CONSTRAINT “_copy_3” PRIMARY KEY (“id_reklama”)
);

CREATE TABLE “tex-specialist” (
“id_tex-specialist” int4 NOT NULL,
“grafik” date NOT NULL,
“zarplata” int4 NOT NULL,
“ispravlenie oshibok” int4 NOT NULL,
CONSTRAINT “_copy_7” PRIMARY KEY (“id_tex-specialist”)
);

CREATE TABLE “uslugi” (
“id_uslugi” int4 NOT NULL,
“vostanavlenia parola” int4 NOT NULL,
“poterya acaunta” int4 NOT NULL,
CONSTRAINT “_copy_4” PRIMARY KEY (“id_uslugi”)
);

ALTER TABLE “companiya” ADD CONSTRAINT “fk_companiya_operator_1” FOREIGN KEY (“id_operator”) REFERENCES “operator” (“id_operator”);
ALTER TABLE “companiya” ADD CONSTRAINT “fk_companiya_uslugi_1” FOREIGN KEY (“id_uslugi”) REFERENCES “uslugi” (“id_uslugi”);
ALTER TABLE “companiya” ADD CONSTRAINT “fk_companiya_filial_1” FOREIGN KEY (“id_filial”) REFERENCES “filial” (“id_filial”);
ALTER TABLE “companiya” ADD CONSTRAINT “fk_companiya_reklama_1” FOREIGN KEY (“id_reklama”) REFERENCES “reklama” (“id_reklama”);
ALTER TABLE “companiya” ADD CONSTRAINT “fk_companiya_tex-specialist_1” FOREIGN KEY (“id_tex-specialist”) REFERENCES “tex-specialist” (“id_tex-specialist”);
ALTER TABLE “polsovateli” ADD CONSTRAINT “fk_polsovateli_companiya_1” FOREIGN KEY (“id_companiya”) REFERENCES “companiya” (“compania_id”);

ERROR: ОШИБКА: ошибка синтаксиса (примерное положение: “”companiya””)
LINE 1: drop table exists “companiya”;
^

Источник

Приложение A. Коды ошибок PostgreSQL

Всем сообщениям, которые выдаёт сервер PostgreSQL , назначены пятисимвольные коды ошибок, соответствующие кодам «SQLSTATE» , описанным в стандарте SQL. Приложения, которые должны знать, какое условие ошибки имело место, обычно проверяют код ошибки и только потом обращаются к текстовому сообщению об ошибке. Коды ошибок, скорее всего, не изменятся от выпуска к выпуску PostgreSQL , и они не меняются при локализации как сообщения об ошибках. Заметьте, что отдельные, но не все коды ошибок, которые выдаёт PostgreSQL , определены стандартом SQL; некоторые дополнительные коды ошибок для условий, не описанных стандартом, были добавлены независимо или позаимствованы из других баз данных.

Согласно стандарту, первые два символа кода ошибки обозначают класс ошибок, а последние три символа обозначают определённое условие в этом классе. Таким образом, приложение, не знающее значение определённого кода ошибки, всё же может понять, что делать, по классу ошибки.

В Таблице A-1 перечислены все коды ошибок, определённые в PostgreSQL 9.4.1. (Некоторые коды в настоящее время не используются, хотя они определены в стандарте SQL.) Также показаны классы ошибок. Для каждого класса ошибок имеется «стандартный» код ошибки с последними тремя символами 000. Этот код выдаётся только для таких условий ошибок, которые относятся к определённому классу, но не имеют более определённого кода.

Символ, указанный в колонке «Имя условия» , определяет условие в PL/pgSQL . Имена условий могут записываться в верхнем или нижнем регистре. (Заметьте, что PL/pgSQL , в отличие от ошибок, не распознаёт предупреждения; то есть классы 00, 01 и 02.)

Для некоторых типов ошибок сервер сообщает имя объекта базы данных (таблица, колонка таблицы, тип данных или ограничение), связанного с ошибкой; например, имя уникального ограничения, вызвавшего ошибку unique_violation. Такие имена передаются в отдельных полях сообщения об ошибке, чтобы приложениям не пришлось извлекать его из возможно локализованного текста ошибки для человека. На момент выхода PostgreSQL 9.3 полностью охватывались только ошибки класса SQLSTATE 23 (нарушения ограничений целостности), но в будущем должны быть охвачены и другие классы.

Источник