InnoDB Transition

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

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

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.