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.
Hi, what is this field?
username_mautic
?
@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.?
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.
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
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?
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’.
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.
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.
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
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.
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?
coalition of this table is utf8_unicode_ci
I would NOT delete random rows!
Could you try changing collation to utf8mb4_unicode_ci - same for all tables ideally I guess - and then optimize/fix the table?
Tried again since last time i didn’t optimize, noticed not al tables were mb4,
It seems like its not possible:
#1833 - Cannot change column ‘id’: used in a foreign key constraint ‘FK_CA0A2625A8752772’ of table ‘vLYHJqU8Br9orB.email_stats’
Deleted segment NO EMAIL which worked perfectly before and the segment update went through.
Will keep an eye on logs, I assume at some point whatever the root issue is will reappear.
Did you use command line/ssh when you tried to convert to utf8mb4_unicode_ci? I think I had the same problem with a bunch of tables, but was later able to convert them using PHPMyAdmin with no issues.
Another regular import. Companies import went through fine. Contacts import stalls with the same ‘Less than 1 second - 0%’ error again.
So I have to ‘optimize’ the lead_event_log table again in PHPMyAdmin I guess…
Edit: …and yes, the same csv file went through after “optimizing” that table again. Why does this keep happening? Any way/plans to fix it?
Just fyi Mautic developers/managers, I still have to regularly “optimize” lead_event_log to get around this error, so this is not solved. Any suggestions/fixes still very much appreciated.
Found this command, probably quicker than PHPMyAdmin next time I’ll need this:
php bin/console doctrine:query:sql “OPTIMIZE LOCAL TABLE lead_event_log;”