Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-26917

InnoDB: Clustered record for sec rec not found index

    XMLWordPrintable

    Details

      Description

      I got about 1 GB of these messages in the logfile:

      2021-10-27 14:41:28 17998619 [ERROR] InnoDB: Clustered record for sec rec not found index `fs_mtime` of table `nextcloud`.`oc_filecache`
      InnoDB: sec index record PHYSICAL RECORD: n_fields 2; compact format; info bits 0
       0: len 8; hex 80000000609b9914; asc     `   ;;
       1: len 8; hex 800000000269a5e6; asc      i  ;;
       
      InnoDB: clust index record PHYSICAL RECORD: n_fields 18; compact format; info bits 0
       0: len 8; hex 800000000269a5e4; asc      i  ;;
       1: len 6; hex 000000000000; asc       ;;
       2: len 7; hex 80000000000000; asc        ;;
       3: len 8; hex 8000000000000002; asc         ;;
       4: len 30; hex 617070646174615f6f6378316f37396f786e69702f707265766965772f63; asc appdata_ocx1o79oxnip/preview/c; (total 51 bytes);
       5: len 30; hex 363233306533373536316561343864656463373035363934393837646538; asc 6230e37561ea48dedc705694987de8; (total 32 bytes);
       6: len 8; hex 800000000269a5e3; asc      i  ;;
       7: len 8; hex 3430343539393336; asc 40459936;;
       8: len 8; hex 8000000000000002; asc         ;;
       9: len 8; hex 8000000000000001; asc         ;;
       10: len 8; hex 8000000000000000; asc         ;;
       11: len 8; hex 8000000060ae224d; asc     ` "M;;
       12: len 8; hex 8000000060ae224d; asc     ` "M;;
       13: len 4; hex 80000000; asc     ;;
       14: len 8; hex 8000000000000000; asc         ;;
       15: len 13; hex 36303962393839653739333532; asc 609b989e79352;;
       16: len 4; hex 8000001f; asc     ;;
       17: len 0; hex ; asc ;;
       
      TRANSACTION 421825623286288, ACTIVE 38 sec fetching rows
      mysql tables in use 1, locked 0
      0 lock struct(s), heap size 1128, 0 row lock(s)
      MySQL thread id 17998619, OS thread handle 140350620571392, query id 731677120 172.21.243.109 nextcloud Sending data
      SELECT `f`.* FROM `oc_filecache` `f` WHERE (((`f`.`storage` = '1594') AND (`f`.`path` LIKE 'files/...%')) OR ((`f`.`storage` = '2947') AND (`f`.`path` LIKE 'files/...%')) OR ((`f`.`storage` = '2947') AND (`f`.`path` LIKE 'files/...%')) OR...
       
      InnoDB: Submit a detailed bug report to https://jira.mariadb.org/
      

      (I shortend the filenames in the query for data protection reasons)

      The error message contains always the same table and index name, the sql differs in the searched data, but is always the same structure.

      CHECK TABLE oc_filecache gives only a "Ok".

      OPTIMIZE TABLE worked, but there the errors are still there.

      Dropping and re-creating the fs_mtime index worked, but the error messages are still there.

      I also tried to export the whole table with mysqldump, dropping the table and re-importing the dump, but after this operation the errors are back again.

      Server config:

      [mysqld]
      user            = mysql
      pid-file        = /var/run/mysqld/mysqld.pid
      socket          = /var/run/mysqld/mysqld.sock
      port            = 3306
      basedir         = /usr
      datadir         = /data/mysql
      tmpdir          = /data/mysqltmp
      lc-messages-dir = /usr/share/mysql
      skip-external-locking
      key_buffer_size         = 16M
      max_allowed_packet      = 16M
      thread_stack            = 192K
      thread_cache_size       = 8
      myisam_recover_options  = BACKUP
      query_cache_size        = 0
      query_cache_type        = OFF
      log_error = /data/log/mysql/error.log
      slow_query_log_file     = /data/log/mysql/slow.log
      long_query_time = 10
      log_slow_rate_limit     = 1000
      log_slow_verbosity      = query_plan
      server-id               = 1
      disable-log-bin
      innodb_file_per_table=true
      ssl-ca=/etc/mysql/nc1-db-ca-cert.pem
      ssl-cert=/etc/mysql/nc1-db-server-cert.pem
      ssl-key=/etc/mysql/nc1-db-server-key.pem
      ssl=on
      character-set-server  = utf8mb4
      collation-server      = utf8mb4_general_ci
      skip-name-resolve       = 1
      join_buffer_size = 1M
      transaction-isolation = READ-COMMITTED
      max_connections = 128 # (> 151)
      wait_timeout = 3600 # (< 28800)
      interactive_timeout = 3600 # (< 28800)
      join_buffer_size = 2M  #(> 1.0M, or always use indexes with joins)
      table_open_cache = 4096 # (> 2000)
      innodb_buffer_pool_size = 724M # (>= 724M) if possible.
      tmp_table_size = 32M
      max_heap_table_size = 32M
      innodb_flush_method=O_DIRECT
      max_statement_time = 90
      performance_schema = OFF
      thread_handling=pool-of-threads
      innodb_log_file_size=192M
      thread_pool_max_threads=128
      

      Create table:

      CREATE TABLE `oc_filecache` (
        `fileid` bigint(20) NOT NULL AUTO_INCREMENT,
        `storage` bigint(20) NOT NULL DEFAULT 0,
        `path` varchar(4000) COLLATE utf8mb4_bin DEFAULT NULL,
        `path_hash` varchar(32) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
        `parent` bigint(20) NOT NULL DEFAULT 0,
        `name` varchar(250) COLLATE utf8mb4_bin DEFAULT NULL,
        `mimetype` bigint(20) NOT NULL DEFAULT 0,
        `mimepart` bigint(20) NOT NULL DEFAULT 0,
        `size` bigint(20) NOT NULL DEFAULT 0,
        `mtime` bigint(20) NOT NULL DEFAULT 0,
        `storage_mtime` bigint(20) NOT NULL DEFAULT 0,
        `encrypted` int(11) NOT NULL DEFAULT 0,
        `unencrypted_size` bigint(20) NOT NULL DEFAULT 0,
        `etag` varchar(40) COLLATE utf8mb4_bin DEFAULT NULL,
        `permissions` int(11) DEFAULT 0,
        `checksum` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
        PRIMARY KEY (`fileid`),
        UNIQUE KEY `fs_storage_path_hash` (`storage`,`path_hash`),
        KEY `fs_parent_name_hash` (`parent`,`name`),
        KEY `fs_storage_mimetype` (`storage`,`mimetype`),
        KEY `fs_storage_mimepart` (`storage`,`mimepart`),
        KEY `fs_storage_size` (`storage`,`size`,`fileid`),
        KEY `fs_size` (`size`),
        KEY `fs_mtime` (`mtime`)
      ) ENGINE=InnoDB AUTO_INCREMENT=46263064 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=COMPRESSED
      

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              marko Marko Mäkelä
              Reporter:
              afleischer Alexander Fleischer
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Dates

                Created:
                Updated:

                  Git Integration