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,
