vt7 dev release - Error in Migration script 660_to_700.php

On line 1279 this commend will never work as the query syntax is invalid:

$db->pquery('ALTER TABLE com_vtiger_workflows ADD COLUMN status SET DEFAULT 1', array());

needs to be:

ALTER TABLE com_vtiger_workflows ADD COLUMN status INT(255) DEFAULT 1; (If it does indeed need to be an INT(255)?)

This column actually needs to be created in the 650_to_660.php script else that one will fail where the part starts with:

// Migration for #261 (closed) - vtiger_portalinfo doesn't update contact

Here's another in the 660_to_770.php

This snippet:

	if (!in_array('systemtemplate', $columns)) {
		$db->pquery('ALTER TABLE vtiger_emailtemplates ADD COLUMN systemtemplate INT(1) NOT NULL DEFAULT 0', array());
	}
	if (!in_array('templatepath', $columns)) {
		$db->pquery('ALTER TABLE vtiger_emailtemplates ADD COLUMN templatepath VARCHAR(100) AFTER templatename', array());
	}
	if (!in_array('module', $columns)) {
		$db->pquery('ALTER TABLE vtiger_emailtemplates ADD COLUMN module VARCHAR(100)', array());
	}```

starts around line 1770.

Yet, around line 830 is the following:

```		$db->pquery('INSERT INTO vtiger_emailtemplates(foldername,templatename,subject,description,body,systemtemplate,templateid) values(?,?,?,?,?,?,?)', array('Public', 'ToDo Reminder', 'Activity Reminder', 'Reminder', $body, '1', $db->getUniqueID('vtiger_emailtemplates')));

which fails because of the missing field "systemtemplate".

This issue solved in previous commits @lord_alan. Please have a look.

Thanks @satish.dvnk. It would be helpful if you mention, in each individual commit, the issue(s) which it addresses. That way it is easy to check and reference what each commit is actually for... You can make multiple small commits, then create one merge request from all of them. ;-)

@satish.dvnk. As I mentioned earlier, I think you need to add the status column in the 650_to_660.php file. Please read line 73 of this file:

$workflowModel->set('status', 1);

I also believe you have a typo in your code to create the new column...

	$columns = $db->getColumnNames('com_vtiger_workflows');
	if (in_array('status', $columns)) {
		$db->pquery('ALTER TABLE com_vtiger_workflows MODIFY COLUMN status INT(11)', array());
		$db->pquery('ALTER TABLE com_vtiger_workflows ALTER COLUMN status SET DEFAULT 1', array());
		$db->pquery('UPDATE com_vtiger_workflows SET status=? WHERE status IS NULL', array(1));
	}

Should that not be if (!in_array('status', $columns)) { ... ?

(Note the "!")

• Why are you not "ADDing" the column? It does not exist in a 6.5.0 database?
• Why is this column INT(11)? Is this purely a 0 or 1 status flag? If so it should be a TINYINT
• I notice another typo (incorrect "ALTER" in 2nd position - should be MODIFY or CHANGE): $db->pquery('ALTER TABLE com_vtiger_workflows ALTER COLUMN status SET DEFAULT 1', array());

Please, check these things ;-)

You can also do both the SET DEFAULT and CHANGE the TYPE in one query rather than two.

@lord_alan Can you check this http://code.vtiger.com/vtiger/vtigercrm/blob/7.0.0/modules/Migration/schema/660_to_700.php#L1371

But the "status" column doesn't exist in 6.5.0 the line above will do nothing.

And there is still your error in http://code.vtiger.com/vtiger/vtigercrm/blob/7.0.0/modules/Migration/schema/660_to_700.php#L1373 (Two ALTERs).

mentioned in merge request !180 (merged)

mentioned in commit bf82681f

mentioned in commit cdeb3879

@lord_alan, Please check the above commit from master

@satish.dvnk @vtigerosm There are still 2 unanswered questions which do not make any sense:

1. The status column does not exist in a clean vtiger 6.5.0 installation so the commit above will not help during migration - it will silently fail.
2. What is the purpose of this "status" column? If it is, as it would appear, simply a 1 or 0 then why are you using INT(11)? It should be a TINYINT.

In the previous migration script (650_660.php) you are setting this "status" value but the column doesn't exist: http://code.vtiger.com/vtiger/vtigercrm/blob/cdeb38795147d2e49ca40f2b04d42f2dba5ed30d/modules/Migration/schema/650_to_660.php#L73

mentioned in merge request !183 (merged)

Thanks @lord_alan. Can you please check this http://code.vtiger.com/vtiger/vtigercrm/merge_requests/183/diffs#1416c9bafc378f7a54dad27ba5cb2c49f78f4726_1415_1414

@satish.dvnk That looks better. but presumably you should repeat the line $db->pquery('UPDATE com_vtiger_workflows SET status=? WHERE status IS NULL', array(1)); after the ADD COLUMN statement?

Also, please review this: http://code.vtiger.com/vtiger/vtigercrm/blob/cdeb38795147d2e49ca40f2b04d42f2dba5ed30d/modules/Migration/schema/650_to_660.php#L73.

Is this script trying to set the "status" column? If so then this file 650_to_660 runs before the 660_to_700 so it will not work as the status column has not yet been created.

That looks better. but presumably you should repeat the line

$db->pquery('UPDATE com_vtiger_workflows SET status=? WHERE status IS NULL', array(1));

No need to repeat @lord_alan, here 1 will be default value. So while adding column to a table, default value 1 sit as column value for every row in that table.

@satish.dvnk Good point - Thanks!

But what about the other bit in the 650_to_660 migration script?

But what about the other bit in the 650_to_660 migration script?

It wont fail because migration / installation will be run when $dieOnError = false (PearDatabase.php)

Yes, but will the $workflowModel->save(); actually do anything at all because the column doesn't exist in the database yet? Surely the db query to save the Workflow modification will not work; the script might not die but the Workflow changes will not be saved?

Yes @lord_alan. You are correct. I will update the script.

mentioned in commit 8530e563

mentioned in commit d2a9f8c6

mentioned in commit 2092b16e

mentioned in merge request !185 (merged)

Status changed to closed by commit 850f576f