0

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

Zorgatone
  • 101
  • 5
  • 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

1 Answers1

0

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

Zorgatone
  • 101
  • 5