InnoDB Transition
Partially compatible definitions
Zeros as default DATE /Datetime value
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.
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.