ERROR when upgrade from 4.1.1 to 4.1.2

phongtrang wrote on Wednesday, May 28, 2014:

Hello everyone,
The software has served us well over the last 3-4 years. Today we ran into some issue that we can not solve. Hope to get help here. The error occurred during upgrade software process. Please see the message below. After that I revert to 4.1.1 and the software works fine. Any attempt to use new binary will not let anyone login

OpenEMR Database Upgrade
Processing 4_1_1-to-4_1_2_upgrade.sql …
Skipping section #IfNotTable report_results
ALTER TABLE version ADD COLUMN v_acl int(11) NOT NULL DEFAULT 0
Skipping section #IfMissingColumn documents_legal_detail dld_moved
Skipping section #IfMissingColumn documents_legal_detail dld_patient_comments
Skipping section #IfMissingColumn documents_legal_master dlm_upload_type
ALTER TABLE list_options ADD COLUMN codes varchar(255) NOT NULL DEFAULT ‘’
UPDATE list_options SET codes=‘SNOMED-CT:449868002’ WHERE list_id=‘smoking_status’ AND option_id=‘1’ AND title=‘Current every day
smoker’
UPDATE list_options SET codes=‘SNOMED-CT:428041000124106’ WHERE list_id=‘smoking_status’ AND option_id=‘2’ AND title=‘Current som
e day smoker’
UPDATE list_options SET codes=‘SNOMED-CT:8517006’ WHERE list_id=‘smoking_status’ AND option_id=‘3’ AND title=‘Former smoker’
UPDATE list_options SET codes=‘SNOMED-CT:266919005’ WHERE list_id=‘smoking_status’ AND option_id=‘4’ AND title=‘Never smoker’
UPDATE list_options SET codes=‘SNOMED-CT:77176002’ WHERE list_id=‘smoking_status’ AND option_id=‘5’ AND title=‘Smoker, current st
atus unknown’
UPDATE list_options SET codes=‘SNOMED-CT:266927001’ WHERE list_id=‘smoking_status’ AND option_id=‘9’ AND title=‘Unknown if ever s
moked’
INSERT INTO list_options ( list_id, option_id, title, seq, is_default, codes ) VALUES (‘smoking_status’, ‘15’, ‘Heavy tobacco smoke
r’, 70, 0, “SNOMED-CT:428071000124103”)
INSERT INTO list_options ( list_id, option_id, title, seq, is_default, codes ) VALUES (‘smoking_status’, ‘16’, ‘Light tobacco smoke
r’, 80, 0, “SNOMED-CT:428061000124105”)
ALTER TABLE code_types ADD COLUMN ct_term tinyint(1) NOT NULL default 0 COMMENT ‘1 if this is a clinical term’
DROP TABLE IF EXISTS temp_table_one
CREATE TABLE temp_table_one ( id int(11) NOT NULL DEFAULT ‘0’, seq int(11) NOT NULL DEFAULT ‘0’) ENGINE=MyISAM
INSERT INTO temp_table_one (id, seq) VALUES ( IF( ((SELECT MAX(ct_id) FROM code_types)>=100), ((SELECT MAX(ct_id) FROM
code_types) + 1), 100 ) , IF( ((SELECT MAX(ct_seq) FROM code_types)>=100), ((SELECT MAX(ct_seq) FROM code_types) + 1), 10
0 ) )
INSERT INTO code_types (ct_key, ct_id, ct_seq, ct_mod, ct_just, ct_fee, ct_rel, ct_nofs, ct_diag, ct_active, ct_label, ct_external,
ct_claim, ct_proc, ct_term ) VALUES (‘SNOMED-CT’ , (SELECT MAX(id) FROM temp_table_one), (SELECT MAX(seq) FROM temp_table_o ne), 0, ‘’, 0, 0, 1, 0, 0, ‘SNOMED Clinical Term’, 7, 0, 0, 1)
DROP TABLE temp_table_one
ALTER TABLE codes CHANGE code code varchar(25) NOT NULL default ‘’
ALTER TABLE billing CHANGE code code varchar(20) default NULL
ALTER TABLE ar_activity CHANGE code code varchar(20) NOT NULL COMMENT ‘empty means claim level’
CREATE TABLE procedure_questions ( lab_id bigint(20) NOT NULL DEFAULT 0 COMMENT ‘references users.id to identify the lab’, pro cedure_code varchar(31) NOT NULL DEFAULT ‘’ COMMENT ‘references procedure_type.procedure_code to identify this order type’, quest ion_code varchar(31) NOT NULL DEFAULT ‘’ COMMENT ‘code identifying this question’, seq int(11) NOT NULL default 0 COMMENT ‘seque
nce number for ordering’, question_text varchar(255) NOT NULL DEFAULT ‘’ COMMENT ‘descriptive text for question_code’, required
tinyint(1) NOT NULL DEFAULT 0 COMMENT ‘1 = required, 0 = not’, maxsize int NOT NULL DEFAULT 0 COMMENT ‘maximum length if text in
put field’, fldtype char(1) NOT NULL DEFAULT ‘T’ COMMENT ‘Text, Number, Select, Multiselect, Date, Gestational-age’, options te
xt NOT NULL DEFAULT ‘’ COMMENT ‘choices for fldtype S and T’, activity tinyint(1) NOT NULL DEFAULT 1 COMMENT ‘1 = active, 0 = ina
ctive’, PRIMARY KEY (lab_id, procedure_code, question_code)) ENGINE=MyISAM

ERROR: query failed: CREATE TABLE procedure_questions ( lab_id bigint(20) NOT NULL DEFAULT 0 COMMENT ‘references users.id to id
entify the lab’, procedure_code varchar(31) NOT NULL DEFAULT ‘’ COMMENT ‘references procedure_type.procedure_code to identify thi
s order type’, question_code varchar(31) NOT NULL DEFAULT ‘’ COMMENT ‘code identifying this question’, seq int(11) NOT NULL def
ault 0 COMMENT ‘sequence number for ordering’, question_text varchar(255) NOT NULL DEFAULT ‘’ COMMENT ‘descriptive text for quest
ion_code’, required tinyint(1) NOT NULL DEFAULT 0 COMMENT ‘1 = required, 0 = not’, maxsize int NOT NULL DEFAULT 0 COMMENT ‘maxi
mum length if text input field’, fldtype char(1) NOT NULL DEFAULT ‘T’ COMMENT ‘Text, Number, Select, Multiselect, Date, Gestation
al-age’, options text NOT NULL DEFAULT ‘’ COMMENT ‘choices for fldtype S and T’, activity tinyint(1) NOT NULL DEFAULT 1 COMMENT
‘1 = active, 0 = inactive’, PRIMARY KEY (lab_id, procedure_code, question_code)) ENGINE=MyISAM

Error: BLOB/TEXT column ‘options’ can’t have a default value

bradymiller wrote on Wednesday, May 28, 2014:

Hi,

This may be a windows mysql5 bug:

What is your OS? If windows, are you using the Xampp-openemr package?

-brady
OpenEMR

phongtrang wrote on Thursday, May 29, 2014:

it is linux unfortunately. Opensuse 13.1 with Mariadb

phongtrang wrote on Thursday, May 29, 2014:

I did the same modification as window and it works afterward. Basically, we have to place the database in 4.0 mode. MySQL 5.0 and above is not compatible with existing database correct?

mdsupport wrote on Thursday, May 29, 2014:

See if this report is applicable for your situation.

bradymiller wrote on Thursday, May 29, 2014:

Hi,

Guessing this is a bug in MariaDB, since no reports of this happening on mysql5 for OpenEMR in linux or windows. The stackflow link above reports the bug to happen in windows and not linux, and guessing the bug got fixed since there are no reports of this issue happening in windows for OpenEMR. This would need some testing to confirm, though.

Note there are many places in database.sql where a TEXT entry gets set to a default ‘’, so odd that your initial installation of 4.1.1 worked.

Anybody else with thoughts here?

-brady
OpenEMR

bradymiller wrote on Thursday, May 29, 2014:

So, according to MD Support’s post (I just saw it after my post), it appears sql-mode should not have STRICT_TRANS_TABLES enabled and then it should work. If this is confirmed, then may need to add this to the installation instructions.

-brady

phongtrang wrote on Thursday, May 29, 2014:

Even with 4.1.1 with mariadb I had to remove STRICK_TRANS_TABLES for it to work. I think it is a good idea to add this to installation instructions. Or if someone can have a script to convert database to be compatible with the new default.

Here is what I change inside file /etc/my.conf
from this line

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

to this line:

sql_mode=MYSQL40

bradymiller wrote on Friday, May 30, 2014:

Hi,

It does appear that we may need to clean the database code to not do this since mysql 5.6 and above may have STRICT_TRANS_TABLES enabled by default:
http://www.tocker.ca/2014/01/14/making-strict-sql_mode-the-default.html

Wouldn’t be too much work. Any other thoughts on this?

-brady
OpenEMR