MySQL GROUP_CONCAT returning BLOB with empty result error

Question

I have to 3 tables to establish one-to-many relationship with files and categories my table schema is as shown below:

categories table

CREATE TABLE `categories` (
    `id` INT(3) NOT NULL  AUTO_INCREMENT,
    `category_name` VARCHAR(25) NOT NULL,
    `visible_name` VARCHAR(25) NOT NULL,
    `is_active` TINYINT(1) NOT NULL,
    PRIMARY KEY (`id`),
    INDEX `idx_file_category_visible_name_colum` (`visible_name`)
)
 ENGINE = INNODB;

files table

CREATE TABLE `files` (
    `id` BIGINT(20) NOT NULL  AUTO_INCREMENT,
    `title` VARCHAR(200) NOT NULL,
    `filename` VARCHAR(255) NOT NULL,
    `description` TEXT NOT NULL,
    `date_uploaded` BIGINT(20)                      DEFAULT 0,
    `last_updated` BIGINT(20)                      DEFAULT 0,
     PRIMARY KEY (`id`),
     UNIQUE KEY `idx_unique_file` (`filename`)
)
 ENGINE = INNODB;

file_categories table

CREATE TABLE `file_categories` (
    `fkfile` BIGINT(20) NOT NULL,
    `fkcategory` INT(3) NOT NULL,
    PRIMARY KEY (`fkfile`, `fkcategory`),
    FOREIGN KEY (`fkfile`)
    REFERENCES files (`id`)
      ON UPDATE CASCADE
      ON DELETE RESTRICT,
    FOREIGN KEY (`fkcategory`)
    REFERENCES categories (`id`)
      ON UPDATE CASCADE
      ON DELETE RESTRICT
)
ENGINE = INNODB;

Here are the sample data to I tested with:

INSERT INTO `stackoverflow_qst`.`files` (`id`, `title`, `filename`, `description`, `date_uploaded`, `last_updated`) VALUES (NULL, 'First Dummy File', 'fdf.pdf', 'Just a dummy file', '1552295385043', '0');

INSERT INTO `stackoverflow_qst`.`categories` (`id`, `category_name`, `visible_name`, `is_active`) VALUES (NULL, 'Category 1', 'Category One', '1'), (NULL, 'Category 2', 'Category Two', '2'), (NULL, 'Category 3', 'Category Three', '1');

INSERT INTO `stackoverflow_qst`.`file_categories` (`fkfile`, `fkcategory`) VALUES ('1', '1'), ('1', '2'), ('1', '3');

On selecting the files a wish to select all categories ids to that file as comma separated column with the file my selection query as as shown below:

SELECT f.id, f.title, f.filename, f.description, f.date_uploaded, f.last_updated, 
GROUP_CONCAT(DISTINCT fc.fkcategory ORDER BY fc.fkcategory ASC ) AS categories_ids 
FROM `files` AS f 
INNER JOIN `file_categories` AS fc ON (fc.fkfile = f.id) 
GROUP BY fc.fkfile

It's returning other columns correctly except for the categories_ids column which returns a BLOB with an error on preview as shown in this screenshot: categories_ids column BLOB preview Query Result in PHPMyAdmin

I have gone ahead to query just the file_categories table as comma separated but it still returns a blob as usual. I have looked up related question here on StackOverflow and I my query seems ok, some of my lookup are: MySQL : Multiple row as comma separated single row(The answer here is very direct and even point to MySql Doc), MYSQL comma separated IDs vs separate table, and SQL Join multiple ids comma separated but I'm still stuck with the same problem, does it have to do with the MySql Version I'm using?

Currently MySql Version 5.1.41, strictly for the server on which my current project would be deployed.

Answer 1

Edit:

Figured it out. GROUP_CONCAT requires a string. A TEXT or BLOB type is usually returned. Considering that your data is the INT type, MySQL appears to be opting for BLOB. To get TEXT back, you need to first convert/cast your integer values to character values.

GROUP_CONCAT reference: https://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html#function_group-concat

Try this out:

    SELECT 
      f.id,
      f.title,
      f.filename,
      f.description,
      f.date_uploaded,
      f.last_updated, 
      GROUP_CONCAT(DISTINCT CONVERT(fc.fkcategory, char(8)) ORDER BY fc.fkcategory ASC ) AS categories_ids 
    FROM `files` AS f 
    INNER JOIN `file_categories` AS fc ON (fc.fkfile = f.id) 
    GROUP BY 
      f.id,
      f.title,
      f.filename,
      f.description,
      f.date_uploaded,
      f.last_updated

You should still upgrade your MySQL version. Somewhere out there, an angel of software security is crying profusely at the prospect of an application being deployed on an ancient database version. Think of the software security angels. Don't make them cry.

---

Old answer:

You should really upgrade from MySQL 5.1. Using such an old version makes you highly vulnerable to security issues, plus you're missing out on the nice features of later versions.

Okay, now to get off my soapbox.

Testing on MySQL 5.6, your query runs successfully returning data; however, as mentioned by Panagiotis Kanavos, you should really be specifying your non-aggregated fields (read as, fields not using aggregate functions like MAX(), AVG(), COUNT(), etc) in a GROUP BY clause.

See this example

    SELECT 
      f.id,
      f.title,
      f.filename,
      f.description,
      f.date_uploaded,
      f.last_updated, 
      GROUP_CONCAT(DISTINCT fc.fkcategory ORDER BY fc.fkcategory ASC ) AS categories_ids 
    FROM `files` AS f 
    INNER JOIN `file_categories` AS fc ON (fc.fkfile = f.id) 
    GROUP BY 
      f.id,
      f.title,
      f.filename,
      f.description,
      f.date_uploaded,
      f.last_updated

Both your version and this version run, though I would strongly recommend this version. Not specifying non-aggregated fields in the group by yields non-deterministic results. You can't guarantee the data that will be returned will be consistent.

I don't have a 5.1 instance available to test against, so I have no way to reproduce your error. I also don't have an explanation for the behavior you're seeing. The best I can offer is to try fixing up your query to use a GROUP BY and see if that fixes things. It wouldn't necessarily make sense that this would fix things, but it's worth a shot.

And here's a SQL Fiddle for kicks: http://sqlfiddle.com/#!9/1eb5fd/3