search

proexchange / com.pesc.sparkpost

Integrates SparkPost to CiviCRM, so email can be sent out over the SparkPost service and bounces can be processed in CiviCRM
Other
9 stars 5 forks source link

Install fails - DB Error: Syntax error #12

Closed mfuggle closed 8 years ago

mfuggle commented 8 years ago

I am trying to switch from using Mandrill to SparkPost. I have a valid Sparkpost account and have tested it inside CiviCRM. However when I install the Sparkpost extension in CiviCRM (Dupal) I get a DB Error: Syntax Error. I am hesitant to make this post since I have been bothering @mark-rodgers over issues I am having with the installation of the vedaconsulting Mosaico extension. My aim is to move away from Mailchimp/Mandrill and move to Civimail (Mosaico)/Sparkpost and I am getting thwarted at every turn. Cheers Martin Fuggle

mark-rodgers commented 8 years ago

@mfuggle do you have any more details as to when the error occurs? Also if you haven't already, try enabling debugging and backtrace in civicrm so we can get more details on the error.

joseltorres commented 8 years ago

What version of civicrm? Php and MySQL version? Any more details on the error? If you have watchdog logging enabled in civicrm, do you see anything else in there?

mfuggle commented 8 years ago

Wow - how's that for a rapid response. I hate to tell you but I am operating in the production version of the site and don't have a dev version created. I know! Just as start I am using Php version 5.5.9-1ubuntu4.14, MySQL 5.6.23, CiviCRM 4.6.14 and Drupal 7.43. All pretty well uop to date other than CiviCRM which is 4.6 rather than 4.7.

I'll see what I can find in logs etc

mfuggle commented 8 years ago

Here is the backtrace:

backTrace

0 /var/www/html/sites/all/modules/civicrm/CRM/Core/Error.php(182): CRM_Core_Error::backtrace()

1 [internal function](): CRM_Core_Error::handle(Object(DB_Error))

2 /var/www/html/sites/all/modules/civicrm/packages/PEAR.php(931): call_user_func((Array:2), Object(DB_Error))

3 /var/www/html/sites/all/modules/civicrm/packages/DB.php(976): PEAR_Error->PEAR_Error("DB Error: syntax error", -2, 16, (Array:2), "INSERT INTO civicrm_mailing_bounce_pattern (bounce_type_id, pattern) VA...")

4 /var/www/html/sites/all/modules/civicrm/packages/PEAR.php(564): DB_Error->DB_Error(-2, 16, (Array:2), "INSERT INTO civicrm_mailing_bounce_pattern (bounce_type_id, pattern) VA...")

5 /var/www/html/sites/all/modules/civicrm/packages/DB/common.php(1905): PEAR->raiseError(NULL, -2, NULL, NULL, "INSERT INTO civicrm_mailing_bounce_pattern (bounce_type_id, pattern) VA...", "DB_Error", TRUE)

6 /var/www/html/sites/all/modules/civicrm/packages/DB/mysql.php(895): DB_common->raiseError(-2, NULL, NULL, NULL, "1064 \ You have an error in your SQL syntax; check the manual that correspon...")

7 /var/www/html/sites/all/modules/civicrm/packages/DB/mysql.php(328): DB_mysql->mysqlRaiseError()

8 /var/www/html/sites/all/modules/civicrm/packages/DB/common.php(1216): DB_mysql->simpleQuery("INSERT INTO civicrm_mailing_bounce_pattern (bounce_type_id, pattern) VA...")

9 /var/www/html/sites/all/modules/civicrm/packages/DB/DataObject.php(2442): DB_common->query("INSERT INTO civicrm_mailing_bounce_pattern (bounce_type_id, pattern) VA...")

10 /var/www/html/sites/all/modules/civicrm/packages/DB/DataObject.php(1634): DB_DataObject->_query("INSERT INTO civicrm_mailing_bounce_pattern (bounce_type_id, pattern) VA...")

11 /var/www/html/sites/all/modules/civicrm/CRM/Core/DAO.php(321): DB_DataObject->query("INSERT INTO civicrm_mailing_bounce_pattern (bounce_type_id, pattern) VA...")

12 /var/www/html/sites/all/modules/civicrm/CRM/Core/DAO.php(1222): CRM_Core_DAO->query("INSERT INTO civicrm_mailing_bounce_pattern (bounce_type_id, pattern) VA...", TRUE)

13 /var/www/html/sites/default/files/civicrm/extensions/com.pesc.sparkpost-1.1/sparkpost.php(55): CRM_Core_DAO::singleValueQuery("INSERT INTO civicrm_mailing_bounce_pattern (bounce_type_id, pattern) VA...")

14 /var/www/html/sites/all/modules/civicrm/CRM/Extension/Manager/Module.php(78): sparkpost_civicrm_enable()

15 /var/www/html/sites/all/modules/civicrm/CRM/Extension/Manager/Module.php(51): CRM_Extension_Manager_Module->callHook(Object(CRM_Extension_Info), "enable")

16 /var/www/html/sites/all/modules/civicrm/CRM/Extension/Manager.php(233): CRM_Extension_Manager_Module->onPreInstall(Object(CRM_Extension_Info))

17 /var/www/html/sites/all/modules/civicrm/CRM/Admin/Form/Extensions.php(195): CRM_Extension_Manager->install((Array:1))

18 /var/www/html/sites/all/modules/civicrm/CRM/Core/Form.php(345): CRM_Admin_Form_Extensions->postProcess()

19 /var/www/html/sites/all/modules/civicrm/CRM/Core/StateMachine.php(164): CRM_Core_Form->mainProcess()

20 /var/www/html/sites/all/modules/civicrm/CRM/Core/QuickForm/Action/Next.php(61): CRM_Core_StateMachine->perform(Object(CRM_Admin_Form_Extensions), "next", "Next")

21 /var/www/html/sites/all/modules/civicrm/packages/HTML/QuickForm/Controller.php(203): CRM_Core_QuickForm_Action_Next->perform(Object(CRM_Admin_Form_Extensions), "next")

22 /var/www/html/sites/all/modules/civicrm/packages/HTML/QuickForm/Page.php(103): HTML_QuickForm_Controller->handle(Object(CRM_Admin_Form_Extensions), "next")

23 /var/www/html/sites/all/modules/civicrm/CRM/Core/Controller.php(353): HTML_QuickForm_Page->handle("next")

24 /var/www/html/sites/all/modules/civicrm/CRM/Core/Page/Basic.php(395): CRM_Core_Controller->run()

25 /var/www/html/sites/all/modules/civicrm/CRM/Core/Page/Basic.php(174): CRM_Core_Page_Basic->edit(1, NULL)

26 /var/www/html/sites/all/modules/civicrm/CRM/Admin/Page/Extensions.php(125): CRM_Core_Page_Basic->run()

27 /var/www/html/sites/all/modules/civicrm/CRM/Core/Invoke.php(312): CRM_Admin_Page_Extensions->run((Array:3), NULL)

28 /var/www/html/sites/all/modules/civicrm/CRM/Core/Invoke.php(86): CRM_Core_Invoke::runItem((Array:13))

29 /var/www/html/sites/all/modules/civicrm/CRM/Core/Invoke.php(54): CRM_Core_Invoke::_invoke((Array:3))

30 /var/www/html/sites/all/modules/civicrm/drupal/civicrm.module(489): CRM_Core_Invoke::invoke((Array:3))

31 [internal function](): civicrm_invoke("admin", "extensions")

32 /var/www/html/includes/menu.inc(527): call_user_func_array("civicrm_invoke", (Array:2))

33 /var/www/html/index.php(21): menu_execute_active_handler()

34 {main}

mark-rodgers commented 8 years ago

Check your CiviCRM database in table civicrm_mailing_bounce_type. When you installed the extension it should have created a row with the value "SparkPost" in the name column. Also check if the name column is of type varchar or enum.

mark-rodgers commented 8 years ago

@mfuggle if the name field in civicrm_mailing_bounce_type table is type enum refer to https://github.com/proexchange/com.pesc.sparkpost/issues/11 this is a known issue that should be fixed soon.

mfuggle commented 8 years ago

It doesn't have a row with the value 'Sparkpost' in the name column. See attached... bounce type

mark-rodgers commented 8 years ago

On row 28 and 29 the name value should say SparkPost. This is why the insert into the civicrm_mailing_bounce_pattern is failing. Check the structure of civicrm_mailing_bounce_type. If the name column is type enum run the fix query in issue #11 and this should resolve your issue. Assuming the column is an enum delete row 28 and 29 after you run the query before you try installing the extension again.

mfuggle commented 8 years ago

OK - In fact there were three more rows with Sparkpost supression list in the description column. Should I delete all but one of them or add Sparkpost in the name column for all of them. I guess the reason there are multiples is because I have tried to install a few times so I guess getting rid of all but one row should be OK

joseltorres commented 8 years ago

delete all rows with 'Sparkpost suppression list' in the description column, run the fix query, then install.

mfuggle commented 8 years ago

All fixed by running the two SQL statements in #11.

Many thanks - now I need to get Mosaico up and running!