Hello,
I'm running web2py on a Windows server with MS SQL, it's running from
source, as a service.
I tried upgrading from version 1.93.2 to the latest stable (2.3.2), and on
launching database administration, I got an IntegrityError.
----- Here's the table definition:
UserTableName = auth.settings.table_user_name
auth_table = db.define_table(
UserTableName,
Field('first_name', length=128, requires = IS_NOT_EMPTY()),
Field('last_name', length=128, requires = IS_NOT_EMPTY()),
Field('email', length=128, unique=True, requires = [IS_EMAIL(),
IS_NOT_EMPTY()]),
Field('password', 'password', length=256, readable=False,
label='Password'),
Field('department', db.department),
Field('is_department_manager', 'boolean', default = False),
Field('registration_key', length=128, default= '', writable=False,
readable=False),
Field('reset_password_key', length=512, writable=False, readable=False,
default=''),
format = '%(first_name)s %(last_name)s'
)
auth_table.password.requires = [CRYPT()]
------ Here's the traceback:
File "C:\Program Files\Hub Pages\web2py\gluon\restricted.py", line 212,
in restricted
exec ccode in environment
File "C:/Program Files/Hub
Pages/web2py/applications/HubFormsTidy/models/0_db.py", line 59, in <module>
format = '%(first_name)s %(last_name)s'
File "C:\Program Files\Hub Pages\web2py\gluon\dal.py", line 7186, in
define_table
table = self.lazy_define_table(tablename,*fields,**args)
File "C:\Program Files\Hub Pages\web2py\gluon\dal.py", line 7222, in
lazy_define_table
polymodel=polymodel)
File "C:\Program Files\Hub Pages\web2py\gluon\dal.py", line 963, in
create_table
fake_migrate=fake_migrate)
File "C:\Program Files\Hub Pages\web2py\gluon\dal.py", line 1069, in
migrate_table
self.execute(sub_query)
File "C:\Program Files\Hub Pages\web2py\gluon\dal.py", line 1709, in
execute
return self.log_execute(*a, **b)
File "C:\Program Files\Hub Pages\web2py\gluon\dal.py", line 1703, in
log_execute
ret = self.cursor.execute(*a, **b)
IntegrityError: ('23000', "[23000] [Microsoft][ODBC SQL Server Driver][SQL
Server]The CREATE UNIQUE INDEX statement terminated because a duplicate key
was found for the object name 'dbo.auth_user' and the index name
'UQ__auth_user__3335971A'. The duplicate key value is (<NULL>). (1505)
(SQLExecDirectW); [42000] [Microsoft][ODBC SQL Server Driver][SQL
Server]Could not create constraint. See previous errors. (1750); [01000]
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
terminated. (3621)")
------- Here's the last bit of sql.log in web2py's databases directory (I
added blank lines for clarity):
timestamp: 2013-01-29T17:21:14.585000
ALTER TABLE auth_user ADD password__tmp VARCHAR(256) NULL;
UPDATE auth_user SET password__tmp=password;
ALTER TABLE auth_user DROP COLUMN password;
ALTER TABLE auth_user ADD password VARCHAR(256) NULL;
UPDATE auth_user SET password=password__tmp;
ALTER TABLE auth_user DROP COLUMN password__tmp;
timestamp: 2013-01-29T17:21:14.585000
ALTER TABLE auth_user ADD registration_key__tmp VARCHAR(128) NULL;
UPDATE auth_user SET registration_key__tmp=registration_key;
ALTER TABLE auth_user DROP COLUMN registration_key;
ALTER TABLE auth_user ADD registration_key VARCHAR(128) NULL;
UPDATE auth_user SET registration_key=registration_key__tmp;
ALTER TABLE auth_user DROP COLUMN registration_key__tmp;
timestamp: 2013-01-29T17:21:14.585000
ALTER TABLE auth_user ADD email__tmp VARCHAR(128) NULL UNIQUE;
(it stops here)
---------------
It seems that the newer version of web2py deemed there were some
differences between how the table is defined in the web2py model file and
how it is in the database, tried to alter the database, but failed.
There were no changes to the database, I just did an upgrade is all, so I
don't see the need for it to have tried a migrate on that table, but
regardless of that there is an issue.
I'm not a SQL expert, but am I right in thinking that what is happening is
that web2py is trying to create column 'email__tmp' which allows NULLS, yet
has a unique constraint, which Ms SQL won't allow as some of the values
will be NULL seeing as there are existing records in the table? Also, it
seems to be creating all the tables as allowing NULLS, even though in the
model I specify "requires = IS_NOT_EMPTY()" on these columns. If it is
necessary to allow NULLS to enable copying of values, then I would expect
to see the columns retrospectively altered to not allow NULLS, and only at
that point should we add any UNIQUE constraints...
Did the DAL migrate functionality change significantly between version
1.93.2 and 2.3.2?
Can anyone advise?
many thanks,
Andrew.
--
---
You received this message because you are subscribed to the Google Groups
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
