search

weecology / retriever

Quickly download, clean up, and install public datasets into a database management system
http://data-retriever.org
Other
306 stars 134 forks source link

Installs into MySQL using GUI fail sporadically #142

Closed ethanwhite closed 10 years ago

ethanwhite commented 10 years ago

Often reports:

Error: (1146, u"Table 'bbs50.region_codes' doesn't exist")

The particular table reported varies, but it is always a table from a different dataset.

Also reports:

Error: Result length not requested length: Expected=8. Actual=4. Position: 1. Data Length: 5

The installation works properly using the CLI and the GUI problem occurs on both OSX and Linux.

On Linux, if you install via the CLI then the GUI will reinstall properly, but on OSX the GUI install fails even after a CLI install.

Occurs across datasets (tested with DelMoral, Zachman, MCDB, and MammalLH) but varies in frequency (DelMoral most of the time, Zachman half of the time, MCDB ~20%, MammalLH ~10%).

@bendmorris any ideas on this one?

ethanwhite commented 10 years ago

One other clue I forgot. These installations work fine using the Mac .app package built from the current source.

bendmorris commented 10 years ago

The "result length not requested" part is a PyMySQL error, but not a very helpful one. At a low level, it's apparently trying to read 8 bytes from a packet when there are only 4 left. Without more detail I'm not sure what the cause could be.

ethanwhite commented 10 years ago

More clues:

On Linux with the Retriever built from source using the GUI I first checked to see if Del Moral installed using other engines (and it succeeded on all of them, and then tried to install it 10 times into MySQL. Of these 10 attempted installs it installed correctly 5 times, errored 4 times (2 "BBS50.counts doesn't exist", 1 "Result length not requested length", 1 "Broken pipe"), and crashed once.

I then removed pymysql, rebuilt the Retriever, and tried in the installations again. All attempts to install resulted in:

Error: (2006, 'MySQL server has gone away')

or hanging while trying to connect to the database. All other datasets I tried worked fine, except for McGlinn2010 which displays the same behavior as DelMoral2010.

ethanwhite commented 10 years ago

I just updated the issue description and title because this is a more general problem than it first appeared. @bendmorris I'm not really sure where to start on this one. Some sort of race condition with the multi-threading?

bendmorris commented 10 years ago

I'm not able to reproduce this right now, but I have a hunch that the "table_exists" function being called from the GUI is causing trouble by using the same cursor as the download thread. Can you try cherry-picking https://github.com/bendmorris/retriever/commit/8bd4625086d0a69a838c79826d144aad7860eafd and see if the issue is still there?

If this was the issue, I'm surprised that it only happened when using pymysql.

ethanwhite commented 10 years ago

Cherry-picking that commit didn't solve the problem unfortunately.

ethanwhite commented 10 years ago

When Del Moral fails it appears to always fail (3/3 failures reporting the 'Table XXX doesn't exist' error) is failing in the middle of importing the species table. Failing at different lines, but always in the same table.

ethanwhite commented 10 years ago

@bendmorris, you'd identified the source of the issue properly. Thanks for getting me in the ballpark! I'm not sure why your fix didn't work, but I think that #150 is probably better anyway since it makes things consistent across MySQL, Postgres, and SQLite in terms of how we check for existing tables.