search

PSMRI / HWC-API

Health and Wellness centre (HWC) is one of the comprehensive applications of AMRIT designed to capture details of 7 Service packages as per guidelines which should be available at Health and Wellness centre.
GNU General Public License v3.0
1 stars 32 forks source link

Error Encountered While Restoring Database (Mysql 8.1.0) #72

Open prasannajeet-01 opened 1 year ago

prasannajeet-01 commented 1 year ago

Description Encountering the following error during database restoration: ERROR 1231 (42000) at line 172: Variable 'sql_mode' can't be set to the value of 'NO_AUTO_CREATE_USER'

Steps to Reproduce

1.0 Create a database named 'db_iemr' ,db_reporting. 2.0 Provide necessary privileges. 3.0 Execute the following commands: mysql -h localhost -P 3306 -u root -p db_iemr < db_iemr.sql mysql -h localhost -P 3306 -u root -p db_iemr < db_iemr_logins_dummydata.sql

repeat step no 3 for db_reporting

Expected Behavior Expecting the restoration process to complete successfully without any errors.

Actual Behavior ERROR 1231 (42000) at line 172: Variable 'sql_mode' can't be set to the value of 'NO_AUTO_CREATE_USER' ERROR 1100 (HY000) at line 2259: Table 'm_ProviderServiceMapping' was not locked with LOCK TABLES

Additional Context

MySQL Version: 8.1.0

Possible solution: Remove 'NO_AUTO_CREATE_USER' from sql file after removing this and retrying the restoration u will get ERROR 1100 (HY000) at line 2259: Table 'm_ProviderServiceMapping' was not locked with LOCK TABLES error in db_iemr, comment out or remove the insert statement.

Labels

-bug -database -restoration

Anjana2310 commented 1 year ago

@drtechie As per the details shared here, you are using MySQL Version: 8.1.0 . Please use MySQL 5.7 as mentioned in the AMRIT documentation and let us know if any issues in restoration.

prasannajeet-01 commented 1 year ago

Already raised an issue for MySQL 5.7 (https://github.com/PSMRI/HWC-API/issues/71#issue-1933489775) but if we remove the NO_AUTO_CREATE_USER from the dump file, it will work with the latest version of MySQL as well as MySQL 5.7. @drtechie @Anjana2310

mo839639 commented 1 year ago

@drtechie ,It will restore if we remove NO_AUTO_CREATE_USER sql_mode from dump file. But to check from Application code it will not workout. @ravishanigarapu ,Please suggest what changes required.

ravishanigarapu commented 1 year ago

@drtechie We have done the changes for mysql upgrade from API side pom.xml file and property file modified. pom.xml file we have added mysql latest(8.0.33)version. also changed driver class name in application properties. that also need to considered. Please let us know if any DB restoration issues in mysql 5.7 version. CC : @mo839639