TMySQL57Connection : Error executing query: Incorrect date value

16 posts / 0 new
Last post
ON6MI
TMySQL57Connection : Error executing query: Incorrect date value

Hi,

After updating to version 2.4.0 I get some errors.

When I choose to open/create new log I get the following error:

Database upgrade crashed with this error:
TMySQL57Connection : Error executing query: Incorrect date value: '0000-00-00' for column 'lotw_qslrdate' at row 1840
----------------------
After clicking on OK I get this error:

TMySQL57Connection : Error executing query: Unknown column 'info' in 'field list'.

Press OK to ignore and risk data corruption.
Press Abort to kill the program.
-----------------------
If I try to open another log after this I get the message with an empty log window:

List index (0) out of bounds.

Press OK to ignore and risk data corruption.
Press Abort to kill the program.
-----------------------

What I did see during install is the following:

The package manager deleted the package libmysqlclient-dev and installed libmariadbclient-dev and libmariadbclient-compat

If I try to reinstall libmysqlclient-dev via the package manager he proposes to delete cqrlog, libmariadbclient-dev and libmariadbclient-compat

Before the update all worked well. What can be the problem here or do you need some more info from the debug function?

73

Rudi - ON6MI

oh1kh
TMySQL57Connection : Error executing query: Incorrect date value

Hi!
Just tried to create new log with 2.4.0 and it works.
Those errors look like upgrading log version has failed. They should not happen if you really create a new fresh log, not upgrading old one

When you create a new log at connect database window do you have several old logs there already?
Are the old logs in number order without any number missing?
If you have EX: logs 1,2,3,5,6 Now log tries to offer log number 4 do not use it, change to 7 or any bigger number.

I have that kind of problem (that I have been too lazy to fix with mysql). using the number between logs causes errors when trying to make/use new log.

-------------------------

If packet manager removes libmysqlclient-dev it happens because it installs MariaDB.
It is bit confusing but there are Mysql and MariaDB that both are Mysql servers. Installing just either one is enough.

Be sure that your linux now has only MariDB or Mysql related packages, not any mixed version of those two.

--
Saku
OH1KH

ON6MI
TMySQL57Connection : Error executing query: Incorrect date value

Hi Saku,

I have 14 logs with no gaps in between them.
To be more correct, the errors occur after I click on open or create new log, then choose a log and click on OK.
It started after the update to 2.4.0, before al worked well. I switched much between log 1 and 14 without problem, now there are these errors.
In the debug file I read something about [UpgradeMainDatabase] Old version: 16 cDB_MAIN_VER: 16

What can I do to resolve this problem?

I attached a debug file to this message, maybe that explains more than my words ;-)

73

Rudi - ON6MI

File: 

ON6MI
TMySQL57Connection : Error executing query: Incorrect date value

Update:

Problem seems to appear only with the first log, all the others open fine.

ok2cqr
ok2cqr's picture
Re: TMySQL57Connection : Error executing query: Incorrect date

Hi Rudi,

we can try this:

run cqrlog, do not choose any database, just leave the database connection with list of logs opened. Run a terminal and enter these commands:


mysql -S ~/.config/cqrlog/database/sock

it should open the mysql console. Now enter this:


use cqrlog001;
alter table freqmem add info varchar(25) null;
quit;

and now open the first log. It should work.

73 Petr, OK2CQR

oh1kh
Re: TMySQL57Connection : Error executing query: Incorrect date

You were 4 minutes faster....

It is strange that sometimes I found that ~ works with mysql and sometimes it requires full path to work.

--
Saku
OH1KH

ON6MI
Re: TMySQL57Connection : Error executing query: Incorrect date

Hi Petr,

I tried these commands but still get the error below on the first log:
Database upgrade crashed with this error:TMySQL57Connection : Error executing query: Incorrect date value: '0000-00-00' for column 'lotw_qslrdate' at row 1840.

The other errors are gone.

But the nice thing is that all qso's are back in the log, don't know where they were yesterday, very strange.

73

Rudi - ON6MI

oh1kh
Re: TMySQL57Connection : Error executing query: Incorrect date

Hi Rudi!

At that point where you are now make a backup copy of your logs and settings. Simply open command console and type:
cp -a ~/.config/cqrlog ~/.config/cqrlog_save

It takes awhile if you have big logs.
After that you could start again as Petr said:

run cqrlog, do not choose any database, just leave the database connection with list of logs opened. Run a terminal and enter these commands:

mysql -S ~/.config/cqrlog/database/sock

it should open the mysql console. Now enter this:

use cqrlog001;

After that copy this command to console (or type it):

select lotw_qslrdate from cqrlog_main where lotw_qslrdate = "0000-00-00";

To see how many qsos you have there with incorrect date. If there actually are those you can clean 0000-00-00 away with command:

update cqrlog_main set lotw_qslrdate=NULL where lotw_qslrdate="0000-00-00";

then give
quit;

And start cqrlog with log 001 to see if it works now.

--
Saku
OH1KH

ON6MI
Re: TMySQL57Connection : Error executing query: Incorrect date

Hi Saku,

All commands work except the last one, there I get the error:

ERROR 1292 (22007): Incorrect date value: '0000-00-00' for column 'lotw_qslrdate' at row 1

I restarted CQRLOG and choose log 1 and checked the value in the error box and it is '0000-00-00' so I don't know why it doesn't work.

73

Rudi - ON6MI

oh1kh
TMySQL57Connection : Error executing query: Incorrect date value

Hi!
Ok that makes sense that question is upgrading old log (or opening a log where upgrade has failed), not creating a new one.
Upgrading should work, but in case you have so many logs could it be possible that you have not opened that log for log time and cqrlog versions have changed many times during that time so that upgrading should jump over several log versions to reach current one?

Easiest way to fix this is to create new log then import backup of log 001 to it.
Otherwise manual mysql operations may help, if log is not badly corrupted.
Or if you have copy of ~/.config/cqrlog folder you could try to restore whole directory and try to open 001 again. (perhaps first with older cqrlog if there is long cap between versions)

Just doing some upgrades with "database connection" window (that is opended from "open or create new log") and it seems that you can not delete log number 001 (by reading then source).
But I have not tested that ever.
And I have not cleared out (from source) appears this only when there is one log, the 001, or is it true always as my interests are not on at that part of source now.

So if you get contents of log 001 restored to another log number it might be that you can not remove the faulty 001

--
Saku
OH1KH

ON6MI
TMySQL57Connection : Error executing query: Incorrect date value

Hi,

The first log is my main log I use all the time.
I created a new log and exported my 1st one.
Now it seems that my first log is empty, all qso's are gone and the adif file is empty too.
I think al qso's are lost. No backup available

Rudi - ON6MI

ok2cqr
ok2cqr's picture
Re: TMySQL57Connection : Error executing query: Incorrect date

Rudi,

compress the ~/.config/cqrlog directory and send it to my email petr@ok2cqr.com. The update crash can't delete any QSO from the log. There must be something else. CQRLOG can make backup to two different locations... . Did you upload QSO to HamQTH? It stores the ADIF file backups or if you used online log upload, I could export the QSO.

73 Petr

ON6MI
Re: TMySQL57Connection : Error executing query: Incorrect date

Hi Petr,

It is indeed a strange thing that all qso's are gone.
More strange is that the ADIF file is empty also.
The update function was turned off so there were no backups after closing the program.
I do upload all qso's to HamQTH but not with ADIF files, directly from CQRLOG, contact by contact.
I will send you the requested file.

ok2cqr
ok2cqr's picture
Re: TMySQL57Connection : Error executing query: Incorrect date

Hi Rudy,

I just sent you an email with fixed cqrlog directory. There were two problems:

- corrupted tables fixed by clicking in Connection window to Utils -> Repair log database. Without that the command with alter table didn't work
- fixing lotw_qslrdate that was 0000-00-00. I have no idea why.

QSO was fine, no data loss. Please enable auto backup to be sure.

73 Petr, OK2CQR

ON6MI
Re: TMySQL57Connection : Error executing query: Incorrect date

Hi Petr, Saku,

Thanks for fixing this and helping me out here.

All works fine like before.

73

Rudi - ON6MI

oh1kh
TMySQL57Connection : Error executing query: Incorrect date valu

Hey !

Try this (explanation at end of message):
open cqrlog and any log that is OK.
open console and then:

[saku@hamtpad ~]$ mysql --socket /home/saku/.config/cqrlog/database/sock
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 20
Server version: 10.3.17-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> use cqrlog004
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [cqrlog004]> show columns from freqmem;
+-----------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| freq | decimal(10,4) | NO | | NULL | |
| mode | varchar(12) | NO | | NULL | |
| bandwidth | int(11) | NO | | NULL | |
+-----------+---------------+------+-----+---------+----------------+
4 rows in set (0.002 sec)

MariaDB [cqrlog004]> alter table freqmem add column info varchar(25) null;
Query OK, 0 rows affected (0.005 sec)
Records: 0 Duplicates: 0 Warnings: 0

MariaDB [cqrlog004]> show columns from freqmem;
+-----------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| freq | decimal(10,4) | NO | | NULL | |
| mode | varchar(12) | NO | | NULL | |
| bandwidth | int(11) | NO | | NULL | |
| info | varchar(25) | YES | | NULL | |
+-----------+---------------+------+-----+---------+----------------+
5 rows in set (0.002 sec)

MariaDB [cqrlog004]> quit
Bye
[saku@hamtpad ~]$

Replace "saku" with your username
Replace cqrlog004 with cqrlog001
if "show columns from freqmem;" list does not have "info" then continue to "alter table freqmem add column info varchar(25) null;"

It looks like this could be the reason and fix for it.

--
Saku
OH1KH