MySQL Bugs: #60920: NUL byte in GROUP_CONCAT()'s SEPARATOR clausule breaks mysqldump (syntax error)

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

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.

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` */;

Thank you for the bug report.

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.

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.