I'm trying to understand a weird problem with a MySQL database on one of my websites.

When I loaded a big set of data from a CSV file (2,200,000 records with a single column, using LOAD DATA) into a table, my application started to misbehave.

After some time looking at the malfunctioning, I found out that some queries on that big table aren't working as expected.

I've tried the simplest query possibile to look for a certain entry in that table: querying for a certain value (that I know it's present) for a varchar(45) primary key column (ex. SELECT column_name FROM table_name WHERE column_name = "string_value"). Surprisingly the server returned me an empty set. I also tried SELECT COUNT(*) FROM table_name WHERE column_name = "string_value" and it, returned zero.

When I do a similar look-up for a value in a similar table (but with just a couple entries), the query works correctly and returns me a single row.

My question: is it possible that this case is some kind of bug with the MySQL database server?

I'm using MySQL version 5.6.35-80.0-log.

The two example tables structure contain two columns: a varchar(45) primary key and a bit(1) "boolean". The first column contains code string consisting of alphanumeric characters

  • 2
    I've seen numerous developers struggle with meaningful (trailing) white space or similar "invisible characters" in table names, column names and data i.e "name-value " != "name-value", which is a typo that admittedly can be quite hard to spot with many tools. – HBruijn Mar 30 '17 at 12:02
  • Your question is exclusively predicated on the content of the data - which we know nothing about. What does SELECT COUNT(*) FROM table_name tell you? – symcbean Mar 30 '17 at 12:10
  • @symcbean I updated the question and added my own answer – Zorgatone Mar 30 '17 at 12:14
  • 1
    @HBruijn thanks you were right about trailing white space. PhpMyAdmin failed to detect the correct line endings of the imported CSV file and I had trailing carriage-return characters in the code strings – Zorgatone Mar 30 '17 at 12:15

I solved my problem. When importing the CSV, PhpMyAdmin was detecting the wrong line endings and the records had a trailing carriage-return character

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.