MySQL Innodb

 

MySQL Innodb 

MySQL can store data using a handful of different storage engines.  Almost all installations of EZ2000 Plus Dental use the MyISAM storage engine.  For some large offices, MyISAM tables lock up for short periods of time when complex queries are being run (ex: Running the recall list).  For example, the behavior you might notice could be that EZ2000 Plus Dental is usually very fast, except when you perform certain specific actions or run certain tools, then EZ2000 Plus Dental locks up for a short period of time on all computers.  If you are experiencing such slowness, first make sure that you are on the most current version of EZ2000 Plus Dental to ensure that the slowness is not due to any known bugs.  The Troubleshooting Slowness steps should also be followed.  Additionally, the slowness issue should be brought to the attention of EZ2000 Plus Dental support in case there is a slowness bug that needs to be fixed.  As a last resort to resolving this type of slowness issue, 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.

How To Convert to Innodb Storage Engine

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

Make sure that no users are currently using EZ2000 Plus Dental.
From the MySQL database server, stop the MySQL service within the service manager.
Edit the my.ini file.
    Comment out the skip-innodb line if present, like this: #skip-innodb
    Comment out the default-storage-engine line if present, like this: #default-storage-engine=MyISAM.
Start the MySQL service within the service manager.
Perform the following steps for each database within the database server:
   1. Make a backup.
   2. Delete the C: empinnodb_convert_script.txt file if it exists. This file might be clutter from step 3 below.
   3. Run the following script from a query capable program such as SQLYog or the command line,
   4. Making sure to launch SQLYog as administrator and to replace the dbname with the database being converted:
       SET @dbname='opendental';
       SET @outengine='INNODB';
       SELECT CONCAT('ALTER TABLE ',@dbname,'.',TABLE_NAME,' ENGINE = ',@outengine,';')
           FROM INFORMATION_SCHEMA.TABLES
           WHERE TABLE_SCHEMA = @dbname
       INTO OUTFILE 'c:/temp/innodb_convert_script.txt';
   5. Open the innodb_convert_script.txt in a text editor and copy the entire contents.  Paste text file contents into SQLYog and run the script.
   The script may take 20 minutes or longer to run.
Once all databases on the database server have been converted to Innodb format, users may begin using EZ2000 Plus Dental again.

Your database tables will now all be in a single file rather than separate files inside a folder. Make sure to review your backup/restore strategy so that you know how to validate restored backups.  It will probably involved a dump to text file instead of copying files.

 

 

  EZ2000 Plus Dental Software 800-273-5033