Answers.Body: <p>Are you sure you dont have logging turned on? It may be the trigger on the table failing to insert into the logging tables because they haven't been refreshed yet?</p>
  
  <p>Solution is to disable logging whilst the upgrade is done then turn back on again.</p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-06-08T18:19:02.917Z
Answers.Text: I'm not sure I understand. Are you saying that while upgrading you should not have logging turned on, since that might prevent the upgrade to fail insert triggers? I'm not familiar with the trigger mechanism in conjunction with logging, what does "refresh" mean? Just to be clear, this does not happen while upgrading. Logging was not turned on when upgrading (I think) since I turned it on to debug this issue.
Answers.UserDisplayName: thoni56
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-06-08T18:52:04.607Z
Answers.Text: Aah, I misread. You said "logging" and I thought debug/traceback. Yes, we have logging turned on. Then the upgrade did not insert everything it should? How can I repair that? Manually insert the trigger?
Answers.UserDisplayName: thoni56
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-06-08T19:13:39.777Z
Answers.Text: I turned of logging and it seems that the error does not longer occur. So thanks for the pointer. However I still would like to know how to repair the database and then I will accept your answer.
Answers.UserDisplayName: thoni56
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-06-08T19:49:16.700Z
Answers.Text: I think Eileen has suggested that adding any Extension can sort out existing trigger problems. i may be paraphrasing incorrectly. but somewhere i read something along the lines of 'just enable any extension, that will sort things, then uninstall it assuming you don't need it'
Answers.UserDisplayName: petednz - fuzion
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-06-09T08:38:10.810Z
Answers.Text: Yep or turning logging off and back on will effectively repair the logging tables, making sure they are inline with the main CiviCRM schema and therefore avoiding these issues.
Answers.UserDisplayName: Parvez Saleh
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-06-08T17:35:23.463Z
Answers.LastActivityDate: 2017-06-08T17:35:23.463Z
Answers.UserDisplayName: Parvez Saleh
Body: <p>I've upgraded CiviCRM multiple times since we last ran an event which requires payments, and are now at 4.7.17 on Joomla 3.6.5 with MySQL 5.7.18.</p>
  
  <p>When many (not all) users try to register they encounter "DBError: no such field" and the debug info points to <code>Unknown column 'card_type' in 'field list', 1054</code> and the query</p>
  
  <pre><code>INSERT INTO civicrm_financial_trxn (to_financial_account_id , trxn_date , total_amount , fee_amount , net_amount , currency , status_id , payment_instrument_id ) VALUES ( 10 ,  20170607160037 ,  3200 ,  0 ,  3200 , 'SEK' ,  2 ,  4 )  [nativecode=1054 ** Unknown column 'card_type' in 'field list']
  </code></pre>
  
  <p>and the backtrace shows this</p>
  
  <pre><code>...
  #14 /var/www/events/administrator/components/com_civicrm/civicrm/CRM/Core/DAO.php(487): DB_DataObject-&gt;insert()
  #15 /var/www/events/administrator/components/com_civicrm/civicrm/CRM/Core/BAO/FinancialTrxn.php(66): CRM_Core_DAO-&gt;save()
  #16 /var/www/events/administrator/components/com_civicrm/civicrm/CRM/Contribute/BAO/Contribution.php(3364): CRM_Core_BAO_FinancialTrxn::create((Array:11))
  #17 /var/www/events/administrator/components/com_civicrm/civicrm/CRM/Contribute/BAO/Contribution.php(236): CRM_Contribute_BAO_Contribution::recordFinancialAccounts((Array:22))
  #18 /var/www/events/administrator/components/com_civicrm/civicrm/CRM/Event/Form/Registration/Confirm.php(1038): CRM_Contribute_BAO_Contribution::add((Array:22), (Array:0))
  #19 /var/www/events/administrator/components/com_civicrm/civicrm/CRM/Event/Form/Registration/Confirm.php(611): CRM_Event_Form_Registration_Confirm::processContribution(Object(CRM_Event_Form_Registration_Confirm), (Array:35), NULL, "6642", TRUE, FALSE, (Array:10))
  #20 /var/www/events/administrator/components/com_civicrm/civicrm/CRM/Core/Form.php(453): CRM_Event_Form_Registration_Confirm-&gt;postProcess()
  ...
  </code></pre>
  
  <p>However, in my database the field <em>does</em> actually exist, as shown by this create statement generated from the table</p>
  
  <pre><code>CREATE TABLE `civicrm_financial_trxn` (
      `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Gift ID',
      ...
     `card_type` int(10) unsigned DEFAULT NULL COMMENT 'FK to accept_creditcard option group values',
      ...
  </code></pre>
  
  <p>How can this happen? What do I do next? Should I try re-running the database upgrade script?</p>
  
Tags: <error><mysql><fields><datatables>
Title: No such field: card_type, but exists
ThreadText: # No such field: card_type, but exists
  
  (Asked by: username_0 on 2017-06-08)
  
  I've upgraded CiviCRM multiple times since we last ran an event which requires payments, and are now at 4.7.17 on Joomla 3.6.5 with MySQL 5.7.18.
  
  When many (not all) users try to register they encounter "DBError: no such field" and the debug info points to `Unknown column 'card_type' in 'field list', 1054` and the query
  
  ```
  INSERT INTO civicrm_financial_trxn (to_financial_account_id , trxn_date , total_amount , fee_amount , net_amount , currency , status_id , payment_instrument_id ) VALUES ( 10 ,  20170607160037 ,  3200 ,  0 ,  3200 , 'SEK' ,  2 ,  4 )  [nativecode=1054 ** Unknown column 'card_type' in 'field list']
  
  ```
  
  and the backtrace shows this
  
  ```
  ...
  #14 /var/www/events/administrator/components/com_civicrm/civicrm/CRM/Core/DAO.php(487): DB_DataObject->insert()
  #15 /var/www/events/administrator/components/com_civicrm/civicrm/CRM/Core/BAO/FinancialTrxn.php(66): CRM_Core_DAO->save()
  #16 /var/www/events/administrator/components/com_civicrm/civicrm/CRM/Contribute/BAO/Contribution.php(3364): CRM_Core_BAO_FinancialTrxn::create((Array:11))
  #17 /var/www/events/administrator/components/com_civicrm/civicrm/CRM/Contribute/BAO/Contribution.php(236): CRM_Contribute_BAO_Contribution::recordFinancialAccounts((Array:22))
  #18 /var/www/events/administrator/components/com_civicrm/civicrm/CRM/Event/Form/Registration/Confirm.php(1038): CRM_Contribute_BAO_Contribution::add((Array:22), (Array:0))
  #19 /var/www/events/administrator/components/com_civicrm/civicrm/CRM/Event/Form/Registration/Confirm.php(611): CRM_Event_Form_Registration_Confirm::processContribution(Object(CRM_Event_Form_Registration_Confirm), (Array:35), NULL, "6642", TRUE, FALSE, (Array:10))
  #20 /var/www/events/administrator/components/com_civicrm/civicrm/CRM/Core/Form.php(453): CRM_Event_Form_Registration_Confirm->postProcess()
  ...
  
  ```
  
  However, in my database the field *does* actually exist, as shown by this create statement generated from the table
  
  ```
  CREATE TABLE `civicrm_financial_trxn` (
      `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Gift ID',
      ...
     `card_type` int(10) unsigned DEFAULT NULL COMMENT 'FK to accept_creditcard option group values',
      ...
  
  ```
  
  How can this happen? What do I do next? Should I try re-running the database upgrade script?
  
  **Tags:** error, mysql, fields, datatables
  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_1 on 2017-06-08. Score: 0 | Accepted answer
  
  Are you sure you dont have logging turned on? It may be the trigger on the table failing to insert into the logging tables because they haven't been refreshed yet?
  
  Solution is to disable logging whilst the upgrade is done then turn back on again.
  
  > **Comments:**
  > **username_0**: I'm not sure I understand. Are you saying that while upgrading you should not have logging turned on, since that might prevent the upgrade to fail insert triggers? I'm not familiar with the trigger mechanism in conjunction with logging, what does "refresh" mean? Just to be clear, this does not happen while upgrading. Logging was not turned on when upgrading (I think) since I turned it on to debug this issue.  
  > **username_0**: Aah, I misread. You said "logging" and I thought debug/traceback. Yes, we have logging turned on. Then the upgrade did not insert everything it should? How can I repair that? Manually insert the trigger?  
  > **username_0**: I turned of logging and it seems that the error does not longer occur. So thanks for the pointer. However I still would like to know how to repair the database and then I will accept your answer.  
  > **username_2**: I think Eileen has suggested that adding any Extension can sort out existing trigger problems. i may be paraphrasing incorrectly. but somewhere i read something along the lines of 'just enable any extension, that will sort things, then uninstall it assuming you don't need it'  
  > **username_1**: Yep or turning logging off and back on will effectively repair the logging tables, making sure they are inline with the main CiviCRM schema and therefore avoiding these issues.  
  
  ---
  
  