Syphon Posted February 29, 2008 Share Posted February 29, 2008 I'm building a simple web CMS for a client. The CMS includes a "reset" page by request that truncates all of the pages and sets each pages Auto Increment value back to 1. The SQL is use to reset looks like this and is called for each table. TRUNCATE TABLE users; ALTER TABLE users AUTO_INCREMENT=1 This is necessary because there are default data that is re-inserted once all the tables have been truncated (eg I have a user level table that has Admin Access as level 1 and Editor as level 2. The auto increment value needs to be reset back to 1 so foreign keys do not break). The issue I'm having is difficult to replicate. It seems that if I drop all foreign key contraints, then drop all tables so I have a empty database; upon running the reset script my inserts generate ids starting at 2, then 12, 22, 32, 42, etc. In another table the auto increment started at 2 then went to 23, 32, 42, 33. For my insert SQL I do not include the id as one of the values. It seems very random, and I don't know of any way to set the auto increment seed from the default of one. The only fix I've found is to drop the database, reset the mysql windows service and then run my creation script, to get the auto increment columns to increment correctly. Any ideas? Thanks in advance. Quote Link to comment Share on other sites More sharing options...
aschk Posted February 29, 2008 Share Posted February 29, 2008 these auto_increment steps (2, 12, 22, 32, 42) look like it's to do with database rotation/replication/clustering that i've seen examplified before in an article. Who is the host responsible for your database? Sounds like they have an auto_increment step set with a base value dependent on the particular MySQL machine you're on. Quote Link to comment Share on other sites More sharing options...
Syphon Posted February 29, 2008 Author Share Posted February 29, 2008 I've had this problem on both my local and server machine. But I can't fix it on the hosting server without creating a new database. The company is http://www.whalehosting.ca Quote Link to comment Share on other sites More sharing options...
fenway Posted February 29, 2008 Share Posted February 29, 2008 or an auto_increment_increment value set. Quote Link to comment Share on other sites More sharing options...
aschk Posted February 29, 2008 Share Posted February 29, 2008 Ok, interseting. Having the problem on your local machine also suggests that there's something else wrong. TRUNCATE will ALWAYS set the auto_increment value back to 1. That is part of the feature that TRUNCATE gives. Show us all the SQL steps you are using, with foreign key references and some table layout (i.e the whole transaction script). Incidently, the client request seems a bit "ridiculous" if you like, because by the sounds of it they're destroying related data (hence your need to turn foreign keys off) and then expecting it to be rebuilt again and magically rematch up? At least this is the impression i'm getting. Quote Link to comment Share on other sites More sharing options...
fenway Posted February 29, 2008 Share Posted February 29, 2008 Incidently, the client request seems a bit "ridiculous" if you like, Like *all* client requests... Quote Link to comment Share on other sites More sharing options...
Syphon Posted February 29, 2008 Author Share Posted February 29, 2008 Ok, interseting. Having the problem on your local machine also suggests that there's something else wrong. TRUNCATE will ALWAYS set the auto_increment value back to 1. That is part of the feature that TRUNCATE gives. Show us all the SQL steps you are using, with foreign key references and some table layout (i.e the whole transaction script). Incidently, the client request seems a bit "ridiculous" if you like, because by the sounds of it they're destroying related data (hence your need to turn foreign keys off) and then expecting it to be rebuilt again and magically rematch up? At least this is the impression i'm getting. You're right; all of the foreign keys are dropped and then added back once all of the data is inserted. I'm almost debating to just get the id returned from an insert instead of just assuming that it will always be 1, 2, 3, etc. I'm not 100% sure but I think that the MySQL database on the hosting server is clustered. I'll have to double check. I'll post the SQL code later today when I get home. Quote Link to comment Share on other sites More sharing options...
Syphon Posted March 18, 2008 Author Share Posted March 18, 2008 I've gotten word from the host that there is "an offset in place on each server to ensure that collisions do not take place". Not much to go on but obviously self-explanatory. As a quick fix I've decided to hard code the values into the SQL statements. Anyone have a second opinion? Quote Link to comment Share on other sites More sharing options...
aschk Posted March 18, 2008 Share Posted March 18, 2008 Yeah i was right then. Clustering with auto_increment set is a bit of a poor solution as it causes other problems (which you are experiencing). You'll have to forget about the auto_increment and do a manual set of the ID because the value supplied by the cluster is unreliable. Quote Link to comment Share on other sites More sharing options...
fenway Posted March 18, 2008 Share Posted March 18, 2008 I've gotten word from the host that there is "an offset in place on each server to ensure that collisions do not take place". Not much to go on but obviously self-explanatory. As a quick fix I've decided to hard code the values into the SQL statements. Anyone have a second opinion? As I mentioned above, this sounds like auto_increment_increment... aschk, why do you feel this causes "other problems"? Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.