Difference between revisions of "InnoDB Transition"
m (→Solution 2) |
Bradymiller (talk | contribs) |
||
(33 intermediate revisions by 2 users not shown) | |||
Line 1: | Line 1: | ||
==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 <code>ALTER TABLE ... ENGINE=INNODB </code> | |||
Added two new commands to SQL upgrade macro syntax: | |||
<code>#IfTableEngine '''TableName''' '''Engine''' </code> - run SQL queries if table defined with specific engine. Used to convert tables that require more than simple query . | |||
<code>#IfInnoDBMigrationNeeded</code> - find all remaining MyISAM tables and convert them to InnoDB. | |||
== Partially compatible definitions == | == Partially compatible definitions == | ||
=== Zeros as default DATE /Datetime value === | === 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 == | == Not compatible definitions == | ||
Line 27: | Line 55: | ||
==== Solution 2 ==== | ==== Solution 2 ==== | ||
Calculate new sequence counter inside of INSERT query. | 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 "" === | === Fields TEXT NOT NULL DEFAULT "" === | ||
The following tables have TEXT and MEDIUMTEXT fields defined as <code>TEXT NOT NULL DEFAULT ""</code> | The following tables have TEXT and MEDIUMTEXT fields defined as <code>TEXT NOT NULL DEFAULT ""</code> | ||
:Native tables | |||
* history_data.exams | :* history_data.exams | ||
* history_data.userarea11 | :* history_data.userarea11 | ||
* history_data.userarea12 | :* history_data.userarea12 | ||
* lang_custom.constant_name | :* lang_custom.constant_name | ||
* layout_options.conditions | :* lang_custom.definition | ||
* patient_data.billing_note | :* layout_options.conditions | ||
* rule_action_item.reminder_message | :* patient_data.billing_note | ||
* procedure_providers.notes | :* rule_action_item.reminder_message | ||
* procedure_questions.options | :* procedure_providers.notes | ||
* procedure_order.patient_instructions | :* procedure_questions.options | ||
* procedure_order_code.diagnoses | :* procedure_order.patient_instructions | ||
* procedure_report.report_notes | :* procedure_order_code.diagnoses | ||
* procedure_result.comments | :* 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 ==== | ==== Solution ==== | ||
Line 67: | Line 98: | ||
Change field type to TEXT / MEDIUMTEXT, drop NOT NULL DEFAULT "". | 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. | |||
<pre> | |||
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 | | | |||
+------------------------+----------------------+------------+-----------------------------------------------------+ | |||
</pre> | |||
== 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. | :The migration process is LONG and it may be EVEN LONGER for large tables. | ||
Text fields values processing problems | :Text fields values processing problems | ||
Race conditions - work under load. Error 1213. | :Race conditions - work under load. Error 1213. |
Latest revision as of 08:14, 22 June 2016
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.