Options table' varchar 64.

[Haluk Karamete]

Is there a harm to change the options table so that the field names for the
transients can have varchar 128 as opposed to varchar 64 and if that's
okay, what's the best way to do this?

In other words, what would be the best or the earliest hook for this kind
of s change? Or should this be done at wp-config?

[J.D. Grimes]

This is something that you need to change in the database table’s schema directly. So you need to run a single ALTER TABLE query. You don’t want to be running this every page load. If this is part of a plugin, you should do it on activation.

Also, see https://core.trac.wordpress.org/ticket/13310

-J.D.

Post by Haluk Karamete
Is there a harm to change the options table so that the field names for the
transients can have varchar 128 as opposed to varchar 64 and if that's
okay, what's the best way to do this?
In other words, what would be the best or the earliest hook for this kind
of s change? Or should this be done at wp-config?
_______________________________________________
wp-hackers mailing list
http://lists.automattic.com/mailman/listinfo/wp-hackers

[Haluk Karamete]

It makes total sense to me to do this on plug-in activation, as opposed to
wp-config.
Thank you for your advise.

Post by J.D. Grimes
This is something that you need to change in the database table’s schema
directly. So you need to run a single ALTER TABLE query. You don’t want to
be running this every page load. If this is part of a plugin, you should do
it on activation.
Also, see https://core.trac.wordpress.org/ticket/13310
-J.D.

Post by Haluk Karamete
Is there a harm to change the options table so that the field names for

the

Post by Haluk Karamete
transients can have varchar 128 as opposed to varchar 64 and if that's
okay, what's the best way to do this?
In other words, what would be the best or the earliest hook for this

kind

Post by Haluk Karamete
of s change? Or should this be done at wp-config?
_______________________________________________
wp-hackers mailing list
http://lists.automattic.com/mailman/listinfo/wp-hackers

_______________________________________________
wp-hackers mailing list
http://lists.automattic.com/mailman/listinfo/wp-hackers

[Haluk Karamete]

On the trac page (https://core.trac.wordpress.org/ticket/13310) , it is
said that "This schema change would have a significant impact on large WP
networks."

Could someone eloborate on this as to why the problem occurs on 'large WP
networks' as opposed to 'large WP sites'? So it's problematic on
multi-sites only?

Post by Haluk Karamete
It makes total sense to me to do this on plug-in activation, as opposed to
wp-config.
Thank you for your advise.

Post by J.D. Grimes
This is something that you need to change in the database table’s schema
directly. So you need to run a single ALTER TABLE query. You don’t want to
be running this every page load. If this is part of a plugin, you should do
it on activation.
Also, see https://core.trac.wordpress.org/ticket/13310
-J.D.

Post by Haluk Karamete
Is there a harm to change the options table so that the field names for

the

Post by Haluk Karamete
transients can have varchar 128 as opposed to varchar 64 and if

that's

Post by Haluk Karamete
okay, what's the best way to do this?
In other words, what would be the best or the earliest hook for this

kind

Post by Haluk Karamete
of s change? Or should this be done at wp-config?
_______________________________________________
wp-hackers mailing list
http://lists.automattic.com/mailman/listinfo/wp-hackers

_______________________________________________
wp-hackers mailing list
http://lists.automattic.com/mailman/listinfo/wp-hackers

[Andrew Nacin]

Large networks have a lot of options tables. For a sufficiently large table
(which might depend also on the storage engine), things could lock up or go
awry. Rather than just doing it once, we're doing it n times, where n is
the number of sites. More chances for things to go wrong.

My take is this:
If you are in a position to be able to run an ALTER TABLE query, you are
also probably hosting your own thing, which means you should stop storing
transients in the database. Instead, install an object cache. Transients in
the database are a last-resort kind of thing. If you're doing heavy stuff
with transients, look for alternatives.

Also, if your transient names are too long, use a prefix plus a hash of the
name. It'll make it constant-length.

Nacin

Post by Haluk Karamete
On the trac page (https://core.trac.wordpress.org/ticket/13310) , it is
said that "This schema change would have a significant impact on large WP
networks."
Could someone eloborate on this as to why the problem occurs on 'large WP
networks' as opposed to 'large WP sites'? So it's problematic on
multi-sites only?

Post by Haluk Karamete
It makes total sense to me to do this on plug-in activation, as opposed

to

Post by Haluk Karamete
wp-config.
Thank you for your advise.

Post by J.D. Grimes
This is something that you need to change in the database table’s schema
directly. So you need to run a single ALTER TABLE query. You don’t want

to

Post by Haluk Karamete

Post by J.D. Grimes
be running this every page load. If this is part of a plugin, you

should do

Post by Haluk Karamete

Post by J.D. Grimes
it on activation.
Also, see https://core.trac.wordpress.org/ticket/13310
-J.D.

Post by Haluk Karamete
Is there a harm to change the options table so that the field names

for

Post by Haluk Karamete

Post by J.D. Grimes
the

Post by Haluk Karamete
transients can have varchar 128 as opposed to varchar 64 and if

that's

Post by Haluk Karamete
okay, what's the best way to do this?
In other words, what would be the best or the earliest hook for this

kind

Post by Haluk Karamete
of s change? Or should this be done at wp-config?
_______________________________________________
wp-hackers mailing list
http://lists.automattic.com/mailman/listinfo/wp-hackers

_______________________________________________
wp-hackers mailing list
http://lists.automattic.com/mailman/listinfo/wp-hackers

_______________________________________________
wp-hackers mailing list
http://lists.automattic.com/mailman/listinfo/wp-hackers

[Haluk Karamete]

Now giving it a second thought, and considering what Morgan has brought
into the picture on this very quesition, doing this at a plug-in activation
time does not sound to me as good of an idea as it did an hour ago. :)

If it's gonna take that much of time, then every time WP updates, ( and as
part of this update, all plugins are automatically deactivated and then
re-activated ) a nd as a result of this, some users will experience such
slowness, and my plug in would be the one causing that, it's hard for me to
take that route... See what I mean?

I think if that code were to be planted in the wp-config.php file instead,
then that would have been better.

At wp-config, and every time it runs ( meaning at each page load), a
constant can be probed to see if it is defined or not.

If that constant is defined, I can drive the fact that the option names
has already been upped to varchar(128).

What say you?

Post by J.D. Grimes
This is something that you need to change in the database table’s schema
directly. So you need to run a single ALTER TABLE query. You don’t want to
be running this every page load. If this is part of a plugin, you should do
it on activation.
Also, see https://core.trac.wordpress.org/ticket/13310
-J.D.

Post by Haluk Karamete
Is there a harm to change the options table so that the field names for

the

Post by Haluk Karamete
transients can have varchar 128 as opposed to varchar 64 and if that's
okay, what's the best way to do this?
In other words, what would be the best or the earliest hook for this

kind

Post by Haluk Karamete
of s change? Or should this be done at wp-config?
_______________________________________________
wp-hackers mailing list
http://lists.automattic.com/mailman/listinfo/wp-hackers

_______________________________________________
wp-hackers mailing list
http://lists.automattic.com/mailman/listinfo/wp-hackers

[Andrew Nacin]

I don't understand why you require anything more than 64 characters. It
would be *nice to have*, but it's not a requirement.

Stick to the existing limits and wait to see if core makes an adjustment in
https://core.trac.wordpress.org/ticket/13310.

Post by Haluk Karamete
Now giving it a second thought, and considering what Morgan has brought
into the picture on this very quesition, doing this at a plug-in activation
time does not sound to me as good of an idea as it did an hour ago. :)
If it's gonna take that much of time, then every time WP updates, ( and as
part of this update, all plugins are automatically deactivated and then
re-activated ) a nd as a result of this, some users will experience such
slowness, and my plug in would be the one causing that, it's hard for me to
take that route... See what I mean?
I think if that code were to be planted in the wp-config.php file instead,
then that would have been better.
At wp-config, and every time it runs ( meaning at each page load), a
constant can be probed to see if it is defined or not.
If that constant is defined, I can drive the fact that the option names
has already been upped to varchar(128).
What say you?

Post by J.D. Grimes
This is something that you need to change in the database table’s schema
directly. So you need to run a single ALTER TABLE query. You don’t want

to

Post by J.D. Grimes
be running this every page load. If this is part of a plugin, you should

do

Post by J.D. Grimes
it on activation.
Also, see https://core.trac.wordpress.org/ticket/13310
-J.D.

Post by Haluk Karamete
Is there a harm to change the options table so that the field names for

the

Post by Haluk Karamete
transients can have varchar 128 as opposed to varchar 64 and if

that's

Post by J.D. Grimes

Post by Haluk Karamete
okay, what's the best way to do this?
In other words, what would be the best or the earliest hook for this

kind

Post by Haluk Karamete
of s change? Or should this be done at wp-config?
_______________________________________________
wp-hackers mailing list
http://lists.automattic.com/mailman/listinfo/wp-hackers

_______________________________________________
wp-hackers mailing list
http://lists.automattic.com/mailman/listinfo/wp-hackers

_______________________________________________
wp-hackers mailing list
http://lists.automattic.com/mailman/listinfo/wp-hackers

[Nikola Nikolov]

What I've used before(and now I see is not the best) is to simply do

$key = md5( "my_{$complicated}_{$multi}_component_{$key}" );

Instead a better approach is

// Should not be longer than 13 characters, since md5() returns 32
characters => 32+13 = 45, which is the limit
$prefix = 'my_';
$key = $prefix . md5( "{$complicated}_{$multi}_component_{$key}" );

This way you can

DELETE FROM $wpdb->options WHERE option_name LIKE '$prefix%';

Which is good to do when your plugin is deactivated.

Post by Andrew Nacin
I don't understand why you require anything more than 64 characters. It
would be *nice to have*, but it's not a requirement.
Stick to the existing limits and wait to see if core makes an adjustment in
https://core.trac.wordpress.org/ticket/13310.

Post by Haluk Karamete
Now giving it a second thought, and considering what Morgan has brought
into the picture on this very quesition, doing this at a plug-in

activation

Post by Haluk Karamete
time does not sound to me as good of an idea as it did an hour ago. :)
If it's gonna take that much of time, then every time WP updates, ( and

as

Post by Haluk Karamete
part of this update, all plugins are automatically deactivated and then
re-activated ) a nd as a result of this, some users will experience such
slowness, and my plug in would be the one causing that, it's hard for me

to

Post by Haluk Karamete
take that route... See what I mean?
I think if that code were to be planted in the wp-config.php file

instead,

Post by Haluk Karamete
then that would have been better.
At wp-config, and every time it runs ( meaning at each page load), a
constant can be probed to see if it is defined or not.
If that constant is defined, I can drive the fact that the option names
has already been upped to varchar(128).
What say you?

Post by J.D. Grimes
This is something that you need to change in the database table’s

schema

Post by Haluk Karamete

Post by J.D. Grimes
directly. So you need to run a single ALTER TABLE query. You don’t want

to

Post by J.D. Grimes
be running this every page load. If this is part of a plugin, you

should

Post by Haluk Karamete
do

Post by J.D. Grimes
it on activation.
Also, see https://core.trac.wordpress.org/ticket/13310
-J.D.

Post by Haluk Karamete
Is there a harm to change the options table so that the field names

for

Post by Haluk Karamete

Post by J.D. Grimes
the

Post by Haluk Karamete
transients can have varchar 128 as opposed to varchar 64 and if

that's

Post by J.D. Grimes

Post by Haluk Karamete
okay, what's the best way to do this?
In other words, what would be the best or the earliest hook for this

kind

Post by Haluk Karamete
of s change? Or should this be done at wp-config?
_______________________________________________
wp-hackers mailing list
http://lists.automattic.com/mailman/listinfo/wp-hackers

_______________________________________________
wp-hackers mailing list
http://lists.automattic.com/mailman/listinfo/wp-hackers

_______________________________________________
wp-hackers mailing list
http://lists.automattic.com/mailman/listinfo/wp-hackers

_______________________________________________
wp-hackers mailing list
http://lists.automattic.com/mailman/listinfo/wp-hackers

[J.D. Grimes]

Nacin and Nikola are right.

But I’d also like to add that I think you have a misconception about the plugin activation when WordPress is updated. I don’t think the activation hooks actually get fired, it is just a silent de/activation (someone correct me if I’m wrong). You are correct though, that you wouldn’t want to run this every time. You would ideally check the length of the column, and only change it if it wasn’t at the desired length already. Or, you could set an option in the database after the first time, and only run the update if it did not exist (this is how many plugins keep track of their database version).

Of course, you really shouldn’t try to do this anyway. :0)

-J.D.

Post by Haluk Karamete
Now giving it a second thought, and considering what Morgan has brought
into the picture on this very quesition, doing this at a plug-in activation
time does not sound to me as good of an idea as it did an hour ago. :)
If it's gonna take that much of time, then every time WP updates, ( and as
part of this update, all plugins are automatically deactivated and then
re-activated ) a nd as a result of this, some users will experience such
slowness, and my plug in would be the one causing that, it's hard for me to
take that route... See what I mean?
I think if that code were to be planted in the wp-config.php file instead,
then that would have been better.
At wp-config, and every time it runs ( meaning at each page load), a
constant can be probed to see if it is defined or not.
If that constant is defined, I can drive the fact that the option names
has already been upped to varchar(128).
What say you?

[Morgan Tocker]

Hi!

Post by Haluk Karamete
Is there a harm to change the options table so that the field names for the
transients can have varchar 128 as opposed to varchar 64 and if that's
okay, what's the best way to do this?

It will lock up the table to extend the VARCHAR, while in the background it fills a temporary table to replace the existing table.

I loaded the wp_options table with 104994 dummy records to test how long this would take. On my local machine:

* 1.71 sec using InnoDB
* ~2.84 sec when using MyISAM.

Gist available for anyone wanting to try and reproduce:
https://gist.github.com/morgo/4f23b48fd3df09fb47f6

In MySQL 5.7 this will be online for InnoDB tables:
http://dev.mysql.com/worklog/task/?id=6554

- Morgan

[Haluk Karamete]

THank you Morgan.

So, assuming if it's a 100 site, multi-site network where each site has as
many options as you described, this one time pain of schema change will
come at the expense of a 2.87*100 sec?

Is this understanding correct in a nut-shell?

Post by Morgan Tocker
Hi!

Post by Haluk Karamete
Is there a harm to change the options table so that the field names for

the

Post by Haluk Karamete
transients can have varchar 128 as opposed to varchar 64 and if that's
okay, what's the best way to do this?

It will lock up the table to extend the VARCHAR, while in the background
it fills a temporary table to replace the existing table.
I loaded the wp_options table with 104994 dummy records to test how long
* 1.71 sec using InnoDB
* ~2.84 sec when using MyISAM.
https://gist.github.com/morgo/4f23b48fd3df09fb47f6
http://dev.mysql.com/worklog/task/?id=6554
- Morgan
--
Morgan Tocker
MySQL Community Manager
Oracle Canada - Hardware and Software, Engineered to Work Together.
Office: Toronto, Canada
_______________________________________________
wp-hackers mailing list
http://lists.automattic.com/mailman/listinfo/wp-hackers

[Morgan Tocker]

Hi Haluk,

Post by Haluk Karamete
So, assuming if it's a 100 site, multi-site network where each site has as
many options as you described, this one time pain of schema change will
come at the expense of a 2.87*100 sec?

On my blog, I only have 163 rows in wp_options, but in my test I generated 104994 rows of dummy data.

(Others will have more than 163 rows - I am probably a simple use case.)

Post by Haluk Karamete
Is this understanding correct in a nut-shell?

I tried to create the test so any skew showed closer to the worst-case than the best case. This is not always easy to do, and I can already identify two cases where this will not be true:

- My local machine is more powerful than virtual machines / VPS hosting environments.
- There might be some very long option_value texts creating a larger table. I used REPEAT('a', 200), but should have probably gone for REPEAT(‘a’, 1889), as this is the average option_value length in my installation.

TL;DR: I think it will be less than 2.87*100. You can try it for yourself though:
https://gist.github.com/morgo/4f23b48fd3df09fb47f6

- Morgan