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

Support
1

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:

  1. I have followed the steps about the sixa document, deleted the sixa document, however, a new document is placed, the same error exist.
  2. 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

2

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

3

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)

4

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.

5

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

Screenshot from 2021-06-24 09-41-24
Screenshot from 2021-06-24 09-41-24970×211 47 KB

6

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

1 Like
7

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)

8

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?

9

Hi Jerry,

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

10

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.

11

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.

12

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)?

13

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

14

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

15

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!

16

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 …

17
  • 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?

18

@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?

19
  • 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?

20

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