search

clear-code / redmine_full_text_search

Full text search for Redmine
MIT License
61 stars 24 forks source link

500:Internal Error happened #156

Open asyahana opened 3 weeks ago

asyahana commented 3 weeks ago

Hello.

I applied the plugin but an error occurred. Is it possible that the version of the database docker image I specified is incorrect? I would like to know how to solve this problem. Thank you in advance.

image

-----Error Log-----

plugins/full_text_search/lib/full_text_search/mroonga.rb:9:in `select'
plugins/full_text_search/lib/full_text_search/searcher.rb:75:in `search'
plugins/full_text_search/lib/full_text_search/hooks/controller_search_index.rb:29:in `index'
lib/redmine/sudo_mode.rb:61:in `sudo_mode'
Started GET "/search?utf8=%E2%9C%93&scope=&q=aaa" for 127.0.0.1 at 2024-06-13 16:47:41 +0900
Processing by SearchController#index as HTML
  Parameters: {"utf8"=>"✓", "scope"=>"", "q"=>"aaa"}
  Current user: adsyahana (id=200)
Completed 500 Internal Server Error in 47ms (ActiveRecord: 33.2ms)

ActiveRecord::StatementInvalid (Mysql2::Error: Incorrect parameters in the call to stored function `mroonga_command`: SELECT mroonga_command('select', 'match_columns', 'title * 100 || scorer_tf_at_most(content, 5)', 'query', mroonga_query_expand('fts_query_expansions', 'source', 'destination', '(aaa)') AS query, 'query_flags', 'ALLOW_COLUMN|ALLOW_LEADING_NOT|QUERY_NO_SYNTAX_ERROR', 'filter', 'in_values(project_id, 2, 3, 4, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 100, 101, 102, 103, 104, 105, 106, 107, 108, 109, 110, 111, 112, 113, 114, 115, 116, 117, 118, 119, 120, 121, 122, 123, 124, 125, 126, 127, 128, 129, 130, 131, 132, 133, 134, 135, 136, 137, 138, 139, 140, 141, 142, 143, 144, 145, 146, 147, 148, 149, 150, 151, 152, 153, 154, 155, 156, 157, 158, 159, 160, 161, 162, 163, 164, 165, 166, 167, 168, 169, 170, 171, 172, 173, 174, 175, 177, 178, 179, 180, 181, 182, 183, 184, 185, 186, 187, 189, 190, 191, 192, 193, 194, 195, 196, 197, 198, 205, 206, 207) &! is_private == true &! ( in_values(project_id, 189) && ( source_type_id == 2 || container_type_id == 2 ) ) &! ( in_values(project_id, 3, 182, 183, 184, 185, 186, 187, 189, 190, 191, 192, 193, 194, 195, 196, 197, 198, 205, 206, 207) && ( source_type_id == 6 || container_type_id == 6 ) ) &! ( in_values(project_id, 3, 182, 183, 184, 185, 186, 187, 189, 190, 191, 192, 193, 194, 195, 196, 197, 198, 205, 206, 207) && ( source_type_id == 7 || container_type_id == 7 ) ) &! ( in_values(project_id, 2, 3, 182, 183, 184, 185, 186, 187, 189, 190, 191, 192, 193, 194, 195, 196, 197, 198, 205, 206, 207) && ( source_type_id == 10 || container_type_id == 10 ) ) &! ( in_values(project_id, 3, 182, 183, 184, 185, 186, 189, 205, 207) && ( source_type_id == 3 || container_type_id == 3 ) ) &! ( in_values(project_id, 3, 182, 183, 184, 186, 187, 189, 190, 191, 192, 193, 194, 195, 196, 197, 198, 205, 206, 207) && ( source_type_id == 8 || container_type_id == 8 ) ) &! ( in_values(project_id, 2, 3, 182, 183, 184, 185, 186, 187, 189, 190, 191, 192, 193, 194, 195, 196, 197, 198, 205, 206, 207) && ( source_type_id == 11 || container_type_id == 11 ) )', 'output_columns', '_score, content_snippets, id, last_modified_at, project_id, source_id, source_type_id, title, highlighted_title, tag_ids', 'limit', '100', 'drilldowns[source_type].keys', 'source_type_id', 'drilldowns[source_type].limit', '-1', 'drilldowns[container_type].keys', 'container_type_id', 'drilldowns[container_type].limit', '-1', 'columns[highlighted_title].stage', 'output', 'columns[highlighted_title].type', 'ShortText', 'columns[highlighted_title].flags', 'COLUMN_SCALAR', 'columns[highlighted_title].value', 'highlight_html(title)', 'columns[content_snippets].stage', 'output', 'columns[content_snippets].type', 'ShortText', 'columns[content_snippets].flags', 'COLUMN_VECTOR', 'columns[content_snippets].value', 'snippet_html(content)', 'drilldowns[tag].keys', 'tag_ids', 'drilldowns[tag].limit', '-1', 'drilldowns[tag].sort_keys', '-_nsubrecs', 'sort_keys', '-_score, -last_modified_at', 'offset', '0', 'table', 'fts_targets')
):

-----Error Log-----

Environment OS : Ubuntu Server 22.04 LTS Docker : 24.0.7, build 24.0.7-0ubuntu2~22.04.1 Docker Compose : 1.29.2, build unknown Redmine(container image) : sameersbn/redmine:4.2.10 DB(container image) : groonga/mroonga:mysql-5.7-latest

abetomo commented 3 weeks ago

Could you use groonga/mroonga:mysql-8.0-latest instead of groonga/mroonga:mysql-5.7-latest?

asyahana commented 3 weeks ago

@abetomo Thanks for comments.

Could you use groonga/mroonga:mysql-8.0-latest instead of groonga/mroonga:mysql-5.7-latest?

I tried to use groonga/mroonga:mysql-8.0-latest instad of groonga/mroonga:mysql-5.7-latest, but it failed to build. That's the reason why I want to use groonga/mroonga:mysql-5.7-latest,

-----Error Log(Build)-----

database_1  | 2024-06-18 10:33:39+09:00 [Note] [Entrypoint]: Switching to dedicated user 'mysql'
database_1  | 2024-06-18 10:33:39+09:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.0.32-1.el8 started.
database_1  | '/var/lib/mysql/mysql.sock' -> '/var/run/mysqld/mysqld.sock'
database_1  | 2024-06-18T01:33:40.154834Z 0 [Warning] [MY-011068] [Server] The syntax '--skip-host-cache' is deprecated and will be removed in a future release. Please use SET GLOBAL host_cache_size=0 instead.
database_1  | 2024-06-18T01:33:40.156170Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.34) starting as process 1
database_1  | 2024-06-18T01:33:40.177025Z 0 [Warning] [MY-013907] [InnoDB] Deprecated configuration parameters innodb_log_file_size and/or innodb_log_files_in_group have been used to compute innodb_redo_log_capacity=1073741824. Please use innodb_redo_log_capacity instead.
database_1  | 2024-06-18T01:33:40.178397Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
database_1  | 2024-06-18T01:33:40.480962Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
database_1  | mysqld: Table 'mysql.plugin' doesn't exist
database_1  | 2024-06-18T01:33:40.580377Z 0 [ERROR] [MY-010735] [Server] Could not open the mysql.plugin table. Please perform the MySQL upgrade procedure.
database_1  | 2024-06-18T01:33:40.580596Z 0 [Warning] [MY-010441] [Server] Failed to open optimizer cost constant tables
database_1  | 2024-06-18T01:33:40.580732Z 0 [Warning] [MY-010441] [Server] Failed to open optimizer cost constant tables
database_1  | 2024-06-18T01:33:40.580896Z 0 [Warning] [MY-010441] [Server] Failed to open optimizer cost constant tables
database_1  | 2024-06-18T01:33:40.581130Z 0 [Warning] [MY-010441] [Server] Failed to open optimizer cost constant tables
database_1  | 2024-06-18T01:33:40.581249Z 0 [Warning] [MY-010441] [Server] Failed to open optimizer cost constant tables
database_1  | 2024-06-18T01:33:40.581371Z 0 [Warning] [MY-010441] [Server] Failed to open optimizer cost constant tables
database_1  | 2024-06-18T01:33:40.581479Z 0 [Warning] [MY-010441] [Server] Failed to open optimizer cost constant tables
database_1  | 2024-06-18T01:33:40.630243Z 0 [Warning] [MY-010015] [Repl] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
database_1  | 2024-06-18T01:33:40.733234Z 0 [Warning] [MY-010015] [Repl] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
database_1  | 2024-06-18T01:33:40.741617Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
database_1  | 2024-06-18T01:33:40.741645Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
database_1  | 2024-06-18T01:33:40.752297Z 0 [Warning] [MY-010441] [Server] Failed to open optimizer cost constant tables
database_1  | 2024-06-18T01:33:40.752533Z 0 [ERROR] [MY-013129] [Server] A message intended for a client cannot be sent there as no client-session is attached. Therefore, we're sending the information to the error-log instead: MY-001146 - Table 'mysql.component' doesn't exist
database_1  | 2024-06-18T01:33:40.752560Z 0 [Warning] [MY-013129] [Server] A message intended for a client cannot be sent there as no client-session is attached. Therefore, we're sending the information to the error-log instead: MY-003543 - The mysql.component table is missing or has an incorrect definition.
database_1  | 2024-06-18T01:33:40.752656Z 0 [ERROR] [MY-000067] [Server] unknown variable 'query_cache_size=0'.
database_1  | 2024-06-18T01:33:40.752754Z 0 [ERROR] [MY-010119] [Server] Aborting
database_1  | 2024-06-18T01:33:42.407761Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.34)  MySQL Community Server - GPL.
docker_database_1 exited with code 1

-----Error Log(Build)-----

c.f. Here is what's in the file: docker-compose.yml amd what's in the file: ./database/Dockerfile

-----Contents of docker-compose.yml-----

version: '3.8'
services:
  database:
    build: ./database
    image: groonga/mroonga:mysql-8.0-latest
    ports:
      - 3306:3306
    environment:
      TZ: Asia/Tokyo
      MYSQL_USER: u_redmine
      MYSQL_PASSWORD: redmine
      MYSQL_DATABASE: db_redmine
      MYSQL_ROOT_PASSWORD: 1999-icsp
    volumes:
      - ./database/data:/var/lib/mysql
      - ./database/my.cnf:/etc/mysql/conf.d/my.cnf
      - ./database/sql:/docker-entrypoint-initdb.d
      - ./database/restore:/etc/mysql/restore
    command: mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci
    restart: always

  redmine:
    build: ./redmine
    image: sameersbn/redmine:4.2.10
    depends_on:
      - database
    environment:
      TZ: Asia/Tokyo

      DB_ADAPTER: mysql2
      DB_HOST: database
      DB_PORT: 3306

      DB_USER: u_redmine
      DB_PASS: redmine
      DB_NAME: db_redmine

      REDMINE_PORT:
      REDMINE_HTTPS: 0
      REDMINE_RELATIVE_URL_ROOT:
      REDMINE_SECRET_TOKEN:

      REDMINE_SUDO_MODE_ENABLED: 0
      REDMINE_SUDO_MODE_TIMEOUT: 15

      REDMINE_CONCURRENT_UPLOADS: 2

      REDMINE_BACKUP_SCHEDULE: daily
      REDMINE_BACKUP_EXPIRY:
      REDMINE_BACKUP_TIME: "23:30"

      SMTP_ENABLED: "true"
      SMTP_DOMAIN: xyz.jp
      SMTP_HOST: smtp.xyz.jp
      SMTP_PORT: 25
    ports:
      - 80:80
    volumes:
      - ./redmine/data:/home/redmine/data
      - ./redmine/logs:/var/log/redmine
    restart: always

-----Contents of docker-compose.yml-----

-----Contents of ./database/Dockerfile-----

FROM groonga/mroonga:mysql-8.0-latest

-----Contents of ./database/Dockerfile-----

otegami commented 3 weeks ago

database_1 | mysqld: Table 'mysql.plugin' doesn't exist database_1 | 2024-06-18T01:33:40.580377Z 0 [ERROR] [MY-010735] [Server] Could not open the mysql.plugin table. Please perform the MySQL upgrade procedure.

Which MySQL version do you use for generating your ./database/data ? I think that if you were using a version of MySQL prior to v8.0.30, you might need to upgrade it at first. Because this groonga/mroonga:mysql-8.0-latest uses MySQL v8.0.30.

asyahana commented 3 weeks ago

MySQL version generating in ./database/data is 8.0-latest, its version is 8.0.30-1206. (source: https://hub.docker.com/layers/groonga/mroonga/mysql-8.0-latest/images/sha256-4c3d0c14cf9e584aea95a182f3d0a0ed21f8198070a543bdd2a1702446ffcb53?context=explore)

I tried specifying the version groonga/mroonga:mysql-8.0.30-12.06 , but it didn't build. To be precise, the mroonga engine was not installed in groonga/mroonga:mysql-8.0.30-12.06.

otegami commented 3 weeks ago

database_1 | mysqld: Table 'mysql.plugin' doesn't exist database_1 | 2024-06-18T01:33:40.580377Z 0 [ERROR] [MY-010735] [Server] Could not open the mysql.plugin table. Please perform the MySQL upgrade procedure.

Thanks. I guess this is MySQL steup problem because of the above error. If you didn't generate the ./database/data directories using a version of MySQL prior to v8.0.30, the mysql user couldn't access to /database/data. That's why mysql user misunderstood the upgrading process didn't successe. Could you show me permissions using following command?

$ ls -la database/data

On the ohter hand, I did it locally, and it succeeded as follows. If you don't mind, could you try it like the following.

My environment

OS: Ubuntu 22.04.4 LTS Docker version: 26.1.4

# docker-compose.yml
services:
  database:
    image: groonga/mroonga:mysql-8.0-latest
    ports:
      - 3306:3306
    environment:
      TZ: Asia/Tokyo
      MYSQL_USER: u_redmine
      MYSQL_PASSWORD: redmine
      MYSQL_DATABASE: db_redmine
      MYSQL_ROOT_PASSWORD: 1999-icsp
    volumes:
      - ./database/data:/var/lib/mysql
      - ./database/my.cnf:/etc/mysql/conf.d/my.cnf
      - ./database/sql:/docker-entrypoint-initdb.d
      - ./database/restore:/etc/mysql/restore
    command: mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci
    restart: always

  redmine:
    image: sameersbn/redmine:4.2.10
    depends_on:
      - database
    environment:
      TZ: Asia/Tokyo

      DB_ADAPTER: mysql2
      DB_HOST: database
      DB_PORT: 3306

      DB_USER: u_redmine
      DB_PASS: redmine
      DB_NAME: db_redmine

      REDMINE_PORT:
      REDMINE_HTTPS: 0
      REDMINE_RELATIVE_URL_ROOT:
      REDMINE_SECRET_TOKEN:

      REDMINE_SUDO_MODE_ENABLED: 0
      REDMINE_SUDO_MODE_TIMEOUT: 15

      REDMINE_CONCURRENT_UPLOADS: 2

      REDMINE_BACKUP_SCHEDULE: daily
      REDMINE_BACKUP_EXPIRY:
      REDMINE_BACKUP_TIME: "23:30"

      SMTP_ENABLED: "true"
      SMTP_DOMAIN: xyz.jp
      SMTP_HOST: smtp.xyz.jp
      SMTP_PORT: 25
    ports:
      - 80:80
    volumes:
      - ./redmine/data:/home/redmine/data
      - ./redmine/logs:/var/log/redmine
    restart: always
$ tree
tree .
.
└── docker-compose.yml
$ docker compose up -d
asyahana commented 2 weeks ago

Thanks. :)) I tried it and it was successful. However, what's annoying is that I don't know why this way of writing was successful when the end result was the same.