Forem: Moonshiner

The disappearing customer: How unstable sorting breaks pagination

Raphael — Thu, 17 Jul 2025 19:05:15 +0000

I have recently discovered a strange issue where a record in my database mysteriously disappears from a paginated API endpoint. And in fact, this is an issue that could happen to you as well, if your sorting is unstable. But let's start from the beginning.

The Starting Point

In my application, I have an endpoint that returns all customers, sorted by customer group. So each customer is assigned to a customer group, has a first name, a last name and some other fields. Because we have quite a few customers in the system, we also added pagination, so that the endpoint only returns 10 records at a time.

Note: The fact that I am using Laravel and the Eloquent ORM does not matter, it happens with any SQL query.

My query using the Eloquent Query Builder looks like this:

Customer::orderBy('customer_group_id')->paginate(10);

Which is equivalent to the following SQL query:

SELECT * FROM customers
ORDER BY customer_group_id
LIMIT 10 OFFSET 0;

The Problem: Unstable Sorting

At first, it seems logical. We first load all customers of group 1, then all customers of group 2 etc. But here's the catch: Although we loaded all pages in the frontend, one customer never showed up. Nowhere, on none of the pages - although they were in the database!
How is this possible? The reason is the following:

⚠️ For customers with the same customer_group_id, the database has no clear rule for how to order them within the group.

This is called unstable sorting and leads to a non-deterministic order of records. In our case that meant that for every page, the order of customers within a specific group was not consistent. While one customer did not get returned on any of the pages, others were returned on multiple pages. Not great.

The Solution: Add a unique tie-breaker

So let's look at how to fix it. To make your pagination stable and reliable, you need to ensure that sorting is deterministic. That means every record has a well-defined position in the order.
The fix? Add so many ORDER BY clauses that there isn't any ambiguity.

Laravel Query Builder:

Customer::orderBy('customer_group_id')
    ->orderBy('last_name')
    ->orderBy('first_name')
    ->orderBy('id')
    ->paginate(10);

Plain SQL:

SELECT * FROM customers
ORDER BY customer_group_id, 
last_name, first_name, id
LIMIT 10 OFFSET 0;

Without specifying the ID as a tie-breaker, you could still have unstable results if there are multiple customers who have the same first and last name. Since the ID is unique, this guarantees a consistent order across pages.

Final Thoughts

This issue is easy to miss, especially if there are lots of entries and only a few of them are non-unique. Debugging non-deterministic issues is never fun. But once you know what causes this problem, it's super easy to fix.

To save yourself the headache, here's my take-away for you:

➡️ If your sorting relies on a non-unique field, always add a unique, secondary ORDER BY column.️️

Written by Raphael Fleischmann, Team Lead Custom Software at Moonshiner.
This post was originally published on Raphael's personal blog: https://www.raphaelf.xyz/the-disappearing-customer-how-unstable-sorting-breaks-pagination/

Collation Confusion: How to Search in MySQL

Martin Sedláček — Thu, 22 May 2025 11:34:11 +0000

Have you ever received a ticket and thought to yourself — "That will be easy"? That was exactly the case for the ticket I received in one of our projects. The ticket was about adding an extra condition to the MySQL database query that would narrow results using a LIKE condition on a database column. The database query is already implemented, so I will just add one extra condition to it, pass a few parameters here and there, and the ticket is done. Easy! Right?

Well, I was WRONG!

After implementing the feature and going through the acceptance loop, I have discovered that filtering columns containing text using a LIKE operator in MySQL can be trickier than it seems, mainly when it comes to texts including special language-specific characters (all the ü, ř, and ß).

Let's dive in

Let's start with a small example. We have a database table with one column (charset utf8mb4 and collation utf8mb4_general_ci).
This table includes the following values - Fußball, summer, stress, Mädchen, aerobic. So let's try to execute a few queries with this table.

-- Stored values: Fußball, summer, stress, Mädchen, aerobic

-- Searching for ä
SELECT * FROM `foo` WHERE text_column LIKE "%ä%";

+-------------+
| text_column |
+-------------+
| Fußball     | 
+-------------+
| Mädchen     |
+-------------+
| aerobic     |
+-------------+

-- Searching for ß
SELECT * FROM `foo` WHERE text_column LIKE "%ß%";

+-------------+
| text_column |
+-------------+
| Fußball     |
+-------------+
| summer      |
+-------------+
| stress      |
+-------------+

Not sure if the results of this example were the ones you would expect, but for sure, they were a bit off for us. So I started investigating how the matching in MySQL even works.

Column Collations

For storing values in the database, MySQL uses Character sets (we won't be talking about them in this post). But when the values are already retrieved and MySQL needs to compare them, then Collation is used. That means that the Collation heavily influences any operation requiring comparison of characters (such as filtering, sorting, etc.).

The Collation is always bound to a specific Character set, but for simplicity, we will be using only the Character Set utf8mb4 for the rest of this post, as it is the most common one.

`general` vs. `unicode` Collation

For Unicode Character sets, there are two most common Collations - xxx_unicode_ci and xxx_general_ci.

The recommended column collation for MySQL database is the xxx_general_ci, as it is the fastest one ⚡ to perform the operations. However, with this Collation, the search results are less correct than with the xxx_unicode_ci. The xxx_general_ci only supports mapping one character to exactly one character. On the other hand, the xxx_unicode_ci supports more complex mapping, where one character can also represent a sequence of characters, or some characters can be ignored.

This can be very well seen in the example of ß.

ß = s   -- For utf8mb4_general_ci
ß = ss  -- For utf8mb4_unicode_ci, as it supports mapping one character to multiple characters

This shows that when the precise character comparison is required, setting the correct Collation can make a difference.

So if we look at our example from the beginning, we can see that, for example, the row containing the word summer was selected, as the ß was "converted" to normal 's' (as we used utf8mb4_general_ci), and that matches the condition.

Language-specific Collations

But what if you want to be even more specific and need to follow really strict rules when comparing characters? Then, MySQL offers many language-specific Collations that include some additional rules for given languages.

So, if you, for example, require German DIN-2 (phone book) ordering, you can use the utf8mb4_german2_ci, which has this set of additional rules

Ä = Æ = AE
Ö = Œ = OE
Ü = UE
ß = ss

The problem

But wait! What if I don't like the behavior shown in the example at the beginning? What if I only want to match the words with either ß or 'ss'? Is there anything I can do with the knowledge about the Collations?

Let's refresh the current state - Collation: utf8mb4_general_ci; Values: Fußball, summer, stress, Mädchen, aerobic.

-- Stored values: Fußball, summer, stress, Mädchen, aerobic

-- Searching for ß
SELECT * FROM `foo` WHERE text_column LIKE "%ß%";

+-------------+
| text_column |
+-------------+
| Fußball     |
+-------------+
| summer      |
+-------------+
| stress      |
+-------------+

-- I don't want the "summer" to show up, 
-- so I change the collation to utf8mb4_general_ci
SELECT * FROM `foo` WHERE text_column LIKE "%ß%" COLLATE utf8mb4_unicode_ci; 

+-------------+
| text_column |
+-------------+
| Fußball     |
+-------------+

After changing the Collation to utf8mb4_unicode_ci, the extra result is gone, and we are only getting the result with a proper match.

The catch with `LIKE` operator

However, have you noticed something strange in the last example? When we changed the Collation to utf8mb4_unicode_ci, the summer also disappeared from the result set.

That seems to go against the facts explained above, right? As we said that for utf8mb4_unicode_ci, the mapping of character is the following - ß = ss.

But actually, this has very little to do with the Collation itself. It has something to do with how the LIKE operator performs matching. Quoting from MySQL documentation, the LIKE performs a per-character matching.

Per the SQL standard, LIKE performs matching on a per-character basis, thus it can produce results different from
the = comparison operator.

That means that even if you use the correct Collation, which correctly maps the characters to their multi-character forms, you might experience an unexpected behavior when using a LIKE operator.

SELECT "ß" LIKE 's' COLLATE utf8mb4_unicode_ci; -- FALSE
SELECT "ß" LIKE 'ss' COLLATE utf8mb4_unicode_ci; -- FALSE
SELECT "ß" LIKE 'ß' COLLATE utf8mb4_unicode_ci; -- TRUE
SELECT "ß" == 'ss' COLLATE utf8mb4_unicode_ci; -- TRUE

SELECT "ß" LIKE 's' COLLATE utf8mb4_general_ci; -- TRUE
SELECT "ß" LIKE 'ss' COLLATE utf8mb4_general_ci; -- FALSE
SELECT "ß" LIKE 'ß' COLLATE utf8mb4_general_ci; -- TRUE
SELECT "ß" = 'ss' COLLATE utf8mb4_general_ci; -- FALSE

Conclusion

When concluding this topic, it might be a good time to reflect on the initial task that was given — "Add an extra condition to an existing database query."

Such a simple task and so many implications. If you look at it from the perspective of how complex this topic can get, you might think, you should not use a MySQL database for searching and filtering. And you might be right.

However, it's also pretty important to see it in the bigger picture, and maybe, it is, in some cases, just the best solution you have. And if you keep in mind all the quirky behaviors that MySQL has when it comes to character comparison, you might be just completely fine when you use it the next time.

Your First Moves in Enterprise Architecture – A Strategic Guide to Getting Started

Raphael — Thu, 17 Apr 2025 09:23:03 +0000

So you want to get started with Enterprise Architecture (EA)? This guide will help you take the first steps successfully. The beginning feels like hacking your way through a dense jungle of many applications, technologies, teams and processes. But with the right tools and a clear strategy, you can cut a path towards a future-proof business. So let's start this expedition!

🌀 Define Your Objectives: Find Your Why

First things first: Why are you diving into EA? Before moving on, take a step back and look at the big picture. What is the organization trying to achieve? Are you aiming to align your business and IT? Perhaps you're on a quest to make your organization more agile, or double-down on digital transformation? Or maybe you're striving to reduce costs – or the opposite: expand rapidly?

Whatever the reasons, make sure to clarify them with top management and articulate them clearly. These objectives are your North Star, guiding you through the fog of enterprise complexity. Write them down, share them widely, and refer to them often. Without understanding the business strategy, architecture efforts will lack direction.

Your success depends on your relationships with upper management. It's office politics, but in a positive way. Show them your superpowers. These are your capabilities to bring structure into chaos, to lead initiatives and to explain complex technical topics in simple words. Once they trust you, you'll have the backing you need to make real, impactful changes.

🦕 Assess Your Current State: Digital Archaeology

Time to play detective! Investigate your current application landscape, business processes, information systems, and infrastructure. It's like archaeology, but instead of dinosaur bones, you're discovering legacy systems and forgotten databases. Create a comprehensive map of all your existing applications, their functions, and how they interact.

Also document their "ingredients": the current technology stack. List out languages, frameworks, infrastructure providers, databases and standard software. You might find some ingredients that are well past their expiration date!

🔠 Categorize and Prioritize: What's Hot, What's Not?

Categorize your applications and tech stacks. Which ones are strategically important, and which ones are the Internet Explorer of your organization (in desperate need of change)? Focus on the important ones - you're an architect, not a magician. You cannot fix everything at once. To make prioritization easier, you might want to take a look at Gartner's TIME framework.

🔮 Develop a Vision for the Future

Based on your objectives and current state, now it's all about your vision for the future. Ensure this vision aligns with your organization's overall strategy.

Establish a set of architecture principles that will guide you through the transformation. These principles are not just goals, they're your architectural mantras. Cover aspects like technology standards, information management and security. Make them clear, memorable, and maybe even a little witty.

Next, paint a picture of your future architecture. This is where you get to channel your inner Da Vinci (but with more rectangles and arrows). Which applications and technologies do you want to improve, replace or sunset?

🗺️ How will you get there? Create a Roadmap

Now that you know where you are and where you want to go, it's time to figure out how to get there. Break down your journey into manageable phases and projects, with some milestones that you can use as checkpoints. One thing is clear: for this step you need your application experts on board, to get a realistic estimation of effort and lead time. Finally, you need management approval to get the necessary resources to make your ideas come true.

🤝 Collaboration, Support and Governance

It's time to establish processes for quality gates, architecture reviews and compliance checks. If needed, create a list of approved services and a process for exceptions. But remember, you're here to improve the organization, not to be the architecture police. Support projects, offer guidance and closely collaborate with architects, developers and product managers. Maybe you can even provide your teams with a convenient shared platform that they can build upon, to simplify and speed up their development.

📈 Try Out, Improve, Repeat.

Nothing is perfect from the beginning, but this should give you a good start into Enterprise Architecture. You can always iterate and continuously improve the status quo. Listen to what your colleagues say (and maybe what they think but don't say), monitor progress and read up about best practices and EA frameworks. Then you will definitely be successful! I wish you all the best!

Written by Raphael Fleischmann, Team Lead Custom Software at Moonshiner.
This post was originally published on Raphael's personal blog: https://www.raphaelf.xyz/your-first-moves-in-enterprise-architecture/