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