Hello all, After launching today CQRLOG and entering some paper QSL's the program froze which forced me to killall cqrlog. Unfortunately, after restart, I'm getting only two QSO's in my list, however "QSO in log" shows the correct number. Also, I cannot "find" (CTRL+F) any QSO's that aren't on the list.
After a little play with SQL Console I managed to figure out that table cqrlog_main contains all QSO's (phew!), however all views contain only those two contacts that are displayed in QSO List window. I'm not sure how I could debug this further or, better, how can I get my log running again.
I'm using Arch Linux and currently ver. 2.3.0 of CQRLOG (well, the problem ocurred on version 2.2.0 and I kinda hoped that an upgrade would solve it, but nope :/ )
Thanks!
Update.
I just tried to open some of my other logs and the behaviour is the same - no contacts in QSO List window, QSO counter shows correctly.
Hi all,
Same issue here, I'm also archlinux user and my QSO list only show one QSO.
I think is a issue related to a recent update of Mariadb Server package, I tried to downgrade to previous version but that isn't fix the problem.
I really appreciate any help anybody can provide.
Current setup:
CQRLOG 2.3.0
mariadb-10.4.6-1
mariadb-clients-10.4.6-1
And the debug output:
Cqrlog Ver:2.3.0 (001) Date:2018-06-17
**** DEBUG LEVEL 1 ****
SSL libraries:
/usr/lib64/libssl.so.1.0.0
/usr/lib64/libcrypto.so.1.0.0
Loading libmysqlclient: /usr/lib64/libmysqlclient.so
**************************
MySQL version: 10.
**************************
**********************************
MySQL version assigned: 5.6
**********************************
Loaded 130787 LoTW users
Loaded 123431 eQSL users
Loaded 37393 SCP calls
*
User home directory: /home/franc/
Program home directory: /home/franc/.config/cqrlog/
Data directory: /home/franc/.config/cqrlog/database/
Memebers directory: /home/franc/.config/cqrlog/members/
ZIP code directory: /usr/share/cqrlog/zipcodes/
Binary dir: /usr/bin/
Share dir: /usr/share/cqrlog/
TConnection to MySQL: 5.6
*
56 us states loaded
/usr/sbin/mysqld --defaults-file=/home/franc/.config/cqrlog/database/mysql.cnf --datadir=/home/franc/.config/cqrlog/database/ --socket=/home/franc/.config/cqrlog/database/sock --port=64000
Trying to connect to database
2019-07-03 16:20:41 0 [Note] /usr/sbin/mysqld (mysqld 10.4.6-MariaDB) starting as process 13104 ...
TMySQL56Connection : Server connect failed.
Trying:1
select * from tables where table_schema = 'cqrlog_common'
SELECT log_nr,log_name FROM cqrlog_common.log_list order by log_nr
use cqrlog001
use cqrlog001
use cqrlog001
use cqrlog001
use cqrlog001
SELECT * FROM cqrlog_config
select * from db_version
[UpgradeMainDatabase] Old version: 15 cDB_MAIN_VER: 15
select * from cqrlog_common.db_version
SELECT * FROM cqrlog_common.dxcc_ref ORDER BY ADIF
SELECT * FROM cqrlog_common.dxcc_ref ORDER BY ADIF
Settings:
-----------------------------------------------------
RigCtldPath:/usr/bin/rigctld
RigCtldArgs:-m 1 -r /dev/ttyUSB0 -t 4532
RunRigCtld: FALSE
RigDevice: /dev/ttyUSB0
RigCtldPort:4532
RigCtldHost:localhost
RigPoll: 500
RigSendCWR: FALSE
RigId: 1
1
2
3
4
5
6
Settings:
-----------------------------------------------------
RotCtldPath:/usr/bin/rotctld
RotCtldArgs:
RunRotCtld: FALSE
RotDevice:
RotCtldPort:4533
RotCtldHost:localhost
RotPoll: 500
RotId: 1
rotctld started!
Connected to localhost:4533
FreqmemSql:select id,freq,mode,bandwidth from freqmem order by id
FreqmemFirst:0 FreqmemLast:0
CW init
Remind timer set to :3600000ms
SELECT * FROM profiles WHERE visible > 0 ORDER BY nr
SELECT * FROM profiles WHERE nr = -1
SELECT * FROM profiles WHERE visible > 0 ORDER BY nr
SELECT * FROM profiles WHERE nr = -1
select * from view_cqrlog_main_by_qsodate where qsodate >= '2019-07-02' order by qsodate,time_on
LoadForm: frmNewQSO
select * from view_cqrlog_main_by_qsodate LIMIT 500 OFFSET 0
LoadForm: frmMain
SELECT COUNT(*) FROM cqrlog_main
select count(*) from (select distinct adif from cqrlog_main where adif <> 0 and (adif not in (488))) as foo
select count(*) from (select distinct dxcc_id.dxcc_ref from dxcc_id left join cqrlog_main on dxcc_id.adif = cqrlog_main.adif WHERE cqrlog_main.adif<>0 and (dxcc_ref NOT LIKE '%*') AND ((qsl_r = 'Q') OR (lotw_qslr = 'L') OR (eqsl_qsl_rcvd='E'))) as foo
NOTE: Window with stalled focus found!, faking focus-out event
73's C31FR
True, it's Arch's pain of being ahead of its own times ;)
In pacman's log there is some information about post-upgrade procedure for MariaDB:
[ALPM-SCRIPTLET] :: MariaDB was updated to a new feature release. To update the data run:
[ALPM-SCRIPTLET] systemctl restart mariadb.service && mariadb-upgrade -u root -p
[ALPM-SCRIPTLET] :: MariaDB configuration layout changed.
[ALPM-SCRIPTLET] -> The main configuration file now is: /etc/my.cnf
[ALPM-SCRIPTLET] -> Instantiated services (mariadb@foo.service) do not longer read their
[ALPM-SCRIPTLET] own configuration file but use group suffix (configuration block
[ALPM-SCRIPTLET] '[mysqld.foo]') in main configuration file.
but CQRLOG uses its own instance of mysqld, so the above doesn't apply directly. If I have a minute to spare I'll investigate. Perhaps the easiest way would be to copy the entire database directory to another instance of CQRLOG on a linux distro that hasn't switched to newest MariaDB yet an see if that works.
Also, if that might be of some help, I have like a ton of
[ERROR] Transaction not registered for MariaDB 2PC, but transaction is active
in my .config/cqrlog/database/mysql.err
-- SQ6POC
Hi,
at the moment i cann't help you. Only can give the information that cqrlog 2.3.0 (220) on Ubuntu 19.10 (daily build) with maria-db 10.4.6 in a docker container works without your observations. it finds all qsos (about 7000 qsos in my log) and with Strg-F he shows all entries, also old entries.
Bye Andreas, DL7OAP
After a bit of digging it seems like an error during DXCC entity list update (or thereof). Let me explain:
As I said in my first post, I'm getting only two QSOs in my QSO list window, these are taken from MariaDB table/view called view_cqrlog_main_by_qsodate, which in turn gets created by the following call (in dData.lfm):
CREATE VIEW view_cqrlog_main_by_qsodate AS SELECT id_cqrlog_main,qsodate,time_on,time_off,callsign,
freq,mode,rst_s,rst_r,name,qth,qsl_s,qsl_r,qsl_via,iota,pwr,itu,waz,loc,my_loc,county,
award,remarks, band, dxcc_id.dxcc_ref AS dxcc_ref ,qso_dxcc, profile,idcall, state, lotw_qslsdate, lotw_qslrdate,lotw_qsls, lotw_qslr, cont,
qsls_date,qslr_date,club_nr1,club_nr2,club_nr3,
club_nr4,club_nr5,eqsl_qsl_sent,eqsl_qslsdate,eqsl_qsl_rcvd,eqsl_qslrdate,concat(qsl_r,lotw_qslr,eqsl_qsl_rcvd) as qslr,dxcc_id.country, rxfreq, satellite, prop_mode
FROM cqrlog_main JOIN dxcc_id ON dxcc_id.adif = cqrlog_main.adif order by qsodate DESC, time_on DESC;
Note the FROM+JOIN phrase. I checked what are entries for cqrlog_main.adif and dxcc_id.adif using Filter -> SQL Console:
SELECT adif FROM cqrlog_main;
SELECT adif FROM dxcc_id;
and got 1050 records (that's my real QSO count) from the former query, and three records from the latter. So, in conclusion - all my QSO's have a corresponding dxcc_id attributed to the in the database, but the database itself knows only three of them... So doing a JOIN effectively discards all QSO entries with ther dxcc_id unknown to the database.
I don't know yet how I will go about fixing this, but if anyone has the same problem, they can do the two SELECTs above and see if that's indeed the culprit.
-- SQ6POC
I have the same problem. I' am running here Manjaro. After import, this morning, from a ADIF-file the window shows only two QSO, but the counter tells me the whole numbers of QSOś.
73 DD0WN
Hello,
in the sql console (Form QSO List [Ctrl-o] and then menu Filter > SQL Console)
select count(*) from dxcc_id
should give about 404 entries, and not only 3 entries.
the table dxcc_id is feeded by ~/.config/cqrlog/ctyfiles/Country.tab and updated in regular intervals by Martin.
He provides a *.tar file which holds this file and some more files.
Maybe you can proof if your Country.tab is empty or a very short file?
Seems that there was a error during update of the table dxcc_id.
at the moment i don't know how to force a reupload of Country.tab into your dxcc_id.
a think you have to change the timestamp of the Country.tab file so cqrlog recognize it during a new start as update-relevant.
(For the people who can read FreePascal look dUtils.pas function TdmUtils.GetLastUpgradeDate. There is the logic how cqrlog recognize when to update)
Bye Andreas, DL7OAP
i don use ArchLinux but i found a hint from arch linux on https://www.archlinux.de/
that MariaDB Configfiles /etc/mysql/my.cnf was moved to /etc/my.cnf
and the configuration file /etc/mysql/my.cnf.d/ was moved to /etc/my.cnf.d
Hello Andreas,
Thanks for suggestions. Unfortunately, there's something terribly wrong somewhere deeper along the SQL-CQRLOG line, I'm afraid. Here's what I found out:
0) I went through the pain of getting to work mariadb-upgrade, just for the sake of it.
1) I was able to force re-population of the cqrlog_common.dxcc_ref table (which I see is then used to populate dxcc_id for each log) by moving the creation date of all *.tab files under cqrlog/ctyfiles to January 2019.
2) In CQRLOG's debug I'm getting a bunch of INSERT statements as follows:
Command line: tar -xvzf /home/przemek/.config/cqrlog/ctyfiles/cqrlog-cty.tar.gz
INSERT INTO cqrlog_common.dxcc_ref (pref,name,cont,utc,lat,longit,itu,waz,adif,deleted) VALUES ('1A','Sovereign Military Order of Malta','EU','-1','41.9055N','12.4808E','28','15',246,0)
INSERT INTO cqrlog_common.dxcc_ref (pref,name,cont,utc,lat,longit,itu,waz,adif,deleted) VALUES ('1S','Spratly Islands','AS','-8','7.3734N','113.8223E','50','26',247,0)
3) In SQL Console however, SELECT * FROM cqrlog_common.dxcc_ref produces... Always the same TWO entities ZS(M) and ZS(W).
4) If I manually invoke INSERT INTO cqrlog_common.dxcc_ref (...) this record does end up in the table and after CQRLOG restart it's finally moved to dxcc_id.
... AND...
5) The exact thing happens with cqrlog_common.iota_list - debug messages say it gets populated, while SELECT count(*) FROM cqrlog_common.iota_list returns 1.
The bottom line is then that automatic update for IOTA/QSL managers/DXCC entities fails to be saved in database, somehow.
Any ideas on how I can debug this further? I'm not a proficient Pascal coder, unfortunately, but I am able to follow the code, if someone tells me where to look.
Thanks!
-- SQ6POC
Thank's Andreas for the Info.
How is the following sentence to be interpreted?
Do I have to copy the files in their old place?
Or what files are to be edited from the database or CQRLOG?
Sorry for the not german speakers.
"Hat man hier Anpassungen vorgenommen, müssen die Dateien und Verzeichnisse entsprechend verschoben werden.
Instanziierte Dienste werden nicht mehr in seperaten Dateien konfiguriert, sondern in der Hauptkonfigurationsdatei.
Wie immer bei einer Aktualisierung bei mariadb ist die DB anschließen zu stoppen und neu zu starten.
systemctl restart mariadb.service && mariadb-upgrade -u root -p"
73 DD0WN
Hi,
as far as I can tell it's not the issue with not invoking mariadb-upgrade, it's simply that one of the tables in CQRLOG database has lost almost all of its entries (luckily it's not the one which stores QSOs). I'm trying now to force CQRLOG to update the table, will post my solution, if I manage to find one.
One of my attempts was to create a brand new log and simply import the missing data from one log to another, but I'm unable to create any other log (using a fresh CQRLOG installation or the old one) due to an error in SQL. I'm reporting it in another thread, but so far nobody has been able to help me out.
Whatever you do, I'd advise the first thing to do is you quit CQRLOG and copy the entire ~/.config/cqrlog directory to somewhere safe, so that should something go terribly wrong, at least you end up with a copy of all information from before.
Good luck!
- SQ6POC
-- SQ6POC
Hi DD0WN,
i don't know if this hint really belongs to the problem. Only wanted to give this link, for someone who will digger deeper to this topic.
when using cqrlog plain vanilla it will start its own sql server by this:
/usr/sbin/mysqld --defaults-file=/home/yourUser/.config/cqrlog/database/mysql.cnf --datadir=/home/yourUser/.config/cqrlog/database/ --socket=/home/yourUser/.config/cqrlog/database/sock --port=64000
so it have references to its own mysql-config files and also using its own port (64000) instead mysql standard port (3306).
the hint from archLinux could be relevant, when you have installed a normal/global mysql/mariadb on your computer or ArchLinux using AppArmor or other SecurityTools which disallows the access from Applications to mysql.
i'm sorry, but at the moment i can not help you further more.
55 & 73 de DL7OAP, Andreas
Hello all,
I managed to get everything working again. The change in MariaDB to version 10.4.6 seems to be bigger than one might think and does require some fiddling. In short, the new version seems to store some additional information about handliing all databases in one database called mysql. CQRLOG does not provide that database, because there was no reason for it, until now, so it would seem.
First, let me make one thing clear: my CQRLOG does NOT use external database server ("Save log data to local machine" option is checked). If you are using e.g. a local database server and experiencing a similar issue, then just execute step 1) from the list below and you should be good.
Here's what worked for me:
https://stackoverflow.com/questions/37856155/mysql-upgrade-failed-innodb-tables-doesnt-exist<
https://dba.stackexchange.com/questions/48166/cannot-open-table-mysql-innodb-index-stats
sudo copy -r /var/lib/mysql/mysql ~/.config/cqrlog/database sudo chown -r {yourUserName}:{yourGroupName} ~/.config/cqrlog/databaseIn the second call, replace {yourUserName} and {yourGroupName} with ... eh, you know what :)
Hopefully, you should see all QSOs in the QSO List window now.
-- SQ6POC
HI!
You can also use local database server (starts when you unstall MariaDB/Mysql) instead of "Save log data to local machine" that starts second instance of mysql/MariaDB server.
Benefit is that memory and cpu resources are not used for running double sql servers.
Disadvantage is that full backup (by copying ~/.config/cqrlog) does not anymore backup your logs. It has to be done from console with mysqldump.
That needs just to select Mysql server name as localhost and port 3306 at Database connection window. and granting user cqrlog to use mysqlserver running in port 3306.
You get the idea from this message: https://www.cqrlog.com/comment/6776#comment-6776
With my alphaTest zip comes a script fixlongcountrynames.sh that is for making country names shrunken so that more text fits to WSJT-x CQ monitor window.
If you look at the end of the script there is guide how to restore original files by making new update happen:
echo 'If you do not like the results delete files:'
echo ' CallResolution.tbl'
echo ' Country.tab'
echo ' AreaOK1RR.tbl'
echo ' cqrlog-cty.tar.gz'
echo 'from ~/.config/cqrlog/ctyfiles/ directory, then'
echo 'run CQRLOG again and confirm auto upgrade'
Just for information...
--
Saku
OH1KH
Yeah, I guess your solution for enforcing the update is easier :)
I chose separate database on purpose - I'm not running my local MariaDB server 24/7 but rather start it when I need it - similarly to what CQRLOG does, so very little gain for me and, as you said, a bit more problems with backing everything up.
Anyway, thanks for sharing your idea!
-- SQ6POC
Hello, for me the problem is still not solved. sq6poc I tried it the way you described it to no avail. Then I installed Ubuntu on another machine, with an older database, no success. Two QSOs are displayed, but 1365 QSOs are listed in the database. Then I tried to install MySQL on my own to pass the data on, but I can not get the database up and running. I'm desperate...
DD0WN
Drop me an e-mail on {my-callsign}@op.pl - I'll try to help you as much as I can.
-- SQ6POC
Hi, I have sent you an e-mail
for me the problem still persists afer applying these steps. I am running Manjaro. After having tried to fix my normal installation, I have tried it with 2 clean installations of Linux in a virtual machine (one with Ubuntu 18.04 and one with a new Manjaro installation).
i then deleted and re-installed Mariadb and CQRLOG, with no success.
After that, I tried my netbook which I use for portable operations with Ubuntu 18.04 on it. With the database which is on the netbook, I have no problems, but when I copy the databases from the PC to the netbook, I have the same issues there.
Since my backups are up to date, I would prefer to wipe everything off the PC and do a clean new installation of CQRLOG on my PC: Is there any reproducable solution for that?
Jens, HB9EKO
First off, the reason that it works for your laptop and doesn't work for the PC is that default version for MariaDB on Ubuntu 18.04 is 10.1. All the problems arise no sooner than with MariaDB 10.4, which is probably what you are running on your PC's Manjaro (long live rolling distros!). What's changed is not the database structure itself, but the way the database engine stores credentials for operating these databases. That's why moving only CQRLOG's database files between your laptop and PC doesn't fix things - you'd have to move mariaDB executable as well (which most likely won't work - but you could try).
So you can try either of two things on your PC:
a) downgrading MariaDB to version 10.1 or changing linux distro to something that by default runs that version -- you will face the same problem again, though, when that distro switches to 10.4...
b) following my steps, making sure that you go through it without any errors - it will upgrade an existing database to the format required by 10.4. Unfortunately, there's very little information about it online. Just make sure that in the end you copy the entire /var/lib/mysql directory to ~./config/cqrlog/database/
As I said before, it's going to hit the rest of CQRLOG users when Ubuntu and other popular distros switch to MariaDB 10.4, so let's be prepared to help them :)
Drop me an email at {my-callsign}@op.pl should you need more detailed help.
Przemek SQ6POC
-- SQ6POC
In my fedora 30 I have Mariadb 10.3. with that cqrlog
works fine, no problem.
<p>Jarmo</p>
thank you for the information. I did not get it to work only by following your instructions. It is working now after I deinstalled both CQRLog and MariaDB and then did a complete reinstall from the scratch. (No idea what went wrong when I did the same thing yesterday).
I hope though that there will be a solution built into CQRLog to make this easier, since the average user probably expects it to work after an install from scratch. What makes it unnecessarily complicated in my point of view is that at least in Manjaro, you cannot just install MariaDB from the packet manager, but have to initialise the database at the console.
For everybody who has the same issues and needs a complete re-install, I would like to summarise the procedure with your fix and a complete re-install of MariaDB and CQRLog, so that it's all in one place. This is applicable for Manjaro Linux (based on Arch Linux). Other distributions might not require the manual initialisation of the database.
1. Remove CQRLog installation with packet manager
2. Remove MariaDB installation with packet manager
3. Delete var/lib/mysql: sudo rm /var/lib/mysql (or rename it: sudo mv /var/lib/mysql /var/lib/mysql.old )
4. Re-install MariaDB with the packet manager
5. Initialise MariaDB with the following commands: (found here: https://manjaro.site/a-complete-guide-to-install-mariadb-server-on-manja... )
sudo mysql_install_db --user=mysql --basedir=/usr --datadir=/var/lib/mysql
sudo systemctl start mariadb
sudo systemctl enable mariadb
sudo mysql_secure_installation
6. Re-install CQRLog with the packet manager
7. Re-initialise database according to SQ6POC:
sudo cp -r /var/lib/mysql/mysql ~/.config/cqrlog/database
sudo chown -R ~/.config/cqrlog/database
cd ~/.config/cqrlog/ctyfiles && touch -t 1901101200 -m *
8. Start CQRLog and update DXCC/IOTA/QSL Managers
9. Restart CQRLog
Thanks for the help Przemek, and let's hope that not too many other users have to go through that.
73 Jens, HB9EKO
I'm glad I could help and that you have a working system again!
As for the logger itself, I've already discussed this briefly with one of the developers - the easiest solution to this problem would be to ship .config/cqrlog/database/mysql/ directory with CQRLOG package/source. This could be pre-made (with MariaDB ver. 10.4) as /var/lib/mysql/mysql . That may be problematic though due to some licensing ...
Still, I think information from this thread should help anyone who is crazy enough to be using bleeding-edge distros ;)
-- SQ6POC