Here's another vTiger finding that cost me a couple days of productivity! After upgrading Vtiger a few times (probably started in version 6 a number of years ago) I was able to get up to version 7.1RC and apply the first 2 hotfixes. However, as soon as I applied the 3rd hotfix named "vtigercrm7.1.0-hotfix3.zip" we suddenly could no longer view any customer records. The data was still there because certainly summaries still showed the correct numbers, and all the Vtiger user logins still worked.

After much trial & error I finally tracked the bug down to this failed SQL request:
SELECT vtiger_account.accountname, vtiger_accountbillads.bill_city, vtiger_account.website, vtiger_account.phone, vtiger_crmentity.smownerid, vtiger_account.accountid, vtiger_accounts_user_field.starred FROM vtiger_account INNER JOIN vtiger_crmentity ON vtiger_account.accountid = vtiger_crmentity.crmid INNER JOIN vtiger_accountbillads ON vtiger_account.accountid = vtiger_accountbillads.accountaddressid LEFT JOIN vtiger_users ON vtiger_crmentity.smownerid = vtiger_users.id LEFT JOIN vtiger_groups ON vtiger_crmentity.smownerid = vtiger_groups.groupid INNER JOIN vtiger_accounts_user_field ON vtiger_account.accountid = vtiger_accounts_user_field. WHERE vtiger_crmentity.deleted=0 AND vtiger_account.accountid > 0 ORDER BY vtiger_crmentity.modifiedtime DESC LIMIT 0,21

This issue was that vtiger_accounts_user_field. should have been vtiger_accounts_user_field.recordid

Turns out the offending code was in /var/www/html/vtigercrm/vtlib/Vtiger/Functions.php where:
Code
	public static function getUserSpecificTableName($moduleName) {
		return 'vtiger_crmentity_user_field';
	}
needed to be turned into
Code
        public static function getUserSpecificTableName($moduleName) {
                $moduleName = strtolower($moduleName);
                if ($moduleName == "events") {
                        $moduleName = "calendar";
                         }
                return "vtiger_".$moduleName.'_user_field';
        }

Viola! All good now...



Also, for MariaDB debugging:

Database Debugging


Nano the file include/database/PearDatabase.php
Look for the line $adb->connect(); (it's right near the end of the file)

You have 2 options:

1 - add this line:
$adb->setDebug(true);
This line will output EVERY Query sent to the database.

2 - add this line:
$adb->setDieOnError(true);
This line will only output a detailed error report if an SQL query generates an error.

Take the line out when you're done debugging - it could cause unexpected behavior.

Last edited by Toner; 09/29/23 06:05 PM.
[Linked Image from web.atcomsystems.ca]
Looking for a VoIP Phone Canada provider? Put Atcom's valuable VoIP expertise to work for your business today!