Significant bug in version 2.6.4 date insertion code/DB

[Patrick Goetz]

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.

[Kevin Bowrin]

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]

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

AtoM Program Manager
Artefactual Systems, Inc.
604-527-2056

@accesstomemory

he / him

--
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]

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: 

• https://accesstomemory.org/docs/latest/admin-manual/maintenance/common-atom-queries/#check-server-sql-modes
  

If you need instructions on how to access the MySQL command prompt, see: 

• https://accesstomemory.org/docs/latest/admin-manual/maintenance/common-atom-queries/#accessing-the-mysql-command-prompt
  

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]

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]

I don't see a way to mark this as SOLVED: in the header, but this issue has been resolved.  Thanks again.

[Dan Gillean]

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

To view this discussion on the web visit https://groups.google.com/d/msgid/ica-atom-users/ed19aa36-fbb9-4f52-b828-6ede5437c8een%40googlegroups.com.