[SOLVED] MYSQL error migration 7.3.0

Hello,

We just migrated to version 7.3.0.

Since then, we have the following error:

2018-11-27 11:25:21.473 ***** ERROR ***** [V7.3.0] Exception-[42000] SQLSTATE[42000]: Syntax error or access violation: 1253 COLLATION 'utf8_general_ci' is not valid for CHARACTER SET 'latin1'
2018-11-27 11:25:21.473 ***** ERROR ***** [V7.3.0] For query : SELECT planningelement.*
FROM
( SELECT cast(id as CHAR) COLLATE utf8_general_ci as id,idProject,cast(refType AS CHAR) COLLATE utf8_general_ci as refType,refId,refName,topId,topRefType,topRefId,
priority,elementary,idle,done,cancelled,idPlanningMode,idBill,
initialStartDate,validatedStartDate,validatedStartFraction,plannedStartDate,plannedStartFraction,realStartDate,
initialEndDate,validatedEndDate,validatedEndFraction,plannedEndDate,plannedEndFraction,realEndDate,
latestStartDate,latestEndDate,
initialDuration,validatedDuration,plannedDuration,realDuration,
initialWork,validatedWork,assignedWork,plannedWork,leftWork,realWork,
validatedCost,assignedCost,plannedCost,leftCost,realCost,
progress,expectedProgress,wbs,wbsSortable,isOnCriticalPath,notPlannedWork, needReplan,
null as idType, null as idStatus, null as idResource, 0 as isGlobal
FROM planningelement
WHERE (planningelement.idProject not in (0) or planningelement.idProject is null ) and (planningelement.idProject not in (0) or planningelement.idProject is null or (1=4) ) and (planningelement.idProject not in (0) or planningelement.idProject is null or (1=3) )
UNION
SELECT coalesce(cast( (pex.id+1000000000) AS CHAR) COLLATE utf8_general_ci,concat('Action','_',action.id)) as id, action.idProject as idProject, cast('Action' AS CHAR) COLLATE utf8_general_ci as refType, action.id as refId, action.name as refName, null as topId, 'Project' as topRefType, action.idProject as topRefId,
null as priority, 1 as elementary, action.idle as idle, action.done as done, action.cancelled as cancelled, 8 as idPlanningMode, null as idBill,
null as initialStartDate, null as validatedStartDate, 0 as validatedStartFraction, action.actualDueDate as plannedStartDate, 0 as plannedStartFraction, action.handledDate as realStartDate,
null as initialEndDate, action.initialDueDate as validatedEndDate, 1 as validatedEndFraction, action.actualDueDate as plannedEndDate, 1 as plannedEndFraction, action.doneDate as realEndDate, null as latestStartDate, null as latestEndDate,
null as initialDuration, 1 as validatedDuration, null as plannedDuration, null as realDuration,
null as initialWork, null as validatedWork, null as assignedWork, null as plannedWork, null as leftWork, null as realWork,
null as validatedCost, null as assignedCost, null as plannedCost, null as leftCost, null as realCost,
null as progress, null as expectedProgress, coalesce(pex.wbs,concat(pe.wbs,'._#',action.id)) as wbs, coalesce(pex.wbsSortable,concat(pe.wbsSortable,'._#',action.id)) as wbsSortable, null as isOnCriticalPath, null as notPlannedWork, null as needReplan, action.idActionType as idType, action.idStatus as idStatus, action.idResource as idResource, 1 as isGlobal
FROM action LEFT JOIN planningelement AS pe ON pe.refType='Project' and pe.refId=action.idProject LEFT JOIN planningelementextension as pex ON pex.refType='Action' and pex.refId=action.id WHERE (action.idProject not in (0) or action.idProject is null ) and (action.idProject not in (0) or action.idProject is null or (action.idResource='1') ) and (action.idProject not in (0) or action.idProject is null or (action.idUser='1') ) and action.idProject not in (0)
UNION
SELECT coalesce(cast( (pex.id+1000000000) AS CHAR) COLLATE utf8_general_ci,concat('Decision','_',decision.id)) as id, decision.idProject as idProject, cast('Decision' AS CHAR) COLLATE utf8_general_ci as refType, decision.id as refId, decision.name as refName, null as topId, 'Project' as topRefType, decision.idProject as topRefId,
null as priority, 1 as elementary, decision.idle as idle, decision.done as done, decision.cancelled as cancelled, 8 as idPlanningMode, null as idBill,
null as initialStartDate, null as validatedStartDate, 0 as validatedStartFraction, decision.decisionDate as plannedStartDate, 0 as plannedStartFraction, decision.decisionDate as realStartDate,
null as initialEndDate, decision.decisionDate as validatedEndDate, 1 as validatedEndFraction, decision.decisionDate as plannedEndDate, 1 as plannedEndFraction, decision.decisionDate as realEndDate, null as latestStartDate, null as latestEndDate,
null as initialDuration, 1 as validatedDuration, null as plannedDuration, null as realDuration,
null as initialWork, null as validatedWork, null as assignedWork, null as plannedWork, null as leftWork, null as realWork,
null as validatedCost, null as assignedCost, null as plannedCost, null as leftCost, null as realCost,
null as progress, null as expectedProgress, coalesce(pex.wbs,concat(pe.wbs,'._#',decision.id)) as wbs, coalesce(pex.wbsSortable,concat(pe.wbsSortable,'._#',decision.id)) as wbsSortable, null as isOnCriticalPath, null as notPlannedWork, null as needReplan, decision.idDecisionType as idType, decision.idStatus as idStatus, decision.idResource as idResource, 1 as isGlobal
FROM decision LEFT JOIN planningelement AS pe ON pe.refType='Project' and pe.refId=decision.idProject LEFT JOIN planningelementextension as pex ON pex.refType='Decision' and pex.refId=decision.id WHERE (decision.idProject not in (0) or decision.idProject is null ) and (decision.idProject not in (0) or decision.idProject is null or (decision.idResource='1') ) and (decision.idProject not in (0) or decision.idProject is null or (decision.idUser='1') ) and decision.idProject not in (0)
UNION
SELECT coalesce(cast( (pex.id+1000000000) AS CHAR) COLLATE utf8_general_ci,concat('Delivery','_',delivery.id)) as id, delivery.idProject as idProject, cast('Delivery' AS CHAR) COLLATE utf8_general_ci as refType, delivery.id as refId, delivery.name as refName, null as topId, 'Project' as topRefType, delivery.idProject as topRefId,
null as priority, 1 as elementary, delivery.idle as idle, delivery.done as done, delivery.cancelled as cancelled, 8 as idPlanningMode, null as idBill,
null as initialStartDate, null as validatedStartDate, 0 as validatedStartFraction, delivery.plannedDate as plannedStartDate, 0 as plannedStartFraction, delivery.handledDateTime as realStartDate,
null as initialEndDate, delivery.initialDate as validatedEndDate, 1 as validatedEndFraction, delivery.plannedDate as plannedEndDate, 1 as plannedEndFraction, delivery.realDate as realEndDate, null as latestStartDate, null as latestEndDate,
null as initialDuration, 1 as validatedDuration, null as plannedDuration, null as realDuration,
null as initialWork, null as validatedWork, null as assignedWork, null as plannedWork, null as leftWork, null as realWork,
null as validatedCost, null as assignedCost, null as plannedCost, null as leftCost, null as realCost,
null as progress, null as expectedProgress, coalesce(pex.wbs,concat(pe.wbs,'._#',delivery.id)) as wbs, coalesce(pex.wbsSortable,concat(pe.wbsSortable,'._#',delivery.id)) as wbsSortable, null as isOnCriticalPath, null as notPlannedWork, null as needReplan, delivery.idDeliveryType as idType, delivery.idStatus as idStatus, delivery.idResource as idResource, 1 as isGlobal
FROM delivery LEFT JOIN planningelement AS pe ON pe.refType='Project' and pe.refId=delivery.idProject LEFT JOIN planningelementextension as pex ON pex.refType='Delivery' and pex.refId=delivery.id WHERE (delivery.idProject not in (0) or delivery.idProject is null ) and (delivery.idProject not in (0) or delivery.idProject is null or (delivery.idResource='1') ) and (delivery.idProject not in (0) or delivery.idProject is null or (delivery.idUser='1') ) and delivery.idProject not in (0)
UNION
SELECT coalesce(cast( (pex.id+1000000000) AS CHAR) COLLATE utf8_general_ci,concat('Issue','_',issue.id)) as id, issue.idProject as idProject, cast('Issue' AS CHAR) COLLATE utf8_general_ci as refType, issue.id as refId, issue.name as refName, null as topId, 'Project' as topRefType, issue.idProject as topRefId,
null as priority, 1 as elementary, issue.idle as idle, issue.done as done, issue.cancelled as cancelled, 8 as idPlanningMode, null as idBill,
null as initialStartDate, null as validatedStartDate, 0 as validatedStartFraction, issue.actualEndDate as plannedStartDate, 0 as plannedStartFraction, issue.handledDate as realStartDate,
null as initialEndDate, issue.initialEndDate as validatedEndDate, 1 as validatedEndFraction, issue.actualEndDate as plannedEndDate, 1 as plannedEndFraction, issue.doneDate as realEndDate, null as latestStartDate, null as latestEndDate,
null as initialDuration, 1 as validatedDuration, null as plannedDuration, null as realDuration,
null as initialWork, null as validatedWork, null as assignedWork, null as plannedWork, null as leftWork, null as realWork,
null as validatedCost, null as assignedCost, null as plannedCost, null as leftCost, null as realCost,
null as progress, null as expectedProgress, coalesce(pex.wbs,concat(pe.wbs,'._#',issue.id)) as wbs, coalesce(pex.wbsSortable,concat(pe.wbsSortable,'._#',issue.id)) as wbsSortable, null as isOnCriticalPath, null as notPlannedWork, null as needReplan, issue.idIssueType as idType, issue.idStatus as idStatus, issue.idResource as idResource, 1 as isGlobal
FROM issue LEFT JOIN planningelement AS pe ON pe.refType='Project' and pe.refId=issue.idProject LEFT JOIN planningelementextension as pex ON pex.refType='Issue' and pex.refId=issue.id WHERE (issue.idProject not in (0) or issue.idProject is null ) and (issue.idProject not in (0) or issue.idProject is null or (issue.idResource='1') ) and (issue.idProject not in (0) or issue.idProject is null or (issue.idUser='1') ) and issue.idProject not in (0)
UNION
SELECT coalesce(cast( (pex.id+1000000000) AS CHAR) COLLATE utf8_general_ci,concat('Opportunity','_',opportunity.id)) as id, opportunity.idProject as idProject, cast('Opportunity' AS CHAR) COLLATE utf8_general_ci as refType, opportunity.id as refId, opportunity.name as refName, null as topId, 'Project' as topRefType, opportunity.idProject as topRefId,
null as priority, 1 as elementary, opportunity.idle as idle, opportunity.done as done, opportunity.cancelled as cancelled, 8 as idPlanningMode, null as idBill,
null as initialStartDate, null as validatedStartDate, 0 as validatedStartFraction, opportunity.actualEndDate as plannedStartDate, 0 as plannedStartFraction, opportunity.handledDate as realStartDate,
null as initialEndDate, opportunity.initialEndDate as validatedEndDate, 1 as validatedEndFraction, opportunity.actualEndDate as plannedEndDate, 1 as plannedEndFraction, opportunity.doneDate as realEndDate, null as latestStartDate, null as latestEndDate,
null as initialDuration, 1 as validatedDuration, null as plannedDuration, null as realDuration,
null as initialWork, null as validatedWork, null as assignedWork, null as plannedWork, null as leftWork, null as realWork,
null as validatedCost, null as assignedCost, null as plannedCost, null as leftCost, null as realCost,
null as progress, null as expectedProgress, coalesce(pex.wbs,concat(pe.wbs,'._#',opportunity.id)) as wbs, coalesce(pex.wbsSortable,concat(pe.wbsSortable,'._#',opportunity.id)) as wbsSortable, null as isOnCriticalPath, null as notPlannedWork, null as needReplan, opportunity.idOpportunityType as idType, opportunity.idStatus as idStatus, opportunity.idResource as idResource, 1 as isGlobal
FROM opportunity LEFT JOIN planningelement AS pe ON pe.refType='Project' and pe.refId=opportunity.idProject LEFT JOIN planningelementextension as pex ON pex.refType='Opportunity' and pex.refId=opportunity.id WHERE (opportunity.idProject not in (0) or opportunity.idProject is null ) and (opportunity.idProject not in (0) or opportunity.idProject is null or (opportunity.idResource='1') ) and (opportunity.idProject not in (0) or opportunity.idProject is null or (opportunity.idUser='1') ) and opportunity.idProject not in (0)
UNION
SELECT coalesce(cast( (pex.id+1000000000) AS CHAR) COLLATE utf8_general_ci,concat('Question','_',question.id)) as id, question.idProject as idProject, cast('Question' AS CHAR) COLLATE utf8_general_ci as refType, question.id as refId, question.name as refName, null as topId, 'Project' as topRefType, question.idProject as topRefId,
null as priority, 1 as elementary, question.idle as idle, question.done as done, question.cancelled as cancelled, 8 as idPlanningMode, null as idBill,
null as initialStartDate, null as validatedStartDate, 0 as validatedStartFraction, question.actualDueDate as plannedStartDate, 0 as plannedStartFraction, question.handledDate as realStartDate,
null as initialEndDate, question.initialDueDate as validatedEndDate, 1 as validatedEndFraction, question.actualDueDate as plannedEndDate, 1 as plannedEndFraction, question.doneDate as realEndDate, null as latestStartDate, null as latestEndDate,
null as initialDuration, 1 as validatedDuration, null as plannedDuration, null as realDuration,
null as initialWork, null as validatedWork, null as assignedWork, null as plannedWork, null as leftWork, null as realWork,
null as validatedCost, null as assignedCost, null as plannedCost, null as leftCost, null as realCost,
null as progress, null as expectedProgress, coalesce(pex.wbs,concat(pe.wbs,'._#',question.id)) as wbs, coalesce(pex.wbsSortable,concat(pe.wbsSortable,'._#',question.id)) as wbsSortable, null as isOnCriticalPath, null as notPlannedWork, null as needReplan, question.idQuestionType as idType, question.idStatus as idStatus, question.idResource as idResource, 1 as isGlobal
FROM question LEFT JOIN planningelement AS pe ON pe.refType='Project' and pe.refId=question.idProject LEFT JOIN planningelementextension as pex ON pex.refType='Question' and pex.refId=question.id WHERE (question.idProject not in (0) or question.idProject is null ) and (question.idProject not in (0) or question.idProject is null or (question.idResource='1') ) and (question.idProject not in (0) or question.idProject is null or (question.idUser='1') ) and question.idProject not in (0)
UNION
SELECT coalesce(cast( (pex.id+1000000000) AS CHAR) COLLATE utf8_general_ci,concat('Risk','_',risk.id)) as id, risk.idProject as idProject, cast('Risk' AS CHAR) COLLATE utf8_general_ci as refType, risk.id as refId, risk.name as refName, null as topId, 'Project' as topRefType, risk.idProject as topRefId,
null as priority, 1 as elementary, risk.idle as idle, risk.done as done, risk.cancelled as cancelled, 8 as idPlanningMode, null as idBill,
null as initialStartDate, null as validatedStartDate, 0 as validatedStartFraction, risk.actualEndDate as plannedStartDate, 0 as plannedStartFraction, risk.handledDate as realStartDate,
null as initialEndDate, risk.initialEndDate as validatedEndDate, 1 as validatedEndFraction, risk.actualEndDate as plannedEndDate, 1 as plannedEndFraction, risk.doneDate as realEndDate, null as latestStartDate, null as latestEndDate,
null as initialDuration, 1 as validatedDuration, null as plannedDuration, null as realDuration,
null as initialWork, null as validatedWork, null as assignedWork, null as plannedWork, null as leftWork, null as realWork,
null as validatedCost, null as assignedCost, null as plannedCost, null as leftCost, null as realCost,
null as progress, null as expectedProgress, coalesce(pex.wbs,concat(pe.wbs,'._#',risk.id)) as wbs, coalesce(pex.wbsSortable,concat(pe.wbsSortable,'._#',risk.id)) as wbsSortable, null as isOnCriticalPath, null as notPlannedWork, null as needReplan, risk.idRiskType as idType, risk.idStatus as idStatus, risk.idResource as idResource, 1 as isGlobal
FROM risk LEFT JOIN planningelement AS pe ON pe.refType='Project' and pe.refId=risk.idProject LEFT JOIN planningelementextension as pex ON pex.refType='Risk' and pex.refId=risk.id WHERE (risk.idProject not in (0) or risk.idProject is null ) and (risk.idProject not in (0) or risk.idProject is null or (risk.idResource='1') ) and (risk.idProject not in (0) or risk.idProject is null or (risk.idUser='1') ) and risk.idProject not in (0)
UNION
SELECT coalesce(cast( (pex.id+1000000000) AS CHAR) COLLATE utf8_general_ci,concat('Ticket','_',ticket.id)) as id, ticket.idProject as idProject, cast('Ticket' AS CHAR) COLLATE utf8_general_ci as refType, ticket.id as refId, ticket.name as refName, null as topId, 'Project' as topRefType, ticket.idProject as topRefId,
null as priority, 1 as elementary, ticket.idle as idle, ticket.done as done, ticket.cancelled as cancelled, 8 as idPlanningMode, null as idBill,
null as initialStartDate, null as validatedStartDate, 0 as validatedStartFraction, ticket.actualDueDateTime as plannedStartDate, 0 as plannedStartFraction, ticket.handledDateTime as realStartDate,
null as initialEndDate, ticket.initialDueDateTime as validatedEndDate, 1 as validatedEndFraction, ticket.actualDueDateTime as plannedEndDate, 1 as plannedEndFraction, ticket.doneDateTime as realEndDate, null as latestStartDate, null as latestEndDate,
null as initialDuration, 1 as validatedDuration, null as plannedDuration, null as realDuration,
null as initialWork, we.plannedWork as validatedWork, null as assignedWork, we.leftWork+we.realWork as plannedWork, we.leftWork as leftWork, we.realWork as realWork,
null as validatedCost, null as assignedCost, null as plannedCost, null as leftCost, null as realCost,
null as progress, null as expectedProgress, coalesce(pex.wbs,concat(pe.wbs,'._#',ticket.id)) as wbs, coalesce(pex.wbsSortable,concat(pe.wbsSortable,'._#',ticket.id)) as wbsSortable, null as isOnCriticalPath, null as notPlannedWork, null as needReplan, ticket.idTicketType as idType, ticket.idStatus as idStatus, ticket.idResource as idResource, 1 as isGlobal
FROM ticket LEFT JOIN planningelement AS pe ON pe.refType='Project' and pe.refId=ticket.idProject LEFT JOIN planningelementextension as pex ON pex.refType='Ticket' and pex.refId=ticket.id
LEFT JOIN workelement AS we ON we.refType='Ticket' AND we.refId=ticket.id WHERE (ticket.idProject not in (0) or ticket.idProject is null ) and (ticket.idProject not in (0) or ticket.idProject is null or (ticket.idResource='1') ) and (ticket.idProject not in (0) or ticket.idProject is null or (ticket.idUser='1') ) and ticket.idProject not in (0)) as planningelement
WHERE planningelement.idle=0 and (1=1) and planningelement.idProject in (0, 271) and planningelement.idProject not in (0)
ORDER BY planningelement.wbsSortable
2018-11-27 11:25:21.473 ***** ERROR ***** [V7.3.0] Strack trace :
2018-11-27 11:25:21.473 ***** ERROR ***** [V7.3.0] #0 Sql->query called at [/homepages/12/d401768937/htdocs/projeqtor/tool/jsonPlanning.php:283]
2018-11-27 11:25:21.473 ***** ERROR ***** [V7.3.0] #1 include called at [/homepages/12/d401768937/htdocs/projeqtor/view/globalPlanningList.php:523]

Can you give us an hint on how to resolve it?

Regards

Hello,

One thing that must mention is that my database default caracter set is set to UTF8 so how could the software detect that i'm using Latin1?

Thanks

Hello,

The problem is still present with version 7.3.1.

While upgrading, an exception occured :
2018-12-04 06:52:04.401 ***** ERROR ***** [V7.3.0] EXCEPTION *****
2018-12-04 06:52:04.401 ***** ERROR ***** [V7.3.0] on file '/homepages/12/d401768937/htdocs/projeqtor/model/Plugin.php' at line (570)
2018-12-04 06:52:04.401 ***** ERROR ***** [V7.3.0] cause = Cannot instantiate abstract class SqlDirectElement
2018-12-04 06:52:04.401 ***** ERROR ***** [V7.3.0] => #0 /homepages/12/d401768937/htdocs/projeqtor/db/maintenance.php (745) -> checkCustomDefinition()
2018-12-04 06:52:04.401 ***** ERROR ***** [V7.3.0] => #1 /homepages/12/d401768937/htdocs/projeqtor/tool/loginCheck.php (143) -> include()"


I've just read that Mysql(i) plugin for PHP uses Latin1 as default if "SET NAMES 'UTF8'" is not used in the connection script.

I can't find any reference to the SET NAMES in source code. Could it be the problem?