2

I'm developing a multi-tenant web app with "Shared Database/Separate Schemas" approach using java, jpa(eclipselink), mysql. My persistence file looks like:

    <persistence-unit name="GroupBuilderPU" transaction-type="RESOURCE_LOCAL">
    <provider>org.eclipse.persistence.jpa.PersistenceProvider</provider>
            <exclude-unlisted-classes>false</exclude-unlisted-classes>
            <properties>
                <property name="eclipselink.cache.shared.default" value="false"/>
                <property name="javax.persistence.jdbc.url" value="jdbc:mysql://localhost:3306/?"/>
                <property name="eclipselink.ddl-generation" value="create-or-extend-tables"/>
<--- Here goes other properties definition -->
        </persistence-unit>

Now here is my EntityMangerFactory and EntityManager:

emfForTenant = Persistence.createEntityManagerFactory("GroupBuilderPU");
EntityManager em = emfForTenant.createEntityManager();
        em.setProperty("eclipselink.tenant-id", schemaNameAsTenantId);

Its working fine untill I'm adding any new persistence column in any entity.

Like I've a Entity UserAccount where I've added a new column 'String rentalinfo' :

@Entity
@Multitenant(MultitenantType.TABLE_PER_TENANT)
@TenantTableDiscriminator(type = TenantTableDiscriminatorType.SCHEMA, contextProperty = PersistenceUnitProperties.MULTITENANT_PROPERTY_DEFAULT)
public class UserAccount implements Serializable {
...
private String rentalinfo;//Newly added column
...
}

Now after that this the following line is giving error:

em.createQuery("SELECT ua FROM UserAccount ua").getResultList();

The error is:

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'RENTALINFO' in 'field list'

So what will be the solution for adding new column (extend table) in this approach?

Farhan Nazmul
  • 51
  • 9

1 Answers1

1

You are getting this exception because the 'RENTALINFO' column does not exist on your UserAccount table. Under normal circumstances, setting "create-or-extend-tables" will have EclipseLink issue an ALTER to your existing table, adding the new column. However, it would appear ddl generation is not supported for MultitenantType.TABLE_PER_TENANT: https://wiki.eclipse.org/EclipseLink/DesignDocs/Multi-Tenancy/TablePerTenant

Not supported:

  1. Schema generation will not be supported since it requires knowledge of all the tenants (schema's) and further to that, access provision must be set once the tables are created if using schema level table per tenant.

So there is no ALTER and your table does not have the column.

As a side note, you can turn on EclipseLink SQL logging using the following persistence properties:

<properties>
    <property name="eclipselink.logging.level" value="ALL"/>
    <property name="eclipselink.logging.level.sql" value="FINE"/>
    <property name="eclipselink.logging.parameters" value="true"/>
</properties>

This way, you can see what queries EclipseLink is (or in this case, isn't) executing.

Community
  • 1
  • 1
Will Dazey
  • 253
  • 2
  • 13
  • Yeap, I know that ddl generation is not supported for MultitenantType.Table_PER_TENANT. Btw the solution is to use any db migration tool like flyway, liquibase etc. I'm using flyway for the solution :) – Farhan Nazmul Feb 22 '17 at 11:32
  • @FarhanNazmul ah, I had assumed you were looking for a solution that involved EclipseLink extending the tables for you... then yes, the solution would be to just extend the table yourself or using a tool... glad you got it worked out. – Will Dazey Feb 22 '17 at 15:17