Error on install with MySQL 5.6

[laryn]

I tried installing on a server that had recently been updated to MySQL 5.6 and it crashes with the following in the log:

Mar 16 10:44:58  [info] $Fatal Error Details = Array
(
    [callback] => Array
        (
            [0] => CRM_Core_Error
            [1] => handle
        )

    [code] => -2
    [message] => DB Error: syntax error
    [mode] => 16
    [debug_info] => INSERT INTO `civicrm_mailing_bounce_pattern` (`bounce_type_id`, `pattern`) VALUES (, 'recipient address suppressed due to customer policy') [nativecode=1064 ** You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' 'recipient address suppressed due to customer policy')' at line 1]
    [type] => DB_Error
    [user_info] => INSERT INTO `civicrm_mailing_bounce_pattern` (`bounce_type_id`, `pattern`) VALUES (, 'recipient address suppressed due to customer policy') [nativecode=1064 ** You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' 'recipient address suppressed due to customer policy')' at line 1]
    [to_string] => [db_error: message="DB Error: syntax error" code=-2 mode=callback callback=CRM_Core_Error::handle prefix="" info="INSERT INTO `civicrm_mailing_bounce_pattern` (`bounce_type_id`, `pattern`) VALUES (, 'recipient address suppressed due to customer policy') [nativecode=1064 ** You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' 'recipient address suppressed due to customer policy')' at line 1]"]
)

Mar 16 10:44:58  [info] $backTrace = #0 /path/to/civicrm/CRM/Core/Error.php(235): CRM_Core_Error::backtrace("backTrace", TRUE)
#1 [internal function](): CRM_Core_Error::handle(Object(DB_Error))
#2 /path/to/civicrm/packages/PEAR.php(931): call_user_func((Array:2), Object(DB_Error))
#3 /path/to/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 /path/to/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 /path/to/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 /path/to/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 /path/to/civicrm/packages/DB/mysql.php(328): DB_mysql->mysqlRaiseError()
#8 /path/to/civicrm/packages/DB/common.php(1216): DB_mysql->simpleQuery("INSERT INTO `civicrm_mailing_bounce_pattern` (`bounce_type_id`, `pattern`) VA...")
#9 /path/to/civicrm/packages/DB/DataObject.php(2442): DB_common->query("INSERT INTO `civicrm_mailing_bounce_pattern` (`bounce_type_id`, `pattern`) VA...")
#10 /path/to/civicrm/packages/DB/DataObject.php(1634): DB_DataObject->_query("INSERT INTO `civicrm_mailing_bounce_pattern` (`bounce_type_id`, `pattern`) VA...")
#11 /path/to/civicrm/CRM/Core/DAO.php(321): DB_DataObject->query("INSERT INTO `civicrm_mailing_bounce_pattern` (`bounce_type_id`, `pattern`) VA...")
#12 /path/to/civicrm/CRM/Core/DAO.php(1222): CRM_Core_DAO->query("INSERT INTO `civicrm_mailing_bounce_pattern` (`bounce_type_id`, `pattern`) VA...", TRUE)
#13 /path/to/civicrm_extensions/com.pesc.sparkpost/sparkpost.php(55): CRM_Core_DAO::singleValueQuery("INSERT INTO `civicrm_mailing_bounce_pattern` (`bounce_type_id`, `pattern`) VA...")
#14 /path/to/civicrm/CRM/Extension/Manager/Module.php(78): sparkpost_civicrm_enable()
#15 /path/to/civicrm/CRM/Extension/Manager/Module.php(51): CRM_Extension_Manager_Module->callHook(Object(CRM_Extension_Info), "enable")
#16 /path/to/civicrm/CRM/Extension/Manager.php(233): CRM_Extension_Manager_Module->onPreInstall(Object(CRM_Extension_Info))
#17 /path/to/civicrm/CRM/Admin/Form/Extensions.php(195): CRM_Extension_Manager->install((Array:1))
#18 /path/to/civicrm/CRM/Core/Form.php(345): CRM_Admin_Form_Extensions->postProcess()
#19 /path/to/civicrm/CRM/Core/StateMachine.php(164): CRM_Core_Form->mainProcess()
#20 /path/to/civicrm/CRM/Core/QuickForm/Action/Next.php(61): CRM_Core_StateMachine->perform(Object(CRM_Admin_Form_Extensions), "next", "Next")
#21 /path/to/civicrm/packages/HTML/QuickForm/Controller.php(203): CRM_Core_QuickForm_Action_Next->perform(Object(CRM_Admin_Form_Extensions), "next")
#22 /path/to/civicrm/packages/HTML/QuickForm/Page.php(103): HTML_QuickForm_Controller->handle(Object(CRM_Admin_Form_Extensions), "next")
#23 /path/to/civicrm/CRM/Core/Controller.php(353): HTML_QuickForm_Page->handle("next")
#24 /path/to/civicrm/CRM/Core/Page/Basic.php(395): CRM_Core_Controller->run()
#25 /path/to/civicrm/CRM/Core/Page/Basic.php(174): CRM_Core_Page_Basic->edit(1, NULL)
#26 /path/to/civicrm/CRM/Admin/Page/Extensions.php(125): CRM_Core_Page_Basic->run()
#27 /path/to/civicrm/CRM/Core/Invoke.php(312): CRM_Admin_Page_Extensions->run((Array:3), NULL)
#28 /path/to/civicrm/CRM/Core/Invoke.php(86): CRM_Core_Invoke::runItem((Array:13))
#29 /path/to/civicrm/CRM/Core/Invoke.php(54): CRM_Core_Invoke::_invoke((Array:3))
#30 /path/to/civicrm/drupal/civicrm.module(489): CRM_Core_Invoke::invoke((Array:3))
#31 [internal function](): civicrm_invoke("admin", "extensions")
#32 /path/to/drupal/includes/menu.inc(527): call_user_func_array("civicrm_invoke", (Array:2))
#33 /path/to/drupal/index.php(21): menu_execute_active_handler()
#34 {main}

[mark-rodgers]

@laryn can you check your database and see if a SparkPost row was created in the table "civicrm_mailing_bounce_type" after you installed the extension?

[laryn]

@mark-rodgers The extension is not listed as installed when I go back to the extensions page after the crash/error, but it looks like it tried to create that row twice, without the name being entered (NB: I tried to install the extension twice):

[mark-rodgers]

@laryn Can you try executing this query manually using the SQL tab in PHPMyAdmin and seeing if you get an error?

EDIT: GitHub removed the backticks, so I linked a txt file with the query you want to run, you can try it with and without the backticks and see if either work. Also we're spinning up a VM with SQL 5.6 right now so we can test.

http://mrodgers.pesc.com/laryn.txt

[laryn]

Same result with and without backticks.

[laryn]

@mark-rodgers

I was poking around in the structure of this table and found something strange. The 'name' column is listed as type="ENUM" and length/values as "'AOL','Away','DNS','Host','Inactive','Invalid','Loop','Quota','Relay','Spam','Syntax','Unknown'".

When I look at my other server/database that value is type="Varchar(24)"

Maybe this is just a strange error on this particular database, and not due to MySQL 5.6?

[joseltorres]

what version of civi?

[laryn]

@joseltorres It is 4.6.14, but has been upgraded for years from older versions. Perhaps there was a change to this table that failed somewhere along the way and never got picked up?

I see this particular value in older versions of Civi so that will be my assumption. I am going to try manually updating this field and see if it installs correctly.

[laryn]

@mark-rodgers @joseltorres

Bingo. Not sure when that was supposed to have changed to a varchar but I deleted all the attempted inserts, changed it to Varchar(24), and tried reinstalling through the GUI. Worked as expected.

Thanks so much for the help and sorry for the false trail on MySQL 4.6!

[mark-rodgers]

@laryn If I recall correctly in CiviCRM 4.5, this is when they changed it from an ENUM to a VARCHAR. Not sure why the field would be an enum on your 4.6.14 database, perhaps something failed along the way like you said.

issue: https://issues.civicrm.org/jira/browse/CRM-15558

solution: ALTER TABLE civicrm_mailing_bounce_type CHANGE name name VARCHAR( 24 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT 'Type of bounce';

EDIT: glad you got it cleared up

[tabletguy]

I just checked, and my system also still had the "enum" field type, and I'm also at Joomla 4.6.14. Don't know if it would be easy for your installation to automatically run this check/fix, but it might save some hassles for people when your extension starts to pick up momentum! Thanks for posting the SQL solution statement!

[joseltorres]

Thanks for the input. I will definitely add a check/fix for this soon.

[joseltorres]

Now checking for this issue and fixing in 1.2. https://github.com/proexchange/com.pesc.sparkpost/releases/tag/1.2