I want to create reports from a view in an external MySQL database at a server. Unfortunately I don't have access to my.cnf, therefore I cannot change any startup settings of mysqld. The problem obviously lies with the definition of character sets and collations at the server. When defining/running Forena reports in my test system everything works fine. However, when running on the server, lines in the report that contain fields where German umlaut characters might occur (eg. names of people) leave these fields completely blank, even though they are printed nevertheless.
The datablock is as follows:
----------------------------------
--ACCESS=access content
select Name, Instrument, telNummer from viewAktTelNummern
where prio=1 and Name != 'N. N.'
and 1 = 1
--IF=:Name
and :Name = Name
--END
--IF=:Instrument
and :Instrument = Instrument
--END
----------------------------------

The frx:file is as follows:
----------------------------------










Telefon-Nummern von RM-Aktiven
Name Instrument Nummer
{Name} {Instrument} {telNummer}
---------------------------------- The test system's pertinent definitions are: ---------------------------------- character_set_client utf8mb4 character_set_connection utf8mb4 character_set_database utf8 character_set_filesystem binary character_set_results utf8mb4 character_set_server utf8 character_set_system utf8 character_sets_dir /usr/share/mysql/charsets/ collation_connection utf8mb4_unicode_ci collation_database utf8_unicode_ci collation_server utf8_unicode_ci ---------------------------------- The server's definitions are: ---------------------------------- character_set_client utf8 character_set_connection utf8 character_set_database utf8 character_set_filesystem binary character_set_results utf8 character_set_server latin1 character_set_system utf8 character_sets_dir /usr/share/mysql collation_connection utf8_general_ci collation_database utf8_general_ci collation_server utf8_unicode_ci ---------------------------------- "character_set_server latin1" comes to my mind. From my "googling" I would think that it isn't the problem nevertheless. Having spent quite a number of hours on the issue I would be VERY happy for any help. Regards H. Stoellinger, Salzburg

Comments

hstoellinger created an issue. See original summary.

metzlerd’s picture

DO I understand correctly that the only problem is when international characters are "filtered on"? I'm used to seeing characters misrepresented, but the idea that the whole field would go blank seems to suggest something different.

hstoellinger’s picture

Hello David,
Thanks for offering your help! I can only describe the problem like this: Whenever the result of the query contains any of the German Umlaut characters or the German "sz" ligature character, the respective columns in the displayed table will be left blank. However, the row will still be displayed and will contain the data of other columns of the same row. I suspect that the rows will still be displayed even if ALL columns contain "illegial" characters. I have played around a lot with the various charset and collation parameters in MySQL. They seem to be somewhat of a mess! I also have unloaded the database, changed every charset/collation the the utf8mb4 versions, converted tables and columns to utf8mb4 and then reloade the database - no change! From what I see, the only real issue seems to be that character_set_server is set to latin1 (collation ...swedish...) on the server, whereas on my test system (where things work like a charm!) it was utf8 and is now utf8mb4 with the respective collations...

hstoellinger’s picture

Hello again,
One more aspect...
Besides trying to create Forena reports from the external database, I also access it through phpmyadmin, the mysql command-line client and LibreOffice. I don't experience the same problems in those environments. On the other hand, "simple pages" defined (without db-access) using the content creation facility of Druapl don't show this behavior either. I really am stuck...
Regards from Salzburg, Austria
H. Stoellinger
P.S.: If you want to see the issue "in action" you can...
- go to www.rainermusik.at
- login as "rmmusiker" (pw: untersberg)
- click on "Datenbank" on the right side of the horizontal menu
- then select "Aushilfen/Telefon" in the displayed table

metzlerd’s picture

I understand your frustration... Character set issues can be really tricky to track down. Are you saying that the phpMyAdmin that you are accessing this database from is on the same server as your web server, or are you talking about local installs. Can you connect your development box to the production web server and replicate the problem? Aside from setting doctypes on the html produced by forena, it doesn't really do anything regarding character set translations. In your problem, the whole column is disappearing, which would seem to imply a database driver issue.

What version of PHP is running on the production server? If greater than 5.3.6, then consider experimenting with charset on the connection string itself (e.g. mysql:host=$host;dbname=$db;charset=utf8)

hstoellinger’s picture

Thanks a lot, David! Adding "charset=utf8mb4" to the connection string did the trick! Yes, I connect to the production server system via phpMyAdmin, LibreOffice JDBC, and MySQL client from my "home" test system. PHP is at 5.5.30 at the production system. I am going to try to get deeper into Forena now (parameters, styling, etc...).
Thanks again for your help and greetings from Salzburg, Austria

metzlerd’s picture

Status: Active » Fixed

Status: Fixed » Closed (fixed)

Automatically closed - issue fixed for 2 weeks with no activity.