Fix PostgreSQL error not null constraint violation gives me an wsod [#3175052]

Problem/Motivation

Trying to install varbase.

Steps to reproduce

Install varbase, using Drupal-VM with postgresql as the database. Enabled most of the extra components (all but SSO & Google).

Get an error:

An AJAX HTTP error occurred.
HTTP Result Code: 200
Debugging information follows.
Path: /core/install.php?rewrite=ok&profile=varbase&langcode=en&id=3&op=do_nojs&op=do
StatusText: OK
ResponseText: Drupal\Core\Entity\EntityStorageException: SQLSTATE[23502]: Not null violation: 7 ERROR:  null value in column "lid" violates not-null constraint
DETAIL:  Failing row contains (null, user, update, <none>, 1, webmaster, <em class="placeholder">webmaster</em> (uid <em class="placehold..., 0, 192.168.88.1, 1601936279).: INSERT INTO admin_audit_trail (lid, type, operation, description, created, uid, ip, path, ref_char, ref_numeric) VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1, :db_insert_placeholder_2, :db_insert_placeholder_3, :db_insert_placeholder_4, :db_insert_placeholder_5, :db_insert_placeholder_6, :db_insert_placeholder_7, :db_insert_placeholder_8, :db_insert_placeholder_9); Array
(
)
in Drupal\Core\Entity\Sql\SqlContentEntityStorage->save() (line 846 of /web/www/docroot/core/lib/Drupal/Core/Entity/Sql/SqlContentEntityStorage.php).

The apache error log includes:

[Mon Oct 05 21:31:09.127475 2020] [proxy_fcgi:error] [pid 46160:tid 140718043535104] [client 192.168.88.1:54880] AH01071: Got error 'PHP message: Drupal\\Core\\Entity\\EntityStorageException: SQLSTATE[23502]: Not null violation: 7 ERROR:  null value in column "lid" violates not-null constraint\nDETAIL:  Failing row contains (null, user, update, <none>, 1, webmaster, <em class="placeholder">webmaster</em> (uid <em class="placehold..., 0, 192.168.88.1, 1601933427).: INSERT INTO admin_audit_trail (lid, type, operation, description, created, uid, ip, path, ref_char, ref_numeric) VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1, :db_insert_placeholder_2, :db_insert_placeholder_3, :db_insert_placeholder_4, :db_insert_placeholder_5, :db_insert_placeholder_6, :db_insert_placeholder_7, :db_insert_placeholder_8, :db_insert_placeholder_9); Array\n(\n)\n in /web/www/docroot/core/lib/Drupal/Core/Entity/Sql/SqlContentEntityStorage.php on line 846 #0 /web/www/docroot/core/lib/Drupal/Core/Entity/EntityBase.php(395): Drupal\\Core\\Entity\\Sql\\SqlContentEntityStorage->save(Object(Drupal\\user\\Entity\\User))\n#1 /web/www/docroot/modules/contrib/varb...', referer: http://gerpeople.test/core/install.php?rewrite=ok&profile=varbase&langcode=en&id=3&op=start

When I try to visit the module's admin page, I get a bare error page and:

[Mon Oct 05 22:03:33.132977 2020] [proxy_fcgi:error] [pid 46159:tid 140717934429952] [client 192.168.88.1:55818] AH01071: Got error 'PHP message: Uncaught PHP Exception Drupal\\Core\\Database\\DatabaseExceptionWrapper: "SQLSTATE[42P01]: Undefined table: 7 ERROR:  relation "admin_audit_trail" does not exist\nLINE 5: admin_audit_trail e) subquery\n        ^: SELECT COUNT(*) AS expression\nFROM\n(SELECT 1 AS expression\nFROM\n{admin_audit_trail} e) subquery; Array\n(\n)\n" at /web/www/docroot/core/lib/Drupal/Core/Database/Connection.php line 701', referer: http://gerpeople.test/admin/help/admin_audit_trail

Proposed resolution

Remaining tasks

User interface changes

API changes

Data model changes

Comment	File	Size	Author
#25	Issue_3175052.patch	1.7 KB	rushikesh raval
#21	Screenshot 2023-09-30 at 17-57-23 Admin audit trails Test Website.png	319.4 KB	rushikesh raval
#10	3175052-01.patch	1.83 KB	juanp.rey.z

Issue fork admin_audit_trail-3175052

Show commands

Start within a Git clone of the project using the version control instructions.

Add & fetch this issue fork’s repository

Or, if you do not have SSH keys set up on git.drupalcode.org:

Add & fetch this issue fork’s repository

1.0.x changes, plain diff MR !11
Check out this branch for the first time

Check out existing branch, if you already have it locally
3175052-fix-postgresql-error changes, plain diff MR !2
Check out this branch for the first time

Check out existing branch, if you already have it locally

About issue forks

Comments

Comment #1

5 October 2020 at 22:10

rivimey created an issue. See original summary.

Comment #2

rivimey

she/her

commented 5 October 2020 at 22:22

Issue summary:

View changes

Just to add: retried but not enabling any of the extra components... no change in the bug.

Ubuntu is 20.04; PHP is 7.4; postgresql is v12 ubuntu.

Comment #3

alfakom commented 7 October 2020 at 07:42

I had the same error: Debian 9 / PHP 7.3.19-1 / Postgresql 11.9

Comment #4

rajab natshah

he/him

commented 7 October 2020 at 08:00

Title:	Varbase profile: error trying to insert NULL Lid	» [PostgreSQL] Varbase profile: error trying to insert NULL Lid
Issue tags:		+PostgreSQL, +PHP 7.3, +PHP 7.4

Comment #5

rivimey

she/her

commented 9 October 2020 at 12:02

It would help if the module enabled a pgsql and sqlite test in the Automated Tests section.

Comment #6

rajab natshah

he/him

commented 10 October 2020 at 09:50

Thank you Ruth for reporting and following.

Good Idea about the Automated Tests
Enabling the Automated Tests config options for DrupalCI

Comment #7

rajab natshah

he/him

commented 10 October 2020 at 10:01

Activated

PHP 7.4 & MySQL 5.7, D9.0.6
PHP 7.3 & MySQL 5.7, D8.9
PHP 7.4 & MySQL 5.7, D9.1
PHP 7.3 & PostgreSQL 12.1, D8.9.6
PHP 7.4 & PostgreSQL 9.5, D9.0.6
PHP 7.3 & SQLite 3.28, D8.9
PHP 7.4 & SQLite 3.27, D9.0.6

https://www.drupal.org/node/3152933/qa

Any patch will be very much welcomed. To run the automated tests and have a green build and tests in all options

Comment #8

rivimey

she/her

commented 11 October 2020 at 20:30

Hi,
I have had another look through the source, and suspect the culprit is the following statement from the main .module file:

  \Drupal::database()->merge('admin_audit_trail')
    ->key('lid')
    ->fields([
      'type' => $log['type'],
      'operation' => $log['operation'],
      'description' => $log['description'],
      'created' => $log['created'],
      'uid' => $log['uid'],
      'ip' => $log['ip'],
      'path' => $log['path'],
      'ref_char' => $log['ref_char'],
      'ref_numeric' => $log['ref_numeric'],
    ])
    ->execute();

Invoking a merge requires that the key fields are set in the provided data, as otherwise a merge cannot happen. In this case, 'lid' is the key field but has no value.

I don't see why a merge is needed in this case: even the function docs say 'insert'. So I suggest this code is altered to a simple insert.

Re Tests, I hadn't previously noted that there are in fact no tests specified for this module. Setting that up would be very beneficial, but is outside the scope of this bug-fix.

Comment #9

rivimey

she/her

commented 11 October 2020 at 20:35

This issue is of course a duplicate of #3094617: Postgres error not null constraint violation gives me an wsod

which states pretty much exactly the same problem, suggested solution, and suggested patch.

Comment #10

juanp.rey.z commented 23 February 2021 at 00:03

Status	File	Size
new	3175052-01.patch	1.83 KB

Let´s check this patch according to rivimey #9.

Comment #11

rajab natshah

he/him

commented 28 August 2021 at 09:25

Title:

[PostgreSQL] Varbase profile: error trying to insert NULL Lid

» Fix PostgreSQL error not null constraint violation gives me an wsod

Comment #12

rajab natshah

he/him

commented 28 August 2021 at 13:39

Status:

Active

» Needs review

Comment #13

rivimey

she/her

commented 28 August 2021 at 21:56

Juan, long time but here's a review for that patch.

```
+++ b/admin_audit_trail.module
@@ -88,21 +88,37 @@ function admin_audit_trail_insert(array &$log) {
+  if (is_null($log['lid']) || empty($log['lid'])) {
```
is_null() is a subset of empty(), so only one of them is required here.

However empty("0") is True, so it might be best to use !isset(), which tests for non-existence and NULLness but not FALSE or 0.

+++ b/admin_audit_trail.module
@@ -88,21 +88,37 @@ function admin_audit_trail_insert(array &$log) {
+        ->fields(array(

Shouldn't be changing [] into array(), twice

+++ b/admin_audit_trail.module
@@ -88,21 +88,37 @@ function admin_audit_trail_insert(array &$log) {
+            'type' => $log['type'],
+            'operation' => $log['operation'],
+            'description' => $log['description'],
+            'created' => $log['created'],
+            'uid' => $log['uid'],
+            'ip' => $log['ip'],
+            'path' => $log['path'],
+            'ref_char' => $log['ref_char'],
+            'ref_numeric' => $log['ref_numeric'],

Create a named array just before the 'if' with this content, and then use it.

Comment #14

13 December 2021 at 21:39

arg-paul made their first commit to this issue’s fork.

Comment #15

13 December 2021 at 21:46

arg-paul opened merge request !2

Comment #16

orkutmuratyilmaz

Turkish

İzmir

commented 24 October 2022 at 12:32

Any updates on this issue?

Comment #17

rivimey

she/her

commented 24 October 2022 at 15:02

arg-paul's branch change looks fine to me (https://git.drupalcode.org/project/admin_audit_trail/-/merge_requests/2....) (RTBC), but needs updating as it can no longer be committed.

Probably updating the base branch will do.

Comment #18

orkutmuratyilmaz

Turkish

İzmir

commented 11 November 2022 at 12:03

@arg-paul, can you update the base branch you've forked?

Comment #19

30 September 2023 at 12:09

rushiraval made their first commit to this issue’s fork.

Comment #20

30 September 2023 at 12:10

rushiraval opened merge request !11

Comment #21

rushikesh raval commented 30 September 2023 at 12:28

Status:

Needs review

» Reviewed & tested by the community

Status	File	Size
new	Screenshot 2023-09-30 at 17-57-23 Admin audit trails Test Website.png	319.4 KB

Comment #22

rajab natshah

he/him

commented 30 September 2023 at 15:20

Noted; to be re-tested for sure.
with PHP 8.1 and PHP 8.2

Comment #23

rajab natshah

he/him

commented 2 October 2023 at 20:12

Version:	1.0.0-alpha1	» 1.0.x-dev
Issue tags:		+PHP 8.0, +PHP 8.1

Comment #24

rajab natshah

he/him

commented 2 October 2023 at 20:21

Status:

Reviewed & tested by the community

» Needs work

Latest fix by Rushikesh, looks good.
Only the issue fork needs to be updated to latest code from the 1.0.x branch.
Or it could be uploaded as a patch for the 1.0.x branch, not the tag release.
Having one more testing round.

Comment #25

rushikesh raval commented 4 October 2023 at 08:13

Status	File	Size
new	Issue_3175052.patch	1.7 KB

@Rajab Natsha code is tested on PHP8.2 and postgresql.
I am not able to update fork, its giving error.

The upstream changes could not be synchronized to this project due to file conflicts in the default branch.

I think you may be able update fork from your side, I have attached patch herewith.

Comment #26

rajab natshah

he/him

commented 4 October 2023 at 08:35

Issue tags:

+PHP 8.2

I like your fix Rushikesh.

Committing your patch and having a follow up release.

Comment #27

4 October 2023 at 09:16

Rajab Natshah committed 995519b8 on 1.0.x authored by rushiraval

Issue #3175052 by rushiraval: Fix PostgreSQL error not null constraint...

Comment #28

rajab natshah

he/him

commented 4 October 2023 at 09:21

Assigned:	Unassigned	» rajab natshah
Status:	Needs work	» Needs review

Comment #29

rajab natshah

he/him

commented 4 October 2023 at 09:35

Assigned:	rajab natshah	» Unassigned
Status:	Needs review	» Fixed

Comment #30

orkutmuratyilmaz

Turkish

İzmir

commented 4 October 2023 at 12:08

@Rajab Natshah, thanks for the fix:)

Comment #31

rajab natshah

he/him

commented 4 October 2023 at 13:45

Issue tags:

+admin_audit_trail-1.0.2

Comment #32

rajab natshah

he/him

commented 4 October 2023 at 13:57

✅ Released admin_audit_trail-1.0.2

Comment #33

18 October 2023 at 13:59

Status:

Fixed

» Closed (fixed)

Automatically closed - issue fixed for 2 weeks with no activity.

Fix PostgreSQL error not null constraint violation gives me an wsod

Problem/Motivation

Steps to reproduce

Proposed resolution

Remaining tasks

User interface changes

API changes

Data model changes

Issue fork admin_audit_trail-3175052

Comments

Referenced by