search

orangehrm / orangehrm

OrangeHRM is a comprehensive Human Resource Management (HRM) System that captures all the essential functionalities required for any enterprise.
GNU General Public License v3.0
746 stars 537 forks source link

Getting error on Mysql 5.7 #1582

Closed nikita-6 closed 1 year ago

nikita-6 commented 1 year ago

OrangeHRM version

5.3

Environment details

Describe the bug

Hi,

I am getting following error on installing OrangeHRM 5.3 on PHP 7.4 and Mysql 5.7 while populating the database.

orange

"Request failed with status code 500. For more details check the error log in /src/log/installer.log file"

Upon checking the log file I am getting following error.

An exception occurred while executing a query: SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'orange53._ohrm_temp_table.col_string' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by [] []

I have also tried on my another testing server with PHP 7.4 and Mysql 5.5 and it seems to be working perfectly fine.

Is the Mysql >= 5.7 is incompatible ?

To reproduce

  1. Extract the package on server.
  2. Fill the details of Database.
  3. The installer throws error while installing the script.

Expected behavior

Installer should work seamlessly

Relevant log output

[2022-12-29T08:29:38.970743+00:00] installer.INFO: Matched route "{route}". {"route":"installer_process","route_parameters":{"_controller":"OrangeHRM\\Installer\\Controller\\Installer\\InstallerController::handle","_route":"installer_process"},"request_uri":"[[URL]]/installer/index.php/installer/process","method":"GET"} []
[2022-12-29T08:29:39.473918+00:00] installer.INFO: Matched route "{route}". {"route":"api_installer_start_registration","route_parameters":{"_controller":"OrangeHRM\\Installer\\Controller\\Installer\\Api\\InstallerDataRegistrationAPI::handle","_route":"api_installer_start_registration"},"request_uri":"[[URL]]/installer/index.php/installer/api/send-data/installer-start","method":"POST"} []
[2022-12-29T08:29:39.609970+00:00] installer.INFO: Matched route "{route}". {"route":"api_installer_database","route_parameters":{"_controller":"OrangeHRM\\Installer\\Controller\\Installer\\Api\\InstallationDatabaseAPI::handle","_route":"api_installer_database"},"request_uri":"[[URL]]/installer/index.php/installer/api/installation/database","method":"POST"} []
[2022-12-29T08:29:39.617859+00:00] installer.INFO: Instance details: {"os":{"os":"Linux","release_name":"3.10.0-1062.1.1.el7.x86_64","version_info":"#1 SMP Fri Sep 13 22:55:44 UTC 2019"},"php":{"version":"7.4.33"},"mysql":{"client_version":"mysqlnd 7.4.33","server_version":"5.7.40","conn_type":"Localhost via UNIX socket"},"server":"Apache","ohrm":{"version":"5.3"}} [] []
[2022-12-29T08:29:39.797316+00:00] installer.INFO: Matched route "{route}". {"route":"api_installer_pre_migration_check","route_parameters":{"_controller":"OrangeHRM\\Installer\\Controller\\Installer\\Api\\PreMigrationCheck::handle","_route":"api_installer_pre_migration_check"},"request_uri":"[[URL]]/installer/index.php/installer/api/installation/pre-migration","method":"POST"} []
[2022-12-29T08:29:39.804936+00:00] installer.INFO: Instance details: {"os":{"os":"Linux","release_name":"3.10.0-1062.1.1.el7.x86_64","version_info":"#1 SMP Fri Sep 13 22:55:44 UTC 2019"},"php":{"version":"7.4.33"},"mysql":{"client_version":"mysqlnd 7.4.33","server_version":"5.7.40","conn_type":"Localhost via UNIX socket"},"server":"Apache","ohrm":{"version":"5.3"}} [] []
[2022-12-29T08:29:39.871907+00:00] installer.ERROR: An exception occurred while executing a query: SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'orange53._ohrm_temp_table.col_string' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by [] []
[2022-12-29T08:29:39.871997+00:00] installer.ERROR: #0 [[PATH]]/src/vendor/doctrine/dbal/src/Connection.php(1814): Doctrine\DBAL\Driver\API\MySQL\ExceptionConverter->convert(Object(Doctrine\DBAL\Driver\PDO\Exception), Object(Doctrine\DBAL\Query)) #1 [[PATH]]/src/vendor/doctrine/dbal/src/Connection.php(1749): Doctrine\DBAL\Connection->handleDriverException(Object(Doctrine\DBAL\Driver\PDO\Exception), Object(Doctrine\DBAL\Query)) #2 [[PATH]]/src/vendor/doctrine/dbal/src/Connection.php(1055): Doctrine\DBAL\Connection->convertExceptionDuringQuery(Object(Doctrine\DBAL\Driver\PDO\Exception), 'SELECT COUNT(_o...', Array, Array) #3 [[PATH]]/src/vendor/doctrine/dbal/src/Query/QueryBuilder.php(310): Doctrine\DBAL\Connection->executeQuery('SELECT COUNT(_o...', Array, Array) #4 [[PATH]]/installer/Util/DatabaseUserPermissionEvaluator.php(116): Doctrine\DBAL\Query\QueryBuilder->executeQuery() #5 [[PATH]]/installer/Util/DatabaseUserPermissionEvaluator.php(376): OrangeHRM\Installer\Util\DatabaseUserPermissionEvaluator->evalCrudPermission() #6 [[PATH]]/installer/Controller/Upgrader/Api/PreMigrationCheck.php(35): OrangeHRM\Installer\Util\DatabaseUserPermissionEvaluator->evalPrivilegeDatabaseUserPermission() #7 [[PATH]]/installer/Controller/AbstractInstallerRestController.php(57): OrangeHRM\Installer\Controller\Upgrader\Api\PreMigrationCheck->handlePost(Object(OrangeHRM\Framework\Http\Request)) #8 [[PATH]]/installer/Controller/AbstractInstallerController.php(85): OrangeHRM\Installer\Controller\AbstractInstallerRestController->execute(Object(OrangeHRM\Framework\Http\Request)) #9 [[PATH]]/src/vendor/symfony/http-kernel/HttpKernel.php(152): OrangeHRM\Installer\Controller\AbstractInstallerController->handle(Object(OrangeHRM\Framework\Http\Request)) #10 [[PATH]]/src/vendor/symfony/http-kernel/HttpKernel.php(74): Symfony\Component\HttpKernel\HttpKernel->handleRaw(Object(OrangeHRM\Framework\Http\Request), 1) #11 [[PATH]]/installer/Framework/HttpKernel.php(244): Symfony\Component\HttpKernel\HttpKernel->handle(Object(OrangeHRM\Framework\Http\Request), 1, true) #12 [[PATH]]/installer/index.php(66): OrangeHRM\Installer\Framework\HttpKernel->handleRequest(Object(OrangeHRM\Framework\Http\Request)) #13 {main} [] []
RajithaKumara commented 1 year ago

Disable ONLY_FULL_GROUP_BY

If sql_mode, ONLY_FULL_GROUP_BY enabled by default please disable it. (StackOverflow, OrangeHRM CI)

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

Warning Please not that if the database server contains more applications (other DBs), make sure to check their requirements also. Otherwise other application may break.

RajithaKumara commented 1 year ago

Closing as required sql_mode change of the database

ujangaripin24 commented 7 months ago

Disable ONLY_FULL_GROUP_BY

If sql_mode, ONLY_FULL_GROUP_BY enabled by default please disable it. (StackOverflow, OrangeHRM CI)

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

Warning Please not that if the database server contains more applications (other DBs), make sure to check their requirements also. Otherwise other application may break.

thx it works!!!!

niyaz11186 commented 7 months ago

used the above sql query and changed localhost to 127.0.0.1 worked for me

hanny0201 commented 1 month ago

it works!!! Thank you!