CSV imports fail - stalling In Progress, Runtime Less than 1 second

Trying to restart my business after a two year hiatus, I can’t get import of Contacts and Companies to work again - all worked fine two years ago.

Even with the most basic CSV file, importing in the background stalls with the following in Import History:

Status: In Progress
Runtime: Less than 1 second
Progress: 0%

I don’t know what this means; has anybody else seen this? Elsewhere I get this fail message:

Import failed. Reason: %reason%
View details of [TEST CONTACTS.csv (85)](https://mymauticpath/s/lead/import/view/85)

Using the Import in the Browser option, it just stalls on this:

#### Please do not leave this page until complete or queued.

#### Import is in progress.

The Details list also gives me no clues:

Status info: unknown

This in Details looks wrong to me:

CSV parser config: delimiter = , , enclosure = \, escape = \\, batchlimit = 100

Enclosure should be " instead of \ I think - and in the UI it still does show ". Where is that CSV parser config set? Edit: A test.csv that avoided enclosures and escapes failed in the same, so this is probably not the cause.

In /var/logs the only thing I can find that may be relevant is this:

[2022-08-28 13:56:20] mautic.WARNING: File /home/myuser/public_html/mautic/app/../var/tmp/imports/20220828124859.csv was removed. [] {"hostname":"server.mysite.com","pid":31831}
[2022-08-28 13:14:01] mautic.WARNING: Command `mautic:import` exited with status code 1 [] {"hostname":"server.mysite.com","pid":880}

What else could I check or try? No idea how to troubleshoot this. I have already looked into cron jobs and think they’re OK.

Your software
My Mautic version is: v4.4.1
My PHP version is: 7.4.30
My Database type and version is: 10.3.36-MariaDB

Hi there - I am shooting in the dark here, but are you sure the csv file is formatted UTF-8.

Also in order to trouble shoot maybe make a csv file with just one column called email, add one email and see what happens

@mikew, thanks, I appreciate the response, but yes, I am aware it has to be UTF-8 and have tested with stripped down files, including a one column file with only email.

I have just tested with a delete.csv list of bounced emails for a clean-up process that worked with no problems before 2020. I now got the same ‘Runtime Less Than 1 Second/0% Progress’ result with even that one.

Does anyone recognise this error? What else could I check? Maybe something in PHP configuration? What parts are involved in this import process that could break? I get nothing on google searches or in Mautic forum and see nothing in Mautic documentation etc. that applies.

Should I look into PHP settings like these?:

max_execution_time This sets the maximum time in seconds a script is allowed to run before it is terminated by the parser. This helps prevent poorly written scripts from tying up the server. The default setting is 30.
PHP Default: 30

max_input_time This sets the maximum time in seconds a script is allowed to parse input data, like POST, GET and file uploads.
PHP Default: -1

max_input_time is set at 60 while the recommended default is -1. After changing that to -1, I get the following in Import History:

In Progress	
 DELETE.csv
Runtime: 18 hours 21 minutes	
Progress: 0%

Not sure if that is an improvement… When I then reimport the same delete.csv file, with just one column bounced email addresses, I get the same ‘In Progress/Less than 1 second/0%’ stalled result.

When I upload a fresh delete.csv file - one column of bounced emails for a clean-up process - and then try to mautic:import via SSH as ‘username’ (not root), I get the following error message:

Import 92 with 45 rows is starting.
  0/45 [>---------------------------]   0%
In AbstractMySQLDriver.php line 68:

  An exception occurred while executing 'INSERT INTO lead_event_log (user
  _id, user_name, bundle, object, action, object_id, date_added, properties,
  lead_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)' with params [1, "My Name", "lead", "import", "updated", 92, "2022-08-29 15:39:05", "{\"line\":3,
  \"file\":\"DELETE.csv\"}", "9670"]:

  SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update
  a child row: a foreign key constraint fails (`username_mautic`.`lead_event_log`, CONSTRAINT `FK_78B7E97955458D` FOREIGN KEY (`lead_id`) REFERENCES
   `leads` (`id`) ON DELETE CASCADE)


In Exception.php line 18:

  SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update
  a child row: a foreign key constraint fails (`username_mautic`.`lead_event_log`, CONSTRAINT `FK_78B7E97955458D` FOREIGN KEY (`lead_id`) REFERENCES
   `leads` (`id`) ON DELETE CASCADE)


In PDOStatement.php line 117:

  SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update
  a child row: a foreign key constraint fails (`username_mautic`.`lead_event_log`, CONSTRAINT `FK_78B7E97955458D` FOREIGN KEY (`lead_id`) REFERENCES
   `leads` (`id`) ON DELETE CASCADE)


mautic:import [-i|--id [ID]] [-l|--limit [LIMIT]] [-h|--help] [-q|--quiet] [-v|vv|vvv|--verbose] [-V|--version] [--ansi] [--no-ansi] [-n|--no-interaction] [-e|--env ENV] [--no-debug] [--] <command>


I’ve run into ‘foreign key constraint fails’ in other places, probably the same issue. Does anyone know what this relates to?

What is FK_78B7E97955458D?
What is the role of lead_event_log in importing contacts data via csv?
Is there a way to reset that table or whatever is causing these hang-ups?
What is involved in the import process; what should I check?
What does VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?) mean - looks bad…?
How can I solve ‘an integrity violation that seems to point to a foreign key that does not exist in the database’?

I’m at a dead end here. Any suggestions or best guesses how to troubleshoot this would be very much appreciated.

In this case a failing foreign key constraint was apparently caused by an encoding mismatch. Almost all of the tables in my database had collation utf8_unicode_ci. A few had utf8mb4_unicode_ci; I have converted those to utf8_unicode_ci and will test later if that made any difference…

What else could I check? Is there a way to safely “reset” the tables that cause these problems? Or does the ‘integrity constraint violation’ point to another problem - what problem does it point to?

Edit:

I am now converting all tables to utf8mb4_unicode_ci, following this recipe to fix the general foreign key mess.

Using alter table lead_event_log CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;, I get the following unusual error:

#1025 - Error on rename of ‘./mydatabase/#sql-a26_721a’ to ‘./mydatabase/lead_event_log’ (errno: 150 “Foreign key constraint is incorrectly formed”)

Same error on tables oauth2_accesstokens, oauth2_clients, oauth2_refreshtokens, users, …

Several other tables, like plugin_citrix_events, have this error:

Specified key was too long; max key length is 3072 bytes

Does anyone know what this means?

Back to this thread after a fruitless tangent here, trying to fix what I thought might be the core foreign key problem…

This still looks most promising for next things to try, with additional clue here and this here. However this solution failed here and here explained why it fails.

Were fixes implemented in latest updates? I’ll try to update to 4.4.2 to see if that fixes the csv import issue.

Next day:

Starting the 4.4.2 upgrade process I found the suggestion to run php bin/console doctrine:schema:validate. This gives me the following message:

`Mapping

[FAIL] The entity-class Mautic\DynamicContentBundle\Entity\DynamicContentLeadData mapping is invalid:

  • The association Mautic\DynamicContentBundle\Entity\DynamicContentLeadData#dynamicContent refers to the inverse side field Mautic\DynamicContentBundle\Entity\DynamicContent#id which is not defined as association.
  • The association Mautic\DynamicContentBundle\Entity\DynamicContentLeadData#dynamicContent refers to the inverse side field Mautic\DynamicContentBundle\Entity\DynamicContent#id which does not exist.

Database

[ERROR] The database schema is not in sync with the current mapping file.
`

This was raised as an issue here and is apparently also connected with the foreign key issue from my fruitless tangent here. What is the current status of this @escopecz @aerendir @DonGilbert @rcheesley ?

I don’t see a solution in that thread, but it has been closed as ‘stale’ - I’ll start googling and researching ‘unidirectional vs bidirectional association’ now, but am unlikely to come up with a solution for this on my own.

There is another thread on this without solution here.

I’ve been advised here that I can ignore the schema error for now, so I’ll try to do the upgrade to 4.4.2 in the next hours… Or bad idea? Anything else I should do/check?

Upgrading to 4.4.2 did not fix anything. I still have the same error. I’ll continue to bang my head against the wall about this next weekend. Any troubleshooting ideas or solutions or clues very much appreciated.