[Solved] How do I get my v11 database into v12?

bkm · August 21, 2019, 1:06pm

I have a v11 (–production) database that was saved from a server that died.

I have not been able to create a fresh v11 system since changes to the playbooks seem to have broken everything.

I have been able to create a fresh v12 (–production) system on a new server.

I also know that there used to be a process for restoring a previous version database to the next version and running a sequence of commands from the frappe-bench directory to get everything converted.

Is there a working process to do this database conversion from v11 to v12 if I don’t have a valid v11 installation? (Remember I only have production configuration)

If so, can someone enlighten me with the step required or point me to something that might explain how to do this?

Thanks.

BKM

lasalesi · August 21, 2019, 1:32pm

Thanks for sharing. Have you tried downgrading to version-11 branch, then restoring? I would do this with the git checkout version-11 command for frappe and erpnext, this seems more robust in experience than the switch-to-branch option in bench (IMHO).

bkm · August 21, 2019, 1:35pm

Umm… no. I did not know that was an option for a production server. I thought that stuff was only for --develop servers.

How would I do that with my production installation? And then how would I get it back to v12 again?

I was really hoping to find the correct path using the bench --site [my.site.net] migrate command.

BKM

lasalesi · August 21, 2019, 1:39pm

On your fresh installation (in v-12):

$ cd /home/frappe/frappe-bench/apps/frappe
$ git checkout version-11
$ cd ../erpnext
$ git checkout version-11
$ cd ../..
$ bench update
$ bench restore [file] --with-public-files [files] --with-private-files [private files]
$ bench update
$ cd /home/frappe/frappe-bench/apps/frappe
$ git checkout version-12
$ cd ../erpnext
$ git checkout version-12
$ cd ../..
$ bench update

If checkout version-11 is failing, check
$ nano .git/config

And make sure that all remote branches are selected (not only version-12), something like

[remote "upstream"]
    url = https://github.com/frappe/erpnext
    fetch = +refs/heads/*:refs/remotes/upstream/*

Hope this helps.

bkm · August 21, 2019, 1:41pm

Awesome!

Thanks, I will give this a try.

BKM

bkm · August 21, 2019, 2:47pm

Well, once I got everything ready, I started this process and it failed on the git checkout for erpnext version 11. I get this error:

erp_jmi@v12:~/frappe-bench/apps/frappe$ cd ../erpnext
erp_jmi@v12:~/frappe-bench/apps/erpnext$ git checkout version-11
error: pathspec 'version-11' did not match any file(s) known to git.

Is there a different branch name for this?

Any ideas?

BKM

lasalesi · August 21, 2019, 2:50pm

this is what I meant with “if git checkout fails”…

Make sure your git config is not limited to a branch, but should contain

[remote "upstream"]
    url = https://github.com/frappe/erpnext
    fetch = +refs/heads/*:refs/remotes/upstream/*

Then, run

$ git fetch upstream

This should get you all the branches available…

bkm · August 21, 2019, 3:04pm

Ok, thank you.

I had already edited the config file but I was not aware of the need to run the “git fetch upstream” before the “git checkout version-11”

Hopefully everything else completes here without to much interruption. The server is not the fastest thing in the work so it takes a while to do everything.

Will update here later with status.

BKM

lasalesi · August 21, 2019, 3:15pm

Best of luck

bkm · August 21, 2019, 3:19pm

Oh well. It was a valiant try at conversion, but it blew up when trying to execute the “Migrating site site1.local”

I believe this is where it was trying to migrate the fresh v12 site down to v11 using the version-11 branches. I never got to the point of a downgraded v11 system to be able to restore the v11 database.

Here is the failure trace:

yarn install v1.17.3
[1/4] Resolving packages...
[2/4] Fetching packages...
[3/4] Linking dependencies...
warning "rollup-plugin-vue > @vue/component-compiler@3.4.4" has unmet peer dependency "postcss@>=6.0".
[4/4] Building fresh packages...
Done in 103.76s.
Patching sites...
Migrating site1.local
Traceback (most recent call last):
  File "/usr/lib/python3.6/runpy.py", line 193, in _run_module_as_main
    "__main__", mod_spec)
  File "/usr/lib/python3.6/runpy.py", line 85, in _run_code
    exec(code, run_globals)
  File "/home/erp_jmi/frappe-bench/apps/frappe/frappe/utils/bench_helper.py", line 97, in <module>
    main()
  File "/home/erp_jmi/frappe-bench/apps/frappe/frappe/utils/bench_helper.py", line 18, in main
    click.Group(commands=commands)(prog_name='bench')
  File "/home/erp_jmi/frappe-bench/env/lib/python3.6/site-packages/click/core.py", line 764, in __call__
    return self.main(*args, **kwargs)
  File "/home/erp_jmi/frappe-bench/env/lib/python3.6/site-packages/click/core.py", line 717, in main
    rv = self.invoke(ctx)
  File "/home/erp_jmi/frappe-bench/env/lib/python3.6/site-packages/click/core.py", line 1137, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/home/erp_jmi/frappe-bench/env/lib/python3.6/site-packages/click/core.py", line 1137, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/home/erp_jmi/frappe-bench/env/lib/python3.6/site-packages/click/core.py", line 956, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/home/erp_jmi/frappe-bench/env/lib/python3.6/site-packages/click/core.py", line 555, in invoke
    return callback(*args, **kwargs)
  File "/home/erp_jmi/frappe-bench/env/lib/python3.6/site-packages/click/decorators.py", line 17, in new_func
    return f(get_current_context(), *args, **kwargs)
  File "/home/erp_jmi/frappe-bench/apps/frappe/frappe/commands/__init__.py", line 25, in _func
    ret = f(frappe._dict(ctx.obj), *args, **kwargs)
  File "/home/erp_jmi/frappe-bench/apps/frappe/frappe/commands/site.py", line 227, in migrate
    migrate(context.verbose, rebuild_website=rebuild_website)
  File "/home/erp_jmi/frappe-bench/apps/frappe/frappe/migrate.py", line 44, in migrate
    frappe.get_attr(fn)()
  File "/home/erp_jmi/frappe-bench/apps/frappe/frappe/patches/v11_0/sync_user_permission_doctype_before_migrate.py", line 6, in execute
    frappe.reload_doc('core', 'doctype', 'user_permission')
  File "/home/erp_jmi/frappe-bench/apps/frappe/frappe/__init__.py", line 799, in reload_doc
    return frappe.modules.reload_doc(module, dt, dn, force=force, reset_permissions=reset_permissions)
  File "/home/erp_jmi/frappe-bench/apps/frappe/frappe/modules/utils.py", line 160, in reload_doc
    return import_files(module, dt, dn, force=force, reset_permissions=reset_permissions)
  File "/home/erp_jmi/frappe-bench/apps/frappe/frappe/modules/import_file.py", line 26, in import_files
    reset_permissions=reset_permissions)
  File "/home/erp_jmi/frappe-bench/apps/frappe/frappe/modules/import_file.py", line 31, in import_file
    ret = import_file_by_path(path, force, pre_process=pre_process, reset_permissions=reset_permissions)
  File "/home/erp_jmi/frappe-bench/apps/frappe/frappe/modules/import_file.py", line 65, in import_file_by_path
    ignore_version=ignore_version, reset_permissions=reset_permissions)
  File "/home/erp_jmi/frappe-bench/apps/frappe/frappe/modules/import_file.py", line 132, in import_doc
    doc.insert()
  File "/home/erp_jmi/frappe-bench/apps/frappe/frappe/model/document.py", line 241, in insert
    d.db_insert()
  File "/home/erp_jmi/frappe-bench/apps/frappe/frappe/model/base_document.py", line 312, in db_insert
    ), list(d.values()))
  File "/home/erp_jmi/frappe-bench/apps/frappe/frappe/database.py", line 199, in sql
    self._cursor.execute(query, values)
  File "/home/erp_jmi/frappe-bench/env/lib/python3.6/site-packages/pymysql/cursors.py", line 170, in execute
    result = self._query(query)
  File "/home/erp_jmi/frappe-bench/env/lib/python3.6/site-packages/pymysql/cursors.py", line 328, in _query
    conn.query(q)
  File "/home/erp_jmi/frappe-bench/env/lib/python3.6/site-packages/pymysql/connections.py", line 517, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/home/erp_jmi/frappe-bench/env/lib/python3.6/site-packages/pymysql/connections.py", line 732, in _read_query_result
    result.read()
  File "/home/erp_jmi/frappe-bench/env/lib/python3.6/site-packages/pymysql/connections.py", line 1075, in read
    first_packet = self.connection._read_packet()
  File "/home/erp_jmi/frappe-bench/env/lib/python3.6/site-packages/pymysql/connections.py", line 684, in _read_packet
    packet.check_error()
  File "/home/erp_jmi/frappe-bench/env/lib/python3.6/site-packages/pymysql/protocol.py", line 220, in check_error
    err.raise_mysql_exception(self._data)
  File "/home/erp_jmi/frappe-bench/env/lib/python3.6/site-packages/pymysql/err.py", line 109, in raise_mysql_exception
    raise errorclass(errno, errval)
pymysql.err.IntegrityError: (1048, "Column 'show_preview_popup' cannot be null")
erp_jmi@v12:~/frappe-bench$

I am not able to decipher the source of the underlying problem, but it looks like something has changed in v12 that it can no longer be downgraded to v11 (just my guess).

Hmm… once it is broken like this you cannot get it back either. Going to have to strip the server and start over.

BKM

igrekus · August 21, 2019, 3:31pm

I had to sync the db back and forth between my dev v12 server and my production v11 server. Simple bench --site site backup and bench --site site --force restore <filename> worked seamlessly. This may be too hardcore, but at least it worked on both ends

brian_pond · August 21, 2019, 3:33pm

"Column 'show_preview_popup' cannot be null".

Definitely a database schema problem. That column is configured to forbid nulls. But looks like it doesn’t have a default value.

bkm · August 21, 2019, 3:33pm

I get the idea of backup and restoring of the database between versions but what steps did you take between the backup and restore operations to get the database upgraded or downgraded after each restore session?

BKM

bkm · August 21, 2019, 3:36pm

That was kinda my guess as well. I installed a fresh v12 site and ran the setup wizard to get a valid installation completed.

Then I started this process to downgrade the v12 system to v11 so I could restore my v11 database and try to upgrade it.

@lasalesi has bee very gracious in helping me to get this far, but I am afraid there my not be a path around this.

BKM

igrekus · August 21, 2019, 3:37pm

Nothing special. v11 and v12 should have more or less consistent schemas to ensure smooth migration. However, I can’t vouch for the process to not break something, all I can say, my custom module with custom tables, HR module, Users, Permissions, Task and Project modules, didn’t break.

bkm · August 21, 2019, 3:38pm

I wish I had your rabbits foot or lucky horse shoe to help me like that.

Thanks for the tip.

BKM

AnthonyS · August 21, 2019, 5:27pm

I’m not quite sure what do you mean by changes to the playbooks broke everything if you can explain more please!

brian_pond · August 21, 2019, 5:53pm

I’m not quite sure what do you mean by changes to the playbooks broke everything if you can explain more please!

One of the (known) challenges we face is maintaining stable, long-term versions.

We have git branches and tags for Frappe version 10, 11, 12, etc.
We have git branches and tags for ERPNext version 10, 11, 12, etc.

However, that is not enough.

For example, Bench does not follow these versions. There are just 2 active git branches for Bench: “master” and “develop”. Bench does not have version 11, 12, etc. And it is the Bench repository, not Frappe or ERPNext, that has the Ansible Playbooks.

So. How do we know what the playbooks looked like previously in version 11, or version 10? We don’t. Maybe the code was the same. Maybe not.

Next problem: dependencies. Frappe and ERPNext have a ton of external, third party dependencies. Not just big dependencies like MariaDB, Redis, Node, or Python. But dozens and dozens of little dependencies on various Python and JavaScript libraries. If any of those change? They can easily break previous Frappe/ERPNext versions.

Sometimes, we target a specific external dependency in requirements.txt or package.json. But many, many times…we do not. And so you never know what version will download from GitHub, PyPi, Node, etc.

Example. Assume today, I install ERPNext version 10.x and 11.x. Later, 30 days from now, @bkm also installs ERPNext 10x and 11.x. We could get very different success and failures. Depending on what happened to those 3rd party dependencies during those 30 days.

Finally, there are SQL database challenges. Our current tools were not designed to Upgrade/Downgrade the database schemas, at-will, properly creating and dropping tables and columns, filling-in default values, and cleanly migrating data. You can (usually) upgrade without too many problems. But going backwards isn’t always easy. Especially if you’ve made any kind of modifications to your SQL schema.

Bottom line: Individual Results May Vary. It is extremely difficult for 2 different humans, on 2 different platforms, at 2 different Date/Times, to get identical installation/upgrade results.

AnthonyS · August 21, 2019, 6:09pm

thank you for your explanation ! was a great help understanding the mean of playbooks and much more

bkm · August 22, 2019, 12:54am

Ok, So I finally found a working path to upgrade my v11 database to v12.

It was much simpler than I had expected.

All I did was:

force restore the v11 database (with files) into the v12 clean system
ran “bench migrate”
After the successful migration, I replaced the encryption key in site_config.json with the one from the original v11 site

It was that simple. The downgrading and upgrading path had far too many errors and ultimately would not work at all.

Hope this helps someone else.

As always… Your mileage may vary…

BKM