DB upgrade failed (owncloud 6 to 7 on debian sid)

[slallemand]

I'm facing this issue when upgrading mysql database on my owncloud server :

An exception occurred while executing 'ALTER TABLE oc_oc_ldap_user_mapping_10b72ceced664 DROP PRIMARY KEY': SQLSTATE[42000]: Syntax error or access violation: 1091 Can't DROP 'PRIMARY'; check that column/key exists

In owncloud.log :

{"app":"core","message":"Simulated database structure update failed (exception 'OC\\DB\\MigrationException' with message 'An exception occurred while executing 'ALTER TABLE `oc_oc_ldap_user_mapping_1819adc55536e` DROP PRIMARY KEY':\n\nSQLSTATE[42000]: Syntax error or access violation: 1091 Can't DROP 'PRIMARY'; check that column\/key exists' in \/usr\/share\/owncloud\/lib\/private\/db\/migrator.php:115\nStack trace:\n#0 \/usr\/share\/owncloud\/lib\/private\/db\/migrator.php(75): OC\\DB\\Migrator->checkTableMigrate(Object(Doctrine\\DBAL\\Schema\\Table))\n#1 \/usr\/share\/owncloud\/lib\/private\/db\/mdb2schemamanager.php(110): OC\\DB\\Migrator->checkMigrate(Object(Doctrine\\DBAL\\Schema\\Schema))\n#2 \/usr\/share\/owncloud\/lib\/private\/db.php(337): OC\\DB\\MDB2SchemaManager->simulateUpdateDbFromStructure('\/usr\/share\/ownc...')\n#3 \/usr\/share\/owncloud\/lib\/private\/updater.php(195): OC_DB::simulateUpdateDbFromStructure('\/usr\/share\/ownc...')\n#4 \/usr\/share\/owncloud\/lib\/private\/updater.php(137): OC\\Updater->doUpgrade('7.0.0.8', '6.0.4.1')\n#5 \/usr\/share\/owncloud\/core\/ajax\/update.php(35): OC\\Updater->upgrade()\n#6 {main})","level":4,"time":"2014-07-25T08:32:51+00:00"}

Any idea ?

Regards

[PVince81]

@blizzz

[DeepDiver1975]

@slallemand which database are you using?

[slallemand]

MariaDB 5.5

[slallemand]

Seems I find a solution for my problem.
I had changed the file apps/user_ldap/appinfo/database.xml and remove "true" entries.
After that, upgrade is ok.

[karlitschek]

@blizzz any idea what could be wrong here ?

[slallemand]

My copy/past has failed ... I had to removed those entries :

<primary>true</primary>

[blizzz]

@slallemand did you ever manually change something in oc_ldap_user_mapping ?

[slallemand]

Nothing I can remember.

[blizzz]

do you still have the old table somewhere available? could you give us the structure before the migration? The SQL command would be

DESCRIBE oc_ldap_user_mapping;

[DavidPrevot]

Hi blizzz, you'll find following the structure before and after the
attempt (current).

CREATE TABLE `oc_ldap_user_mapping` (
   `ldap_dn` varchar(255) NOT NULL DEFAULT '',
   `owncloud_name` varchar(255) NOT NULL DEFAULT '',
   `directory_uuid` varchar(255) NOT NULL DEFAULT '',
   UNIQUE KEY `ldap_dn_users` (`ldap_dn`),
   UNIQUE KEY `owncloud_name_users` (`owncloud_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Current table structure

CREATE TABLE `oc_ldap_user_mapping` (
   `ldap_dn` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
   `owncloud_name` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
   `directory_uuid` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
   UNIQUE KEY `ldap_dn_users` (`ldap_dn`),
   UNIQUE KEY `owncloud_name_users` (`owncloud_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

[blizzz]

Well, there is no primary key, but unique keys. Anyway, why would we attempt to drop it, the KEY descriptions are not different. Any idea @butonic ?

[butonic]

the primary keys were added by @DeepDiver1975 in 8661fe3 as part of fixing #8252. My guess is that the migration hiccups in this case ... @DeepDiver1975 do you have any tips to get more info? Some more logging that can be enabled?

[blizzz]

@DeepDiver1975 any tips?

[butonic]

@blizzz @DeepDiver1975 @icewind1991 this smells a little like migration hiccupping like in #10980. However, user_ldap does not try to use a preupdate.php

[DeepDiver1975]

Afair there has been a migration unit test fir exactly this case. No clue why this is now failing.

[Ange7]

Same problem here. Someone found any tips ?

[VicDeo]

Possible duplicates
#10033
#10798

[jonathanvaughn]

Any news on this? We're hesitant to try upgrading to 7 as this would be a show stopper for us.

[Natureshadow]

It is incredible that the ownCloud devs leave this issue unfixed for so long. Did they realise that there are two major versions that users could not update to by now?

[PVince81]

@Natureshadow @jonathanvaughn @Ange7 feel free to help providing more information about this issue to push it forward. Tell us about your environment and answer @blizzz's questions above about the LDAP uuid. Here is the issue template: https://raw.githubusercontent.com/owncloud/core/master/issue_template.md

Many people have been able to upgrade to OC 7 already who didn't have this issue, it doesn't affect every setup (even with LDAP). This is why the more information about the issue/environemt/situation, the more likely the devs will be able to reproduce the issue and eventually fix it.

Thanks!

[PVince81]

Do you all use mariadb ?

[Pete1979]

Hello,

I have OC 8.0.2 on a ubuntu 14.04 with MySQL. When i upgraded from version 7 to 8 i disabled the apps (LDAP and others) to get the upgrade from 7 to 8 to work, so now i have a working OC 8. But LDAP inactive, when i activate it, owncloud wants to update, but fails with the messege

"An exception occurred while executing 'ALTER TABLE oc_oc_ldap_user_mapping_c590aed7a1e5d DROP PRIMARY KEY': SQLSTATE[42000]: Syntax error or access violation: 1091 Can't DROP 'PRIMARY'; check that column/key exists"

Im not very good at SQL, but i can follow instructions, anyone got some?

Thanks

Best regards
Peter

[PVince81]

@blizzz @icewind1991 any idea ?
Maybe the Doctrine migration is trying to do something incompatible.

[bremensaki]

I encountered this issue when upgrading a system from OC6 to OC8, and think I've noticed a few things that might be relevant.

I combined the upgrade with a server move, so I used mysqldump to bring my DB over from one server to the new one. My dump file looked a lot like #9893 (comment) at this point, two UNIQUE KEY entries and no PRIMARY KEY.

But checking the source database, my "oc_ldap_user_mapping" table has ldap_dn specified as PK, where the database.xml specifies that owncloud_name should be primary.

mysql> describe oc_ldap_user_mapping;
+----------------+--------------+------+-----+---------+-------+
| Field          | Type         | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| ldap_dn        | varchar(255) | NO   | PRI |         |       |
| owncloud_name  | varchar(255) | NO   | UNI |         |       |
| directory_uuid | varchar(255) | NO   |     |         |       |
+----------------+--------------+------+-----+---------+-------+

mysql> show create table oc_ldap_user_mapping;
+----------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table                | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                     |
+----------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| oc_ldap_user_mapping | CREATE TABLE `oc_ldap_user_mapping` (
  `ldap_dn` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `owncloud_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `directory_uuid` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  UNIQUE KEY `ldap_dn_users` (`ldap_dn`),
  UNIQUE KEY `owncloud_name_users` (`owncloud_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
+----------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

This is also the structure created on the new server from the dump file.

I manually edited my mysqldump file, changed:

    UNIQUE KEY `ldap_dn_users` (`ldap_dn`),
    UNIQUE KEY `owncloud_name_users` (`owncloud_name`)

to:

    PRIMARY KEY (`owncloud_name`),
    UNIQUE KEY `ldap_dn_users` (`ldap_dn`)

After reloading this new dump with explicit PK definitions in place to match #8252 then the upgrade proceeded flawlessly.

[blizzz]

OC6 to OC8

You must not got from OC 6 to 8 directly, but to OC 7 first. Skipping major versions is neither supported nor tested.

[bremensaki]

Is that likely to be part of this issue? I hit this problem when I first tried upgrading 6 to 7, from memory, but didn't have time to investigate. I'm only going to 8 directly now as that's what the repos are delivering to me - the test system is working fine after the upgrade though, it's really file sharing only, very few addons.

I have only done a test move on a copy of the existing system. My original OC6 system is still in production, so I can do another run at 7 if I can locate the right set of RPMs and see if the same issues occur.

[PVince81]

Is this still happening when upgrading from OC 6 to 7.0.7 or OC 7 to 8.0.5 ?

[PVince81]

@bremensaki you can get the 7 RPMs here: https://software.opensuse.org/download/package?project=isv:ownCloud:community:7.0&package=owncloud
and the 8.0 here https://software.opensuse.org/download/package?project=isv:ownCloud:community:8.0&package=owncloud

In any way, major versions should not be skipped.

If you're still having trouble despite this, feel free to reopen.