DB table fields do not change after successful update (using DB Manager)

[mgaussie]

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

Wappler Version : 3.0.1
Operating System : Mac
NodeJS app.

Expected behavior

I am trying to change a table field from integer to reference. I expect after updating the schema (and a successful change appears) for this field to now be changed.

Actual behavior

The schema appears to update, I have a successful update shown in changes. Yet when I open the table, the changes have reverted back to integer.

How to reproduce

1. Open table
2. click on the field
3. change type from integer to reference
4. choose the right reference table
5. click apply database changes
6. after successful update go back to table to view change - no change has taken place.
7. This has happened on older tables, and also a brand new table.

[mgaussie]

Just attempted this again.

I can add two additional bugs found:

When I first tried to create the table, an intermittent bug I have found is that on occassion when clicking reference it will not find the database. If I add another field then try reference it might then work. As a workaround to store the field, I then have to change it to string. Update the db schema and then go back to the table.

When I then try to change the string to a reference link - it uploads the changes fine, and then when I go back - it’s now changed from string to integer, but no reference.

Postgres db by the way.

[Teodor]

Can you post a few screenshots of what you are doing exactly and what exactly are you trying to change and what’s the result?

[mgaussie]

I don’t know how I can be more expressive?

I am creating a new field in a database table. The above bugs appear when trying to change a field to a reference field.

Two bugs appear:
one, the connected db tables do not appear for me to select a table.
two, if I find the table and try to change ‘type’ string to ‘type’ reference, the change is successful based on the UI/UX feedback, but then when I go back to that table in the DB Manager, it has now changed from ‘type’ string to ‘type’ integer, and not reference.

I actually seem to have been able to solve it, by selecting the table then clicking add new field, instead of having a table field selected then clicking add new (to add it beneath). This worked fine doing it that way, but it failed when trying to add a new field with another field selected.

[Teodor]

It’s still a bit unclear to me what exactly the issue is, but i will try to recreate this locally.

[mgaussie]

Another screen, I just cannot update any field type in the DB to a reference link without errors. So I have to delete the field and create a new one.

[BiBo]

@mgaussie

The schema appears to update, I have a successful update shown in changes. Yet when I open the table, the changes have reverted back to integer.

Not quite.
In my case if I change INT(11) to reference, after update this field set Data Type to INT(10) UNSIGNED.
I think this may help in correcting the error.

Before change a table field from integer to reference:
CREATE TABLE product_feature (
ID int(11) NOT NULL AUTO_INCREMENT,
product_id int(11) DEFAULT NULL,
feature_id int(11) DEFAULT NULL,
PRIMARY KEY (ID)
)
ENGINE = INNODB,
CHARACTER SET utf8,
COLLATE utf8_general_ci;

ALTER TABLE product_feature
ADD CONSTRAINT FK_product_feature_product_id FOREIGN KEY (product_id)
REFERENCES Products (ID) ON DELETE NO ACTION ON UPDATE NO ACTION;

After:
CREATE TABLE product_feature (
ID int(11) NOT NULL AUTO_INCREMENT,
product_id int(11) DEFAULT NULL,
feature_id int(10) UNSIGNED DEFAULT NULL,
PRIMARY KEY (ID)
)
ENGINE = INNODB,
CHARACTER SET utf8,
COLLATE utf8_general_ci;

ALTER TABLE product_feature
ADD CONSTRAINT FK_product_feature_product_id FOREIGN KEY (product_id)
REFERENCES Products (ID) ON DELETE NO ACTION ON UPDATE NO ACTION;

[Fred_K]

Picking up on this topic with a similar error:

I’m trying to reference the userID fk of “consultant” table on Wappler.

I’ve done that already thru phpMyAdmin when I created my DB.

Shall I remove all FK contraints defined on my server and define them via Wappler or can I leave as is and Wappler will understand all the referencing defined on my server?
Thanks all.

[Fred_K]

Been working on this error message all day.
EITHER: I get a success confirmation if I remove the FK contraint from my DB server and add it from Wappler’s DB manager. But then I see no changes applied in the database manager
OR: I get the error message that I cannot update the table.

See screenshot after I apply the changes: it makes the new field as INT(10), UNSIGNED, yet I have added a new reference and referenced the right user table and id field.

Is that a known bug or am I doing something wrong?

[George]

We cannot really support changing the kind of reference columns as to do that all constrains and keys have to be dropped first and remake.

So it is essential to get your key fields to be right initially and not made up your mind later on.

[Teodor]

This has been fixed in Wappler 3.7.5