frappe.exceptions.ValidationError: item_code field cannot be set as unique in tabItem, as there are non-unique existing values

yashodhan · September 24, 2018, 7:58pm

cd ~/frappe-bench # or your bench path
cd apps/frappe && git fetch upstream && git checkout master && cd -
cd apps/erpnext && git fetch upstream && git checkout master && cd -
bench update --requirements
bench update --patch

post error logs to pastebin, so that I can help troubleshoot.

shmuel · September 24, 2018, 8:44pm

yashodhan · September 25, 2018, 7:27pm

Run following sequence, your frappe database .sql file was not able to process properly.

cd ~/frappe-bench # or your bench path
cd apps/frappe && git clean -df && git fetch upstream && git checkout master && cd -
bench update --requirements
bench update --patch

Next time, also include command followed by error log (if you encounter error)
Please also share following logs, if above cmds fails.

cd ~/frappe-bench # or your bench path
tail 100 ./logs/node-socketio.error.log
tail 100 ./logs/worker.error.log
tail 100 ./logs/web.error.log
tail 100 ./logs/redis-cache.error.log
tail 100 ./logs/redis-socketio.error.log
tail 100 ./logs/web.error.log

Sai_Nath_Ravipati · February 6, 2019, 7:33am

None of the commands help in resolution of the issue…

clarkej · February 6, 2019, 2:35pm

In post # 2 above Sangram’s advice seems worth a try.

anand gives similar advice and also a PR fix to refine a validation to prevent that case.

To identify exactly what non-unique Item records are causing the problem might help clarify your problem case. To inspect the database would require sql skills.

Mohd_Rizal_sharif · March 10, 2019, 9:31am

hey i hv same problem here https://discuss.frappe.io/t/get-error-updating-v11-migrating-the-doctype-item/46579

any solution yet?

Himanshu_Mishra · March 10, 2019, 11:32am

Use bench mariadb.

Use following command

select item_code from `tabItem` group by item_code having count(*) > 1;

This command will give you items which aren’t unique. The use bench console to debug.

Mohd_Rizal_sharif · March 11, 2019, 7:15pm

MariaDB [c578196c85e90dce]> select item_code from `tabItem` group by item_code having count(*) > 1;
+-----------+
| item_code |
+-----------+
| FORMULA80 |
+-----------+

hi @Himanshu_Mishra thanks for helping, this is output i get, i don’t know how to debug it,

Himanshu_Mishra · March 12, 2019, 1:46am

SELECT 
    name, item_group, 
    stock_uom, is_stock_item 
FROM 
   `tabItem` 
WHERE item_code="FORMULA80";

Can you run this query as well??

Mohd_Rizal_sharif · March 12, 2019, 5:09am

yes here what i get

MariaDB [c578196c85e90dce]> SELECT
    ->     name, item_group,
    ->     stock_uom, is_stock_item
    -> FROM
    ->    `tabItem`
    -> WHERE item_code="FORMULA80";
+-------------+------------------------------------+-----------+---------------+
| name        | item_group                         | stock_uom | is_stock_item |
+-------------+------------------------------------+-----------+---------------+
| FORMULA80   | Uninterruptible Power Supply (UPS) | Unit      |             1 |
| FORMULA80-1 | Uninterruptible Power Supply (UPS) | Nos       |             1 |
+-------------+------------------------------------+-----------+---------------+
2 rows in set (0.00 sec)

Himanshu_Mishra · March 12, 2019, 5:22am

can you open http://[[yourdomain]]/desk#Form/Item/FORMULA80-1 and check if this item can be deleted?

Mohd_Rizal_sharif · March 12, 2019, 5:43am

no i cannot access at all

Himanshu_Mishra · March 12, 2019, 5:56am

There’s a file frappe-bench/apps/frappe/frappe/modules/patch_handler.py. In that, there’s a function like

def check_session_stopped():
	if frappe.db.get_global("__session_status")=='stop':
		frappe.msgprint(frappe.db.get_global("__session_status_message"))
		raise frappe.SessionStopped('Session Stopped')

Change it to

def check_session_stopped():
	return
	if frappe.db.get_global("__session_status")=='stop':
		frappe.msgprint(frappe.db.get_global("__session_status_message"))
		raise frappe.SessionStopped('Session Stopped')

then run

sudo supervisorctl restart all

Then open above link again

Mohd_Rizal_sharif · March 12, 2019, 9:01am

i got this

Himanshu_Mishra · March 12, 2019, 10:12am

open frappe-bench/apps/erpnext/erpnext/stock/doctype/item/item.json
search for item_code, and try finding

{
   "allow_bulk_edit": 0, 
   "allow_in_quick_entry": 0, 
   "allow_on_submit": 0, 
   "bold": 1, 
   "collapsible": 0, 
   "columns": 0, 
   "description": "", 
   "fieldname": "item_code", 
   "fieldtype": "Data", 
   "hidden": 0, 
   "ignore_user_permissions": 0, 
   "ignore_xss_filter": 0, 
   "in_filter": 0, 
   "in_global_search": 1, 
   "in_list_view": 0, 
   "in_standard_filter": 0, 
   "label": "Item Code", 
   "length": 0, 
   "no_copy": 0, 
   "oldfieldname": "item_code", 
   "oldfieldtype": "Data", 
   "permlevel": 0, 
   "precision": "", 
   "print_hide": 0, 
   "print_hide_if_no_value": 0, 
   "read_only": 0, 
   "remember_last_selected_value": 0, 
   "report_hide": 0, 
   "reqd": 0, 
   "search_index": 0, 
   "set_only_once": 0, 
   "translatable": 0, 
   "unique": 1
  },

change unique field to

{
   "allow_bulk_edit": 0, 
   "allow_in_quick_entry": 0, 
   "allow_on_submit": 0, 
   "bold": 1, 
   "collapsible": 0, 
   "columns": 0, 
   "description": "", 
   "fieldname": "item_code", 
   "fieldtype": "Data", 
   "hidden": 0, 
   "ignore_user_permissions": 0, 
   "ignore_xss_filter": 0, 
   "in_filter": 0, 
   "in_global_search": 1, 
   "in_list_view": 0, 
   "in_standard_filter": 0, 
   "label": "Item Code", 
   "length": 0, 
   "no_copy": 0, 
   "oldfieldname": "item_code", 
   "oldfieldtype": "Data", 
   "permlevel": 0, 
   "precision": "", 
   "print_hide": 0, 
   "print_hide_if_no_value": 0, 
   "read_only": 0, 
   "remember_last_selected_value": 0, 
   "report_hide": 0, 
   "reqd": 0, 
   "search_index": 0, 
   "set_only_once": 0, 
   "translatable": 0, 
   "unique": 0
  },

Solve the duplicate issue, the stash the changes.

Mohd_Rizal_sharif · March 12, 2019, 11:37am

Executing erpnext.patches.v11_0.rename_field_max_days_allowed in mydomain.com (c578196c85e90dce)
Traceback (most recent call last):
File “/usr/lib/python2.7/runpy.py”, line 174, in _run_module_as_main
“main”, fname, loader, pkg_name)
File “/usr/lib/python2.7/runpy.py”, line 72, in _run_code
exec code in run_globals
File “/home/frappe/frappe-bench/apps/frappe/frappe/utils/bench_helper.py”, line 97, in
main()
File “/home/frappe/frappe-bench/apps/frappe/frappe/utils/bench_helper.py”, line 18, in main
click.Group(commands=commands)(prog_name=‘bench’)
File “/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/click/core.py”, line 764, in call
return self.main(*args, **kwargs)
File “/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/click/core.py”, line 717, in main
rv = self.invoke(ctx)
File “/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/click/core.py”, line 1137, in invoke
return _process_result(sub_ctx.command.invoke(sub_ctx))
File “/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/click/core.py”, line 1137, in invoke
return _process_result(sub_ctx.command.invoke(sub_ctx))
File “/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/click/core.py”, line 956, in invoke
return ctx.invoke(self.callback, **ctx.params)
File “/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/click/core.py”, line 555, in invoke
return callback(*args, **kwargs)
File “/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/click/decorators.py”, line 17, in new_func
return f(get_current_context(), *args, **kwargs)
File “/home/frappe/frappe-bench/apps/frappe/frappe/commands/init.py”, line 25, in _func
ret = f(frappe._dict(ctx.obj), *args, **kwargs)
File “/home/frappe/frappe-bench/apps/frappe/frappe/commands/site.py”, line 227, in migrate
migrate(context.verbose, rebuild_website=rebuild_website)
File “/home/frappe/frappe-bench/apps/frappe/frappe/migrate.py”, line 38, in migrate
frappe.modules.patch_handler.run_all()
File “/home/frappe/frappe-bench/apps/frappe/frappe/modules/patch_handler.py”, line 29, in run_all
if not run_single(patchmodule = patch):
File “/home/frappe/frappe-bench/apps/frappe/frappe/modules/patch_handler.py”, line 63, in run_single
return execute_patch(patchmodule, method, methodargs)
File “/home/frappe/frappe-bench/apps/frappe/frappe/modules/patch_handler.py”, line 83, in execute_patch
frappe.get_attr(patchmodule.split()[0] + “.execute”)()
File “/home/frappe/frappe-bench/apps/erpnext/erpnext/patches/v11_0/rename_field_max_days_allowed.py”, line 11, in execute
frappe.db.sql_ddl(“”“ALTER table tabLeave Type modify max_days_allowed int(8) NOT NULL”“”)
File “/home/frappe/frappe-bench/apps/frappe/frappe/database.py”, line 276, in sql_ddl
self.sql(query, debug=debug)
File “/home/frappe/frappe-bench/apps/frappe/frappe/database.py”, line 210, in sql
self._cursor.execute(query)
File “/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/cursors.py”, line 170, in execute
result = self._query(query)
File “/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/cursors.py”, line 328, in _query
conn.query(q)
File “/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/connections.py”, line 516, in query
self._affected_rows = self._read_query_result(unbuffered=unbuffered)
File “/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/connections.py”, line 727, in _read_query_result
result.read()
File “/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/connections.py”, line 1066, in read
first_packet = self.connection._read_packet()
File “/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/connections.py”, line 683, in _read_packet
packet.check_error()
File “/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/protocol.py”, line 220, in check_error
err.raise_mysql_exception(self._data)
File “/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/err.py”, line 109, in raise_mysql_exception
raise errorclass(errno, errval)
pymysql.err.InternalError: (1292, u"Truncated incorrect INTEGER value: ‘60 days’")

i am facing new error

Himanshu_Mishra · March 17, 2019, 1:09pm

open bench mariadb.
In there, write

SELECT distinct(max_days_allowed) from `tabLeave Type` where max_days_allowed not REGEXP '^[0-9]+$';

and send the output.

Mohd_Rizal_sharif · March 18, 2019, 6:45am

thanks for helping , its ok now.

shreyasdk · March 20, 2019, 7:54am

after running db command. getting this error

Cannot proceed with update: You have local changes in app “frappe” that are not committed.

Here are your choices:

Merge the frappe app manually with “git pull” / “git pull --rebase” and fix conflicts.
Temporarily remove your changes with “git stash” or discard them completely
with “bench update --reset” or for individual repositries “git reset --hard”
If your changes are helpful for others, send in a pull request via GitHub and
wait for them to be merged in the core.

what should be done to solve this.

Mohd_Rizal_sharif · March 20, 2019, 8:10am

hai @shreyasdk you have commit first merge the app what you have edited. but what make you edit the app ? you have same problem like mine?