Thread: [SQLObject] MySQL and running out of `id' space...
SQLObject is a Python ORM.
Brought to you by:
ianbicking,
phd
From: Jaime W. <pro...@gm...> - 2006-03-29 21:07:20
|
Ok, I've created a sqlobject like so: class Output(SQLObject): print_job =3D ForeignKey("PrintJob", notNone =3D True) piece_id =3D IntCol(notNone =3D True) ... Currently I have about 1.7 million rows in there. SQLObject created the `id' column for the table as `int'. MySQL's int has a value from -2147483648 to 2147483647. I suspect that I'll be at the maximum INT valu= e shortly. Is there an `easy' way to reset the ID column to use BIGINT? I suspect I'll have to regenerate the models, somehow specifying the id, but I'm not sure. Thanks! jw -- "Government does not solve problems; it subsidizes them." Ronald Reagan |
From: Oleg B. <ph...@ma...> - 2006-03-29 21:15:12
|
On Wed, Mar 29, 2006 at 03:07:15PM -0600, Jaime Wyant wrote: > Is there an `easy' way to reset the ID column to use BIGINT? ALTER TABLE... I don't remember if MySQL can change the type of a column in ALTER TABLE. If it cannot you need a temporary table... Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Jaime W. <pro...@gm...> - 2006-03-29 21:21:50
|
Yah, you can. I'll give that a go. Thanks, jw On 3/29/06, Oleg Broytmann <ph...@ma...> wrote: > > On Wed, Mar 29, 2006 at 03:07:15PM -0600, Jaime Wyant wrote: > > Is there an `easy' way to reset the ID column to use BIGINT? > > ALTER TABLE... > > I don't remember if MySQL can change the type of a column in ALTER TABLE. > If it cannot you need a temporary table... > > Oleg. > -- > Oleg Broytmann http://phd.pp.ru/ ph...@ph... > Programmers don't die, they just GOSUB without RETURN. > > > ------------------------------------------------------- > This SF.Net email is sponsored by xPML, a groundbreaking scripting > language > that extends applications into web and mobile media. Attend the live > webcast > and join the prime developer group breaking into this new coding > territory! > http://sel.as-us.falkag.net/sel?cmd=3Dlnk&kid=3D110944&bid=3D241720&dat= =3D121642 > _______________________________________________ > sqlobject-discuss mailing list > sql...@li... > https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss > -- "Government does not solve problems; it subsidizes them." Ronald Reagan |
From: Uwe G. <mai...@on...> - 2006-03-30 13:06:33
|
Jaime Wyant wrote: > Ok, I've created a sqlobject like so: > > class Output(SQLObject): > print_job = ForeignKey("PrintJob", notNone = True) > piece_id = IntCol(notNone = True) > > ... > > Currently I have about 1.7 million rows in there. SQLObject created the > `id' column for the table as `int'. MySQL's int has a value from > |-2147483648| |to 2147483647. |I suspect that I'll be at the maximum > INT value shortly. Is there an `easy' way to reset the ID column to use > BIGINT? I suspect I'll have to regenerate the models, somehow > specifying the id, but I'm not sure. > > Thanks! > jw > Jaime, if piece_id is a primary key you can't do that in SQLObject. I tried with Firebird to use BIGINT for pk's. No way! Uwe |
From: Vetlugin Y. <vy...@ch...> - 2006-03-30 13:33:58
|
Actually I have some positive xp in that ;) I`ve patch sqlobject: 1) In mysqlconnection.py I`ve replaced INT type with INT UNSIGNED type in createIDColumn and joinSQLType functions. 2) In cols.py I`ve replaced INT with INT UNSIGNED in SOKeyCol._mysqlType. It works fine for me, but I`m not shore if I miss something. And I`m sorry - I can`t create patch for now. I think you can replace INT with BIGINT this way and It should work fine (don`t forget to change type of ID in database). On Thursday 30 March 2006 17:07, Uwe Grauer wrote: > Jaime Wyant wrote: > > Ok, I've created a sqlobject like so: > > > > class Output(SQLObject): > > print_job = ForeignKey("PrintJob", notNone = True) > > piece_id = IntCol(notNone = True) > > > > ... > > > > Currently I have about 1.7 million rows in there. SQLObject created the > > `id' column for the table as `int'. MySQL's int has a value from > > > > |-2147483648| |to 2147483647. |I suspect that I'll be at the maximum > > > > INT value shortly. Is there an `easy' way to reset the ID column to use > > BIGINT? I suspect I'll have to regenerate the models, somehow > > specifying the id, but I'm not sure. > > > > Thanks! > > jw > > Jaime, > > if piece_id is a primary key you can't do that in SQLObject. > I tried with Firebird to use BIGINT for pk's. No way! > > Uwe > > > ------------------------------------------------------- > This SF.Net email is sponsored by xPML, a groundbreaking scripting language > that extends applications into web and mobile media. Attend the live > webcast and join the prime developer group breaking into this new coding > territory! > http://sel.as-us.falkag.net/sel?cmd=lnk&kid=110944&bid=241720&dat=121642 > _______________________________________________ > sqlobject-discuss mailing list > sql...@li... > https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss |
From: Uwe G. <mai...@on...> - 2006-03-30 14:16:47
|
Vetlugin Yury wrote: > Actually I have some positive xp in that ;) > I`ve patch sqlobject: > 1) In mysqlconnection.py I`ve replaced INT type with INT UNSIGNED type in > createIDColumn and joinSQLType functions. > 2) In cols.py I`ve replaced INT with INT UNSIGNED in SOKeyCol._mysqlType. > > It works fine for me, but I`m not shore if I miss something. And I`m sorry - I > can`t create patch for now. I think you can replace INT with BIGINT this way > and It should work fine (don`t forget to change type of ID in database). > Yes, but thats a dirty hack! I was told that there is no way in SQLObject to use pk's which aren't INT's. There is no way to specify what the PK-Type is. Uwe > On Thursday 30 March 2006 17:07, Uwe Grauer wrote: >> Jaime Wyant wrote: >>> Ok, I've created a sqlobject like so: >>> >>> class Output(SQLObject): >>> print_job = ForeignKey("PrintJob", notNone = True) >>> piece_id = IntCol(notNone = True) >>> >>> ... >>> >>> Currently I have about 1.7 million rows in there. SQLObject created the >>> `id' column for the table as `int'. MySQL's int has a value from >>> >>> |-2147483648| |to 2147483647. |I suspect that I'll be at the maximum >>> >>> INT value shortly. Is there an `easy' way to reset the ID column to use >>> BIGINT? I suspect I'll have to regenerate the models, somehow >>> specifying the id, but I'm not sure. >>> >>> Thanks! >>> jw >> Jaime, >> >> if piece_id is a primary key you can't do that in SQLObject. >> I tried with Firebird to use BIGINT for pk's. No way! >> >> Uwe >> >> >> ------------------------------------------------------- >> This SF.Net email is sponsored by xPML, a groundbreaking scripting language >> that extends applications into web and mobile media. Attend the live >> webcast and join the prime developer group breaking into this new coding >> territory! >> http://sel.as-us.falkag.net/sel?cmd=lnk&kid=110944&bid=241720&dat=121642 >> _______________________________________________ >> sqlobject-discuss mailing list >> sql...@li... >> https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss > > > ------------------------------------------------------- > This SF.Net email is sponsored by xPML, a groundbreaking scripting language > that extends applications into web and mobile media. Attend the live webcast > and join the prime developer group breaking into this new coding territory! > http://sel.as-us.falkag.net/sel?cmd=lnk&kid=110944&bid=241720&dat=121642 > _______________________________________________ > sqlobject-discuss mailing list > sql...@li... > https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss > |
From: Oleg B. <ph...@ma...> - 2006-03-30 14:22:24
|
On Thu, Mar 30, 2006 at 04:17:22PM +0200, Uwe Grauer wrote: > I was told that there is no way in SQLObject to use pk's which aren't > INT's. > There is no way to specify what the PK-Type is. Currently primary keys can be of types int and str. Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Jeremy F. <je...@go...> - 2006-04-01 07:16:30
|
Jaime Wyant wrote: > Currently I have about 1.7 million rows in there. SQLObject created > the `id' column for the table as `int'. MySQL's int has a value from > |-2147483648| |to 2147483647. |I suspect that I'll be at the maximum > INT value shortly. Is there an `easy' way to reset the ID column to > use BIGINT? I suspect I'll have to regenerate the models, somehow > specifying the id, but I'm not sure. You've used about 1/1200th of your PK ID space. Are you really generating records at such a rate that this is a concern? If so, simply switching to unsigned isn't going to buy you much time. J |
From: Justin J. <jus...@nt...> - 2006-04-01 11:52:25
|
To put that in perspective a little, at a row consumption rate of 1 million a day it'll take approx 5.88 years to hit the limit. Comfortably out of range you might think but I'd sure hate to be the one to alter such a colossal table when the time came. And there are tables out in web land that hit these kind of figures - take some of the larger social networking sites for example. In a sizable system where perhaps there are multiple web servers to handle the traffic along with replication etc, it's likely that time until overflow will be greatly reduced. Being able to specify an ID of BIGINT (bigserial in Postgres) would be one less thing to worry about. Justin On 1 Apr 2006, at 08:17, Jeremy Fitzhardinge wrote: > Jaime Wyant wrote: >> Currently I have about 1.7 million rows in there. SQLObject >> created the `id' column for the table as `int'. MySQL's int has a >> value from |-2147483648| |to 2147483647. |I suspect that I'll be >> at the maximum INT value shortly. Is there an `easy' way to reset >> the ID column to use BIGINT? I suspect I'll have to regenerate >> the models, somehow specifying the id, but I'm not sure. > You've used about 1/1200th of your PK ID space. Are you really > generating records at such a rate that this is a concern? If so, > simply switching to unsigned isn't going to buy you much time. > > J > > > ------------------------------------------------------- > This SF.Net email is sponsored by xPML, a groundbreaking scripting > language > that extends applications into web and mobile media. Attend the > live webcast > and join the prime developer group breaking into this new coding > territory! > http://sel.as-us.falkag.net/sel? > cmd=lnk&kid=110944&bid=241720&dat=121642 > _______________________________________________ > sqlobject-discuss mailing list > sql...@li... > https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss |
From: Jaime W. <pro...@gm...> - 2006-04-01 19:38:28
|
I'm not generating records that quickly, no. In all honesty, I misread the width of the int. But I should've known it was around 2 billion. Thanks for bringing me back down to earth. :) jw On 3/31/06, Jeremy Fitzhardinge <je...@go...> wrote: > > Jaime Wyant wrote: > > Currently I have about 1.7 million rows in there. SQLObject created > > the `id' column for the table as `int'. MySQL's int has a value from > > |-2147483648| |to 2147483647. |I suspect that I'll be at the maximum > > INT value shortly. Is there an `easy' way to reset the ID column to > > use BIGINT? I suspect I'll have to regenerate the models, somehow > > specifying the id, but I'm not sure. > You've used about 1/1200th of your PK ID space. Are you really > generating records at such a rate that this is a concern? If so, simply > switching to unsigned isn't going to buy you much time. > > J > -- "Government does not solve problems; it subsidizes them." Ronald Reagan |