[Vtigercrm-developers] MySQL 5.7 Defaults causing issues?

[Alan Lord]

I'm migrating a customer's vtiger systems from a Turnkey Linux server 
with PHP5.3 to a new Ubuntu 16.04 server and am seeing some issues 
regarding the new defaults for MySQL 5.7

The first is that the SQL Mode "only_full_group_by" is enabled and this 
was breaking the ability for me to edit a lead and save, for example a 
new telephone number...

> Wed Aug 10 14:42:40 2016,127 [25950] INFO VT - function insertIntoEntityTable Leads vtiger_table name vtiger_leadscf
> Wed Aug 10 14:42:40 2016,127 [25950] DEBUG VT - Prepared sql query being executed : select leadid from vtiger_leadscf where leadid=?
> Wed Aug 10 14:42:40 2016,127 [25950] DEBUG VT - Prepared sql query parameters : [30527]
> Wed Aug 10 14:42:40 2016,128 [25950] DEBUG VT - Prepared sql query being executed : select * from vtiger_field where tabid in (?) and tablename=? and displaytype in (1,3) and presence in (0,2) group by c
> olumnname
> Wed Aug 10 14:42:40 2016,128 [25950] DEBUG VT - Prepared sql query parameters : [7,vtiger_leadscf]
> Wed Aug 10 14:42:40 2016,128 [25950] INFO VT - PearDatabase ->ADODB error  Query Failed:select * from vtiger_field where tabid in (?) and tablename=? and displaytype in (1,3) and presence in (0,2) group
> by columnname::->[1055]Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'vtigercrm_amber.vtiger_field.fieldid' which is not functionally dependent on columns in GR
> OUP BY clause; this is incompatible with sql_mode=only_full_group_by

If I disable only_full_group_by in the SQL Mode settings then this part 
of the functionality works.

So then I tried to create and save a new Opportunity...

On the new Ubuntu 16.04 server I see the two following db errors reported:

> Thu Aug 11 07:41:52 2016,347 [14395] DEBUG VT - Prepared sql query being executed : insert into vtiger_potential(potentialid,potentialname,potential_no,amount,related_to,closingdate,potentialtype,nextstep,sales_stage,probability,campaignid,forecast_amount,contact_id) values(?,?,?,?,?,?,?,?,?,?,?,?,?)
> Thu Aug 11 07:41:52 2016,347 [14395] DEBUG VT - Prepared sql query parameters : [30531,My Test Opportunity,POT1637,0,,2016-08-21,,,2-Labwork Started,0,0,0,]
> Thu Aug 11 07:41:52 2016,347 [14395] INFO VT - PearDatabase ->ADODB error  Query Failed:insert into vtiger_potential(potentialid,potentialname,potential_no,amount,related_to,closingdate,potentialtype,nextstep,sales_stage,probability,campaignid,forecast_amount,contact_id) values(?,?,?,?,?,?,?,?,?,?,?,?,?)::->[1366]Incorrect integer value: '' for column 'related_to' at row 1
> Thu Aug 11 07:41:52 2016,347 [14395] INFO VT - function insertIntoEntityTable Potentials vtiger_table name vtiger_potentialscf

The related_to field is the Organisation link. This column is an 
Integer. I think that having a default of NULL is probably the issue. 
Perhaps it should have a default of 0 instead?

and then

> Thu Aug 11 07:41:52 2016,361 [14395] DEBUG VT - Prepared sql query parameters : [30531,0,0,,0,,,,,,,,,,0,,,,,,,,,,,,0,,,,,,,,,,,,,,,,,,,,,,,,,,,,0,,0,,,0,,,,,0,,0,0,0]
> Thu Aug 11 07:41:52 2016,374 [14395] INFO VT - PearDatabase ->ADODB error  Query Failed:insert into vtiger_potentialscf(potentialid,cf_563,cf_564,cf_565,cf_566,cf_570,cf_573,cf_575,cf_576,cf_586,cf_587,cf_588,cf_589,cf_590,cf_603,cf_605,cf_606,cf_607,cf_608,cf_609,cf_610,cf_611,cf_612,cf_613,cf_614,cf_615,cf_616,cf_617,cf_618,cf_619,cf_620,cf_621,cf_622,cf_623,cf_624,cf_625,cf_626,cf_627,cf_628,cf_629,cf_630,cf_631,cf_632,cf_633,cf_634,cf_635,cf_636,cf_637,cf_638,cf_639,cf_640,cf_641,cf_642,cf_643,cf_645,cf_656,cf_671,cf_800,cf_805,cf_806,cf_882,cf_884,cf_886,cf_887,cf_890,cf_891,cf_893,cf_1002,cf_1004) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)::->[1452]Cannot add or update a child row: a foreign key constraint fails (`vtigercrm_amber`.`vtiger_potentialscf`, CONSTRAINT `fk_1_vtiger_potentialscf` FOREIGN KEY (`potentialid`) REFERENCES `vtiger_potential` (`potentialid`) ON DELETE CASCADE)
> Thu Aug 11 07:41:52 2016,375 [14395] INFO VT - PearDatabase ->TRANS  Rolled Back
> Thu Aug 11 07:41:52 2016,384 [14395] INFO VT - PearDatabase ->TRANS  Completed

Which I have no idea about.

the *exact* same system, on the Turnkey Linux server works fine.

Any ideas?

Thanks

Al