Remote DataBase

26 posts / 0 new
Last post
UA6CEY
Remote DataBase

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!

ok2cqr
ok2cqr's picture
Re: 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

on5hb
on5hb's picture
But there is no way to export

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

ok2cqr
ok2cqr's picture
Re: But there is no way to export

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

on5hb
on5hb's picture
The problem is that you have

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

ok2cqr
ok2cqr's picture
Re: The problem is that you have

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

on5hb
on5hb's picture
Thank you very much, I will

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

on5hb
on5hb's picture
I tried the backup that

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

ok2cqr
ok2cqr's picture
Re: I tried the backup that

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
 
 

on5hb
on5hb's picture
I'm getting weird errors like

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

ok2cqr
ok2cqr's picture
Re: I'm getting weird errors like

Bas, 
 
please run cqrlog --debug=1 from console and paste here what it writes down. Thanks!
 
73 Petr, OK2CQR
 

on5hb
on5hb's picture
Petr,

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

on5hb
on5hb's picture
Debug messages

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

ok2cqr
ok2cqr's picture
Re: Debug messages

Hi Bas,
 
could you check if exist two views in your database?
 
73 Petr

on5hb
on5hb's picture
view_cqrlog_main_by

view_cqrlog_main_by_callsign
view_cqrlog_main_by_qsodate
Yes they are in cqrlog001 database.
 

Greetings, Bas. ON5HB

ok2cqr
ok2cqr's picture
Re: view_cqrlog_main_by

Now I have no idea what could be wrong. Could you look at MySQL error log, please? Maybe there is something important.

on5hb
on5hb's picture
No errors to be found in my

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

ok2cqr
ok2cqr's picture
Re: No errors to be found in my

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

on5hb
on5hb's picture
Did you move the database

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

do4deb
Dump Import Error

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

on5hb
on5hb's picture
Going to try this week.

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

do4deb
yes, i think you have to

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.

OH1E
a possible fix

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

dl1sdz
Hello,

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

oh1kh
Backup

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

dl1sdz
Thanks Saku,

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