KNOWLEDGE BASE

When importing Tableau Online site to on-premises Tableau Server "insert or update on table "users" violates foreign key constraint"


Published: 19 Apr 2018
Last Modified Date: 21 Apr 2018

Issue

When importing Tableau Online site to on-premises Tableau Server  "insert or update on table "users" violates foreign key constraint"

Messages similar to the following will be logged in the tabadmin.log

2018-04-09 10:08:02.168 -0700_FATAL_<IP_Address>:<HOST_NAME>_:_pid=5332_0x1b7a52dd__user=__request=__ org.springframework.orm.jpa.JpaSystemException: org.hibernate.TransactionException: commit failed; nested exception is javax.persistence.PersistenceException: org.hibernate.TransactionException: commit failed
2018-04-09 10:08:02.168 -0700_DEBUG_<IP_Address>:<HOST_NAME>_:_pid=5332_0x1b7a52dd__user=__request=__ NativeException: org.springframework.orm.jpa.JpaSystemException: org.hibernate.TransactionException: commit failed; nested exception is javax.persistence.PersistenceException: org.hibernate.TransactionException: commit failed
 

A message similar to the following will be logged in the tabsvc - tabadmin -java.log file

Caused by: org.postgresql.util.PSQLException: ERROR: insert or update on table "users" violates foreign key constraint "users_system_user_id_fkey"
Detail: Key (system_user_id)=(-39840) is not present in table "system_users".

Environment

  • Tableau Server 
  • Active Directory Authentication
  • Windows Server

Resolution

Enable access to the Tableau Server repository on the target instance and connect to the Tableau Server (PostgreSQL ) Repository using Tableau Desktop. For more information see Collect Data with the Tableau Server Repository.
      
Once connected to the Tableau Server Repository, follow these steps:
  1. Drag the "system_users" table into the data pane.
  2. Click the "add" button to add an additional data source.
  3. Add a connection to the User Mapping file from the source Tableau Online site.
  4. Create a left join between "Name" (in Postgres) to "Target Name" in the mapping file
  5. Click on "Sheet 1" and drag "Name" and "Target Name" into Rows
  6. Look for "Null" values in the Left Column (Name)
  7. These users do not exist in Active Directory

1. If possible, map the extra Tableau Online users to existing active directory accounts or create temporary Active Directory accounts for mapping purposes.
  • After the import, the content can be reassigned and the accounts removed.
2. Remove the extra users from the Tableau Online site and request an additional site export.

Cause

Users exist in Tableau Online source site, that do not exist in the Target Site's Active Directory, causing the  "insert or update on table "users" violates foreign key constraint".

Additional Information

If it is possible to map to existing users, this will most likely take less time, than requesting another site import from the Customer Solutions department. 
Did this article resolve the issue?