Opportunities date created is empty

SuiteCRM Forum - English Language SuiteCRM General Discussion
1

We have just discovered that a number of opportunities do not have a ‘date created’ attached to the record. I can see that the ‘Date Entered’ field in the database is NULL.

The field type is ‘DATETIME’ and the default shows as NULL. I have tried modifying the table so the default is ‘CURRENT TIMESTAMP’ but when you repair the database in SuiteCRM it reverts back to default ‘NULL’

Many entries have a date created but lately for all opportunities this field is NULL

Any ideas as to why this should suddenly have happened? There have not been any updates or changes to the database that I am aware of.

How do we fix this?

We are running Suite 7.8.8 on a linux server.

Thank you

2

Has anybody experienced this issue before? I’d be grateful for any guidance.

Thanks

3

How did you modify the table? If you edited files, please tell me the exact locations and all the changes you tried.

4

Hi,

The only thing I tried was to change the table attributes from Null to current timestamp from phpmyadmin. I then rebuilt the database. Upon inspection the attribute had reverted back to ‘Null’ and the date created field in all opportunities remains empty

I don’t know what has happened but the date created field only started showing a blank a couple of months ago.

5

You shouldn’t change database structure directly. You are right that a QR&R will sync the database with the “vardefs”, the PHP files that describe the database structure in SuiteCRM. You shouldn’t try to mess with this manually…

We should focus on why the Opps are saved without a date. If there is a bug, and it hasn’t been fixed yet in the newest versions, then that’s what we should be trying to figure out. Can you find some pattern of which ones don’t get a date? Can you try different things and find out a fixed “steps to reproduce” that makes the bug appear?

6

Thank you for your help @pgr.

I have found an entry in the log that appears after you add an opportunity which I wonder if you could advise on.

These are custom fields we have added to the opportunity and have worked fine until recently. It seems to be complaining about the primary key.

I am also seeing that any selections made from a ‘multi-select’ field insert a caret (^) symbol in to the database. How do we stop this from happening?

Thank you

Query Failed: INSERT INTO opportunities_cstm (id_c ,previously_outsourced_c ,proposal_date_c ,needs_identified_check_c ,jjwg_maps_lat_c ,solution_presented_date_c ,service_type_c ,will_outsource_c ,no_of_positions_c ,closed_lost_date_c ,proposal_sent_check_c ,closed_won_date_c ,total_billing_amount_c ,billing_amount_c ,csa_sent_check_c ,csa_sent_date_c ,meeting_booked_check_c ,activity_so_far_c ,closed_won_check_c ,division_c ,needs_identified_date_c ,jjwg_maps_lng_c ,needs_position_filled_by_c ,closed_lost_check_c ,meeting_booked_date_c ,solution_presented_check_c ,reason_for_recruitment_c ,issues_faced_c ,clear_amount_c ) VALUES (‘8aecf1db-0794-e167-a3f9-5d937158e46f’ ,‘Yes’ ,NULL ,‘0’ ,0 ,NULL ,’^Benchmarking^’ ,‘Yes’ ,NULL ,NULL ,‘0’ ,NULL ,0 ,0 ,‘0’ ,NULL ,‘0’ ,‘nn’ ,‘0’ ,‘Performance’ ,NULL ,0 ,‘2019-10-15’ ,‘0’ ,NULL ,‘0’ ,‘Growth New Position’ ,‘nn’ ,0 ): MySQL error 1062: Duplicate entry ‘8aecf1db-0794-e167-a3f9-5d937158e46f’ for key ‘PRIMARY’

7

If you raise your log level to debug, can you please check if there are other “INSERT INTO opportunities_cstm” commands happening?

I am trying to figure out why the duplication is happening - is it the same command being given twice? By the way, this command is coming from core code, right? Or do you have any customizations (like logic_hooks) creating rows in the database?

About the caret character, that’s how SuiteCRM keeps track of the multiple concatenated values that are selected. But then the UI interprets that and shows the multiselect like it should. So you shouldn’t worry, and you shouldn’t change it.

8

Hi

As far as I am aware this is coming from the core code although we did have a specific module written for us for our recruitment processes so I do not know if there are any logic hooks creating rows in the database caused by this module.

Attached is a selection from the log file after turning the log level to debug.

Thanks for your help with this.

9

You should definitely have a look at those logic hooks:

Sun Oct 6 13:57:47 2019 [167023][1][DEBUG] Creating new instance of hook class KPI_Hooks without parameters

Sun Oct 6 13:57:47 2019 [167023][1][DEBUG] Creating new instance of hook class SugarChimp_LogicHook without parameters

Sun Oct 6 13:57:47 2019 [167023][1][INFO] Query:INSERT INTO opportunities_cstm (id_c
,closed_won_date_c ,division_c ,closed_won_check_c ,needs_identified_date_c
,meeting_booked_check_c ,closed_lost_date_c ,activity_so_far_c
,proposal_sent_check_c ,needs_identified_check_c ,service_type_c
,csa_sent_check_c ,clear_amount_c ,reason_for_recruitment_c ,no_of_positions_c
,meeting_booked_date_c ,total_billing_amount_c ,previously_outsourced_c
,closed_lost_check_c ,will_outsource_c ,csa_sent_date_c ,needs_position_filled_by_c
,jjwg_maps_lng_c ,issues_faced_c ,billing_amount_c ,jjwg_maps_lat_c
,solution_presented_check_c ,proposal_date_c ,solution_presented_date_c ) VALUES
('95b6f286-c880-f1e5-c74b-5d9a2b7000fd' ,NULL ,'Performance' ,'0' ,NULL ,'0' ,NULL ,'no
info' ,'0' ,'0' ,'^Executive Recruitment^' ,'0' ,0 ,'Succession Planning' ,NULL ,NULL ,0 ,'No' ,'0'
,'Yes' ,NULL ,NULL ,0 ,'no info' ,0 ,0 ,'0' ,NULL ,NULL )

Sun Oct 6 13:57:47 2019 [167023][1][FATAL] Mysqli_query failed.
Sun Oct 6 13:57:47 2019 [167023][1][INFO] Query Execution Time:0.00034594535827637
Sun Oct 6 13:57:47 2019 [167023][1][FATAL] Query Failed: INSERT INTO
opportunities_cstm (id_c ,closed_won_date_c ,division_c ,closed_won_check_c
,needs_identified_date_c ,meeting_booked_check_c ,closed_lost_date_c
,activity_so_far_c ,proposal_sent_check_c ,needs_identified_check_c ,service_type_c
,csa_sent_check_c ,clear_amount_c ,reason_for_recruitment_c ,no_of_positions_c
,meeting_booked_date_c ,total_billing_amount_c ,previously_outsourced_c,closed_lost_check_c ,will_outsource_c ,csa_sent_date_c ,needs_position_filled_by_c
,jjwg_maps_lng_c ,issues_faced_c ,billing_amount_c ,jjwg_maps_lat_c
,solution_presented_check_c ,proposal_date_c ,solution_presented_date_c ) VALUES
('95b6f286-c880-f1e5-c74b-5d9a2b7000fd' ,NULL ,'Performance' ,'0' ,NULL ,'0' ,NULL ,'no
info' ,'0' ,'0' ,'^Executive Recruitment^' ,'0' ,0 ,'Succession Planning' ,NULL ,NULL ,0 ,'No' ,'0'
,'Yes' ,NULL ,NULL ,0 ,'no info' ,0 ,0 ,'0' ,NULL ,NULL ): MySQL error 1062: Duplicate entry
'95b6f286-c880-f1e5-c74b-5d9a2b7000fd' for key 'PRIMARY'

Just as I thought, the code is first saving the Opportunity custom fields, and then fails when it tries to save the second time, with the same id as before. that second either should not exist, or it should be an UPDATE, instead of an INSERT.

I don’t think that core code would save just to the custom table, without saving the main table. I don’t know where your hooks are defined, but they shouldn’t be too hard to find, under “custom” dir.