Just today a problem arose and it's stopping my work.
When I add a new column to a table it doesn't appear in the "Scheme" section associated with the Code section.
I've run the "Refresh" countless times.
I've checked on the server and when I Refresh, I can see the query Retool sends to the server. (It queries table "information_schema.columns" and information_schema.tables)
I've copied that query and run it myself, both directly on the server and via a new retool query, and confirmed that the new column is present in the table and in the query result.
If I use the new column in a query in Retool, then the query succeeds with no trouble, so Retool can clearly access the column.
But when I try to write to the new column using the GUI section "Update an existing record", I can't update the Change set because it only accepts column names it finds in the Schema.
I created a new "Page" and set up a simple query and table of results, and on that page the Schema does show the new column in the table. But it seems each page must have it's own copy of the Schema, because on all my other (existing) pages the new columns never appear.
I tested removing a column from a table, and the Scheme doesn't show that change either. The deleted column remains in the Schema even after multiple tries to "Refresh".
EDIT: I found by chance that it's somehow related to the use of dynamic resources. When I switched a query from dynamic resource selection to a fixed one (from drop-down list) then suddenly the Schema shown when that query is in the editor is properly updated.
After more testing all seems to be back to normal and the Schemas are updating even though all queries are using Dynamic resource selection.
I did find one query that was using a fixed resource different from the one the Dynamic selection is currently pointing to. So possible the mix of resources confused the Schemas?
Ah very interesting - thanks for providing an update, @davblo! It wouldn't have occurred to me that queries with a dynamic resource might have trouble fetching an updated schema. I'll do some digging into why this might have happened and file a bug report internally.
I created a new query, set to use one of my mysql databases via a dynamic Recource ID.
The Schema was all fine to start with.
I used the query to add a new column to a table (I wrote in the query as SQL... "ALTER TABLE table-name ADD new-column-name; and used "Preview" which (unexpectedly) does send it to the database.
Then I changed the SQL to just "SELECT * FROM same-table and clicked Preview again.
The table was read properly and the new column was present, filed with default nulls.
I checked the Schema and the new column was not shown. I clicked the Refresh icon, but still no new column.
So then I changed the Resource to the fixed setting for the same database, and the new column appeared in the Schema.
After that I switched back to the dynamic resource setting (but still the same database) and the new column remained in view in the Schema.
Hey @davblo - I've tried replicating this on the current Cloud release but haven't had any luck. When using a query configured with a dynamic resource, all the changes I make to the database schema are reflected within Retool as soon as I hit the "Refresh" button.
Are you on a Cloud or On-Prem instance? And do you know if your query is configured to use any caching? You mentioned earlier having looked at the network request that fires off whenever you hit the "Refresh "button - did you confirm that the response shows a stale version of the schema?
I'll do some digging into why this might have happened and file a bug report internally.