Hi, I'm a beginner at using MySQL, I currently deployed my crud app on Heroku using the ClearDB add-on. I didn't see that coming, but the increased value of the primary key instead of being 1 is set to 10, and I cannot find a way to change it. Now I`m considering switching and deploying the full app and MySql to DigitalOcean any advice on that? Will I get the same issue? Thanks in advance!
If a database tables has had some records added to it, and those records are then deleted the Auto Increment value is not automatically reset to avoid accidentally referencing an incorrect row. (e.g. maybe another reference to that row ID exists somewhere). If that's not a problem you can manually reset it with
ALTER TABLE tablename AUTOINCREMENT = 0;
Alternatively, if you were to TRUNCATE the table (DELETE ALL RECORDS), this would also reset the AUTO INCREMENT value: TRUNCATE TABLE table_name;
Hi Andy, thank you. I will save these lines of code. My issue by the way was that the autoincrement for every record is 10 a the time. If for example I insert one record clearDB will give the id of 5, for the next one it will be 15, 25 ,35 and so on, instead of be 1,2,3,4,5. Anyway I solve temporary the problem not showing the ID value on my HTML table. I will look in the future to change the deployment service.
Oh I see! There's 2 possible causes for this, the most likely is the auto_increment_increment setting is not 1. Try running this query to check the setting.
`SHOW VARIABLES LIKE 'auto_incre%'; `
auto_increment_increment = 1
auto_increment_offset = 1
If they are not set to 1 then change your configuration and restart the mysql server.
I've just read that with ClearDB you can't change this, you're stuck with an offset of 10.
ClearDB's response to the same question on SO here https://stackoverflow.com/questions/26002784/auto-increment-by-1-in-mysql-with-cleardb#26004621
ClearDB uses circular replication to provide master-master MySQL support. As such, certain things such as auto_increment keys (or sequences) must be configured in order for one master not to use the same key as the other, in all cases. We do this by configuring MySQL to skip certain keys, and by enforcing MySQL to use a specific offset for each key used. The reason why we use a value of 10 instead of 2 is for future development.