Using MySQL on a Linux Server
This information is for advanced IT users only. Linux installs are the responsibility of your practice or IT to install. Open Dental support cannot install Linux for you. See also Linux and Macintosh.
- Install from RPM or install package that best matches system from http://dev.mysql.com/downloads/mysql/5.5.html.
- Setup grant tables on Linux by running the following commands. This is documented at:http://dev.mysql.com/doc/refman/5.5/en/grant.html.
GRANT ALL ON *.* TO 'root'@'%' IDENTIFIED BY '';
GRANT ALL ON *.* TO 'root'@'localhost' IDENTIFIED BY '';
- Edit the MySQL configuration file named my.cnf to disable the use of InnoDB tables by default. The my.cnf file is usually located at the path /etc/init.d/my.cnf. Add the following text on its own line: default-storage-engine=MyISAM
When done, the my.cnf file should look very similar to the my.ini file that's described on MySQL Server Variables. There should not be a lot of additional clutter.
- Copy a blank or current OpenDental database to the Linux server.
Each Linux installation might have a different MySQL data location or 'path'. The database path is documented in my.cnf and is defined on a line starting with "datadir=".
Copy the database files to a folder that you create in the data directory. For example, if the path is /var/lib/mysql and opendental is the name of your new database, copy to /var/lib/mysql/opendental/.
- Ensure the newly created database folder has permissions for MySQL to access the files. Either set the owner to the "mysql" user or set the folder permissions to allow access to all users. Open a terminal window and type one of the two commands (change the directory path to match your actual database path): "chown -R mysql:mysql /var/lib/mysql/opendental" or "chmod -R 777 /var/lib/mysql/opendental".
Set Up File Share (OpenDentImages)
This is just like setting up any other fileshare on a Linux machine. You may set it up however you wish, but all users need full permissions (read, write and delete).
A good option is to set up Samba, then set up the share. Here are some web sites with directions:
Steps we followed:
1. Install Webmin (www.webmin.com). Logon as root or other user with adequate permissions. Click the Servers tab, choose Samba windows filesharing.
2. Create fileshare, then edit securities to set <writeable> and <guest access> to yes.
3. Open an SSH terminal to your linux machine. Logon as root or other user with adequate permissions.
4. Navigate to immediate parent directory of shared directory.
5. Type chmod -R 777 foldername.
6. You are done. ls - l will show the permissions for all of the child folders including your share.
Once the share is set up, copy the contents of OpenDentImages to this new share, then set Data Paths to this location.
Convert an Existing Linux Open Dental MySQL database to use MyISAM tables instead of Innodb Tables
In Database Maintenance run the InnoDb tool.
Another option is below. Only consider this option if the office in question has been using the Linux server already.
- If needed, check to see if any of your current database tables are not in MyISAM format. Run the following query and replace the database name of 'opendental' with your actual database name:
WHERE TABLE_SCHEMA = 'opendental' AND
ENGINE NOT LIKE 'MyISAM';
If the query does not return any results, you do not need to complete steps 2 - 6
- Stop the MySQL service.
- Backup the database.
- Restart the MySQL service.
- Generate the alter table script using the following script (make name adjustments as needed). Make sure that the output folder exists and that the "mysql" user has full permissions on that folder. Also make sure that the output script file (see below) DOES NOT exist before running the command or else the command will fail. For example, if running the command a second time, you must delete the script previously generated.
SELECT CONCAT('ALTER TABLE ',TABLE_NAME,' ENGINE = ',@outengine,';')
WHERE TABLE_SCHEMA = @dbname
INTO OUTFILE '/tmp/mysql/myisam_convert_script.txt';
- Run the output script "myisam_convert_script.txt" in mysql as follows. From a terminal window type (make changes for actual temporary script file path as needed):
mysql -u root -p opendental < /tmp/mysql/myisam_convert_script.txt
- Edit the my.cnf file and make sure that the 'skip-innodb' option is in the file on its own line and that the line is uncommented. Save any changes to the my.cnf file and if the file was modified then restart the mysql service. This step prevents new tables from being generated as innodb tables in the future. Note: Newer versions of MySQL might not utilize the skip-innodb line, if that is the case, look for or add the line 'default-storage-engine=MyISAM'
Problem: During an update, receive this error: The database tables are in MyISAM format, but the default database engine format is InnoDB. You must change the default storage engine within the my.ini (or my.cnf) file on the database server and restart MySQL in order to fix this problem. Exiting.
Solution: To my.cnf, add default-storage-engine=MyISAM (Step 3 above under MySQL)
Case Sensitivity: Linux MySQL is case sensitive by default. Open Dental tries to use only lowercase table names, however may have some mixed case variables. If you have issues with case sensitivity, you may want to turn case sensitivity off. Simply add this line to the my.cnf file on the Linux server: lower_case_table_names=1.
Or you can contact us and we will try to update the code.
Example of errors due to case sensitivity:
- MySql.Data.MySqlClient.MySqlException (0x80004005): Table 'opendental_bsm_lou_sh.ehrTrigger' doesn't exist.
(Notice the table name ehrTrigger is mixed case).
- Unhandled exception trying to access the EHR Dashboard.