Queries search is not working on release/7.0.x

[veerkat]

Issue Summary

I try building docker image with the redash dockerfile and start by docker-compose. But queries search is not working

Steps to Reproduce

Check out the queries table and find that search_vector is null.

Technical details:

• Redash Version: release/7.0.x
• Browser/OS: Chrome Mac
• How did you install Redash: redash setup

[arikfr]

How did you build the database?

[veerkat]

How did you build the database?

use redash/setup/docker-compose.yml

postgres:
  image: postgres:9.5.6-alpine
  env_file: /opt/redash/env
  volumes:
    - /opt/redash/postgres-data:/var/lib/postgresql/data
  restart: always

[arikfr]

Yes, but how did you create the database tables?

[veerkat]

Yes, but how did you create the database tables?

Replace the original image with the built image in redash/setup/docker-compose.yml,
and run in redash/setup

docker-compose -f docker-compose.yml -p redash run --rm server create_db

[arikfr]

Just to make sure: this is a new install not an upgrade, correct?

[veerkat]

Just to make sure: this is a new install not an upgrade, correct?

Yes, just to test the new version

[arikfr]

Thanks. I managed to confirm this :-(

@jezdez maybe we need to setup something for the trigger to be created when creating DB from scratch (using the create_db command)?

[jezdez]

Ah, right, we simply have to run the migrations in the create_tables command so this gets picked up. I don't think there is a better way to cover ongoing schema changes than the migration system, it's built for that exact purpose. I don't recall why create_tables was written like this to be honest.

[arikfr]

Because from my experience migrations don't age well. Usually over time they tend to break.

When creating a database from scratch the ORM usually does a better job by creating the tables from their definitions than trying to mutate the DB over all the different versions we ever had.

[jezdez]

Yeah, I've had that experience as well, especially when migrations are leaky and codify subtle data model structure or use specific code paths. I don't have enough experience with it to say if that's the case for Alembic though.

One thing we could consider is resetting the migrations to reduce the risk of decay, basically squash them to just a few migration files. Alternatively we could create a command to hook up the search update trigger manually if needed.

[arikfr]

Fixed in #3634. Soon a new Docker image will be built.

@veerkat thank you for reporting this!

[veerkat]

Yes, it's fixed!

[bogeit]

Yes, but how did you create the database tables?

Replace the original image with the built image in redash/setup/docker-compose.yml,
and run in redash/setup

docker-compose -f docker-compose.yml -p redash run --rm server create_db

How do I build the database for postgres db when I use kubernetes?

postgres was error :
ERROR: relation "queries" does not exist at character 979
......

---

I have found a solution myself, as follows:

kubectl -n redash exec -it server-c86dd7579-knnn5 -- /app/bin/docker-entrypoint create_db

[2019-03-29 15:09:22,520][PID:630][INFO][root] Generating grammar tables from /usr/lib/python2.7/lib2to3/Grammar.txt
[2019-03-29 15:09:22,538][PID:630][INFO][root] Generating grammar tables from /usr/lib/python2.7/lib2to3/PatternGrammar.txt
[2019-03-29 15:09:27,072][PID:630][INFO][alembic.runtime.migration] Context impl PostgresqlImpl.
[2019-03-29 15:09:27,073][PID:630][INFO][alembic.runtime.migration] Will assume transactional DDL.
[2019-03-29 15:09:27,134][PID:630][INFO][alembic.runtime.migration] Running stamp_revision -> e5c7a4e2df4d