[solved] CiviDiscount error when using a code: DB Error: no such field

[curious-trick]

We've been using CiviDiscount for over a year, and it started giving errors in the past two weeks. Currently we're using CiviDiscount 2.5, CiviCRM 4.4.1, Drupal 7.26.

Some of the pages work ok, such as the "Manage Discount Codes" page:
http://www.oursite.com/civicrm/cividiscount/discount/list?reset=1

what still works:
- viewing a list of discount codes
- viewing a discount code
- editing a discount code
- registering for an event without using a discount code

what stopped working (both actions give the same error, see example below):
- using a discount code when registering for an event
- whatever's supposed to show up when I click on the number of times a discount code has been used (see example below)

For example, on the discount code list page
http://www.oursite.com/civicrm/cividiscount/discount/list?reset=1
we see this as the 4th line:

Code: [Select]

SMN40
Someone's code  $ 80.00   3/Unlimited     View Edit more
That "3" before the "Unlimited" is a hyperlink, and clicking on it gives the error, instead of whatever's supposed to show up. It goes to the URL
http://www.oursite.com/civicrm/cividiscount/report?id=1&reset=1
and says:

Code: [Select]

Sorry but we are not able to provide this at the moment.
DB Error: no such field
Return to home page.
As far as I know, we haven't done any updates that coincided with the appearance of this error, so I don't know what made it start, or where to poke around to fix it.

Any hints about what's wrong and what we might do?

thanks for reading

[lolas]

One thing you can do is check your CiviCRM log file for more information. The DB error will usually be there with more information including the exact SQL statement that caused the error and the DB error message that should say which is the missing field.

You will find the log file under your base directory (your site files directory) then under civicrm/ConfigAndLog/. Look at the dates on the files to find the most recent one or the one from the date of the error. If you need to you can find your base directory location at: /civicrm/admin/setting/updateConfigBackend?reset=1

[marxistvegan]

@lolas thanks for the tip I am working with @curious-trick on debugging this

I looked through the logs and came across this

Code: [Select]

[db_error: message="DB Error: no such field" code=-19 mode=callback callback=CRM_Core_Error::handle prefix="" info="INSERT INTO cividiscount_track (ite\
m_id , contact_id , used_date , contribution_id , entity_table , entity_id , description ) VALUES ( 46 ,  607 ,  20140313141023 ,  53 , 'civicrm_participant' ,  81 , '^AF\
PC 2014, Day 1-5 (Early Registration) (Discount IPC11: $ 80.00 Cuba IPC11)^A' )  [nativecode=1054 ** Unknown column 'description' in 'field list']"]
)

Which suggests to me that whatever the cividiscount fields are, are not seen in the db at least that is my poor experience in mysql speaking

~mv

[curious-trick]

and here's what I see in the civi log from clicking on the number of times a discount code was used:

Code: [Select]

Mar 21 21:59:13  [info] $Fatal Error Details = Array
(
    [callback] => Array
        (
            [0] => CRM_Core_Error
            [1] => handle
        )

    [code] => -19
    [message] => DB Error: no such field
    [mode] => 16
    [debug_info] =>
SELECT    t.item_id as item_id,
      t.contact_id as contact_id,
      t.used_date as used_date,
      t.contribution_id as contribution_id,
      t.entity_table as entity_table,
      t.entity_id as entity_id,
      t.description as description  FROM cividiscount_track AS t  WHERE t.item_id = 1 ORDER BY t.item_id, t.used_date  [nativecode=1054 ** Unknown column 't.description' in 'field list']
    [type] => DB_Error
    [user_info] =>
SELECT    t.item_id as item_id,
      t.contact_id as contact_id,
      t.used_date as used_date,
      t.contribution_id as contribution_id,
      t.entity_table as entity_table,
      t.entity_id as entity_id,
      t.description as description  FROM cividiscount_track AS t  WHERE t.item_id = 1 ORDER BY t.item_id, t.used_date  [nativecode=1054 ** Unknown column 't.description' in 'field list']
    [to_string] => [db_error: message="DB Error: no such field" code=-19 mode=callback callback=CRM_Core_Error::handle prefix="" info="
SELECT    t.item_id as item_id,
      t.contact_id as contact_id,
      t.used_date as used_date,
      t.contribution_id as contribution_id,
      t.entity_table as entity_table,
      t.entity_id as entity_id,
      t.description as description  FROM cividiscount_track AS t  WHERE t.item_id = 1 ORDER BY t.item_id, t.used_date  [nativecode=1054 ** Unknown column 't.description' in 'field list']"]
)


Mar 21 21:59:13  [info] $backTrace = #0 /path/to/drupal/sites/all/modules/civicrm/CRM/Core/Error.php(197): CRM_Core_Error::backtrace("backTrace", TRUE)
#1 [internal function](): CRM_Core_Error::handle(Object(DB_Error))
#2 /path/to/drupal/sites/all/modules/civicrm/packages/PEAR.php(931): call_user_func((Array:2), Object(DB_Error))
#3 /path/to/drupal/sites/all/modules/civicrm/packages/DB.php(969): PEAR_Error->PEAR_Error("DB Error: no such field", -19, 16, (Array:2), "\nSELECT    t.item_id as item_id,\n      t.contact_id as contact_id,\n      t...")
#4 /path/to/drupal/sites/all/modules/civicrm/packages/PEAR.php(564): DB_Error->DB_Error(-19, 16, (Array:2), "\nSELECT    t.item_id as item_id,\n      t.contact_id as contact_id,\n      t...")
#5 /path/to/drupal/sites/all/modules/civicrm/packages/DB/common.php(1905): PEAR->raiseError(NULL, -19, NULL, NULL, "\nSELECT    t.item_id as item_id,\n      t.contact_id as contact_id,\n      t...", "DB_Error", TRUE)
#6 /path/to/drupal/sites/all/modules/civicrm/packages/DB/mysql.php(898): DB_common->raiseError(-19, NULL, NULL, NULL, "1054 ** Unknown column 't.description' in 'field list'")
#7 /path/to/drupal/sites/all/modules/civicrm/packages/DB/mysql.php(327): DB_mysql->mysqlRaiseError()
#8 /path/to/drupal/sites/all/modules/civicrm/packages/DB/common.php(1216): DB_mysql->simpleQuery("\nSELECT    t.item_id as item_id,\n      t.contact_id as contact_id,\n      t...")
#9 /path/to/drupal/sites/all/modules/civicrm/packages/DB/DataObject.php(2421): DB_common->query("\nSELECT    t.item_id as item_id,\n      t.contact_id as contact_id,\n      t...")
#10 /path/to/drupal/sites/all/modules/civicrm/packages/DB/DataObject.php(1613): DB_DataObject->_query("\nSELECT    t.item_id as item_id,\n      t.contact_id as contact_id,\n      t...")
#11 /path/to/drupal/sites/all/modules/civicrm/CRM/Core/DAO.php(160): DB_DataObject->query("\nSELECT    t.item_id as item_id,\n      t.contact_id as contact_id,\n      t...")
#12 /path/to/drupal/sites/all/civi-extensions/org.civicrm.module.cividiscount/CDM/BAO/Track.php(120): CRM_Core_DAO->query("\nSELECT    t.item_id as item_id,\n      t.contact_id as contact_id,\n      t...")
#13 /path/to/drupal/sites/all/civi-extensions/org.civicrm.module.cividiscount/CDM/BAO/Track.php(82): CDM_BAO_Track::getUsage("1", NULL, NULL)
#14 /path/to/drupal/sites/all/civi-extensions/org.civicrm.module.cividiscount/CDM/Page/Discount/Report.php(152): CDM_BAO_Track::getUsageByCode("1")
#15 /path/to/drupal/sites/all/civi-extensions/org.civicrm.module.cividiscount/CDM/Page/Discount/Report.php(161): CDM_Page_Discount_Report->preProcess()
#16 /path/to/drupal/sites/all/modules/civicrm/CRM/Core/Invoke.php(323): CDM_Page_Discount_Report->run((Array:3), NULL)
#17 /path/to/drupal/sites/all/modules/civicrm/CRM/Core/Invoke.php(72): CRM_Core_Invoke::runItem((Array:12))
#18 /path/to/drupal/sites/all/modules/civicrm/CRM/Core/Invoke.php(52): CRM_Core_Invoke::_invoke((Array:3))
#19 /path/to/drupal/sites/all/modules/civicrm/drupal/civicrm.module(456): CRM_Core_Invoke::invoke((Array:3))
#20 [internal function](): civicrm_invoke("cividiscount", "report")
#21 /usr/local/share/drupal-7.26/includes/menu.inc(517): call_user_func_array("civicrm_invoke", (Array:2))
#22 /usr/local/share/drupal-7.26/index.php(21): menu_execute_active_handler()
#23 {main}

[curious-trick]

Possible next step: http://wiki.civicrm.org/confluence/display/CRMDOC/What+to+do+if+your+DB+gets+into+an+inconsistent+state -- does anyone out there think this might resolve the issue?

[curious-trick]

I haven't tried the "inconsistent database" fix yet. One workaround that came to mind: since all of our discount codes give the same discount amount, maybe we could use a Webform as page 1 of registration, and have a discount code field that checks if the discount code is one of the valid ones, and if so opens either A) a new registration option with the $80 discount applied, or B) registration for a different event (i.e. the same real-life event, but a different CiviCRM event) with the $80 discount applied. I wonder if this is possible with Webform and CiviCRM -- I think so.

[curious-trick]

Resolution:
- copy current discount codes, save in text/spreadsheet for later reference
- disable CiviDiscount through CiviCRM extensions manager
- uninstall CiviDiscount (this drops the CiviDiscount database tables)
- re-install CiviDiscount
- recreate desired discount codes
(-celebrate!)

Thanks to dlobo and burgess on the IRC channel for affirming that this was likely to work.

[zorgalina]

I see you solved the problem by reinstalling your cividiscount tables (and luckily cividiscount only involves 2 tables) but what had happened that the DB 'thought' that column was missing? It sounds like the column 'description' was actually still there in your 'cividiscount_track' table.  You were live and so couldn't easily reinstall from scratch or your discount IDs in the civicrm tables would be broken.