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:
- Is this an issue?
- If this is an issues, should we drop the NOT NULL
- 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.