Error Encountered While Restoring Database (Mysql 8.1.0)

[prasannajeet-01]

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]

@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]

Already raised an issue for MySQL 5.7 (#71 (comment)) 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]

@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]

@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