Cannot Delete a Stash Repository due to missing create temporary table permission

Troubleshooting Databases

On this page

Still need help?

The Atlassian Community is here for you.

Ask the community

Symptoms

Delete repository action, in a Stash instance integrated with an external database, fails with a similar exception in the Stash logs:

PostgreSQL

 

2015-02-09 23:38:38,038 WARN  [clusterScheduler_Worker-8]  o.h.h.s.TemporaryTableBulkIdStrategy unable to drop temporary id table after use [ERROR: current transaction is aborted, commands ignored until end of transaction block]
Caused by: org.postgresql.util.PSQLException: ERROR: current transaction is aborted, commands ignored until end of transaction block

MySQL

 

2013-02-06 16:12:32,589 WARN  [http-bio-7990-exec-226] <username> 972x1856x1 1oyb6d9 10.41.5.17,127.0.0.1 "DELETE /projects/SANDBOX/repos/random HTTP/1.1" o.h.e.jdbc.spi.SqlExceptionHelper SQL Error: 1044, SQLState: 42000
2013-02-06 16:12:32,590 ERROR [http-bio-7990-exec-226] <username> 972x1856x1 1oyb6d9 10.41.5.17,127.0.0.1 "DELETE /projects/SANDBOX/repos/random HTTP/1.1" o.h.e.jdbc.spi.SqlExceptionHelper Access denied for user 'stash'@'10.1.3.32' to database 'stash'
2013-02-06 16:12:32,590 WARN  [http-bio-7990-exec-226] <username> 972x1856x1 1oyb6d9 10.41.5.17,127.0.0.1 "DELETE /projects/SANDBOX/repos/random HTTP/1.1" o.h.e.jdbc.spi.SqlExceptionHelper SQL Error: 1146, SQLState: 42S02
2013-02-06 16:12:32,590 ERROR [http-bio-7990-exec-226] <username> 972x1856x1 1oyb6d9 10.41.5.17,127.0.0.1 "DELETE /projects/SANDBOX/repos/random HTTP/1.1" o.h.e.jdbc.spi.SqlExceptionHelper Table 'stash.HT_sta_pr_activity' doesn't exist
2013-02-06 16:12:32,591 WARN  [http-bio-7990-exec-226] <username> 972x1856x1 1oyb6d9 10.41.5.17,127.0.0.1 "DELETE /projects/SANDBOX/repos/random HTTP/1.1" o.h.e.jdbc.spi.SqlExceptionHelper SQL Error: 1051, SQLState: 42S02
2013-02-06 16:12:32,591 ERROR [http-bio-7990-exec-226] <username> 972x1856x1 1oyb6d9 10.41.5.17,127.0.0.1 "DELETE /projects/SANDBOX/repos/random HTTP/1.1" o.h.e.jdbc.spi.SqlExceptionHelper Unknown table 'HT_sta_pr_activity'
2013-02-06 16:12:32,591 WARN  [http-bio-7990-exec-226] <username> 972x1856x1 1oyb6d9 10.41.5.17,127.0.0.1 "DELETE /projects/SANDBOX/repos/random HTTP/1.1" o.h.h.s.TemporaryTableBulkIdStrategy unable to drop temporary id table after use [Unknown table 'HT_sta_pr_activity']
2013-02-06 16:12:32,629 ERROR [http-bio-7990-exec-226] <username> 972x1856x1 1oyb6d9 10.41.5.17,127.0.0.1 "DELETE /projects/SANDBOX/repos/random HTTP/1.1" c.a.e.i.AsynchronousAbleEventDispatcher There was an exception thrown trying to dispatch event 'com.atlassian.stash.event.RepositoryDeletionRequestedEvent[source=com.atlassian.stash.internal.repository.RepositoryStateManagerImpl@71bf6746]' from the invoker 'SingleParameterMethodListenerInvoker{method=public void com.atlassian.stash.internal.pull.PullRequestRescopeListener.onRepositoryDeleteRequested(com.atlassian.stash.event.RepositoryDeletionRequestedEvent), listener=com.atlassian.stash.internal.pull.PullRequestRescopeListener@63b8dba5}'.
java.lang.RuntimeException: A database error has occurred.

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'stash.HT_sta_pr_activity' doesn't exist
        at sun.reflect.GeneratedConstructorAccessor447.newInstance(Unknown Source) ~[na:na]

Cause

The cause for both is the lack of the Temporary Tables permission for the database user; 

PostgreSQL

 

As per this blog, this behaviour is related to temporary tables Hibernate creates (or is supposed to create) for some operations around the inheritance hierarchy for activity tables. That suggests that the user Stash is using to connect to PostgreSQL does not have the right permissions to create temporary tables. Specifically, from PostgreSQL reference:

Temporary Tables

Although the syntax of CREATE TEMPORARY TABLE resembles that of the SQL standard, the effect is not the same. In the standard, temporary tables are defined just once and automatically exist (starting with empty contents) in every session that needs them. PostgreSQL instead requires each session to issue its own CREATE TEMPORARY TABLE command for each temporary table to be used. This allows different sessions to use the same temporary table name for different purposes, whereas the standard's approach constrains all instances of a given temporary table name to have the same table structure.

MySQL

'stash.HT_sta_pr_activity' is not a table created by Stash.

As per this blog, this behaviour is related to temporary tables Hibernate creates (or is supposed to create) for some operations around the inheritance hierarchy for activity tables. That suggests that the user Stash is using to connect to MySQL does not have the right permissions. Specifically, from MySQL's reference:

Temporary Tables

You can use the TEMPORARY keyword when creating a table. A TEMPORARY table is visible only to the current connection, and is dropped automatically when the connection is closed. This means that two different connections can use the same temporary table name without conflicting with each other or with an existing non-TEMPORARY table of the same name. (The existing table is hidden until the temporary table is dropped.) To create temporary tables, you must have the CREATE TEMPORARY TABLES privilege.

Further Troubleshooting

PostgreSQL

To help confirm this, run the following SQL on your PostgreSQL database:

SELECT * FROM has_database_privilege('<Stash_User>', '<Stash_Database>', 'temporary');


If it returns 't':

has_database_privilege 
------------------------
t
(1 row)

it would indicate that the user does have this permission (t=true). If not, you can grant this permission to the user and retry.

MySQL

Use the Show Grants command to confirm the permissions for the Stash user and ensure they have the CREATE TEMPORARY TABLES permission on the Stash database.

Resolution

PostgreSQL

To create temporary tables, you must have the CREATE TEMPORARY TABLES privilege. This can be enabled with the following grant.

GRANT TEMPORARY ON DATABASE <Stash_Database> TO <Stash_User>;

MySQL

To create temporary tables, you must have the CREATE TEMPORARY TABLES privilege. Ensure 'GRANT ALL PRIVILEGES' has been used when creating the Stash database as mentioned in the MySQL setup guide.

In some instances it has been required to explicitly grant the CREATE TEMPORARY TABLES permission to the database user that Stash uses

GRANT CREATE TEMPORARY TABLES <STASHDBNAME>.* '<username>'@'%';

And then restart of Stash

Last modified on Nov 2, 2018

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.