Clone (update latest changes) in new remote DB in DB Manager on new remote Docker

======== TEMPLATE BUG FORM ========

Wappler Version : 3.0.2
Operating System : Mac
NODEJS Project

Expected behavior

I have connected to a remote docker, and I want to apply changes to the db in the new target to rebuild the entire schema in the new remote docker db.

I even created a new DB outside of docker (digital ocean) connected to this and tried to do the same.

Actual behavior

I get numerous errors stating certain tables don’t exist. I’m optimistic I’m just doing something wrong, but whatever I try - I just get a error stating a table doesn’t exist (but it does exist in the correct db schema on local docker environment.

How to reproduce

Create local docker
create database
build database schema, data and so on (and back up to docker)
create new docker remote via digitalocean docker machine setup (chose NY as San Francisco was defaulting to LON region).
Go to DB Manager, click on the same DB - connect
Go to changes - click ‘apply latest changes’

Then the errors start rolling.

I note that you can backup the entire DB plus data which I do, but I’m not sure how I could use this.

@George @Patrick - appreciate any help here, due to this I can’t push app into a production state as I can’t clone the DB schema. if there is a way of copying an actual file from the directory, or another work around I’m happy to do it.

If you just change the target to the remote with blank database you should be able to just apply all the changes.

What errors do you get exactly?

Errors such as ‘name of table’ doesn’t exist etc… similar to an answer about 1 month ago, you recommended deleting all changes as it was likely some of the change files had been lost or deleted. I did this, but the problem is, then there is nothing to apply, so it didn’t work.

As I’m now working within Docker for this project (project 2) - I saved the database, and tried to rebuild - all seemed to work OK - but I’m getting this well known error now when trying to login to the rebuilt remote docker containerised app:
code: “ER_NOT_SUPPORTED_AUTH_MODE” it seems it’s something to do with the password version.

This has all been setup natively within Wappler, however - so I’ve not done anything myself!

Ooh that is the root of your problem, you have deleted all the previous changes and are just applying a few new ones.

I actually never said that you should delete any changes, but only if you have accidentally done so you can reset the history.

You really need to have all the changes to reapply them to your new database. Having just last few won’t work as you will be missing lots of tables etc.

This wasn’t the root of the problem, I didn’t delete any changes at all - and I kept getting errors, regardless of what I tried - so then I followed your advice in the previous post and just removed the changes, as it wasn’t working anyway.

I think it can be resolved with the docker db backup, but the auth issue above is holding that back.

Hi @george,
As Wappler queries the DB schema for tables/views etc. would it be possible to have a clone function that creates the same structure to a new target even if it hasn’t been built from scratch in DB Manager?

2 Likes

Well if you have all the changes from the beginning it should really work. Everything is kept.

Otherwise you can do a docker db backup ( from the toolbar) and copy the generated sql from the .wappler/targets/your_dev_target/init_db

To
.wappler/targets/your_live_target/init_db

And then clean and redeploy the live servers

Maybe we can find a better way to do full db structure synchronization in the future, when not all changes are available and you just want exactly the same structure to deploy. Any advise @JonL?

2 Likes

Yes that is exactly what I’m thinking about. The problem is that if the database have already some of the structure and data, a complex synchronization is required.

If it was an empty db - then it is easy indeed.

1 Like

In theory, if I ‘Save Database Structure and Data’ from the Wappler toolbar, then go to the live target, and rebuild - it should capture that db structure and data right? From within Wappler?

The issue I’m having - I can login locally to the app via the Docker local server. But I cannot login with the remote, I keep getting the above error.

You already create a JSON for tables and their columns in the ‘meta’ options in Connections php file. Could you not iterate through that to create the same structure?

It would need to identify which are views and then which views depend on other views etc. Also which may have FK dependencies on other tables so they were created in order.

Other than exporting/importing a dump file through the db engine internal tools? That is what I would do.

This is kind of a very specific scenario that shouldn’t happen too much, right? Or at least it is the first topic I see about it.

I don’t see personally much added value in adding this to Wappler and will probably make people break their db more than fixing it and it will render the migration files useless.

I mean this is fixable in its current state. Export a dump and import a dump. It involves two terminal commands only.

1 Like

Cloning the empty structure, would be a tremendous start. For me it would be as good (or possibly better) as being able to copy the data too.

I’ve looked in the db_init for the local, and it just has the generic .sql files for the docker container (cars.sql, countries.sql etc.) there is nothing in there relating to my DB that I’be built with the DB Manager.

To backup the full db structure and data from the local docker dev database and reapply it on the live remote docker follow the following steps:

WARNING: this should be only performed on empty new Live target!! Otherwise you will loose your existing live database.

  1. Switch to your local docker target and click on “Save Database Data and Structure” icon

image

  1. Open your project folder in Finden/Windows Explorer and copy the file db_save.sql from the Local Docker folder db_init to your Live Docker target folder db_init - make sure there are no other sql files in it!

image

  1. Switch to the Live target (1), shudown the Services if running (2) and then launch them (3) - this will build the database with the backup data and structure you saved from the local dev

image

6 Likes

I think this would be very useful.

1 Like

I can see this being hugeley beneficial @George - this would allow for the entire db structure to be setup on dev, work with it - then when ready, simply clone the db structure to staging and production - as it’s typical to work with different data in those environments, so cloning the data isn’t so much of a requirement for me.

You guys know that you can do that already right? No need to wait for Wappler to implement it.

For mysql you can just use mysqldump command or the UI of the workbench app.

https://dev.mysql.com/doc/workbench/en/wb-admin-export-import-management.html

And for PostgreSQL you can use pg_dump command or a UI like pgAdmin.

Or use the docker method I mentioned above:

1 Like