Migrate after restore fails

ERPNext
,
1

I’m trying to build up a development environment.
To achieve this, I followed this tutorial: [Tutorial] ERPNext-15 Setup | Docker

Because I want to work on some real data, I made a backup of my current production environment and restored it.

Without the restore, everything works fine.
After restoring the backup, the second bench migrate fails. (The restore itself runs without an error).

Here is the error message:

bench migrate
Migrating frontend
Updating DocTypes for frappe        : [========================================] 100%
Updating DocTypes for erpnext       : [========                                ] 20%
Failed to alter schema using query: ALTER TABLE `tabPayment Entry` MODIFY  
`paid_amount_after_tax` decimal(21,9) not null default 0, MODIFY   
`source_exchange_rate` decimal(21,9) not null default 0, MODIFY   
`base_total_allocated_amount` decimal(21,9) not null default 0, MODIFY 
`unallocated_amount` decimal(21,9) not null default 0, MODIFY `difference_amount` 
decimal(21,9) not null default 0, MODIFY `base_total_taxes_and_charges` 
decimal(21,9) not null default 0, MODIFY `total_taxes_and_charges` decimal(21,9) not 
null default 0, MODIFY `reference_no` varchar(140) default '0', MODIFY 
`reference_date` date default '1.1.2000', MODIFY `paid_to_account_balance` 
decimal(21,9) not null default 0, MODIFY `base_received_amount` decimal(21,9) not 
null default 0, MODIFY `base_paid_amount_after_tax` decimal(21,9) not null default 0, 
MODIFY `paid_amount` decimal(21,9) not null default 0, MODIFY 
`paid_from_account_balance` decimal(21,9) not null default 0, MODIFY 
`received_amount_after_tax` decimal(21,9) not null default 0, MODIFY 
`base_paid_amount` decimal(21,9) not null default 0, MODIFY 
`total_allocated_amount` decimal(21,9) not null default 0, MODIFY `received_amount` 
decimal(21,9) not null default 0, MODIFY `party_balance` decimal(21,9) not null 
default 0, MODIFY `target_exchange_rate` decimal(21,9) not null default 0, MODIFY 
`base_received_amount_after_tax` decimal(21,9) not null default 0


There was an issue while migrating the DocType: Payment Entry


Queued rebuilding of search index for frontend

Traceback (most recent call last):
  File "/home/frappe/.pyenv/versions/3.10.13/lib/python3.10/runpy.py", line 196, in _run_module_as_main
    return _run_code(code, main_globals, None,
  File "/home/frappe/.pyenv/versions/3.10.13/lib/python3.10/runpy.py", line 86, in _run_code
    exec(code, run_globals)
  File "/workspace/development/frappe-bench/apps/frappe/frappe/utils/bench_helper.py", line 114, in <module>
    main()
  File "/workspace/development/frappe-bench/apps/frappe/frappe/utils/bench_helper.py", line 20, in main
    click.Group(commands=commands)(prog_name="bench")
  File "/workspace/development/frappe-bench/env/lib/python3.10/site-packages/click/core.py", line 1442, in __call__
    return self.main(*args, **kwargs)
  File "/workspace/development/frappe-bench/env/lib/python3.10/site-packages/click/core.py", line 1363, in main
    rv = self.invoke(ctx)
  File "/workspace/development/frappe-bench/env/lib/python3.10/site-packages/click/core.py", line 1830, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/workspace/development/frappe-bench/env/lib/python3.10/site-packages/click/core.py", line 1830, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/workspace/development/frappe-bench/env/lib/python3.10/site-packages/click/core.py", line 1226, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/workspace/development/frappe-bench/env/lib/python3.10/site-packages/click/core.py", line 794, in invoke
    return callback(*args, **kwargs)
  File "/workspace/development/frappe-bench/env/lib/python3.10/site-packages/click/decorators.py", line 34, in new_func
    return f(get_current_context(), *args, **kwargs)
  File "/workspace/development/frappe-bench/apps/frappe/frappe/commands/__init__.py", line 29, in _func
    ret = f(frappe._dict(ctx.obj), *args, **kwargs)
  File "/workspace/development/frappe-bench/apps/frappe/frappe/commands/site.py", line 681, in migrate
    SiteMigration(
  File "/workspace/development/frappe-bench/apps/frappe/frappe/migrate.py", line 193, in run
    self.run_schema_updates()
  File "/workspace/development/frappe-bench/apps/frappe/frappe/migrate.py", line 54, in wrapper
    raise e
  File "/workspace/development/frappe-bench/apps/frappe/frappe/migrate.py", line 46, in wrapper
    ret = method(*args, **kwargs)
  File "/workspace/development/frappe-bench/apps/frappe/frappe/migrate.py", line 123, in run_schema_updates
    frappe.model.sync.sync_all()
  File "/workspace/development/frappe-bench/apps/frappe/frappe/model/sync.py", line 43, in sync_all
    sync_for(app, force, reset_permissions=reset_permissions)
  File "/workspace/development/frappe-bench/apps/frappe/frappe/model/sync.py", line 111, in sync_for
    import_file_by_path(
  File "/workspace/development/frappe-bench/apps/frappe/frappe/modules/import_file.py", line 146, in import_file_by_path
    import_doc(
  File "/workspace/development/frappe-bench/apps/frappe/frappe/modules/import_file.py", line 239, in import_doc
    doc.insert()
  File "/workspace/development/frappe-bench/apps/frappe/frappe/model/document.py", line 334, in insert
    self.run_post_save_methods()
  File "/workspace/development/frappe-bench/apps/frappe/frappe/model/document.py", line 1173, in run_post_save_methods
    self.run_method("on_update")
  File "/workspace/development/frappe-bench/apps/frappe/frappe/model/document.py", line 1007, in run_method
    out = Document.hook(fn)(self, *args, **kwargs)
  File "/workspace/development/frappe-bench/apps/frappe/frappe/model/document.py", line 1367, in composer
    return composed(self, method, *args, **kwargs)
  File "/workspace/development/frappe-bench/apps/frappe/frappe/model/document.py", line 1349, in runner
    add_to_return_value(self, fn(self, *args, **kwargs))
  File "/workspace/development/frappe-bench/apps/frappe/frappe/model/document.py", line 1004, in fn
    return method_object(*args, **kwargs)
  File "/workspace/development/frappe-bench/apps/frappe/frappe/core/doctype/doctype/doctype.py", line 515, in on_update
    raise e
  File "/workspace/development/frappe-bench/apps/frappe/frappe/core/doctype/doctype/doctype.py", line 512, in on_update
    frappe.db.updatedb(self.name, Meta(self))
  File "/workspace/development/frappe-bench/apps/frappe/frappe/database/mariadb/database.py", line 470, in updatedb
    db_table.sync()
  File "/workspace/development/frappe-bench/apps/frappe/frappe/database/schema.py", line 46, in sync
    self.alter()
  File "/workspace/development/frappe-bench/apps/frappe/frappe/database/mariadb/schema.py", line 111, in alter
    frappe.db.sql_ddl(query)
  File "/workspace/development/frappe-bench/apps/frappe/frappe/database/database.py", line 412, in sql_ddl
    self.sql(query, debug=debug)
  File "/workspace/development/frappe-bench/apps/frappe/frappe/database/database.py", line 230, in sql
    self._cursor.execute(query, values)
  File "/workspace/development/frappe-bench/env/lib/python3.10/site-packages/pymysql/cursors.py", line 153, in execute
    result = self._query(query)
  File "/workspace/development/frappe-bench/env/lib/python3.10/site-packages/pymysql/cursors.py", line 322, in _query
    conn.query(q)
  File "/workspace/development/frappe-bench/env/lib/python3.10/site-packages/pymysql/connections.py", line 563, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/workspace/development/frappe-bench/env/lib/python3.10/site-packages/pymysql/connections.py", line 825, in _read_query_result
    result.read()
  File "/workspace/development/frappe-bench/env/lib/python3.10/site-packages/pymysql/connections.py", line 1199, in read
    first_packet = self.connection._read_packet()
  File "/workspace/development/frappe-bench/env/lib/python3.10/site-packages/pymysql/connections.py", line 775, in _read_packet
    packet.raise_for_error()
  File "/workspace/development/frappe-bench/env/lib/python3.10/site-packages/pymysql/protocol.py", line 219, in raise_for_error
    err.raise_mysql_exception(self._data)
  File "/workspace/development/frappe-bench/env/lib/python3.10/site-packages/pymysql/err.py", line 150, in raise_mysql_exception
    raise errorclass(errno, errval)
pymysql.err.OperationalError: (1067, "Invalid default value for 'reference_date'")

Here is my script to create the devcontainer:

#!/bin/bash -i

nvm install v18
nvm use v18
npm -g install yarn
PYENV_VERSION=3.10.13 bench init --skip-redis-config-generation --frappe-branch version-15 frappe-bench
cd frappe-bench 

bench set-config -g db_host mariadb
bench set-config -g redis_cache redis://redis-cache:6379
bench set-config -g redis_queue redis://redis-queue:6379
bench set-config -g redis_socketio redis://redis-queue:6379

bench new-site --mariadb-root-password 123 --admin-password admin --no-mariadb-socket frontend
bench use frontend

bench --site frontend set-config developer_mode 1
bench --site frontend clear-cache

bench get-app --branch version-15 erpnext
bench --site frontend install-app erpnext

bench get-app --branch version-15 hrms
bench --site frontend install-app hrms

bench get-app --branch version-15 https://github.com/frappe/payments.git
bench --site frontend install-app payments

bench get-app https://github.com/frappe/wiki
bench --site frontend install-app wiki

bench get-app --branch version-15 https://github.com/alyf-de/erpnext_germany.git
bench --site frontend install-app erpnext_germany

bench build
bench migrate
#bench start 

####################################
## Until here, everything is fine ##
####################################

bench --site frontend restore --db-root-password 123 /workspace/development/backup/backup.sql.gz --with-private-files /workspace/development/backup/private-files.tar --with-public-files /workspace/development/backup/frontend-files.tar

bench build
bench migrate
bench start

I’m working on that since two days now and I’m running out of ideas.
I couldn’t find any hint, where the wrong default date could come from.

Any ideas?

2

Did you check into your table tabPayment Entry is there is non-valid date in column reference_date (like NULL or 0000-00-00) ?

3

There are no null entries in that column.
I just saw that I pasted the wrong error message. I updated my post.
The point is that it seems not to be caused by an existing entry, but instead there is a faulty migration

Failed to alter schema using query: ALTER TABLE `tabPayment Entry` [...] MODIFY `reference_date` date default '1.1.2000'
4

In this case, it’s seems that it’s a property setters on this field that you have to remove before launch the upgrade.
Or this is a customization done by one of your custom app ?
Or you’ve edit DocType Payment Entry directly without use customization process ?
May be date format on your old server allow this format and now it’s not the case.

5

I can’t remember that I ever customized erpnext.
The custom app is my first try to do so.

Do youhave some hints to hunt for the source of evil?
The error message is not very helpful.