1

Adding a new field to a set of fields results in an error DB Error: unknown error

In ConfigAndLog (real names are replaced by , and ):

Mar 16 05:19:37  [info] $backTrace = #0 /home/<domain>/public_html/sites/all/modules/civicrm/CRM/Core/Error.php(229): CRM_Core_Error::backtrace("backTrace", TRUE)
#1 [internal function](): CRM_Core_Error::handle(Object(DB_Error))
#2 /home/<domain>/public_html/sites/all/modules/civicrm/packages/PEAR.php(921): call_user_func((Array:2), Object(DB_Error))
#3 /home/<domain>/public_html/sites/all/modules/civicrm/packages/DB.php(985): PEAR_Error->__construct("DB Error: unknown error", -1, 16, (Array:2), "CREATE TABLE `<prefix>_drupal7`.log_civicrm_value_<name>_4 (\n  `id` in...")
#4 /home/<domain>/public_html/sites/all/modules/civicrm/packages/PEAR.php(575): DB_Error->__construct(-1, 16, (Array:2), "CREATE TABLE `<prefix>_drupal7`.log_civicrm_value_<name>_4 (\n  `id` in...")
#5 [internal function](): PEAR->_raiseError(Object(DB_mysqli), NULL, -1, NULL, NULL, "CREATE TABLE `<prefix>_drupal7`.log_civicrm_value_<name>_4 (\n  `id` in...", "DB_Error", TRUE)
#6 /home/<domain>/public_html/sites/all/modules/civicrm/packages/PEAR.php(224): call_user_func_array((Array:2), (Array:8))
#7 /home/<domain>/public_html/sites/all/modules/civicrm/packages/DB/common.php(1905): PEAR->__call("raiseError", (Array:7))
#8 /home/<domain>/public_html/sites/all/modules/civicrm/packages/DB/common.php(1905): PEAR->raiseError(NULL, -1, NULL, NULL, "CREATE TABLE `<prefix>_drupal7`.log_civicrm_value_<name>_4 (\n  `id` in...", "DB_Error", TRUE)
#9 /home/<domain>/public_html/sites/all/modules/civicrm/packages/DB/mysqli.php(933): DB_common->raiseError(-1, NULL, NULL, NULL, "1286 ** Unknown storage engine 'ARCHIVE'")
#10 /home/<domain>/public_html/sites/all/modules/civicrm/packages/DB/mysqli.php(403): DB_mysqli->mysqliRaiseError()
#11 /home/<domain>/public_html/sites/all/modules/civicrm/packages/DB/common.php(1216): DB_mysqli->simpleQuery("CREATE TABLE `<prefix>_drupal7`.log_civicrm_value_<name>_4 (\n  `id` in...")
#12 /home/<domain>/public_html/sites/all/modules/civicrm/packages/DB/DataObject.php(2438): DB_common->query("CREATE TABLE `<prefix>_drupal7`.log_civicrm_value_<name>_4 (\n  `id` in...")
#13 /home/<domain>/public_html/sites/all/modules/civicrm/packages/DB/DataObject.php(1627): DB_DataObject->_query("CREATE TABLE `<prefix>_drupal7`.log_civicrm_value_<name>_4 (\n  `id` in...")
#14 /home/<domain>/public_html/sites/all/modules/civicrm/CRM/Core/DAO.php(354): DB_DataObject->query("CREATE TABLE `<prefix>_drupal7`.log_civicrm_value_<name>_4 (\n  `id` in...")
#15 /home/<domain>/public_html/sites/all/modules/civicrm/CRM/Core/DAO.php(1297): CRM_Core_DAO->query("CREATE TABLE `<prefix>_drupal7`.log_civicrm_value_<name>_4 (\n  `id` in...", FALSE)
#16 /home/<domain>/public_html/sites/all/modules/civicrm/CRM/Logging/Schema.php(718): CRM_Core_DAO::executeQuery("CREATE TABLE `<prefix>_drupal7`.log_civicrm_value_<name>_4 (\n  `id` in...", (Array:0), TRUE, NULL, FALSE, FALSE)
#17 /home/<domain>/public_html/sites/all/modules/civicrm/CRM/Logging/Schema.php(385): CRM_Logging_Schema->createLogTableFor("civicrm_value_<name>_4")
#18 /home/<domain>/public_html/sites/all/modules/civicrm/CRM/Core/BAO/SchemaHandler.php(348): CRM_Logging_Schema->fixSchemaDifferencesFor("civicrm_value_<name>_4", (Array:1), FALSE)
#19 /home/<domain>/public_html/sites/all/modules/civicrm/CRM/Core/BAO/CustomField.php(1842): CRM_Core_BAO_SchemaHandler::alterFieldSQL((Array:6), FALSE, TRUE)
#20 /home/<domain>/public_html/sites/all/modules/civicrm/CRM/Core/BAO/CustomField.php(312): CRM_Core_BAO_CustomField::createField(Object(CRM_Core_DAO_CustomField), "add", FALSE, TRUE)
#21 /home/<domain>/public_html/sites/all/modules/civicrm/CRM/Custom/Form/Field.php(1000): CRM_Core_BAO_CustomField::create((Array:35))
#22 /home/<domain>/public_html/sites/all/modules/civicrm/CRM/Core/Form.php(435): CRM_Custom_Form_Field->postProcess()
#23 /home/<domain>/public_html/sites/all/modules/civicrm/CRM/Core/QuickForm/Action/Done.php(75): CRM_Core_Form->mainProcess()
#24 /home/<domain>/public_html/sites/all/modules/civicrm/packages/HTML/QuickForm/Controller.php(203): CRM_Core_QuickForm_Action_Done->perform(Object(CRM_Custom_Form_Field), "done")
#25 /home/<domain>/public_html/sites/all/modules/civicrm/packages/HTML/QuickForm/Page.php(103): HTML_QuickForm_Controller->handle(Object(CRM_Custom_Form_Field), "done")
#26 /home/<domain>/public_html/sites/all/modules/civicrm/CRM/Core/Controller.php(351): HTML_QuickForm_Page->handle("done")
#27 /home/<domain>/public_html/sites/all/modules/civicrm/CRM/Utils/Wrapper.php(113): CRM_Core_Controller->run()
#28 /home/<domain>/public_html/sites/all/modules/civicrm/CRM/Core/Invoke.php(284): CRM_Utils_Wrapper->run("CRM_Custom_Form_Field", "Aangepast veld - Toevoegen", NULL)
#29 /home/<domain>/public_html/sites/all/modules/civicrm/CRM/Core/Invoke.php(84): CRM_Core_Invoke::runItem((Array:13))
#30 /home/<domain>/public_html/sites/all/modules/civicrm/CRM/Core/Invoke.php(52): CRM_Core_Invoke::_invoke((Array:6))
#31 /home/<domain>/public_html/sites/all/modules/civicrm/drupal/civicrm.module(448): CRM_Core_Invoke::invoke((Array:6))
#32 [internal function](): civicrm_invoke("admin", "custom", "group", "field", "add")
#33 /home/<domain>/public_html/includes/menu.inc(527): call_user_func_array("civicrm_invoke", (Array:5))
#34 /home/<domain>/public_html/index.php(27): menu_execute_active_handler()
#35 {main}

I think the error is created in lines 9 and 10 ...

#9 /home/<domain>/public_html/sites/all/modules/civicrm/packages/DB/mysqli.php(933): DB_common->raiseError(-1, NULL, NULL, NULL, "1286 ** Unknown storage engine 'ARCHIVE'")
#10 /home/<domain>/public_html/sites/all/modules/civicrm/packages/DB/mysqli.php(403): DB_mysqli->mysqliRaiseError()
#11 /home/<domain>/public_html/sites/all/modules/civicrm/packages/DB/common.php(1216): DB_mysqli->simpleQuery("CREATE TABLE `<prefix>_drupal7`.log_civicrm_value_<name>_4 (\n  `id` in...")

Searching for "1286 ** Unknown storage engine 'ARCHIVE'" i found this entry:

http://civicrm.stackexchange.com/questions/15218/civicrm-logging-enabled-i-am-now-getting-an-error-on-mysqldump-unknown-storage

Executing

ALTER TABLE log_civicrm_install_canary ENGINE=InnoDB

results in

#1146 - Table '<prefix>_drupal7.log_civicrm_install_canary' doesn't exist

In the comment: "The civicrm_install_canary is a table used for installation purposes. The purpose is explained in the answer on What is the installation canary?"

http://civicrm.stackexchange.com/questions/15594/what-is-the-installation-canary

But no information in this link how to install/create civicrm_install_canary if the table is missing.

Is the non existence of this table, the real cause of the "DB error unknown"? If so, how to create it?

3

The error occurs when it tries to create the table _drupal7`.log_civicrm_value__4. It is trying to create it having storage engine of ARCHIVE. You can't see the whole command from the log. It would be something like

create table_name ( column1, column2) Engine=ARCHIVE...  

So do you have that engine? From mysql do a

show engines;

You should get something like

mysql> SHOW ENGINES;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MyISAM             | DEFAULT | MyISAM storage engine                                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

I suspect you'll not see an entry for ARCHIVE or it might have a No in the Support column.

Here is some info on enabling it

https://stackoverflow.com/questions/1458122/enabling-archive-engine

| improve this answer | |
  • ARCHIVE is not present as an engine, and in shared hosting i cannot enable it. Is it possible to change the command in CiviCRM? Is INNODB for the civicrm log files a drawback? – MarkDQ Mar 17 '17 at 13:06
  • 1
    "The ARCHIVE Storage Engine was enabled by default until MariaDB 10.0. From MariaDB 10.1, the plugin needs to be specifically enabled." see mariadb.com/kb/en/mariadb/archive . Because my webhost did not enable the plugin, i need another solution. – MarkDQ Mar 17 '17 at 18:46
  • Do you logging turned on (admin=>system=>misc)? It is trying to create the logging table for this field. The code is basically hard coded to create the table with engine=archive. What's odd to me is if logging is turned on how did it create all the other log tables? Do you see lots of tables that are civicrm_log_TABLENAME? Logging is handled with a trigger. Do you see a trigger on civicrm_value_FIELDSETNAME_4? Do you have a test instance? If so you could try dropping the trigger or try turning off logging. I can't remember if you turn of logging is a major thing with it drop triggers – Paul-Tahoe Mar 17 '17 at 23:40
  • 1
    The site was moved recently to a faster server. The old server had MySQL with ARCHIVE engine, the new server MariaDB 10.1 without ARCHIVE. That explains why we did not have problems before, only after the change. Our provider got a reply from the CPANEL guys (MARIADB is installed using CPANEL): we are working on a new version, a beta will be released next Friday, the 24th. I am wondering why they need a new version, as MARIADB stated that only "INSTALL SONAME 'ha_archive';" has to be added... – MarkDQ Mar 18 '17 at 16:59
  • And yes, disabling logging (admin=>system=>misc) removed the error, as no ARCHIVE table has to be created. – MarkDQ Mar 18 '17 at 17:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.