MySQL Database Recovery

I recently had a key application server die (hardware). One of the key applications running on this machine was a web-application that used a MySql database. We did have some specific MySql backup files, but the most recent backup of the data was a complete server backup of all files.

This meant that I had to work out how to restore a MySql database from the files stored in the data folder (under MySQL Server5.0/data/)

The files I had were:

ib_logfile0
ib_logfile1
ibdata1
and a folder, my_application, which contained a whole bunch of .frm files describing the table structures.

To restore MySql I started by copying these three files and one folder into a newly installed MySql folder on a replacement server. However, on starting mySql as a service, I got:

Could not start the MySQL service on Local Computer.
Error 1067: The process terminated unexpectedly.

I then checked out more information about this error in the file named machineName.err in the MySql/data folder.

This showed that:

InnoDB: Error: log file .\ib_logfile0 is of different size 0 25165824 bytes
InnoDB: than specified in the .cnf file 0 1782 57920 bytes.
080917 12:30:17 [ERROR] Default storage engine (InnoDB) is not available
080917 12:30:17 [ERROR] Aborting.

In this case, I went to my.ini file and changed:

innodb_log_file_size=170M

to

innodb_log_file_size=25165824

I was then able to start the MySql Server and create a normal backup for later retrieval on other servers.

Tomcat - Error starting.

I recently have been installing Tomcat 6 on a new server.

After installing a fresh JRE and then apache-tomcat-6.0.18.exe I got the error:

Windows could not start the Apache Tomcat on Local Computer. For more information, review the System Event Log. If this is a non-Microsoft service, contact the service vendor, and refer to service-specific error code 0.

To fix this I copied the file msvcr71.dll from my Java/bin directory into the Apache-Tomcat/bin directory.

It worked... though why it didn't work "out of the box" without such "black magic" I really don't know!!!!. I spent ages trying a range of different Tomcat installations, JAVA versions and classpath settings.

Aaaargh! Those were precious hours of my life I'm never going to get back...

Upgrading Hibernate Annotations from V3.2 to V3.4.0

I recently blooged about a Hibernate Annotations bug that had been fixed in Hibernate Annotations 3.4.0, but not in 3.2.

While upgrading fixed this problem, I did have some problems with changing to Hibernate Annotations 3.4.

I tried downloading hibernate-annotations-3.4.0.GA.tar.gz and just swapping over the hibernate-annotations.jar file in my project. Seemed like a simple thing to do, however, using the new hibernate-annotations.jar file caused a new error:

Caused by: java.lang.NoClassDefFoundError: org/hibernate/annotations/common/reflection/ReflectionManager

This problem is discussed at this post. It turns out that, in upgrading, I need also to include hibernate-commons-annotations.jar.

Then I got another problem that is due to the lack of the slf4j-api.jar:

Caused by: java.lang.NoClassDefFoundError: org/slf4j/LoggerFactory

After that, I decided I'd better trust the README.txt file that comes with hibernate-annotations-3.4.0 and pulled across a bunch of (I think) required libraries:

dom4j.jar
ejb3-persistence.jar
hibernate-core.jar
slf4j-api.jar
slf4j-log4j12.jar

which fixed one problem... but led to another new error:

Caused by: java.lang.NoSuchFieldError: TRACE

The problem here seems to be that I need log4j.jar to be of version 1.2.12 or higher. (I've got 1.2.11 - missed it by that much!)

Having upgraded to log4j-1.2.15 that fixed that problem - but, a new one was waiting for me:

Caused by: java.lang.NoClassDefFoundError: org/apache/commons/collections/map/LRUMapat org.hibernate.util.SimpleMRUCache.init(SimpleMRUCache.java:71)

I realized (following a bit of reading I probably should have done right at the start!) that I needed to upgrade not just the hibernate-annotations, but also the hibernate core packages themselves to be compatible with the new annotations.

I downloaded the hibernate-distribution-3.3.0.SP1-dist.tar.gz file (something I probably should have done right from the start).

With Hibernate and Hibernate Annotations both updated, everything worked just fine.

The Moral of the Story: Check the dependencies when you upgrade one part of a system and make sure you read the documentation!

Now... I won't make that mistake again (not a guarantee!)

@Transient Annotation and Generics in Hibernate Superclasses

I'm working with Hibernate Annotations, and have a transient field in one of my entity superclasses that is mapped as @MappedSuperclass. That is:

@MappedSuperclass
public abstract class MyEntity<T extends MyEntity<T>> implements Serializable {
@Transient
protected Class<T> domainClass = getDomainClass();

This is throwing the following error:

org.hibernate.AnnotationException: Property my.entity.MyClass.domainClass has an unbound type and no explicit target entity. Resolve this Generic usage issue or set an explicit target attribute (eg @OneToMany(target=) or use an explicit @Type

I found the following Hibernate bug report that seems to refer to the same problem, and indicated it is a bug in Hibernate:

http://opensource.atlassian.com/projects/hibernate/browse/ANN-698?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel

This bug was recently fixed. Seeing as I've been using Hibernate 3.3, I decided to upgrade to Hibernate Annotations 3.4 to fix this.

It worked! Just thought I'd mention this as a fix to that problem, in case others have it.

PS: Sounds easy, huh ?- but I did have some issues with changing to Hibernate Annotations 3.4 which you can read about here.

@Entity annotations

I've just been scratching my head, attempting to figure out why some of my Java classes, marked with the @Entity annotation, weren't being generated (by Hibernate) as tables in my database.

They were correctly included in my hibernate.cfg.xml file with mappings:

<mapping class="mypackage.entity.myclass" />

However, while most of my classes were being correctly generated, some were not.

It turns out that in the classes which were failing, I'd accidently imported org.hibernate.annotations.Entity rather than the correct class (javax.persistence.Entity)

I guess this is a pretty easy trap to fall into - especially if you're using nice editor features (like the myEclipse "organize imports" plugin) and not paying sufficient attention (I did it once in a template and then copied this to a bunch of other locations!). Once done it can be kinda hard to see what isn't right .. Of course, the documentation is clear when it states:

"Note: @javax.persistence.Entity is still mandatory, @org.hibernate.annotations.Entity is not a replacement. "

Oooops...

Hibernate and SQL keyword Column Names

I have recently experienced some problems with Hibernate and the generation of SQL Tables. I am using Hibernate Annotations, and some of my entities have annotations that look something like:

@Column(nullable = false)
String myProperty;

@Column
@Temporal(TemporalType.TIMESTAMP)
@GeneratedValue(strategy = GenerationType.AUTO)
Date timestamp;

@Column
boolean insert;

@Column
boolean update;

@Column
boolean delete;

The problem is that Hibernate is throwing the following errors:

DEBUG [org.hibernate.tool.hbm2ddl.SchemaUpdate] - <create table MyTable (id bigint not null auto_increment, modifyingUsername varchar(255) not null, timestamp datetime, insert bit, update bit, delete bit, primary key (id))>

ERROR [org.hibernate.tool.hbm2ddl.SchemaUpdate] - <Unsuccessful: create table TextMetaDataHistory (id bigint not null auto_increment, modifyingUsername varchar(255) not null, timestamp datetime, insert bit, update bit, delete bit,primary key (id))>

ERROR [org.hibernate.tool.hbm2ddl.SchemaUpdate] - <Syntax error or access violation message from server: "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'insert bit, update bit, delete bit' at line 1">

Upon further investigation, it seems the problem is with the SQL that Hibernate is generating. Unfortunately some of my Java variable names (such as "timestamp", "insert", "update" and "delete") are themselves SQL keywords.

The Debug information above shows that the SQL being generated is not correct SQL syntax. In particular, if you type in MySQL:

create table MyTable (id bigint not null auto_increment, modifyingUsername varchar(255) not null, timestamp datetime, insert bit, update bit, delete bit, primary key (id))

you'll get an error.

If you manually edit this so it, instead, reads:

create table MyTable (id bigint not null auto_increment, modifyingUsername varchar(255) not null, `timestamp` datetime, `insert` bit, `update` bit, `delete` bit, primary key (id))

the SQL will run beautifully. All I've done here is insert quotes (the "`" quote, which is found on a standard keyboard above Tab, and using Shift on this key produces "~").

All I need to do now is work out how to get Hibernate to generate SQL with this included!!!
More news soon... (I hope!)

Update: Ok, so I decided, in the end, to fix this just by manually changing my Hibernate annotations to give an explicit name for each "questionable" column that includes the quotes. Thus the modified annotations are now:

@Column(name="`timestamp`")
@Temporal(TemporalType.TIMESTAMP)
@GeneratedValue(strategy = GenerationType.AUTO)
Date timestamp;

@Column(name="`insert`")
boolean insert;

@Column(name="`update`")
boolean update;

@Column(name="`delete`")
boolean delete;

NB: The "`" quote I'm using is the one above on a US keyboard, sharing the key with "~". Other quotation marks didn't work for me.

This now works - but it does seem unusual that I have to do this. I'm surprised Hibernate doesn't do this automatically for all Column names to avoid such "keyword" problems. Having said that, some "newbie" Hibernate users who have experienced similar problems, have suggested modifying Hibernate to do this, and the response from more experienced Hibernate programmers in at least one case was "Read the manual - stop wasting time" or something to that effect. Makes me wonder if I'm missing something in proposing this solution?

If you know of a better way to fix this problem, please post a comment on this blog! In the meantime, using the @Column(name="`whatever`") annotation is fixing my immediate problem, and doesn't seem to have any bad side effects, so I'll just stick with it!



PS: By the way, to get Hibernate to log the SQL it was using, I set:

log4j.logger.org.hibernate=debug

in the log4j.properties file I'm using..

Incorrect Version of ehcache

Within a web application I'm currently working on, I've just added the Hibernate entity mappings into the hibernate.cfg.xml file. These look something like:

<mapping class="project.entity.MyClass" />

and follow a cache configuration line that says:

<property name="cache.use_second_level_cache">true</property>
<property name="hibernate.cache.provider_class">
net.sf.ehcache.hibernate.SingletonEhCacheProvider
</property>

However, when I launch Tomcat, I get the following errors:


org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'sessionFactory' defined in ServletContext resource [/WEB-INF/bean-defs-6-session.xml]: Invocation of init method failed; nested exception is java.lang.NoSuchMethodError: net.sf.ehcache.CacheManager.getEhcache(Ljava/lang/String;)Lnet/sf/ehcache/Ehcache;
Caused by:
java.lang.NoSuchMethodError: net.sf.ehcache.CacheManager.getEhcache(Ljava/lang/String;)Lnet/sf/ehcache/Ehcache;
at net.sf.ehcache.hibernate.SingletonEhCacheProvider.buildCache(SingletonEhCacheProvider.java:89)
at ...

It turns out that the application is using an old version of ehcache.jar. Further investigation revealed that the reason for this is that the MyEclipse "Hibernate 3.1 Core Libraries" includes a copy of "ehcache-1.1.jar". Even though I had "ehcache-1.3.0.jar" included as a project library (and even changed its order above the Hibernate Core Libraries in the "Order and Export" tab), the application was still defaulting to the ehcache-1.1.jar version.

To fix this, I removed the Hibernate 3.1 Core Libraries from the project libraries. (Subsequently you can add it back in, if you want, as it will now be below the ehcache-1.3.0.jar so the correct version of ehcache will now be used, if you need other bits from the Hibernate 3.1 Core libraries).

Defining DAO Objects

A little while back in an earlier blog post I mentioned a web application I've been developing which uses Hibernate Annotations and Spring MVC.

As mentioned then, the framework I'm using is based on one described in this tutorial.

In the section of this tutorial that discusses defining the DAOs (Database Access Objects) it suggests creating your DAOs using code that looks like:

<bean id="daoTmpl">
<property name="sessionFactory">
<ref bean="sessionFactory"/>
</property>
</bean>

<bean id="myObjectDao" class="org.annotationmvc.dao.MyObjectDaoTmpl" parent="daoTmpl" />

A similar pattern is repeated in a number of other locations on the internet, suggesting using a daoTmpl bean so you don't have to include the sessionFactory object individually in each one of your DAOs.

This is great advice, but in every case you get an error something like:

org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'daoTmpl' defined in ServletContext resource [/WEB-INF/bean-defs-5-dao.xml]: Instantiation of bean failed; nested exception is java.lang.IllegalStateException: No bean class specified on bean definition
Caused by:
java.lang.IllegalStateException: No bean class specified on bean definition

The problem is that you can only define a bean without a class if you declare it as abstract. To do this, change the bean definition to:

<bean id="daoTmpl" abstract="true" >
<property name="sessionFactory">
<ref bean="sessionFactory"/>
</property>
</bean>

and it will work as expected.