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.
