View ProjeQtOr On SourceForge.net
ProjeQtOr - Project Management Tool
Supportez nous sur Capterra
OIN - Open Invention Network
ProjeQtOr free project management software - Encoding errors after mariaDB upgrade - ProjeQtOr

Prochaines Sessions

Les prochaines formations et démonstrations sont ouvertes, inscrivez-vous rapidement !

 

Démonstration de ProjeQtOr

(gratuit, sur inscription)
 

16 juillet (10h30-12h)

22 août (16h-17h30)

12 sept (10h30-12h)

1er oct (16h-17h30)

 
 

Planifiez avec ProjeQtOr

18 et 19 septembre (9h - 12h30)

 
 

Administrez avec ProjeQtOr

25 et 26 septembre (9h - 12h30)

 

 

 

Encoding errors after mariaDB upgrade

More
09 Avr 2024 17:01 - 09 Avr 2024 17:41 #1 by Amaury
introduction

Hello,

we are currently self hosting projeQtor using XAMPP 7.2.1, and after security audit, we want to upgrade the stack, and hopefully getting ride of XAMPP.
While trying, to restore projeQtor database on a new mariaDB, I get encoding errors in the new projeQtor application.


FROM (SOURCE)
  • windows server
  • projeqtor 11.1.1 (custom modification and plugins, unrelated to the database)
  • XAMPP 7.2.13
    • mariadb 10.1.37


TO

current database state (Source)
  • show create database projector;
    • CREATE DATABASE `projector` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin */
  • global variables
  • select *  from projector.project p ;  (USING DBEAVER or PHPMYADMIN)
    • ---> show incorrect encoding in values
  • set names 'latin1' COLLATE 'latin1_swedish_ci';  select *  from projector.project p ;
    • ---> show correct encoding in values(USING DBEAVER or PHPMYADMIN)

Unsuccessful upgrade procedure 
    
  1. connect to SOURCE database using DBeaver
  2. right click on the projeqtor database, and do "dump database"
    1. specify "--default-character-set=latin1" as this seems to produce the "best looking" SQL dump file
  3. check the dump
    1. open in notepad++
    2.  
    3.  
  4. connect to GOAL database using dbeaver
  5. check that the projeqtor database had been created after admin logon on projeqtor
  6. drop all tables
  7. right click on the projeqtor database, do "restore dump"
    1. select the dump file
    2. dont specify anything for ther character set
    3. or specify utf8, or latin1 depending of the mariadb server charset, at this point nothing works
  8. - set names 'latin1' COLLATE 'latin1_swedish_ci';  select *  from projector.project p ;
    1.  ---> show correct encoding in values(USING DBEAVER or PHPMYADMIN)
  9. launch GOAL Projeqtor, connect, look at the incorrect encoding in the projects...

I tried some variants, like the different --default-character-set for dump and restore or changing database or server charset..

Crying for help

As you can see, at the end of the procedure, when I looked at the databases, they look the same. I was wondering if I can easily specify the charset encoding that php should use with the DB, but is this the good solution? It's in the model/persistence/Sql.php file.
Is my case related to this one www.projeqtor.org/en/forum/5-ask-questio...tor-database-charset ? My dump look OK, enforceUTF8 already set to 1.

What do I don't understand, or what did I missed?


Miscellaneous additional informations

- I tried to change GOAL database server encoding to match the SOURCE server
    -  

- I don't want to update SOURCE mariadb server encoding (from latin1 to utf8) because it's the production environnement.
 
Attachments:
Last edit: 09 Avr 2024 17:41 by Amaury.

Please Connexion or Create an account to join the conversation.

More
09 Avr 2024 17:27 #2 by Plucks
Hello,
Please edit your post and use the attachments feature to send your pictures.
The following user(s) said Thank You: Amaury

Please Connexion or Create an account to join the conversation.

More
09 Avr 2024 17:46 #3 by Amaury
Hello,
thank you, it's done.

Please Connexion or Create an account to join the conversation.

More
19 Avr 2024 15:08 #4 by babynus
Try and add in parameters.php
$enforceUTF8 = '1';  

Or better, change DB collation to UTF8MB4

Babynus
Administrator of ProjeQtOr web site

Please Connexion or Create an account to join the conversation.

More
30 Avr 2024 15:02 - 30 Avr 2024 15:15 #5 by Amaury
Hello

I did try both, they seems logical. No success.
The db upgrade was motivated by security reasons. Our security accountant allowed me to use temporally the last minor version of mariaDB 10.1.
using the last minor worked directly.

So i will stop investigating for a moment and I ll try later. But i am still out of ideas.

Amaury

ps: i was on vacation one week that's why i didn't answered rapidly
Last edit: 30 Avr 2024 15:15 by Amaury.

Please Connexion or Create an account to join the conversation.

More
02 Mai 2024 14:56 #6 by babynus
You can try :
Export DB.
Open dump in Notepad++.
Check format provided, and check accentuated characters.
If they are not correct, convert file so that it is UTF-8 and with correct accentuated characters.
Import cvonverted dump forcinf UTF8
[code]mysql -u [dbuser]  -p --default_character_set utf8 projeqtor < yourdump.sql[code]

Babynus
Administrator of ProjeQtOr web site

Please Connexion or Create an account to join the conversation.

Moderators: babynusprotion
Time to create page: 0.088 seconds

Paramétrages de cookies

×

Cookies fonctionnels

Ce site utilise des cookies pour assurer son bon fonctionnement et ne peuvent pas être désactivés de nos systèmes. Nous ne les utilisons pas à des fins publicitaires. Si ces cookies sont bloqués, certaines parties du site ne pourront pas fonctionner.

Session

Veuillez vous connecter pour voir vos activités!

Autres cookies

Ce site web utilise un certain nombre de cookies pour gérer, par exemple, les sessions utilisateurs.