MySQL InnoDB

MySQL and MariaDB can store data using a handful of different storage engines. Almost all installations of Open Dental use the MyISAM storage engine. For large offices, MyISAM tables lock up for short periods of time when complex queries are being run (e.g. running the recall list). For example, you may notice that Open Dental is usually fast, but when you perform certain specific actions or run certain tools Open Dental locks up for a short period of time on all computers.

If you are experiencing such slowness:

  1. Update to the most recent version of Open Dental to ensure a bug isn't causing slowness.
  2. Follow the steps on Troubleshooting Slowness.
  3. Bring the slowness issue to the attention of Open Dental support in case there is a bug that needs to be fixed.
  4. As a last resort, consider switching the storage engine from MyISAM to InnoDB. The MyISAM storage engine locks at the table level and the InnoDB storage engine locks at the row level, so InnoDB does not run into locking slowness as often as MyISAM does.

Convert to InnoDB Storage Engine

Replication Warning: Offices using Replication will need to disable replication on all servers prior converting the database. It is suggested that you call and speak with a Replication Coordinator at Open Dental before this process is started.

These steps are for advanced technical users and will cause all databases within the database server to be converted to Innodb format.

Note:
  • MySQL version 5.5 is the minimum version required to convert the storage engine to InnoDB, however we strongly recommend that you upgrade to MariaDB before converting.
  • InnoDB users are responsible for their own backups.
  • Open Dental Cloud users cannot convert to InnoDb.

Review your backup/restore strategy before converting the database to ensure that you know how to validate restored backups. It will probably involve a dump to text file instead of copying files. You will not be able to use Open Dental's built in backup/restore tool.

  1. Make certain that all users stay out of Open Dental during the conversion process. This could take a couple of hours depending on the size of the database.
  2. Stop the MySQL Service.
  3. Make a backup copy of the data folder for safety. Typically found in C:\mysql\data.
  4. Change the my.ini file to include the InnoDB specific items (see below).
  5. Start the MySQL service.
  6. Launch the database through the Open Dental application.
  7. Go to Tools, Database Maintenance. Select the Tools tab and click InnoDB.
  8. Use the interface to convert all Open Dental tables from MyISAM to InnoDB. This process will take a long time since it is rewriting all of the database tables. Ensure that it ends successfully.

All of the tables in the database should have changed from three files per table (FRM, MYD, MYI) to two files per table (FRM, IBD). Your database tables will now all be in a single file rather than separate files inside a folder.

If you have multiple databases on your server you will need to follow steps 6 and 7 for each database.

InnoDB Backup and Restore Methods

This database type will not function with the Open Dental backup tool, or most online backups. It is important to have a backup plan in place prior to making changes.

InnoDB Cold Backup
One solution is to routinely perform a cold backup. This method can require a fair amount of disk space due to the copying of the entire MySQL Data Folder.

  1. Stop the MySQL service. Ensure that it stops without errors.
  2. Copy the MySQL Data directory (e.g. C:\mysql\data).
  3. Start the MySQL Service.

InnoDB SQL Dump
Another solution is to create a MySQL Dump of the database. This method will create a smaller total file size, in a .sql file format, which can be further compressed as a ZIP file.

  1. Run Windows Command Prompt as Administrator, then navigate to the MySQL bin folder (e.g. C:\Program Files\MySQL\MySQL Server 5.5\bin).
  2. Run the following command, replacing ### with the database name (e.g. opendental). Adjust the target directory as necessary. Enter the MySQL root password when prompted, if required.
    mysqldump -u root -p --max-allowed-packet=1024MB --verbose ### > C:\mysql\###.sql
  3. The dump will be located in the MySQL Folder directory (by default) when finished.

Note: If the dump process fails, it may be due to the max-allowed-packet size being too small. Increase the value for that variable and try again.

InnoDB Cold Backup Restore

  1. On the computer being used to restore the database, ensure MySQL is installed and configured for InnoDB.
  2. Stop the MySQL service.
  3. Archive any existing MySQL Data directory.
  4. Place the backup Data directory in the MySQL directory (e.g. C:\mysql).
  5. Start the MySQL service.

InnoDB SQL Dump Restore

  1. On the restore computer, ensure MySQL is installed and configured for InnoDB.
  2. Run Windows Command Prompt, then navigate to the MySQL bin folder.
  3. Run the following command. Enter the MySQL root password when prompted, if required.
    mysql -u root -p
  4. Run the following comand, replacing ### with a database name that currently does not exist in the MySQL data directory (e.g. opendental_[datetoday]).
    CREATE DATABASE ### CHARACTER SET=UTF8 COLLATE=utf8_general_ci;
  5. Run the following command, replacing ### with the database name you just created.
    use ###
  6. Run the following command to verify you are using an empty database.
    SHOW TABLES\G
    If this command returns anything other than Empty set, return to step 5 and verify the database name used is unique.
  7. Run the following command replacing ### with the name of the dump file (e.g. opendental) to import the dump into the newly created empty database.
    SOURCE C:/mysql/###.sql;

Alternative InnoDB Backup and Restore Methods using 3rd Party

A backup and restore can also be achieved using 3rd party software such as MySQL Workbench or SQLYog. Please reference their documentation for more information.