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

Product Support
1

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

2

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

3

@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.

4

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.

5

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.

6

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?

7

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.

8

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?

9

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.

10

Hi, what is this field?

username_mautic

?

11

@joeyk - thanks for the follow-up. I think I got the Mautic app administrator username there and also my own full name earlier in the error message - I am the only admin on the system.

I still get the same ‘Less than 1 second - 0%’ error after updating to 4.4.3.

According to mysqlcheck all database tables are OK, so I doubt rebuilding the database would fix anything.

???

Still completely stuck. What else could I check/try? Is there anyone who udnerstands the inner working of the code what would trigger these particular error messages - ‘less than 1 second’ etc.?

12

How can I replicate all the step in this CSV import process manually, to find out where it breaks?

I had already asked how to manually import contacts here and the last answer was essentially that it can’t be done (?).

It should be possible to just go manually through the steps of this failing CSV import process. What database queries are involved? Is there a way to find out, generate a list of SQL to go through?

Which scripts, files, functions are involved?

Etc.

13

Can you PM me an example of your CSV (just plz leave 1 line in there, so I can test.) I really want to see that migic csv that kills your instance :smiley:

14

After another diversion I now want to try to “fix” or “restore” the lead_event_log table with whatever solution I can find. I thought I could use mysqlcheck r- ..., but this site says mysqlcheck only works for MyISAM tables, not InnoDB.

Is that correct or is stellarinfo.com just trying to sell their software? What are my alternative options?

I can access ‘the MariaDB monitor’ via SSH, by simply typing mysql and enter, but logged in as root. Can I manage MySQL as root or will that cause all kinds of file permission and ownership problems again? Should I log in as some kind of user? With a user/pass for a specific database? How would I access a specific database?

15

What is the role of lead_event_log in importing via CSV?

I tried to change collation of that table to utf8mb4_general_ci via PHPMyAdmin, but get a ‘foreign key contraint incorrectly formed’ error message.

When I try to downgrade (?) that table from InnoDB to MYISAM I get this error message:

Error
Static analysis:

1 errors were found during analysis.

Missing comma before start of a new alter operation. (near "DEFAULT CHARSET" at position 51)
SQL query: Edit Edit

ALTER TABLE `mtc_lead_event_log` ENGINE = MYISAM DEFAULT CHARSET=utf8 COLLATE utf8_unicode_ci;

MySQL said: Documentation

#1071 - Specified key was too long; max key length is 1000 bytes

I tried ‘Optimize table’ in PHPMyAdmin, which returned this unhelpful message:

username_mautic.lead_event_log	optimize	note	Table does not support optimize, doing recreate + ...
username_mautic.lead_event_log	optimize	status	OK

‘… recreate + …’ is apparently ‘… recreate + analyze instead’.

And lo and behold, after two months without any useful help/hints from Mautic developers, it actually seems to have fixed my problem; import of the most basic CSV now results in ‘Runtime 2 seconds, Progress 100%, 2 inserted, 43 updated’.

16

I’m glad you fixed it.
I saw this error during upgrades from 2.16, but it didn’t come to my mind, that you should check DB collation, I’m sorry.

17

It’s not fixed. It’s still/again (?) failing with the same errors.

Edit: After ‘Optimize table’ on the same lead_event_log via PHPMyAdmin again, the import that kept failing went through.

18

I’m currently experiencing the same issue but while trying to update segments through the CLI and I see the error all over the log from the cron job to update segments,

Optimizing lead_event_log in phpmyadmin did not work for me unfortunately

19

Are there other tables involved you could try to optimize/fix?

Ultimately the foreign key errors seemed to have something to do with collation; I think you’re supposed to utf8mb4_unicode_ci instead of utf8_unicode_ci. Nobody knows.

20

The error seems to be exclusive to that table according to the errors in the image
Is there a way I can track the specific row with that key and just delete it?

[Album] imgur.com

coalition of this table is utf8_unicode_ci :frowning: