Conversion error from 4.8 to 4.9

steveng · Post by **steveng** » Tue Jun 19, 2007 7:56 am

Dr.Sparks,
First, congratulations on a new forum.

Just wanted to elaborate on my findings as I was going through SQL commands from ClassConvertDatabase.

Initial error was during conversion, for benefit table.
Reason: some entries in the benefit table for ADACode(OldCode) column are blanks/spaces (is it right?) and my procedurecode table doesn't have blank entries for ADACode(OldCode). Therefore query: UPDATE benefit SET benefit.CodeNum= (SELECT procedurecode.CodeNum FROM procedurecode WHERE procedurecode.ProcCode=benefit.OldCode) fails with CodeNum result is Null for Not Null column.

Thanks,
Steve.

Post by **jordansparks** » Tue Jun 19, 2007 9:02 am

Did you ever call us to upload a copy of your database? This is very hard to do without a test database.

steveng · Post by **steveng** » Tue Jun 19, 2007 5:22 pm

just uploaded my DB to mediamax.

Thanks,
Steve

Post by **jordansparks** » Tue Jun 19, 2007 5:32 pm

Thanks.

Post by **jordansparks** » Tue Jun 19, 2007 9:46 pm

Your database converted just fine on my system. I'm going to look into a few more things to try to make it crash, but I just wanted to let you know what was going on in case you were curious.

Post by **jordansparks** » Tue Jun 19, 2007 10:00 pm

Nope. Can't duplicate it. Works just fine. So in a situation like this, we usually just convert it for the customer ourselves. I am also using MySQL 5.0, so my only guess as to what's going on is that you have customized some of your server variables, resulting in MySQL being more likely to give errors on certain types of queries. I've seen that before, but not on this specific query. I don't remember how to reset the server variables to default.

tdong · Post by **tdong** » Wed Jun 20, 2007 6:01 am

Another thing as for Canadian database update in debug mode it replaces all Procedure Buttons with the default one and set ADA2006 as default claim form

Using realease mode to update working just fine

steveng · Post by **steveng** » Wed Jun 20, 2007 7:30 am

Thanks. I'll try to reinstall MySQL on my developer's mashine with same version as on my production server without tweakening it. I think I was playing with it, but it was such a long time ago...
I'll update you on the outcome.

steveng · Post by **steveng** » Fri Jun 22, 2007 1:40 pm

OK. Here is where I'm at:

Reinstalled MySql on developer's machine to the latest 5.0.41 and back to distributed version 5.0.22 -- not working. Extracted SQL from Data Convert class and ran it through Navicat -- same result -- not working.
I thought I messed up my machine completely.... Installed fresh MySql on new machine and ran the script with Navicat to make sure my compiled OD is not in question --- not working.
It is definitely not the compiled OD but MySQL.

IÃ¢â‚¬â„¢m getting same error on all cases:

1263 Column set to default value; NULL supplied to NOT NULL column 'CodeNum' at row 0 for query

UPDATE benefit SET benefit.CodeNum= (SELECT procedurecode.CodeNum FROM procedurecode WHERE procedurecode.ProcCode=benefit.OldCode)

Strangely enough, I can run the join SELECT procedurecode.CodeNum FROM procedurecode, benefit WHERE procedurecode.ProcCode=benefit.OldCode) without any problems.

I do understand that database could be converted for me, but I want to make sure that I wonÃ¢â‚¬â„¢t have any problems later after an upgrade.

Did anyone else see this type of an error?
Dose sql_mode has anything to do with it? Ã¢â‚¬â€œ it was introduced to version 5.
Dr.Sparks, what is your "my.ini" looks like. I think its the source of my problems.

I'm truly puzzled.

Thanks for all the help,
Steven G.

Post by **jordansparks** » Fri Jun 22, 2007 2:54 pm

I can't quite tell if you installed mysql using our installer. If you use our installer and our grant tables, then that's the standard installation. Did you use our grant tables or did you configure it yourself? Anyway, that's my best guess as to the problem. Like I said, it ran fine on my computer.

steveng · Post by **steveng** » Fri Jun 22, 2007 6:46 pm

It worked!!!
I commented out sql_mode parameter in MY.INI file and everything worked like a charm.
Yes, I did install everything from trial version....
I guess as I was going through the wizard questioner of what type of use for My Sql would be it created those new parameters that caused all my troubles.

Anyway, I'm happy it worked and if anyone else experiences it, they would have this reference.

Thanks,
Steve

Post by **jordansparks** » Fri Jun 22, 2007 7:15 pm

I'm glad it worked for you. I didn't quite think it would be in the ini file. That's easy to reinstall using our installer. We recommend using our installer for both the grant tables and the ini file to avoid such issues. I'll keep this in mind for the next time it comes up.