Can't create new Volunteer Project on initial install

[MattTrim1]

I am performing the following steps from the documentation: https://docs.civicrm.org/volunteer/en/latest/projects/#stand-alone

New stand-alone project

A "stand-alone" project is not associated with an event, and thus can be ongoing. Create one as follows:
    Choose Volunteer > New Volunteer Project.
    Fill out the settings and click Save.

When I submit the settings form, I receive an on-screen error message:

A technical problem has occurred
Your submission was not saved. Resubmitting the form is unlikely to resolve this problem. Please contact a system administrator.

Looking at the logs, it appears that the technical error message being generated is (output response shortened for brevity):

"error_message": "DB Error: unknown error",
"debug_info": "INSERT INTO `civicrm_volunteer_project` (`title` , `description` , `is_active` , `loc_block_id` , `campaign_id` ) VALUES ('Test' , 'Test' ,  1 ,  NULL ,  NULL )  [nativecode=1364 ** Field 'entity_table' doesn't have a default value]"

Looking at the schema file for a Project I can see that entity_table and entity_id are required fields. I would be able to submit a PR that changes the required value on these fields on these fields to false and regens the entity boilerplate if this is deemed an appropriate solution. It's worked in my testing and allows me to create a new Standalone Project.

We may also need an Upgrader script that removes the "NOT NULL" from existing installs.

[ginkgomzd]

What is the origin of your install?

The installer initially creates the volunteer project table with NOT NULL, but an upgrade step for 2.0 makes those fields nullable.
https://github.com/civicrm/org.civicrm.volunteer/blob/1a0edeab91bd2edd3cd63a0c602fc28c0aeb2c3c/sql/volunteer_upgrade_2.0.sql

Curious if you can fully replicate the scenario to see if there is an install or upgrade edge-case that should be addressed.

Thanks for reporting!

[MattTrim1]

Hi @ginkgomzd - thanks for the response!

The issue has presented on the following environment:

• Newly created Buildkit development instance
• Civi version 5.47.1
• D9 latest

I don't think this is an edge case.

The full recreation steps are:

• Create new Civi instance
• Install and enable Angular Profiles and this extension, either via the UI or via git clone
• Navigate to "Volunteers -> New Volunteer Project"
• Fill in the fields in the form
• Submit form
• Receive error message

SHOW CREATE TABLE civicrm_volunteer_project shows:

Create Table: CREATE TABLE `civicrm_volunteer_project` (
   `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Project Id',
   `title` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'The
title of the Volunteer Project',
   `description` text COLLATE utf8mb4_unicode_ci COMMENT 'Full
description of the Volunteer Project. Text and HTML allowed. Displayed
on sign-up screens.',
   `entity_table` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL
COMMENT 'Entity table for entity_id (initially civicrm_event)',
   `entity_id` int(10) unsigned NOT NULL COMMENT 'Implicit FK project
entity (initially eventID).',
   `is_active` tinyint(4) NOT NULL DEFAULT '1' COMMENT 'Is this need
enabled?',
   `loc_block_id` int(10) unsigned DEFAULT NULL COMMENT 'FK to Location
Block ID',
   `campaign_id` int(10) unsigned DEFAULT NULL COMMENT 'The campaign
associated with this Volunteer Project.',
   PRIMARY KEY (`id`),
   KEY `FK_civicrm_volunteer_project_loc_block_id` (`loc_block_id`),
   KEY `FK_civicrm_volunteer_project_campaign_id` (`campaign_id`),
   CONSTRAINT `FK_civicrm_volunteer_project_campaign_id` FOREIGN KEY
(`campaign_id`) REFERENCES `civicrm_campaign` (`id`) ON DELETE SET NULL,
   CONSTRAINT `FK_civicrm_volunteer_project_loc_block_id` FOREIGN KEY
(`loc_block_id`) REFERENCES `civicrm_loc_block` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

Specifically noting:

`entity_table` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL 'Entity table for entity_id (initially civicrm_event)',
`entity_id` int(10) unsigned NOT NULL COMMENT 'Implicit FK project entity (initially eventID).',

So it looks like the upgrader step you've referred doesn't get executed on an initial install of the extension, only upgrades of existing installations. My system does not offer any database upgrades for the extension.

My thoughts are that we need to implement a change to the schema XML files and regenerate entity boilerplate code, to prevent new installs of this extension experiencing this issue. Any upgrades from prior versions should be handled by the upgrade file you refer to.

What do you think?

Cheers!

[ginkgomzd]

I think you are hitting the issue resolved in v2.4.3:
#563

2.4.1-2 were missing the schemaUpgrade20() step.

[MattTrim1]

If that's the case, I don't understand how this happens when I clone master.

[ginkgomzd]

indeed, that is the million dollar question.
Are you game for putting some extra logging in the installer to confirm if that step is running?
https://github.com/civicrm/org.civicrm.volunteer/blob/master/CRM/Volunteer/Upgrader.php#L193

Ah, hmmm, is it possible that check for target_contact_id is short-circuiting it?
... no, I don't think that could happen. Unless... is it possible you un-installed and re-installed the extension? There is evidence that the uninstaller is not clean enough.

I appreciate your troubleshooting on this.

[gvigneron]

First of all I confirm that the problem is not solved in v2.4.3: the bug is present on new installations (v2.4.3 - Wordpress).

I have tested uninstalling and reinstalling the extension, it does not change anything. The entity_table and entity_id fields are NOT NULL in DB.

The extension being not usable, I tried a workaround while waiting for a bug fix:
I ran the following part of the volunteer_upgrade_2.0.sql script by hand:

ALTER TABLE `civicrm_volunteer_project`
MODIFY `entity_table` varchar(64) NULL,
MODIFY `entity_id` int(10) NULL;

which seems to works for now

[nasiralamreeki]

@ginkgomzd this is blocking our adoption of CiviCRM as CiviVolunteer is the extension we will be primarily using

[ginkgomzd]

Hi @nasiralamreeki ,
As you can see in the above discussion... it is not clear how to reproduce, and there is a work-around in the last comment before yours. Did that not work for you? Do you have the skills to troubleshoot. If so, perhaps you could add logging as I suggest above.
Cheers

[irsbugs]

I have experienced this CiviVolunteer (2.4.4) issue observed by @MattTrim1 on a new installation of Wordpress (6.2.2) and CiviCRM (5.62.1) on three Ubuntu 22.04 systems. Two were LAMP one was LEMP.

I implemented the workaround provided by @gvigneron and no longer get the error.

I looking through the source, I'm wondering if the following changes may resolve this issue:

In the file: https://github.com/civicrm/org.civicrm.volunteer/blob/master/sql/auto_install.sql

Change (lines 40 and 41)...

  `entity_table` varchar(64) NOT NULL COMMENT 'Entity table for entity_id (initially civicrm_event)',
  `entity_id` int unsigned NOT NULL COMMENT 'Implicit FK project entity (initially eventID).',

To...

`entity_table` varchar(64) NULL COMMENT 'Entity table for entity_id (initially civicrm_event)',
 `entity_id` int unsigned NULL COMMENT 'Implicit FK project entity (initially eventID).',

Regards.

[ginkgomzd]

@irsbugs do you still have a setup where it is convenient to reproduce this?
I have long had doubts about the installer/uninstaller and simply changing the schema like that might break other things. I think the whole installer needs to be refactored.

It would be a big help if you can put tracing into the installer and verify that each step is succeeding.

[irsbugs]

@ginkgomzd

do you still have a setup where it is convenient to reproduce this?

Yes - see below.

It would be a big help if you can put tracing into the installer and verify that each step is succeeding.

I don't know how to put tracing into the installer. Maybe you could e-mail me with instructions on how to do this.

Today, as a way of testing my hypothesis that the issue is in the CiviVolunteer sql/auto_install.sql file, I did the following:

1. Downloaded CiviVolunteer 2.4.4 from https://civicrm.org/extensions/civivolunteer

2. Unzipped org.civicrm.volunteer-2.4.4.zip

3. Edited lines 40 and 41 in sql/auto_install.sql. i.e. NOT NULL was changed to NULL...

...original...

  `entity_table` varchar(64) NOT NULL COMMENT 'Entity table for entity_id (initially civicrm_event)',
  `entity_id` int unsigned NOT NULL COMMENT 'Implicit FK project entity (initially eventID).',

...patched to...

  `entity_table` varchar(64) NULL COMMENT 'Entity table for entity_id (initially civicrm_event)',
  `entity_id` int unsigned NULL COMMENT 'Implicit FK project entity (initially eventID).',

4. Zipped back up the folder and files as org.civicrm.volunteer-2.4.4-patched.zip archive, while also keeping the original downloaded org.civicrm.volunteer-2.4.4.zip archive.

5. Installed into a PC an empty SSD.

6. Installed onto the SSD: Ubuntu-Mate 22.04, php 8.1, mysql 8.0.33, WordPress 6.2.2, CiviCRM 5.62.1, Angular Profiles 1.2.2.

7. Performed the manual installation of native extension described here...
   https://docs.civicrm.org/sysadmin/en/latest/customize/extensions/#installing-a-new-extension

8. Installation of the original CiviVolunteer zip archive was done three times, and each time creating a New Volunteer Project failed with: A technical problem has occurred. Your submission was not saved. Resubmitting the form is unlikely to resolve this problem. Please contact a system administrator.

9. The above installations were alternated with three installations of the patched CiviVolunteer zip archive. Each time creating a New Volunteer Project was performed then it would save with the message: Saved. Changes saved successfully.

In summary, on the platform that I used, the two edits performed to sql/auto_install.sql, appear to resolve the issue.

I'll attach my console log, which is interspersed with statements of what I'm also doing on the browser.

FYI: I also encountered this CiviVolunteer issue with a system using nginx 1.18.0 and MariaDB 10.6.12

Regards, Ian.
CiviVolunteer testing patch to auto_install.sql.txt

[ginkgomzd]

@irsbugs I greatly appreciate the offer to test.
I have no doubt that changing the schema resolves the error. However, I think it leaves a sleeping bug in the installer (or UNinstaller).
Your account lends credence to my theory that this happens to systems where CiviVolunteer was uninstalled and then reinstalled.
Are you able to reproduce the issue on a completely clean install of CiviCRM and the tagged release of CiviVolunteer?

[irsbugs]

@ginkgomzd

Are you able to reproduce the issue on a completely clean install of CiviCRM and the tagged release of CiviVolunteer?

I'm sorry, but I don't know what you mean by the "tagged release" of CiviVolunteer.

To summarise what I've done, so far:

On three separate PC's I've performed clean installs of Ubuntu, Wordpress and CiviCRM. In two cases I used Apache2 and MySQL, in the other case I used nginx and MariaDB. In all three cases I then used the CiviCRM Administer -> System Settings -> Extension and used the "Add New" and "Extension" tabs in performing the download and installation of Angular Profiles 1.2.2 and CiviVolunteer 2.4.4. After completing these installations I then experienced the issue when performing a "Save and Done" in Volunteers -> New Volunteer Project.

I also used an AWS ARM arch t4g.small instance with Ubuntu, Apache2, and MySQL Wordpress, CiviCRM and installed Angular Profiles 1.2.2 and CiviVolunteer 2.4.4. This also experienced the issue.

In all the above cases I was able to successfully save a new CiviVolunteer project by applying the @gvigneron workaround.

Regarding...

Your account lends credence to my theory that this happens to systems where CiviVolunteer was uninstalled and then reinstalled.

...note that in my above four cases CiviVolunteer was not uninstalled and then reinstalled. It was only installed.

Yesterday I used one of my PC's and a clean SSD to install Ubuntu, Wordpress, CiviCRM, Apache2, MySQL, and Angular Profiles. I then manually installed CiviVolunteer original version and my patched version. In three cases the original version failed and in three cases the patched version saved a New Volunteer Project OK.

If you would like to provide me with a "tagged release" of CiviVolunteer as a zip file, then I'd be happy to do a manual install on a clean system and test it out.

cheers, Ian.

[larssandergreen]

I'm seeing what looks like the same issue on dmaster and locally with the same set up (master on D7). Not doing anything other than trying to create a project.

To replicate

1. New Volunteer Project
2. Fill in required fields (Title and Beneficiary)
3. Click Save or Continue
4. Result is this error:

A technical problem has occurred
Your submission was not saved. Resubmitting the form is unlikely to resolve this problem. Please contact a system administrator.

Immediately on page load I'm seeing the following in the console:

angular.js:15697 TypeError: Cannot read properties of undefined (reading 'id')
    at angular-modules.1723f1a92053d26973e1641923c28baa.js?rxigs1:2154:66
    at m.$digest (angular.js:19270:23)
    at angular.js:19562:15
    at Yg.completeTask (angular.js:21403:7)
    at angular.js:6879:7

On clicking Save or Continue, I get:
api.VolunteerProject.create failed with the following message: DB Error: unknown error

[larssandergreen]

Looking into the details a bit, it looks like $scope.project is being passed to the API, but it does not contain two required fields: entity_table and entity_id — the same issue as @irsbugs has identified above.

If these fields shouldn't actually be required, then that should be fixed in the XML here and the sql files can be regenerated from that. But I don't know enough about CiviVolunteer to say if that might have knock-on effects elsewhere.

I can submit a PR with this change if required.

[larssandergreen]

This should be fine to do, updating the XML was just missed in the past. Here's a PR for a start (doing what @MattTrim1 proposed on opening this issue), but some work will be required to upgrade the extension with civix so that the sql and DAO can be regenerated.

It's also possible that other steps included in volunteer_upgrade_2.0.sql are needed, but I hope not!

[larssandergreen]

My PR is now ready to go.

[Upperholme]

Any progress on this? The extension is essentially broken on a clean install which doesn't inspire confidence. Many users won't get as far as looking at this page.

[nickperkins]

I have a fresh install of CiviVolunteer on a Drupal 10 site and I have hit this issue.

[broswell]

I am having the same issue Wordpress (6.7.1), CiviCRM 5.79.0
Any help getting started would be appreciated?
https://museum.syssrc.com 410-771-5544 x 4336

[broswell]

using the data earlier in the thread, I fixed

mysql> alter table civicrm_volunteer_project modify entity_id int unsigned NULL;
Query OK, 0 rows affected (0.17 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> alter table civicrm_volunteer_project modify entity_table varchar(64) NULL;
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0