Stored Procedure error when using \n

[expired user #6671]'s profile image [expired user #6671] posted 11 years ago in General Permalink
Hi!
I use a variable when creating stored procedures to include a carriage return.
If you put this:
BEGIN
# Versión 1.3.0 - 20130201
SET @message := ''; -- Mensaje
SET @sep := '\n'; -- Separador de Mensajes

SET @message := CONCAT(@message, 'Linea 1', @sep);
SET @message := CONCAT(@message, 'Linea 2', @sep);

# Mensaje de ejecución de CREACION de TABLAS CRUZADAS
CALL common_schema.prettify_message('Ejemplo', common_schema.trim_wspace(@message));

END

You'll get this:
BEGIN
# Versión 1.3.0 - 20130201
SET @message := ''; -- Mensaje
SET @sep := '
';
-- Separador de Mensajes

SET @message := CONCAT(@message, 'Linea 1', @sep);
SET @message := CONCAT(@message, 'Linea 2', @sep);

# Mensaje de ejecución de CREACION de TABLAS CRUZADAS
CALL common_schema.prettify_message('Ejemplo', common_schema.trim_wspace(@message));

END
[expired user #6671]'s profile image [expired user #6671] posted 11 years ago Permalink
I try the SHOW CREATE PROCEDURE in MySQL Workbench and the function is OK!

Also i have problems when using ' on CONCAT, i mean, when using ''

I belive this behaviour didn't occur in previous versions!
ansgar's profile image ansgar posted 11 years ago Permalink
This is a server issue. HeidiSQL takes the procedure code from IS:
SELECT `ROUTINE_DEFINITION` FROM information_schema.`ROUTINES` WHERE `ROUTINE_SCHEMA`='yourdatabase' AND `ROUTINE_NAME`='yourroutinename' AND `ROUTINE_TYPE`='PROCEDURE';


Escaped input gets stored unescaped by MySQL. See
http://bugs.mysql.com/bug.php?id=58342
http://bugs.mysql.com/bug.php?id=62129
ansgar's profile image ansgar posted 11 years ago Permalink
See also issue #3107.
[expired user #6671]'s profile image [expired user #6671] posted 11 years ago Permalink
Thank you for your answer, but I am sure that a few days ago i didn't suffer this problem either with ' neither with \n

I don't know if it's because i'm using MySQL 5.6.10 (previourly i was using MySQL 5.5.28).
ansgar's profile image ansgar posted 11 years ago Permalink
Please read all comments there. Issue #3103 and issue #3104 are also relevant here. I had to move from the SHOW CREATE PROCEDURE approach to the SELECT .. FROM IS.VIEWS approach as it's now, to fix several other bugs in my parser. So, I fixed a few bugs and ran into a server bug unfortunately. I hope the server bug gets fixed at some point, as I don't want to break the other fixes again.
[expired user #6671]'s profile image [expired user #6671] posted 11 years ago Permalink
Thanks again.
I have solved all issues related to ' by usin ' and "

The issue with \n is notr crucial, because i have located in a variable!
[expired user #6671]'s profile image [expired user #6671] posted 11 years ago Permalink
The server bug is also present in MySQL Workbench
ansgar's profile image ansgar posted 11 years ago Permalink
Yes, this should not be serious at all, as the server just makes a formal change on escaped characters. The server does not break any character. Though it's really a bit of a quirky bug I think, should be highly prioritized by MySQL.

Please login to leave a reply, or register at first.