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 ""
- Native tables
- 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
- Non-native tables (ie. forms that are not added by default; core forms scan completed and still need to scan through the forms in the contrib directory)
- form_painmap.data (need to add to upgrade script; ensure does not break if table does not exist)
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
- If go this route, big issue with interface/super/edit_layout.php which is creating TEXT NOT NULL fields; easy to change the code, but what about all the sql fields that have already been created??
Potential problems
- Installation is way to LONG without optimization. This was solved via following optimization:
- https://github.com/openemr/openemr/pull/130/commits/bb251d84195254e7abca2b69e7e2e4eb44bcbd46
- (May need to use above optimization in other places that import lots of data(ie the external/native import stuff).
- 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.