Invalid datetime format

[thie...@gmail.com]

I'm trying to upload several EAD files via the command line into AtoM 4.2. I get an error message  

Unable to execute INSERT statement. [wrapped: SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect date value: '1977-0-0' for column 'start_date' at row 1]

In the EAD xml file I'm trying to upload I have 

<unitdate label="Dates" encodinganalog="245$f" type="inclusive" normal="1977/1978">1977-1978</unitdate>

As far as I can tell, that complies with the EAD standard for inclusive dates.

How do I get around this problem?

[Dan Gillean]

Hi there, 

I suspect that this has to do with the SQL Modes set in your MySQL database. Are you using MySQL version 5.6, or 5.7?

You can read more about SQL modes in MySQL here: 

• v5.7:  https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html
• See also:  https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sql-mode-changes
• v5.6:  https://dev.mysql.com/doc/refman/5.6/en/sql-mode.html

In any case, we have seen this error in the past when the NO_ZERO_IN_DATE mode is set. These SQL modes affect the SQL syntax MySQL supports and the data validation checks it performs. In MySQL 5.7, NO_ZERO_IN_DATE is enabled by default - our 2.5 installation instructions using MySQL 5.7 now include a section on how to create your own SQL modes configuration file:

First, let’s create a new file with our SQL modes.

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

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

Now we’ll restart MySQL:

sudo systemctl restart mysql

See: https://www.accesstomemory.org/docs/latest/admin-manual/installation/linux/ubuntu-bionic/#create-the-database

For both MySQL 5.6 and 5.7, you can also use SQL to check the modes set. First we will need to access the MySQL command prompt. As always, we strongly recommend making a backup of your database before you make any changes using SQL! 

Accessing the MySQL command prompt

To access the MySQL command prompt so we can run SQL queries,  we will need to know the MySQL username, password, and database name used during installation. If you can't recall for certain what credentials you used, you can always check in config/config.php - for example, to see this file you could run the following from the root AtoM installation directory, which should be /usr/share/nginx/atom if you have followed our recommended installation instructions: 

• sudo nano config/config.php

You should see the database name and credentials listed near the top of the file. You can also check your database username and password in /root/.my.cnf like so:

• sudo cat  /root/.my.cnf

Once you have the database name, MySQL user name, and password, we can use these to access the MySQL command prompt. Assuming in the following example that your database name is atom and your user and password are root, you could access the prompt like so: 

• mysql -u root -proot atom;

Notice that there is a space between the -u and root, but NOT between the -p and the root password. Alternatively, you can leave no password following the -p, and you will be prompted to enter it by the command prompt before proceeding. 

At this point, we should have access to the MySQL command prompt, which should look like this: 

mysql>

Note that you can type exit into the MySQL command prompt at any time to exit back to the unix command line. 

Checking the current MySQL modes

You can use the following query to see what SQL modes are currently set: 

• SELECT @@sql_mode, @@GLOBAL.SQL_MODE;
  

Changing SQL modes

The snippet above from our 2.5 documentation explains how to create a separate MysQL modes configuration file. If you are going to use this approach with 2.4, don't include the STRICT_TRANS_TABLES mode - otherwise, the process would be the same. 

You can also use SQL to update the modes directly, as outlined in the MySQL documentation here: 

• 5.6: https://dev.mysql.com/doc/refman/5.6/en/sql-mode.html#sql-mode-setting
  
• 5.7: https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sql-mode-setting
  

An example query for MySQL 5.7: 

• SET GLOBAL sql_mode = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
  
• SET SESSION sql_mode = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
  

Remember, if you are using AtoM 2.4, don't add the STRICT_TRANS_TABLES mode! 

Restarting services

If you have changed the modes in your MySQL instance, you will need to restart MySQL after. Run the following commands from AtoM's root installation directory - typically this is /usr/share/nginx/atom if you have followed our recommended installation instructions.  

• Ubuntu 14.04: sudo service mysql restart
• Ubuntu 16.04 and 18.04: sudo systemctl restart mysql
  

You should also clear all caches, so let's restart PHP-FPM, memcached (if you are using it), and clear the application cache as well: 

Restarting PHP-FPM:

• Ubuntu 14.04 with PHP 5.x: sudo service php5-fpm restart
• Ubuntu 16.04 or 18.04 with PHP 7.0: sudo systemctl restart php7.0-fpm
• Ubuntu 16.04 or 18.04 with PHP 7.2: sudo systemctl restart php7.2-fpm

Restarting memcached: 

• Ubuntu 14.04: sudo service memcached restart
• 16.04 and 18.04: sudo systemctl restart memcached

Clearing the application cache:

• php symfony cc

Now I'd recommend testing your import again, and seeing if that helps!

An EAD workaround to try if the above didn't work:

Finally, one thing you could try if this doesn't solve your problem - add the broadest valid possible date range to your @normal date values, like so: 

• <unitdate label="Dates" encodinganalog="245$f" type="inclusive" normal="1977-01-01/1978-12-31">1977-1978</unitdate>
  

AtoM's date range search works best with full YYYY-MM-DD values anyway - so while this is not an ideal workaround, it may lead to better search results when using the date range search. 

Let us know how it goes! 

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/9d123718-009e-4098-a62e-1bce67177273%40googlegroups.com.