After Confluence copy: java.sql.SQLException: The user specified as a definer does not exist

Written by Claudio Kuenzler - 2 comments

Published on August 17th 2022 - last updated on February 15th 2023 - Listed in Atlassian MySQL MariaDB Databases

---

Although this blog article is based on a MySQL Definer issue appearing on a database used by Atlassian Confluence, the same MySQL error (The user specified as a definer ... does not exist) is irrelevant to the application and also happens on other software using triggers and procedures in the MySQL database.

After an Atlassian Confluence server was copied to a test instance, certain operations, such as managing users, stopped working in the test instance. In the Confluence application logs the following error started to show up:

ERROR [http-nio-8090-exec-17] [engine.jdbc.spi.SqlExceptionHelper]  logExceptions The user specified as a definer ('wikiuser'@'192.168.15.43') does not exist

A closer look at the error shows that the MySQL user (wikiuser) from production is used.

Confluence database settings

The first obvious thought is: Oh, we've overwritten the MySQL connection settings!

When you run a environment clone operation or a basic copy of any application, you obviously need to watch out for database settings. In the case of Atlassian Confluence these database configuration can be found in confluence.cfg.xml which is located in the CONFLUENCE_HOME path (usually /var/atlassian/application-data/confluence).

If you copy the data from another server, you need to make sure to not overwrite this file - or to adjust it again after a copy.

But looking at this file showsthat the correct database credentials (using wikitest MySQL user) are being used:

root@wikitest~# cat /var/atlassian/application-data/confluence/confluence.cfg.xml|grep username
<property name="hibernate.connection.username">wikitest</property>

That means: The database credentials used by Confluence are correct.

Triggers and procedures

What is often forgotten is that an application itself can created (stored) procedures or triggers in the database, too. These database entries are created by a so-called DEFINER, which uses (by default) the user which created the entry. As the database was copied from the production server to the test server, the triggers and procedures were copied as well.

This can be verified easily by running SHOW TRIGGERS inside the Confluence database:

mysql> USE confluence;
mysql> SHOW TRIGGERS;

This shows all the different triggers, including the DEFINER.

Changing the definer (via dump file)

Unfortunately there is no "easy" way (such as an UPDATE query) to change the definer. The triggers and procedures need to be removed and re-created with the new definer.

In order to do this it is best to create a very small dump only containing the triggers and procedures (but no other data) of the application's database:

root@mysql~# mysqldump --triggers --events --routines --add-drop-trigger --no-create-info --no-data --no-create-db --skip-opt confluence > confluence-triggers.sql

In this mini-dump we can now search for the current DEFINER - which is indeed showing the production user (wikiuser) and production server IP (192.168.15.43):

root@mysql~# cat confluence-triggers.sql |grep -i DEFINER
/*!50003 CREATE*/ /*!50017 DEFINER=`wikiuser`@`192.168.15.43`*/ /*!50003 TRIGGER denormalised_content_trigger_on_insert
/*!50003 CREATE*/ /*!50017 DEFINER=`wikiuser`@`192.168.15.43`*/ /*!50003 TRIGGER denormalised_content_trigger_on_update
/*!50003 CREATE*/ /*!50017 DEFINER=`wikiuser`@`192.168.15.43`*/ /*!50003 TRIGGER denormalised_content_trigger_on_delete
/*!50003 CREATE*/ /*!50017 DEFINER=`wikiuser`@`192.168.15.43`*/ /*!50003 TRIGGER denormalised_content_permission_trigger_on_insert
/*!50003 CREATE*/ /*!50017 DEFINER=`wikiuser`@`192.168.15.43`*/ /*!50003 TRIGGER denormalised_content_permission_trigger_on_update
/*!50003 CREATE*/ /*!50017 DEFINER=`wikiuser`@`192.168.15.43`*/ /*!50003 TRIGGER denormalised_content_permission_trigger_on_delete
/*!50003 CREATE*/ /*!50017 DEFINER=`wikiuser`@`192.168.15.43`*/ /*!50003 TRIGGER denormalised_content_perm_set_trigger_on_delete
/*!50003 CREATE*/ /*!50017 DEFINER=`wikiuser`@`192.168.15.43`*/ /*!50003 TRIGGER denormalised_space_permission_trigger_on_insert
/*!50003 CREATE*/ /*!50017 DEFINER=`wikiuser`@`192.168.15.43`*/ /*!50003 TRIGGER denormalised_space_permission_trigger_on_update
/*!50003 CREATE*/ /*!50017 DEFINER=`wikiuser`@`192.168.15.43`*/ /*!50003 TRIGGER denormalised_space_permission_trigger_on_delete
/*!50003 CREATE*/ /*!50017 DEFINER=`wikiuser`@`192.168.15.43`*/ /*!50003 TRIGGER denormalised_space_trigger_on_insert
/*!50003 CREATE*/ /*!50017 DEFINER=`wikiuser`@`192.168.15.43`*/ /*!50003 TRIGGER denormalised_space_trigger_on_update
/*!50003 CREATE*/ /*!50017 DEFINER=`wikiuser`@`192.168.15.43`*/ /*!50003 TRIGGER denormalised_space_trigger_on_delete
CREATE DEFINER=`wikiuser`@`192.168.15.43` PROCEDURE `content_permission_procedure_for_denormalised_permissions`(OUT isServiceDisabled BOOL)
CREATE DEFINER=`wikiuser`@`192.168.15.43` PROCEDURE `content_perm_set_procedure_for_denormalised_permissions`(OUT isServiceDisabled BOOL)
CREATE DEFINER=`wikiuser`@`192.168.15.43` PROCEDURE `content_procedure_for_denormalised_permissions`(OUT isServiceDisabled BOOL)
CREATE DEFINER=`wikiuser`@`192.168.15.43` PROCEDURE `space_permission_procedure_for_denormalised_permissions`(OUT isServiceDisabled BOOL)
CREATE DEFINER=`wikiuser`@`192.168.15.43` PROCEDURE `space_procedure_for_denormalised_permissions`(OUT isServiceDisabled BOOL)

Using sed, we can now replace the definer with the correct MySQL user (wikitest) and IP of the test server (192.168.15.141):

root@mysql~# sed -i "/DEFINER/s/wikiuser/wikitest/g" confluence-triggers.sql
root@mysql~# sed -i "/DEFINER/s/15.43/15.141/g" confluence-triggers.sql

Now let's import that adjusted dump into the confluence database:

root@mysql~# mysql confluence < confluence-triggers.sql
ERROR 1304 (42000) at line 404: PROCEDURE content_permission_procedure_for_denormalised_permissions already exists

Although this has worked for the triggers (thanks to the --add-drop-trigger option used with the mysqldump command), this hasn't worked for the procedures. As there is no equivalent option (there is no --add-drop-procedure option available), these procedures need to be manually removed from the database:

mysql> use confluence;
mysql> DROP PROCEDURE content_permission_procedure_for_denormalised_permissions;
mysql> DROP PROCEDURE content_perm_set_procedure_for_denormalised_permissions;
mysql> DROP PROCEDURE content_procedure_for_denormalised_permissions;
mysql> DROP PROCEDURE space_permission_procedure_for_denormalised_permissions;
mysql> DROP PROCEDURE space_procedure_for_denormalised_permissions;

The confluence-triggers dump can now be imported and shouldn't cause an SQL error anymore:

root@mysql~# mysql confluence < confluence-triggers.sql

After this, Confluence was immediately able to manage users again (without a Confluence restart).

Add a comment

Show form to leave a comment

Comments (newest first)

Eric from wrote on Feb 15th, 2023:

Had this issue with another software after migrating to a new server with MySQL 8. You just saved me and made my day. Many thanks!

---

Mark from Germany wrote on Oct 24th, 2022:

Wow, thanks a lot! I was able to recover my Confluence installation following your descriptions. :)

---