Operations

Blended or BLENDED: VICIdial ids are case-insensitive

MySQL treats Blended and BLENDED as one VICIdial campaign, yet both spellings get stored. Where that breaks code, exports and reserved-name checks.

By Published 4 min read
Laptop screen showing debugging software with code, perfect for tech and software development themes.

To the point

To MySQL, a campaign called Blended and one called BLENDED are the same campaign, and VICIdial will store both spellings in different tables at once. Code that compares ids with a plain === decides they differ.

  • On one dialer we read, vicidial_campaigns held Blended while every row of vicidial_live_agents held BLENDED.
  • Affected ids: campaign ids, in-group ids, user names, carrier ids and DID route names. List ids are numbers and are safe.
  • Compare case-insensitively in code and keep the stored spelling for display.
  • Use BINARY in SQL only when you mean an exact match.

Why the database allows it

MySQL's default collations are case-insensitive: latin1_swedish_ci in older MySQL and MariaDB, and the _ci family in newer ones. In such a column 'Blended' = 'BLENDED' is true, an index lookup finds either, and a primary key refuses to hold both in one table. Different tables keep whatever the writer typed, and VICIdial's scripts do not normalize before writing.

SELECT campaign_id FROM vicidial_live_agents WHERE campaign_id = 'blended';

This returns rows stored as BLENDED, Blended or any mix. To force an exact match, ask for it:

WHERE BINARY campaign_id = 'Blended'

Check your collation with SHOW CREATE TABLE. A _bin collation behaves differently. We did not survey every deployment. On the dialers we looked at, matching ignored case.

Where to see the stored spelling

Test server screens below, campaign Sunline Solar Q4 callbacks (SUNQ4). Markers on each picture map to its list.

VICIdial campaign page with Campaign ID and Campaign Name highlighted
The campaign id as typed here is not necessarily the spelling other tables hold
  1. Campaign ID: the spelling the campaign was created with.
  2. Campaign Name: a free text label, separate from the id.

Where it bites

In application code. === in JavaScript, == in Python, Array.includes, Set.has, Map.get, a dictionary key and a switch are all case-sensitive. Normalize both sides at the comparison, not at storage. Wrap it in two helpers, such as sameId(a, b) and hasId(list, id), and upper-case both the stored values and the lookup value in any Set. Do not rewrite the stored value: the customer expects to see what they typed.

In exports. A CSV from vicidial_log and one from vicidial_live_agents can spell a campaign differently. Excel's VLOOKUP and MATCH ignore case. A pivot table may count the spellings as one. In pandas, merge is case-sensitive and silently drops rows that do not match. Lower-case the key on both sides first.

In SQL. JOIN ... ON a.campaign_id = b.campaign_id works. GROUP BY campaign_id merges the spellings and shows whichever MySQL picked, which is not defined. For the display spelling, join to vicidial_campaigns. The trap appears when you move the comparison out of MySQL, such as SELECT DISTINCT campaign_id into a program and comparing there.

In reserved-name guards. If a tool refuses to edit or delete the API user and system agents, the comparison must be case-insensitive on both sides. Api slips past a guard written for api, and MySQL treats the altered spelling as the protected user.

In logs. Use grep -i for a campaign id, and alert on the lower-cased form.

User names behave the same way. An agent logging in as Maria and a record created as maria are one user, so a spreadsheet that groups by name may split one person into two rows.

A worked example

Say vicidial_campaigns has Blended and four rows in vicidial_live_agents have BLENDED.

Diagram: the SQL join returns 4 agents and the strict JavaScript comparison returns 0 for the same data
Same data, two answers: the simpler code is the wrong one

The corrected filter, agents.filter(a => sameId(a.campaign_id, campaign.campaign_id)).length, returns 4. A fixture that spells the id one way everywhere passes either version, so test against a real server's data.

Two similar traps

Accents. Some collations ignore accents (_ai), so José and Jose may compare equal.

Trailing spaces. Under older PAD SPACE collations 'abc' equals 'abc '. Compare through MySQL when you can and trim before comparing outside it.

The habits

Checklist: compare ids case-insensitively, BINARY only for exact matches, normalize before merging, guard reserved names, test mixed case
Five habits for any code that reads VICIdial ids

Questions people ask

Can a table hold both Blended and BLENDED?

Not in the same table under a case-insensitive primary key. The two spellings end up in different tables because each keeps what the writer typed.

Which ids does this affect?

Anything that is a name, not a number: campaign ids, in-group ids, user names, carrier ids and DID route names.

Next steps

Add mixed-case ids to your test data today. If you are moving between servers, where casing can differ between source and destination, read what we copy and what we never copy. For the security side of the same habit, see securing a VICIdial server, or look at VICIdial hosting.

VICIdial is a trademark of its owner. Trustane is an independent hosting provider.

Sources

  1. Capacity findings, live agent and campaign data read from real dialers
  2. MySQL reference manual, case sensitivity in string searches
  3. Cover photo by Daniil Komov on Pexels

Trustane

Managed VICIdial hosting

Run it on cores of your own

Managed VICIdial, billed by the hour against a balance you top up.

Or try one server free for 7 days, no top-up.