Recently, we received a support request from one of our clients about a foreign key error when using the BulkMerge method from our Entity Framework Extensions library.
MySqlConnector.MySqlException: 'Cannot add or update a child row: a foreign key constraint fails (`database-1`.`Products`, CONSTRAINT `FK_Products_Manufacturers_ManufacturerId` FOREIGN KEY (`ManufacturerId`) REFERENCES `Manufacturers` (`Id`))'At first, nothing seemed strange:
- We knew they were using MariaDB.
- We knew it was a foreign key error.
Just by looking at the error message, we could assume that one of the products had a ManufacturerId that didn’t exist in the Manufacturers table. The error was very explicit. But this time? It turned out to be anything but normal.
We simply told our client to double-check their data and explained what the error meant. But they replied that all the data was correct.
Yeah right… obviously, we had our doubts! I mean, we just had to read the error again to know EXACTLY what was happening.
But you never know. Our library isn’t perfect, and we always give our clients the benefit of the doubt—until proven otherwise. So we asked for a project that could reproduce the issue.
The project
Very quickly, they created a project and sent it to us. Perfect—let’s run it.
No error! Of course…
So we started looking into what could be different—maybe the database structure, the entities, anything that might make sense.
We were both confused and starting to go crazy:
- The project WAS throwing an error on his machine.
- The project WAS NOT throwing any error on my machine.
He even simplified the project further to rule out anything that could interfere.
Still, same result. And to make things even more confusing, we both tried running it on different computers—with the exact same pattern:
- Error on his side
- No error on mine (on 3 different machines)
Again, this “foreign key error” made absolutely no sense. It was driving us nuts!
But when he told me he could reproduce the issue on a brand-new AWS database, I told myself: “Okay, let’s try this one more time.”
And boom—we finally got the same error!
Why? No idea yet. But just being able to reproduce the issue was already a big win.
The problem
We quickly found out that using a batch size of 187 or less didn’t cause the error, while a batch size of 188 or more did. (Our default batch size for MariaDB is 200.)
So at least we could give the client a temporary fix while continuing to dig deeper.
But what was the root cause?
No clue—yet. But we discovered that the problem came from the UPDATE part of our BulkMerge method.
That was progress.
Our library uses a lot of UNION ALL statements during the update—one for each entity to merge. Could that be the problem?
One of our developers suggested it could be related to internal limits—caching, memory, buffers, etc.
And guess what? He proposed a few configuration changes.
Ohhh, a great idea that led to another win!
We noticed that the MariaDB config in our test environment was different from the AWS one. So we started testing there.
Solution
Option 1 – Lower the batch size
We already knew reducing the batch size to 187 or less worked. Not the most elegant solution, but if needed, we suggested reducing it to 100 or 150 to be safe.
Option 2 – Modify the configuration
Thanks to ChatGPT (yep!), we discovered that tweaking a few config values fixed the issue.
We found two different sets of configuration combinations that solved the problem:
1. max_allowed_packet + net_buffer_length
max_allowed_packet = 67108864 # or more
net_buffer_length = 16384 # or more2. tmp_table_size + max_heap_table_size
tmp_table_size = 67108864 # or more
max_heap_table_size = 67108864 # or moreChatGPT also suggested changing a few other values to optimize the configuration. We don’t know enough about MariaDB tuning, so here’s the full list of suggested values:
| Parameter | Suggested Value (bytes) |
|---|---|
tmp_table_size | 67108864 (64MB) or more |
max_heap_table_size | 67108864 (64MB) or more |
max_allowed_packet | 67108864 or more |
net_buffer_length | 16384 |
join_buffer_size | 262144 |
sort_buffer_size | 2097152 |
read_rnd_buffer_size | 4194304 |
These settings worked well—but keep in mind, the issue could still come back if the batch size grows beyond what the configuration can handle.
Conclusion
So in the end, MariaDB raised a foreign key error… that had nothing to do with foreign keys.
The real issue was a configuration that couldn’t handle the large UNION ALL statements used in the update part of the merge. Unfortunately, we cannot change the way our library currently performs the UPDATE to bypass this.
But more importantly, we learned a solid lesson: even if the error looks obvious or makes no sense, always trust your clients and work with them to reproduce the issue. As long as communication stays open, progress will always be possible.