Migration issue OC 5.0.14a -> OC 5.0.15rc1 -> OC 6.0.2rc1

[PVince81]

Steps to reproduce

1. Install OC 5.0.14a from scratch (use MariaDB/MySQL)
2. Upload a few files, create shares, run a search (for file index), but I think it's not needed
3. Upgrade to OC 5.0.15rc1
4. Upgrade to OC 6.0.2rc1

Env

DB: MariaDB/MySQL
Server OS: openSUSE 13.1 x86_64

The following occurs:

An exception occurred while executing 'ALTER TABLE `oc_privatedata` CHANGE user `user` VARCHAR(255) DEFAULT '' NOT NULL, CHANGE app `app` VARCHAR(255) DEFAULT '' NOT NULL, CHANGE key `key` VARCHAR(255) DEFAULT '' NOT NULL, CHANGE value `value` VARCHAR(255) DEFAULT '' NOT NULL': SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'key `key` VARCHAR(255) DEFAULT '' NOT NULL, CHANGE value `value` VARCHAR(255) DE' at line 1

MariaDB [owncloud2]> describe oc_privatedata;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| keyid | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| user  | varchar(255)     | NO   |     |         |                |
| app   | varchar(255)     | NO   |     |         |                |
| key   | varchar(255)     | NO   |     |         |                |
| value | varchar(255)     | NO   |     |         |                |
+-------+------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

Doctrine mess up ?

CC @bantu @icewind1991 @karlitschek @DeepDiver1975

[PVince81]

I tried running the query directly:

ALTER TABLE `oc_privatedata` CHANGE user `user` VARCHAR(255) DEFAULT '' NOT NULL, CHANGE app `app` VARCHAR(255) DEFAULT '' NOT NULL, CHANGE key `key` VARCHAR(255) DEFAULT '' NOT NULL, CHANGE value `value` VARCHAR(255) DEFAULT '' NOT NULL

it gives the same error.

Now let's try and squint the eyes to find the syntax error...

[PVince81]

"key" is a reserved word and for some reason the column names appear twice, the first time without backticks. 😦

[PVince81]

I manually ran the query and added backticks to key and it worked.
The weird thing is that this alter table is completely useless as it doesn't change anything.
So why would Doctrine try and run this at all...

[PVince81]

Migration from 6.0.1 to 6.0.2rc2 works correctly, so it seems that Doctrine to Doctrine migration works fine.

The issue is when migrating from MDB2 to Doctrine that this buggy ALTER TABLE is done above.

Possible solution: patch Doctrine to add the missing backticks.

Else, need to find a way to rename that column in OC 5.0.15a and OC 6, but not sure about the consequences.

[PVince81]

CC @bartv2

[PVince81]

I just tried directly from OC 5.0.15rc1 to OC 6.0.2rc1 and the problem also appears, this narrows it down to a single version migration (should help reproduce it more quickly). I've updated the bug description.

[nickvergessen]

I vote for prefixing all columns with either oc_ or the table name (shorten version) in future, to avoid keyword problems

[DeepDiver1975]

@PVince81 confirmed - upgrade testing is showing the same error:

 ./test-upgrade.sh 5.0.15rc1 6.0.2rc1  mysql
....
Start upgrading from owncloud-5.0.15rc1.tar.bz2 to owncloud-6.0.2rc1.tar.bz2
Turned on maintenance mode
An exception occurred while executing 'ALTER TABLE `oc_privatedata` CHANGE user `user` VARCHAR(255) DEFAULT '' NOT NULL, CHANGE app `app` VARCHAR(255) DEFAULT '' NOT NULL, CHANGE key `key` VARCHAR(255) DEFAULT '' NOT NULL, CHANGE value `value` VARCHAR(255) DEFAULT '' NOT NULL':

SQLSTATE[42000]: Syntax error or access violation: 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 'key `key` VARCHAR(255) DEFAULT '' NOT NULL, CHANGE value `value` VARCHAR(255) DE' at line 1
Turned off maintenance mode

[DeepDiver1975]

I'll run this against the other databases as well

[DeepDiver1975]

pgsql is okay

[bartv2]

We add some quoting in lib/private/db/mdb2schemamanager.php#84 for ->name and i also think we need to add quoting to ->oldColumnName. can't test at the moment

[DeepDiver1975]

We add some quoting in lib/private/db/mdb2schemamanager.php#84 for ->name and i also think we need to add quoting to ->oldColumnName. can't test at the moment

@bartv2 can you submit a pull request? We can take care of testing - THX

[bantu]

The answer to why the name appears twice at all is because the ALTER TABLE syntax also supports renaming. See http://dev.mysql.com/doc/refman/5.5/en/alter-table.html
CHANGE [COLUMN] old_col_name new_col_name column_definition