Very frustrated....

[Bill Thayer]

Ok,

I have my Oracle database. From some attempted migrations I have the auth tables defined and two other tables defined of about 17 tables.The typical error is Object with that name already exists or some crap like that. I figured clicking the clean button would help... DON"T DO THAT! Now my browser just spins and spins.

My SQL log has a bunch of crap that i do not want created in my database like archive tables that the wizard created. Yet if I delete the log file it will not re-create with a fresh log.

After restoiring my backup my sql.log file has 2627 lines in it and the time stamps go back 6 days when I started this all from scratch.

Without having to re-create my application again. How can i just get a nice clean SQL file and how, once connect to my DB can I get my new db.py or db_wizard.py definitions to make the database tables that aren't already created without throwing errors for the ones that already are?

[Massimo Di Pierro]

LOL

- Make sure you use stable "Version 2.0.9 (2012-09-13 23:51:30)" of the latest trunk. Not any intermediate version.

- do not use the wizard if you do not want crap. The wizard is experimental and not the best piece of web2py.

- If your tables already exist and you are happy with them run onces with db = DAL(....,fake_migrate=True) and then run with DAL(...,migrate=false)

- If instead you want web2py to create tables and use migrations, delete you database, re-create it empty, delete everything under yourapp/databases/ and run with DAL(...,migrate=True).

[Bill Thayer]

Hi Massimo,

Thank you.

After installing the latest version I deleted the contents of the databases folder and run with DAL(...,migrate=True) got one error, set the table causing the error to migrate=false and re-ran. Now all the database tables are created but now (after seting my settings.migrate to False in 0.py the browser just shows the spinner when I try to access my app's index page. The progress bar is shows only half progress.

Regards,
Bill

[Cliff Kachinske]

Can you connect to the database outside of Web2py?  

The long delay can mean connection problems, which I have induced by typing the db name incorrectly.

[Massimo Di Pierro]

Try run 

python web2py.py -S welcome

then do things manually

>>> db = DAL('oracle://....',migrate=True)

>>> db.define_tables('person',Field('name'))

etc. See what goes wrong. If your want to access tables that are already there you set migrate=False but still need to define_table them.

[Bill Thayer]

Hi Cliff,

Via SQL developer and also through using DAL on the command line. I've seen delays from problems in syntax and misplellings too however omething else is going on. The rocket server will actually lock up and require me to end it's process. Too.

[Bill Thayer]

That's a good suggestion. i was going to create the remaining tables in SQL developer then run web2py with migrate=false.

BTW. The tables I'm having my current problems with are the plug_wiki tables. DAL managed to create PLUGIN_WIKI_PAGE but not PLUGIN_WIKI_ATTACHEMENT. The error returned in the web2py ticket is

<class 'cx_Oracle.DatabaseError'> ORA-02264: name already used by an existing constraint

A very nice piece of information would be WHICH CONSTRAINT? That's what I hate about Oracle.

Does anyone know if it's OK to change the name of this constraint (once I find it) on the database side without breaking my web2py application?

Regards,

Bill

[Cliff Kachinske]

I've seen that.

Certain changes to a model will cause it.  One possibility is adding a notnull constraint to a table that already has data, at least with Postgres and psycopg2.

If you're not working with production data the simplest thing to do would be drop all your tables, clear out the database directories and restart.

If you want test data, Web2py provides a facility to populate your tables with dummy data.  Sorry, I don't remember the manual reference.  Search the book for 'populate.'

If you want more control over your test data, you can always write your own controllers to populate the tables.

[Bill Thayer]

WRT morning's Oracle error:

Oracle hates long names so an identifier like PLUGIN_WIKI_PAGE_MODIFIED_BY_CONSTRAINT and PLUGIN_WIKI_ATTACHEMENT_MODIFIED_BY_CONSTRAINT both get shortened to PLUGIN_WIK_MODIFIE__CONSTRIANT so that creates a conflict on the second one.

-Bill

P.S.

The plugin wiki code is nice. I wound up stripping out much of what I wrote for my app in favor of the wiki approached but the naming conventions are posing problems that newbies like myself are going to find difficult to understand. Then my challenge is to actually use the functionality properly in my app..

[Massimo Di Pierro]

Oracle is the worst database we have to deal with. It does not support pagination. It does not support long names. It deals with BLOB in a different ay then anything else. 

Anyway, my advice is edit plugin_wiki and change the names of the tables to make them shorter.

Or better. Do not use plugin_wiki. Most of the functionality is now in auth.wiki() (although it may present the same problem you are experiencing).

[Bill Thayer]

I'll take a look at auth.wiki if I can find it then.

Unfortunately, large companies have Oracle and have invested $$$ in training and hiring an Oracle DBA. Mentioning it is supported by IT in a project proposal makes points towards risk mitigation. The draw to web2py (and my sales pitch to my company) is that 'mere mortals' can actually create a fexible application for our department that is written in python and will meet our needs for a long time because it can be changed. If someone figures out a simple web2py2Oracle formula using DEV, TEST & PROD environments you will be king!

Honestly, Only my personal persistence and willingness to work all nighters have gotten me this far. So my mere mortal theory is weak because of Oracle.

Regards,
Bill

[Massimo Di Pierro]

You are right. My employer uses Oracle too. Moreover we do claim support.

to use auth.wiki() just create an app that does

def index():

     return auth.wiki()

then try it out. It works very much like plugin_wiki but uses the oembed protocol for embedding widgets. Hopefully that will work well with Oracle.

[Bill Thayer]

Spent the last hour trying to do use auth_wiki() in the Welcome App and documenting my failed experience. Then Google decides it can't post my response. Luckily I was able to copy into a word document so I will post my saga in parts.

[Bill Thayer]

Lets see here....Google not letting me post?

[Bill Thayer]

Here's my saga then...

I took the welcome app, added my oracle connection string and edited

## create all tables needed by auth if not custom tables
auth.define_tables(username=True, signature=True, migrate=False)

[Bill Thayer]

[villas]

When migrating my DB...

1. I have to remember to access a auth.wiki page to make sure my wiki table definitions are run.

2. I always set global variables to migrate and/or fake_migrate my DB and tables but these do not catch the auth.wiki tables.  So I need to make sure I also do fake_migrate_all too.  I think that the migrate/fake_migrate should be exposed for auth.wiki to make this process easier.

Not sure whether this helps in your case,  but I think that this has caused me a problem at times.

[Massimo Di Pierro]

I think you need to read in the book about migrate and fake_migrate. You cannot use a table that you have not defined, you need to migrate(=True) it field.

As I said. Remove you db. Make a blank one again. remove any migrate or fake_migrate option from your code.

def index(): return auth.wiki() should work.

Massimo

[Bill Thayer]

Fixing broken migrations...

db.define_table(....,migrate=False,fake_migrate=True) means rebuild the table meta data according to the table definitions.
db = DAL(...,fake_migrate_all=True) means you are fixing all the tables at once but will not help in narrowing down the problem.

Looks like the metadata matches my table definitions. I have narrowed the problem down to wiki page does not exist in the database. Since it doesn't exist in 
metadata either then what's the problem? Shouldn't the behavior be to create the database table and the table metadata? That is my desire.

if I delete all of my meta data that usually creates more problems. But in theory setting migrate=False, fake_migrate_all=True then all the 
metadata gets rebuilt without touching the database. Not my intention as I want the missing tables added.


    db = DAL('oracle://uname/pw@dbserver:1521', migrate=False, fake_migrate_all=True)

    
    auth.define_tables(username=True, signature=False, migrate=False, fake_migrate=True)

Error: 

<class 'cx_Oracle.DatabaseError'> ORA-00942: table or view does not exist

and Welcome /database has 6 auth tables and wiki_<media, page, tag>

So this should not build metadata but go ahead and build the tables not in the database:

    db = DAL('oracle://uname/pw@dbserver:1521', migrate=True)

    auth.define_tables(username=True, signature=False, migrate=True, fake_migrate=False)

Error:

<class 'cx_Oracle.DatabaseError'> ORA-00955: name is already used by an existing object

No Kidding! There's no way I'm going to delete my auth_user table from Oracle at this point, I have another app using it!

Perhaps this will create the missing tables?

    db = DAL('oracle://uname/pw@dbserver:1521', migrate=True)

    auth.define_tables(username=True, signature=False, migrate=False, fake_migrate=False)

Now my browser is spinning and spinning. I suspect that the rocket server is hung again and I need to stop the process with the task manager. I'll give it a few minutes and see.

[villas]

Myabe you missed my point 1 ?

When migrating my DB...

1. I have to remember to access a auth.wiki page to make sure my wiki table definitions are run.

[Bill Thayer]

As I suspected the rocket server was hung so I had to use the task manager to stop the process and restart. It normally displays the weclome page but got this ticket issued instead:

2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13. 14. 15. 16. 17. 18. 19. 20. 21. 22. 23. 24. 25. 26. 27.

Traceback (most recent call last): File "C:\web2py\gluon\restricted.py", line 209, in restricted exec ccode in environment File "C:/web2py/applications/welcome/controllers/default.py", line 75, in <module> File "C:\web2py\gluon\globals.py", line 186, in <lambda> self._caller = lambda f: f() File "C:/web2py/applications/welcome/controllers/default.py", line 21, in index return auth.wiki() File "C:\web2py\gluon\tools.py", line 3192, in wiki env=env) File "C:\web2py\gluon\tools.py", line 4591, in __init__ db.define_table(key, *value['args'], **value['vars']) File "C:\web2py\gluon\dal.py", line 7095, in define_table table = self.lazy_define_table(tablename,*fields,**args) File "C:\web2py\gluon\dal.py", line 7126, in lazy_define_table polymodel=polymodel) File "C:\web2py\gluon\dal.py", line 905, in create_table self.create_sequence_and_triggers(query,table) File "C:\web2py\gluon\dal.py", line 2826, in create_sequence_and_triggers self.execute(query) File "C:\web2py\gluon\dal.py", line 2820, in execute return self.log_execute(command, args) File "C:\web2py\gluon\dal.py", line 1665, in log_execute ret = self.cursor.execute(*a, **b) DatabaseError: ORA-00904: : invalid identifier

[Bill Thayer]

Sorry villas.

Tried that already but failed to mention it. The auth tables are listed but not the auth.wiki tables. yet they are in the databases folder. The sql log shows the creation in the transcript but the database is not creating them. So is there a particular way to access the auth.wiki pages that I don't know about?

Available Databases and Tables

db.auth_user	New Record
db.auth_group	New Record
db.auth_membership	New Record
db.auth_permission	New Record
db.auth_event	New Record
db.auth_cas	New Record

no wiki tables here yet

[Massimo Di Pierro]

Bill,

have you tried using web2py wil sqlite only? does that work for you? I do not understand the reason for migrate=false.

[Bill Thayer]

Hi Massimo,

    auth.define_tables(username=True, signature=False, migrate=False, fake_migrate=False)

The migrate=false here was an attempt to supress the 
Error:

<class 'cx_Oracle.DatabaseError'> ORA-00955: name is already used by an existing object

Changed my connection string to sqllite and did get a wiki page that said to create page: index. So I entered - test index creation - in the body and got this error:

Traceback (most recent call last):
  File "C:\web2py\gluon\restricted.py", line 209, in restricted
    exec ccode in environment
  File "C:/web2py/applications/welcome/controllers/default.py", line 75, in <module>
  File "C:\web2py\gluon\globals.py", line 186, in <lambda>
    self._caller = lambda f: f()
  File "C:/web2py/applications/welcome/controllers/default.py", line 21, in index

    return dict(form=auth.wiki())
  File "C:\web2py\gluon\tools.py", line 3198, in wiki
    return self._wiki.read(slug)['content'] if slug else self._wiki()
  File "C:\web2py\gluon\tools.py", line 4647, in __call__
    return self.read(zero)
  File "C:\web2py\gluon\tools.py", line 4695, in read
    return dict(content=XML(self.fix_hostname(page.html)))
  File "C:\web2py\gluon\tools.py", line 4678, in fix_hostname
    return body.replace('://HOSTNAME','://%s' % self.host)
AttributeError: 'NoneType' object has no attribute 'replace'

[Bill Thayer]

Is this the correct controller?

def index():
    """
    example action using the internationalization operator T and flash
    rendered by views/default/index.html or views/generic.html

    if you need a simple wiki simple replace the two lines below with:
    return auth.wiki()
    """
    response.flash = T("Welcome to web2py!")
    return auth.wiki()