Cannot Delete a Stash Repository due to missing create temporary table permission
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 blockMySQL
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