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.