My hamshack computer is a wonder box of many functions. It functions as my personal computer and as a local network server. I have recently upgraded to Ubuntu 20.04.1 from 18.04.x. On the personal side I use Ham radio applications. On the server side I am trying to set up a development/test server for web applications. On 18.04 I had CQRLOG working with MySQL as well as Joomla! working with the same server with the server running as a service. I am having the same issues as others trying to use MySQL and not getting it to work but that is another discussion.
My main question here is how do I set up CQRLOG with the existing database server? CQRLOG seems to want to run its own instantiation of the database server. What "magic" do I use as far as CQRLOG is concerned to have it recognize the existing server and use the database that I backed-up from my previous Ubuntu version which I think was running CQRLOG 2.4. I password protect the server just in case anyone penetrates my network for nefarious reasons.
Bill WD5HHH
Hi Bill!
When you start cqrlog does it open to "database connection" window or does it go direct to "New QSO" window?
If it goes to "New QSO" then select there "File/Open or create new log"
Now you should be in "database connection".
Uncheck "Save log data to local machine". You should see now extension with details of MySQL server. Fill them up with proper information. Your servers IP address and mysql server port (usually 3306) user and password that is allowed to access your server's log database.
Try to connect and if succeed you should see log list from database server. Check "auto connect" and "remember me" if needed.
Remember that you must open firewall of your server to allow external traffic to mysql port (usually 3306)
Switch between local mysql_safe and external server does not always work with cqrlog (if you did come from New QSO). That is unfortunately unresolved bug. Sometimes it works, sometimes not.
How ever when coming to "database connection" right from start, selection between local or external mysql server works properly.
If you like to switch them time to time leave "open recent log at startup" unchecked to have opportunity to select mysql server.
At your server side you have imported your log database (with mysqldump or similar). You can not use just copied ~/.config/cqrlog/database folder directly. After done that granted all privileges to username you are going to use for log database. I think this chain may be useful to read https://cqrlog.com/node/2979
--
Saku
OH1KH
Saku,
Do you have a mysqldump of an empty database that I can pull into my mariadb running as a service? I can't seem to connect to the cqrlog instance of mysqld (mariadb). It says it cannot connect to the socket.
mysqldump: Got error: 2002: "Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)" when trying to connect
Bill WD5HHH
Hi Bill!
You can move your existing log to server with mysqldump.
Open cqrlog. At Database connection window check "save data to local machine". That starts the mysql_save thread and you will see the log list of local machine.
At that point it has created socket file ~/.config/cqrlog/database/sock how ever I have noticed that the "~" does not always work propely with mysqldump so it is better to use full path with username /home/saku/.config/cqrlog/database/sock (replace to your username)
Then you can make mysqldump adding sock parameter to start parameters.
mysqldump -S /home/saku/.config/cqrlog/database/sock
See also this https://cqrlog.com/comment/8378#comment-8378
--
Saku
OH1KH
Hi
Forgot to say that you must modify the command in message https://cqrlog.com/comment/8378#comment-8378 so that it has socket paramater at both "mysql" and "mysqldump"
$(/usr/bin/mysql -B -N -S/home/saku/.config/cqrlog/database/sock -ucqrlog -pmypassword -e'show databases like "cqr%"' | /usr/bin/xargs /bin/echo -n mysqldump -S/home/saku/.config/cqrlog/database/sock -ucqrlog -pmypassword --databases) > /tmp/mycqrlogsdump.sql
Replace "saku" and "mypassword" with yours.
Without modification command works with server based logs.
--
Saku
OH1KH
When trying to load my cqrlog001 database from the dump file cqrlog_wd5hhh_201106.sql , I get the following error:
$ mysql -u cqrlog -p cqrlog001 < /home/bealwd/.config/cqrlog/cqrlog_wd5hhh_201106.sql
Enter password:
ERROR 1959 (OP000) at line 836: Invalid role specification ``
My best guess is that it is in this bit of the code:
/*!50001 CREATE ALGORITHM=UNDEFINED DEFINER=`` SQL SECURITY DEFINER VIEW
My database and sql knowledge is minimal so I have no idea how to fix the problem. I suspect that it will occur in a few lines below this one. I attached the whole dump file for you to look at.
Bill WD5HHH
File:
Hi Bill!
Yep. We have seen this before.
There is quite long thread about syncing two databases but from this message on
https://cqrlog.com/comment/8506#comment-8506
The definer problem is discussed.
I did get it working then with removing the definer as described in stackoverflow link.
--
Saku
OH1KH
Saku,
Thanks so much for your help in solving this. I replaced the 3 instances of DEFINER=' ' with DEFINER=CURRENT_USER (using vim in a remote terminal). I redid the command line upload and it worked great. I then used xrdp to remotely access a user on my Ubuntu box and ran Cqrlog. Everything seemed to work fine.
Bill WD5HHH
Fine Bill !
Glad to hear that.
Just made again a test try. For some reason (I have now Fedora 32) things are fixed somewhere.
There is no empty definer any more in this database.
[saku@tpad tmp]$ $(/usr/bin/mysql -B -N -ucqrlog -pmypassword -e'show databases like "cqr%"' | /usr/bin/xargs /bin/echo -n mysqldump -ucqrlog -pmypassword --databases) > /tmp/mycqrlogsdump.sql
[saku@tpad tmp]$ grep -i definer /tmp/mycqrlogsdump.sql
/*!50013 DEFINER=`cqrlog`@`localhost` SQL SECURITY DEFINER */
/*!50013 DEFINER=`cqrlog`@`localhost` SQL SECURITY DEFINER */
/*!50013 DEFINER=`cqrlog`@`localhost` SQL SECURITY DEFINER */
/*!50013 DEFINER=`cqrlog`@`localhost` SQL SECURITY DEFINER */
/*!50013 DEFINER=`cqrlog`@`localhost` SQL SECURITY DEFINER */
/*!50013 DEFINER=`cqrlog`@`localhost` SQL SECURITY DEFINER */
/*!50013 DEFINER=`cqrlog`@`localhost` SQL SECURITY DEFINER */
/*!50013 DEFINER=`cqrlog`@`localhost` SQL SECURITY DEFINER */
/*!50013 DEFINER=`cqrlog`@`localhost` SQL SECURITY DEFINER */
/*!50013 DEFINER=`cqrlog`@`localhost` SQL SECURITY DEFINER */
/*!50013 DEFINER=`cqrlog`@`localhost` SQL SECURITY DEFINER */
/*!50013 DEFINER=`cqrlog`@`localhost` SQL SECURITY DEFINER */
/*!50013 DEFINER=`cqrlog`@`localhost` SQL SECURITY DEFINER */
/*!50013 DEFINER=`cqrlog`@`localhost` SQL SECURITY DEFINER */
/*!50013 DEFINER=`cqrlog`@`localhost` SQL SECURITY DEFINER */
/*!50013 DEFINER=`cqrlog`@`localhost` SQL SECURITY DEFINER */
/*!50013 DEFINER=`cqrlog`@`localhost` SQL SECURITY DEFINER */
/*!50013 DEFINER=`cqrlog`@`localhost` SQL SECURITY DEFINER */
[saku@tpad tmp]$
--
Saku
OH1KH