search

salesagility / SuiteCRM

SuiteCRM - Open source CRM for the world
https://www.suitecrm.com
GNU Affero General Public License v3.0
4.39k stars 2.06k forks source link

Cannot create email campaigns on SuiteCRM using MySQL 5.7 #1719

Closed cumanacr closed 7 years ago

cumanacr commented 8 years ago

Cannot create emails campaigns on SuiteCRM 7.6.5

Issue

Clicking on Create Campaign button -> Email Button -> Next Button, with name field filled doesn't create record on campaigns table causing that the rest of the process fail.

Link: http://localhost/{your-instance}/index.php?module=Campaigns&action=WizardHome&return_module=Campaigns&return_action=index

suitecrm.log entry:

Fri Jul  8 18:40:13 2016 [356][1][FATAL] Error inserting into table: campaigns: Query Failed: INSERT INTO campaigns (id,name,date_entered,date_modified,modified_user_id,created_by,deleted,assigned_user_id,tracker_count,refer_url,end_date,status,impressions,currency_id,campaign_type,frequency)
                                        VALUES ('a8004221-8f37-21c8-23b8-5780473df32f','TEST','2016-07-09 00:40:13','2016-07-09 00:40:13','1','1',0,'1',0,'http://','0000-00-00','Planning',0,'-99','Email',''): MySQL error 1292: Incorrect date value: '0000-00-00' for column 'end_date' at row 1
Fri Jul  8 18:40:13 2016 [356][1][FATAL] Exception in Controller: Database failure. Please refer to suitecrm.log for details.

Expected Behavior

Be able to complete Campaing creation helper steps. Campaign Header -> Target Lists -> Templates -> Marketing -> Send Email and Summary

Actual Behavior

Clicking next on Target Lists steps gives a blank screen and process ends.

Possible Fix

1- Fix incorrect date value: '0000-00-00' to end_date field on record insertion. 2- Add field end_date on editview in the step one.

Steps to Reproduce

  1. http://localhost/{your-instance}/index.php?module=Campaigns&action=WizardHome&return_module=Campaigns&return_action=index
  2. Click on Create Campaign button
  3. Click on Email button
  4. Fill name field
  5. Click on next button
  6. Create or select target list
  7. Click on next button
  8. Blank screen and unable to create campaign

    Context

Cannot create new email campaigns on SuiteCRM 7.6.5 This is a critical high priority bug IMHO

Your Environment

samus-aran commented 8 years ago

@cumanacr Did refreshing your browser cache and/or repair and rebuild help? The campaigns wizard does use new js libraries.

If not, where did you download to install 7.6.5 or was it an upgrade from what version?

cumanacr commented 8 years ago

I did as you tell me, but it didn't work, after a whole machine restart.

I git cloned, then installed.

Regards,

cumanacr commented 8 years ago

Hello @samus-aran, @willrennie,

I'm installed a fresh SuiteCRM 7.7.1 instance and the problem to create new campaigns due to incorrect date value: '0000-00-00' to end_date field on record insertion still exists.

But now the Chrome's console log show this message:

Error getting record id wizard.js?v=WF6J8rcchhuZhzrhk98ddA:63

The code on that line is:

$.post($('#wizform').attr('action'),$('#wizform').serialize(),function(data){var re=/{"record":"\w{1,}-\w{1,}-\w{1,}-\w{1,}-\w{1,}"}/g;var found=data.match(re);if(found==null){console.log('Error getting record id');}else{var response=jQuery.parseJSON(found[0]);$('input[name="record"]').val(response.record);$('input[name="campaign_id"]').val(response.record);$('input[name="action"]').val('WizardTargetListSave');}});}

Again if I search the insert statement in mysql queries log:

INSERT INTO campaigns (id,name,date_entered,date_modified,modified_user_id,created_by,deleted,assigned_user_id,tracker_count,refer_url,end_date,status,impressions,currency_id,campaign_type,frequency) VALUES ('c5170602-39ce-016b-bcdc-57acfbcb3316','Test','2016-08-11 22:26:33','2016-08-11 22:26:33','1','1',0,'1',0,'http://','0000-00-00','Planning',0,'-99','Email','');

and changed it to:

INSERT INTO campaigns (id,name,date_entered,date_modified,modified_user_id,created_by,deleted,assigned_user_id,tracker_count,refer_url,end_date,status,impressions,currency_id,campaign_type,frequency) VALUES ('c5170602-39ce-016b-bcdc-57acfbcb3316','Test','2016-08-11 22:26:33','2016-08-11 22:26:33','1','1',0,'1',0,'http://','2016-08-11','Planning',0,'-99','Email','');

After that I can insert the record manually and then re-enter the campaing wizard to continue creating the campaing.

I'm curious, somebody else are having problems creating campaigns?

Regards,

mrbarletta commented 8 years ago

The issue is related to MySQL 5.7 default STRICT MODE .

The query works on versions previous to 5.7 but will fail with current versions.

INSERT INTO campaigns (id,name,date_entered,date_modified,modified_user_id,created_by,deleted,assigned_user_id,tracker_count,refer_url,end_date,status,impressions,currency_id,campaign_type,frequency) VALUES ('a8004221-8f37-21c8-23b8-5780473df32f','TEST','2016-07-09 00:40:13','2016-07-09 00:40:13','1','1',0,'1',0,'http://','0000-00-00','Planning',0,'-99','Email','')
This can be fixed changing the default sql_mode to ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Default installation on ubuntu will add the NO_ZERO_DATE and the query will fail.

cumanacr commented 8 years ago

@mrbarletta confirmed thanks,

This is the default configuration for my MySQL version.

mysql --version: mysql Ver 14.14 Distrib 5.7.13, for Linux (x86_64) using EditLine wrapper

SHOW variables WHERE variable_name = 'sql_mode';
Variable_name Value
sql_mode ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Changing sql_mode as it looks on previous MySQL version allows to create the campaigns.

SET GLOBAL sql_mode = 'NO_ENGINE_SUBSTITUTION';

This will be a serious problem when people start upgrading their mysql instances. SuiteCRM out of the box will fail for everybody with MySQL default configuration.

Here's the link that explains more about 5.7 sql_mode.

Thanks again,

blloyd78 commented 7 years ago

Further to the comments added to case #2322, the workaround posted here DOES NOT WORK on my installation. I have changed the SQL mode as instructed but this has no effect.

Although the behaviour described in this bug are similar, the error listed in apache.log is different:

[Wed Sep 28 07:41:12.010428 2016] [:error] [pid 18814] [client 86.143.239.193:1036] PHP Warning: Declaration of SugarWidgetSubPanelRemoveButton::displayHeaderCell(&$layout_def) should be compatible with SugarWidgetField::displayHeaderCell($layout_def) in /var/www/crm2015/include/generic/SugarWidgets/SugarWidgetSubPanelRemoveButton.php on line 0, referer: http://**REDACTED**/crm2015/index.php?module=Campaigns&action=TrackDetailView&record=99dd0904-836a-931c-2a93-579871f812ca

[Wed Sep 28 07:41:12.010436 2016] [:error] [pid 18814] [client 86.143.239.193:1036] PHP Warning: Declaration of SugarWidgetSubPanelRemoveButton::displayList(&$layout_def) should be compatible with SugarWidgetField::displayList($layout_def) in /var/www/crm2015/include/generic/SugarWidgets/SugarWidgetSubPanelRemoveButton.php on line 0, referer: http://**REDACTED**/crm2015/index.php?module=Campaigns&action=TrackDetailView&record=99dd0904-836a-931c-2a93-579871f812ca

[Wed Sep 28 07:41:12.011248 2016] [:error] [pid 18814] [client 86.143.239.193:1036] PHP Notice: Undefined index: CampaignTrackers in /var/www/crm2015/include/SearchForm/SearchForm2.php on line 129, referer: http://**REDACTED**/crm2015/index.php?module=Campaigns&action=TrackDetailView&record=99dd0904-836a-931c-2a93-579871f812ca

[Wed Sep 28 07:41:12.012380 2016] [:error] [pid 18814] [client 86.143.239.193:1036] PHP Notice: Undefined index: EmailMarketing in /var/www/crm2015/include/SearchForm/SearchForm2.php on line 129, referer: http://**REDACTED**/crm2015/index.php?module=Campaigns&action=TrackDetailView&record=99dd0904-836a-931c-2a93-579871f812ca

blloyd78 commented 7 years ago

Further to my previous comment, the fix suggested above works perfectly with PHP 5.6. It does not work on PHP 7.

pgorod commented 7 years ago

Although this issue is not mentioned explicitly, it seems there is now a PR for this: #2454