plugin development - Does using set_transient() function can lead to MySQL problems?

admin管理员组

文章数量:1388146

As a plugin author, I have to think about many things. One of my plugins uses a temporarily PHP session to save the JSON data that receives through its API in array type.

Because PHP session has its limitations and sometimes causes memory problems, I decided to use the transient function that working nice.

If one site has over a thousand visits a day, each visitor has one temporary record in the database via transient function that expires every 60 minutes or less (depends on how the user determines).

Transient function uses a wp_options table that has by default filed option_id bigint(20).

What will happen when I reach the last option_id?

Does WordPress have some security trigger to reset option_id or there is some other rule?

Or do I just not have to worry about it?

As a plugin author, I have to think about many things. One of my plugins uses a temporarily PHP session to save the JSON data that receives through its API in array type.

Because PHP session has its limitations and sometimes causes memory problems, I decided to use the transient function that working nice.

If one site has over a thousand visits a day, each visitor has one temporary record in the database via transient function that expires every 60 minutes or less (depends on how the user determines).

Transient function uses a wp_options table that has by default filed option_id bigint(20).

What will happen when I reach the last option_id?

Does WordPress have some security trigger to reset option_id or there is some other rule?

Or do I just not have to worry about it?

• plugin-development
• mysql
• session
• transient

Share Improve this question asked Apr 14, 2020 at 19:57 Ivijan Stefan StipićIvijan Stefan Stipić 45533 silver badges1616 bronze badges 2

• Your question, and the way you phrased it, is an indicator that you have abigger, design-wise, problem in your plugin. > to save the JSON data that receives through its API in array type. Does that mean that each site visitor recieves 100% unique data via the API? – SeeBeen Commented Apr 15, 2020 at 17:50
• @SeeBeen Yes. Each visitor have own geo API information's saved to we not trigger API each time when visitor search something. Right now we not use transients because we build separate database table for this but before we use transients that are shown as bad choice. – Ivijan Stefan Stipić Commented May 11, 2022 at 9:47

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 9

Does using set_transient() function can lead to MySQL problems?

No, although if you used it so much that the MySQL server ran out of disk space, then yes it could cause issues. However, you would know that's the case by checking the disk space, and if you manage to do this then I would consider that something is very wrong with your usage of transients.

The same is true of any table though, this isn't a transients issue, it's a storing so much stuff that things are clearly going wrong. Like when a large drive is filled by a log, or a program enters an infinite loop.

What will happen when I reach the last option_id?

You will not reach the last option ID.

I have never seen a site encounter this, to breach a BIGINT 20, you'd need 2 to the 64 options, or 18,446,744,073,709,552,000 options. That's 18,446,744,073,709 million options, or 18,446,744,073 trillion options, or 18,446,744 quadtrillion options.

Your server will run out of memory and disk space long before this is an issue. So this isn't worth worrying about. Keep in mind though that if you need lots of transients and you have hundreds, or even thousands of transients, this is indicative of a scaling problem in the code.

Even if we say that 400k entries will be added to the options table each day, and we have a machine with infinite storage CPU and RAM, it would still take at least 126 trillion years to reach the upper limit. The heat death of the universe may happen sooner than that.

Does WordPress have some security trigger to reset option_id or there is some other rule?

No, it isn't necessary, or a security issue, and I doubt any WP site has ever come close to even using half of the value. This is something MySQL/MariaDB would have to handle.

Or do I just not have to worry about it?

Correct! The option ID field is a complete red herring. It is not worth your time and energy. Do not worry about it.

As a plugin author, I have to think about many things. One of my plugins uses a temporarily PHP session to save the JSON data that receives through its API in array type.

This is a problem. On most WP hosts PHP sessions just do not work. PHP sessions are incompatible with the vast majority of plugin based and server based caching mechanisms. PHP sessions also run counter to how WordPress manages its own sessions using cookies. Additionally, PHP sessions have to be stored in memory, and because of the way they work, it will struggle to scale up with traffic.

It's also insecure, a user can meddle with PHP sessions to switch them around. You were correct to investigate alternatives.

本文标签： plugin developmentDoes using settransient() function can lead to MySQL problems