4

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?

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
    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. May 11, 2022 at 9:47

1 Answer 1

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.

2
  • 1
    Yes. I want avoid PHP sessions ASAP. Before my plugin use only few records and how grows last few years, services and API require more and more data and session must be avoided thast's why I use transient. But in my last test what running 10 days, option_id reach over 400.000. That's why I'm concerned. Apr 15, 2020 at 8:33
  • 1
    That doesn't mean you have 400k options, but without knowing what those options are, there really isn't any way for someone to help you. Otherwise, if I call add_option then delete_option 400k times the option ID will get high even though there's only a single option. Even at 400k option IDs per day, it would still take 126 trillion years ( 126,347,562,148 years ) to use all the IDs up. If this answer answers your question can you mark it as accepted? Otherwise it seems your question is incomplete. Q's need to be specific and answerable, it isn't a discussion forum
    – Tom J Nowell
    Apr 15, 2020 at 14:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.