Update from OC5.0.13 to OC6RC4 - Base table or view not found: 1146 Table 'owncloud.oc_jobs' doesn't exist

[hdering]

hi guys,

update from OC5.0.13 to OC6RC4.

1. backed up /config and /data
2. deleted the rest /var/www
3. copied the new files from OC6RC4
4. copy /config and /data into /var/www

data/owncloud.log

Error core can't remove app files_mv. It is not installed. 2013-12-07T15:08:38+00:00
Error core App "" (files_mv) can't be used because it is not compatible with this version of ownCloud 2013-12-07T15:08:38+00:00
Error core can't remove app files_odfviewer. It is not installed. 2013-12-07T15:08:38+00:00
Error core App "" (files_odfviewer) can't be used because it is not compatible with this version of ownCloud 2013-12-07T15:08:38+00:00
Error core can't remove app admin_migrate. It is not installed. 2013-12-07T15:08:38+00:00
Error core App "" (admin_migrate) can't be used because it is not compatible with this version of ownCloud 2013-12-07T15:08:38+00:00
Error core can't remove app user_migrate. It is not installed. 2013-12-07T15:08:38+00:00
Error core App "" (user_migrate) can't be used because it is not compatible with this version of ownCloud 2013-12-07T15:08:38+00:00
Error core can't remove app files_imageviewer. It is not installed. 2013-12-07T15:08:38+00:00
Error core App "" (files_imageviewer) can't be used because it is not compatible with this version of ownCloud 2013-12-07T15:08:38+00:00
Fatal index Exception: An exception occurred while executing 'SELECT id FROM oc_jobs WHERE class = ? AND argument = ?': SQLSTATE[42S02]: Base table or view not found: 1146 Table 'owncloud.oc_jobs' doesn't exist - Verursacht durch: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'owncloud.oc_jobs' doesn't exist[42S02] 2013-12-07T15:07:59+00:00
Fatal index Exception: 11 {main} 2013-12-07T15:07:59+00:00
Fatal index Exception: 10 /var/www/index.php(30): OC::handleRequest() 2013-12-07T15:07:59+00:00
Fatal index Exception: 9 /var/www/lib/base.php(689): OC_App::loadApps() 2013-12-07T15:07:59+00:00
Fatal index Exception: 8 /var/www/lib/private/app.php(66): OC_App::loadApp('files') 2013-12-07T15:07:59+00:00
Fatal index Exception: 7 /var/www/lib/private/app.php(93): require_once('/var/www/apps/f...') 2013-12-07T15:07:59+00:00
Fatal index Exception: 6 /var/www/apps/files/appinfo/app.php(22): OCP\BackgroundJob::addRegularTask('\OC\Files\Cache...', 'checkNext') 2013-12-07T15:07:59+00:00
Fatal index Exception: 5 /var/www/lib/public/backgroundjob.php(86): OCP\BackgroundJob::registerJob('OC\BackgroundJo...', Array) 2013-12-07T15:07:59+00:00
Fatal index Exception: 4 /var/www/lib/public/backgroundjob.php(75): OC\BackgroundJob\JobList->add('OC\BackgroundJo...', Array) 2013-12-07T15:07:59+00:00
Fatal index Exception: 3 /var/www/lib/private/backgroundjob/joblist.php(24): OC\BackgroundJob\JobList->has('OC\BackgroundJo...', Array) 2013-12-07T15:07:59+00:00
Fatal index Exception: 2 /var/www/lib/private/backgroundjob/joblist.php(71): OC_DB_StatementWrapper->execute(Array) 2013-12-07T15:07:59+00:00
Fatal index Exception: 1 /var/www/lib/private/db/statementwrapper.php(70): Doctrine\DBAL\Statement->execute(Array) 2013-12-07T15:07:59+00:00
Fatal index Exception: 0 /var/www/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Statement.php(140): Doctrine\DBAL\DBALException::driverExceptionDuringQuery(Object(PDOException), 'SELECT id FRO...', Array) 2013-12-07T15:07:59+00:00
Fatal index Exception: An exception occurred while executing 'SELECT id FROM oc_jobs WHERE class = ? AND argument = ?': SQLSTATE[42S02]: Base table or view not found: 1146 Table 'owncloud.oc_jobs' doesn't exist 2013-12-07T15:07:59+00:00

php error log:

[Sat Dec 07 16:08:40 2013] [error] [client 192.168.178.26] PHP Warning: Cannot modify header information - headers already sent by (output started at /var/www/lib/private/eventsource.php:71) in /var/www/lib/private/user/session.php on line 170, referer: http://192.168.178.32/index.php
[Sat Dec 07 16:08:40 2013] [error] [client 192.168.178.26] PHP Warning: Cannot modify header information - headers already sent by (output started at /var/www/lib/private/eventsource.php:71) in /var/www/lib/private/user/session.php on line 171, referer: http://192.168.178.32/index.php
[Sat Dec 07 16:08:40 2013] [error] [client 192.168.178.26] PHP Warning: Cannot modify header information - headers already sent by (output started at /var/www/lib/private/eventsource.php:71) in /var/www/lib/private/user/session.php on line 172, referer: http://192.168.178.32/index.php
[Sat Dec 07 16:08:40 2013] [error] [client 192.168.178.26] PHP Warning: Cannot modify header information - headers already sent by (output started at /var/www/lib/private/eventsource.php:71) in /var/www/lib/private/user/session.php on line 175, referer: http://192.168.178.32/index.php
[Sat Dec 07 16:08:40 2013] [error] [client 192.168.178.26] PHP Warning: Cannot modify header information - headers already sent by (output started at /var/www/lib/private/eventsource.php:71) in /var/www/lib/private/user/session.php on line 176, referer: http://192.168.178.32/index.php
[Sat Dec 07 16:08:40 2013] [error] [client 192.168.178.26] PHP Warning: Cannot modify header information - headers already sent by (output started at /var/www/lib/private/eventsource.php:71) in /var/www/lib/private/user/session.php on line 177, referer: http://192.168.178.32/index.php

---

hermann

[DeepDiver1975]

Fatal index Exception: An exception occurred while executing 'SELECT id FROM oc_jobs WHERE class = ? AND argument = ?': SQLSTATE[42S02]: Base table or view not found: 1146 Table 'owncloud.oc_jobs' doesn't exist - Verursacht durch: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'owncloud.oc_jobs' doesn't exist[42S02] 2013-12-07T15:07:59+00:00

@bartv2 oc_jobs reminds me of you again 😉 - please comment on this - THX

[karlitschek]

related? #6298

[BernhardPosselt]

I ran into the same issue and its not related

[BernhardPosselt]

quick fix for people upgrading and stuck, excecute this for mysql database setups:

mysql -uUSER -p

use DATABASE_NAME;

CREATE TABLE oc_jobs (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    class VARCHAR(255) NOT NULL,
    argument VARCHAR(255) NOT NULL,
    last_run INT
);

[BernhardPosselt]

also confirmed for sqlite btw, added the tags. Did not test the other dbs but most likely same result

[tneo]

With SQLite add the table with the following statement:

CREATE TABLE "oc_jobs" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "class" VARCHAR(255) NOT NULL, "argument" VARCHAR(255) NOT NULL, "last_run" INTEGER);

Edit the config.php and set version number back to 5.0.13 to get the update screen again. (suggested in #6190 (comment))

Click refresh, it will update the database.

[karlitschek]

duplicate of #6330

[PVince81]

I suspect this might be related to the table prefix.
I had a config file with sqlite as DB and "oc_" as table prefix and it failed the upgrade with "oc_jobs not found".
Then starting from scratch with a brand new config.php (without the prefix), it worked.

Will confirm that soon.

[PVince81]

I can confirm that I have two config files that, when setting installed to false, one causes our issue, the other one doesn't.
I'm posting them here now... for science! While I try different combinations:

Here comes the broken one:

<?php
$CONFIG = array (
  'instanceid' => 'oc94227ced66',
  'apps_paths' => 
  array (
    0 => 
    array (
      'path' => '/srv/www/htdocs/owncloudtest/apps',
      'url' => '/apps',
      'writable' => true,
    ),
    1 => 
    array (
      'path' => '/srv/www/htdocs/owncloudtest/apps2',
      'url' => '/apps2',
      'writable' => true,
    ),
  ),
  'passwordsalt' => '*** Removed for security reasons',
  'datadirectory' => '/srv/www/htdocs/owncloudtest/data',
  'dbtype' => 'sqlite3',
  'version' => '5.0.24',
  'dbname' => 'owncloudtest',
  'dbhost' => 'localhost',
  'dbtableprefix' => 'oc_',
  'dbuser' => 'owncloud',
  'dbpassword' => '*** Removed for security reasons',
  'installed' => false,
  'theme' => '',
);

And here is the working one:

<?php
$CONFIG = array (
  'instanceid' => '52aa02aab373f',
  'passwordsalt' => 'd1b0876d5f7598e80bf74db82fd81f',
  'datadirectory' => '/srv/www/htdocs/owncloudtest/data',
  'dbtype' => 'sqlite3',
  'version' => '5.0.24',
  'installed' => true,
  'theme' => '',
  'maintenance' => false,
  'apps_paths' => 
  array (
    0 => 
    array (
      'path' => '/srv/www/htdocs/owncloudtest/apps',
      'url' => '/apps',
      'writable' => true,
    ),
    1 => 
    array (
      'path' => '/srv/www/htdocs/owncloudtest/apps2',
      'url' => '/apps2',
      'writable' => true,
    ),
  ),
);

[PVince81]

Now starting with the broken config, tested this:
Removing all fields that start with "db": migration works!
Aha! Getting closer...

Could you guys verify your config.php and check whether you also have those extra "db" fields ?
That would help confirming that it's all the same issue.

[PVince81]

It's getting ridiculous.
With the "ok" config the migration works every time.
With the "broken" config it never works.
When I change the "broken" config and remove all "db" fields except "dbtype", it also doesn't work any more!

I tried each like 5+ times.

So now it seems the only difference between "ok" config and "broken->fixed" config is the order of the config fields. I'll try changing those...

Here are the databases from each case:
https://owncloud.vincentpetry.net/public.php?service=files&t=30c9fcd80c818a498739fc2542aa2c77

It could be a race condition...

[PVince81]

Ok, now I've aligned the "ok" config with the "broken->fixed" config (removed the "db" fields except "dbtype").
The only diff I see is that the "ok" config has an explicit "maintenance" => false
And the "ok" config has an "oc" prefix in the instance id...

OUCH, still fails.

[PVince81]

Whaaaaa !?

I removed the "oc" in the front of the "instanceid" in the broken config, and now the migration works!

[PVince81]

Removing the "oc" at the time where the error message appears is already too late...
Also, removing it before the migration doesn't fix it.
It seems the "oc" needs to be removed before the initial setup of OC5...

Probably need to debug in the code where instance id is used and see what's going on.

[PVince81]

When I clear the cache between every test I can get consistent results:
With "oc": broken, Without "oc": works.

When "oc" is set, the DB creation time seems to be longer.
And then instead of logging me in OC5 directly it asks for a login again.

[PVince81]

Interesting, I've done the following:

1. Clear cache + cookies
2. Setup OC 5
3. Log out, clear cache + cookies
4. Upgrade code to OC 6
5. Run upgrade in browser

It works, regardless of whether the instanceid has a "oc" prefix.
(tried it three times)

If I don't log out but only clear the cache by being in the files view, refreshing the browser page will make it fail, as the URL is "index.php/files/". Maybe it requires me to open the "index.php" to make it work instead ?

Could there be any remaining value in the session that messes up with the upgrade !?

[PVince81]

Ignore everything I've written before, just found out something:

Do not use "http://localhost/owncloudtest/index.php/apps/files" or any other path when upgrading.
Use "http://localhost/owncloudtest/index.php" directly (or without "index.php")

If I manually go to "index.php" after getting the error, I get the login page and ownCloud works.

@hdering @Raydiation @eMerzh what is the URL appearing in your browser bar when you got the error ?

I suspect that some apps might be trying to accessing things when they see "apps/files" in the URL which can't work before the migration is complete.

[hdering]

I tried now to reproduce the failure but in my test area it works (VM).
I think in my vServer is something different.

In my test area I get only follow errors (but the update procedure worked. he said database was correct and he switched me to the login page):
Error core can't remove app files_mv. It is not installed. 2013-12-12T22:01:11+00:00
Error core App "" (files_mv) can't be used because it is not compatible with this version of ownCloud 2013-12-12T22:01:11+00:00
Error core can't remove app files_odfviewer. It is not installed. 2013-12-12T22:01:11+00:00
Error core App "" (files_odfviewer) can't be used because it is not compatible with this version of ownCloud 2013-12-12T22:01:11+00:00
Error core can't remove app admin_migrate. It is not installed. 2013-12-12T22:01:11+00:00
Error core App "" (admin_migrate) can't be used because it is not compatible with this version of ownCloud 2013-12-12T22:01:11+00:00
Error core can't remove app user_migrate. It is not installed. 2013-12-12T22:01:11+00:00
Error core App "" (user_migrate) can't be used because it is not compatible with this version of ownCloud 2013-12-12T22:01:11+00:00
Error core can't remove app files_imageviewer. It is not installed. 2013-12-12T22:01:11+00:00
Error core App "" (files_imageviewer) can't be used because it is not compatible with this version of ownCloud 2013-12-12T22:01:11+00:00
Fatal index Exception: An exception occurred while executing 'SELECT id FROM oc_jobs WHERE class = ? AND argument = ?': SQLSTATE[42S02]: Base table or view not found: 1146 Table 'owncloud.oc_jobs' doesn't exist - Verursacht durch: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'owncloud.oc_jobs' doesn't exist[42S02] 2013-12-12T22:00:52+00:00
Fatal index Exception: 11 {main} 2013-12-12T22:00:52+00:00
Fatal index Exception: 10 /var/www/index.php(30): OC::handleRequest() 2013-12-12T22:00:52+00:00

[PVince81]

@hdering what URL did you open when doing the upgrade ? This is important.

[PVince81]

Problems also occurs on another machine, with MySQL instead of SQlite.
I'll try to find out what else is executed when opening http://localhost/owncloud/index.php/apps/files for the migration instead of http://localhost/owncloud/index.php

[hdering]

@PVince81 I opened http://192.168.188.25 without index.php
I test it now what happens when I open /index.php/apps/files

[hdering]

@PVince81

[PVince81]

When running a successful upgrade, the oc_jobs error is still there. The error itself is misleading and let people believe that it's the cause but it's not. By reading this code it seems to be expected for oc_jobs calls to fail on upgrade:

in lib/base.php:

    public static function registerCacheHooks() {
                if (OC_Config::getValue('installed', false)) { //don't try to do this before we are properly setup
                        // register cache cleanup jobs
                        try { //if this is executed before the upgrade to the new backgroundjob system is completed it will throw an exception
                                \OCP\BackgroundJob::registerJob('OC\Cache\FileGlobalGC');
                        } catch (Exception $e) {

                        }

[hdering]

I am very sure that I have entered directly index.php on my production system.

Maybe it has to do something with the open session?

What is even more different to productive sytem und test system:

• productive system: forcessl => true and https//
• test system => forcessl = false and http://

[PVince81]

@hdering thanks for the info.
In this ticket I'll mostly investigate the issue with the "index.php" and non-"index.php".
Let's hope this can also fix your other issue.
If not, we'll need to look into this separately.

[PVince81]

@hdering can you try setting forcessl = true on your test system, just to make sure it's not related ?

[karlitschek]

:-)

[PVince81]

It seems the OC.webroot is only wrong on the update page.
When testing it inside other apps it appears correctly. Strange.

[PVince81]

Here it is:
In normal non-migration mode, a file config.js is loaded with the correct oc_webroot.
In migratin mode, the config.js is not loaded, so js.js has a fallback for that. The fallback is BROKEN

[PVince81]

Fix for webroot in above PR.

I'll see whether we can prevent the oc_jobs log message when that exception is expected (as per #6294 (comment))

CC @icewind1991 who seem to have written that part.

[hdering]

@PVince81 If you give me a new version of oc6 I can test it directly :)

[icewind1991]

#6392 prevents the oc_jobs message

[fmjrey]

I'm glad to hear this bug has been tracked down, I see how tedious this was.
Now for those of us that don't really hack owncloud, and have been a victim of a failed upgrade from v5, how can we fix our install?
In my case this has been happening ubuntu 12.04, using pgsql, and using the ubuntu update manager (apt), for upgrading. My browser session certainly reloaded the file app and not the main index page.
Thanks for any help.

[PVince81]

@fmjrey have you tried just manually opening the "index.php" (not the files app) URL to complete the migration ?

[icewind1991]

can this be closed now #6392 and #6390 are merged?

[PVince81]

Yes, closing.

[MoriTanosuke]

I just encountered this error again while upgrading from 5.0.13 to 6.0.0.14. Maybe still open?

[PVince81]

@MoriTanosuke can you please give more details ?

[madmas]

Same here. Just got

An exception occurred while executing 'SELECT "id" FROM "oc_jobs" WHERE "class" = ? AND "argument" = ?':
SQLSTATE[42P01]: Undefined table: 7 FEHLER: Relation »oc_jobs« existiert nicht LINE 1: 
SELECT "id" FROM "oc_jobs" WHERE "class" = $1 AND "argument"... ^

while trying to upgrade to 6.0.0a :(
Database Postgresql , System Ubuntu

Update: Even after try to work-around this by manually creating the oc_jobs table, the next error I get is

An exception occurred while executing 'SELECT "fileid", "storage", "path", "parent", "name", "mimetype", "mimepart", 
"size", "mtime", "storage_mtime", "encrypted", "unencrypted_size", "etag" FROM "oc_filecache" 
WHERE "storage" = ? AND "path_hash" = ?': SQLSTATE[42703]: Undefined column: 7 
FEHLER: Spalte »storage_mtime« existiert nicht LINE 2: "storage_mtime", "encrypted", "unencrypted_size", "e... ^

...and last update for tonight: I realized that I didn't update to the latest 5x relese. SO I upgraded to 5.0.14a, which went smooth. Then, again try to upgrade to 6.0.0a, and this time:

An exception occurred while executing 'ALTER TABLE "oc_appconfig" DROP CONSTRAINT IF EXISTS
appconfig_appid_key_index': SQLSTATE[42601]: Syntax error: 7 FEHLER: Syntaxfehler bei 
»EXISTS« LINE 1: ALTER TABLE "oc_appconfig" DROP CONSTRAINT IF EXISTS appconf... ^

Let me know if I can provide any futher information. I would be happy for any hint to upgrade my installation to OC6. (also see http://forum.owncloud.org/viewtopic.php?f=26&t=18497)

[PVince81]

@madMarkus I see you're using Ubuntu 10.04, I wonder whether it's related.
A few weeks ago I had a test on openSUSE 13.1 with Postgres 9.2.
You seem to have 8.4.19, might be too old ?

i'll have a try on Ubuntu 13.10 with Postgres 9.1, upgrading from 5.0.14 to 6.0.0a.

[PVince81]

@bantu any idea about the above SQL syntax error and Postgresql version ?

[bantu]

@PVince81 Yes. IF EXISTS is not valid 8.4 syntax. Compare http://www.postgresql.org/docs/9.0/static/sql-altertable.html to http://www.postgresql.org/docs/8.4/static/sql-altertable.html

[PVince81]

Ok thanks @bantu
So we need to update the documentation and require postgres >= 9.0 ?

[bantu]

@PVince81 That would probably be a valid solution. Not sure whether PostgreSQL 8.4 support is that big of a goal. @karlitschek

[PVince81]

I've just tested migrating from OC 5.0.14a to OC 6.0.0a on Ubuntu 13.10 with PostgreSQL 9.1 with a few test data and it works fine with that version.

[karlitschek]

@bantu Unfortunately I can't answer that. Does someone know how common 8.4 is?

[bantu]

Here are some data points:
PostgreSQL 8.4 will be EOL in July 2014.
Ubuntu 10.04 LTS is fairly old and has PosgreSQL 8.4.
Ubuntu 12.04 LTS has PostgreSQL 9.1.
Debian Squeeze (oldstable) has 8.4. too
Debian Wheezy (stable) has 9.1.
ArchLinux has 9.3 or higher.
Gentoo does not seem to expose package search to the web, but probably has a very high version available.

[bantu]

Fedora 18 has 9.2
Fedora 19 has 9.2
Fedora 20 has 9.3

[lock]

This thread has been automatically locked since there has not been any recent activity after it was closed. Please open a new issue for related bugs.