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)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 Gofixes 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 |
It looks like a The 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. 1
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.
(05 Nov '16, 08:59)
Volker Barth
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)
and therefore '\n', '\t' and '\' are not allowed there. For v9 and below, the rules for (quoted) identifiers were less stringent:
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.
(07 Nov '16, 02:19)
Volker Barth
|
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...)
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
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?
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.
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?
@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:)