Hello Petr!
Have you any intention to work cqrlog with remote DataBase(s)?
X-ssh is the way but it needs a serious bandwidth. It isn't a way for slow internet connection.
Thank you!
Wed, 2013-07-17 05:57
#1
Remote DataBase
Yes, it works without any problem. You can have MySQL server installed on your home server and connect to it.
If you start CQRLOG, you'll see the list of logs. On the top of the window is "Save data to local machine". If you uncheck it, connection settings will appear. Unfortunatelly cqrlog still runs local mysql server but it will be fixed in next version.
73 Petr, OK2CQR
--
http://HamQTH.com/ok2cqr
https://ok2cqr.com
But there is no way to export the database first and import it on the remote server.
Is it possible to have this function? Or can you move everything into 1 myisam database?
That would make moving a lot easier.
Thanks,
Bas.
Greetings, Bas. ON5HB
Bas,
you can run full MySQL on your computer, create the log there and use Adminer/PhpMyAdmin to export whole databases into SQL.
73 Petr, OK2CQR
--
http://HamQTH.com/ok2cqr
https://ok2cqr.com
The problem is that you have to install Apache2, PHP, Adminer or PHPMyadmin. I do not want to do this on a workstation, rather compicated.
That is the reason I asked to put everything into MyISAM and 1 database, as 1 simple command can backup the database.
As 1 simple command can import the same database into a remote server when you change it.
The way you use thew databases now is not simplistic and not backup/move friendly.
I do hope you see my reason and are willing to change it.
Thanks,
Bas.
Greetings, Bas. ON5HB
Hi Bas,
no you haven't to. You can create the dump of cqrlog DB without installing anything. Run cqrlog, open a terminal and put there this:
mysqldump --single-transaction -S /home/ok2cqr/.config/cqrlog/database/sock cqrlog001 > aaa.sql
where ok2cqr is my username. It will create SQL dump into aaa.sql of first log.
CQRLOG needs at least two databases. First is cqrlog_common where iota, dxcc tables, qsl managers are stored. The second is main log database called cqrlog001.
MyISAM is not good idea. It doesn't support transactions, foreign keys, when you insert/edit/delete to a table it's locked. InnoDB is fast enough.
73 Petr, OK2CQR
--
http://HamQTH.com/ok2cqr
https://ok2cqr.com
Thank you very much, I will try that later today.
As I have tried mysqldump earlier, it didn't work, but used different commands than you gave.
Greetings, Bas. ON5HB
I tried the backup that worked.
But then the import via mysql --user "name" --password "pass" cqrlog001 < aaa.sql
I can see the database is filled and the logs are in there, but any sign of logs or configs in the program is not to be found.
When I check the database by hand, it seems to be cleared after the first connection.
So again my wish to move all data into MyISAM and just one database, it's far easier to backup and restore.
I have done backup and restoring of databases before, but never so complicated as yours.
It's not a major issue (yet) as I'm not doing contests and such, but friends of mine do, and 1 central database would be very nice to use.
Also my experience in the past with databases (I was a hoster of websites) is that MyISAM (when done properly in my.cnf) is a lot faster then InnoDB.
Please conider this, or tell me how to use it, as all my efforts so far result in either rubbish or empty-database after first start.
I did even move mysql data from /var/lib/mysql into the newly created dir's, but that turns the entire program to show weird numbers and letters.
With MyIsam and just 1 database the simple solution to backup is:
mysqldump -u -p cqrlog > backup.sql
to restore:
mysql -u -p cqrlog < backup.sql
As you can see, far easier and a lot less complicated.
I'm also willing to beta-test for you if you like.
If this is not an option, I stick to local mysql, but I rather put it on my server as it has backup as well as raid systems in place.
Thanks.
Greetings, Bas. ON5HB
Bas,
the config file is stored in cqrlog_config table. After program starts, the config file is saved info text file to ~/.config/cqrlog/cqrlog.cfg and after program is closed, it's saved back to database.
MyISAM was faster in older version of MySQL. But what is most importat, it doesn't support transactions and foreign keys. CQRLOG uses this functions.
I backup my QSO with autobackup function which creates ADIF file from my log. This backup is created after I close the program.
When you moved the database, it should work without any problem. This seems to be something related to MySQL (apparmor?).
73 Petr, OK2CQR
--
http://HamQTH.com/ok2cqr
https://ok2cqr.com
I'm getting weird errors like: Warning: Can't find /home/bas/.config/cqrlog/xplanet/marker inxplanet/markers/home/bas/.xplanet/markers/usr/share/xplanet/markersWarning: Can't load marker file /home/bas/.config/cqrlog/xplanet/markerTApplication.HandleException : Error executing query: The user specified as a definer (''@'') does not exist Stack trace:
I did manage to make 2 databases, but it doesn't show anything.
Greetings, Bas. ON5HB
Bas,
please run cqrlog --debug=1 from console and paste here what it writes down. Thanks!
73 Petr, OK2CQR
--
http://HamQTH.com/ok2cqr
https://ok2cqr.com
Petr,
I'm going to do this in a few days, as I'm working people on the bands too :-)
And it takes a bit of time to switch everything.
When it's good weather like today, I rather ride my bike.
I post the results soon from the debug, no worries, we will work it out.
Greetings, Bas. ON5HB
Hi Petr,
Here is the debug output, remember, with the local database everything work, this happens only when using remote database:
**** DEBUG LEVEL 1 ****Loading libssl: /usr/lib/x86_64-linux-gnu/libssl.soLoading libcrypto: /usr/lib/x86_64-linux-gnu/libcrypto.soLoading libmysqlclient: /usr/lib/x86_64-linux-gnu/libmysqlclient.so.18Loaded 68668 LoTW usersLoaded 78590 eQSL usersLoaded 45735 SCP calls/home/bas/.config/cqrlog/database/workstation.pidCommand: kill 3670*User home directory: /home/bas/Program home directory: /home/bas/.config/cqrlog/Data directory: /home/bas/.config/cqrlog/database/Memebers directory: /usr/share/cqrlog/members/ZIP code directory: /usr/share/cqrlog/zipcodes/Binary dir: /usr/bin/Share dir: /usr/share/cqrlog/TConnection to MySQL: 5.5*select * from tables where table_schema = 'cqrlog_common'SELECT log_nr,log_name FROM cqrlog_common.log_list order by log_nruse cqrlog001use cqrlog001use cqrlog001SELECT * FROM cqrlog_configselect * from db_versionselect * from cqrlog_common.db_versionSELECT * FROM cqrlog_common.dxcc_ref ORDER BY ADIFSELECT * FROM cqrlog_common.dxcc_ref ORDER BY ADIFSettings:-----------------------------------------------------RigCtldPath:/usr/bin/rigctldRigCtldArgs:-m 311 -r /dev/ttyUSB0 -t 4531 RunRigCtld: TRUERigDevice: /dev/ttyUSB0RigCtldPort:4531RigCtldHost:127.0.0.1RigPoll: 300RigSendCWR: FALSERigId: 311Starting RigCtld .../usr/bin/rigctld -m 311 -r /dev/ttyUSB0 -t 4531 rigctld started!Connected to 127.0.0.1:4531Settings:-----------------------------------------------------RotCtldPath:/usr/bin/rotctldRotCtldArgs:RunRotCtld: FALSERotDevice: RotCtldPort:4533RotCtldHost:localhostRotPoll: 500RotId: 1rotctld started!Connected to localhost:4533Command line: /usr/bin/xplanet -config /home/bas/.config/cqrlog/xplanet/geoconfig -glare 28 -light_time -range 2.5 -wait 10 -geometry 550x550+2640+300 -window_title "CQRLOG - xplanet"Warning: Can't find /home/bas/.config/cqrlog/xplanet/marker inxplanet/markers/home/bas/.xplanet/markers/usr/share/xplanet/markersWarning: Can't load marker file /home/bas/.config/cqrlog/xplanet/markerselect * from view_cqrlog_main_by_qsodate LIMIT 500 OFFSET 0TApplication.HandleException : Error executing query: The user specified as a definer (''@'') does not exist Stack trace: $0000000000889893 $00000000008D6299 $00000000008D7D0A $00000000008C9BF1 $000000000088CAEA $00000000008C958F $000000000044A791 $00000000004497CA $000000000042E0E1 $0000000000717014 $000000000044B88A $00000000007254F5 $000000000071448A $00000000007143B0 $000000000044FA10 $0000000000712988 $0000000000731D2ASFI: 172A: 6K: 1GF: QUIETSSN: 151Time: 1425 UT 12 Feb 2014Warning: Can't find /home/bas/.config/cqrlog/xplanet/marker inxplanet/markers/home/bas/.xplanet/markers/usr/share/xplanet/markersWarning: Can't load marker file /home/bas/.config/cqrlog/xplanet/markerWarning: Can't find /home/bas/.config/cqrlog/xplanet/marker inxplanet/markers/home/bas/.xplanet/markers/usr/share/xplanet/markersWarning: Can't load marker file /home/bas/.config/cqrlog/xplanet/markerWarning: Can't find /home/bas/.config/cqrlog/xplanet/marker inxplanet/markers/home/bas/.xplanet/markers/usr/share/xplanet/markersWarning: Can't load marker file /home/bas/.config/cqrlog/xplanet/marker
I hope this info is right for you.
(sorry for the mess, your forum does this somehow).
Greetings, Bas. ON5HB
Hi Bas,
could you check if exist two views in your database?
73 Petr
--
http://HamQTH.com/ok2cqr
https://ok2cqr.com
view_cqrlog_main_by_callsign
view_cqrlog_main_by_qsodate
Yes they are in cqrlog001 database.
Greetings, Bas. ON5HB
Now I have no idea what could be wrong. Could you look at MySQL error log, please? Maybe there is something important.
--
http://HamQTH.com/ok2cqr
https://ok2cqr.com
No errors to be found in my mysql.err
The problem is not mysql, as that server ius running various mysql-stuff without problems.
I think this part is a major clue: Error executing query: The user specified as a definer (''@'') does not exist
I think you don't keep the user logged-on to the database, as it also shows no data in the qso-list at all.
Greetings, Bas. ON5HB
Bas,
CQRLOG keeps user logged in. It wouldn't work without it. Please try to login with your user name and password with mysql utility in the command line. I use CQRLOG on dedicated server without any problem. It's for testing only because I have to make some changes to CQRLOG code to let user choose what configuration will be save to local machine and what will be saved to database. Without this feaure, it's not so comfortable to have cqrlog on two machines and database on dedicated server.
73 Petr
--
http://HamQTH.com/ok2cqr
https://ok2cqr.com
Did you move the database from your local backup to remote?
Also is it using some other username, mine is using bas / test / port 3306, or I'm doing something really wrong exporting and importing te database to remote.
Did you test it on a database in use?
Greetings, Bas. ON5HB
Hello I am Using cqrlog-1.7.3 and had the same Problem after importing the sql Dump. It seems to be a common problem when exporting and importing an sql database and has nothing to do with cqrlog. cqrlog debug: select * from view_cqrlog_main_by_qsodate LIMIT 500 OFFSET 0 TApplication.HandleException : Error executing query: Access denied for user 'cqrlog'@'localhost' (using password: YES) Phpmyadmin presented the same error as ON2SNV noted: The user specified as a definer (''@'') does not exist The following solution worked for me: Open your previously created sql Dump and edit the lines including DEFINER: for example /*!50013 DEFINER=``@`` SQL SECURITY DEFINER */ to /*!50013 DEFINER=`cqrlog`@`localhost` SQL SECURITY DEFINER */ where cqrlog is the User privileged to use the cqrlog Database. Clear all old Tables and reimport the sql Dump. I Hope this was useful for someone else. 73 de DO4DEB
Thanks a lot man! I'm going to test your fix somewhere this week when I have time.
But one more question, my server is at 192.168.1.50, shouldn't the definer be: `cqrloguser`@`192.168.1.50` ?
Thanks.
Greetings, Bas. ON5HB
yes, i think you have to place the remote host instead of localhost when connecting from another computer. sorry for the messed up post. The Forum deleted all Newlines, and HTML Styles won´t work either.
I was having almost same problem. ON MY CASE:
I moved only the database to other server.
steps needed to do for this working:
1. edited dumped database file:
you need search DEFINER and change EVERY DEFINER to following:
DEFINER=`cqrlog`@`%`
this is very important, it will not work if you put IP address there, i tried it and took long time to find out this problem.(cqrlog gave alot nonsense errors)
searching answer was no luck, there is lack of documentation about this part.
2. set your sql-server to listen the interface ip instead of localhost. (and firewall the internet one)
on my case i use local private tunneled network trough server.
4. create cqrlog user to your sql-server, and do a permission for cqrlog/_% (default)
5. you must create also a log database to root of server(what i don't understand why) it will not work other way, so you need add permission manually for that database. there is no option to have database under a "folder/username"
6. import database
7. start program and setup ip and port, thats it. it will work, took 2 days from me to find out the errors and why i was not able to just "move the db".
hope this helps someone. remember always backup your db before editing it.
Best 73, OH1E Riku
Hello,
Petr OK7AN wrote:
I backup my QSO with autobackup function which creates ADIF file from my log. This backup is created after I close the program.
Is there a way to automatically transfer this file for example with scp to another machine?
73 de Hajo dl1sdz
Hi!
There are many ways to do replication to another PC (server).
Make start script for cqrlog. Define autobackup to desired folder with desired name (call-date-compressed) in preferences/auto backup.
Start scipt starts first cqrlog. When it is closed it will move new backup files using rsync.
----------
#/bin/sh
cqrlog
rsync -avzu ~/.config/cqrlog/YOURCALL*.gz YOURUSERNAME@YOURSERVER:~/.config/cqrlog/
----------
To get rsync working you have to start ssh server in YOURSERVER.
Test connect from your ham-pc and when it works create public key in ham-pc with command:
ssh-keygen
Default values are ok for keygen. After pub_key is generated copy it to YOURSERVER with command:
ssh-copy-id YOURUSERNAME@YOURSERVER
When copy is done try to log on YOURSERVER using:
ssh YOURUSERNAME@YOURSERVER
If it works and does not ask password you are ready to use rsync. If you give rsync parameters as "-avzun"
the "n" prevents anything happen (dry run) and you can test that it chooses right files and places them to right folder at YOURSERVER before using scrpt (without "n" parameter).
But as said: there are many other ways, too.
--
Saku
OH1KH
--
Saku
OH1KH
Thanks Saku,
that will be the way I am going. I thought that there was a built in solution. But finding a way around is more adventurous.
73 de Hajo dl1sdz