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

brady.miller · July 17, 2021, 7:44pm

how about:

SELECT `value` FROM `keys`;

(btw, if this is a production environment, don’t paste the values here since it is a encryption key)

brady.miller · July 17, 2021, 7:46pm

then try:

SELECT * FROM `keys`;

(btw, if this is a production environment, don’t paste the values here since it is a encryption key)

brady.miller · July 17, 2021, 7:51pm

btw, what I am potentially getting at here is you have a blank value for the key (will be more clear when see what for get for the subsequent queries above). As an example, here is what I see for query SELECT `value` FROM `keys` WHERE `name` = 'sixa';

brady.miller · July 17, 2021, 7:55pm

Actually, I bet you just blacked out your value in your screenshot. Either way, lets see what the subsequent queries show (and keep blacking out the encryption key values).

rickzimm · July 17, 2021, 8:03pm

SELECT value FROM keys.pdf|attachment (119.5 KB)

rickzimm · July 17, 2021, 8:04pm

SELECT FROM keys.pdf|attachment (117.6 KB)

rickzimm · July 17, 2021, 8:05pm

Just to make sure I blacked out the values

brady.miller · July 18, 2021, 5:04am

Here is the only place where a new key is inserted into database:
openemr/CryptoGen.php at rel-600 · openemr/openemr · GitHub

And in your case it should never get there since you have a value in the row with name sixa. After this line:
openemr/CryptoGen.php at rel-600 · openemr/openemr · GitHub
would place the following debug error_log and then check your php error log:

error_log("DEBUG_label: " . $label);
error_log("DEBUG_value: " . $sqlValue['value']);

rickzimm · July 18, 2021, 6:55am

Hi @brady.miller,

Here the error_log : <removed since private info in it>

Note: in the log: first I placed the lines in CryptoGen.php, then logged in in openemr and selected settings practice-> pharmacy.

rickzimm · July 18, 2021, 7:06am

Hi @brady.miller
Maybe, this is connected:
Here the error what also happens in a client account when editing client information under provider.
error1.pdf (103.1 KB)
Note: although the adjustment will be saved.

brady.miller · July 18, 2021, 7:06am

hi @rickzimm ,
Removed the link to pdf to not publish the keys. But something weird is definitely happening (works fine grabbing the key, but then all of sudden it stops working) and I’ll look at the log in more detail.

rickzimm · July 18, 2021, 7:11am

Hi @brady.miller,
Thank you, I did indeed miss private info in the error log, not very useful of me

brady.miller · July 18, 2021, 9:47am

pretty crazy stuff. things look great in the log (sixa and sixb keys are properly collected via the query) until all of sudden the query to collect the sixa key comes back blank:

[Sun Jul 18 08:44:02.345381 2021] [fcgid:warn] [pid 20221:tid 140668198401792] [client 2a02:a45c:3bef:1:c444:7a79:c0d2:3e59:36648] mod_fcgid: stderr:
DEBUG_label: sixa, referer: https://www.domainname.nl/openemr/controller.php?practice_settings&pharmacy&action=list
[Sun Jul 18 08:44:02.345386 2021] [fcgid:warn] [pid 20221:tid 140668198401792] [client 2a02:a45c:3bef:1:c444:7a79:c0d2:3e59:36648] mod_fcgid: stderr:
DEBUG_value: , referer: https://www.domainname.nl/openemr/controller.php?practice_settings&pharmacy&action=list
[Sun Jul 18 08:44:02.345391 2021] [fcgid:warn] [pid 20221:tid 140668198401792] [client 2a02:a45c:3bef:1:c444:7a79:c0d2:3e59:36648] mod_fcgid: stderr:
SQL Error with statement:query failed: INSERT INTO `keys` (`name`, `value`) VALUES (?, ?)--Duplicate entry &#039;sixa&#039; for key
&#039;name&#039;==&gt;/var/www/vhosts/domainname.nl/httpdocs/openemr/src/Common/Crypto/CryptoGen.php at 435:sqlStatementNoLog, referer:
https://www.domainname.nl/openemr/controller.php?practice_settings&pharmacy&action=list

Is this happening consistently on one script, or does it just happen randomly while using OpenEMR?

It appears there is a critical failure in the mysq/mariadb server (intermittently not collecting a row/item in a query when it is there ). Would look into the mysql/mariadb log at the time of the failure to see if that provides any hints.

rickzimm · July 18, 2021, 11:13am

Hi @brady.miller,

The sixa error only happens at the practice settings in the pharmacy and X12 submenus.
Note: in menu Administration -> practice, in submenu only practice settings appears, not rules and alerts!

A few other things happens:

I’m not sure if this is of any importants (still figuring out), however I also have an error when using “patient finder”: DataTables warning: table id=pt_table - Invalid JSON response. For more information about this error, please see http://datatables.net/tn/1. And also when selecting other menus items it says: Site ID is missing from session data!

Note: this only happens after 10 a 15 minutes after logging in, before that it works fine. After logging in again, the error temporarily disappears. I already checked the Core demographics and they are original.

About “Would look into the mysql/mariadb log at the time of the failure to see if that provides any hints.” I have to take a look into it, I’m not sure I have the privilege to look into the mysql/mariadb log, the mysql/mariadb log is at a relative path I cannot access, have to ask my provider … to be continued …
The logs I’m able to read are:
access_log
access_log.processed
access_log.webstat
access_ssl_log.processed
access_ssl_log.webstat
error_log
proxy_access_ssl_log
proxy_error_log
xferlog_regular
xferlog_regular.processed
xferlog_regular.webstat

The mysql/mariadb log is at a relative path I cannot access. have to ask my provider … to be continued …

stephenwaite · July 19, 2021, 3:37pm

hi @tjbarr, deleted that post and reposting with removed key

I am getting this error also when I go to fees -> Billing Manager
I have updated to 6.0 and have ran the 2nd patch
My php version is 7.3 and I am using a chrome browser
There are 2 non empty fields in the key table

Query Error

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

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

/emr/src/Common/Crypto/CryptoGen.php at 433:sqlStatementNoLog
/emr/src/Common/Crypto/CryptoGen.php at 231:collectCryptoKey(six,a,database)
/emr/src/Common/Crypto/CryptoGen.php at 100:coreDecrypt(**removed key **,database,six)
/emr/src/Common/Crypto/CryptoGen.php at 462:decryptStandard(**removed key **,database)
/emr/src/Common/Crypto/CryptoGen.php at 153:collectCryptoKey(six,a,drive)
/emr/src/Common/Crypto/CryptoGen.php at 66:coreEncrypt(SHOW COLUMNS FROM x12_partners,drive,six)
/emr/src/Common/Logging/EventAuditLogger.php at 742:encryptStandard(SHOW COLUMNS FROM x12_partners)
/emr/src/Common/Logging/EventAuditLogger.php at 644:recordLogItem(1,security-administration-select,EBH-admin-20,Default,SHOW COLUMNS FROM x12_partners,Security)
/emr/library/ADODB_mysqli_log.php at 45:auditSQLEvent(SHOW COLUMNS FROM x12_partners,1,)
/emr/vendor/adodb/adodb-php/drivers/adodb-mysqli.inc.php at 632:Execute(SHOW COLUMNS FROM x12_partners)
/emr/src/Common/ORDataObject/ORDataObject.php at 114:MetaColumns(x12_partners)
/emr/library/classes/X12Partner.class.php at 43:_load_enum(processing_format,)
/emr/interface/billing/billing_report.php at 109:__construct()

tjbarr · July 19, 2021, 3:38pm

Thanks so much. My mistake.

stephenwaite · July 19, 2021, 3:41pm

like Brady was wondering

Would look into the mysql/mariadb log at the time of the failure to see if that provides any hints.

rickzimm · July 20, 2021, 7:22am

Hi @brady.miller, here the information with the logs …
There are 3 errors which are probably related, they appear or begin when:

In practice settings: under pharmacy and X12 partners: sixa error, keys, as discussed in this topic. See also attachments: error log and Mariadb log (actually nothing is in the MariaDB log about the error, simulated the error om Monday 7/19, last info is from 07/18, provider made a mistake there is also log 07/19)
In a client account under menu “documents” when f.i. a pdf document saved in a category (saved before the patch was installed), when selecting document: ERROR cannot load pdf. Load again.

a. Via the FTP in the file defaults->documents where the pdf documents related to a client are stored, the pdf is still there and readable, however, for some documents the extension changed from pdf to 93f217ce-d73a-4341-8c0c-951c8198741a document.

b. After the patch: when uploading a new pdf document in the client account itself to a category, the pdf can be opened again after logging out and logging in. However, via the FTP-access the extension has been changed from pdf to 93f217ce-d73a-4341-8c0c-951c8198741a document
Patient finder, error after not using the finder for a while, if the patient finder is used regularly it keeps on working. Otherwise, the only way to get openemr working again, is logging out and logging in. I could only find info about this in the access log (see attachment).

error log.pdf (71.5 KB)
Mariadb log.pdf (57.4 KB)
access log.pdf (75.6 KB)
Mariadb log 07-19.pdf (57.4 KB)

brady.miller · July 23, 2021, 6:42am

Hi,

I’d focus on the first issue since there appears to be a critical issue in a query (ie. if a query is not returning results that we know are in the database, then guessing there will be many other related issues). Would output a trace on the error to see the path it takes in the codebase with following after this line of code (also use above error_log() lines where had previously to help orient us in the error log except for the $sqlValue[‘value’] output so the log won’t contain sensitive information)
openemr/CryptoGen.php at rel-600 · openemr/openemr · GitHub

error_log("DEBUG_trace " . print_r(debug_backtrace(), true));

rickzimm · July 23, 2021, 7:57am

Hi Brady,

The following error appears after opening practice settings->pharmacy:

Fatal error : Allowed memory size of 134217728 bytes exhausted (tried to allocate 65015808 bytes) in /var/www/vhosts/mannenkliniek-de-boer.nl/httpdocs/openemr/src/Common/Crypto/CryptoGen.php on line 429

And error_log: Error log - Fri Jul 23 09.pdf (90.3 KB)