Extend option_name to varchar(255)

[scribu]

option_name is currently set to varchar(64). This raises problems when one tries to use transients with slightly longer names and a timeout:

_transient_timeout_feed_mod_23a137101df6920fbf6047... has 60 chars already.

[wpmuguru]

This schema change would have a significant impact on large WP networks.

IMO, it should be punted to 3.1 to give admins of large installs an opportunity to implement the schema change prior to running the upgrade.

[nacin]

Yeah, no way we're rushing in a schema change. I'm not sure it's necessary, frankly.

Going to leave the unassigned milestone and tag for 2nd-opinion, but expecting wontfix.

[scribu]

Related: #15058

[chrisbliss18]

This isn't a bad idea. Especially the specific length.

The get_site_option function uses the options table when the site isn't multisite and uses the sitemeta table when it is multisite. This translates into a maximum name size of 64 characters (length of option_name) for site option storage for non-multisite and 255 characters (length of meta_key) for multisite.

[fabio84]

Wow, in 2 years you were not able to make option_name a varchar(255)... I tried to use get_transient and set_transient, but I got stuck by this "feature".

As reported also in #15058 with transient values the limit is 45 characters, and if you use hashes and long identifiers to avoid collisions it's quite easy to reach the limit.

Please excuse me for this rant, but developing WordPress plugins using its APIs sometimes is frustrating.

[tivnet]

Still not fixed in 2013!
Version: WP 3.6-beta3-24265

Example of the error:

[22-May-2013 22:29:06 UTC] WordPress database error
Data too long for column 'option_name' at row 1 for query
INSERT INTO `wp_options` (`option_name`, `option_value`, `autoload`)
VALUES ('_transient_timeout_woocommerce-gateway-paypal-express/woocommerce-gateway-paypal-express.php_version_data'

---

@SergeyBiryukov - varchar(255) - is that really hard to make? While 3.6 is still in beta...

[markoheijnen]

I do agree that we should fix it but the error you have is also stupid from WooCommerce or the PayPal plugin.

[tivnet]

Replying to markoheijnen:

I do agree that we should fix it but the error you have is also stupid from WooCommerce or the PayPal plugin.

Well, why this error is more stupid than that one from @Scribu, the original reporter? Anyway, I believe, there is no reason to keep varchar(64) these days, whether the transients are stupid or smart :-)

[SergeyBiryukov]

Replying to tivnet:

@SergeyBiryukov - varchar(255) - is that really hard to make? While 3.6 is still in beta...

It's simple on single site installs, but can be an issue on large network installs, as noted in comment:1.

We're in late beta, when lots of things are being punted to a future release. Anything that requires a schema change should be brought up early in the cycle.

[markoheijnen]

Replying to tivnet:

Replying to markoheijnen:

I do agree that we should fix it but the error you have is also stupid from WooCommerce or the PayPal plugin.

Well, why this error is more stupid than that one from @Scribu, the original reporter? Anyway, I believe, there is no reason to keep varchar(64) these days, whether the transients are stupid or smart :-)

The name of the transient is way too long. It's includes the plugin location of the plugin with the name. So for a fast fix you should contact WooCommerce team and see what they can do.

[tivnet]

• Replying to Sergey Biryukov:

We're in late beta, when lots of things are being punted to a future release. Anything that requires a schema change should be brought up early in the cycle.

Well, I guess this is a dead horse then. 3 years ago was not early enough :-)

• Replying to Marko Heijnen:

  ...contact WooCommerce team...
  

Started with that, but... it will probably take 5 years to Woo, if it takes 3 years to WP :-)

Thank you!

[ocean90]

#25035 was marked as a duplicate.

[tivnet]

Celebrating 4 years of this soon... MAZAL TOV! :-)))

[sc0ttkclark]

I'd really like to see this move forward, what's exactly the holdup? Is it the lack of use-cases for using longer option names? If that's the case, I'd think that most use-cases currently use bad hack arounds and just deal with it, and the lack of characters in option_name begets use-cases for longer character use.

[DrewAPicture]

In 28735:

Improve inline documention for set_transient() and set_site_transient() to specify the 45- and 40-character limits for their respective transient name values.

Props edwin-at-studiojoyo.com for the original patch.
See #15058, #13310. Fixes #28467.

[DrewAPicture]

Replying to sc0ttkclark:

I'd really like to see this move forward, what's exactly the holdup?

I'm assuming the primary holdup is the schema change. Moving to 4.0 for consideration.

[khromov]

Really hoping for a schema change!

The Network Update feature should make updating the schema a breeze, no?

[archon810]

Unbelievable that this ticket has now spent 4 years in discussions that went nowhere. I just ran into a bug that kept failing transients, and the culprit was the key name.

At the very least, the set_transient function should error out altogether, with a message of some sort when the key is known to get truncated.

I'm going to work on getting the keys smaller and I'll probably manually up the key length to 255.

Btw, it was quite easy to run into this issue by adding a domain to the transient key name, along with a few more things. I only caught this problem after looking at the database data.

[DrewAPicture]

To late for a schema change. Maybe 4.1-early will be the ticket.

[tivnet]

Replying to DrewAPicture:

To late for a schema change. Maybe 4.1-early will be the ticket.

This ticket is cursed...

How about moving transients to a separate table instead?

[OriginalEXE]

I believe now would be the time to bump this :)

[Hube2]

Yes please, since updating to 4.0 reset this field to a length of 64 on a site where I needed to increase it to 255 to make the site work. Which means that I need to either find a way to disable this reset or not upgrade the site. Why did 4.0 update mess with my tables?

[sc0ttkclark]

@Hube2 Upgrades run a DB delta check, so any inconsistencies will get fixed, or in our case, any customizations to the field lengths.

[OriginalEXE]

I'll see if we can maybe tackle this on contributors day #wceu

[archon810]

Odd. I changed mine to 255 4 months ago, and it's still 255, so it didn't get reset during the 4.0 update.

[Hube2]

I believe this will prevent it from happening ​http://codex.wordpress.org/Editing_wp-config.php#Do_not_upgrade_global_tables. Not sure it's the best temporary solution though.

@OriginalEXE, I'm sure not only I would be extremely grateful. There is a very popular plugin that saves values to the options table for fields added to terms as well as other admin pages. Due to the way that plugin generates field names "option_name" the length of them can grow quite long at times.

[OriginalEXE]

Updated patch

[OriginalEXE]

I have uploaded the updated patch. Now I just need to get this going with someone with commit access.

[jdgrimes]

#30048 was marked as a duplicate.

[akshay_raje]

Replying to jdgrimes:

#30048 was marked as a duplicate.

Didn't know this is 4 year old issue! Having a 64 character key with 11-19 characters reserved (for _transient_ or _transient_timeout_) really leaves little scope to use any hashes as keys.

Please target to fix this in next release.

[khromov]

@akshay_raje

Check: #15058

[Hube2]

For anyone in the same situation as myself that has altered options_name in the DB to allow 255 characters, I have also created a plugin that can be used to prevent WP from changing it back again. ​https://github.com/Hube2/wp-update-prevent-db-changes

Validation is not sufficient because as was just mentioned, 64 characters is simply not a sufficient size under all conditions.

[SergeyBiryukov]

#29879 was marked as a duplicate.

[nerrad]

If this isn't going to get fixed, then transients should get their own bonafide table. Which imo is a better option(!) anyways.

[Hube2]

Any word on this being in 4.1?

[nerrad]

If this isn't going to get fixed, then transients should get their own bonafide table. Which imo is a better option(!) anyways

Either that or add a timestamp column to the options table so that transients don't require two rows for each transient, which might be a good interim measure.

[tivnet]

Replying to nerrad:

If this isn't going to get fixed, then transients should get their own bonafide table. Which imo is a better option(!) anyways

Separate table, of course. Just TRUNCATE it to clear all transients!

[Hube2]

I really think that transients and putting in a new table is a completely separate discussion.

Changing option_name from varchar(64) to varchar(255) is really a rather simple schema change that should have no other effects on WP other then stopping the silent failure to get values from the database because the option_name value was too long and truncated when the value was saved. I just don't understand why this is 5 years old.

[nerrad]

Replying to Hube2:

I really think that transients and putting in a new table is a completely separate discussion.

Yeah true, however with the lack of traction that has gone on for this "simple" schema change maybe there's other things that should be done that will resolve the existing issue.

Either way, there really should be some feedback as to:

1. Why this has been lingering for 5 years (yeah network install WP sites is the given reason... but why?)
2. It's looking like this will NOT go in 4.1 because of where we are in the 4.1 release cycle... if not WHY? (which will likely lead to the answer for number 1.

So really, if a simple schema change is really not being considered.

1. What needs to change to make it considered?
2. Do we need to evaluate whether using the options table for transients is what exposed this problem to begin with. And if so, maybe that reveals the need for something different to be done for transients (which would yes warrant another ticket likely and then CLOSING this ticket that is getting no action).
3. OR as I also suggested, would adding a timestamp column to the options table and introducing a change for transients to use one row instead of two be a good interim measure both to incrementally fix the issue raised by this ticket and satisfy the holdup for making a "simple" schema change on an existing column?

[Hube2]

@nerrad

I completely agree.

What exposed this problem for me?

I use the plugin Advanced Custom Fields (ACF). For those not familiar with it, you can add custom fields to options pages. The values for options pages are stored in the options table. This is not a problem most of the time. The problem manifests itself when using nested repeater fields or flexible content fields because of the way that ACF generates the option_name values of these types of fields.

I didn't get into using transients until after I discovered the issue with ACF, and being already aware of the that issue I already knew I needed to be careful when generating the option_name for any transients I was creating.

But as you say, I would really like to understand why, what I think is an extremely simple fix, would cause any issues for any site, network or not. I have gone into many sites, both network sites and not, small sites and huge sites, and manually changed option_name from 64 to 255 and never had a problem. I've even created my own MU plugin that I install on these sites to prevent WP from changing it back again. So I'm really confused about what problems this could create and why it has been waiting 5 years with no action taken.

[khromov]

In hopes of getting this resolved for 4.2, I am attaching a full patch (against trunk) which covers both schema change for new installations as well as schema update for existing installations. I have tested this on multiple sites without any issues. Feedback welcome.

[khromov]

@scribu @ryan

Any chance to lift this discussion for 4.3? It's been punted a couple of times already so starting the discussion early could maybe help this get resolved (one way or another).

[Hube2]

they are asking for requests, already posted this one but people could 2nd, 3rd, 4th, whatever it. ​https://make.wordpress.org/core/2015/01/28/trac-tickets-the-bands-taking-requests/

[wonderboymusic]

@pento can speak to this one, and give his expert advice in some direction or the other

[pento]

Huh, I could've sworn I'd posted an opinion on this previously.

Anyway, I have no problem doing this in 4.3. We already have several time-consuming schema changes scheduled for 4.2, I'd like to avoid adding any more.

[khromov]

@pento,

I think a lot of people both here and in #15058 would love to see an increase to 255 chars. The patch I wrote still works, just needs to update the function name to upgrade_430().

https://core.trac.wordpress.org/ticket/13310#comment:47

Seeing as there was a huge schema change in 4.2 (switching to utf8mb4), this is an incredibly minor tweak in comparison.

[khromov]

@pento @scribu - Any way to get this into 4.3? If you need anything, let us know!

[pento]

I think this is a good candidate for 4.3. If I haven't done anything about by early May (I usually look at my x-early tickets in the week or two following the previous release), please ping me again, either here or in Slack. :-)

[MikeHansenMe]

Updated for 4.3

[MikeHansenMe]

When this goes in we need to make sure #15058 get closed since they are 2 approaches to the same problem.

[johnbillion]

This needs to be inside a database version conditional, probably the one right above it.

[khromov]

Hey @johnbillion

Attaching new patch for upgrading to WP 4.3.

[MikeHansenMe]

inside the conditional

[dd32]

Shifting to an enhancement - Although some might find the field too short, it's not a bug that it's that length IMHO.

The patch will also need to alter the index length from unbounded to 191 characters to abide by the utf8mb4 767 byte InnoDB index limits.
I don't expect that this will make it into 4.3, but I would like to see it done at some point.

[obenland]

Replying to dd32:

I don't expect that this will make it into 4.3, but I would like to see it done at some point.

Let's pick it up in 4.4 again then.

[khromov]

Still waiting for either schema change or code level validation here. A patch has been provided multiple times for schema change (which is imho. the best way forward) and there is a serious lack of feedback from the core team to the dozens of participants in this ticket. Either let us know what's wrong with the current patch so it can be fixed or mark this five year old behemoth as wontfix.

[MikeHansenMe]

13310-430.2.diff​ still applies if that is the approach we want to take.

[pento]

The option_name index length will also need to be set to $max_index_length. (Before the option_name column is resized.)

[netweb]

In 13310-440.diff

• Adds $max_index_length to option_name
• Upgrades database with upgrade_440()
• Database version on current revision 33726, this will need to change during commit to the next revision.

[pento]

The index length change needs to be made in pre_schema_upgrade(), as dbDelta() can't handle it.

[netweb]

Replying to pento:

The index length change needs to be made in pre_schema_upgrade(), as dbDelta() can't handle it.

Updated 13310-440.2.diff performs the index length change in pre_schema_upgrade()

[pento]

13310.diff​ fixes a bug in 13310-440.2.diff​, the prefix index can't be created until after option_name column has been extended to be longer than the prefix.

This still isn't entirely correct, however. the option_name index is UNIQUE, but because we're making it unique by prefix, it means this query will fail, despite the two values being different:

INSERT INTO wp_options (option_name) VALUES(REPEAT('a',192)),(REPEAT('a',193));

So, our options are to either make the option_name column 191 characters long, so that we don't get bad query failures. The other option is to accept that sometimes data will fail to insert, despite SELECT * FROM wp_options WHERE option_name='...' showing that there's no clashing option_name.

Both of these options kind of suck.

[pento]

People interested in option_name being extended: please provide feedback on the two options above, because I'm going to need to commit or punt soon.

[Hube2]

Needed to do some reading on index lengths and utf8mb4.. Unique key max length 767 bytes / 4 = 191.75

191 is better than 64 and not having queries silently fail is one of the reasons why some have been interested in this for so long.

My vote would be for 191.

[akshay_raje]

+1 for 191 characters long option_name column. At least it's 3x better than the current state.

[OriginalEXE]

+1 to extending it to 191 characters.

[Hube2]

I also think that #15058 should be done to validate the length as well. While this will give us more room it will not fix the problem of inserting values with longer names. They will still fail silently. You might say, well with it at 191 it will be fine, but I'm betting that eventually someone will run into this problem again.

[jdgrimes]

Replying to Hube2:

I also think that #15058 should be done to validate the length as well. While this will give us more room it will not fix the problem of inserting values with longer names. They will still fail silently. You might say, well with it at 191 it will be fine, but I'm betting that eventually someone will run into this problem again.

+1

[chrisjean]

Considering that silent failure is currently the primary issue, further entrenching on that behavior would be going backwards in my opinion.

I vote for the increase to 191 characters.

[pento]

Thanks for the feedback, everyone! Let's go with 191.

We can revisit increasing it further when MySQL 5.7 becomes the required minimum version, and we can figure out a way to rely on innodb_large_prefix being enabled.

[pento]

In 34030:

Schema: Increase the length of wp_options.option_name.

It's pretty easy to run over the option_name length, which causes undefined behaviour when inserting and retrieving options. Increasing the length from VARCHAR(64) to VARCHAR(191) significantly reduces the risk of this occurring.

Because option_name has a UNIQUE index, we can only increase it to 191 characters, rather than 255. The index can only use a prefix of 191 characters, so will incorrectly restrict long different strings that have the same prefix, if we make the column longer.

Props scribu, OriginalEXE, khromov, MikeHansenMe, netweb, pento.

Fixes #13310.

[johnbillion]

The inline docs for set_transient() (and maybe others) needs to be updated.

[OriginalEXE]

Uploaded patch for the inline docs. I could not find any other place where we mention the length limit besides set_transient.

[SergeyBiryukov]

In 34045:

Update the length limit in set_transient() docs after [34030].

Props OriginalEXE.
Fixes #13310.