[Mifos-developer] Database upgrade error

[Aliya Walji]

Hi,

I tried updating the test server to rev 11884 (Version 132) from rev
11823 (Version 122) and I encountered the following database upgrade
error. Has anyone else encountered this error on their local
installation? Unfortunately, I cannot try reproducing on my machine
because I have a Grameen Koota DB loaded for testing purposes at the
moment.

Thanks for your help,

Aliya

Database upgrade error:

Database Version = 124
Application Version = 132

Here are details of what went wrong:

java.sql.SQLException: Error on rename of '.\mifos\#sql-1270_17ae' to
'.\mifos\survey_instance' (errno: 150)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2975)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1600)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1695)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2998)
at com.mysql.jdbc.Statement.executeUpdate(Statement.java:1071)
at com.mysql.jdbc.Statement.executeUpdate(Statement.java:1005)
at
org.mifos.framework.persistence.SqlUpgrade.execute(SqlUpgrade.java:48)
at
org.mifos.framework.persistence.SqlUpgrade.upgrade(SqlUpgrade.java:32)
at
org.mifos.framework.persistence.DatabaseVersionPersistence.upgradeDataba
se(DatabaseVersionPersistence.java:214)
at
org.mifos.framework.persistence.DatabaseVersionPersistence.upgradeDataba
se(DatabaseVersionPersistence.java:191)
at
org.mifos.framework.ApplicationInitializer.syncDatabaseVersion(Applicati
onInitializer.java:231)
at
org.mifos.framework.ApplicationInitializer.init(ApplicationInitializer.j
ava:72)
at
org.mifos.framework.ApplicationInitializer.contextInitialized(Applicatio
nInitializer.java:61)
at
org.mortbay.jetty.handler.ContextHandler.startContext(ContextHandler.jav
a:530)
at
org.mortbay.jetty.servlet.Context.startContext(Context.java:135)
at
org.mortbay.jetty.webapp.WebAppContext.startContext(WebAppContext.java:1
218)
at
org.mortbay.jetty.handler.ContextHandler.doStart(ContextHandler.java:500
)
at
org.mortbay.jetty.webapp.WebAppContext.doStart(WebAppContext.java:448)
at
org.mortbay.component.AbstractLifeCycle.start(AbstractLifeCycle.java:40)
at
org.mortbay.jetty.handler.HandlerCollection.doStart(HandlerCollection.ja
va:147)
at
org.mortbay.jetty.handler.ContextHandlerCollection.doStart(ContextHandle
rCollection.java:161)
at
org.mortbay.component.AbstractLifeCycle.start(AbstractLifeCycle.java:40)
at
org.mortbay.jetty.handler.HandlerCollection.doStart(HandlerCollection.ja
va:147)
at
org.mortbay.component.AbstractLifeCycle.start(AbstractLifeCycle.java:40)
at
org.mortbay.jetty.handler.HandlerWrapper.doStart(HandlerWrapper.java:117
)
at org.mortbay.jetty.Server.doStart(Server.java:210)
at
org.mortbay.component.AbstractLifeCycle.start(AbstractLifeCycle.java:40)
at
org.mortbay.xml.XmlConfiguration.main(XmlConfiguration.java:929)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown
Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.mortbay.start.Main.invokeMain(Main.java:183)
at org.mortbay.start.Main.start(Main.java:497)
at org.mortbay.start.Main.main(Main.java:115)

-------------------------------------------------------------------------
This SF.net email is sponsored by DB2 Express
Download DB2 Express C - the FREE version of DB2 express and take
control of your XML. No limits. Just data. Click to get it now.
http://sourceforge.net/powerbar/db2/

[Van Mittal-Henkle]

Hi Aliya,

Yes, I have also encountered this error and was just discussing this with Jim
the other day. I also checked with Kimloan Ho and she has also seen it
(once).

--Van

[Aliya Walji]

Thanks Van. So I'm assuming there's no resolution yet?

[Van Mittal-Henkle]

Correct. When Jim and I last spoke, the cause of the error was not determined.

[Jim Kingdon]

> Yes, I have also encountered this error and was just discussing this
with Jim
> the other day. I also checked with Kimloan Ho and she has also seen
it
> (once).

I have developed a way to more easily reproduce this problem.

See the new class Upgrader which right now is a main method
which drops the database tables and runs all the upgrade
scripts in order. When I run it I get a MySQL error with
errno 150 (although although I don't know for sure it is
the same error that others have seen, that seems like the
way to guess).

[Jim Kingdon]

> > I tried updating the test server to rev 11884 (Version 132) from rev
> > 11823 (Version 122) and I encountered the following database upgrade
> > error.

The problem was in the upgrade to 125. I've checked in
a fix (as of 11893).

The test server may be left with a partly-upgraded database;
I don't know if you want to do surgery on it or whether you
have a copy of the version 122 database or what.

I've also updated the tests so that this problem (having to
do with renaming a column with a foreign key) will be detected
by the tests in the future.

[Aliya Walji]

Thanks Jim,

After I tried updating the test server last time, I ran downgrade scripts to downgrade from 124 to 122.  Is it possible that part of the upgrade script to 125 was executed and was not cleaned up?  If that's not a possibility, then the test server should be fine (it seems to be functionally working ok).  If this is a possibility, I have a backup I took before I tried to update the test server and I can just load that.

Let me know if I need to be checking for any vestiges of the attempt to upgrade to 125 on the test server.

Aliya

[Jim Kingdon]

> Is it possible that part of the upgrade script to 125 was
> executed and was not cleaned up?

I think so, although it would appear that the result would
be harmless (the new upgrade to 125 will clean things up).
This would not always be the case, but here is seems like
it would be.

> If this is a possibility, I have a backup I took before
> I tried to update the test server and I can just load that.

If you seem to be running into trouble, this might be
worth a try.