Can not create user -- already exists

[BudDurland]

We are using SQL/Anywhere 16. We started having trouble with Sybase Central crashing when we clicked "users" or "roles". When I looked at the error message that was being generated, it was reporting an error 'user "BugsBunny" already exists in user map. I did a DROP USER BugsBunny, and it executed OK. Now Sybase Central can load the "Users" and "Roles" lists.

I tried to re-create the user "BugsBunny" in Sybase Central, and it fails with a "User already exists" error. I did "select * from sysusers", and BugsBunny is not there.

I'm stumped on where to look next.

[Vlad]

I am curious, what if you unload the DB to CSV files. Would you find "BugsBunny" there?

[Breck_Carter]

Is it possible you have created two or more databases, and from time to time you click on the wrong entry in Sybase Central?

Or, is it possible you are using Sybase Central for some operations, and ISQL for other operations, and they are connected to different databases?

[BudDurland]

That's actually my plan for today. It's a 50+ GB database, so first I'm going to do a "no data" unload and look at the resulting SQL.

[BudDurland]

We do have a copy of this database that we use for testing and training. Because this started as an error in Sybase Central that started happening after a flurry of patches and updates, that was one of the first things we tested -- does it occur in the test database, and it does not.

[BudDurland]

No Joy. loading the the .sql file created by a "no data" unload into an editor shows not traces of BugsBunny. I can unload the data files & see if he appears there, but I suspect that will be pointless, since the data is user tables, not system tables.

[Vlad]

What if you create the user via SQL?

[BudDurland]

Same error with different window dressing:

Could not execute statement.

User "BugsBunny" already exists SQLCODE=-1198, ODBC 3 State="HY000" Line 1, column 1

create user BugsBunny IDENTIFIED by WhatsUpDoc

[VolkerBarth]

When you use the reload script to build a new database with the same schema but without data, can you create that particular user there? Does it matter whether you use CREATE USER or GRANT CONNECT?

[chris_keating]

If I was facing these behaviours, I would tend to start thinking about the possibility of a corruption in the database. Have you run validation on it?

[VolkerBarth]

So a "VALIDATE TABLE sys.isysuser" might show a problem?

[BudDurland · Accepted solution]

Wanted to follow up on this. The problem was resolved by restarting the database service. I worked with Sybase tech support on it. It was probably a corrupt in-memory table or hash of some sort. I failed to capture a memory dump of the running dbSrv process, which would have been useful to tech support.