Copy a MySQL Schema

I don’t know much about database and SQL, since I am not a database administrator. This post is probably way to easy for you. Nevertheless, I thought I’ll share the findings from my googling around.

What I am trying to do is simple. I have a schema, and I want to copy it (for backing up, for integration testing, etc etc).

Let us just say the schema name is dwuysan_prod and I would like to create an integration schema dwuysan_integration.

Here is the step-by-step todo:

  • Open a terminal, we will be using command mysql
  • Firstly, connect to mysql

    mysql -u root -p;
    
  • Create database named dwuysan_integration

    CREATE DATABASE dwuysan_integration;
    
  • Grant all privileges

    GRANT ALL PRIVILEGES ON dwuysan_integration.* TO dwuysan_prod@"%";
    
  • Exit the terminal, and let’s use mysqldump

    mysqldump --add-drop-table --complete-insert -uroot -p<your password> dwuysan_prod | mysql -uroot -p<your password> dwuysan_integration;
    

References

Ottuzzi, P 2009, ‘How to Clone a Schema in MySQL’, Brucalipto.org, accessed on 22 October 2015

Advertisements

Netbeans 7.3 connecting to local mysql

I thought I’ll write this one up since I just spent 30 minutes trying to connect to local mysql.

I just downloaded the new Netbeans 7.3 Beta 2, and am now trying to play around with MySql. So, firstly, I simply use the Synaptic Package Manager and install mysql. Once that’s done, it’s time to connect.

I have used Oracle in the past and have managed to connect to it via Netbeans Services > Database (and downloading the ojdbc drivers, connecting, etc etc, they all goes without saying). With MySql however, I notice, there is a dedicated menu Register MySql Server.

Ooooooooooooooohhhhhhhhhhh…… fantastic!!!

Okay …

Then comes the page this this MySQL Server Properties. Hmmm, what values should I put in? …

Well, I spent some time looking up google and also trying various entries. I thought I’ll just share the screenshot here:

mysqlConfig

The problem with JPA Composite primary keys and CascadeType.ALL

Following my previous blog here, I encountered another issue with the use of composite primary keys.

With JPA, we can specify the cascade of relationship to be CascadeType.ALL, so that when we persist the CUSTOMER, all of its PRODUCT are persisted too.

So, the code will be modified as follows:

CUSTOMER

package com.wordpress.dwuysan;

// imports omitted

@Entity
@Table(name = "customer")
public class Customer implements Serializable {
    @Id
    @Column(name = "customer_id")
    private String customerId;

    @OneToMany(
        fetch = FetchType.EAGER,
        mappedBy = "customer",
        cascade = CascadeType.ALL)
    private Set<Product> products = new HashSet<Product>();

    /* the rest of the code omitted */
}

During unit testing however, when executing the following lines:

    this.entityManager.persist(customer); // customer with child products here
    final Query query = this.entityManager.createQuery("FROM Customer cust");
    return query.getResultList(); // error here

And the error is:

javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: Could not execute JDBC batch update
	at org.hibernate.ejb.AbstractEntityManagerImpl.throwPersistenceException(AbstractEntityManagerImpl.java:637)
	at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:74)
	at com.wordpress.dwuysan.CustomerServiceBean(CustomerServiceBean.java:59)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	at java.lang.reflect.Method.invoke(Method.java:597)
	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:309)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:110)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)

SO, for now, I think I will use another ID for the PRODUCT. Would need to look for the underlying reason.

@JoinColumn is part of the composite primary keys

Whilst it is not very common (hmmm … , come to think of it, it may be quite common), there are cases where one would require the use of composite primary keys in JPA, but at the same time, one of the key is actually a foreign key to another parent table.

Let’s just say for example there are table CUSTOMER and PRODUCT (Smith 2007), in this case the PRODUCT uses composite primary key of PRIMARY KEY (customer_id, product_id).

In JPA, they are mapped as follows:

CUSTOMER

package com.wordpress.dwuysan;

// imports omitted

@Entity
@Table(name = "customer")
public class Customer implements Serializable {
    @Id
    @Column(name = "customer_id")
    private String customerId;

    @OneToMany(
        fetch = FetchType.EAGER,
        mappedBy = "customer")
    private Set<Product> products = new HashSet<Product>();

    /* the rest of the code omitted */
}

PRODUCTPK

package com.wordpress.dwuysan;

// imports omitted

@Embeddable
public class ProductPK implements Serializable {
    private String productId;
    private String customerId;

    /* the rest of the code omitted */
}

PRODUCT

package com.wordpress.dwuysan;

// imports omitted

@Entity
@Table(name = "product")
public class Product implements Serializable {

    @AttributeOverrides({
        @AttributeOverride(
            name = "customerId",
            column = @Column(name = "customer_id")),
        @AttributeOverride(
            name = "productId",
            column = @Column(name = "productId"))
    })
    private ProductPK productPK;

    @ManyToOne(fetch = FetchType.EAGER)
    @JoinColumn(name = "customer_id")
    private Customer customer;
}

However, on compiling, I got the following error from JUnit:

Caused by: org.hibernate.MappingException: Repeated column in mapping for entity: com.wordpress.dwuysan.Product column: customer_id (should be mapped with insert="false" update="false")
	at org.hibernate.mapping.PersistentClass.checkColumnDuplication(PersistentClass.java:652)
	at org.hibernate.mapping.PersistentClass.checkPropertyColumnDuplication(PersistentClass.java:674)
	at org.hibernate.mapping.PersistentClass.checkColumnDuplication(PersistentClass.java:696)
	at org.hibernate.mapping.PersistentClass.validate(PersistentClass.java:450)
	at org.hibernate.mapping.RootClass.validate(RootClass.java:192)
	at org.hibernate.cfg.Configuration.validate(Configuration.java:1108)

So, to resolve this, we need to modify the Product class to:

PRODUCT

package com.wordpress.dwuysan;

// imports omitted

@Entity
@Table(name = "product")
public class Product implements Serializable {

    @AttributeOverrides({
        @AttributeOverride(
            name = "customerId",
            column = @Column(name = "customer_id")),
        @AttributeOverride(
            name = "productId",
            column = @Column(name = "productId"))
    })
    private ProductPK productPK;

    @ManyToOne(fetch = FetchType.EAGER)
    @JoinColumn(name = "customer_id", insertable = false, updatable = false)
    private Customer customer;
}

Reference:
Smith, J, 2007, ‘Composite Primary Keys’, accessed 22 February 2012.

Cannot MODIFY CONSTRAINT in Oracle

I just notice that in Oracle, one cannot really MODIFY constraints. Instead, should DROP and ADD.

For example, the following line:

ALTER TABLE work_order MODIFY CONSTRAINT status_check CHECK (business_object IN ('Open', 'Closed', 'Hold'));

results in:

Error report:
SQL Error: ORA-00933: SQL command not properly ended
00933. 00000 -  "SQL command not properly ended"
*Cause:
*Action: