InnoDB Transition

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

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

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.

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.