Did Something Stupid, tried to upgrade, lost Contacts and Calendar. Need help

ℹ️ Support
,
1

Hi, I really need your help.

First of all, I am the one to blame… Shame on me, but please help me get my data back.

My OC was running fine since Version 9.x. A few days ago I tried to complete some of the recommended “Optimizations” like switching to utf8. I clearly did something wrong which caused an app to fail (not sure which one it was). I kicked in the Upgrade to 18 (was on 17.0.x) as I was hoping it will fix itself. It did not and resulted in an error after upgrade:

[code] Doctrine\DBAL\Exception\DriverException: An exception occurred while executing ‘CREATE TABLE oc_migrations (app VARCHAR(255) NOT NULL, version VARCHAR(255) NOT NULL, PRIMARY KEY(app, version)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin ENGINE = InnoDB ROW_FORMAT = compressed’: SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes

Detaillierte Protokollmeldungen

Update vorbereiten

Log-Level auf “debug” gesetzt

Wartungsmodus eingeschaltet

Reparaturschritt: Repair MySQL collation

Reparaturinformation: All tables already have the correct collation -> nothing to do

Reparaturschritt: Repair SQLite autoincrement

Reparaturschritt: Copy data from accounts table when migrating from ownCloud

Reparaturschritt: Drop account terms table when migrating from ownCloud

Das Datenbankschema wird aktualisiert

Doctrine\DBAL\Exception\DriverException: An exception occurred while executing ‘CREATE TABLE oc_migrations (app VARCHAR(255) NOT NULL, version VARCHAR(255) NOT NULL, PRIMARY KEY(app, version)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin ENGINE = InnoDB ROW_FORMAT = compressed’: SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes[/code]

This error is shown when trying to do the app updates to 18

I thought it’s easier to setup a new Instance as there isn’t much data on it anyway. So New DB and new files (I kept all the old files and stuff)
After the install I tried to connect my phone to OC using DAVx5, this resulted in an 404 error so I liked it again which seem to work, but wiped my calendar and my Contacts :frowning: :frowning: :frowning: :frowning: :frowning:

Is there any way to get my Data back ? As said I still got the old DB and Files.

Let me know what kind of other infos you need.

Any help is very much appreciated :frowning: :sad:

2

First of all, don’t worry. I’m pretty sure your data isn’t lost :slight_smile:
What Database + Version do you use?

Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes

As far as I know this could indicate an outdated mariadb version. Stumbled upon this in several github issues.

1 Like
3

Hi, thanks for your help.

It’s MySQL 5.6.44

To me it sounds that some Values in the DB are set incorrectly. I mean I was editing the SQL structure and had to update some vchars to lower then 255. The recommendation was 191 and I guess I did edit the/a wrong value

Edit: I just saw that the old DB was set to utf8mb4_bin while the new created one is set to utf8_bin

4

Just to explain. The OC installation is currently awaiting App updates as shown below. If I click the button, the error as Post #1 is shown

765×1199

5

Any further ideas?

6

Well you said you switched your database to utf8 - I guess from a latin1_xxxx (?)
In our place we want to switch an other software from nonunicode to unicode (utf8) database and the database guys told us that it will be dangerous because the length of the fields will change (become longer) - thats exactly what your error said:

Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes[/code]

This means your field definitions is too small(!)
So in my opinion you have 2 possibilities:

  1. go back to latin1_xxx or whatever was set before.
  2. adjust every column definition tat is complaining with a bigger value (in most cases VarChar Columns)
1 Like
7

Could the solution shown here be of any help to you? I know it’s MariaDB, but they are quite similar.

And some reference from the mysql 5.6 docs:
https://dev.mysql.com/doc/refman/5.6/en/innodb-limits.html

1 Like
8
  1. Did not work, because when I kicked in the Upgrade, it converted it back to utf8mb4_bin. Any idea how I prevent this ?
    2 . How do I identify the column required to change ?
9

I do not have access to the command line. I had via OCC, but this isn’t available till the system is running again :frowning:

10

Do you have access to a DB Tool like phpmyadmin ?

11

Yes, I got access to this

12

Then use phpmyadmin to type in the commands in the answer of @bpcurse
This should fix at least the DB

13

And take a look here:
https://dev.mysql.com/doc/refman/5.6/en/dynamic-system-variables.html

14

I think I got it fixed (messy way).

  1. Did a backup of the Original DB
  2. Moved the Original Files from the Web server into a secure location.
  3. Imported the Backup into a new DB
  4. Installed a brand new NC 18 into a new web space location and new DB
  5. Moved the file from the new NC18 Installation to the location of the original installation (the one in 2.)
  6. Pointed the config file to the new DB from 3
  7. Ran the update. It gave me 30 - 40 messages like the one below about existing rows, tables and indexes (I guess). I bloody removed them all :smiley:
    after 20 Minutes of deleting and praying I’m in !!! F*k Yeah !
    All my Contacts are there and the calendar looks fine. The shared Calendar account looks fine too. Wives Account is working too … I’m close to tears !

Now the tricky question, how do I migrate the data back to my phone(s) ? Cos I do not want to work any much longer with this messy system, I will move to a brand new Install. So how do I migrate data from one system to another ? Ideas ?

Thank you so much for all your ideas ! Very much appreciated. !

866×422

1 Like
15

If the latest version of the data (contacts and calendar) is in the server db and you did not perform any changes/additions on the phones, then you could probably simply delete the old connection(s) and data on the phones. And reconnect them to the new instance. Then the data should be synchronized automatically to the phones.
Sounds easy, but maybe I’m overlooking something. So be careful (but your data is backup’ed now as I understand).

16

Not sure this works.
Using DAVx5, as soon as I remove a connection, the contacts / calendar dissapear.
I try to add another link and see how this goes, not sure, will keep you guys posted.

17

The Calendar entries can be moved form one calendar to the next by using ACalendar+
The Contacts would need to get moved by a contact managing tool. Not sure which one yet.