Query Error ERROR: query failed: INSERT INTO `keys` (`name`, `value`) VALUES (?, ?) - Error: Duplicate entry ‘sixa’ for key ‘name’

rickzimm · June 22, 2021, 10:08am

Situation
I upgraded to 6.0 (2) emr and run sql_upgrade.php
Under the “settings for practice” the “healthinsurance companies” comes up with this:

Query Error

ERROR: query failed: INSERT INTO keys (name, value) VALUES (?, ?)

Error: Duplicate entry ‘sixa’ for key ‘name’

/var/www/vhosts/domainname.com/httpdocs/openemr/src/Common/Crypto/CryptoGen.php at 433:sqlStatementNoLog
/var/www/vhosts/domainname.com/httpdocs/openemr/src/Common/Crypto/CryptoGen.php at 231:collectCryptoKey(six,a,database)
/var/www/vhosts/domainname.com/httpdocs/openemr/src/Common/Crypto/CryptoGen.php at 100:coreDecrypt(bnRPZxeP3IT9IHro2Q5cth+1d43HcXvwTyC8uoQjnTIuJkWea7aWRLyqzHfhgJnTzNMpJT5tL1CfvQpjqLJ+T34uQ9sWeFwHgOLtv7Wm6ujxGmJDefufQFOHyXEDDxkVlMQbHDdYbgUW8SWdcLRUqQ==,database,six)
/var/www/vhosts/domainname.com/httpdocs/openemr/src/Common/Crypto/CryptoGen.php at 462:decryptStandard(006bnRPZxeP3IT9IHro2Q5cth+1d43HcXvwTyC8uoQjnTIuJkWea7aWRLyqzHfhgJnTzNMpJT5tL1CfvQpjqLJ+T34uQ9sWeFwHgOLtv7Wm6ujxGmJDefufQFOHyXEDDxkVlMQbHDdYbgUW8SWdcLRUqQ==,database)
/var/www/vhosts/domainname.com/httpdocs/openemr/src/Common/Crypto/CryptoGen.php at 153:collectCryptoKey(six,a,drive)
/var/www/vhosts/domainname.com/httpdocs/openemr/src/Common/Crypto/CryptoGen.php at 66:coreEncrypt(SHOW COLUMNS FROM x12_partners,drive,six)
/var/www/vhosts/domainname.com/httpdocs/openemr/src/Common/Logging/EventAuditLogger.php at 742:encryptStandard(SHOW COLUMNS FROM x12_partners)
/var/www/vhosts/domainname.com/httpdocs/openemr/src/Common/Logging/EventAuditLogger.php at 644:recordLogItem(1,security-administration-select,admin,DeBoer,SHOW COLUMNS FROM x12_partners,Security)
/var/www/vhosts/domainname.com/httpdocs/openemr/library/ADODB_mysqli_log.php at 45:auditSQLEvent(SHOW COLUMNS FROM x12_partners,1,)
/var/www/vhosts/domainname.com/httpdocs/openemr/vendor/adodb/adodb-php/drivers/adodb-mysqli.inc.php at 632:Execute(SHOW COLUMNS FROM x12_partners)
/var/www/vhosts/domainname.com/httpdocs/openemr/src/Common/ORDataObject/ORDataObject.php at 114:MetaColumns(x12_partners)
/var/www/vhosts/domainname.com/httpdocs/openemr/library/classes/X12Partner.class.php at 43:_load_enum(processing_format,)
/var/www/vhosts/domainname.com/httpdocs/openemr/library/classes/InsuranceCompany.class.php at 153:__construct()
/var/www/vhosts/domainname.com/httpdocs/openemr/controllers/C_InsuranceCompany.class.php at 19:__construct()
/var/www/vhosts/domainname.com/httpdocs/openemr/library/classes/Controller.class.php at 100:__construct()
/var/www/vhosts/domainname.com/httpdocs/openemr/controllers/C_PracticeSettings.class.php at 58:act(Array)
/var/www/vhosts/domainname.com/httpdocs/openemr/library/classes/Controller.class.php at 137:insurance_company_action(list)
/var/www/vhosts/domainname.com/httpdocs/openemr/controller.php at 6:act(Array)

OpenEMR Version
I’m using OpenEMR version v6.0.0 (2)

Browser:
I’m using: Microsoft Edge

Operating System
I’m using: Windows 10 64

PHP vs 7.3.28

Note:

I have followed the steps about the sixa document, deleted the sixa document, however, a new document is placed, the same error exist.
I did the sql_upgrade.php, (5.0.2 to 6).

Logs
Did you check the logs? No logs found (empty)
Was there anything pertinent in them?
Please paste them here (surround with three backticks (```) for readability.
You can also turn on User Debugging under Administration->Globals->Logging User Debugging Options=>All

sjpadgett · June 23, 2021, 3:47pm

@brady.miller
Can you help out with this. I’m unsure why this is happening…

brady.miller · June 24, 2021, 8:28am

hi @rickzimm and @sjpadgett ,
Looked through the code and the only way i think i can see that happening is if the value for the sixa entry in the keys sql table was set to a blank entry. Are you seeing a value there or is it empty? (if so, don’t paste it here since it is a private encryption key that rec not making public)

rickzimm · June 24, 2021, 8:51am

For sixa as wel for sixb in the value field there is a code filled in. Settings:
Column: value
Type: text

I’m not sure if this is of any importants (still figuring out), however I also have an error when using “search patient”: DataTables warning: table id=pt_table - Invalid JSON response. For more information about this error, please see http://datatables.net/tn/1

This only happens after 20 to 25 minutes after logging in, before that it works fine. After logging in again, the error has temporarily disappeared.

Note: I already checked the Core demographics and they are original. What is striking is that communication with the database is very slow during an adjustment in the core demographics.

stephenwaite · June 24, 2021, 1:41pm

hi @rickzimm, when you upgraded did you bring over the old documents?

rickzimm · June 24, 2021, 2:35pm

Hi Stephan, thank you for ypur response.
I did the steps mentioned in the upgrade.

rickzimm · June 24, 2021, 10:51pm

Hi Brady,

There is also something else not working as it should be, maybe it’s related:
In a (self made) template I added some keywords, like SSN and insurance provider.
Till the upgrade it worked fine, however, after the upgrade the added keywords are not printed in the template.
Note: I added the keywords as mentioned here : Adding keywords - Management and Administration - OpenEMR Community (open-emr.org)

rickzimm · June 25, 2021, 11:14am

Hi Brady,

More errors are popping-up. Here a print screen of the error, and again related with the domographics!
Print screen - error.pdf (60.1 KB)

Does this make sense?

rickzimm · June 25, 2021, 7:42pm

Hi Jerry,

I added some more issues, can you make sense out of it?

sjpadgett · June 25, 2021, 8:47pm

Best I can tell you is to try running sql_upgrade again. Make sure you’re on patch 2.
You won’t hurt anything by running upgrade again.

rickzimm · June 26, 2021, 9:41am

If this doesn’t work, is it an idea to install OPENEMR v 6 again or only patch 2?

I have a back-up from the database.

Ralf_Lukner · June 28, 2021, 2:06am

What was the original version you upgraded from? If it is not 5.0.2(5), can you upgrade to that intermediate version first and make sure that works? Also, what was the original MySql database version (before the upgrade)?

rickzimm · June 29, 2021, 8:04am

I upgraded from the last version.

The original MySql database version was 10.1.38-MariaDB.
My provider is using since a month (+/-) version 10.5.10-MariaDB

Ralf_Lukner · June 29, 2021, 8:57am

Do an ls -la on the main website directory (in docker ), sites, sites/default and make sure that all directories and files are owned by apache:apache (NOT root!). Then repeat the sql_upgrade on the original database. (@adunsulag taught me this trick. He probably can give you better advice on the database upgrade issue than my advice if this does not work).
Ralf

rickzimm · June 29, 2021, 9:19am

Hi Ralf,

Thank you, I’m in contact with my provider and ask them to help met out with the apache …
They are very cooperative and eager to help me out … normally, they response with a solution within 1/2 a day. Keep you posted!

rickzimm · June 29, 2021, 3:23pm

I have sent Stephen a message about the ls -la on the main website directory (in docker ), sites, sites/default and make sure that all directories and files are owned by apache:apache (NOT root!).

My provider isn’t seeing any abnormalities, hope that Stephen can put me in the right direction …

sjpadgett · June 29, 2021, 4:00pm

Have you first checked the keys table so we know what it is currently set at?(This is important to know)
Have you deleted sixa sixb in sites or are they even there?
Are you using file encryption?

Woops sorry I see you did give answer on keys. Is there more than one entry?

sjpadgett · June 29, 2021, 4:14pm

@rickzimm
For some reason when the method that check for those keys is getting back that sixa is not in table so it’s try to create key.
You must not delete those because previous encrypted files may be lost.

I can give you a debug version of the class to trap the workflow bette if you want to try?

rickzimm · June 29, 2021, 6:23pm

I just compared the demographics layout again with the demo version (without demo data ). The only difference is sometimes in the order column and the “ss” has another label name.
f.i. under choices: in demo openemr row 14 is cmsportal_login and in my openemr there is no row 14, row 15 is cmsportal_login.
Does that makes a difference?
in the openemr files I copied the sixa and sixb in sites before I deleted those 2. New ones were made, again.
No file encryption

What do you mean by more enties?

rickzimm · June 29, 2021, 6:25pm

debug version of the class to trap the workflow better … yes please, that might give more insight