export_to_csv then import_from_csv gives field limit exception

[Thadeus Burgess]

>>> db.export_to_csv_file(open('/home/thadeusb/Desktop/data.csv', 'wb'))



#then later

>>> db.import_from_csv_file(open('/home/thadeusb/Desktop/data.csv', 'r'))

Traceback (most recent call last):


  File "/home/thadeusb/Applications/web2py-devel/gluon/restricted.py", line 184, in restricted


    exec ccode in environment
  File "/home/thadeusb/Applications/web2py-devel/applications/bootygrab/controllers/default.py", line 617, in <module>


  File "/home/thadeusb/Applications/web2py-devel/gluon/globals.py", line 102, in <lambda>


    self._caller = lambda f: f()


  File "/home/thadeusb/Applications/web2py-devel/applications/bootygrab/controllers/default.py", line 566, in dbio


    db.import_from_csv_file(open('/home/thadeusb/Desktop/bootygrab.csv', 'r'))


  File "/home/thadeusb/Applications/web2py-devel/gluon/sql.py", line 1193, in import_from_csv_file


    self[tablename].import_from_csv_file(ifile, id_map, null, unique)


  File "/home/thadeusb/Applications/web2py-devel/gluon/sql.py", line 1705, in import_from_csv_file


    for line in reader:
Error: field larger than field limit (131072)

-Thadeus

[Thadeus Burgess]

Ah, see this would be the perfect place for that variable dump I was talking about :)

That is still on my todo list... haha.

-Thadeus

[mdipierro]

1) You open with 'wb' and you read with 'r' instead of 'rb'.
I do not think this is the problem but it may be

2) I think the problem may be that you had corrupted data in the db in
the first place (like you stored something in a text files, then
changed type to string and the large data is still there. You can dump
it but not reimport it. Try open the dumped file with excel.

Massimo

On Oct 22, 9:16 pm, Thadeus Burgess <thade...@thadeusb.com> wrote:
> >>> db.export_to_csv_file(open('/home/thadeusb/Desktop/data.csv', 'wb'))
>
> #then later
>
> >>> db.import_from_csv_file(open('/home/thadeusb/Desktop/data.csv', 'r'))
>
> Traceback (most recent call last):
>
>   File "/home/thadeusb/Applications/web2py-devel/gluon/restricted.py",
> line 184, in restricted
>
>     exec ccode in environment
>   File "/home/thadeusb/Applications/web2py-devel/applications/bootygrab/controllers/default.py"

> <http://127.0.0.1:8000/admin/default/edit/bootygrab/controllers/defaul...>,

> line 617, in <module>
>
>   File "/home/thadeusb/Applications/web2py-devel/gluon/globals.py",
> line 102, in <lambda>
>
>     self._caller = lambda f: f()
>
>   File "/home/thadeusb/Applications/web2py-devel/applications/bootygrab/controllers/default.py"

> <http://127.0.0.1:8000/admin/default/edit/bootygrab/controllers/defaul...>,

[Thadeus Burgess]

I tried changing 'wb', to 'w'

Unfortunately, open office freezes when trying to open it....

here is my sql.log http://pastebin.com/m4ea163df

I did not alter any columns that would have truncated data.

There is however, the text field in table booty. tank_data.

This is a massive string of JSON.. massive. like... thats what takes up 40mb of my 62mb database.

If anything, this field is causing the error.

Is there any way to not export this column (I don't need the JSON, it will get replaced on the next update). Is it save to remove the Field() from the db.define_table, delete the database files (to start from fresh) and then add the Field declaration again right before import?

-Thadeus

[Thadeus Burgess]

That was it. It was the tank_data field.

Here is an example of what gets stored in the field. It seems like if I am able to insert this much data, import_from_csv should be able to import it as well?

http://gaiaonline.com/chat/gsi/gateway.php?v=json&m=[[6500,[1]],[6510,[%222137385%22,0]],[6511,[%222137385%22,1]],[6512,[%222137385%22,1]],[107,[%22null%22]]]&X=1256164219

-Thadeus

[mdipierro]

No because the web2py <1.68 did not enforce size, 1.68 does.

Massimo

On Oct 22, 10:11 pm, Thadeus Burgess <thade...@thadeusb.com> wrote:
> That was it. It was the tank_data field.
>
> Here is an example of what gets stored in the field. It seems like if I am
> able to insert this much data, import_from_csv should be able to import it
> as well?
>
> http://gaiaonline.com/chat/gsi/gateway.php?v=json&m=[[6500,[1]],[6510,[%222137385%22,0]],[6511,[%222137385%22,1]],[6512,[%222137385%22,1]],[107,[%22null%22]]]&X=1256164219
>
> -Thadeus
>

> On Thu, Oct 22, 2009 at 10:07 PM, Thadeus Burgess <thade...@thadeusb.com>wrote:
>
> > I tried changing 'wb', to 'w'
>
> > Unfortunately, open office freezes when trying to open it....
>

> > here is my sql.loghttp://pastebin.com/m4ea163df

>
> > I did not alter any columns that would have truncated data.
>
> > There is however, the text field in table booty. tank_data.
>
> > This is a massive string of JSON.. massive. like... thats what takes up
> > 40mb of my 62mb database.
>
> > If anything, this field is causing the error.
>
> > Is there any way to not export this column (I don't need the JSON, it will
> > get replaced on the next update). Is it save to remove the Field() from the
> > db.define_table, delete the database files (to start from fresh) and then
> > add the Field declaration again right before import?
>
> > -Thadeus
>

[Thadeus Burgess]

Wow I am just having lots of problems tonight, Ok so creating my table on a fresh mysql database causes this error. 



This key error happens on the following fields

    Field('booty_url', length=300), Field('tank_data', 'text'))

Traceback (most recent call last):
  File "/home/thadeusb/Applications/web2py-devel/gluon/restricted.py", line 184, in restricted


    exec ccode in environment

  File "/home/thadeusb/Applications/web2py-devel/applications/bootygrab/models/db.py", line 76, in <module>


    Field('tank_data', 'text'))


  File "/home/thadeusb/Applications/web2py-devel/gluon/sql.py", line 1130, in define_table


    query = t._create(migrate=migrate)


  File "/home/thadeusb/Applications/web2py-devel/gluon/sql.py", line 1470, in _create


    self._db._execute(query)


  File "/home/thadeusb/Applications/web2py-devel/gluon/sql.py", line 805, in <lambda>


    self._execute = lambda *a, **b: self._cursor.execute(*a, **b)


  File "/usr/lib/python2.6/site-packages/MySQLdb/cursors.py", line 173, in execute


    self.errorhandler(self, exc, value)


  File "/usr/lib/python2.6/site-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler


    raise errorclass, errorvalue
OperationalError: (1071, 'Specified key was too long; max key length is 767 bytes')

-Thadeus

[Thadeus Burgess]

I might just be doing something wrong, but I think there is something with the MySQL DAL...

Nothing with this define_table will pass... listed below are the errors.

db.define_table("test_table",
    Field('id_user', auth.user),
    Field('name'),
    Field('email', length=300),
    Field('about', 'text'),
    Field('count', 'integer'),
)

I either get the below traceback, or the one in my previous post. I'm confused O.O Considering that the Auth.define_tables goes through. I am clearing everything under databases folder each time to test, as well as manually dropping the tables.

Traceback (most recent call last):


  File "/home/thadeusb/Applications/web2py-devel/gluon/restricted.py", line 184, in restricted


    exec ccode in environment

  File "/home/thadeusb/Applications/web2py-devel/applications/welcome/models/db.py", line 54, in <module>


    Field('count', 'integer'),


  

File "/home/thadeusb/Applications/web2py-devel/gluon/sql.py", line 1130, in define_table


    query = t._create(migrate=migrate

)


  File "/home/thadeusb/Applications/web2py-devel/gluon/sql.py", line 1396, in _create


    elif field.type[:10] == 'reference ':


TypeError: 'NoneType' object is unsubscriptable

-Thadeus

[DenesL]

Hi Thadeus,

On Oct 23, 2:08 am, Thadeus Burgess <thade...@thadeusb.com> wrote:
> I might just be doing something wrong, but I think there is something with
> the MySQL DAL...
>
> Nothing with this define_table will pass... listed below are the errors.
>
> db.define_table("test_table",
> Field('id_user', auth.user),
> Field('name'),
> Field('email', length=300),
> Field('about', 'text'),
> Field('count', 'integer'),
> )

...
Field('id_user', db.auth_user),
...

Denes

[Thadeus Burgess]

Yeah that was just a typo when I wrote it in gmail, I did not copy/paste it from my code. It is db.auth_user in my code.

-Thadeus

[DenesL]

So just to be clear, you are not getting
TypeError: 'NoneType' object is unsubscriptable ?

I get the same error using your posted code with MSSQL (and it will
blow up with any other DB), so it is not something with the MySQL DAL.

Denes.

On Oct 23, 11:42 am, Thadeus Burgess <thade...@thadeusb.com> wrote:
> Yeah that was just a typo when I wrote it in gmail, I did not copy/paste it
> from my code. It is db.auth_user in my code.
>
> -Thadeus
>

[Thadeus Burgess]

No, I am getting

OperationalError: (1071, 'Specified key was too long; max key length is 767 bytes')

Yes, I do get the unscriptable error if I use auth.user... I think I caught that after I made the post and got tired and went to sleep :)

But I still get the OperationalError with this: It works if I change my db to sqlite, but not on mysql.

db = DAL('mysql://username:alphanumer...@mysql.thadeusb.com')

 db.define_table("test_table",
     Field('id_user', db.auth_user),

     Field('name'),
     Field('email', length=300),
     Field('about', 'text'),
     Field('count', 'integer'),
  )

-Thadeus

[Thadeus Burgess]

There is definitely something broken with the mysql DAL.

On the app I am working on in my day job, I pointed it over to our mysql servers to an already created database, and got the OperationlError key was too long;


-Thadeus

[DenesL]

You version of MySQL might have a bug:
http://bugs.mysql.com/bug.php?id=4541

If there is way around it then the DAL might be modified to handle it.

There are additional hits doing a Google search with that error.

Denes.

[Thadeus Burgess]

Is there a way in the DAL to specify ENGINE=MyISAM; ?

It might have to do with using InnoDB, not sure.

Let me try this on postgres :)

-Thadeus