table auth_user already exists

[Christopher Baron]

Hi web2py users,

Just upgraded to Version 1.97.1

I checked my code out into the new directory, opened my browser, and
received and error stating 'auth_cas' already exists.

I removed all files from the databases folder and tried again
receiving an error that 'auth_user' already exists.

I'm using a private/config file that points to some mysql database on
another server.

What can I do ?

I'd be happy to share more information needed to help solve the
problem.

Thanks in advance,

Chris

[Richard Vézina]

Did you upgrade by the web2py "admin" update link?

Richard

[Richard Vézina]

Have you try :

http://www.web2py.com/book/default/chapter/06?search=migrate#Fixing-Broken-Migrations

http://www.web2py.com/book/default/chapter/06?search=migrate#Migrations

You should use migrate=False in your models once your tables have been created and you know you will not have to modify them...

Richard

[Christopher Baron]

I upgraded via the admin update link and received an error, so I went
ahead and downloaded the source.

Regarding the migration techniques, the exception is being thrown on
creating web2py auth tables - specifically the following line of
code :

auth.define_tables()

Shouldn't web2py handle this for me? Perhaps I need to include a line
similar to the following?

db = DAL('sqlite://storage.db', migrate=False)



On Jun 27, 2:45 pm, Richard Vézina <ml.richard.vez...@gmail.com>
wrote:
> Have you try :
>
> http://www.web2py.com/book/default/chapter/06?search=migrate#Fixing-B...http://www.web2py.com/book/default/chapter/06?search=migrate#Migrations

>
> You should use migrate=False in your models once your tables have been
> created and you know you will not have to modify them...
>
> Richard
>

> On Mon, Jun 27, 2011 at 3:40 PM, Richard Vézina <ml.richard.vez...@gmail.com

[Massimo Di Pierro]

Try this:

db = DAL('sqlite://storage.db', migrate=True, fake_migrate=True)

if everything works than your .table files are correct, remove
migrate=True, fake_migrate=True and you should be fine.

On Jun 27, 3:07 pm, Christopher Baron <cba...@fieldmuseum.org> wrote:
> I upgraded via the admin update link and received an error, so I went
> ahead and downloaded the source.
>
> Regarding the migration techniques, the exception is being thrown on
> creating web2py auth tables - specifically the following line of
> code :
>
> auth.define_tables()
>
> Shouldn't web2py handle this for me?  Perhaps I need to include a line
> similar to the following?
>
> db = DAL('sqlite://storage.db', migrate=False)
>
> On Jun 27, 2:45 pm, Richard Vézina <ml.richard.vez...@gmail.com>
> wrote:
>
>
>
>
>
>
>
> > Have you try :
>
> >http://www.web2py.com/book/default/chapter/06?search=migrate#Fixing-B...
>

[Christopher Baron]

I've tried

db = DAL("mysql://%s:%s@%s/%s" % (user, password, host, dbname),
migrate=False)

and still have the same problem.

On Jun 27, 3:07 pm, Christopher Baron <cba...@fieldmuseum.org> wrote:

> I upgraded via the admin update link and received an error, so I went
> ahead and downloaded the source.
>
> Regarding the migration techniques, the exception is being thrown on
> creating web2py auth tables - specifically the following line of
> code :
>
> auth.define_tables()
>
> Shouldn't web2py handle this for me?  Perhaps I need to include a line
> similar to the following?
>
> db = DAL('sqlite://storage.db', migrate=False)
>
> On Jun 27, 2:45 pm, Richard Vézina <ml.richard.vez...@gmail.com>
> wrote:
>
>
>
>
>
>
>
> > Have you try :
>
> >http://www.web2py.com/book/default/chapter/06?search=migrate#Fixing-B...
>

[Massimo Di Pierro]

Wait... don't just try this or you messed up more. You first used
'sqlite://...' and it told you "table already exists". Now you need to
fix that before you can change database (you changed to "mysql").

[Christopher Baron]

Ok,

auth.define_tables( migrate=False )

seems to fix it, but what happens when a future web2py update changes
the auth tables ?

[Anthony]

Note, the 'migrate' and 'fake_migrate' arguments to DAL may not do what you think they should do -- they merely set the 'migrate' and 'fake_migrate' default values for any table definitions that do not explicitly set those arguments -- if a table explicitly sets one or both of those arguments, that will override the DAL arguments. I think auth.define_tables may explicitly set 'migrate' to True for auth tables unless you do auth.define_tables(migrate=False).

If you want to override the explicit arguments of all individual tables, use DAL(...,fake_migrate_all=True) to force fake migration of all tables, and DAL(...,migrate_enabled=False) to force all migrations off.

Anthony

On Monday, June 27, 2011 4:09:54 PM UTC-4, Christopher Baron wrote:

I've tried

db = DAL("mysql://%s:%s@%s/%s" % (user, password, host, dbname),
migrate=False)

and still have the same problem.

On Jun 27, 3:07 pm, Christopher Baron <cba...@fieldmuseum.org> wrote:
> I upgraded via the admin update link and received an error, so I went
> ahead and downloaded the source.
>
> Regarding the migration techniques, the exception is being thrown on
> creating web2py auth tables - specifically the following line of
> code :
>
> auth.define_tables()
>
> Shouldn't web2py handle this for me?  Perhaps I need to include a line
> similar to the following?
>
> db = DAL('sqlite://storage.db', migrate=False)
>

> On Jun 27, 2:45 pm, Richard Vézina <ml.richa...@gmail.com>

> wrote:
>
>
>
>
>
>
>
> > Have you try :
>
> >http://www.web2py.com/book/default/chapter/06?search=migrate#Fixing-B...
>
> > You should use migrate=False in your models once your tables have been
> > created and you know you will not have to modify them...
>
> > Richard
>

> > On Mon, Jun 27, 2011 at 3:40 PM, Richard Vézina <ml.richa...@gmail.com

[pbreit]

I still need a tutorial on 1) how the .table and sql.log files are used, 2) what exactly migrate and fake_migrate do and 3) how to best resolve broken migrations.

[Chris]

I've found what seems a simple way to let multiple apps share a DB and create the same table definitions without conflict.  Chris, you may find this useful and also I'd love to hear from the true experts if there are problems with this approach.  (It works for me but maybe there are other implications).

Step 1, be sure that each application is applying the exact same table definition / structure (by defining in a module that all apps import or whatnot).  There isn't any table-sharing approach that will work right if two different applications are competing violently to change the schema over and over.

Step 2, have all the subsystems share a single directory where the *.table files are stored.  The usual behavior is that each app has its own store of *.table files in its own app-local ./databases directory.  The problem with conflicting table creates happens, as far as I can tell, when a table already exists but there is no corresponding table-definition file in the app's own local ./databases directory.  In web2py / gluon / dal.py / BaseAdapter / create_table(), the logic that determines the location and name of the table-definition file looks like this (in web2py version 1.99.7):

## at line 697
dbpath = self.folder
## e.g. /path/to/web2py/applications/appname/databases

## at line 706
table._dbt = os.path.join(dbpath, '%s_%s.table' % (table._db._uri_hash, tablename))
## e.g. /path/to/web2py/applications/appname/databases/51bd584ac1754de09bdf0aac5438df72_tablename.table

In other words, the path is derived from the app name (and is below the app directory), and the file name is derived from the DB URI + table name.

To allow multiple apps to share table-definition files, you could either change DAL code to point to a different folder that doesn't differ per app (maybe under web2py or web2py/private), or you could leave DAL alone and use symbolic links to make all the ./databases folders point to the same place.

What I've done is create a web2py / applications / databases folder that isn't really an app (isn't mentioned in routes.py and so on), then for each app:

## on OSX 10.7.3, may differ on other OS's
ln -s /path/to/web2py/applications/databases /path/to/web2py/applications/someapp/databases

(Note no trailing "/" on either of the paths.)  This allows DAL to continue as usual, but all apps are reading & writing the same set of table definition files.

There are some tradeoffs between doing this in DAL and doing via symlinks.  If you do it via DAL, then there are no additional steps to configuring your app other than deploying the right source files, and you don't rely on having an OS that supports symlinks -- although that's a smaller issue than it used to be now that Windows Vista supports symlinks; however this approach does mean a source code change, that you would need to keep track of over web2py releases, and might obligate you to publish the code and so on for licensing reasons.  Also if you do it via DAL, if you want different subsets of  apps in a single web2py instance to share DBs differently (i.e. it's not all-or-none), you would have to add logica saying which shared directories are for which apps, via a configuration file or whatever.  If you do it via symlinks, then you don't have to change source code, you can decide which apps share table definitions or not by which app directories you point to which shared directories -- but you have to know how to make these changes as you move code between machines, changing deployment from a "drop the files in" approach to "... then run these scripts", which is probably a matter of taste.

In any case, once I have apps sharing a folder for table-definition files, this problem goes away.

[Niphlod]

all of this instead of

migrate=False

where you know a table already exist ?????