Could not locate column in row for column 'user.id'
Hi Team,
I am currently running Mailman 3 with version 3.3.1 and in built postfix version 2.10.1-6 on RHEL 7.5 in production environment from the past 2 months almost.
From the past few days, I found my server is responding to the users very slowly and throwing the Server Error page (of course getting the required page after multiple refreshes) for almost every click on the postorius pages (not yet observed on hyperkitty front end which in fact is not so important for me as of now).
During that time, I observed in the mailman.log an error message saying "Could not locate column in row for column 'user.id'". The complete traceback log is attached with the mail. The error message is generated for almost all the lists and all the clicks.
And despite this error message, I don't know how the required page is loaded after a few refreshes of the webpage.
I don't know how to resolve this issue. Please advise. Also, please letme know if anything I need to share with you.
-- Thanks & Regards, Shashi Kanth.K 9052671936
On 7/26/20 10:56 AM, Shashikanth Komandoor wrote:
Hi Team,
I am currently running Mailman 3 with version 3.3.1 and in built postfix version 2.10.1-6 on RHEL 7.5 in production environment from the past 2 months almost.
From the past few days, I found my server is responding to the users very slowly and throwing the Server Error page (of course getting the required page after multiple refreshes) for almost every click on the postorius pages (not yet observed on hyperkitty front end which in fact is not so important for me as of now).
During that time, I observed in the mailman.log an error message saying "Could not locate column in row for column 'user.id'". The complete traceback log is attached with the mail. The error message is generated for almost all the lists and all the clicks.
I don't understand what's going on. I suspect some kind of database corruption, but I don't know what.
The traceback indicates Mailman is getting the Members of a list and in this process is trying to get the display_name for a member. The member's subscriber attribute doesn't have a display_name so it is trying to get is from the member's user attribute so it is trying to get the user record for the member's email address via IUserManager.get_user(). This in turn gets the Address record for the email address and returns it's user attribute.
This leads to the
sqlalchemy.exc.NoSuchColumnError: "Could not locate column in row for column 'user.id'"
error which I don't understand. There is no table with a 'user.id' column. user.id refers to the id column in the user table which comes from the user_id column in the address table which should be a reference to the id column in the user table, not a reference to ay 'user.id' column.
What is your backend database and what is the structure of the address table in that database. Have you done any kind of direct database query that might have affected this?
-- Mark Sapiro <mark@msapiro.net> The highway is for gamblers, San Francisco Bay Area, California better use your sense - B. Dylan
Hi Mark,
Thank you for your response. And sorry for not giving youcomplete information as I am not sue what to share with you.
I am using Postgresql-11.7 as my backend database. The below isthe structure of the address table in* mailman *database:
mailman=# \d address; Table "public.address" Column | Type | Collation | Nullable | Default ----------------+-----------------------------+-----------+----------+------------------------------------- id | integer | | not null | nextval('address_id_seq'::regclass) email | character varying | | | _original | character varying | | | display_name | character varying | | | verified_on | timestamp without time zone | | | registered_on | timestamp without time zone | | | user_id | integer | | | preferences_id | integer | | | Indexes: "address_pkey" PRIMARY KEY, btree (id) "ix_address_email" UNIQUE, btree (email) "ix_address_preferences_id" btree (preferences_id) "ix_address_user_id" btree (user_id) Foreign-key constraints: "address_preferences_id_fkey" FOREIGN KEY (preferences_id) REFERENCES preferences(id) "address_user_id_fkey" FOREIGN KEY (user_id) REFERENCES "user"(id) Referenced by: TABLE ""user"" CONSTRAINT "_preferred_address" FOREIGN KEY (_preferred_address_id) REFERENCES address(id) ON DELETE SET NULL TABLE "autoresponserecord" CONSTRAINT "autoresponserecord_address_id_fkey" FOREIGN KEY (address_id) REFERENCES address(id) TABLE "member" CONSTRAINT "member_address_id_fkey" FOREIGN KEY (address_id) REFERENCES address(id) TABLE "onelastdigest" CONSTRAINT "onelastdigest_address_id_fkey" FOREIGN KEY (address_id) REFERENCES address(id)
The below is the structure of *user* table:mailman=# \d user; Table "public.user" Column | Type | Collation | Nullable | Default -----------------------+-----------------------------+-----------+----------+---------------------------------- id | integer | | not null | nextval('user_id_seq'::regclass) display_name | character varying | | | password | character varying | | | _user_id | uuid | | | _created_on | timestamp without time zone | | | is_server_owner | boolean | | | _preferred_address_id | integer | | | preferences_id | integer | | | Indexes: "user_pkey" PRIMARY KEY, btree (id) "ix_user__user_id" btree (_user_id) "ix_user_preferences_id" btree (preferences_id) Foreign-key constraints: "_preferred_address" FOREIGN KEY (_preferred_address_id) REFERENCES address(id) ON DELETE SET NULL "user_preferences_id_fkey" FOREIGN KEY (preferences_id) REFERENCES preferences(id) Referenced by: TABLE "address" CONSTRAINT "address_user_id_fkey" FOREIGN KEY (user_id) REFERENCES "user"(id) TABLE "domain_owner" CONSTRAINT "domain_owner_user_id_fkey" FOREIGN KEY (user_id) REFERENCES "user"(id) TABLE "member" CONSTRAINT "member_user_id_fkey" FOREIGN KEY (user_id) REFERENCES "user"(id)
mailman=# \dn List of schemas Name | Owner --------+---------- public | postgres (1 row)
mailman=# select * from user; user
postgres (1 row)
*The above command output is not showing any fields. But if use the table as public.user (schema.tablename) with select command, it is showing the data. But without schema name this table is not showing those fields. Not sure if this the expected behavior or not.*
I have not done any changes. Few days back I made DR machine up as I am taking primary into maintenance. The entire database was running streaming replication and I even kept the /var/lib/mailman directory in sync where all the components are installed.
Please let me know if any more things I need to share.
On Mon, Jul 27, 2020 at 7:45 AM Mark Sapiro <mark@msapiro.net> wrote:
Hi Team,
I am currently running Mailman 3 with version 3.3.1 and in built
On 7/26/20 10:56 AM, Shashikanth Komandoor wrote: postfix
version 2.10.1-6 on RHEL 7.5 in production environment from the past 2 months almost.
From the past few days, I found my server is responding to the users very slowly and throwing the Server Error page (of course getting the required page after multiple refreshes) for almost every click on the postorius pages (not yet observed on hyperkitty front end which in fact is not so important for me as of now).
During that time, I observed in the mailman.log an error message saying "Could not locate column in row for column 'user.id'". The complete traceback log is attached with the mail. The error message is generated for almost all the lists and all the clicks.
I don't understand what's going on. I suspect some kind of database corruption, but I don't know what.
The traceback indicates Mailman is getting the Members of a list and in this process is trying to get the display_name for a member. The member's subscriber attribute doesn't have a display_name so it is trying to get is from the member's user attribute so it is trying to get the user record for the member's email address via IUserManager.get_user(). This in turn gets the Address record for the email address and returns it's user attribute.
This leads to the
sqlalchemy.exc.NoSuchColumnError: "Could not locate column in row for column 'user.id'"
error which I don't understand. There is no table with a 'user.id' column. user.id refers to the id column in the user table which comes from the user_id column in the address table which should be a reference to the id column in the user table, not a reference to ay 'user.id' column.
What is your backend database and what is the structure of the address table in that database. Have you done any kind of direct database query that might have affected this?
-- Mark Sapiro <mark@msapiro.net> The highway is for gamblers, San Francisco Bay Area, California better use your sense - B. Dylan
Mailman-users mailing list -- mailman-users@mailman3.org To unsubscribe send an email to mailman-users-leave@mailman3.org https://lists.mailman3.org/mailman3/lists/mailman-users.mailman3.org/
-- Thanks & Regards, Shashi Kanth.K 9052671936
On 7/27/20 2:48 AM, Shashikanth Komandoor wrote:
Hi Mark,
Thank you for your response. And sorry for not giving youcomplete information as I am not sue what to share with you.
I am using Postgresql-11.7 as my backend database. The below isthe structure of the address table in* mailman *database:
mailman=# \d address; Table "public.address" Column | Type | Collation | Nullable | Default ----------------+-----------------------------+-----------+----------+------------------------------------- id | integer | | not null | nextval('address_id_seq'::regclass) email | character varying | | | _original | character varying | | | display_name | character varying | | | verified_on | timestamp without time zone | | | registered_on | timestamp without time zone | | | user_id | integer | | | preferences_id | integer | | | Indexes: "address_pkey" PRIMARY KEY, btree (id) "ix_address_email" UNIQUE, btree (email) "ix_address_preferences_id" btree (preferences_id) "ix_address_user_id" btree (user_id) Foreign-key constraints: "address_preferences_id_fkey" FOREIGN KEY (preferences_id) REFERENCES preferences(id) "address_user_id_fkey" FOREIGN KEY (user_id) REFERENCES "user"(id) Referenced by: TABLE ""user"" CONSTRAINT "_preferred_address" FOREIGN KEY (_preferred_address_id) REFERENCES address(id) ON DELETE SET NULL TABLE "autoresponserecord" CONSTRAINT "autoresponserecord_address_id_fkey" FOREIGN KEY (address_id) REFERENCES address(id) TABLE "member" CONSTRAINT "member_address_id_fkey" FOREIGN KEY (address_id) REFERENCES address(id) TABLE "onelastdigest" CONSTRAINT "onelastdigest_address_id_fkey" FOREIGN KEY (address_id) REFERENCES address(id)
The below is the structure of *user* table:mailman=# \d user; Table "public.user" Column | Type | Collation | Nullable | Default -----------------------+-----------------------------+-----------+----------+---------------------------------- id | integer | | not null | nextval('user_id_seq'::regclass) display_name | character varying | | | password | character varying | | | _user_id | uuid | | | _created_on | timestamp without time zone | | | is_server_owner | boolean | | | _preferred_address_id | integer | | | preferences_id | integer | | | Indexes: "user_pkey" PRIMARY KEY, btree (id) "ix_user__user_id" btree (_user_id) "ix_user_preferences_id" btree (preferences_id) Foreign-key constraints: "_preferred_address" FOREIGN KEY (_preferred_address_id) REFERENCES address(id) ON DELETE SET NULL "user_preferences_id_fkey" FOREIGN KEY (preferences_id) REFERENCES preferences(id) Referenced by: TABLE "address" CONSTRAINT "address_user_id_fkey" FOREIGN KEY (user_id) REFERENCES "user"(id) TABLE "domain_owner" CONSTRAINT "domain_owner_user_id_fkey" FOREIGN KEY (user_id) REFERENCES "user"(id) TABLE "member" CONSTRAINT "member_user_id_fkey" FOREIGN KEY (user_id) REFERENCES "user"(id)
mailman=# \dn List of schemas Name | Owner --------+---------- public | postgres (1 row)
mailman=# select * from user; user
postgres (1 row)
*The above command output is not showing any fields. But if use the table as public.user (schema.tablename) with select command, it is showing the data. But without schema name this table is not showing those fields. Not sure if this the expected behavior or not.*
This is expected. psql gets confused because it has its own user table.
The above table definitions look fine.
Is there any entry in the address table for which id is not an integer?
-- Mark Sapiro <mark@msapiro.net> The highway is for gamblers, San Francisco Bay Area, California better use your sense - B. Dylan
Thank you Mark for your response.
I tried for the command to know if any entry in the "address" table for which 'id' is not an integer.
But unfortunately, I could not get any command or idea how to find that.
But out of my extra talent, I tried the below commands which, I thought, may print the records which contain a non integer id value.
*mailman=# select count(*) from address; count------- 82387(1 row)mailman=# select id,email from address where id NOT BETWEEN 1 AND 84203; id | email----+-------(0 rows)mailman=# select id,email from address where id NOT BETWEEN 1 AND 84202; id | email-------+----------------------------- 84203 | cons3.birmingham@mea.gov.in <cons3.birmingham@mea.gov.in>*
*1 and 84203 are the minimum and maximum values of the id:*
Please see the above outputs and suggest if anything possible or please let me know the command what should I run to find a non integer id value.
I also doubt this error as if my REST API is contacting postgresql in a delayed manner. If possible, can you say what optimal setting I can do at Postgresql so that faster responses would be given to REST API.
The above things are only my doubts. Please correct me if I am wrong.
On Tue, Jul 28, 2020 at 7:31 AM Mark Sapiro <mark@msapiro.net> wrote:
On 7/27/20 2:48 AM, Shashikanth Komandoor wrote:
Hi Mark,
Thank you for your response. And sorry for not giving youcomplete information as I am not sue what to share with you.
I am using Postgresql-11.7 as my backend database. The below isthe structure of the address table in* mailman *database:
mailman=# \d address; Table "public.address" Column | Type | Collation | Nullable | Default
----------------+-----------------------------+-----------+----------+-------------------------------------
id | integer | | not null | nextval('address_id_seq'::regclass) email | character varying | | | _original | character varying | | | display_name | character varying | | | verified_on | timestamp without time zone | | | registered_on | timestamp without time zone | | | user_id | integer | | | preferences_id | integer | | | Indexes: "address_pkey" PRIMARY KEY, btree (id) "ix_address_email" UNIQUE, btree (email) "ix_address_preferences_id" btree (preferences_id) "ix_address_user_id" btree (user_id) Foreign-key constraints: "address_preferences_id_fkey" FOREIGN KEY (preferences_id) REFERENCES preferences(id) "address_user_id_fkey" FOREIGN KEY (user_id) REFERENCES "user"(id) Referenced by: TABLE ""user"" CONSTRAINT "_preferred_address" FOREIGN KEY (_preferred_address_id) REFERENCES address(id) ON DELETE SET NULL TABLE "autoresponserecord" CONSTRAINT "autoresponserecord_address_id_fkey" FOREIGN KEY (address_id) REFERENCES address(id) TABLE "member" CONSTRAINT "member_address_id_fkey" FOREIGN KEY (address_id) REFERENCES address(id) TABLE "onelastdigest" CONSTRAINT "onelastdigest_address_id_fkey" FOREIGN KEY (address_id) REFERENCES address(id)
The below is the structure of *user* table:mailman=# \d user; Table "public.user" Column | Type | Collation | Nullable | Default
id | integer | | not null | nextval('user_id_seq'::regclass) display_name | character varying | | | password | character varying | | | _user_id | uuid | | | _created_on | timestamp without time zone | | | is_server_owner | boolean | | | _preferred_address_id | integer | | | preferences_id | integer | | | Indexes: "user_pkey" PRIMARY KEY, btree (id) "ix_user__user_id" btree (_user_id) "ix_user_preferences_id" btree (preferences_id) Foreign-key constraints: "_preferred_address" FOREIGN KEY (_preferred_address_id) REFERENCES address(id) ON DELETE SET NULL "user_preferences_id_fkey" FOREIGN KEY (preferences_id) REFERENCES preferences(id) Referenced by: TABLE "address" CONSTRAINT "address_user_id_fkey" FOREIGN KEY (user_id) REFERENCES "user"(id) TABLE "domain_owner" CONSTRAINT "domain_owner_user_id_fkey" FOREIGN KEY (user_id) REFERENCES "user"(id) TABLE "member" CONSTRAINT "member_user_id_fkey" FOREIGN KEY (user_id) REFERENCES "user"(id)
mailman=# \dn List of schemas Name | Owner --------+---------- public | postgres (1 row)
mailman=# select * from user; user
postgres (1 row)
*The above command output is not showing any fields. But if use the
-----------------------+-----------------------------+-----------+----------+---------------------------------- table
as public.user (schema.tablename) with select command, it is showing the data. But without schema name this table is not showing those fields. Not sure if this the expected behavior or not.*
This is expected. psql gets confused because it has its own user table.
The above table definitions look fine.
Is there any entry in the
addresstable for whichidis not an integer?-- Mark Sapiro <mark@msapiro.net> The highway is for gamblers, San Francisco Bay Area, California better use your sense - B. Dylan
Mailman-users mailing list -- mailman-users@mailman3.org To unsubscribe send an email to mailman-users-leave@mailman3.org https://lists.mailman3.org/mailman3/lists/mailman-users.mailman3.org/
-- Thanks & Regards, Shashi Kanth.K 9052671936
On 7/28/20 10:20 AM, Shashikanth Komandoor wrote:
I tried for the command to know if any entry in the "address" table for which 'id' is not an integer.
But unfortunately, I could not get any command or idea how to find that.
But out of my extra talent, I tried the below commands which, I thought, may print the records which contain a non integer id value.
...
select id,email from address where id NOT BETWEEN 1 AND 84203; id | email ----+-------(0 rows)
mailman=# select id,email from address where id NOT BETWEEN 1 AND 84202; id | emai> -------+-----------------------------> 84203 | user@exampl.com
*1 and 84203 are the minimum and maximum values of the id:*
Please see the above outputs and suggest if anything possible or please let me know the command what should I run to find a non integer id value.
The above looks qood to determine if there is a non-integer value.
I also doubt this error as if my REST API is contacting postgresql in a delayed manner. If possible, can you say what optimal setting I can do at Postgresql so that faster responses would be given to REST API.
I'm out of ideas.
-- Mark Sapiro <mark@msapiro.net> The highway is for gamblers, San Francisco Bay Area, California better use your sense - B. Dylan
participants (2)
-
Mark Sapiro -
Shashikanth Komandoor