ProjeQtOr free project management software - [Fixed ] Database collation error 1253 - ProjeQtOr
 

[Fixed ] Database collation error 1253

More
20 Mar 2020 16:11 #1 by sbs
Hello,

We have installed a new version of Prjeqtor (8.4.0) with mysql database stored on a separate server. Database was created using the standard setup.

Users randomly get errors when performing actions on activity (could not yet pinpoint what course of action leads to this error).
When looking in the logs, I found several occurence of the following error :

***** ERROR ***** [V8.4.0] Exception-[42000] SQLSTATE[42000]: Syntax error or access violation: 1253 COLLATION 'utf8_general_ci' is not valid for CHARACTER SET 'latin1'

Below is a sample of the faulty 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,^M
        priority,elementary,idle,done,cancelled,idPlanningMode,idBill,^M
        initialStartDate,validatedStartDate,validatedStartFraction,plannedStartDate,plannedStartFraction,realStartDate,^M
        initialEndDate,validatedEndDate,validatedEndFraction,plannedEndDate,plannedEndFraction,realEndDate,^M
        latestStartDate,latestEndDate,^M
        initialDuration,validatedDuration,plannedDuration,realDuration,^M
        initialWork,validatedWork,assignedWork,plannedWork,leftWork,realWork,^M
        validatedCost,assignedCost,plannedCost,leftCost,realCost,^M
        progress,expectedProgress,wbs,wbsSortable,isOnCriticalPath,notPlannedWork, needReplan,^M
        null as idType, null as idStatus, null as idResource, 0 as isGlobal ^M
      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) )) ) as planningelement
 WHERE (1=1) and planningelement.idProject in (20, 16, 11, 13, 9, 14, 1, 10, 17, 15, 12, 19, 18, 21, 22, 26, 27, 28, 29, 30, 31, 32) and planningelement.idProject not in (0)
 ORDER BY planningelement.wbsSortable

I thought there was a collation issue in the database but all tables are utf8. When running the query from mysql workbench I get a slightly different error message :
Error Code: 1253. COLLATION 'utf8_general_ci' is not valid for CHARACTER SET 'utf8mb4'

Did I miss some configuration item somewhere? How can I fix this?

Regards,

Please Connexion or Create an account to join the conversation.

More
25 Mar 2020 15:51 #2 by babynus
You DB is configured with collation utf8mb4
ProjeQtOr supports only utf8 collations.

Babynus
Administrator of ProjeQtOr web site

Please Connexion or Create an account to join the conversation.

More
26 Mar 2020 07:27 - 06 Mai 2020 09:20 #3 by sbs
Hello,

I have issued the required alter database statement to set it to UTF8.

May be the setup should take care of this (or if not possible warn that the database must be altered with the proper query).

I'll keep you posted in the next days to confirm that this fixed our issue.

Regards,

After a few weeks, we don't seem to find this error anymore. Tnak you
Last edit: 06 Mai 2020 09:20 by sbs.

Please Connexion or Create an account to join the conversation.

Moderators: babynusprotion
Time to create page: 0.046 seconds