Tommie: Yesterday, I had to make such changes to the live app’s DB involving the creation of a new column in one table and the manipulation of FK constraints in another.
Haddad: All of that was fine.
Feeling: However, a completely unrelated table seems to have had its AUTOINCREMENT value reset to zero.
Crystal: I’m using this table to generate unique IDs when required that get used elsewhere for various purposes, but there is otherwise no link between that table and any other so far as MySQL is aware
Althoff: I’m doing this by INSERTing a row, getting the new row’s ID, then rolling back the txn
Kochanek: So this rid_gen table’s AUTOINCREMENT keeps increasing, but rows are otherwise never actually written to the table
Devaughan: Under what cir***stances could AUTOINCREMENT be reset to 0 like this?
Magar: It seems to happen when I ALTER other tables, but since I’m not touching _this_ the rid_gen table, I can’t see why modifying other tables should make a difference to the rid_gen table.
Shimkus: StrixUK: is it innodb table? on daemon’s restart
Munce: Naktibalda: yeah, innodb
Varieur: Was the table truncated?
Roker: Because rid_gen never has INSERTs committed
Varieur: There you go. That’s normal, then.
Moeller: After a server startup, for the first insert into a table t, InnoDB executes the equivalent of this statement: SELECT MAXai_col FROM t FOR UPDATE;
Brits: Http://dev.mysql.com/doc/refman/5.5/en/innodb-auto-increment-traditional.html
Courson: Well, that’s my answer then, thank you
Wisecup: Simple solution: commit them changes to rid_gen.
Rocca: So this could happen when the server is upgraded
Rouch: Which’d involve a restart
Cywinski: You can keep all values or only “last” value in there you can just sometimes delete all values but the last to keep the table really small, no need to have exactly 1 row in there
Butrick: Jkavalik: meh, it’s a table with a single int11, it will not take up much space, and if i try to delete all but the most recent row, i could make a mistake.
Dourado: I suppose i could do it as part of a maintenance run, but for now, they can persist.
Varieur: StrixUK: surely hope it’s unsigned.
Engles: Tbh, it probably is signed
Arduini: StrixUK, it depends on how many rows you expect to generate in some time. If the table grows to few millions of rows then it stops being so trivial.
Foust: The exact type is taken care of by SQLAlchemy, and the type I specified was Integer, so it probably is.
Spaugh: Varieur: does it matter whether it’s signed or not? MySQL presumably will never ***ign a negative value to an autoincrementing primary key
Ojito: Okay, still 2 billion, then
Varieur: StrixUK: 231 -1, 232 -1
Malagisi: Jkavalik: true. this is a financial application, and if we ever get to the point where we have 2 billion GL entries, then that would be a nice problem to have
Vasher: Also, i very much doubt that my app will be scalable to a company of that size though you never know, so this is all written on the ***umption that, on the remote Gallon that the company should grow to such a size, the needs of the company will change also, and will require other refactoring as well.
Kaupu: Hi guys ,can you help me with this sql ,i am trying to do an update ,but its giving me an error saying that “You can’t specify target table ‘t1’ for update in FROM clause ”
Langmyer: Http://pastebin.ca/3155940
Whitebread: R0ny, you have to use self-join, not subquery
Feltenberger: StrixUK: What kind of financial application is it? You may want to be careful using words like “***umption”, “presume” and “meh” when you’re dealing with that kind of application :
Dupray: Jkavalik: but i dont have another table,i want to do operation within that table,if you look at my query you can see that,may be i am missing something