Significant bug in version 2.6.4 date insertion code/DB

59 views
Skip to first unread message

Patrick Goetz

unread,
Aug 13, 2022, 3:29:30 PMAug 13
to AtoM Users
We recently upgraded from atom 2.6 to 2.6.4 and have run into a show stopping bug.

The first screenshot shows the focus on a date field in a test record. The sidebar indicates that YYYY is a valid date and year is what we typically use, as anything else would be misleading.

When I enter the date 1988 and click save I get a 500 internal server error (see screenshot 2)

It appears to be trying to insert the date 1988-0-0, which is of course an invalid date.  Checking the webserver logs confirms this:

2022/08/13 19:19:06 [error] 242#242: *1654 FastCGI sent in stderr: "PHP message: Unable to execute INSERT statement. [wrapped: SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect date value: '1988-0-0' for column 'start_date' at row 1]" while reading response header from upstream, client: 127.0.0.1, server: _, request: "POST /index.php/test/edit HTTP/1.1", upstream: "fastcgi://unix:/run/php7.2-fpm.atom.sock:", host: "erap-atx:8084", referrer: "http://catalog.episcopalarchives.org/index.php/test/edit"

The problem is really in the database schema:  Given that this field is set to datetime format, one cannot enter just a year.  See the mysql documentation for details:

  https://dev.mysql.com/doc/refman/8.0/en/datetime.html

Let me stress that we can't use atom if it becomes impossible to specify things like Creation time as a year only.  Putting in a precise date would be misleading to our users when often we don't have this information.

atom_screenshot_2.png
atom_screenshot_1.png

Kevin Bowrin

unread,
Aug 13, 2022, 6:21:36 PMAug 13
to AtoM Users
We also ran into this issue. You have to set the MySQL modes as described here: https://accesstomemory.org/en/docs/2.6/admin-manual/installation/linux/ubuntu-bionic/#mysql

From the MySQL docs:  The default SQL mode in MySQL 8.0 includes these modes: ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, and NO_ENGINE_SUBSTITUTION. 

By removing this mode, the database allows for datetimes with zero elements.

Best,
Kevin Bowrin
Carleton University Library

Dan Gillean

unread,
Aug 15, 2022, 8:47:15 AMAug 15
to ICA-AtoM Users
Hi Kevin, 

I'm a bit confused as to where your second post leaves this issue. As you will note by following the documentation link you shared, we do recommend explicitly setting the SQL modes, and we only recommend using 2 of them - neither of which are the restrictive date settings. From the link: 

Paste the following values in a new file at /etc/mysql/conf.d/mysqld.cnf and save:

[mysqld]
sql_mode=ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
optimizer_switch='block_nested_loop=off'

If I am understanding correctly, it sounds like the issue in your original post was resolved by removing the NO_ZERO_IN_DATE mode - is that correct? Are you still having issues? 

I've done a quick check in our public demo site by setting the default template to DACS and adding a new description with two year-only dates. It saves and displays correctly. I think your issue may have been a configuration one?

If you are still having issues, please clarify so we can offer further suggestions. 

Cheers, 

Dan Gillean, MAS, MLIS

--
You received this message because you are subscribed to the Google Groups "AtoM Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ica-atom-user...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/ica-atom-users/f03a154a-c3cf-44ad-832c-49971b534d67n%40googlegroups.com.

Dan Gillean

unread,
Aug 15, 2022, 8:52:41 AMAug 15
to ICA-AtoM Users
HAHAHHA oops, my apologies! 

It took me a minute to realize that this is in fact two different posters, and that Kevin was supplying a potential solution to Patrick. 

Patrick, have you been able to resolve this issue by setting the proper SQL modes? For further tips on how to do this, see: 
If you need instructions on how to access the MySQL command prompt, see: 

Dan Gillean, MAS, MLIS
AtoM Program Manager
Artefactual Systems, Inc.
604-527-2056
@accesstomemory
he / him

On Sat, Aug 13, 2022 at 6:21 PM Kevin Bowrin <kjbo...@gmail.com> wrote:

Patrick Goetz

unread,
Aug 16, 2022, 10:38:25 AMAug 16
to AtoM Users
Hi Dan and Kevin -

Actually I had the recommended modes set already:

    root@atom:/etc/mysql/conf.d# cat mysqld.conf
    [mysqld]
    sql_mode=ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
    optimizer_switch='block_nested_loop=off'

Notice the problem, though:  I misnamed the file .conf instead of .cnf.  Consequently it was being ignored:

# mysql -u root -p -e "SHOW VARIABLES LIKE 'sql_mode';"
Enter password:
+---------------+-----------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                                                 |
+---------------+-----------------------------------------------------------------------------------------------------------------------+
| sql_mode      | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+---------------+-----------------------------------------------------------------------------------------------------------------------+


changing the name from mysqld.conf to mysqld.cnf and restarting mysql solved the problem:

# mysql -u root -p -e "SHOW VARIABLES LIKE 'sql_mode';"
Enter password:
+---------------+---------------------------------------------------+
| Variable_name | Value                                             |
+---------------+---------------------------------------------------+
| sql_mode      | ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+---------------+---------------------------------------------------+


Thanks for the suggestions and sorry to be a dingbat.

Patrick Goetz

unread,
Aug 16, 2022, 10:47:48 AMAug 16
to AtoM Users
I don't see a way to mark this as SOLVED: in the header, but this issue has been resolved.  Thanks again.

Dan Gillean

unread,
Aug 16, 2022, 12:34:45 PMAug 16
to ICA-AtoM Users
Thanks for updating us with what you discovered and how it was resolved, Patrick! I've marked the thread as complete :)

Cheers, 

Dan Gillean, MAS, MLIS
AtoM Program Manager
Artefactual Systems, Inc.
604-527-2056
@accesstomemory
he / him

Reply all
Reply to author
Forward
0 new messages