Upgrade - Database Conversion Problems

opensource · Post by **opensource** » Sun Jul 22, 2007 8:02 am

Hello Team,

I have been upgrading to a newer versions of Opendental myself and each time I upgrade to a newer version, I wrestle with the fact that there is something in the table structure or data in my current version which does not match with what is expected in the newer version.

For Example : CodeNum column recently (version 5.x) added to one of the tables (I think it was the procedurelog or something) expects that column to be Not Null but then it pulls data from some column which has Null data (so the database conversion fails).

I have found a work-around for dealing with such problems but that would mean I have to try the database conversion 'N' number of times, until all such database conversion problems are fixed.

It would be great if we could have a database (data / structure integrity) check script prior to the start of the database conversion and it would throw out a report about everything that is wrong with the current version which would stop a successful conversion. This way, We could bring the database to a state which is acceptable for the upgrade . Still better, if the database integrity check can itself give options of 'Do you want to update the Null' values with '0' or some such options.

Ofcourse, it is a mystery to me on why the database (date / structure integrity) is compromised from one version to another but I think that has a lot to do with some minor data integrity related bugs along the way from one minor version to another. Not sure about this part but that is the only thing that I can think of why the database would be in this condition.

Dr. Sparks / Team, Any thoughts on how to deal with it. More importantly, does anyone else also have similar problems during upgrade ?

Cheers,

OpenSource

Post by **jordansparks** » Sun Jul 22, 2007 1:46 pm

I posted this a few weeks ago, but the post is now lost. Look in your my.ini file. You did a custom installation of MySQL instead of using our installer. Comment out all the options you have added to the my.ini file. Essentially, you've set mysql to throw an error at a higher sensitivity level. I only figured this out about a month ago. And we will certainly be trying to test for this variable and not allow conversion until it is changed. But it would have been easier to just use our installer in the first place to avoid all this. And also, I would be VERY uncomfortable using a database that you had to perform a "workaround" on. There is now even less certainty that the database is not corrupt.

opensource · Post by **opensource** » Sun Jul 22, 2007 9:18 pm

Hello Dr. Jordan,

Thanks for sharing your words of wisdom. Some pieces of the conversion worked like a charm when I updated the my.ini but the NULL related issue continued to error out the database conversion.

I did some debugging and looks like one of the places, it gets stuck is
-------
command="UPDATE benefit SET benefit.CodeNum= (SELECT procedurecode.CodeNum FROM procedurecode WHERE procedurecode.ProcCode=benefit.OldCode)";

General.NonQEx(command);
--------
I created some breakpoints and executed the above SQL seperately (commented the two lines of code) and it works great. so..the database conversion continued and is getting stuck at some other point..which I am yet to debug to.

I do however think the problem lies elsewhere..something is not right when the SQL works fine but gives an exception error when it is execute through ClassConvertDatabase.cs

Dr. Jordan, can u share some more of your wisdom or is this too tech talk.

BTW, I am playing around in my development environment and will not venture into Production environment until I can make the upgrade work without having to touch the database or the opendental code

Cheers,

Opensource
p.s. Based on where this post is leading...I guess it might need to be re-pointed to the Developers Forum

Post by **jordansparks** » Sun Jul 22, 2007 9:48 pm

You're using MySQL 5.0, right? Fix the grant tables using our installer. Honestly. I don't need you to list the queries where it fails. That's irrelevant. The MySQL installation is the problem.

opensource · Post by **opensource** » Mon Jul 23, 2007 8:11 am

Hello Dr. Jordan,

I upgraded my installation with the grant tables and the database conversion/upgrade worked like a charm.

Thanks once again

I do have a follow-up question though, I was not aware that with every upgrade, I need to worry about updating the grant tables (using the installer). Infact, I do not think I have seen the installer being released for every upgraded version.

Is there a pattern that I should be aware of. Like when do I worry about using the installer vs upgrading it directly from the source files. Appreciate some standard guidelines that I can follow.

Cheers,

OpenSource

Post by **jordansparks** » Mon Jul 23, 2007 9:17 pm

You should never have to worry about the grant tables again. Once is all it takes, and you had never done it initially.