InnoDB Transition
Overview
- OpenEMR 4.3.1 has been migrated to InnoDB. The below page is a wiki page that was used for the planning of the migration.
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 "".
Potentially may automate this during upgrade script
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??
- Potentially may automate this during upgrade script
Automated solution to standardize all the TEXT entries
- Below is listing of all is_nullable='NO' TEXT entries on a standard install that are proposing to fix on the upgrade. The fix would also fix other TEXT entries in the database that are not part of the standard install.
mysql> SELECT `table_name`, `column_name`, `data_type`, `column_comment` FROM `information_schema`.`columns` WHERE (`data_type`='tinytext' OR `data_type`='text' OR `data_type`='mediumtext' OR `data_type`='longtext' ) AND is_nullable='NO' AND table_schema=database(); +------------------------+----------------------+------------+-----------------------------------------------------+ | table_name | column_name | data_type | column_comment | +------------------------+----------------------+------------+-----------------------------------------------------+ | amendments | amendment_desc | text | Amendment Details | | amendments_history | amendment_note | text | Amendment requested from | | ar_activity | follow_up_note | text | | | ar_session | description | text | | | audit_details | field_value | text | openemr table's field value | | audit_master | comments | text | | | automatic_notification | message | text | | | clinical_rules_log | value | text | | | clinical_rules_log | new_value | text | | | customlists | cl_list_item_long | text | | | documents_legal_detail | dld_denial_reason | longtext | | | facility_user_ids | field_value | text | | | history_data | exams | text | | | history_data | usertext11 | text | | | history_data | userarea11 | text | | | history_data | userarea12 | text | | | lang_custom | constant_name | mediumtext | | | lang_custom | definition | mediumtext | | | layout_options | conditions | text | serialized array of skip conditions | | lbf_data | field_value | text | | | lbt_data | field_value | text | | | log_comment_encrypt | checksum | longtext | | | notification_log | message | text | | | notification_log | patient_info | text | | | patient_data | billing_note | text | | | patient_data | industry | text | | | procedure_order | patient_instructions | text | | | procedure_order_code | diagnoses | text | diagnoses and maybe other coding (e.g. ICD9:111.11) | | procedure_providers | notes | text | | | procedure_questions | options | text | choices for fldtype S and T | | procedure_report | report_notes | text | notes from the lab | | procedure_result | comments | text | comments from the lab | | rule_action_item | reminder_message | text | Custom message in patient reminder | | shared_attributes | field_value | text | | +------------------------+----------------------+------------+-----------------------------------------------------+
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.
