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:
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
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:
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:
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:
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:
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:
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!