Database copies

For complex topics that regular users would not be interested in. For power users and database administrators.
Post Reply
Mifa
Posts: 141
Joined: Wed Nov 21, 2007 6:52 pm
Location: Saint-Bruno, QC, Canada
Contact:

Database copies

Post by Mifa » Wed Mar 18, 2015 9:57 am

We've recently upgraded to 14.3 (we compiled it ourselves) and I've noticed that our main database is now being copied several times every day and saved under <DBname>backup_MM_DD_YYYY_someIndex. After 2 weeks, I have more than 100 copies. Is Is there a way to stop that?

User avatar
dgraffeo
Posts: 147
Joined: Wed Sep 24, 2014 3:19 pm

Re: Database copies

Post by dgraffeo » Wed Mar 18, 2015 10:22 am

Woah that is weird... What sort of backup software do you use, if any? OpenDental doesn't make any automatic updates, you always have to do them manually.
"To understand what recursion is, you must first understand recursion."

David Graffeo
Open Dental Software
http://www.opendental.com

User avatar
jsalmon
Posts: 1551
Joined: Tue Nov 30, 2010 12:33 pm
Contact:

Re: Database copies

Post by jsalmon » Wed Mar 18, 2015 12:41 pm

Sounds like you're getting the "Your database will now be converted..." message every time you launch your compiled version of OD which will automatically make a backup because it thinks there are schema changes that are needed. You might have messed up the assembly info versioning or played around with the convert script?

My best suggestion is to put a break point in MiscData.MakeABackup() and see why / when it is hitting that method.
The best thing about a boolean is even if you are wrong, you are only off by a bit.

Jason Salmon
Open Dental Software
http://www.opendental.com

Mifa
Posts: 141
Joined: Wed Nov 21, 2007 6:52 pm
Location: Saint-Bruno, QC, Canada
Contact:

Re: Database copies

Post by Mifa » Wed Mar 18, 2015 12:56 pm

Weird indeed! It seems to be happening once a day for each computer when we first connect to OD. We get a screen about tables being optimized, it seems that the backup happens then. OD 12.4 we were using in production before this one was also showing this screen but not backing up the DB...

Our Backup software is Cobian, it is scheduled for daily backups on a remote machine, not in the same directory as our production DB...

Mifa
Posts: 141
Joined: Wed Nov 21, 2007 6:52 pm
Location: Saint-Bruno, QC, Canada
Contact:

Re: Database copies

Post by Mifa » Wed Mar 18, 2015 12:59 pm

Jason, we've added 3 extra tables to the DB to handle SMS messages. Do you think that may be the cause?

User avatar
jsalmon
Posts: 1551
Joined: Tue Nov 30, 2010 12:33 pm
Contact:

Re: Database copies

Post by jsalmon » Wed Mar 18, 2015 5:41 pm

That might be. Are they of storage type InnoDB instead of MyISAM by any chance? I vaguely remember something about a mixture of InnoDB tables and MyISAM tables will sometimes try to back up and convert them. I'll have to look again when I get back in the office.
The best thing about a boolean is even if you are wrong, you are only off by a bit.

Jason Salmon
Open Dental Software
http://www.opendental.com

Mifa
Posts: 141
Joined: Wed Nov 21, 2007 6:52 pm
Location: Saint-Bruno, QC, Canada
Contact:

Re: Database copies

Post by Mifa » Thu Mar 19, 2015 5:46 am

The new tables are MyISAM.

From what I can see, it happens at startup (but not all the time!). The method CheckMySQLVersion() gets executed and BackupRepairAndOptimize() as well (so the condition Prefs.UpdateString(PrefName.MySqlVersion,floatVersion.ToString("f1")) must be true. The MySQL version we use is 5.5.14 ("SELECT @@version" returns 5.5.14), we did not upgrade it when we installed 14.3. Is that the problem?

The message we receive is "Tables will now be backed up, optimized, and repaired. This will take a minute or two. Continue?"

It is very similar to the message we were receiving with 12.4, except that this one did not mention tables backup. I don't have the source code for that version, but I suspect we had the same problem then, except that maybe automatic backup was not implemented?

Mifa
Posts: 141
Joined: Wed Nov 21, 2007 6:52 pm
Location: Saint-Bruno, QC, Canada
Contact:

Re: Database copies

Post by Mifa » Thu Mar 19, 2015 6:20 am

I've checked the preference table for the MySQL version and I've found a discrepancy between the production database and the one that was backed up automatically. One reads 5.5 (production) the other 5,5 (backup). Our computers input locale (regional settings) are set to canada french (customized to use "." as decimal separator (instead of the standard french ","). I know that our users are been requested from time to time by XDR support to temporarily switch back to english us in order to attach floating x-rays back to a patient. When switching back to canada french, at least one of our computers may still be using "," as decimal separator causing the version to be altered. Is that a possibility?

Mifa
Posts: 141
Joined: Wed Nov 21, 2007 6:52 pm
Location: Saint-Bruno, QC, Canada
Contact:

Re: Database copies

Post by Mifa » Thu Mar 19, 2015 7:00 am

I see that the version number is converted to a float in the code (prefL.cs - floatVersion=PIn.Float(thisVersion.Substring(0,3)) then back to string. I guess this is where we have the problem.

User avatar
jsalmon
Posts: 1551
Joined: Tue Nov 30, 2010 12:33 pm
Contact:

Re: Database copies

Post by jsalmon » Thu Mar 19, 2015 9:05 am

Mifa wrote:I see that the version number is converted to a float in the code (prefL.cs - floatVersion=PIn.Float(thisVersion.Substring(0,3)) then back to string. I guess this is where we have the problem.
That's verifying your MySQL version. What are the results of the following query for you?

Code: Select all

SHOW VARIABLES LIKE "%version%";
The best thing about a boolean is even if you are wrong, you are only off by a bit.

Jason Salmon
Open Dental Software
http://www.opendental.com

Mifa
Posts: 141
Joined: Wed Nov 21, 2007 6:52 pm
Location: Saint-Bruno, QC, Canada
Contact:

Re: Database copies

Post by Mifa » Thu Mar 19, 2015 12:40 pm

The query returns:

innodb_version 1.1.8
protocol_version 10
slave_type_conversions
version 5.5.14
version_comment MySQL Community Server (GPL)
version_compile_machine x86
version_compile_os Win32

User avatar
jsalmon
Posts: 1551
Joined: Tue Nov 30, 2010 12:33 pm
Contact:

Re: Database copies

Post by jsalmon » Thu Mar 19, 2015 12:56 pm

That's good. That's the versions that we are expecting within Open Dental. Now comes the question as to how it's stored within the preference table. Hopefully there is a visible character or anomaly getting saved into the database so that we can find the culprit. Lots of times it has to do with language / region settings treating periods as commas and such.
Please make sure that the following query returns '5.5' and not something like '5,5' or even ' 5.5' (space or some invisible char in the beginning of the cell).

Code: Select all

SELECT ValueString FROM preference WHERE PrefName='MySqlVersion';
Also, you could put a breakpoint within that method and see what floatVersion looks like after reading it in from the database (which should be 5.5). I don't like the idea of reading it in as a float so we'll probably change that code but I'd like to make sure we fix your issue as well so that we can throw one stone to kill two birds.
The best thing about a boolean is even if you are wrong, you are only off by a bit.

Jason Salmon
Open Dental Software
http://www.opendental.com

Mifa
Posts: 141
Joined: Wed Nov 21, 2007 6:52 pm
Location: Saint-Bruno, QC, Canada
Contact:

Re: Database copies

Post by Mifa » Thu Mar 19, 2015 1:08 pm

I've checked the preference table this morning and mentioned that 5,5 was stored in the backed-up DB instead of 5.5 (see earlier post).

Your query returns 5,5 in the backups done today (5 of them so far) and 5.5 in production so far, but it will change as soon as someones connects on the computer(s) with different input locale settings.

I agree with you regarding the float conversion especially since the result is compared to 5.0f afterwards...

User avatar
jsalmon
Posts: 1551
Joined: Tue Nov 30, 2010 12:33 pm
Contact:

Re: Database copies

Post by jsalmon » Thu Mar 19, 2015 2:46 pm

Mifa wrote:I've checked the preference table this morning and mentioned that 5,5 was stored in the backed-up DB instead of 5.5 (see earlier post).

Your query returns 5,5 in the backups done today (5 of them so far) and 5.5 in production so far, but it will change as soon as someones connects on the computer(s) with different input locale settings.

I agree with you regarding the float conversion especially since the result is compared to 5.0f afterwards...
Oh gosh, I totally skipped over that post. I'll add this to the bug tracker right now.
http://opendentalsoft.com:1942/ODBugTra ... sions.aspx
The best thing about a boolean is even if you are wrong, you are only off by a bit.

Jason Salmon
Open Dental Software
http://www.opendental.com

Mifa
Posts: 141
Joined: Wed Nov 21, 2007 6:52 pm
Location: Saint-Bruno, QC, Canada
Contact:

Re: Database copies

Post by Mifa » Fri Mar 20, 2015 6:27 am

Thanks.

As mentioned yesterday morning, I believe the problem is when you update the preference table in Prefs.UpdateString(PrefName.MySqlVersion,floatVersion.ToString("f1"). I think, the conversion floatVersion.ToString("f1") may have a culture specific result. (see https://msdn.microsoft.com/en-us/librar ... 10%29.aspx)

User avatar
jsalmon
Posts: 1551
Joined: Tue Nov 30, 2010 12:33 pm
Contact:

Re: Database copies

Post by jsalmon » Fri Mar 20, 2015 10:12 am

Bug "MySQL version was stored incorrectly sometimes due to computer locales. This was causing automatic backups to be made every time Open Dental was launched" fixed and will be released with v14.3.37 and v15.1.16
http://opendentalsoft.com:1942/ODBugTra ... sions.aspx
The best thing about a boolean is even if you are wrong, you are only off by a bit.

Jason Salmon
Open Dental Software
http://www.opendental.com

Post Reply