vt 7.4 schema migration script not being executed in migration and error in query [urgent: this leaves vtiger unusable]

modules/Migration/schema/730_to_740.php doesn't seem to be executed in migration from vt 7.3

Since the row userlabel in table vtiger_users is not created, you get the following query error:

Error filling in user array: Query Failed:SELECT id, user_name,first_name,last_name,userlabel from vtiger_users WHERE status=? and (user_name != 'admin' OR is_owner=1) order by user_name ASC::ADODB error Error filling in user array: Query Failed:SELECT id, user_name,first_name,last_name,userlabel from vtiger_users WHERE status=? and (user_name != 'admin' OR is_owner=1) order by user_name ASC::->Unknown column 'userlabel' in 'field list'

Furthermore, the update query generated here has an error:

$sql = "UPDATE vtiger_users SET $fieldName = TRIM(CONCAT(".implode(' ', $entityFieldNames)."))";

the first argument in the implode function should be this: ', ' and not this ' '

It seems this script was not even tested once, this would have popped out immediately.

I think someone reported this same error in the official demo a few days ago.

@ruben.estrada We have validated the script pre-release. I am not sure how did this got araised. Will re-check and update asap.

mentioned in merge request !830 (merged)

Status changed to closed by commit f2b28ff0

@ruben.estrada

This flaw is due to the values of current_version is vtiger_version table of 7.3.0 instance, which is set to 7.3 instead of 7.3.0. Which is failing to read the migration script for DB changes.

RCA : vitger7.3.0 package glitch.

@uma.s this line is still a problem: https://code.vtiger.com/vtiger/vtigercrm/blob/7.4.0/modules/Migration/schema/730_to_740.php#L41

you will get an error when running that query.

@ruben.estrada This has been validated, Works perfectly fine. Please do use the latest migration script and update your observations.

Downloaded latest migration files. Still getting error in listviews of contacts, organisations, etc - Unknown column 'userlabel' in 'field list'

@uma.s The MySql function CONCAT needs that its arguments are separated by comma. Yesterday I saw the query error and fixed it as I suggested above.

The upgrade process continues as if there were no errors but I really don't see how that could work correctly. Its a syntax error. Turn on the logs and you'll see what I mean.

@david.pettigrew that is probably beacause of a missing slash here: https://code.vtiger.com/vtiger/vtigercrm/blob/master/modules/Migration/schema/73_to_740.php

It should be: include_once dirname(__FILE__)."/730_to_740.php";

My guess is that because it is an "include_once" instead of a "require_once", it just fails silently.

@ruben.estrada Thanks Ruben. I've implemented both your above changes and redone the migration. When I run vtiger it still produces the same error. I also just noticed the following errors before the migration terminates:-

[Wed Jul 21 19:25:54.534096 2021] [php7:warn] [pid 2355] [client 192.168.1.208:49492] PHP Warning: Declaration of EmailTemplates_Module_Model::saveRecord(EmailTemplates_Record_Model $recordModel) should be compatible with Vtiger_Module_Model::saveRecord(Vtiger_Record_Model $recordModel) in /var/www/html/vtigercrm/modules/EmailTemplates/models/Module.php on line 35, referer: http://192.168.1.213/vtigercrm/index.php

[Wed Jul 21 19:25:54.534388 2021] [php7:warn] [pid 2355] [client 192.168.1.208:49492] PHP Warning: Declaration of EmailTemplates_Module_Model::deleteRecord(EmailTemplates_Record_Model $recordModel) should be compatible with Vtiger_Module_Model::deleteRecord(Vtiger_Record_Model $recordModel) in /var/www/html/vtigercrm/modules/EmailTemplates/models/Module.php on line 68, referer: http://192.168.1.213/vtigercrm/index.php

These errors are also present in the vtiger_error.log. Not sure if it's related or something else.

@uma.s

I've done my third migration from 73 to 74. This time I set $db->setDebug(true) and here's the query error I mention:

Query: UPDATE vtiger_users SET userlabel = TRIM(CONCAT(first_name last_name)) failed. Incorrect parameters in the call to native function 'concat'
1583: Incorrect parameters in the call to native function 'concat'
   ADOConnection._Execute(UPDATE vtiger_users SET userlabel = TRIM(CONCAT(first_name last_name)), false) % line 1229, file: adodb.inc.php
ADOConnection.Execute(UPDATE vtiger_users SET userlabel = TRIM(CONCAT(first_name last_name))) % line  357, file: PearDatabase.php
PearDatabase.pquery(UPDATE vtiger_users SET userlabel = TRIM(CONCAT(first_name last_name)), Array[0]) % line   54, file: 730_to_740.php

Notice how there is no coma between first_name and last_name

What happens is that the userlabel column in the vtiger_users table ends up with only nulls.

For some reason the error didn't make it into the logs (I don't know why).

about this:

include_once dirname(__FILE__)."730_to_740.php";

now I'm 100% it's wrong. dirname() does not return a trailing slash. It says so in the documentation and I've also tested it.

Both errors have to be fixed.

@david.pettigrew those errors you show are just warnings. Those shouldn't affect the migration.

create a file with these contents and put it in your root vtiger directory:

<?php

require_once 'vtlib/Vtiger/Module.php';
require_once 'include/utils/VtlibUtils.php';
require_once 'config.inc.php';
require_once 'includes/Loader.php';
require_once 'modules/Emails/mail.php';
require_once 'vtlib/Vtiger/Mailer.php';
require_once 'libraries/ToAscii/ToAscii.php';
vimport ('includes.runtime.EntryPoint');

//para hacer operaciones con permisos de admin.
$current_user = Users::getActiveAdminUser();

if (defined('VTIGER_UPGRADE') || true) {
	global $current_user, $adb;
    $db = PearDatabase::getInstance();
    $db->setDebug(true);
 
    $eventManager = new VTEventsManager($db);
    $className = 'Vtiger_RecordLabelUpdater_Handler';
    $eventManager->unregisterHandler($className);
    echo "Unregistered record label update handler.<br>";

    $moduleName = 'Users';
    $moduleModel = Vtiger_Module_Model::getInstance($moduleName);
    $fieldName = 'userlabel';
    $blockModel = Vtiger_Block_Model::getInstance('LBL_MORE_INFORMATION', $moduleModel);
    if ($blockModel) {
        $fieldModel = Vtiger_Field_Model::getInstance($fieldName, $moduleModel);
        if (!$fieldModel) {
            $fieldModel				= new Vtiger_Field();
            $fieldModel->name		= $fieldName;
            $fieldModel->label		= 'User Label';
            $fieldModel->table		= 'vtiger_users';
            $fieldModel->columntype = 'VARCHAR(255)';
            $fieldModel->typeofdata = 'V~O';
            $fieldModel->displaytype= 3;
            $blockModel->addField($fieldModel);
            echo "<br>Successfully added <b>$fieldName</b> field to <b>$moduleName</b><br>";
        }
    }
    
    $entityFields = Vtiger_Functions::getEntityModuleInfo($moduleName);
    $entityFieldNames  = explode(',', $entityFields['fieldname']);
    $sql = "UPDATE vtiger_users SET $fieldName = TRIM(CONCAT(".implode(', ', $entityFieldNames)."))";
    $db->pquery($sql, array());
    
    Vtiger_Access::syncSharingAccess();
}

Then run that file through your browser. That should fix the problem.

@ruben.estrada Many thanks for your help Ruben. Your code as above fixed this issue for me.

Status changed to reopened

@uma.s I reopened the issue because of the two remaining problems.

@ruben.estrada Thank you! for your insight, I will review this closely to see if update is necessary.

mentioned in merge request !831 (merged)

Status changed to closed by commit 475adb6b

@all Migration glitch has been resolved. So our patches are been updated.

The users on vtiger 7.4.0 can resolve the issue by running the above file mentioned by @ruben.estrada from your browser.

Thanks for taking care of it @uma.s :)