InnoDB Transition

From OpenEMR Project Wiki

InnoDB migration is a part of upgrade script.

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 ""

  • 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

Solution

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

Potential problems

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.