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.
1 Like
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;”
This problem is still there. I know nobody at Mautic cares, just wanted to mention it. And the command from the previous comment doesn’t work with fancy quotes of course, so use this:
php bin/console doctrine:query:sql "OPTIMIZE LOCAL TABLE lead_event_log;"
Can you plz check if you have any index on the email column in leads?
That solved it for me.
1 Like
Thanks @joeyk. How/where would I check if I ‘have any index on the email column in leads’? How would I fix it if I don’t have any index?
There is an option ‘Add AUTO_INCREMENT value’ that you can apply to a table in PHPMyAdmin; is that adding an index? Or should I select all columns in the table and then click ‘index’ after ‘with selected’?
Or should I do “ALTER TABLE leads ADD INDEX(email);”?
Could unselecting the option ‘Add constraints’ from lead_event_log prevent future issues?
Optimizing the table via the SSH command in my previous comment failed, probably because ‘Table does not support optimize’; PHPMyAdmin does ‘recreate + analyze’ when you try optimize. That did “fix” it again. There is probably a command line way to do ‘recreate + analyze’. I’ll figure that out next time…