Multiple Newline(s) Get Removed During Insert

[TZJ]

First of all, great product. SQLyog has helped tremendously with our MySQL development. Thanks for that. However …

Below is the test case for a problem I am running into when inserting text containing multiple consecutive newline characters. This works fine on the mysql> prompt, but not through SQLyog's interface – there, it removes all duplicate newlines, leaving only single line breaks. To reproduce, simply run the following snippet against an existing database. The expected result would be an empty line between “two newline characters:” and “end of story”.

I am running SQLyog Enterprise 5.31, but confirmed the problem in the community edition, too. Any ideas?

[codebox]

drop table if exists test;

create table test (

id int not null auto_increment primary key,

some text not null

) engine = InnoDB;

insert into test (some) values ('

one newline character:

two newline characters:

end of story

');

select *

from test;

drop table test;

[/codebox]

[peterlaursen]

My first thought was that this is basically how a GUI editor is supposed to work!

a SPACE, a TAB or a NEWLINE (or hundreds of them!) are considered totally having the same meaning!

If there is more than one only the first is kept and the rest are stripped away –  SPACES, TABS and NEWLINES are considered a matter of SQL code formatting for the display only.

But well . . you got a point: inside a quoted string it should not do like this!   So I will agree that this is a bug! As we accept one linebreak inside the quoted string we should accept all of them (as we do accept multiple TABS and multiple SPACES inside a quoted string and do not strip any of those)

The workaround is to add an additional 'newline' as a C-style escape sequence “n” like

Code:

drop table if exists test;

create table test (
id int not null auto_increment primary key,
some text not null
) engine = InnoDB;

insert into test (some) values ('

one newline character:
two newline characters:n

end of story

');

select *
from test;

etc.

and of course this

Code:

one newline character:
two newline characters:n

end of story

and this

Code:

one newline character:
two newline characters:n
end of story

would be exactly the same!

But basically I would avoid using visual linebreaks in the quoted string itself but do like:

Code:

insert into test (some)
values ('one newline character:ntwo newline characters:nnend of story');

.. because that would work with any client – whether a graphical client as SQlyog or a commandline or a script/an application! In a similar way I would write “t” instead of a visual TAB.

Actually there could be a platform related issue too.  On Linux 'newline' is plain “n” (or ins ASCII/ANSI terminologi) – on windows it is “rn” ().  Typing/sending the escape sequences instead of relying on the visual display in sosme 'accidential program' is the safe way.  

So it could also be

Code:

insert into test (some)
values ('one newline character:rntwo newline characters:rnrnend of story');

. and this one I think I would actually prefer as not all Windows programs understand the unix/linux way of doing things.  One program that does not is Notepad!  I have not come across any *nix program having problems with display of Windows text!

[Author]

Posts