MySQL error 1054: Unknown column

📦 Install & Upgrade Help
1

After migrating from 7.14 to 8.6.2, when I click ‘View Demo,’ it shows the following error in the suitecrm.log:
MySQL error 1054: Unknown column ‘btpcm_support_demo.btpcm_master_machine_brand_id_c’ in the ‘ON’ clause.
Due to this error, the demo list is not displaying, but the recently viewed data shows without any issues.

Query Failed:
SELECT btpcm_support_demo.id , btpcm_support_demo.modified_user_id , btpcm_support_demo.created_by ,
btpcm_support_demo.assigned_user_id , btpcm_support_demo_cstm.btpcm_master_machine_brand_id_c ,
btpcm_support_demo_cstm.btpcm_master_machine_type_id_c , btpcm_support_demo_cstm.btpcm_master_machine_model_id_c , btpcm_support_demo.name , btpcm_support_demo.date_entered , btpcm_support_demo.date_modified , jt0.user_name modified_by_name , jt0.created_by modified_by_name_owner , ‘Users’ modified_by_name_mod , jt1.user_name created_by_name , jt1.created_by created_by_name_owner , ‘Users’ created_by_name_mod, btpcm_support_demo.description , btpcm_support_demo.deleted , jt2.user_name assigned_user_name , jt2.created_by assigned_user_name_owner , ‘Users’ assigned_user_name_mod, btpcm_support_demo.btpcm_support_demo_number , btpcm_support_demo.type , btpcm_support_demo.status , btpcm_support_demo.priority , btpcm_support_demo.resolution , btpcm_support_demo.work_log , btpcm_support_demo.place , btpcm_support_demo.competition_models , btpcm_support_demo.demo_required_on , btpcm_support_demo.demo_completed_on , btpcm_support_demo.comments , jt3.name brand , jt4.name required_machine , jt5.name required_model , jt6.name btpcm_support_demo_accounts_name, jtl6.btpcm_support_demo_accountsaccounts_ida btpcm_support_demo_accountsaccounts_ida, jt7.name brand_c , btpcm_support_demo_cstm.lead_no_c , btpcm_support_demo_cstm.lead_status_c , btpcm_support_demo_cstm.negotiation_c , btpcm_support_demo_cstm.purchase_c , btpcm_support_demo_cstm.quotation_status_c , btpcm_support_demo_cstm.required_configuration_c , jt8.name required_machine_c , jt9.name required_model_c FROM btpcm_support_demo LEFT JOIN btpcm_support_demo_cstm ON btpcm_support_demo.id = btpcm_support_demo_cstm.id_c LEFT JOIN users jt0 ON btpcm_support_demo.modified_user_id=jt0.id AND jt0.deleted=0

AND jt0.deleted=0 LEFT JOIN users jt1 ON btpcm_support_demo.created_by=jt1.id AND jt1.deleted=0

AND jt1.deleted=0 LEFT JOIN users jt2 ON btpcm_support_demo.assigned_user_id=jt2.id AND jt2.deleted=0

AND jt2.deleted=0 LEFT JOIN btpcm_master_machine_brand jt3 ON btpcm_support_demo.btpcm_master_machine_brand_id_c = jt3.id AND jt3.deleted=0 LEFT JOIN btpcm_master_machine_type jt4 ON btpcm_support_demo.btpcm_master_machine_type_id_c = jt4.id AND jt4.deleted=0 LEFT JOIN btpcm_master_machine_model jt5 ON btpcm_support_demo.btpcm_master_machine_model_id_c = jt5.id AND jt5.deleted=0 LEFT JOIN btpcm_support_demo_accounts_c jtl6 ON btpcm_support_demo.id=jtl6.btpcm_support_demo_accountsbtpcm_support_demo_idb AND jtl6.deleted=0

LEFT JOIN accounts jt6 ON jt6.id=jtl6.btpcm_support_demo_accountsaccounts_ida AND jt6.deleted=0
AND jt6.deleted=0 LEFT JOIN btpcm_master_machine_brand jt7 ON btpcm_support_demo_cstm.btpcm_master_machine_brand_id_c = jt7.id AND jt7.deleted=0 LEFT JOIN btpcm_master_machine_type jt8 ON btpcm_support_demo_cstm.btpcm_master_machine_type_id_c = jt8.id AND jt8.deleted=0 LEFT JOIN btpcm_master_machine_model jt9 ON btpcm_support_demo_cstm.btpcm_master_machine_model_id_c = jt9.id AND jt9.deleted=0 where ( ( btpcm_support_demo.assigned_user_id =‘89799648-5038-2e22-9eb7-659bd04b4e6a’ or EXISTS (SELECT 1
FROM securitygroups secg
INNER JOIN securitygroups_users secu
ON secg.id = secu.securitygroup_id
AND secu.deleted = 0
AND secu.user_id = ‘89799648-5038-2e22-9eb7-659bd04b4e6a’
INNER JOIN securitygroups_records secr
ON secg.id = secr.securitygroup_id
AND secr.deleted = 0
AND secr.module = ‘BTPCM_Support_Demo’
WHERE secr.record_id = btpcm_support_demo.id
AND secg.deleted = 0) ) ) AND btpcm_support_demo.deleted=0 LIMIT 0,21: MySQL error 1054: Unknown column ‘btpcm_support_demo.btpcm_master_machine_brand_id_c’ in ‘on clause’

2

How did you migrate? It seems one of your custom fields is missing in the database.

When you run Quick repair and Rebuild, if you scroll down to the bottom, does it offer to run a query to update database? If so, click the button to go ahead.

3

have SuiteCRM version 7.14 and migrated it to version 8.6.2 using SuiteCRM-8.6.2-7.14-migration.zip. The following vardef configuration exists:

php

Copy code

'btpcm_master_machine_brand_id_c' => 
  array (
    'required' => false,
    'name' => 'btpcm_master_machine_brand_id_c',
    'vname' => 'LBL_BRAND_BTPCM_MASTER_MACHINE_BRAND_ID',
    'type' => 'id',
    'massupdate' => 0,
    'no_default' => false,
    'comments' => '',
    'help' => '',
    'importable' => 'true',
    'duplicate_merge' => 'disabled',
    'duplicate_merge_dom_value' => 0,
    'audited' => false,
    'inline_edit' => true,
    'reportable' => false,
    'unified_search' => false,
    'merge_filter' => 'disabled',
    'len' => 36,
    'size' => '20',
  ),
  'brand' => 
  array (
    'required' => false,
    'source' => 'non-db',
    'name' => 'brand',
    'vname' => 'LBL_BRAND',
    'type' => 'relate',
    'massupdate' => 0,
    'no_default' => false,
    'comments' => '',
    'help' => '',
    'importable' => 'true',
    'duplicate_merge' => 'disabled',
    'duplicate_merge_dom_value' => '0',
    'audited' => false,
    'inline_edit' => true,
    'reportable' => true,
    'unified_search' => false,
    'merge_filter' => 'disabled',
    'len' => '255',
    'size' => '20',
    'id_name' => 'btpcm_master_machine_brand_id_c',
    'ext2' => 'BTPCM_Master_Machine_Brand',
    'module' => 'BTPCM_Master_Machine_Brand',
    'rname' => 'name',
    'quicksearch' => 'enabled',
    'studio' => 'visible',
  ),

The issue is that I am using the same database for both SuiteCRM 7.14 and 8.6.2. However, the 7.14 instance is working correctly, while there are issues with the 8.6.2 version.

4

What makes you think this is possible?

I don’t know many details, but the DB format typically evolves with time, and the upgrade scripts include changes to the database… I would say this attempt at working with both versions on the same DB is very risky, I would definitely not recommend it.

5

I have a snapshot of my SuiteCRM deployment on Digital Ocean, so I can restore it if something goes wrong.

6

Interesting. If you updated your SuiteCRM, then I think you’re using the DB with v8.6. From where v7.14 is using the same DB? Or is it two different databases like suitecrm & suitecrm8?

7

same DB for both suitecrm and suitecrm8

8

I don’t know if you can use like that. Just create another DB for v8.6 like suitecrm8 and update your instance from v7.14

9

Ithink the query genrating in suitecrm 8 causing the error.