Bug #60920 NUL byte in GROUP_CONCAT()'s SEPARATOR clausule breaks mysqldump (syntax error)
Submitted: 19 Apr 2011 10:34 Modified: 22 Jan 2013 19:22
Reporter: dexen deVries Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Views Severity:S3 (Non-critical)
Version:5.1.50 OS:Any
Assigned to: CPU Architecture:Any
Tags: Backup, escaping, group_concat, mysqldump, nul byte, restore, SEPARATOR, syntax error, VIEW, zero byte
Triage: Needs Triage: D3 (Medium)

[19 Apr 2011 10:34] dexen deVries
Description:
A view's definition of the following form:

CREATE or replace VIEW foobarXX
AS
SELECT GROUP_CONCAT(123 SEPARATOR "X\0X") AS v;

(where the backslash-0 stands for a zero byte, ASCII character NUL)

is wrongly dumped -- mysqldump stops at the NUL byte. The dump has invalid syntax (only partial SQL statement gets dumped), and thus it's impossible to load back into mysql (if used as backup).

The output from mysqldump is:

/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`dexen`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `foobarXX` AS select group_concat(123 separator 'X */;

-- note view definition cut at the first `X'.

How to repeat:
0) create view with

CREATE or replace VIEW foobarXX
AS
SELECT GROUP_CONCAT(123 SEPARATOR "X\0X") AS v;

1) use `mysqldump' to dump the view

2) note broken view definition. For example, pass to `mysql' (the CLI tool) -- it reports syntax error.
[19 Apr 2011 10:58] dexen deVries
Important note: the NUL byte must be in SEPARATOR clause of the view definition (possibly also some other clauses). Merely having a NUL byte *somewhere* view definition doesn't cause problems. For reference, the dumps & restores  just right, in spite of containing a NUL byte -- but the NUL byte is contained in a string in general select list:

CREATE or replace VIEW frobknob
AS
SELECT "abc\0def" AS v;

dumps correctly as

/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`dexen`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `frobknob` AS select 'abc\0def' AS `v` */;
[19 Apr 2011 11:52] Miguel Solorzano
Thank you for the bug report.
[6 May 2011 13:03] dexen deVries
It seems the problem isn't with just the NUL byte; virtually any special character (like ASCII control characters) that normally should be escaped in a string with backslashes is NOT escaped in the `SEPARATOR' clausule by mysqldump.
For example, backslash-Z (ASCII code 26d) is dumped literally as an byte with value of 26d.
[22 Jan 2013 19:22] Paul Dubois
Noted in 5.7.1 changelog.

When a view definition contained a special character in the SEPARATOR
clause of the GROUP_CONCAT() aggregate function, mysqldump created an
invalid view definition that produced an error when the dump file was
reloaded.