Massimo, thanks for looking into this.
These are the contents of file the ....auth_user.table file:
(dp1
S'first_name'
p2
S'VARCHAR(128)'
p3
sS'last_name'
p4
S'VARCHAR(128)'
p5
sS'is_department_manager'
p6
S'BIT'
p7
sS'email'
p8
S'VARCHAR(128) UNIQUE'
p9
sS'reset_password_key'
p10
S'VARCHAR(512)'
p11
sS'department'
p12
S'INT, CONSTRAINT auth_user_department__constraint FOREIGN KEY (department)
REFERENCES department(id) ON DELETE CASCADE'
p13
sS'password'
p14
S'VARCHAR(256)'
p15
sS'registration_key'
p16
S'VARCHAR(128)'
p17
sS'id'
p18
S'INT IDENTITY PRIMARY KEY'
p19
s.
On Wed, Jan 30, 2013 at 3:06 PM, Massimo Di Pierro <
massimo.dipie...@gmail.com> wrote:
> Can you send me your .table file for auth_user. Before the migration.
>
>
> On Wednesday, 30 January 2013 07:17:11 UTC-6, Andrew Buchan wrote:
>>
>> 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.
>
>
>
--
---
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.
