MySQL and supposedly missing oc_authtoken table

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:

example

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.5
Operating system and version (eg, Ubuntu 17.04): Ubuntu 16.0.4
Apache or nginx version (eg, Apache 2.4.25): Apache 2.4.18
PHP version (eg, 7.1): 7.0.33
MySQL: 5.7.25

The issue you are facing:
Nextcloud has been working for a couple of year on my server, but failed this morning. Yesterday I upgraded three apps and afterwards I upgraded Nextcloud from 15.0.2 to 15.0.5. This morning Nextcloud failed most likely due to a MySQL issue. The MySQL error.log is being written to constantly and mentions issues with the authtoken table. When issuing a “show tables” command within the mysql cli, the authtoken table is shown amongst all the other Nextcloud tables. When issuing a “CHECK TABLE oc_authtoken” command, MySQL says “Table ‘owncloud.oc_authtoken’ doesn’t exist”.
I’ve put Nextcloud into maintenance mode and have tried the “sudo -u www-data php occ maintenance:repair” command. It shows the following:
Nextcloud is in maintenance mode - no apps have been loaded

Failed to load repair step for audioplayer: Repair step ‘OCA\audioplayer\Migration\Migration’ is unknown
Failed to load repair step for dav: Repair step ‘OCA\DAV\Migration\FixBirthdayCalendarComponent’ is unknown
Failed to load repair step for dav: Repair step ‘OCA\DAV\Migration\CalDAVRemoveEmptyValue’ is unknown
Failed to load repair step for dav: Repair step ‘OCA\DAV\Migration\BuildCalendarSearchIndex’ is unknown
Failed to load repair step for dav: Repair step ‘OCA\DAV\Migration\RefreshWebcalJobRegistrar’ is unknown
Failed to load repair step for dav: Repair step ‘OCA\DAV\Migration\RemoveClassifiedEventActivity’ is unknown
Failed to load repair step for files_sharing: Repair step ‘OCA\Files_Sharing\Migration\OwncloudGuestShareType’ is unknown
Failed to load repair step for files_sharing: Repair step ‘OCA\Files_Sharing\Migration\SetPasswordColumn’ is unknown
Failed to load repair step for oauth2: Repair step ‘OCA\OAuth2\Migration\SetTokenExpiration’ is unknown
Failed to load repair step for twofactor_backupcodes: Repair step ‘OCA\TwoFactorBackupCodes\Migration\CheckBackupCodes’ is unknown

  • Repair MySQL collation
    • All tables already have the correct collation -> nothing to do
  • Repair mime types
  • Clean tags and favorites
    • 0 tags of deleted users have been removed.
    • 0 tags for delete files have been removed.
    • 0 tag entries for deleted tags have been removed.
    • 0 tags with no entries have been removed.
  • Repair invalid shares
  • Remove shares of a users root folder
  • Move .step file of updater to backup location
  • Fix potential broken mount points
    • No mounts updated
  • Repair invalid paths in file cache
  • Add log rotate job
  • Clear frontend caches
    • Image cache cleared
    • SCSS cache cleared
    • JS cache cleared
  • Clear every generated avatar on major updates
  • Add preview background cleanup job
  • Queue a one-time job to cleanup old backups of the updater
  • Repair pending cron jobs
    • No need to repair pending cron jobs.
  • Extract the vcard uid and store it in the db

The messages (they keep repeating) in the MySQL error.log is somewhat ominous. Other than page dumps, it shows the following:
[Note] InnoDB: Uncompressed page, stored checksum in field1 2278197719, calculated checksums for field1: crc32 1598924062/2669165296, innodb 2251606416, none 3735928559, stored checksum in field2 4098179892, calculated checksums for field2: crc32 1598924062/2669165296, innodb 2190848791, none 3735928559, page LSN 3 2016071666, low 4 bytes of LSN at page end 2006634632, page number (if stored to page already) 4, space id (if created with >= MySQL-4.1.1 and stored already) 705
InnoDB: Page may be an index page where index id is 1497
[Note] InnoDB: Index 1497 is authtoken_token_index in table owncloud.oc_authtoken
[Note] InnoDB: It is also possible that your operating system has corrupted its own file cache and rebooting your computer removes the error. If the corrupt page is an index page. You can also try to fix the corruption by dumping, dropping, and reimporting the corrupt table. You can use CHECK TABLE to scan your table for corruption. Please refer to http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html for information about forcing recovery.
[ERROR] InnoDB: Database page corruption on disk or a failed file read of page [page id: space=705, page number=4]. You may have to recover from a backup.
[Note] InnoDB: Page dump in ascii and hex (16384 bytes):
… (page dump) …

I’m no db expert. I’m sort of hoping it might be possible to simply remove that authtoken table and then have Nextcloud re-create it, f.ex. with the occ repair option.
I’d rather ask here for suggestions as to what to do before I potentially do something stupid.

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

Steps to replicate it:

  1. I’d rather not

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

My oc_authtoken table was missing today due to other reasons I’d rather not list here :expressionless: and I got my NextCloud instance working again by creating the table with the following commands:

  1. Log into MySQL
    root@nextcloud:~# mysql -u root -p

  2. Use the “nextcloud” database:
    MariaDB [(none)]> use nextcloud;

  3. Paste in the following:

CREATE TABLE oc_authtoken (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
uid VARCHAR(64) NOT NULL,
login_name VARCHAR(64) NOT NULL,
password LONGTEXT,
name LONGTEXT NOT NULL,
token VARCHAR(200) NOT NULL,
type SMALLINT(5) UNSIGNED NOT NULL DEFAULT '0',
last_activity INT(10) NOT NULL DEFAULT '0',
last_check INT(10) UNSIGNED NOT NULL DEFAULT '0',
remember SMALLINT(5) UNSIGNED NOT NULL DEFAULT '0',
scope LONGTEXT,
expires INT(10) UNSIGNED,
private_key LONGTEXT,
public_key LONGTEXT,
version SMALLINT(5) UNSIGNED NOT NULL DEFAULT '1',
password_invalid TINYINT(1) NOT NULL DEFAULT '0')
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

…and my NC instance seems happy again. :relieved:

2 Likes

This was exactly what I was looking for! Thanks a lot.

I just had to rebuild this and the db structure changed a little bit so this is what fixed it for me. (notice the password_hash variable)

CREATE TABLE oc_authtoken (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, uid VARCHAR(64) NOT NULL, login_name VARCHAR(64) NOT NULL, password LONGTEXT, password_hash LONGTEXT, name LONGTEXT NOT NULL, token VARCHAR(200) NOT NULL, type SMALLINT(5) UNSIGNED NOT NULL DEFAULT '0', last_activity INT(10) NOT NULL DEFAULT '0', last_check INT(10) UNSIGNED NOT NULL DEFAULT '0', remember SMALLINT(5) UNSIGNED NOT NULL DEFAULT '0', scope LONGTEXT, expires INT(10) UNSIGNED, private_key LONGTEXT, public_key LONGTEXT, version SMALLINT(5) UNSIGNED NOT NULL DEFAULT '1', password_invalid TINYINT(1) NOT NULL DEFAULT '0') ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;