I had a problem with my oc_filecache table being corrupt. I searched on how to fix this and, unfortunately, one of the first “solutions” that I found was “delete the table then recreate it”. I succeeded at the “delete the table” part, but I can’t find any information on how to recreate it…
Yes, I now know that I should have truncated.
I tried a few occ commands : “maintenance:repair”, “files:scan” and even “upgrade”, but none worked.
Can someone please tell me which command to run?
Or, give me the schema of their oc_filecache table so that I can create it in my database?
Thank you,
-MIke
P.S. I didn’t give any system info because I don’t know where to get it, since Nextcloud can’t run…
Ah you did not empty one but rather delete it. OK then you have to recreate it fully with sql.
This is how my backup looks like using mysqldump. It’s from Nextcloud 17. It creates a new empty table with correct columns. occ files:scan --all -vvv should work now.
--
-- Table structure for table `oc_filecache`
--
DROP TABLE IF EXISTS `oc_filecache`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
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_mtime` (`mtime`)
) ENGINE=InnoDB AUTO_INCREMENT=727173 DEFAULT CHARSET=utf8mb4 COLLA
TE=utf8mb4_bin ROW_FORMAT=COMPRESSED;
/*!40101 SET character_set_client = @saved_cs_client */;
I know this is an old topic, but I feel having the commands for PostgreSQL is important. here is what worked for me after tons of debugging and going through an export of a backup
DROP TABLE IF EXISTS oc_filecache;
CREATE TABLE public.oc_filecache (
fileid bigint NOT NULL,
storage bigint DEFAULT 0 NOT NULL,
path character varying(4000) DEFAULT NULL::character varying,
path_hash character varying(32) DEFAULT ''::character varying NOT NULL,
parent bigint DEFAULT 0 NOT NULL,
name character varying(250) DEFAULT NULL::character varying,
mimetype bigint DEFAULT 0 NOT NULL,
mimepart bigint DEFAULT 0 NOT NULL,
size bigint DEFAULT 0 NOT NULL,
mtime bigint DEFAULT 0 NOT NULL,
storage_mtime bigint DEFAULT 0 NOT NULL,
encrypted integer DEFAULT 0 NOT NULL,
unencrypted_size bigint DEFAULT 0 NOT NULL,
etag character varying(40) DEFAULT NULL::character varying,
permissions integer DEFAULT 0,
checksum character varying(255) DEFAULT NULL::character varying
);
ALTER TABLE public.oc_filecache OWNER TO <<nextcloud_user>>;
CREATE SEQUENCE public.oc_filecache_fileid_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
ALTER TABLE public.oc_filecache_fileid_seq OWNER TO <<nextcloud_user>>;
ALTER TABLE ONLY public.oc_filecache ALTER COLUMN fileid SET DEFAULT nextval('public.oc_filecache_fileid_seq'::regclass);
You will need to replace <<nextcloud_user>> to what your varible dbuser in config/config.php is
This was a life saver for PostgreSQL. The last command finally fixed it since in the backup I have all the previous commands but this one was lost in the rest of the backup. Thank you so much.
