https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38788
Bug ID: 38788
Summary: Fix '0000-00-00' issue on database
Change sponsored?: ---
Product: Koha
Version: unspecified
Hardware: All
OS: All
Status: NEW
Severity: major
Priority: P5 - low
Component: Database
Assignee: [email protected]
Reporter: [email protected]
QA Contact: [email protected]
Continuation of the thread from
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31143#c90
I have mysql version 8.0.40-0ubuntu0.24.04.1 setup in the remote azure machine.
The infrastructure is setup such that there are 2 VMs, one acting as Koha
application server and another acting as MySQL database server. MySQL server
does not have koha installed but the credentials are setup such that
application server has access to it.
I am getting errors while running fix_invalid_dates.pl file.
DBIx::Class::Storage::DBI::_dbh_execute(): DBI Exception: DBD::mysql::st
execute failed: Incorrect TIMESTAMP value: '0000-00-00' at
./fix_invalid_dates.pl line 65
The line 65 contains this line:
my $invalid_dates = $rs->search( { $column => '0000-00-00' } )->count;
It is very obvious that the mysql database throws sanity errors when there are
errors in database. To attempt fixing it, I added these lines on top:
# Disable strict mode for this session
$schema->storage->dbh_do(
sub {
my ($storage, $dbh) = @_;
$dbh->do("SET SESSION sql_mode = ''"); # Disables all kinds of checks
in mysql for the whole script session
}
);
These seemingly did nothing. After running cli tool, it didn't return any
response. Especially when I try searching names of old patrons, it throws error
"month out of range" (there is possibility that the "borrowers" table is not
properly cleaned using that script)
What worked for me is below command when running mysql as root user:
UPDATE borrowers SET lastseen = NULL WHERE CAST(lastseen AS CHAR) = '0000-00-00
00:00:00'; -- 6000+ rows changed
UPDATE borrowers SET date_renewed = NULL WHERE CAST(date_renewed AS CHAR) =
'0000-00-00'; -- 3000+ rows changed
UPDATE categories SET enrolmentperioddate = NULL WHERE CAST(enrolmentperioddate
AS CHAR) = '0000-00-00'; -- 4 rows changed
Please suggest me if there are other tables having same issue too so that I can
manually fix it using mysql. I manually created and run the
fix_invalid_dates.pl file using koha-shell in koha 23.11 (which was originally
not backported) but I don't think the script file not working properly is
version-related issue at all.
--
You are receiving this mail because:
You are the assignee for the bug.
You are watching all bug changes.
_______________________________________________
Koha-bugs mailing list
[email protected]
https://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-bugs
website : http://www.koha-community.org/
git : http://git.koha-community.org/
bugs : http://bugs.koha-community.org/
