MySql table already exists error

[mmstud]

I dont know how this should be, but in my case, i have a table
'Members' already created on database having inserted rows and data to
use in my application. But when i try 'database administration' it
seems web2py tries to create a new table, although i think it should
just see, if table definition matches table in db.

I countered this problem again, when changed mysql://user:pass to
db.py... when i changed back to previous connection string, everything
worked again.

-Marko

Traceback (most recent call last):
File "/home/.../tmp/web2py/gluon/restricted.py", line 62, in
restricted
exec ccode in environment
File "/home/.../tmp/web2py/applications/app/models/db.py", line 49,
in <module>
SQLField('Memo','text')
File "/home/.../tmp/web2py/gluon/sql.py", line 540, in define_table
raise e
OperationalError: (1050, "Table 'Members' already exists")

[mdipierro]

in define_table(....) you need to set define_table(....,migrate=False)
and it will not try to recreate the table if it exist.
web2py only known is a table exist if web2py created it.

Massimo

[mmstud]

Confirmed, what i just read from "Rolling with web2py". Thanks.

[Thomas Dall'Agnese]

But if we change the table definition, for example we add one field, it will not be updated then, isn't it?

How can we migrate a MySQL table?

[Massimo Di Pierro]

There are various scenarios:

1) the table exist in db but were not created by web2py
run at least once with migrate=True,fake_migrate=True
2) the table exist in db and where created by web2py (or you run
fake_migrate once)
it is ok to run with migrate=False
3) web2py is aware of the tables (created by web2py or you run
fake_migrate) but you need to change them
set migrate=True until the migration has happened.

I guess you are asking about 3.

On Dec 30, 9:53 pm, Thomas Dall'Agnese <thomas.dallagn...@gmail.com>
wrote:

[Thomas Dall'Agnese]

Indeed, I am talking about (3), and in that case, running with migrate=True raises an error (table already exists).

The table had been created by web2py so of course it exists.

In case (3) should I also have fake_migrate=True?

[Massimo Di Pierro]

Not quote. First, your model must exactly describe what is in the db.
Then you set fake_migrate=True, this will create the metadata. Then
you set fake_migrate=False (or remove it) and migrate=True. Not you
can change the model and web2py will perform the ALTER TABLE.

If web2py does not know what is in DB it will always try create the
table not alter it.

Massimo

On Dec 30, 10:01 pm, "Thomas Dall'Agnese"

[Thomas Dall'Agnese]

I see, thanks for your help!

Cheers,

Thomas