Exception: Unable to execute INSERT statement. [wrapped: SQLSTATE[HY000]: General error: 1366 ....

[sergio folco]

Hi, I am populating ATOM (v 2.5.3).During the DIP uploading (pdf + dublin core metadata ) from the Archivematica, this job failed because I have received the message:

Exception: Unable to execute INSERT statement. [wrapped: SQLSTATE[HY000]: General error: 1366 Incorrect string value: '\xF0\x9D\x90\xB7\xF0\x9D...' for column 'value' at row 1]

I recognise this problem depends on mysql and (I am not sure) on the passed metadata values that are perhaps  not UTF-8. Although this exception, the digital objects appears in the "Item List", but it is  not clickable and I cannot delete it from this list because I receive the message to remove all "File" content. In fact the item seems a zombie because it is not published, but the status is "[Item]" instead of "(Draft) [Item] XXXXXXXX"

Questions:

1. How can I remove this record from the database? 

2. How can I understand the INSERT command and prevent this problem?

Thank  you

[Dan Gillean]

Hi Sergio, 

I think you're correct that this was probably caused by the CSV not being properly UTF-8 encoded. I suspect that the resulting information object in AtoM is corrupted - either incomplete, or containing unexpected data in columns that are affecting its display. 

We have some tips on finding and resolving data corruption on our Troubleshooting page, here: 

• https://www.accesstomemory.org/docs/latest/admin-manual/maintenance/troubleshooting/#dealing-with-data-corruption  
  

You could start by running two tasks that resolve common issues - rebuilding the nested set and generating slugs. From AtoM's root installation directory, try: 

• php symfony propel:build-nested-set
• php symfony propel:generate-slugs

Now we can clear the application cache and restart PHP-FPM and see if it has helped. 

• Clear the cache: php symfony cc
• Restart PHP-FPM: 
• Using PHP 7.0: sudo systemctl restart php7.0-fpm
• Using PHP 7.2: sudo systemctl restart php7.2-fpm

If you are able to access the problematic description in the browser, and can see the slug, then we can use that to delete the record using SQL. 

Accessing MySQL

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>

Finding the information object ID

If you have the slug of the description, we can use that to return the information object ID with the following query:

• SELECT io.id FROM information_object io JOIN slug s ON s.object_id=io.id WHERE s.slug='slug-goes-here;
  

Replace slug-goes-here in the example query above with the slug of your problem description. 

If you don't have the slug but you do know the title (and importantly, the title is UNIQUE in your AtoM installation), you could find the information object ID using the title with the following query instead: 

• SELECT id FROM information_object_i18n WHERE title='Title goes here';
  

Replace Title goes here in the example query above with the title of your description. 

Using the information object ID to delete a record

Once we have the object ID, we can use that to delete the record from the object table - this deletion will then cascade through other related tables in the database, removing the record: 

• DELETE FROM information_object WHERE id=12345;
  

Replace 12345 with the object ID you retrieved using the queries above. 

I recommend that you clear the cache, restart PHP-FPM, and re-index your site after making any deletions using SQL! Repopulating the search index:

• php symfony search:populate
• See:  https://www.accesstomemory.org/docs/latest/admin-manual/maintenance/populate-search-index/

If that still doesn't work

If the above solutions won't work for you, then there are a few more suggestions in the Data corruption section of the Troubleshooting documentation: 

• https://www.accesstomemory.org/docs/latest/admin-manual/maintenance/troubleshooting/#dealing-with-data-corruption  
  

Let us know what you've tried, and what the outcome was for each attempt. We can try to provide some further suggestions if needed!

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/05cebb18-c023-40e7-b79b-3c9efee76ecd%40googlegroups.com.