Problem with the date format after upgrading to version 2.6

[nelthario...@gmail.com]

Dear,

After updating the system from version 2.5 to version 2.6, the following error appeared

[Thu Aug 13 20:54:34.106067 2020] [php7:notice] [pid 25128] [client 10.14.0.220:18646] Unable to execute UPDATE statement. [wrapped: SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect date value: '1990-0-0' for column 'start_date' at row 1], referer: http://192.168.0.100/index.php/precsum-sum3/edit

This error appears every time I have to change an existing document

The date format has changed in this version?

Is there a possibility to change it?

Thanks in advance

[José Raddaoui]

Hi there,

This could be caused by using the default sql_mode from MySQL, which needs to be changed to allow zeros in dates (among other things):

https://www.accesstomemory.org/en/docs/2.6/admin-manual/installation/linux/ubuntu-bionic/#mysql

Best regards,

Radda.

[nelthario...@gmail.com]

Hi José!

Thank you for your help

so ... I followed the installation procedure

I had already made this change to the configuration file

[mysqld] sql_mode=ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

optimizer_switch='block_nested_loop=off'

Best regards

[José Raddaoui]

Hi,

I can't think of any other reason for this error. I guess it's a copy paste issue and that in your configuration file the sql_mode is in a new line and not in the same as [mysql]. Also, you could double check that the sql_mode is actually enabled in the running server (use the database user configured in AtoM):

$ mysql -u atom -p

mysql> SELECT @@sql_mode;
mysql> SELECT @@GLOBAL.sql_mode;

Bests,

Radda.

[Leeroy Jenkins]

Hi,

José, Thank you for your help

I ran the commands. Is the output ok?

My mysqld.cnf configuration file

José, If there is no way to change the format in the system, I will change the format of the dates in my documents.

At least the system is running well.

Best regards.

--
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/8ecbe7d8-0fdb-4cd1-ba22-b83c5cfd8ae0n%40googlegroups.com.

[José Raddaoui]

Hi,

Somehow, the configuration changes are not taking effect in the server, you should see the same sql_mode running the queries than the one from the config Maybe the configuration file is not located in the right path? Was the server restarted after changing the config?

I'd suggest that you try to fix the issue and get MySQL running with that sql_mode instead of changing the date format, as other modes may cause other issues.

Best,

Radda.

[Dan Gillean]

In the meantime, I have included some sample SQL queries that can be used to update the sql_modes directly in the database, which you could use to ensure that they match what is found in the configuration file. See: 

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

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/26a551ed-f577-4aa1-a99e-037c0d193495n%40googlegroups.com.

[nelthario...@gmail.com]

Thank you for your help

My configuration file is in the /etc/my.cnf.d/mysqld.cnf (default dir for rhel/centos distros)

mysql  Ver 8.0.21 for Linux on x86_64 (MySQL Community Server - GPL)

The server was not restarted after installation, only the database 

I will restart the server and retake the tests ... then I'll post the result

Best regards

[nelthario...@gmail.com]

Hi José,

I restarted the server but the error continues

I did some tests and got a change in the result of the command

sudo systemctl set-environment MYSQLD_OPTS="--sql-mode=ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"

systemctl restart mysqld.service

Does the result of the command SELECT @@sql_mode; and SELECT @@GLOBAL.sql_mode; have to be this?

Regards!

[José Raddaoui]

Hi,

That looks good to me, thanks for reporting back! I don't know a lot about CentOS nor `systemctl set-environment` and I'm not sure if it will be maintained in case of a server restart, but the zero dates should work with that sql_mode now.

Best regards,

Radda.

[Leeroy Jenkins]

Hi José!

when the server was restarted the settings were lost

To solve the problem definitively, i added the parameters in the mysqld.service configuration file

vim /usr/lib/systemd/system/mysqld.service

After that, then only execute:

systemctl daemon-reload
systemctl restart mysqld.service

Regards!!!

To view this discussion on the web visit https://groups.google.com/d/msgid/ica-atom-users/92640226-e786-4b58-8dad-608994b767b2n%40googlegroups.com.

[José Raddaoui]

  Thank you!