def verify_table_encoding(db): with db.execution_context(): cmd_sql = ''' SELECT table_name FROM information_schema.tables WHERE table_collation != "utf8mb4_unicode_ci" AND table_schema = "{}"; '''.format(cfg_get('db_name')) change_tables = db.execute_sql(cmd_sql) if change_tables.rowcount > 0: log.info('Changing collation and charset on database.') cmd_sql = "ALTER DATABASE {} CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci".format( cfg_get('db_name')) db.execute_sql(cmd_sql) log.info('Changing collation and charset on {} tables.'.format( change_tables.rowcount)) db.execute_sql('SET FOREIGN_KEY_CHECKS=0;') for table in change_tables: log.debug('Changing collation and charset on table {}.'.format( table[0])) cmd_sql = '''ALTER TABLE {} CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;'''.format(str( table[0])) db.execute_sql(cmd_sql) db.execute_sql('SET FOREIGN_KEY_CHECKS=1;')
def init_database(app): log.info('Connecting to MySQL database on %s:%i...', cfg_get('db_host'), cfg_get('db_port')) db = MyRetryDB( cfg_get('db_name'), user=cfg_get('db_user'), password=cfg_get('db_pass'), host=cfg_get('db_host'), port=cfg_get('db_port'), max_connections=cfg_get('db_max_connections'), stale_timeout=300, charset='utf8mb4') app.config['DATABASE'] = db flaskDb.init_app(app) db.connect() if not Account.table_exists(): create_tables(db) InsertQuery(Version, {Version.key: 'schema_version', Version.val: db_schema_version}).execute() old_schema_version = db_schema_version elif not Version.table_exists(): old_schema_version = 1 else: old_schema_version = Version.get(Version.key == 'schema_version').val if old_schema_version < db_schema_version: migrate_database(db, old_schema_version) # Last, fix database encoding verify_table_encoding(db) return db
def update_account(data, db): with db.atomic(): try: acc, created = Account.get_or_create(username=data['username']) acc_previous = copy.deepcopy(acc) metadata = {} for key, value in data.items(): if not key.startswith('_'): setattr(acc, key, value) else: metadata[key] = value if acc.system_id and acc.assigned_at is None: acc.assigned_at = datetime.now() #make sure accounts with a system_id have an assigned time acc.last_modified = datetime.now() eval_acc_state_changes(acc_previous, acc, metadata) acc.save() if cfg_get('log_updates'): log.info("Processed update for {}".format(acc.username)) except Exception as e: # If there is a DB table constraint error, dump the data and # don't retry. # # Unrecoverable error strings: unrecoverable = ['constraint', 'has no attribute', 'peewee.IntegerField object at'] has_unrecoverable = filter( lambda x: x in str(e), unrecoverable) if has_unrecoverable: log.warning('%s. Data is:', repr(e)) log.warning(data.items()) else: log.warning('%s... Retrying...', repr(e)) time.sleep(1)
def auto_release(): release_timeout = cfg_get('account_release_timeout') while True: try: pastdate = datetime.now() - timedelta(minutes=release_timeout) accounts = Account.select().where( Account.system_id.is_null(False) & (Account.last_modified <= pastdate)) if len(accounts) > 0: log.info( "Releasing {} accounts that haven't been updated in the last {} minutes." .format(len(accounts), release_timeout)) for acc in accounts: new_account_event( acc, "Auto-releasing from [{}]".format(acc.system_id)) if webhook_queue: webhook_queue.put(('release', create_webhook_data( 'release', acc.system_id, acc, "Auto-releasing from [{}]".format( acc.system_id)))) acc.system_id = None acc.assigned_at = None acc.last_modified = datetime.now() acc.save() except Exception as e: log.error(e) time.sleep(60)
def init_database(app): log.info('Connecting to MySQL database on %s:%i...', cfg_get('db_host'), cfg_get('db_port')) db = MyRetryDB(cfg_get('db_name'), user=cfg_get('db_user'), password=cfg_get('db_pass'), host=cfg_get('db_host'), port=cfg_get('db_port'), max_connections=cfg_get('db_max_connections'), stale_timeout=300, charset='utf8') app.config['DATABASE'] = db flaskDb.init_app(app) create_tables(db) return db
def accounts_update(): if db_updates_queue.qsize() >= cfg_get('max_queue_size'): msg = "DB update queue full ({} items). Ignoring update.".format( db_updates_queue.qsize()) log.warning(msg) return msg, 503 data = json.loads(request.data) if isinstance(data, list): for update in data: db_updates_queue.put(update) else: db_updates_queue.put(data) return 'ok'
def run_server(): app.run(threaded=True, host=cfg_get('host'), port=cfg_get('port'))
# --------------------------------------------------------------------------- log.info("PGPool starting up...") db = init_database(app) # DB Updates db_updates_queue = Queue() t = Thread(target=db_updater, name='db-updater', args=(db_updates_queue, db)) t.daemon = True t.start() if cfg_get('account_release_timeout') > 0: log.info( "Starting auto-release thread releasing accounts every {} minutes.". format(cfg_get('account_release_timeout'))) t = Thread(target=auto_release, name='auto-release') t.daemon = True t.start() else: log.info("Account auto-releasing DISABLED.") # Start thread to print current status and get user input. t = Thread(target=print_status, name='status_printer', args=('logs', db_updates_queue)) t.daemon = True t.start()
</form> """ return page def run_server(): app.run(threaded=True, host=cfg_get('host'), port=cfg_get('port')) # --------------------------------------------------------------------------- log.info("PGPool starting up...") # WH updates queue wh_updates_queue = None if not cfg_get('wh_filter'): log.info('Webhook disabled.') else: log.info('Webhook enabled for events; loading filters from %s', cfg_get('wh_filter')) if not load_filters(cfg_get('wh_filter')): log.warning( "Unable to load webhook filters from {}. Exiting...".format( cfg_get('wh_filter'))) raise SystemExit wh_updates_queue = Queue() set_webhook_queue(wh_updates_queue) # Thread to process webhook updates. for i in range(cfg_get('wh_threads')): log.debug('Starting wh-updater worker thread %d', i) t = Thread(target=wh_updater,