Preregistration MasterDataNotAvailable

application preregistration getting below error

but we have master data in the database
mosip_master=# \dt+

                  List of relations

Schema | Name | Type | Owner | Size |
Description

--------±-----------------------------±------±---------±-----------±-----------------------------------------------------------------------------------------------------------------------


master | admin_param | table | postgres | 8192 bytes | Admin Parameters : Stores admin parameters with values used in application modules.
master | app_authentication_method | table | postgres | 8192 bytes | App Authentication Method : Store List of application, process, role and their user authentication methods mapped with
sequence.
master | app_detail | table | postgres | 16 kB | Application Details : List of MOSIP Applications
master | app_role_priority | table | postgres | 8192 bytes | Application Role Priority : Defines role priority for each application and processes for application user.
master | appl_form_type | table | postgres | 8192 bytes | Application Form Type : Type of forms used for UIN / user registration.
master | applicant_valid_document | table | postgres | 8192 bytes | Applicant Valid Document : This is mapping table that relates applicant type, document category and document type, that
is valid document proof for UIN registration process.
master | authentication_method | table | postgres | 16 kB | Authentication Method : List of user Authentication methods supported by the system.
master | authentication_type | table | postgres | 8192 bytes | Authentication Type : List of Authentication types supported by the system.
master | batch_job_execution | table | postgres | 8192 bytes |
master | batch_job_execution_context | table | postgres | 8192 bytes |
master | batch_job_execution_params | table | postgres | 8192 bytes |
master | batch_job_instance | table | postgres | 0 bytes |
master | batch_step_execution | table | postgres | 8192 bytes |
master | batch_step_execution_context | table | postgres | 8192 bytes |
master | biometric_attribute | table | postgres | 8192 bytes | Biometric Attribute : List of all biometric attributes to be captured for each biometric type during UIN registration
master | biometric_type | table | postgres | 16 kB | Biometric Type : List of all biometric type supported for UIN registration
master | blacklisted_words | table | postgres | 16 kB | Black Listed Words : List of words that are black listed.
master | blocklisted_words | table | postgres | 8192 bytes | Black Listed Words : List of words that are black listed.
master | bulkupload_transaction | table | postgres | 8192 bytes | Bulk Upload Transaction: This transaction table to store all bulk upload transactions, This includes uploading master
table data as well as packets uploads to the registration processor.
master | ca_cert_store | table | postgres | 8192 bytes |
master | daysofweek_list | table | postgres | 8192 bytes | Days of Week List : Stores all days of the week with Code and Name. The Days of week are kept with multiple language ba
sed on country configured languages.
master | device_master | table | postgres | 8192 bytes | Device Master : Contains list of approved devices and details, like fingerprint scanner, iris scanner, scanner etc us
ed at registration centers. Valid devices with active status only allowed at registration centers for respective functionalities. Device onboarding are handled through admin application/portal
by the user who is having the device onboarding authority.
master | device_master_h | table | postgres | 8192 bytes | Device Master History : This to track changes to master record whenever there is an INSERT/UPDATE/DELETE ( soft delete
), Effective DateTimestamp is used for identifying latest or point in time information. Refer master.device_master table description for details.
master | device_spec | table | postgres | 8192 bytes | Device Specification : Specification of devices for each device type that are supported by system for various process
requirements, like scanning, printing, photo, biometric etc
master | device_type | table | postgres | 16 kB | Device Type : Types of devices that are supported by the system, like scanning, printing, photo, biometric etc
master | doc_category | table | postgres | 16 kB | Document Category : List document categories for registration for ex., POA, POI, etc
master | doc_format | table | postgres | 8192 bytes | Document Format : List of acceptable document formats supported by the system, for ex., pdf, jpeg, etc.
master | doc_type | table | postgres | 48 kB | Document Type : List of acceptable document types supported by the system, for ex., passport, driving license, etc
master | dynamic_field | table | postgres | 8192 bytes | Schema Dynamic Fields: Table to store the fields which are used dynamically in MOSIP applications. These fields are als
o part of identity schema.
master | gender | table | postgres | 16 kB | Gender : Gender List
master | global_param | table | postgres | 8192 bytes | Global Parameters: Stores global system and application parameters with default values used across applications and mod
ules. These can be configured/changed through admin portal as needed.
master | id_type | table | postgres | 16 kB | Id Type : List of ID types of various IDs generated , used or refered, for ex., PRID, RID, VID, etc

Hi Team,
after uploading though bulk upload master data database valid document table we facing below error. kindly let me know how to upload csv files in the admin portal

Hi Team,
Please find the masterdata-loader logs, we facing
duplicate key value violates unique constraint “pk_tmpltyp_code” error, Kindly help on this

root@ip-10-100-100-53:/home/ubuntu/mosip/mosip-infra/deployment/v3/mosip/masterdata-loader# kubectl -n masterdata-loader logs -f masterdata-loader-bdqrz
Cloning into ‘mosip-data’…
Uploading …
Running python upload …

[2023-09-20 07:09:43,997]
‘/home/mosip/mosip-data/mosip_master/xlsx/app_detail.xlsx’

[2023-09-20 07:09:44,206]
‘/home/mosip/mosip-data/mosip_master/xlsx/authentication_method.xlsx’

[2023-09-20 07:09:44,882]
‘/home/mosip/mosip-data/mosip_master/xlsx/biometric_type.xlsx’

[2023-09-20 07:09:45,630]
‘/home/mosip/mosip-data/mosip_master/xlsx/blacklisted_words.xlsx’

[2023-09-20 07:09:45,651]
‘/home/mosip/mosip-data/mosip_master/xlsx/device_type.xlsx’

[2023-09-20 07:09:46,350]
‘/home/mosip/mosip-data/mosip_master/xlsx/doc_category.xlsx’

[2023-09-20 07:09:46,420]
‘/home/mosip/mosip-data/mosip_master/xlsx/doc_type.xlsx’

[2023-09-20 07:09:46,495]
‘/home/mosip/mosip-data/mosip_master/xlsx/gender.xlsx’

[2023-09-20 07:09:46,527]
‘/home/mosip/mosip-data/mosip_master/xlsx/id_type.xlsx’

[2023-09-20 07:09:46,558]
‘/home/mosip/mosip-data/mosip_master/xlsx/language.xlsx’

[2023-09-20 07:09:46,586]
‘/home/mosip/mosip-data/mosip_master/xlsx/machine_type.xlsx’

[2023-09-20 07:09:46,683]
‘/home/mosip/mosip-data/mosip_master/xlsx/module_detail.xlsx’

[2023-09-20 07:09:46,728]
‘/home/mosip/mosip-data/mosip_master/xlsx/process_list.xlsx’

[2023-09-20 07:09:46,797]
‘/home/mosip/mosip-data/mosip_master/xlsx/reason_category.xlsx’

[2023-09-20 07:09:46,841]
‘/home/mosip/mosip-data/mosip_master/xlsx/reg_center_type.xlsx’

[2023-09-20 07:09:46,895]
‘/home/mosip/mosip-data/mosip_master/xlsx/role_list.xlsx’

[2023-09-20 07:09:46,982]
‘/home/mosip/mosip-data/mosip_master/xlsx/status_type.xlsx’

[2023-09-20 07:09:47,046]
‘/home/mosip/mosip-data/mosip_master/xlsx/template_file_format.xlsx’

[2023-09-20 07:09:47,093]
‘/home/mosip/mosip-data/mosip_master/xlsx/template_type.xlsx’
Traceback (most recent call last):
File “/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py”, line 1751, in _execute_context
self.dialect.do_executemany(
File “/usr/local/lib/python3.10/site-packages/sqlalchemy/dialects/postgresql/psycopg2.py”, line 962, in do_executemany
context._psycopg2_fetched_rows = xtras.execute_values(
File “/usr/local/lib/python3.10/site-packages/psycopg2/extras.py”, line 1270, in execute_values
cur.execute(b’'.join(parts))
psycopg2.errors.UniqueViolation: duplicate key value violates unique constraint “pk_tmpltyp_code”
DETAIL: Key (code, lang_code)=(RPR_SUP_REJECT_EMAIL_SUBJECT, eng) already exists.

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
File “/home/mosip/lib/upload_masterdata.py”, line 62, in
main()
File “/home/mosip/lib/upload_masterdata.py”, line 59, in main
upload_xlsx(files, table_order, args.user, args.db_user, args.db_pwd, args.db_host, args.db_port)
File “/home/mosip/lib/upload_masterdata.py”, line 36, in upload_xlsx
df.to_sql(table, engine, index=False, if_exists=‘append’)
File “/usr/local/lib/python3.10/site-packages/pandas/core/generic.py”, line 2872, in to_sql
sql.to_sql(
File “/usr/local/lib/python3.10/site-packages/pandas/io/sql.py”, line 717, in to_sql
pandas_sql.to_sql(
File “/usr/local/lib/python3.10/site-packages/pandas/io/sql.py”, line 1761, in to_sql
sql_engine.insert_records(
File “/usr/local/lib/python3.10/site-packages/pandas/io/sql.py”, line 1350, in insert_records
raise err
File “/usr/local/lib/python3.10/site-packages/pandas/io/sql.py”, line 1340, in insert_records
table.insert(chunksize=chunksize, method=method)
File “/usr/local/lib/python3.10/site-packages/pandas/io/sql.py”, line 967, in insert
exec_insert(conn, keys, chunk_iter)
File “/usr/local/lib/python3.10/site-packages/pandas/io/sql.py”, line 882, in _execute_insert
conn.execute(self.table.insert(), data)
File “/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py”, line 1263, in execute
return meth(self, multiparams, params, _EMPTY_EXECUTION_OPTS)
File “/usr/local/lib/python3.10/site-packages/sqlalchemy/sql/elements.py”, line 323, in _execute_on_connection
return connection._execute_clauseelement(
File “/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py”, line 1452, in _execute_clauseelement
ret = self._execute_context(
File “/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py”, line 1814, in _execute_context
self.handle_dbapi_exception(
File “/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py”, line 1995, in handle_dbapi_exception
util.raise
(
File “/usr/local/lib/python3.10/site-packages/sqlalchemy/util/compat.py”, line 207, in raise

raise exception
File “/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py”, line 1751, in _execute_context
self.dialect.do_executemany(
File “/usr/local/lib/python3.10/site-packages/sqlalchemy/dialects/postgresql/psycopg2.py”, line 962, in do_executemany
context._psycopg2_fetched_rows = xtras.execute_values(
File “/usr/local/lib/python3.10/site-packages/psycopg2/extras.py”, line 1270, in execute_values
cur.execute(b’'.join(parts))
sqlalchemy.exc.IntegrityError: (psycopg2.errors.UniqueViolation) duplicate key value violates unique constraint “pk_tmpltyp_code”
DETAIL: Key (code, lang_code)=(RPR_SUP_REJECT_EMAIL_SUBJECT, eng) already exists.

[SQL: INSERT INTO template_type (lang_code, code, descr, is_active, cr_by, cr_dtimes) VALUES (%(lang_code)s, %(code)s, %(descr)s, %(is_active)s, %(cr_by)s, %(cr_dtimes)s)]
[parameters: ({‘lang_code’: ‘eng’, ‘code’: ‘auth-email-content’, ‘descr’: ‘Template for authorization content’, ‘is_active’: True, ‘cr_by’: ‘admin’, ‘cr_dtimes’: ‘2023-09-20 07:09:47.148378’}, {‘lang_code’: ‘eng’, ‘code’: ‘auth-email-subject’, ‘descr’: ‘Template for authorization subject’, ‘is_active’: True, ‘cr_by’: ‘admin’, ‘cr_dtimes’: ‘2023-09-20 07:09:47.148378’}, {‘lang_code’: ‘eng’, ‘code’: ‘auth-sms’, ‘descr’: ‘Template for authorization SMS’, ‘is_active’: True, ‘cr_by’: ‘admin’, ‘cr_dtimes’: ‘2023-09-20 07:09:47.148378’}, {‘lang_code’: ‘eng’, ‘code’: ‘RPR_DUP_UIN_EMAIL’, ‘descr’: ‘Template for duplicate UIN Email’, ‘is_active’: True, ‘cr_by’: ‘admin’, ‘cr_dtimes’: ‘2023-09-20 07:09:47.148378’}, {‘lang_code’: ‘eng’, ‘code’: ‘RPR_DUP_UIN_SMS’, ‘descr’: ‘Template for duplicate UIN SMS’, ‘is_active’: True, ‘cr_by’: ‘admin’, ‘cr_dtimes’: ‘2023-09-20 07:09:47.148378’}, {‘lang_code’: ‘eng’, ‘code’: ‘RPR_TEC_ISSUE_EMAIL’, ‘descr’: ‘Template for Technical Issue Email’, ‘is_active’: True, ‘cr_by’: ‘admin’, ‘cr_dtimes’: ‘2023-09-20 07:09:47.148378’}, {‘lang_code’: ‘eng’, ‘code’: ‘RPR_TEC_ISSUE_SMS’, ‘descr’: ‘Template for Technical Issue SMS’, ‘is_active’: True, ‘cr_by’: ‘admin’, ‘cr_dtimes’: ‘2023-09-20 07:09:47.148378’}, {‘lang_code’: ‘eng’, ‘code’: ‘RPR_UIN_GEN_EMAIL’, ‘descr’: ‘Template for UIN generation Email’, ‘is_active’: True, ‘cr_by’: ‘admin’, ‘cr_dtimes’: ‘2023-09-20 07:09:47.148378’} … displaying 10 of 1772 total bound parameter sets … {‘lang_code’: ‘kan’, ‘code’: ‘RPR_SUP_REJECT_SMS’, ‘descr’: ‘Template for Supervisor Reject SMS’, ‘is_active’: True, ‘cr_by’: ‘admin’, ‘cr_dtimes’: ‘2023-09-20 07:09:47.148378’}, {‘lang_code’: ‘tam’, ‘code’: ‘RPR_SUP_REJECT_SMS’, ‘descr’: ‘Template for Supervisor Reject SMS’, ‘is_active’: True, ‘cr_by’: ‘admin’, ‘cr_dtimes’: ‘2023-09-20 07:09:47.148378’})]
(Background on this error at: Error Messages — SQLAlchemy 1.4 Documentation)
Masterdata uploaded successfully.

Hi @bal_sam

I have asked @aihamh to look into this and check why you are facing duplicate key value violates unique constraint “pk_tmpltyp_code” error .

Best Regards,
Team MOSIP

Hi @bal_sam ,

we get “pk_tmpltyp_code” error due to a foreign key constraint. master.template table has a foreign key constraint from template_file_format and template_type table. please create template_file_format, template_type table and then map the data with template table.

Best Regards,
Aiham
Team MOSIP

1 Like

Hi @aihamh

we already have above mentioned tables in the master database.

Hi @bal_sam ,

please check the values in master.template table and see if you have set the primary_key for the table. and the values in master.table is matching with the template_file_format and template_type table table. we have a Schema relation primary, foreign key between these 3 tables

1 Like

Hi @aihamh
we are installing masterdata loader via helm install, so before postgres init created all the tables. after helm install masterdata loader we struck in template type table. we checked values in master.table is matching with the template_file_format and template_type table table. please see the below sql tables Schema relation primary, foreign key between these 3 tables. also we tried to upload master data via admin portal facing error.

STATUS: FAILED, MESSAGE: [Line → 2 → Datatype mismatch/ Validation error / Failed to write into object]

SQL details below,

mosip_master=# \d template_file_format
Table “master.template_file_format”
Column | Type | Collation | Nullable | Default
------------±----------------------------±----------±---------±--------
code | character varying(36) | | not null |
descr | character varying(256) | | not null |
lang_code | character varying(3) | | not null |
is_active | boolean | | not null |
cr_by | character varying(256) | | not null |
cr_dtimes | timestamp without time zone | | not null |
upd_by | character varying(256) | | |
upd_dtimes | timestamp without time zone | | |
is_deleted | boolean | | | false
del_dtimes | timestamp without time zone | | |
Indexes:
“pk_tffmt_code” PRIMARY KEY, btree (code, lang_code)
Referenced by:
TABLE “template” CONSTRAINT “fk_tmplt_tffmt” FOREIGN KEY (file_format_code, lang_code) REFERENCES template_file_format(code, lang_code)

mosip_master=# \d template_type
Table “master.template_type”
Column | Type | Collation | Nullable | Default
------------±----------------------------±----------±---------±--------
code | character varying(64) | | not null |
descr | character varying(256) | | not null |
lang_code | character varying(3) | | not null |
is_active | boolean | | not null |
cr_by | character varying(256) | | not null |
cr_dtimes | timestamp without time zone | | not null |
upd_by | character varying(256) | | |
upd_dtimes | timestamp without time zone | | |
is_deleted | boolean | | | false
del_dtimes | timestamp without time zone | | |
Indexes:
“template_type_pkey” PRIMARY KEY, btree (code, lang_code)
Referenced by:
TABLE “template” CONSTRAINT “fk_tmplt_tmpltyp” FOREIGN KEY (template_typ_code, lang_code) REFERENCES template_type(code, lang_code)

mosip_master=# \d template
Table “master.template”
Column | Type | Collation | Nullable | Default
-------------------±----------------------------±----------±---------±--------
id | character varying(36) | | not null |
name | character varying(128) | | not null |
descr | character varying(256) | | |
file_format_code | character varying(36) | | not null |
model | character varying(128) | | |
file_txt | character varying | | |
module_id | character varying(36) | | |
module_name | character varying(128) | | |
template_typ_code | character varying(64) | | not null |
lang_code | character varying(3) | | not null |
is_active | boolean | | not null |
cr_by | character varying(256) | | not null |
cr_dtimes | timestamp without time zone | | not null |
upd_by | character varying(256) | | |
upd_dtimes | timestamp without time zone | | |
is_deleted | boolean | | | false
del_dtimes | timestamp without time zone | | |
Indexes:
“template_pkey” PRIMARY KEY, btree (id, lang_code)
Foreign-key constraints:
“fk_tmplt_tffmt” FOREIGN KEY (file_format_code, lang_code) REFERENCES template_file_format(code, lang_code)
“fk_tmplt_tmpltyp” FOREIGN KEY (template_typ_code, lang_code) REFERENCES template_type(code, lang_code)

Hi @aihamh , @sanchi-singh24

issue fixed after change mosipDataGithubBranch

helm -n $NS install masterdata-loader mosip/masterdata-loader --set mosipDataGithubBranch=1.2.0.1 --version $CHART_VERSION --wait

to
helm -n $NS install masterdata-loader mosip/masterdata-loader --set mosipDataGithubBranch=v1.2.0.1-B3 --version $CHART_VERSION --wait

Hi @bal_sam ,

Thats great. Thanks for the update.

Best Regards,
Aiham
Team MOSIP

1 Like