Hello,

I am running with SQL Anywhere v17 and I am trying to implement a locking scheme to protect a table row over a long (perhaps several hours) period of time. I have two problems.

1) Per the documentation, I try to execute in iSQL:

CREATE MUTEX protect_my_cr_section SCOPE CONNECTION;

but I get:

Syntax error near 'MUTEX'

I assigned to my User the "CREATE ANY OBJECT" System Privilege so I see no reason for why this is happening. Can someone tell me what's wrong here?

2) The v17 doc reference above says:

"You must have the CREATE ANY MUTEX SEMAPHORE or CREATE ANY OBJECT system privilege."

However, while using SQLCentral and looking at the System Privileges list I see "CREATE ANY OBJECT" but "CREATE ANY MUTEX SEMAPHORE" is not in the list. Since I am viewing v17 docs I don't understand why why it says is not in effect for the server. Can someone explain this?

Thank you.

asked 22 Aug, 21:25

AlK's gravatar image

AlK
4651936
accept rate: 50%

edited 23 Aug, 11:05

Graeme%20Perrow's gravatar image

Graeme Perrow
8.7k374111

Are you suuure you're running on a V17 server? What does SELECT @@VERSION display?

(23 Aug, 09:45) Breck Carter

Hi Breck,

Yes, here's what I see:

SELECT @@VERSION 17.0.4.2053

I am starting to wonder if Mutexes are supported in the Developer version of the server! Do you know if this is the case?

Thanks.

(23 Aug, 15:13) AlK
Comment Text Removed
Comment Text Removed

When using the default v17 demo database with the default DBA user,

  • the mentioned SQL statement does work and
  • the mentioned system privilege is granted to the DBA user by default, cf.
select * from sp_displayroles( 'dba', 'expand_down')
where role_name like 'Create any %' or role_name like '%mutex%'
order by 1

So I guess your particular user is missing that role.

As to the required privilege, the docs on "CREATE ANY OBJECT" and "DROP ANY OBJECT" do not mention mutexes and semaphores. I don't know whether that is an oversight or by design.


FWIW, I thought the syntax error might appear if one uses v16 DBISQL with a v17 database engine (say, to use v16's separate message pane) but that does not prevent v17 syntax to be used. (I was unsure whether the DBISQL statement parser would "know" newer syntax, back in the days, I had problems with dbisqlc now and then.)

permanent link

answered 23 Aug, 03:40

Volker%20Barth's gravatar image

Volker Barth
31.3k312458674
accept rate: 32%

edited 23 Aug, 04:41

Hi Volker,

I have tried this with dba and here's what I see:

Could not execute statement. Syntax error near 'MUTEX' SQLCODE=-131, ODBC 3 State="42000" Line 1, column 1

CREATE MUTEX protect_my_cr_section SCOPE CONNECTION

select * from sp_displayroles( 'dba', 'expand_down')
where role_name like 'Create any %' or role_name like '%mutex%'
order by 1;

role_name,parent_role_name,grant_type,role_level
'CREATE ANY INDEX','SYS_AUTH_SA_ROLE','ADMIN',3
'CREATE ANY MATERIALIZED VIEW','SYS_AUTH_SA_ROLE','ADMIN',3
'CREATE ANY OBJECT','SYS_AUTH_SA_ROLE','ADMIN',3
'CREATE ANY PROCEDURE','SYS_AUTH_SA_ROLE','ADMIN',3
'CREATE ANY SEQUENCE','SYS_AUTH_SA_ROLE','ADMIN',3
'CREATE ANY SEQUENCE','SYS_AUTH_RESOURCE_ROLE','NO ADMIN',2
'CREATE ANY TABLE','SYS_AUTH_SA_ROLE','ADMIN',3
'CREATE ANY TEXT CONFIGURATION','SYS_AUTH_SA_ROLE','ADMIN',3
'CREATE ANY TRIGGER','SYS_AUTH_SA_ROLE','ADMIN',3
'CREATE ANY TRIGGER','SYS_AUTH_RESOURCE_ROLE','NO ADMIN',2
'CREATE ANY VIEW','SYS_AUTH_SA_ROLE','ADMIN',3

I am starting to wonder if Mutexes are supported in the Developer version of the server! Do you know if this is the case?

Thanks.

(23 Aug, 15:13) AlK
Replies hidden

Then I would add that system privilege and try again.

(23 Aug, 17:07) Volker Barth

Hi Volker,

It looks like I found the problem and it's rather strange. Since everyone suggested that there should be no problem with this (as long as my user has the rights) I tried this with a different database and had no problem. It then occurred to me that the original database I was testing with is the ultimate "legacy" database. This database was originally created for use with the original Watcom server and has gone through numerous updates and upgrades all the way up to now v17. So the good news for me is that I don't have any sort of fundamental problem but the bad news is that something is wrong with user rights and roles in this database (which is compatible with server v17). Here is what we get running with a "new/fresh/v17" database: select * from sp_displayroles( 'dba', 'expand_down') where role_name like 'Create any %' or role_name like '%mutex%' order by 1;

role_name,parent_role_name,grant_type,role_level 'CREATE ANY INDEX','SYS_AUTH_SA_ROLE','ADMIN',3 'CREATE ANY MATERIALIZED VIEW','SYS_AUTH_SA_ROLE','ADMIN',3 'CREATE ANY MUTEX SEMAPHORE','SYS_AUTH_SA_ROLE','ADMIN',3 'CREATE ANY OBJECT','SYS_AUTH_SA_ROLE','ADMIN',3 'CREATE ANY PROCEDURE','SYS_AUTH_SA_ROLE','ADMIN',3 'CREATE ANY SEQUENCE','SYS_AUTH_SA_ROLE','ADMIN',3 'CREATE ANY SEQUENCE','SYS_AUTH_RESOURCE_ROLE','NO ADMIN',2 'CREATE ANY TABLE','SYS_AUTH_SA_ROLE','ADMIN',3 'CREATE ANY TEXT CONFIGURATION','SYS_AUTH_SA_ROLE','ADMIN',3 'CREATE ANY TRIGGER','SYS_AUTH_SA_ROLE','ADMIN',3 'CREATE ANY TRIGGER','SYS_AUTH_RESOURCE_ROLE','NO ADMIN',2 'CREATE ANY VIEW','SYS_AUTH_SA_ROLE','ADMIN',3 'DROP ANY MUTEX SEMAPHORE','SYS_AUTH_SA_ROLE','ADMIN',3 'UPDATE ANY MUTEX SEMAPHORE','SYS_AUTH_SA_ROLE','ADMIN',3

and here's what we get when we run this against the "legacy" database that has been upgraded numerous times over a couple of decades: role_name,parent_role_name,grant_type,role_level 'CREATE ANY INDEX','SYS_AUTH_SA_ROLE','ADMIN',3 'CREATE ANY MATERIALIZED VIEW','SYS_AUTH_SA_ROLE','ADMIN',3 'CREATE ANY OBJECT','SYS_AUTH_SA_ROLE','ADMIN',3 'CREATE ANY PROCEDURE','SYS_AUTH_SA_ROLE','ADMIN',3 'CREATE ANY SEQUENCE','SYS_AUTH_SA_ROLE','ADMIN',3 'CREATE ANY SEQUENCE','SYS_AUTH_RESOURCE_ROLE','NO ADMIN',2 'CREATE ANY TABLE','SYS_AUTH_SA_ROLE','ADMIN',3 'CREATE ANY TEXT CONFIGURATION','SYS_AUTH_SA_ROLE','ADMIN',3 'CREATE ANY TRIGGER','SYS_AUTH_SA_ROLE','ADMIN',3 'CREATE ANY TRIGGER','SYS_AUTH_RESOURCE_ROLE','NO ADMIN',2 'CREATE ANY VIEW','SYS_AUTH_SA_ROLE','ADMIN',3

Dropping the common rows we are left with the apparent fact that the following are missing in the "legacy" database: 'CREATE ANY MUTEX SEMAPHORE','SYS_AUTH_SA_ROLE','ADMIN',3 'DROP ANY MUTEX SEMAPHORE','SYS_AUTH_SA_ROLE','ADMIN',3 'UPDATE ANY MUTEX SEMAPHORE','SYS_AUTH_SA_ROLE','ADMIN',3

When I say missing I mean that they are not even listed as being available to be assigned to a user (at least in SQL Central). I'd mentioned in my initial question that "CREATE ANY MUTEX SEMAPHORE" was not available to be assigned.)

Since the latest update of this database was done by exporting all data from a (I believe) v15 database and then importing into a fresh v17 database I'm not sure what I could have done differently to avoid this problem. Do you have any idea how I can salvage this database by getting whatever System tables are messed up straightened out? (Vague question but I have no idea what tables are not correct. All I can see at this point is that there are no MUTEX related roles available for assignment.)

Thank you.

(23 Aug, 20:31) AlK
Replies hidden

Strange. If a system orivilege is missing, I'd just reload the database with v17, as the newly inited database should contain all system data.

FWIW, I'd also try with a newer EBF, build 2097 lists a fix w.r.t. reloading and assigned roles. (Note, I do not claim it is related to your problem.)

(24 Aug, 03:19) Volker Barth
1

If it was a privilege issue you would not get "syntax error"...

Could not execute statement.
Permission denied: you do not have permission to use the "CREATE MUTEX"
statement
SQLCODE=-121, ODBC 3 State="42000"
Line 1, column 1

CREATE MUTEX abc SCOPE CONNECTION
(24 Aug, 15:22) Breck Carter

Hi Volker,

Would you please clarify your suggestion. This database that is failing was most recently created by taking a fresh v17 empty database exporting from (I believe) a v15 database and importing into the v17 database. So I have already done that and as I've explained I missing various roles. However, if I read your suggestion literally there is some way to "reload the database with v17". If that's what you meant could you please point me to the documentation for what that means and how to do that or provide me with some guidance on how to do this? I sure need to find some way to salvage his database because it is a demo database (that's part of why it's been around for two decades) and to manually re-create it in a brand-new empty v17 database would take more hours than I want to think about.

Thanks.

(24 Aug, 16:53) AlK
Replies hidden
1

Well, I just meant that, an reload into a fresh v17 database, say via dbunload -an. Something seems strange about your database, and a reload might or might not fix that.

What do the rows with "init" or "upgrade" from system view syshistory tell about your database?

FWIW, there is no version 15 of SQL Anywhere.

(25 Aug, 03:29) Volker Barth

Thank you Volker!

Your suggestion to use dbunload –an did it. I ran out on the "bad" database, generated a new copy connected to the new copy and was able to use CREATE MUTEX with no syntax error.

Thanks very much for helping me salvage his database since manually re-creating it would've taken a huge amount of time.

(28 Aug, 17:28) AlK
showing 4 of 8 show all flat view

The only way I have found to get SQL Anywhere 17 to issue Syntax error near 'MUTEX' on a perfectly good CREATE MUTEX statement is to create the database with SQL Anywhere 16 and then start it with V17.

Rant

IMO easiest way to make a mistake like that is to use Sybase Central for important work like unloading, creating and reloading databases... it has been my experience that even when I do know what I'm doing, when I use Sybase Central I don't know what it's doing :)

/Rant

Here's how to make that mistake using command lines:

"%SQLANY16%\bin64\dbinit.exe"^
  ddd16.db

"%SQLANY17%\bin64\dbspawn.exe"^
  -f "%SQLANY17%\bin64\dbsrv17.exe"^
  -n ddd17^
  -o dbsrv17_log_ddd17.txt^
  -oe dbsrv17_log_fatal_ddd17.txt^
  ddd16.db 

"%SQLANY17%\bin64\dbisql.com"^
  -c "ENG=ddd17;DBN=ddd16;UID=dba;PWD=sql;CON=ddd17-1"

"%SQLANY17%\bin64\dbisql.com"^
  -c "ENG=ddd17;DBN=ddd16;UID=dba;PWD=sql;CON=ddd17-2"

PAUSE

SELECT @@VERSION;
SELECT operation, version, last_time FROM SYSHISTORY;
CREATE MUTEX protect_my_cr_section SCOPE CONNECTION;

@@VERSION
'17.0.7.3399'

operation,version,last_time
'INIT','16.0.0.2344',2017-08-26 09:26:36.0
'LAST_START','17.0.7.3399',2017-08-26 09:26:38.0
'START','17.0.7.3399',2017-08-26 09:26:38.0

Could not execute statement.
Syntax error near 'MUTEX'
SQLCODE=-131, ODBC 3 State="42000"
Line 1, column 1
CREATE MUTEX protect_my_cr_section SCOPE CONNECTION

...so please, do like Volker asks, show us the SYSHISTORY table :)

permanent link

answered 26 Aug, 09:48

Breck%20Carter's gravatar image

Breck Carter
26.9k438609883
accept rate: 21%

Breck, but shouldn't one upgrade the DB file to the higher version if the server version has been increased?

(26 Aug, 09:50) Vlad
Replies hidden
Comment Text Removed

Please understand, I am NOT suggesting that V17 should be used to run the V16 database, just the opposite :)

(26 Aug, 10:02) Breck Carter

Ok-ok, I understood, don't shoot :)

(26 Aug, 10:31) Vlad

Hello all,

Here is the relevant part of SYSHISTORY on the failing database (sorry, the last upgrade was from v16 not v15!) - NOTE: I apologize that my formatting of each entry on a separate line is lost! Does this site not support any form of formatting, plus I see no way to attach a file such as a screenshot of this table! :

operation       version         platform
'INIT'          '16.0.0.1915'   'W7 #7601 SP 1 X86'
'LAST_START'    '17.0.4.2053'   'Linux 4.4.0-92-generic #115-Ubuntu SMP Thu Aug 10 '
'START'         '16.0.0.1915'   'W7 #7601 SP 1 X86'
'START'         '17.0.4.2053'   'W7 #7601 SP 1 X86'
'START'         '17.0.4.2053'   'Linux 4.4.0-66-generic #87-Ubuntu SMP Fri Mar 3 15'
'START'         '17.0.4.2053'   'Linux 4.4.0-72-generic #93-Ubuntu SMP Fri Mar 31 1'
'START'         '17.0.4.2053'   'Linux 4.4.0-75-generic #96-Ubuntu SMP Thu Apr 20 0'
'START'         '17.0.4.2053'   'Linux 4.4.0-78-generic #99-Ubuntu SMP Thu Apr 27 1'
'START'         '17.0.4.2053'   'Linux 4.4.0-79-generic #100-Ubuntu SMP Wed May 17 '
'START'         '17.0.4.2053'   'Linux 4.4.0-81-generic #104-Ubuntu SMP Wed Jun 14 '
'START'         '17.0.4.2053'   'Linux 4.4.0-83-generic #106-Ubuntu SMP Mon Jun 26 '
'START'         '17.0.4.2053'   'Linux 4.4.0-89-generic #112-Ubuntu SMP Mon Jul 31 '
'START'         '17.0.4.2053'   'Linux 4.4.0-92-generic #115-Ubuntu SMP Thu Aug 10 '
(28 Aug, 17:05) AlK
Replies hidden
1

Ok. The init tells us it is a v16 database. So it certainly cannot support v17 features. So the solution is to rebuild the database with v17, as you have done:)

As to formatting, see the according current question.

(29 Aug, 05:41) Volker Barth

wow!!! cool view! I have never used it before!!!

(29 Aug, 07:05) Vlad

"the last upgrade was from v16"

No, it was not. There was no "upgrade from v16", just a bunch of "START" rows showing when the v16 database was started with v17.

( yes, this is a repeat of what Volker said, only blunter ruder :)

(29 Aug, 08:20) Breck Carter

Hi Breck,

Well I now understand that I misunderstood what the table was telling me but let me ask: How does someone find documentation for the contents of such a table?

Thanks.

(29 Aug, 09:24) AlK

Thanks Vlad - that answers a question about several tables (views) I've had over time!

(29 Aug, 14:34) AlK

Well, I'd say the SQL Anywhere docs are generally well crafted and organized IMVHO, so it's usually worthwhile to search within them or use their index...

Of course it does not help that much when one does not expect a certain feature and therefore does not think about consulting the help:)

(29 Aug, 16:17) Volker Barth
1

> when one does not expect a certain feature

Oh, yes, that extends far beyond SQL Anywhere, far beyond programming... whenever I find myself puzzling over One Of Life's Problems, I just ask this guy... no, wait, I ask Google :)

(29 Aug, 17:59) Breck Carter
showing 2 of 12 show all flat view

As to the doc topic on the required privileges for the CREATE MUTEX statement:

"You must have the CREATE ANY MUTEX SEMAPHORE or CREATE ANY OBJECT system privilege."

Jack has commented in DCX that this statement isn't true, the CREATE ANY OBJECT privilege does not apply here, and has additionally explained that those "ANY OBJECT" privileges do not include mutexes and semaphores, as one might have thought...

So another thanks to Jack for the clarification!

permanent link

answered 25 Aug, 16:02

Volker%20Barth's gravatar image

Volker Barth
31.3k312458674
accept rate: 32%

edited 25 Aug, 16:06

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×5

question asked: 22 Aug, 21:25

question was seen: 211 times

last updated: 08 Sep, 07:02