InnoDB Transition

Overview

OpenEMR 4.3.1 has been migrated to InnoDB. The below page is a wiki page that was used for the planning of the migration.

System-wide changes

Most of the tables can be converted using simple ALTER TABLE ... ENGINE=INNODB

Added two new commands to SQL upgrade macro syntax:

#IfTableEngine TableName Engine - run SQL queries if table defined with specific engine. Used to convert tables that require more than simple query .

#IfInnoDBMigrationNeeded - find all remaining MyISAM tables and convert them to InnoDB.

Partially compatible definitions

Zeros as default DATE /Datetime value

More related to STRICT SQL support.

The following tables fields are declared with default value 0000-00-00 for DATE.

While this is not strictly InnoDB, this produces errors if sql_mode is not set to ( SET sql_mode= ).

Otherwise Error 1067 (Invalid default value) is generated.

• insurance_data.date
• drug_inventory.last_notify
• drugs.last_notify
• batchcom.msg_date_sent
• openemr_postcalendar_events.pc_eventDate

Not compatible definitions

AUTO_INCREMENT sequences

• ar_activity
• claims
• procedures_order_code
• procedure_answers

Those tables have PRIMARY KEY defined as multiple columns index with AUTO_INCREMENT column in the end.

Solution 1

Create sequence table for each of those table. Sequence table emulates AUTO_INCREMENT behavior.

Before INSERT'ing, obtain unique counter from sequence table.

After deleting, check if need to delete from sequence table.

Refactored the code to emulate INSERT functionality using transactions.

Solution 2

Calculate new sequence counter inside of INSERT query. Example:

  INSERT INTO `openemr`.`ar_activity`
  (
  `pid`, `encounter`, 
  `sequence_no`,   `code_type`,`code`,`payer_type`,`post_time`,`post_user`,`session_id`,`modified_time`,`follow_up`,`follow_up_note`,`account_code`
  )
  VALUES
  (1, 1, 
  (SELECT ifnull(max(c.sequence_no),0)+1 AS sequence_no FROM ar_activity as c where pid=1 and encounter=1), 
  "codetype","code",1,now(),1,1,now(),'Y',,"acct1" );

Solution 3

This was the chosen solution

Separate the collection of the sequence counter and the insert into 2 separate sql queries which are both completed within 1 transaction.

Fields TEXT NOT NULL DEFAULT ""

The following tables have TEXT and MEDIUMTEXT fields defined as TEXT NOT NULL DEFAULT ""

Native tables

• history_data.exams
• history_data.userarea11
• history_data.userarea12
• lang_custom.constant_name
• lang_custom.definition
• layout_options.conditions
• patient_data.billing_note
• rule_action_item.reminder_message
• procedure_providers.notes
• procedure_questions.options
• procedure_order.patient_instructions
• procedure_order_code.diagnoses
• procedure_report.report_notes
• procedure_result.comments

Non-native tables (ie. forms that are not added by default; core forms scan completed and still need to scan through the forms in the contrib directory)

• form_painmap.data (need to add to upgrade script; ensure does not break if table does not exist)

Solution

Change field type to TEXT / MEDIUMTEXT, drop NOT NULL DEFAULT "".

Potentially may automate this during upgrade script

Fields TEXT NOT NULL

Just looking into this now:

1. Is this an issue?
2. If this is an issues, should we drop the NOT NULL
3. If drop the NOT NULL, what effects will it have
4. If go this route, big issue with interface/super/edit_layout.php which is creating TEXT NOT NULL fields; easy to change the code, but what about all the sql fields that have already been created??
5. Potentially may automate this during upgrade script

Automated solution to standardize all the TEXT entries

Below is listing of all is_nullable='NO' TEXT entries on a standard install that are proposing to fix on the upgrade. The fix would also fix other TEXT entries in the database that are not part of the standard install.

mysql> SELECT `table_name`, `column_name`, `data_type`, `column_comment` FROM `information_schema`.`columns` WHERE (`data_type`='tinytext' OR `data_type`='text' OR `data_type`='mediumtext' OR `data_type`='longtext' ) AND is_nullable='NO' AND table_schema=database();
+------------------------+----------------------+------------+-----------------------------------------------------+
| table_name             | column_name          | data_type  | column_comment                                      |
+------------------------+----------------------+------------+-----------------------------------------------------+
| amendments             | amendment_desc       | text       | Amendment Details                                   |
| amendments_history     | amendment_note       | text       | Amendment requested from                            |
| ar_activity            | follow_up_note       | text       |                                                     |
| ar_session             | description          | text       |                                                     |
| audit_details          | field_value          | text       | openemr table's field value                         |
| audit_master           | comments             | text       |                                                     |
| automatic_notification | message              | text       |                                                     |
| clinical_rules_log     | value                | text       |                                                     |
| clinical_rules_log     | new_value            | text       |                                                     |
| customlists            | cl_list_item_long    | text       |                                                     |
| documents_legal_detail | dld_denial_reason    | longtext   |                                                     |
| facility_user_ids      | field_value          | text       |                                                     |
| history_data           | exams                | text       |                                                     |
| history_data           | usertext11           | text       |                                                     |
| history_data           | userarea11           | text       |                                                     |
| history_data           | userarea12           | text       |                                                     |
| lang_custom            | constant_name        | mediumtext |                                                     |
| lang_custom            | definition           | mediumtext |                                                     |
| layout_options         | conditions           | text       | serialized array of skip conditions                 |
| lbf_data               | field_value          | text       |                                                     |
| lbt_data               | field_value          | text       |                                                     |
| log_comment_encrypt    | checksum             | longtext   |                                                     |
| notification_log       | message              | text       |                                                     |
| notification_log       | patient_info         | text       |                                                     |
| patient_data           | billing_note         | text       |                                                     |
| patient_data           | industry             | text       |                                                     |
| procedure_order        | patient_instructions | text       |                                                     |
| procedure_order_code   | diagnoses            | text       | diagnoses and maybe other coding (e.g. ICD9:111.11) |
| procedure_providers    | notes                | text       |                                                     |
| procedure_questions    | options              | text       | choices for fldtype S and T                         |
| procedure_report       | report_notes         | text       | notes from the lab                                  |
| procedure_result       | comments             | text       | comments from the lab                               |
| rule_action_item       | reminder_message     | text       | Custom message in patient reminder                  |
| shared_attributes      | field_value          | text       |                                                     |
+------------------------+----------------------+------------+-----------------------------------------------------+

Potential problems

Installation is way to LONG without optimization. This was solved via following optimization:

• https://github.com/openemr/openemr/pull/130/commits/bb251d84195254e7abca2b69e7e2e4eb44bcbd46
• (May need to use above optimization in other places that import lots of data(ie the external/native import stuff).

The migration process is LONG and it may be EVEN LONGER for large tables.

Text fields values processing problems

Race conditions - work under load. Error 1213.