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: koha-bugs@lists.koha-community.org Reporter: adifbbk1@gmail.com QA Contact: testopia@bugs.koha-community.org 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.