cpdb errors

[Keith Edmunds]

I'm trying to copy a database from Sqlite to MySQL using the cpdb.py
script. In the manual, it states:

cd web2py
python scripts/cpdb.py \
-f applications/app/databases \
-y 'sqlite://storage.sqlite' \
-Y 'postgresql://username:password@hocalhost/mydb'

However, if I do that I get "gluon path not found". The help file suggests
that I can specify the path to dal.py with -d, but:

python scripts/cpdb.py -d gluon/dal.py -f applications/pytrack2/databases
-y 'sqlite://storage.sqlite' -Y
'mysql://pytrack2:pytrack2@localhost/pytrack2'
gluon path not found
EXCEPTION: could not set DAL
No module named dal

If I use '-d gluon', it's better but now fails with a MySQL error (which I
think relates to foreign keys), although it still gives the 'gluon path
not found' error:

$ python scripts/cpdb.py -d gluon -f applications/pytrack2/databases -y
'sqlite://storage.sqlite' -Y
'mysql://pytrack2:pytrack2@localhost/pytrack2'
gluon path not found
creating tables...
EXCEPTION: could not make a copy of the database
(1005, u"Can't create table 'pytrack2.t_companies_archive' (errno: 150)")

I'm going to continue by migrating by hand; if I should report this
elsewhere or if you need more details, let me know.
--
"You can have everything in life you want if you help enough other people
get what they want" - Zig Ziglar.

Who did you help today?

[mart]

the first message is expected because of this import:

try:

    from gluon import DAL

except ImportError as err:

    print('gluon path not found')  

It wants to assume that that gluon is already loaded as part of the web2py environment (or as part of some other environment, like Aptana for example) and that you already have /gluon in your path (maybe even hard coded). If the gluon path is not set, well, you will get that "gluon path not found" message and the -d option will be expected. Because the -d option is expecting a relative path, your cmd line args will look different depending on the environment in which you are running the script. 

Hum... I guess being relative is relative ;) 

So, as an example, if you were to run this cmd from a simple bash shell (outside of any dev environment, or outside of a web2py context), you would simply cd and drill down into .../blabla/.../web2py/scripts/. In which case, your -d option would look like this:

-d ../gluon.  

I just did this, and it all worked fine:

macMart:Documents mart$ cd Aptana\ Studio\ Workspace/_p4/src/web2py/scripts/

macMart:scripts mart$ python cpdb.py -f ../../db_storage -d ../gluon -y sqlite://storage.sqlite -Y sqlite://storage2.sqlite -F ../../db_storage2

gluon path not found    <== THIS IS AN EXPECTED MESSAGE

creating tables...

exporting data...

importing data...

done!

Hope it helps and that it makes sense!

Mart :)

[Simon Ashley]

Interesting but encountering similar problems to backseat on windows 7 using the following command from a prompt in web2py root folder: i.e.

c:\web2py> set path=%path%;c:\web2py\gluon
c:\web2py> c:\python27\python scripts\cpdb.py -d gluon -f application\app\databases -y 'sqlite://storage.sqlite' -Y 'postgres://postgres:password@localhost:5432/db_target'

It ends up with the attached errors, implying more path issues:

Can anyone suggest windows commands/ path's that may work? 

(Noted the Aptana environment solution, but have a time frame issue. Otherwise may have to go back to backseat's solution of individual exports/ imports)

[mart]

hum... you should just remove the quotes around the connection strings (options -y and -Y)  since they are already strings when passed in.

Otherwise, argparse will do this: " ' sqlite://storage.sqlite' " and then dal will not be able to open the DB.

So, no quotes, should fix the problem.

-y sqlite://storage.sqlite  &   -Y postgres://postgres:password@localhost:5432/db_target

I haven't run this in a long time, but just tried it and it ran just fine.  

Note, the -d option is pointing to a recent dal.py which i simply dumped

in the /Users/mart directory.

I don't have a windows machine, but I think i can find one at work tomorrow and see if it behaves differently.

guimauve:src mart$ python cpdb.py -f db_storage/cvs -d /Users/mart -y sqlite://auth_storage.sqlite -Y sqlite://storage2.sqlite -F db_storage/cvs_2

gluon path not found

creating tables...

exporting data...

importing data...

done!

Attention: do not run this program again or you end up with duplicate records

guimauve:src mart$ 

BTW - @ user backseat:  Perhaps a little late, but I just noticed now... same goes for you, the quotes need to be removed.

If the manual says "sqlite://storage.sqlite" for options -y and -Y, then that should be simply be corrected.

Hope it helps,

Mart 

[Simon Ashley]

Thanks Mart, 

That's sort of fixed it, but starting to get additional issues with the auth tables. 

Think have seen this before with csv imports and have gotten around by deleting suspect areas from the import source files.

(obviously not really an option here, and now wondering if that hack is causing a postgres memory leak on import)

ps. what OS and web2py version are you using?

(source 2.3.2 here)

[mart]

Hi Simon,

Yes, that looks like a CSV error message. Possibly, the CSV format is missing something expected... I would say that the problem occurs when converting from CSV. I would suggest that you take a look at  Massimo's "CSVStudio"  @    https://code.google.com/p/csvstudio/  

i think it has something that will convert your CSV cols and rows to a model by generating  "db.define_table(...) "

Never the less, I will keep looking and see what turns up.

I ran these on either a Linux flavour or MacOS... Unfortunately, I wasn't able to find a usable windows box today.

I didn't run this against entire web2py filesets, I simply pointed to a location on my laptop, dumped different versions of dal.py there

replacing them with what ever version I found - some old some more recent)... But they all worked.

I hope this helps,

Mart :)

[Simon Ashley]

Thanks Mart,

That gives me some clues and a renewed focus. Issue may be with postgres (at least the install I had - has been deleted and will be installed). Will also try ubuntu environment. Will report back ...

 ... confirmed the memory leak with postgres (eventually absorbs all available memory) under win7. The same csv file imports fine under SQLite and mySQL. Connection strings are:

    db = DAL('sqlite://storage.sqlite', lazy_tables=True, migrate=True, fake_migrate=False, check_reserved=['all','mysql', 'postgres', 'postgres_nonreserved'])
    db = DAL('postgres://postgres:xx@localhost:5433/yyy', lazy_tables=True, migrate=True, fake_migrate=False, check_reserved=['all','mysql', 'postgres', 'postgres_nonreserved'])
    db = DAL('mysql://root:xx@localhost:3306/zzz', lazy_tables=True, migrate=True, fake_migrate=False, check_reserved=['all','mysql', 'postgres', 'postgres_nonreserved'])

[mart]

Hi Simon,

Thanks for the update! This is interesting. One question, are you making use of cache.py? I had huge memory problems a while ago and I didn't have time to debug. A colleague was testing on windows and each time it crapped out his system where he was forced to do a hard reboot (but seemed to work fine on Linux). I ended up pulling it out until such time as I could upgrade to a later web2py release (I believe Massimo had made a fix in a later release than I was using that addressed the memory leak issue (but I can't remember any more details on this, so I may be out of my tree :) ). Anyways, I was just wondering if you were using an older web2py version ?

Thanks,

Mart :)

On Monday, January 7, 2013 8:01:01 PM UTC-8, Simon Ashley wrote:

Thanks Mart,

[Simon Ashley]

That's a negative on cache.py. 

It's from a nightly build pulled about 5 days ago.

The csv in question is about 150k records. 

(have pruned it down to a test of 10 records, and they import OK).

Has about 140 fields, quite a few nulls, and have compensated for the adapter's postgres decimal precision issue.

(its the largest table in the app, and others appear to import OK)

Still building a u12.10 environment under vmware to test 

(linux skills are rusty but coming back)

[Massimo Di Pierro]

Is there still an open issue here?

[Martin Senecal]

I don't think so. Once Simon  unquoted his cmd line option values, he was able to do a DB copy with the following OS/DB  combinations:

Windows + sqlite

Windows + mysql

But is reporting issues with

Windows + postgres (memory leak?)

His plan is to repeat the same steps but on a linux system, then compare/diff output.

I think it is unrelated to the script, but i dont have a windows machine to test/debug.

Mart :)

Sent from mart's iPhone

--
 
 
 

[Simon Ashley]

Just confirming that csv imports fail with Postgres on Ubuntu 12.10, but both sqlite and mysql import fine. 

Suspect its a postgres issue, and will attempt different adapters/ refactor of the DAL table def to try and identify the cause

Error message is fairly generic.

 

Traceback (most recent call last):
  File "/users/www-data/web2py/gluon/restricted.py", line 212, in restricted
    exec ccode in environment
  File "/users/www-data/web2py/applications/crm2_04_PSQL/controllers/data.py", line 13, in <module>
  File "/users/www-data/web2py/gluon/globals.py", line 193, in <lambda>
    self._caller = lambda f: f()
  File "/users/www-data/web2py/applications/crm2_04_PSQL/controllers/data.py", line 5, in import_data
    db.import_from_csv_file(open('crm0721.csv', 'rb'))
  File "/users/www-data/web2py/gluon/dal.py", line 7423, in import_from_csv_file
    ifile, id_map, null, unique, id_offset, *args, **kwargs)
  File "/users/www-data/web2py/gluon/dal.py", line 8076, in import_from_csv_file
    curr_id = self.insert(**dict(items))
  File "/users/www-data/web2py/gluon/dal.py", line 7918, in insert
    ret =  self._db._adapter.insert(self,self._listify(fields))
  File "/users/www-data/web2py/gluon/dal.py", line 1176, in insert
    raise e
InternalError: Unexpected response msg <ReadyForQuery Idle in Failed Transaction>

Exception during snapshot rendering: Traceback (most recent call last):
  File &quot;/users/www-data/web2py/applications/admin/views/default/ticket.html&quot;, line 160, in &lt;module&gt;
  File &quot;/users/www-data/web2py/gluon/globals.py&quot;, line 202, in write
    self.body.write(xmlescape(data))
  File &quot;/users/www-data/web2py/gluon/html.py&quot;, line 120, in xmlescape
    return data.xml()
  File &quot;/users/www-data/web2py/gluon/html.py&quot;, line 914, in xml
    (fa, co) = self._xml()
  File &quot;/users/www-data/web2py/gluon/html.py&quot;, line 905, in _xml
    self.components])
  File &quot;/users/www-data/web2py/gluon/html.py&quot;, line 120, in xmlescape
    return data.xml()
  File &quot;/users/www-data/web2py/gluon/html.py&quot;, line 914, in xml
    (fa, co) = self._xml()
  File &quot;/users/www-data/web2py/gluon/html.py&quot;, line 905, in _xml
    self.components])
  File &quot;/users/www-data/web2py/gluon/html.py&quot;, line 120, in xmlescape
    return data.xml()
  File &quot;/users/www-data/web2py/gluon/html.py&quot;, line 914, in xml
    (fa, co) = self._xml()
  File &quot;/users/www-data/web2py/gluon/html.py&quot;, line 905, in _xml
    self.components])
MemoryError

Traceback (most recent call last):
  File "/users/www-data/web2py/gluon/main.py", line 632, in wsgibase
    BaseAdapter.close_all_instances('rollback')
  File "/users/www-data/web2py/gluon/dal.py", line 543, in close_all_instances
    db._adapter.close(action)
  File "/users/www-data/web2py/gluon/dal.py", line 523, in close
    getattr(self, action)()
  File "/users/www-data/web2py/gluon/dal.py", line 1670, in rollback
    if self.connection: return self.connection.rollback()
  File "/users/www-data/web2py/gluon/contrib/pg8000/dbapi.py", line 455, in _fn
    return fn(self, *args, **kwargs)
  File "/users/www-data/web2py/gluon/contrib/pg8000/dbapi.py", line 551, in rollback
    self.conn.rollback()
  File "/users/www-data/web2py/gluon/contrib/pg8000/interface.py", line 622, in rollback
    self._rollback.execute()
  File "/users/www-data/web2py/gluon/contrib/pg8000/interface.py", line 166, in execute
    self.c.close_portal(self._portal_name)
  File "/users/www-data/web2py/gluon/contrib/pg8000/protocol.py", line 1326, in close_portal
    reader.handle_messages()
  File "/users/www-data/web2py/gluon/contrib/pg8000/protocol.py", line 937, in handle_messages
    raise InternalError("Unexpected response msg %r" % (msg))
InternalError: Unexpected response msg <gluon.contrib.pg8000.protocol.BindComplete object at 0xaf241a2c>

[Massimo Di Pierro]

which postgres version?

[Simon Ashley]

9.1 (fresh install yesterday)

[Simon Ashley]

I hate this.
Its fixed (stable) for the time being, but cant clearly identify the cause. 

I'm sure that there is a still memory leak and it may appear again.. 

Early in the process, Ubuntu was acting similarly to Windows 7 i.e. consuming memory when importing the csv file and eventually failing. 

Steps taken were:

• installed psycopg2 (still had issues)
  
• took the table definition in the DAL back to the first few fields (imported OK)
  
• Gradually added the fields back in (import OK on each iteration)
  
• when the complete/ original table imported, re imported the entire app data (OK)

The table definition in the DAL has approx 150 fields (and string fields had been left at their default length)

[Simon Ashley]

Final conclusion: memory leak confined to pg8000.

psycopg2 works fine in Windows and Linux.