DB not threaded

[Mila432]

Running more then 500 workers and using a changed version of this without problems, now upgraded to the latest commit and have this:

Exception in thread worker-179:
Traceback (most recent call last):
  File "/usr/lib/python2.7/threading.py", line 801, in __bootstrap_inner
    self.run()
  File "worker.py", line 466, in run
    self.main(service, api_endpoint, access_token, profile_response)
  File "worker.py", line 497, in main
    session.commit()
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/session.py", line 801, in commit
    self.transaction.commit()
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/session.py", line 392, in commit
    self._prepare_impl()
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/session.py", line 372, in _prepare_impl
    self.session.flush()
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/session.py", line 2019, in flush
    self._flush(objects)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/session.py", line 2137, in _flush
    transaction.rollback(_capture_exception=True)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/util/langhelpers.py", line 60, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/session.py", line 2101, in _flush
    flush_context.execute()
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/unitofwork.py", line 373, in execute
    rec.execute(self)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/unitofwork.py", line 532, in execute
    uow
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/persistence.py", line 174, in save_obj
    mapper, table, insert)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/persistence.py", line 800, in _emit_insert_statements
    execute(statement, params)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 914, in execute
    return meth(self, multiparams, params)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/sql/elements.py", line 323, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1010, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1146, in _execute_context
    context)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1341, in _handle_dbapi_exception
    exc_info
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/util/compat.py", line 202, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1139, in _execute_context
    context)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/default.py", line 450, in do_execute
    cursor.execute(statement, parameters)
OperationalError: (sqlite3.OperationalError) database is locked [SQL: u'INSERT INTO sightings (pokemon_id, spawn_id, expire_timestamp, normalized_timestamp, lat, lon) VALUES (?, ?, ?, ?, ?, ?)'] [parameters: (19, u'47b18c1070d', 1469644400.419473, 1469644320, 53.560029272416344, 10.079304464214422)]

[Aiyubi]

there is already #47 which will fix this one.

[modrzew]

Or you can use something different than SQLite. I think it's not safe for multithreaded access, is it?

I'm using MySQL personally and it's working like a charm.

[nathaned]

@modrzew can you add that to the guide? you clearly suggest using SQLite in the Readme

[fu20161234]

Can we also get quick directions on how to switch?

Is all we have to do swap it out and it's instantly thread safe?

[ghost]

I am also having these errors, and I am pretty clueless when it comes to switching over to mysql. Can anyone make a guide an update? Please :)

[andrewbroberg]

So I'm a complete novice at python, only just started fiddling with it because of pokemongo, but I got mysql working. This is what I did. I am running my miner on Ubuntu 16.04 so it may not work for your operating system

• Run 'apt-get install mysql-server'. This will install MySQL on your server. You will be prompted to set a root MySQL password when installting so note this down so you don't forget it.
• Run apt-get install libmysqlclient-dev to install mysql_config which is required. I got an error without having this installed and this fixed it for me
• Run pip install mysql-python. This will install the python mysql connector
• Run mysql -u root. You will then get a prompt for a password. Enter the password you choose when setting up MySQL. The password won't show when typing so just enter it then press enter
• Once logged into MySQL, run create database pokemon;. This will create the database we are going to store all our sightings in
• Close out of MySQL prompt by typing exit
• Change into the directory of your pokeminer code using cd /path/to/directory
• Edit config.py with your preferred editor and change DB_ENGINE = 'mysql+mysqldb://root:PASSWORD@localhost/pokemon' and then save
• Run python -i
• Run import db and db.Base.metadata.create_all(db.get_engine()) . This will create the required database table we need.
• You should now be able to run your worker and it will now save into the MySQL database. If you get errors about lat and lon being too long for the field run the following in MySQL prompt. alter table sightings modify column lat varchar(128); and alter table sightings modify column lon varchar(128);

Hopefully this works for you guys. These steps worked for me.

[fu20161234]

Awesome, thanks. Now to merge all of the sqlite db records over...

[andrewbroberg]

Yeah same. I'm a php developer so I'll probably just write a script in PHP to pull it all from sqlite and into mysql

On 29 Jul 2016 01:12, "fu20161234" notifications@github.com wrote:

Awesome, thanks. Now to merge all of the sqlite db records over...

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/modrzew/pokeminer/issues/52#issuecomment-235925588, or mute the thread https://github.com/notifications/unsubscribe-auth/AHQfrRLr8GLHlvu7s_VmLjsGmKik-Xg8ks5qaMb3gaJpZM4JWeyN .

[fu20161234]

Feel free to share that if you want. Otherwise I'll be doing a bash equivalent.

[suppoo]

Does /report work normally for you after switching to mysql?

[andrewbroberg]

Yeah, seems to. You'll need to wait an hour for it to gather data.

On 29 Jul 2016 10:53, "suppoo" notifications@github.com wrote:

Does /report work normally for you after switching to mysql?

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/modrzew/pokeminer/issues/52#issuecomment-236067210, or mute the thread https://github.com/notifications/unsubscribe-auth/AHQfrdVoyNam5ohaFF1hCkSvxDdMz6huks5qaU8YgaJpZM4JWeyN .

[Mila432]

fixed with lots of try's

[Tanish2409]

i am a complete newbie to this so can anyone plz help me on how i can shift to MySQL on 32bit win7!! i am new to all these things and just doing it cause of pokemon go..so plz help!!