"DB Error: unknown error" when adding (custom) field

Question

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?

Community · Accepted Answer · 2017-05-23 12:38:59Z

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

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, Commented Mar 17, 2017 at 13:06
"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, Commented Mar 17, 2017 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, Commented Mar 17, 2017 at 23:40
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, Commented Mar 18, 2017 at 16:59
And yes, disabling logging (admin=>system=>misc) removed the error, as no ARCHIVE table has to be created. — MarkDQ, Commented Mar 18, 2017 at 17:02

Stack Exchange Network

"DB Error: unknown error" when adding (custom) field

1 Answer 1

Your Answer

Not the answer you're looking for? Browse other questions tagged
custom-fields
db-logging
or ask your own question.

Linked

Hot Network Questions

"DB Error: unknown error" when adding (custom) field

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

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

Linked

Related

Hot Network Questions

Not the answer you're looking for? Browse other questions tagged
custom-fields
db-logging
or ask your own question.