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

OK, you’re using edge browser? Have you cleared the cache?

In this case I use Firefox, Edge and Chrome I did clear the cache several times

OK, when you say you’ve checked the core demographics, have you compared the IDs in the Demographics layout

with the Name column in the patient_data table like in this phpmyadmin view?
Screenshot from 2021-07-15 09-37-01

I checked demographics with the demo version.

I will check also as mentioned above.

The following copied from my error log:
[Thu Jul 15 15:27:58.336768 2021] [fcgid:warn] [pid 7409:tid 140668190009088] [client 2a02:a45c:3bef:1:716f:4b2f:4c0c:b845:36538] mod_fcgid: stderr: SQL Error with statement:query failed: INSERT INTO keys (name, value) VALUES (?, ?)–Duplicate entry 'sixa' for key 'name'==>/var/www/vhosts/domeinname.nl/httpdocs/openemr/src/Common/Crypto/CryptoGen.php at 433:sqlStatementNoLog, referer: https://www.domeinname.nl/openemr/controller.php?practice_settings&pharmacy&action=list

Does this make the error a little clearer?

what if you comment out lines 19 and 21 of sites/default/config.php so the cryptogen isn’t called on these 2 array values? does that suppress the error in practice settings?

I did ## lines 19 and 21, the same error still exist …

A temporary workaround would be to replace the INSERT statement by :

  1. If the value of sixa needs to be updated -
INSERT INTO keys (name, value) VALUES (?,?)
** Will need to provide "value" twice to be replacing additional "?" **
  1. If logic requires current sixa to be retained -
INSERT IGNORE INTO keys (name, value) VALUES (?,?);
1 Like

The issue here is the routine that queries keys table to check if the key exist is returning they don’t so, it tries to insert. I suspect the keys table is corrupt so try to run

  • CHECK TABLE `keys`;
  • ALTER TABLE `keys` ENGINE = InnoDB;
  • OPTIMIZE TABLE `keys`; This will really recreate and analyze instead
  • FLUSH TABLE `keys`;

btw: check your collations

1 Like

Hi @sjpadgett ,

I do not have the proper RELOAD privileges to run this in PHPmyAdmin, so I asked my provider to solve this, as soon as I have the information or right privileges from them I will get back to you.

What do I have to do for: check your collations?

I installed a new (clean) openemr vs 6.0.0 (2), checked that everything worked as it is supposed to be, uploaded the (old) database from the openemr version which are giving the errors in the new openemr version, and replaced the correct keys (sixa and sixb) in the new openemr.
After logging in, in the new openemr version, the same errors still persist …
Maybe this will help you find the solution …

Hi @mdsupport,

where do I have to put those lines, in which file?

huh, something’s not making sense then, since if those are commented then the practice settings wouldn’t call the cryptogen function

From the error log, it looks like src/Common/Crypto/CryptoGen.php - line 433.
Disclaimer - Workaround till root cause is hunted down by the project angels.

Hi @mdsupport,

Can you show me how I have to insert those lines in: sqlStatementNoLog(“INSERT INTO keys (name, value) VALUES (?, ?)”, [$label, base64_encode($newKey)]);

Is this what you mean?
sqlStatementNoLog("INSERT INTO ‘keys’ (‘name’, ‘value’) VALUES (?, ?), ON DUPLICATE KEY UPDATE ‘value’ = ?, INSERT IGNORE INTO ‘keys’ (‘name’, ‘value’) VALUES (?, ?), [$label, base64_encode($newKey)]);

Hi @sjpadgett,

Attached the results of SQL query to the database …pr. screen results query.pdf (105.6 KB)
Can you make something out of it?

hi @rickzimm, can you share the collation info too?

Screenshot from 2021-07-16 16-52-58

Hi @stephenwaite and @sjpadgett

Here the collation:
pr. screen collation.pdf (301.8 KB)

will become if you want new value to replace current record -

sqlStatementNoLog(“INSERT INTO keys ( name , value ) VALUES (?, ?) ON DUPLICATE KEY UPDATE value=?”, [$label, base64_encode($newKey), base64_encode($newKey)]);

Be sure to save the backup in case something strange happens…

Hi @mdsupport,

Replaced new value for current: in openemr by “practice settings” it’s says: unknown, actually for all menus.
Replaced new value for back -up (only CryptoGen.php), it didn’t worked out well: WSOD

Is it enough to only replace the database back up or also openemr itself?

Her is my error_log:
[Sat Jul 17 10:23:41.889921 2021] [fcgid:warn] [pid 7409:tid 140668265543424] [client 2a02:a45c:3bef:1:6000:873:b6eb:db00:40696] mod_fcgid: stderr: OpenEMR Error : Key creation is not working - Exiting., referer: https://www.domainname.nl/openemr/controller.php?practice_settings&pharmacy&action=list
[Sat Jul 17 10:23:51.413342 2021] [fcgid:warn] [pid 7315:tid 140668257150720] [client 2a02:a45c:3bef:1:6000:873:b6eb:db00:40754] mod_fcgid: stderr: OpenEMR Error : Key creation is not working - Exiting., referer: https://www.domeinname.nl/openemr/interface/main/tabs/main.php?token_main=onFEjAhJw69X5JY8SCSwULJ41oVC2FXmg1rBJcq0

hi @rickzimm, Can you try reinstall with
collation utf8mb4_general_ci
Screenshot from 2021-07-17 08-37-01

and server charset utf8Server charset: UTF-8 Unicode (utf8mb4)

Screenshot from 2021-07-17 08-36-05