I am using ClearDB for a mysql database. I have a table with an auto incremented primary key. The problem is that rather than it incrementing by 1, its incrementing by 10. How can I fix this please?
Also when I tested the db on local host, the incrementation was working correctly.
MySQL has a configuration option for this: auto_increment_increment. It's possible for the option to be set to 10 on your ClearDB host, but 1 on your local host.
Check it in each environment:
SELECT @@auto_increment_increment;
If it's set to 10 on the ClearDB environment, you'll have to ask whoever set up that environment why it's configured that way. There might be a good reason.
Unfortunately it's a global setting, so if it's set on the server it affects all tables in all databases on that MySQL instance.
You can override the value per session, so if you want your app to use a different value than the global value you can execute this as an SQL query before you do any INSERTs:
SET auto_increment_increment = 1;
But that only takes effect for the current session, so you'd have to do it every time you open a new session.
Also double-check this related option, because it's often set to something non-default if the increment has been changed:
SELECT @@auto_increment_offset;
ClearDB sets the auto_increment value globally to 10 to facilitate the cluster functionality they do. You cannot override this! Even on single-tenant clusters, this is always in multiples of 10. They will not adjust it for you.
