Problems converting indices to bigint

ℹ️ Support
,
1
Support intro

Sorry to hear you’re facing problems :slightly_frowning_face:

help.nextcloud.com is for home/non-enterprise users. If you’re running a business, paid support can be accessed via portal.nextcloud.com where we can ensure your business keeps running smoothly.

In order to help you as quickly as possible, before clicking Create Topic please provide as much of the below as you can. Feel free to use a pastebin service for logs, otherwise either indent short log examples with four spaces:

Or for longer, use three backticks above and below the code snippet:

longer
example
here

Some or all of the below information will be requested if it isn’t supplied; for fastest response please provide as much as you can :heart:

Nextcloud version (eg, 12.0.2):15.0
Operating system and version (eg, Ubuntu 17.04):?
Apache or nginx version (eg, Apache 2.4.25): ?
PHP version (eg, 7.1): 7.2
MySQL version: 5.7.21-nmm1-log - (Ubuntu)

The issue you are facing:
Modifications on the database as suggested by NextCloud often require some occ commands on the shell. When using a shared hosting provider the command shell usually is not available.

It would be helpful if next to the suggestion on what to change in the SQL database there were a button to download an *.sql file which contains the corresponding SQL commands to be executed on the database. This can usually easily be applied on some managment interface like phpMyAdmin even for instances running on a shared hosting.

Can you provide the commands for the conversion int -> bigint?

Is this the first time you’ve seen this error? (Y/N): N

Steps to replicate it:

  1. update from NexCloud 14.0.4 to version 15.0.0
  2. check th suggestions made on the settings tab
  3. phpMyAdmin: ALTER TABLE `prefix_activity` ALTER COLUMN `activity_id` bigint
  4. Error: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘bigint’ at line 1

The output of your Nextcloud log in Admin > Logging:

PASTE HERE

The output of your config.php file in /path/to/nextcloud (make sure you remove any identifiable information!):

PASTE HERE

The output of your Apache/nginx/system log in /var/log/____:

PASTE HERE
2

I goggled a bit and found out the following. This worked for me:

ALTER TABLE `your_table_prefix_activity` MODIFY `activity_id` bigint NOT NULL AUTO_INCREMENT;
ALTER TABLE `your_table_prefix_activity` MODIFY `object_id` bigint NOT NULL DEFAULT ‘0’;
ALTER TABLE `your_table_prefix_activity_mq` MODIFY `mail_id` bigint NOT NULL AUTO_INCREMENT;
ALTER TABLE `your_table_prefix_filecache` MODIFY `fileid` bigint NOT NULL AUTO_INCREMENT;
ALTER TABLE `your_table_prefix_filecache` MODIFY `storage` bigint NOT NULL DEFAULT ‘0’;
ALTER TABLE `your_table_prefix_filecache` MODIFY `parent` bigint NOT NULL DEFAULT ‘0’;
ALTER TABLE `your_table_prefix_filecache` MODIFY `mimetype` bigint NOT NULL DEFAULT ‘0’;
ALTER TABLE `your_table_prefix_filecache` MODIFY `mimepart` bigint NOT NULL DEFAULT ‘0’;
ALTER TABLE `your_table_prefix_filecache` MODIFY `mtime` bigint NOT NULL DEFAULT ‘0’;
ALTER TABLE `your_table_prefix_filecache` MODIFY `storage_mtime` bigint NOT NULL DEFAULT ‘0’;
ALTER TABLE `your_table_prefix_mimetypes` MODIFY `id` bigint NOT NULL AUTO_INCREMENT;
ALTER TABLE `your_table_prefix_storages` MODIFY `numeric_id` bigint NOT NULL AUTO_INCREMENT;

I hope this helps others. :wink:

3 Likes
3

Hi @nieroster,

Do you think these 2 lines can fix the second part of my problem in this post? :

  • ALTER TABLE oc_filecache MODIFY mtime bigint NOT NULL DEFAULT ‘0’;
  • ALTER TABLE oc_filecache MODIFY storage_mtime bigint NOT NULL DEFAULT ‘0’;

I’m scared to make mistakes by manipulating phpmyadmin without confirmation.

And by any chance, would you have a proposal for the first part?

Thank you in advance

4

Hello,

In case you need to perform occ commands and don’t have shell access due to the usage of hosting providers, you could use cronjobs to do the job for you. Just to have another idea. Then you could run the occ command safely and don’t need to worry about breaking your database.

5

Hi ffs69,

before applying SQL commands you should make a backup using phpMyAdmin. Use the export tab to generate an sql file of the entire database. Don’t forget to change your NextCloud instance to maintenance mode before continuing.

The 2 commands above are correct provided your table prefix is “oc_”. Look into config.php if you are not sure.

For the missing indices use these commands:
ALTER TABLE `oc_share` ADD KEY `owner_index` (`uid_owner`) USING BTREE;
ALTER TABLE `oc_share` ADD KEY `initiator_index` (`uid_initiator`) USING BTREE;

Happy patching!

1 Like
6

Hi schmu,

how do you run occ commands using webcron? Cron itself is not available on shared hosting.

7

Hi @nieroster

I don’t need a shared hoster myself, but read a user comment here, who solved the problem with OCC commands on shared hosts with cron, so I thought I mention it here and you know how to do it :smiley:
After your question it took me a while to find that post again:

Not sure if you have c-panel with your hosting provider. If not, maybe this is helpful:

8

Another new solution is available. See

2 Likes
9

@rakekniven You sir are a legend. You have solved something simply and quickly that I had been struggling with for hours. I have my own VPS with NextCloud installed via cPanel and the convert-filecache-bigint script wouldn’t run no matter which of the hundreds of suggestions I tried. I installed the app and it was all done in a matter of seconds. Thank you very much!