CODEWITHSUNDEEP
mysqlchr
Marco
Marco

Reputation: 980

Mysqldump breaks output when using CHAR(0), "\0" or 0x0

I have a problem with this little piece of a MySql query:

SELECT

`p`.`person_id`,
`p`.`prename`,
`p`.`name`,
(
    SELECT GROUP_CONCAT(`node`.`name` SEPARATOR "\0")

    FROM `node`

    INNER JOIN `nodegroup`
    ON `nodegroup`.`nodegroup_id` = `node`.`fk_nodegroup_id`

    INNER JOIN `person_has_node`
    ON `person_has_node`.`fk_node_id` = `node`.`node_id`

    WHERE `person_has_node`.`fk_person_id` = `p`.`person_id`

    GROUP BY `nodegroup`.`nodegroup_id`

) AS `nodes`

FROM `person` `p`

This query is used as a view inside of my application. It is programmatically created by the application in a kind of a update process. The result is just as expected and I can use the view without any problems.

The problem begins when it comes to the subselect that generates a CHAR(0) seperated field.

When I want to export the complete database incl. this view, the output is broken on exactly that part where this view should have been dumped. The complete view definition string ends right after the keyword SEPARATOR. The complete rest is missing. As a result the dumpfile is incomplete, therefore broken and can not be imported again.

I tried different approaches to handle the null char:

GROUP_CONCAT(`node`.`name` SEPARATOR "\0")
GROUP_CONCAT(`node`.`name` SEPARATOR CHAR(0))
GROUP_CONCAT(`node`.`name` SEPARATOR 0x0)

The result is always the same:

/*!50001 DROP TABLE IF EXISTS `person_nodes`*/;
/*!50001 DROP VIEW IF EXISTS `person_nodes`*/;
/*!50001 SET @saved_cs_client          = @@character_set_client */;
/*!50001 SET @saved_cs_results         = @@character_set_results */;
/*!50001 SET @saved_col_connection     = @@collation_connection */;
/*!50001 SET character_set_client      = utf8 */;
/*!50001 SET character_set_results     = utf8 */;
/*!50001 SET collation_connection      = utf8_general_ci */;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `person_nodes` AS select `p`.`person_id` AS `person_id`,`p`.`prename` AS `prename`,`p`.`name` AS `name`,(select group_concat(`node`.`name` separator ' */;
/*!50001 SET character_set_client      = @saved_cs_client */;
/*!50001 SET character_set_results     = @saved_cs_results */;
/*!50001 SET collation_connection      = @saved_col_connection */;

Right now I dont have a clue how to solve this. I need to be able to dump that database via mysqldump and import elsewhere via mysql as this is part of kind of a deploy process.

Currently I am using mysql Ver 14.14 Distrib 5.5.50, for debian-linux-gnu (x86_64) using readline 6.3

I am creating the dumpfile that way:

mysqldump -u <user> -p<pass> <dbname> -r <dumpfile>

or

mysqldump -u <user> -p<pass> <dbname> > <dumpfile>

Does anyone have an idea how I can prevent breaking the mysqldump output? Maybe this is a char-set/conversion problem? By default I am using utf8/utf8_general_ci.

Info: Tools like MysqlWorkbench or Navicat are not able to edit the view. The editable result string is also broken in the same way.

Upvotes: 2

Views: 773

Answers (1)

Marco
Marco

Reputation: 980

Meanwhile I guess I found the problem (not a solution). This a real mysql bug that exists since 2011.

See https://bugs.mysql.com/bug.php?id=60920

The bad thing is (if I understand the comments right), that it has been fixed in Mysql version 5.7.1. This means I have to rewrite some code and use some non-whitespace separator to get that thing to work in mysql versions < 5.7.1.

Upvotes: 1

Related Questions