Path is limited to 255 chars

[dantleech]

What happens when somebody creates a path which is longer than 255 chars?

Looking at the schema, path is varchar(255)

[dbu]

write a test and find out :-P

i would expect at least mysql to just cut off after 255 chars. and indeed this sounds like a potential pitfall. at the very least we should throw a clear exception. not sure about performance implications if we take a higher value. oh, and seems newer versions of mysql can do longer varchar: http://stackoverflow.com/questions/13506832/what-is-the-mysql-varchar-max-size so maybe we should just make that configurable and default to a higher value.
no idea what the cost is (in terms of storage or read / write performance) of putting it to say 10k.

reading this, we should just use a TEXT field in postgres: http://www.postgresql.org/docs/8.3/static/datatype-character.html

for sqlite, it seems to not matter at all what we do - it already handles any reasonable path and quite a bit more, if this answer is correct: http://stackoverflow.com/questions/6109532/what-is-the-maximum-size-limit-of-varchar-data-type-in-sqlite

[lsmith77]

I think the issue is a limitation in MySQL and indexes. not sure if this limitation still exists but yeah, we should not make other RDBMS suffer ..

[dantleech]

For the record this came up in IRC when somebody was getting "the path already exists" errors, I assumed it was related to the fact that his path was 320 characters long and the first 255 characters were duplicated in some paths.

[dbu]

sounds plausible, yes.

[pavelvondrasek]

Hi,

I'm working on this issue and I examined how to enlarge varchar in MySQL with index.

I read that in MySQL v.5.5.14+ you can do this on varchar(1024):
SET @@global.innodb_large_prefix = 1
ALTER TABLE phpcr_nodes ADD INDEX(path)

global.innodb_large_prefix allows index size 3072.

In Postgres I read that text is same like varcher and there are no performance differences between these two types. I tried to create table with column, added index on it and all worked.

In SQLite I think that varchar size is unlimited: https://sqlite.org/faq.html#q9

Do you think that is better to use biggest available path size on database platform or be consistent between platforms?

I think we can do:

1. add option for MySQL to create/update schema with varchar(1024) if it technically possible; on Postgres use text and on SQLite text too (I think that varchar(n) is same like text) and use available path size.

or

2. Add option for MySQL to work with varchar(1024) and limit other platforms to make consistent size (such as if isn't allowed bigger path size so that all platforms work with 255 chars, if is allowed bigger size all platforms work with 1024 chars)

What do you think is better?

And in cases that path size is bigger than available path size we will throw exception before save.

[dbu]

sounds good. i think we should: - use text on postgres (found [this article](https://www.depesz.com/2010/03/02/charx-vs-varcharx-vs-varchar-vs-text/) that explains text and varchar uses the same C code in postgres). - use text on sqlite (according to the faq you linked, we could make it varchar(1) and it would behave the same, but TEXT is more explicit) - try to push mysql to what we can, and add a length check before we write paths to mysql i prefer having good functionality with postgres over forcing compatibility between all databases with the lowest common feature set :-) its however important that the documentation mentions the restriction for mysql. i geuss that can go here: https://github.com/jackalope/jackalope-doctrine-dbal#create-a-repository - and the changelog should explain that only version 1.4+ allows longer paths than 255. and provide a SQL statement to migrate postgres to TEXT

[pavelvondrasek]

Ok, tonight I will work on PHPCR\Utils tests to remove deprecations and skip on PHP7 unfunctional test but on the weekend I will prepare PR :-)