Oh, it's no problem, I'm all good now. I was just trying to let you
guys know how I got in this mess in hopes that you could prevent it in
the future for others.
-Jim
On 5/8/2012 3:41 PM, villas wrote:
Hi Jim
Sounds like a bit of a tangle. However, I think you should be ok. I
would try this:
1. Use your DB management tool to change the keys on the newly added
table to BIGINT (make sure Foreign Key references and any triggers
are consistent too.
2. Backup your existing *.table files in the databases folder and
then delete all those files.
3. On the DAL connection in db.py, set
... bigint_id=True, fake_migrate_all=True
4. Run your app and see whether all the .table files are re-created.
5. If OK, on the DAL connection you can delete the fake_migrate_all
setting. (Note when your app is production ready
set migrate_enabled=False to stop all the migration checking).
Regards,
D
On Tuesday, 8 May 2012 21:11:34 UTC+1, Jim S wrote:
In looking back, I think this is what got me.
1. When bigint_id=True became the default, my apps broke and I'm
not sure why
2. To fix it, I created a blank db and let things auto-migrate to
create new
3. I dumped the data from the original and imported to my new db
4. Then I recreated my original db using the same name as before
and dumped in the structure and data from the db created in step 2
5. At some point, bigint_id=False became the default. My new db
ceased working and I reverted to using the original (recreated)
database successfully. I worked with that for a time and since
there were no db structural changes generated for a time that
included a foreign key, I got along well.
6. Today I added a table with a foreign key and it generated the
SQL as though the id fields were INTs (but all ID fields were
BIGINT). This failed.
What I'm thinking is the problem is that somewhere in the files in
the databases sub-directory, it is thinking that my original
(recreated) db is using INTs and doesn't realize that I changed
them all to BIGINTs outside of web2py.
Is there a way to tell web2py to recreate the stuff in the
databases sub-directory for the current connection?
-Jim
On 5/8/2012 2:48 PM, villas wrote:
Hi Jim
Yes, some of this discussion has been going on the developer list
and I made a post there to try and sum up the current situation.
I will re-post this summary here:
1. If you explicitly set bigint_id=True all references will use
BIGINT keys.
2. Otherwise, it will continue to use INTs as keys as usual (the
default is now bigint_id=False).
3. On starting a new project, if you feel that BIGINT id fields
are appropriate then set bigint_id=True.
4. If you really do need to change this setting on an existing
DB, make sure you alter all the ids and references directly on
the DB (outside web2py) and make sure all triggers and SPs are
also OK. You should not rely on auto-migration.
5. For all DBs (including those which have INT keys), you can
now use separate BIGINT integer fields as there is now a 'bigint'
fieldtype.
Regards, David
On Tuesday, 8 May 2012 18:14:28 UTC+1, Jim S wrote:
Well, that explains things quite nicely. I guess that is
what I get for working with trunk all the time.
This also explains what was happening to me the post I
referenced below.
-Jim
On 5/8/2012 11:48 AM, Carlos wrote:
Hi Jim,
The default now is bigint_id=False, so try to pass
bigint_id=True in the connection string.
Carlos
On Tuesday, May 8, 2012 11:40:44 AM UTC-5, Jim S wrote:
My problem is the opposite. All of my tables now have
BIGINT for the id columns. The SQL generated today is
now creating them with INT columns and the reference
fields are also being created with INT. When I changed
the SQL (using an outside SQL tool) from using INTs to
BIGINTs it all worked (with migrate=False,
fake_migrate=True).
I'm not passing anything to bigint_id in the connect
string. I do want (and have) all my models using BIGINT
now. Except for this new problem, all has been fine.
-Jim
On 5/8/2012 10:27 AM, Richard Vézina wrote:
Jim,
Try this : bigint_id=False in connection string I think...
https://groups.google.com/forum/?fromgroups#!topic/web2py-developers/DrCFEnZIYt4
<https://groups.google.com/forum/?fromgroups#%21topic/web2py-developers/DrCFEnZIYt4>
Also, I think with trunk your entire database models is
now consider bigint. It maybe not what you want.
Richard
On Tue, May 8, 2012 at 10:53 AM, Jim S <j...@qlf.com
<mailto:j...@qlf.com>> wrote:
I upgraded to this trunk right when it came out. I
upgrade trunk again yesterday (5/7/2012) and
created a new table today with a reference field to
an old table. Here is the SQL that was generated
(MySQL).
CREATE TABLE ticketActivity(
ticketActivityId INT AUTO_INCREMENT NOT NULL,
ticketId INT, INDEX ticketId__idx (ticketId),
FOREIGN KEY (ticketId) REFERENCES ticket(ticketId)
ON DELETE CASCADE,
createdOn DATETIME,
activity LONGTEXT,
PRIMARY KEY(ticketActivityId)
) ENGINE=InnoDB CHARACTER SET utf8;
This failed. I changed the INTs to BIGINTs and it
worked. I'm wondering if something related to this
post got reverted that caused my generated SQL to
not use BIGINT now.
Or, am I losing my mind? -->
https://groups.google.com/forum/?fromgroups#!topic/web2py/nGB1nYlpHwA
<https://groups.google.com/forum/?fromgroups#%21topic/web2py/nGB1nYlpHwA>
-Jim
On Saturday, April 21, 2012 12:37:15 PM UTC-5,
Massimo Di Pierro wrote:
There is a change in trunk. I replaced 'id' and
'reference' types from INT to BIGINT (when
supported).
If you have an existing table it should not
cause a migration and there is an explicit
check to avoid a migration that would break tables.
The bottom line is hat new tables are not
affected but new tables will have the BIGINT.
SQLite does not support BIGINT AUTOINCREMENT
therefore nothing happens there.
Yet, this needs to be tested with the other DB
engines. Make sure you backup your data before
testing this feature by upgrading to trunk your
production environment.
massimo
On Saturday, April 21, 2012 12:37:15 PM UTC-5,
Massimo Di Pierro wrote:
There is a change in trunk. I replaced 'id' and
'reference' types from INT to BIGINT (when
supported).
If you have an existing table it should not
cause a migration and there is an explicit
check to avoid a migration that would break tables.
The bottom line is hat new tables are not
affected but new tables will have the BIGINT.
SQLite does not support BIGINT AUTOINCREMENT
therefore nothing happens there.
Yet, this needs to be tested with the other DB
engines. Make sure you backup your data before
testing this feature by upgrading to trunk your
production environment.
massimo
On Saturday, April 21, 2012 12:37:15 PM UTC-5,
Massimo Di Pierro wrote:
There is a change in trunk. I replaced 'id' and
'reference' types from INT to BIGINT (when
supported).
If you have an existing table it should not
cause a migration and there is an explicit
check to avoid a migration that would break tables.
The bottom line is hat new tables are not
affected but new tables will have the BIGINT.
SQLite does not support BIGINT AUTOINCREMENT
therefore nothing happens there.
Yet, this needs to be tested with the other DB
engines. Make sure you backup your data before
testing this feature by upgrading to trunk your
production environment.
massimo
On Saturday, April 21, 2012 12:37:15 PM UTC-5,
Massimo Di Pierro wrote:
There is a change in trunk. I replaced 'id' and
'reference' types from INT to BIGINT (when
supported).
If you have an existing table it should not
cause a migration and there is an explicit
check to avoid a migration that would break tables.
The bottom line is hat new tables are not
affected but new tables will have the BIGINT.
SQLite does not support BIGINT AUTOINCREMENT
therefore nothing happens there.
Yet, this needs to be tested with the other DB
engines. Make sure you backup your data before
testing this feature by upgrading to trunk your
production environment.
massimo
On Saturday, April 21, 2012 12:37:15 PM UTC-5,
Massimo Di Pierro wrote:
There is a change in trunk. I replaced 'id' and
'reference' types from INT to BIGINT (when
supported).
If you have an existing table it should not
cause a migration and there is an explicit
check to avoid a migration that would break tables.
The bottom line is hat new tables are not
affected but new tables will have the BIGINT.
SQLite does not support BIGINT AUTOINCREMENT
therefore nothing happens there.
Yet, this needs to be tested with the other DB
engines. Make sure you backup your data before
testing this feature by upgrading to trunk your
production environment.
massimo
