Re: Alternative MySQL PEAR DB sequence behavior

"Tomas V.V.Cox" wrote: > > "Stig S. Bakken" wrote: > > > > Oleg Rekutin wrote: > > > > > > Current MySQL sequence emulation simply keeps adding and adding IDs to the > > > sequence's table. This bothered me, because a lot of sequences with a lot of > > > IDs are just going to take up space for no reason. > > > > > > So I modified it to keep track of just one value... Haven't tested it > > > rigorously, but AFAIK it works. Patch is quoted below. (So here I am, having > > > wasted 1.5 hours on this patch when I have an urgent project otherwise :).) > > > > > > It also backwards-compatible, as it cleans up the garbage from the old-style > > > sequences (otherwise UPDATE fails), so that nothing has to be done on the > > > part of the user when the new mechanism is used. In addition, since the new- > > > style sequences use the same tables w/ an AUTO_INCREMENT id, it is also > > > backwards-compatible in the sense that if the user downgrades to the old- > > > style mechanism or moves the DB & application to a server with an older > > > version of PEAR DB, everything should still work (it will just keep on > > > inserting values). > > > > > > Deep inside I want all sequences to be contained in one table, but that's > > > too much of a pain in the ass to do, plus various concurrency issues arise > > > (there are concurrency issues nonetheless, mostly arising from the fact if > > > two clients attempt to create two sequences simultaneously... CREATE > > > DATABASE will fail for one of them... might want to use CREATE DATABASE IF > > > NOT EXISTS then?). > > > > > > Again, you might want to run tests of your own... Works for me :) > > > > I've committed this patch now, and it works fine except that the first > > id returned seems to be 2? At least in the case of > > DB/tests/mysql/005.phpt. Any ideas? > > It seems that INSERT INTO ${sqn}_seq VALUES(0) in createSequence inserts > the value "1", so update always get at least value 2. Should I change > the test? > > Tomas V.V.Cox > > PS. I've commited the new version of the patch Oleg send me time ago If there are any race conditions in the conversion from old to new sequence (mysql implementation), I guess a table lock or something should be applied? - Stig