User names beginning with N cause re-load problems

[justin_willey]

v 16.0.0.2213

If a user name starts with an N we get an error when trying to reload the database structure. Using default choices throughout, the reload.sql file contains lines like:

GRANT INTEGRATED LOGIN TO "group\\nigel.farage" AS USER "nigel.farage"
go
COMMENT ON INTEGRATED LOGIN "group\\nigel.farage" IS NULL
Go

this generates errors:

Could not execute statement.
Syntax error near 'group
igel.farage' on line 1
SQLCODE=-131, ODBC 3 State="42000"
Line 142228, column 1
(Continuing after error)
Could not execute statement.
Syntax error near 'group
igel.farage' on line 1
SQLCODE=-131, ODBC 3 State="42000"
Line 142231, column 1
(Continuing after error)

because the \\n is being treated as a new line character. Manually editing the reload.sql file:

GRANT INTEGRATED LOGIN TO "group\\\\nigel.farage" AS USER "nigel.farage"
go
COMMENT ON INTEGRATED LOGIN "group\\\\nigel.farage" IS NULL
Go

fixes the problem.

I encountered the problem when unloading a v11 file to upgrade, but the problem persists when unloading v16 format databases as well. Problem is also present in build 2344 - see below.

---

Reported to SAP as incident ID:002075129400004752422016

---

Fixed by Engineering Case 804480 SAP SQL Anywhere Version 16.0.0 build 2417 SAP SQL Anywhere Version 17.0 build 2795

[VolkerBarth]

Hm, what is the difference between the generated SQL file and your manual correction?

(I would expect that group members's names starting with t might also lead to problems...)

[justin_willey]

Help - we are being escaped again! You should see:

GRANT INTEGRATED LOGIN TO "group\\\\nigel.farage" AS USER "nigel.farage"

with double \\ to make it work

[Vlad]

Justin, I see that you are using the DB server that is ~10 month old. What does the version "SQL Anywhere 16.0 SP65 Build 2344" generate?

[justin_willey]

Hi Vlad - there's nothing in the release notes for 2344 that applies, but I tried it anyway (unloading and reloading in 2344) and the behaviour is just the same. The \\n isn't escaped in the reload.sql file, so causes an error when it's run in the reload.

[VolkerBarth]

Reported to SAP as incident ID:002075129400004752422016

Hm, I would have expected that anybody of the helpful SQL Anywhere engineers would have replied here, as well... - or am I too spoilt with their reactions on other FAQs?

[VolkerBarth]

@Justin: Personally, I think it's easier to follow this FAQ when information on CRs/fixed issues is posted as an answer instead of an updated question... - just my 2 cents, anyway:)

[Breck_Carter · Accepted solution]

It looks like a bug behavior change to me, since "group\\nigel.farage" is not a string literal, it is a quoted identifier.

The bug behavior change was introduced in Version 10.

CREATE TABLE t ( "group\\nigel.farage" INTEGER );
INSERT t VALUES ( 1 );
COMMIT;
SELECT @@VERSION, * FROM t;

-- SQL Anywhere 9
@@VERSION,group
igel.farage
'9.0.2.3951',1

-- SQL Anywhere 10
Could not execute statement.
Syntax error near 'group
igel.farage' on line 1
SQLCODE=-131, ODBC 3 State="42000"
Line 1, column 1
CREATE TABLE t ( "group\\nigel.farage" INTEGER );

I wish there was a global server option that would completely turn off the stupid arcane unnecessary special character processing in string literals. Nobody needs \\n to mean new line, but they ABSOLUTELY POSITIVELY FREQUENTLY DO NEED \\ CHARACTERS in string literals.

[VolkerBarth · Accepted solution]

Nobody needs \\n to mean new line

Hm, I do often need new lines in strings - and probably most folks with a C-based background at least are aware of "\\n" as a platform-independent notation for that.

However, an option would be handy, I agree.

[VolkerBarth · Accepted solution]

Breck, the noticed behaviour change between v9 and v10 seems to have to do with the fact that for v10 and above, identifiers (even quoted ones) are not allowed to contain (among others)

Control characters (characters with an ordinal value of less than 32, or the character value 127).. Backslashes

and therefore '\\n', '\\t' and '\\' are not allowed there.

---

For v9 and below, the rules for (quoted) identifiers were less stringent:

Identifiers need to be enclosed in double quotes or square brackets if any of the following conditions are true:
[...]
The identifier contains characters other than alphabetic characters and digits.

---

However, I'n mot sure whether this does apply to Justin's case:

The user profile name used for integrated Windoes login names requires the '\\' to separate domain and user name. Nevertheless, the docs leave open whether the <user-profile-name> is an identifier or not... - But apparently it is a bug if you can create such login names but they get messed up during a reload.