Could not find view q_..., query caches not being created

[elhardoum]

I can't get it to work on docker containers running on local machine (MacOS intel chip). So far, the MySQL database snapshot works but queries aren't cached, I had gotten a variety of errors, but the most common ones are about views not found.

Here's my setup:

# docker-compose.yml
version: '3'
services:
  mysql:
    image: mysql:5.7.22
    environment:
      - MYSQL_DATABASE=${MYSQL_DATABASE}
      - MYSQL_ROOT_PASSWORD=${MYSQL_ROOT_PASSWORD}
      - MYSQL_USER=${MYSQL_USER}
      - MYSQL_PASSWORD=${MYSQL_PASSWORD}
    volumes:
      - ./data/db/mysql:/var/lib/mysql
    env_file: .env
    command: 'mysqld --log-bin --binlog-format=ROW --server-id=1'

  readyset:
    image: public.ecr.aws/readyset/readyset:beta-2022-12-15
    ports:
      - 5433:5433
      - 3307:3307
    platform: linux/amd64
    volumes:
      - ./data/readyset:/state
    environment:
      - DEPLOYMENT_ENV=quickstart_github
    env_file: .env
    depends_on:
      - mysql
    entrypoint: >
      sh -c "readyset \
          --prometheus-metrics \
          --standalone \
          --deployment=github-mysql \
          --database-type=mysql \
          --upstream-db-url=mysql://$${MYSQL_ROOT_USER}:$${MYSQL_ROOT_PASSWORD}@mysql:3306/$${MYSQL_DATABASE} \
          --address=0.0.0.0:5433 \
          --username=$${MYSQL_ROOT_USER} \
          --password=$${MYSQL_ROOT_PASSWORD} \
          --query-caching=explicit \
          --db-dir=/state
        "

Do I have to define the query caching patterns somewhere? Swapping the query-caching flag options from readyset -h doesn't work.

Some common error messages:

readyset_1   | 2023-01-28T17:05:02.356464Z ERROR prepare_select{statement_id=1 create_if_not_exist=false override_schema_search_path=None}: readyset_adapter::backend::noria_connector: getting view from noria failed context=LogContext({"deployment": "github-mysql"}) error=Could not find view `q_448e8f3d42afad54`
readyset_1   | 2023-01-28T17:05:02.358047Z  WARN readyset_adapter::backend: View not found during mirror_prepare() context=LogContext({"deployment": "github-mysql"}) error=Could not find view `q_448e8f3d42afad54`

[alanamarzoev]

Hey @elhardoum thanks for opening an issue!

Yes, assuming you're running ReadySet in explicit caching mode, you have to explicitly cache queries. You can see info on how to do this here: caching docs

If you're in implicit mode, ReadySet will cache all queries that are supported.

Could you check which mode you're using by doing (it's the query-caching flag)?

It would also be helpful to see the output of SHOW PROXIED QUERIES and SHOW CACHED QUERIES as well as the full error logs.

[elhardoum]

Sorry, this is not a bug, when I posted this I couldn't run those custom SQL commands on either MySQL shell (same for now). Not much luck with the documentation.

[jbensal]

@elhardoum

Are there any other errors in the logs that you're seeing? If you see Snapshotting finished in the logs after running docker logs readyset | grep 'Snapshotting finished', that means ReadySet is up and running successfully. Another thing to try is bumping your MySQL version up to 8.0.

I'd like to help you get this working! I'm happy to help you troubleshoot this and see where we can improve the documentation.

[aarjan]

@alanamarzoev I tried to run similar code as OP in docker swarm, it is not working. I am getting this error,

nknown-linux-gnu, rustc_version: rustc 1.64.0-nightly (fe3342816 2022-08-01), profile: release, opt_level: 3}
pn_readyset.1.yi44l6c5i734@ip-172-31-26-247    | 2023-02-10T08:38:51.891786Z ERROR authority:create:exec:execute:send: rustify::clients::reqwest: error=Error sending HTTP request
pn_readyset.1.yi44l6c5i734@ip-172-31-26-247    | 2023-02-10T08:38:51.891927Z ERROR authority:create:exec:execute: rustify::client: error=Error sending HTTP request
pn_readyset.1.yi44l6c5i734@ip-172-31-26-247    | 2023-02-10T08:38:51.891977Z ERROR authority:create:exec: rustify::endpoint: error=Error sending HTTP request
pn_readyset.1.yi44l6c5i734@ip-172-31-26-247    | 2023-02-10T08:38:51.892060Z ERROR authority:create: consulrs::session: error=An error occurred with the request
pn_readyset.1.yi44l6c5i734@ip-172-31-26-247    | 2023-02-10T08:38:51.892266Z ERROR readyset_server::startup: Controller failed context=LogContext({"deployment": "quickstart_docker"}) error=Internal invariant violated: controller's authority thread failed: Error issuing request An error occurred with the request

This is the docker config,

version: "3.3"
services:
  mysql:
    image: mysql:5.7
    ports:
      - "3307:3307"
    networks:
      - backend
    volumes:
      - ./data/sqldata:/var/lib/mysql
    command: mysqld --default-authentication-plugin=mysql_native_password
    environment:
       MYSQL_ROOT_PASSWORD: pn_pass
       MYSQL_DATABASE: pn
    deploy:
      mode: replicated
      replicas: 1
      placement:
        constraints:
          - node.role == manager
    logging: &logging
      options:
        max-size: "10m"
        max-file: "3"

  readyset:
    image: public.ecr.aws/readyset/readyset:beta-2023-01-18
    ports:
      - 5433:5433
      - 3308:3308
    depends_on:
      - mysql
    networks:
      - backend
    volumes:
      - ./data/readyset:/state
    environment:
      DB_DIR: /state
      ALLOWED_USERNAME: root
      ALLOWED_PASSWORD: pn_pass
      DATABASE_TYPE: mysql
      DEPLOYMENT: quickstart_docker
      UPSTREAM_DB_URL: mysql://root:pn_pass@mysql:3307/neuroflip_copy
      RS_API_KEY: public.ecr.aws/readyset/readyset:beta-2023-01-18
      # LISTEN_ADDRESS: 0.0.0.0:5433
    logging:
      <<: *logging

networks:
  backend:
    driver: overlay
    ```

[alanamarzoev]

Hey @aarjan, could you try adding STANDALONE: “1" under environment and let me know if that fixes the problem?

[jseldess]

Also, @aarjan, I'd suggest tweaking a few other env variables for readyset. For example, your RS_API_KEY variable is referencing the docker image. You don't really need that variable for testing. It's mostly to help readyset with telemetry. And you should probably set caching to "explicit" mode, which means you decide which queries to cache rather than having readyset cache every query it supports.

Here's what I would recommend for the readyset environment variables:

    environment:
      STANDALONE: "1"
      DEPLOYMENT: "quickstart_docker"
      DATABASE_TYPE: mysql
      UPSTREAM_DB_URL: "mysql://root:pn_pass@mysql:3307/neuroflip_copy"
      ALLOWED_USERNAME: root
      ALLOWED_PASSWORD: pn_pass
      LISTEN_ADDRESS: "0.0.0.0:5433"
      QUERY_CACHING: explicit
      DB_DIR: "/state"

Also, if you use readyset:latest instead of readyset:beta-2023-01-18, we've changed it so that the readyset user and password default to the username/password for the db in UPSTREAM_DB_URL, so if you just want to reuse those credentials for readyset (and it looks like you do), you can leave out ALLOWED_USERNAME and ALLOWED_PASSWORD.

All of these CLI options are now in our docs at https://docs.readyset.io/reference/cli/readyset/, in case that helps. We're also working on adding a docker compose file to the docs.

[aarjan]

Thank you @alanamarzoev, it worked as a charm. I was try so many other combination but couldn't figure it out.

Thank you @jseldess for your suggestions. That docker-compose file was a major missing in the docs. I had to browse through every github issue to get answers.

[jseldess]

Will get those docs online asap, hopefully by the end of today.