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

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

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

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.