Page URL primary key too long for at least one host

[dashifen]

Greetings! For what it's worth, the Pantheon hosting environment prevents primary keys at the length specified for the page_url column in the *_ed11y_urls table. I debated adding this to a comment on Issue 18 but I wasn't sure if the enhancement you envision there to the issues table would ripple "backwards" to the urls one.

From Pantheon's explanation:

Pantheon's database does default to a multibyte utf8 character, and we do have a maximum index column size 
of 767 bytes which may be a bit smaller than some other hosts. If a plugin/theme tries to create an index for a 
longer field, it does throw that error you noted. So if each character is 4 bytes, then any VARCHAR field that 
wants to be indexed has to be probably less than 190 characters long.

I recognize that catering to a specific host isn't really a thing y'all need to do, but I thought I'd let you know in case it's useful as you're re-thinking database layout for other issues.

[itmaybejj]

That's...interesting. Does it fail to install, or just throw a warning?

I'm asking because we are running this on a lot of Pantheon sites, and I'm wondering if I should go look at the logs.

[dashifen]

Throws a warning on activation:

WordPress database error Index column size too large. 
The maximum column size is 767 bytes.

Which I followed to this page in their documentation about the maximum index size. Then, I opened a support ticket with them because we have also gotten it working on a handful of sites. It was in that support ticket that they made the above quote. It sounds like maybe it does work sometimes, but not other times. And, if it works once and the tables are created, then it'll keep working forever. The unfortunate thing is that, through my investigations, they may discover why it works from time to time and shore up whatever weakness allows it to function intermittently.

But, based on our research, we've tried activating the plugin on about 30 sites and it's worked on 2 of them. The plugin still appears to be active on the site when this happens, but the database tables do not get created, so I suspect that its operation is severely impaired.

[itmaybejj]

Oooof. Ok I'll need to rewrite the tables then. It's...not trivial, so it may be late spring / early summer. Fortunately I have the official green light to throw hours at this, so it will happen soonish.

[dashifen]

Happy to offer some of my own time if that's a thing that might be helpful to you. Also: I wonder if you might have considered just storing your data as post meta in the default WP tables? I haven't dug in on your code yet, but that might be a way to avoid the need for custom database tables entirely but with the added complexity of having to work with WP's concepts of what is a post and its post meta.

[itmaybejj]

I aaaaaaaalways welcome advice, tips, patches and pull requests.

I was trying to avoid relying on post ID as my primary key, since there are other route destinations other than posts and I need some way to track those as well, especially on random websites with random other plugins creating custom "things."

I'll have to study the actual DB tables to see if there is some way I can reliably store and retrieve information about arbitrary non-post entities other than URL. And do another search to see if there are any other plugins out there trying to do something like this, and what they've done.

[dashifen]

I'm out here, even if you just want to verbally process at someone who's well versed in the WP way of doing things.

[itmaybejj]

Had a facepalm this weekend -- I'm only long by a few characters, and I don't really need them, so I can just tweak the requested column size for now.

Hopefully 1.0.14 works for you...

[dashifen]

Oh! Great :) A quick test on a site where it didn't work before indicates that the plugin activated successfully without errors. Does this preclude your plans to re-work the databases, or might that still hapen?

[itmaybejj]

Splendid!

I definitely plan to keep working on the database structure. At a minimum I want to log the the Post Id when available so I can add deletion hooks. That and a proper multisite config page are on the docket next.

[dashifen]

Awesome. Thanks for this intermediate step, regardless!!