CODEWITHSUNDEEP
mysqlcharacter-encodingstring
mirkobrankovic
mirkobrankovic

Reputation: 2347

String comparison not working in databases, files... could the character set be an issue?

I recently encountered a strange problem with strings:

I had 2 tables:

| AgentCodes | CREATE TABLE `AgentCodes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `AgentName` varchar(32) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `AgentName` (`AgentName`),
) ENGINE=ndbcluster AUTO_INCREMENT=319 DEFAULT CHARSET=latin1 |

and

| AgentNumbers | CREATE TABLE `AgentNumbers` (
  `AgentName` varchar(32) NOT NULL,
  `TelephoneNumber` varchar(64) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `AgentName` (`AgentName`),
  KEY `TelephoneNumber` (`TelephoneNumber`),
) ENGINE=ndbcluster AUTO_INCREMENT=62 DEFAULT CHARSET=latin1 |

LIKE didn't work on AgentCodes.AgentName table, I guessed it was because AgentName had unicode charset, so I changed charset on that field to Latin1.

Now the problem is AgentNumbers table is used as realtime table for AsteriskPBX queue Basically, I get the name of the Agent and put caller in that queue.

For example I get the name JohnDoe, the conf file looks like this:

[JohnDoe]
musicclass = temp
timeout = 35
member => SIP/...

And I get an error saying no such queue. I knew what the problem was so I copied the name from the database to that conf file and it worked.

My question is what could cause that problem, to see 2 strings one in file and other in database, copy/paste them in a third file (where they still look identical) and they are not recognized as being the same?

I guess it has something to do with Encoding/Charset, but I would like to hear why :)

Upvotes: 0

Views: 264

Answers (1)

gbn
gbn

Reputation: 432672

Don't forget MySQL LIKE does character wise comparison, not string comparisons. So things like trailing spaces matter. And we don't have the actual SQL, for example is it a literal or variable you are comparing to.

Also, why have AgentName in 2 different tables? This doesn't look normalised, and of course you are finding out now why normalisation matters.

Upvotes: 2

Related Questions