View Issue Details

IDProjectCategoryView StatusLast Update
00028093 - Current Dev ListBugpublic2019-04-18 19:41
Reporterg3ucqAssigned Tog3ucq 
PriorityurgentSeveritymajorReproducibilityalways
Status resolvedResolutionfixed 
PlatformPCOSWindowsOS Version10 64 bit Home
Product Version 
Target VersionFixed in Version 
Summary0002809: Some call signs cannot be logged
DescriptionSome call signs cannot be logged because of incompatible data in the lookup file.
Steps To ReproduceOpen Logbook and the ALE
Enter the call sign LY2BMX and press the Lookup button.
The ALE fields are populated.
Then click the ADD button and you see an error message as in capture.jpg.
The same will occur with SP2CHY - capture2.jpg.
This has also been reported in the Peer forum.
Additional InformationLY2BMX has characters which may not be recognized by the Logbook/ALE but SP2CHY would appear not to have those characters.
TagsNo tags attached.
ModuleLogbook
Sub-ModuleALE Window
TestingNot Started

Relationships

related to 0003181 resolvedK7ZCZ Logbook requires a database schema migration solution 

Activities

g3ucq

2018-07-14 04:58

updater  

Capture.JPG (53,329 bytes)
Capture.JPG (53,329 bytes)
Capture2.JPG (54,992 bytes)
Capture2.JPG (54,992 bytes)

K7ZCZ

2018-07-15 21:03

manager   ~0005712

I'm not able to reproduce this issue. while there are bogus chracters in the QTH (and I can fix that), I don't encounter the error message reported.

The text in the error message displayed isn't from HRD, so it's probably from the back-end data store. If information about the back-end data store, the ODBC driver, and the versions involved would be helpful.

g3ucq

2018-07-16 03:36

updater   ~0005713

MySql is causing the problem as I do not get this problem with an Access database.
I tried installing the ODBC driver but that created another problem so I have switched to the Access database.
No problems now with those call signs.

K7ZCZ

2018-07-16 10:02

manager   ~0005714

It would help to know which version of MySQL is in use. It would also be useful to know which version of which MySQL ODBC driver is being used.

I'll see if I can make progress without that information, but a complete report would hasten progress.

g3ucq

2018-07-16 10:58

updater   ~0005715

Not sure if these are what you want.
In C:\Program Files (x86)\MySQL\Connector ODBC 5.1 there are a few files one of which is myodbc5.dll which is v5.1.5.0 date modified 18/08/2008
In C:\WINDOWS\system32 there is obcad32.exe which is v10.0.17134.1 dated 12/04/2018.
HTH

K7ZCZ

2018-07-16 21:51

manager   ~0005716

To find ODBC driver verions, I would do this:

1) run "ODBC Data sources (32-bit)" from the Start menu
2) in the resulting "ODBC Data Source Administrator (32-bit)" dialog, activate the "Drivers" tab
3) In the list in the "Drivers" tab, find the driver the Logbook data source is configured to use

K7ZCZ

2018-07-16 22:07

manager   ~0005717

Last edited: 2018-07-16 22:08

View 2 revisions

The characters produced here for LY2BMX are correct. "Anykščiai" is the correct spelling of a district in Lithuania.

The error occurs when the Logbook tries to write to the QTH column in the MySQL database. The Unicode characters are not accepted by MySQL because the Logbook has created a table which does not actually accept Unicode characters.

It's possible to demonstrate the same problem using the MySQL Workbench:

CREATE TABLE Fooey ( Col1 VARCHAR(100));

INSERT INTO Fooey (Col1) VALUES ('Anykščiai District Municipality');


The string given in the INSERT statement is precisely the string of the QTH that LY2BMX has. The CREATE TABLE matches the column definition given for character (non-memo) columns in the creation of a MySQL database for the Logbook.

This command, executed in MySQL Workbench, returns this error when run against my MySQL 5.7.22 server:

Error Code: 1366. Incorrect string value: '\xC4\x8Diai ...' for column 'Col1' at row 1	0.000 sec


The problem is that the Unicode string can't be handled by the VARCHAR column. The column is set to use the wrong character set, and the data is rejected. The VARCHAR definition in the CREATE TABLE statement doesn't specify a character set; the default is usually LATIN1, which won't handle a UTF8-encoded string.

The obvious (but naieve) solution is to migrate customer databases to recreate tables to use the a correct character set and collation. Of course, this is a massive undertaking: migrating customer data is difficult and error prone, and quite inconvenient for users. It is a bridge we must someday cross, however, in order to manage the schema of user database to fix other bugs and make enhancement to the product.

There may be a less drastic solution, such as casting or transforming inserted data for MySQL database connections. I'll have to research those approaches to see if I can find something that is feasible. This issue could conceivably affect any VARCHAR column in MySQL-backed Logbooks where Unicode data is intended to be stored.

g3ucq

2018-07-17 04:23

updater   ~0005718

According to one user, .787 does not have this problem.

K7ZCZ

2018-07-17 09:33

manager   ~0005719

Last edited: 2018-07-17 09:44

View 2 revisions

What strings come back for the QTH and address in 787? (Also need to know MySQL and MySQL ODBC driver versions for that user.)

Also, what's the output of this query for that user's database?

SHOW VARIABLES LIKE 'char%';


g3ucq

2018-07-17 11:14

updater   ~0005720

MySQL ODBC 5.1 driver

g3ucq

2018-07-17 15:06

updater   ~0005721

From one user.
HRD .846 MARIA DB 32bit MYSQL ODBC 5.3 UNICODE DRIVER

K7ZCZ

2018-07-17 17:51

manager   ~0005727

Er, so the problem is _not_ happening to the user with HRD 846 and Maria DB, through the MySQL 5.3 driver?

Which version of Maria DB?

g3ucq

2018-07-18 08:03

updater   ~0005729

Maria DB v.10.
There are newer versions than this so should the user update to them?
He wants to know if he should update but I told him to wait until you have looked further at this.

K7ZCZ

2018-07-18 10:40

manager   ~0005731

I don't think anyone has suggested upgrading anything. What reason has been discovered to suggest an upgrade is necessary?

At this point, I'm still trying to understand the issue. It's not clear to me if the Maria user is, or is not, experiencing these symptoms.

It has been reported that some users are not experiencing these symptoms. What is it that is different about their configuration, or their repro steps?

g3ucq

2018-07-18 14:02

updater   ~0005737

The user assumes that the problem may be with Maria DB and updating Maria DB may cure his problem.
This what he posted -
"If I ask ALE to lookup either SP2CHY or LY2BMX before hitting save then it will not save either callsign both show the adr column error. I have a blank access db and it will save these callsigns to this db. My main DB is Maria db mysql HRD version .846 I have over 31K of log entries and I do not recall ever seeing this happen trying to enter a callsign before."
I have the same experience as he does.

g3ucq

2018-07-26 10:15

updater   ~0005841

This problem does not occur with an Access database, only MySQL.
Not fixed in .873

K7ZCZ

2018-07-26 18:44

manager   ~0005853

No change was made, thus a fix was not expected to be apparent in 873.

I'm confused, as I think the user who experiences this problem is using MariaDB, but your response says that it only occurs with MySQL. Before investigaton continues, can we first be certain which database systems experience this issue? Knowing where to focus might help efficiency.

g3ucq

2018-07-27 03:16

updater   ~0005857

AFIK the problem is only apparent with MariaDB and MySQL but not with Access.
My MySQL db has the problem but an Access db does not.
I only mentioned this again as a user complained it had not been fixed and went back to an older version.

vk2byi

2018-08-08 20:17

updater   ~0005948

This issue came up in the HamApps JTAlert groups.io support forum and I also couldn't reproduce the problem at first so I took a closer look. I found the problem to be caused by MySQL and MariaDB databases that have 'latin1' character set and 'latin1_swedish_ci' collation on the 'table_hrd_contacts_v01' table. These are the default character set and collation settings for non-system databases in MySQL and MariaDB. However, when I installed MySQL I changed the encoding and collation from these defaults to UTF-8.

Extended characters in the QRZ.com Name, Address and QTH details are not compatible with 'latin1' encoding. The solution is to alter the character set and collation table options on 'table_hrd_contacts_v01' to 'utf8' and 'utf8_general_ci' in the MySQL or MariaDB database server. I produced a document explaining the steps involved and it is attached.

UTF-8 Encoding.pdf (333,425 bytes)

vk2byi

2018-08-08 22:54

updater   ~0005949

And I would suggest that a better alternative would be if feasible to amend the Create Table script that is used by HRD Logbook to create the table in a new MySQL or MariaDB database by appending the character set and collate arguments:
    create table table_hrd_contacts_v01 ( ...column definitions.. ) character set utf8, collate utf8_general_ci;

Or use an alter table statement after the create table statement:
   alter table table_hrd_contacts_v01 convert to character set utf8 collate utf8_general_ci;

Either will produce the same result. This also assumes that the table is created by sending a create table statement, and not using an object that may encapsulate the command arguments.

73 Chris

K7ZCZ

2018-10-20 22:40

manager   ~0006314

Indeed, specifying the character set in the CREATE TABLE command will fix the issue for newly-created databases.

Problem is, we have lots of customers with lots of existing databases. We have to find a way to migrate the table to have the right character set:

1) When do we check for the need to migrate? Automatically checked at the time the application connects? (Every time?) Or does the user manually migrate? How do we tell them they might need to migrate?
2) When does the convesion happen? If we idnetify the need to migrate, it might take a couple of minutes to alter the table, including all of its indexes. This is pretty invasive, and could take a lot of disk space. How does it fit into the user's workflow?
3) Is ALTER TABLE adequate, or is there a chance for damaged data to exist? Maybe the application has to read each row, cleanse the data, and then re-write it to a new table.
4) What if migration fails? How do we test migration to figure out when it might fail? How does the user recover? How do we roll-back to a stable state?
5) It's conceivable that we'll want to update the database schema for many other reasons. Can the answers to the ese questions be encapsulated and re-used? For all database platofrms -- not just MySQL?

As usual, writing code that seems to solve the problem isn't hard ... integrating that code into the product and coming up with a sensible and reliable workflow for our customers is more difficult.

vk2byi

2018-10-27 21:11

updater   ~0006349

As this defect only affects MySQL/MariaDB database users, the number of users affected may not be a large number.
1) The character set table property on MySQL/MariaDB connections only could be checked at Logbook application start-up with a recommendation made to the user to migrate now or later or to ignore it altogether (i.e. turn off further reminders).
2) When the user decides to perform the conversion at application start-up. The users workflow in this scenario is a deliberate choice to migrate so they can log ‘utf8’ characters going forward and would be at a time of their choosing.
3) ALTER TABLE should be adequate, but maybe DROP TABLE if exists followed by CREATE TABLE statement could be used for both new and existing Logbook table scenarios. I would suspect that damaged data could not exist - only characters acceptable to the ‘latin1’ character set could be in existing rows otherwise the initial insert would have failed. The data is already ‘cleansed’ in that sense, and is a subset of, and acceptable to, a ‘utf8’ character set.
4) DROP/CREATE/ALTER TABLE DDL statements are individually implicit transactions and either complete or rollback. If any of these DDL statements fail, there is something dramatically wrong with that users installation.
RENAME TABLE also exists and the existing ‘latin1’ table could be renamed, the new ‘utf8’ table created and rows inserted into the new table preserving the original table.
5) Good point. But I don’t have any visibility of the HRD code (and don’t wish too) and therefore don’t feel qualified to offer you much advice on this point.

K7ZCZ

2018-10-28 15:50

manager   ~0006351

1) I think we need something more involved than this. Not all configured data stores are opened at application startup, and that might be intentional. Further, a database configuration may be added at runtime, the application should check at that point, too. In general, the configuration of data stores in the Logbook application is pretty messy, and in fixing this issue I don't want to make matters worse.

2) Unless there's alternative migration UI (other than just in response to a detected error at startup or configuration time) then the migration isn't exactly at the time of the user's choosing ... it's only when the application has been indirectly convinced to check for (and detect) the problem.

3) I think that bad data exists because the application previously inserted poorly encoded UTF-8 data. It's correctly encoded UTF-8 data that now fails to insert. Fixing this issue completely involves finding incorrectly encoded data, re-encoding it, and writing it back.

4) DROP/CREATE don't migrate data; they just drop or create the table structure. I'm not confident that we can use ALTER TABLE because of issue #3.


HRD uses ODBC, so any data store can be hooked up. In the past, customers have been told to use Maria by MikeC because of "better performance". I don't think we have a way to measure how many people are using it because our application doesn't perform any telemetry about configuration or usage (or, at all, really). We're in the regreful position of having recommended a configuration that we didn't fully test, and now customers are suffering for trusting our advice.

There are several outstanding questions about how to approach a fix here. I think they're all solvable, but because the team has no established process for discussing and resolving design issues, I'm not sure what the best way forward might be.

g3ucq

2018-10-28 16:20

updater   ~0006352

One way to get the characters into a Maria database is to export the QSO and import it into an Access database.
Then use the Lookup to populate the missing information.
Then import back into the Maria db.

g3ucq

2019-03-07 04:47

updater   ~0007620

Not fixed.

K7ZCZ

2019-03-07 08:38

manager   ~0007625

No fix expected, as no change has been made. Progress on this issue is dependent on Mantis 3181.

K7ZCZ

2019-04-03 14:05

manager   ~0007818

fixed with this checkin in the 6.7 branch:
https://hrdsoftware.visualstudio.com/HRD/_versionControl/changeset/4934

Issue History

Date Modified Username Field Change
2018-07-14 04:58 g3ucq New Issue
2018-07-14 04:58 g3ucq File Added: Capture.JPG
2018-07-14 04:58 g3ucq File Added: Capture2.JPG
2018-07-15 21:03 K7ZCZ Assigned To => g3ucq
2018-07-15 21:03 K7ZCZ Status new => feedback
2018-07-15 21:03 K7ZCZ Note Added: 0005712
2018-07-16 03:36 g3ucq Note Added: 0005713
2018-07-16 10:02 K7ZCZ Note Added: 0005714
2018-07-16 10:58 g3ucq Note Added: 0005715
2018-07-16 21:51 K7ZCZ Note Added: 0005716
2018-07-16 22:07 K7ZCZ Note Added: 0005717
2018-07-16 22:08 K7ZCZ Note Edited: 0005717 View Revisions
2018-07-17 04:23 g3ucq Note Added: 0005718
2018-07-17 09:33 K7ZCZ Note Added: 0005719
2018-07-17 09:44 K7ZCZ Note Edited: 0005719 View Revisions
2018-07-17 11:14 g3ucq Note Added: 0005720
2018-07-17 15:06 g3ucq Note Added: 0005721
2018-07-17 17:51 K7ZCZ Note Added: 0005727
2018-07-18 08:03 g3ucq Note Added: 0005729
2018-07-18 10:40 K7ZCZ Note Added: 0005731
2018-07-18 14:02 g3ucq Note Added: 0005737
2018-07-26 10:15 g3ucq Note Added: 0005841
2018-07-26 18:44 K7ZCZ Note Added: 0005853
2018-07-27 03:16 g3ucq Note Added: 0005857
2018-08-08 20:17 vk2byi File Added: UTF-8 Encoding.pdf
2018-08-08 20:17 vk2byi Note Added: 0005948
2018-08-08 22:54 vk2byi Note Added: 0005949
2018-10-20 22:40 K7ZCZ Note Added: 0006314
2018-10-27 21:11 vk2byi Note Added: 0006349
2018-10-28 15:50 K7ZCZ Note Added: 0006351
2018-10-28 16:20 g3ucq Note Added: 0006352
2019-02-16 18:12 K7ZCZ Relationship added related to 0003181
2019-03-07 04:47 g3ucq Note Added: 0007620
2019-03-07 08:38 K7ZCZ Note Added: 0007625
2019-04-03 14:05 K7ZCZ Status feedback => resolved
2019-04-03 14:05 K7ZCZ Resolution open => fixed
2019-04-03 14:05 K7ZCZ Note Added: 0007818
2019-04-18 19:41 WA9PIE Project 1 - Backlog => 3 - Current Dev List