Restored openemr instance fail to select patients

Management and Administration
1

I had restore this v 4.3 openemr image before with no problem. Now when I select a patient I have the following error:
ERROR: query failed: SELECT distinct group_name FROM layout_options WHERE form_id = ? AND uor > 0 ORDER BY group_name, seq

Error: Expression #2 of ORDER BY clause is not in SELECT list, references column ‘openemr.layout_options.seq’ which is not in SELECT list; this is incompatible with DISTINCT

/var/www/html/openemr/library/options.inc.php at 2396:sqlStatement

Help need it

2

The original box database is a 10.1.21-MARIADB-1 innodb 5.6.34 and the target box database is mysql innodb 5.7.18-0ubuntu0.16.04.1

3

Hi @robertovasquez ,

Was this a development version (we never released 4.3; we relabeled it to a 5.0).

Is 5.0 working on your environment?

My guess here:
I am guessing 5.0 is working and that the sql-mode setting in mariadb is maybe the culprit. 5.0 works with any sql-mode, but prior versions required this to be set to nothing (ie. ‘’).

-brady

4

Thanks _brady for throwing light to this issue. In MySQL 5.7.5, the ONLY_FULL_GROUP_BY SQL mode is enabled by default. I will keep digging in to this issue and I will share the results.

5

Yes the 5.0 is working ok in my MYSQL 5.7.5 environment. The issue is the 4.3 that was working ok in a MariaDB 10.1.21.
I will try removing the ONLY_FULL_GROUP_BY sql-mode in the MYSQL 5.7.5 box.

6

I found out that MariaDB NO_ENGINE_SUBSTITUTION is sql-mode default when you install MariaDB, if you do not remove it; when you restore openemr in a different data base it will generate errors.

7

Are you using standard restore scripts? if so, will make sense to force sql-mode to blank in those scripts.

8

The openemr restored instance works after sql-_mode=’ ’ Thanks Brady