def reportOnQueues(self):
if self.reportCollectionRef:
priorities = ['H', 'M', 'L']
qDict = {'qCheckTime': datetime.now()}
for priority in priorities:
qArray = []
if priority == 'H':
theQ = self.sqlBHandler.sqlQname_H
elif priority == 'M':
theQ = self.sqlBHandler.sqlQname_M
elif priority == 'L':
theQ = self.sqlBHandler.sqlQname_L
qlen = self.getQLens(priority)
qlenKey = f'{priority}__len'
qcontentKey = f'{priority}_content'
qDict[qlenKey] = qlen
qcontents = self.sqlBHandler.redis_client.lrange(theQ, -10, -1)
for element in qcontents:
elementParsed = json.loads(element,
cls=mu.RoundTripDecoder)
qArray.append(elementParsed)
qDict[qcontentKey] = qArray
docUid = mu.makeAscendingUid()
self.reportCollectionRef.document(docUid).set(qDict)
def persistErrorDetailInFB(self, exception, attempt, willBeRetried=True):
lst = dir(exception)
dict_ = {
'attempt': attempt,
'createdAt': datetime.now(),
'willBeRetried': willBeRetried,
'_type': str(type(exception))
}
if 'orig' in lst:
try:
dict_['orig_args'] = exception.orig.args[0]
except:
dict_['orig_args_M'] = 'Unable to get this'
if 'args' in lst:
dict_['args'] = str(exception.args)
if 'code' in lst:
dict_['code'] = exception.code
if 'statement' in lst:
dict_['statement'] = exception.statement
if 'params' in lst and exception.params:
dict_['params'] = list(exception.params)
if 'connection_invalidated' in lst:
dict_['connection_invalidated'] = exception.connection_invalidated
if 'detail' in lst:
dict_['detail'] = exception.detail
if self.fb_db:
docuid = mu.makeAscendingUid()
destDoc = self.fb_db.collection('logging/sqlQ/errors').document(
docuid)
print(
f'Error record will be written to Firebase as docUid={docuid}: {dict_}'
)
destDoc.set(dict_)
def sendFlare(self, messageData='awaken', flareDeadspaceSeconds=10):
"""
Sends a "flare" (a message to the pubsub topic path = self.topic_path) which will spark the SQL worker to jump into action.
A flare will only be sent if there's been no flare sending in the last (flareDeadspaceSeconds) seconds (defaults to 10).
"""
flareSendable = False
rightNow = datetime.now(timezone.utc)
data = messageData.encode("utf-8")
if self.redis_client:
# Get the last flare-send time
lastFlare = self.redis.get_project_human_val(
'SQLHandlerFlareSendTime')
if not lastFlare:
flareSendable = True
# If the seconds-elapsed since this is longer than flareDeadspaceSeconds, send another one. else: eat it.
elif mu.dateDiff('second', lastFlare,
rightNow) >= flareDeadspaceSeconds:
flareSendable = True
if self.pubsub and flareSendable:
self.redis.set_project_human_val('SQLHandlerFlareSendTime',
'datetime', rightNow)
print(f'sending flare!')
future = self.pubsub.publish(self.topic_path, data)
future.result()
def goToWork(self, forHowLong=60, inactivityBuffer=10):
print(f'XXX goToWork. ForHowLong={forHowLong}')
startTs = datetime.now(timezone.utc)
i = 0
howLong = 0
queuesAreEmpty = False
while howLong <= forHowLong - inactivityBuffer and not queuesAreEmpty:
i += 1
self.cypherQueues.getQLens()
if self.cypherQueues.totalInWorkingQueue >= 10:
self.lookForExpiredWorkingBlocks()
ctb = self.popBlockFromWaitingQueues()
if ctb:
stm = ctb.statements
print(f'XXX got CTB from Q statements = {stm}')
print(ctb.transactionUid)
self.executeBlock(ctb)
else:
queuesAreEmpty = True
self.lookForExpiredWorkingBlocks()
howLong = um.dateDiff('sec', startTs, datetime.now(timezone.utc))
print(f'Running for how long: {howLong}')
if howLong >= forHowLong - inactivityBuffer and self.cypherQueues.totalInWaitingQueues > 0:
# numFlares = self.cypherQueues.totalInWaitingQueues / 10
for k in range(3):
print(f'sending flare (max 3) {k}')
self.pubsub.publish_message('awaken')
time.sleep(0.5)
def goToWork(self, forHowLong=60, inactivityBuffer=10, batchSize=50):
print(f'XXX goingToWork. ForHowLong={forHowLong} s')
priorities = ['H', 'M', 'L']
startTs = datetime.now()
i = 0
howLong = 0
self.reportOnQueues()
for priority in priorities:
queuesAreEmpty = False
while howLong <= forHowLong - inactivityBuffer and not queuesAreEmpty:
i += 1
k = 0
batch = []
while not queuesAreEmpty and k < batchSize:
sqlB, queuesAreEmpty = self.popNextBlock(priority=priority)
if sqlB:
batch.append(sqlB)
k += 1
sortedBatches, transactionList = self.sortBatch(batch)
for sb in sortedBatches:
sqb = MonkeeSQLblock(query=sb[0],
insertList=sb[1],
numRetries=sb[2],
maxRetries=sb[3],
soloExecution=sb[4],
lastExecAttempt=sb[5])
self.executeBlock(sqb, priority=priority)
for transaction_i in transactionList:
# transaction_i is a sqb, probably with more than one transactionSqb entry
sqb = MonkeeSQLblock(
query=transaction_i['query'],
insertList=transaction_i['insertList'],
numRetries=transaction_i['numRetries'],
maxRetries=transaction_i['maxRetries'],
soloExecution=transaction_i['soloExecution'],
lastExecAttempt=transaction_i['lastExecAttempt'],
transactionSqb=transaction_i['transactionSqb'])
self.executeBlock(sqb, priority=priority)
howLong = mu.dateDiff('sec', startTs, datetime.now())
#print(f'sqlw Running for how long: {howLong}')
qlen = self.getQLens(priority=priority)
if qlen == 0:
queuesAreEmpty = True
else:
queuesAreEmpty = False
if howLong >= forHowLong - inactivityBuffer and qlen > 0:
numFlares = 3
for k in range(numFlares):
self.sqlBHandler.sendFlare()
def sortBatch(self, batch):
batchDict = {}
batchList = []
transactionList = []
for line in batch:
isTransaction = mu.getval(line, "isTransaction", 0)
# The reason that non-transactions are broken up out of their sqbs is so that similar queries can be batched,
# meaning that all sqbs in this batch where the query part is the same, but only the parameters differ are bundled together and executed as one.
if isTransaction == 0:
query = mu.getval(line, "query")
soloExecution = mu.getval(line, "soloExecution", 0)
numRetries = mu.getval(line, "numRetries", 0)
maxRetries = mu.getval(line, "maxRetries", 0)
lastExecAttempt = mu.getval(line, "lastExecAttempt")
if query:
if soloExecution == 0:
if query not in batchDict:
batchDict[query] = []
batchDict[query].append(line["insertList"])
elif soloExecution == 1: # soloExecution = flagging this element to be executed on its own, i.e. not as part of a batch
batchList.append([
query, line["insertList"], numRetries, maxRetries,
soloExecution, lastExecAttempt
])
elif isTransaction == 1:
# if this is a transaction, there is no batching. This implies that the sqb (containing the transaction) can be passed through directly.
transactionList.append(line)
for q in batchDict:
batchList.append([q, batchDict[q], 0, 30, 0, lastExecAttempt])
return batchList, transactionList
def popNextBlock(self, priority):
if priority == 'H':
theQ = self.sqlBHandler.sqlQname_H
elif priority == 'M':
theQ = self.sqlBHandler.sqlQname_M
elif priority == 'L':
theQ = self.sqlBHandler.sqlQname_L
elif priority == 'ERR':
theQ = self.sqlBHandler.sqlQname_ERR
popped = self.sqlBHandler.redis_client.blpop(theQ, 1)
if not popped:
print(
f"SQL_Q {priority} is EMPTY_________________________________________"
)
else:
dataFromRedis = json.loads(popped[1], cls=mu.RoundTripDecoder)
numRetries = mu.getval(dataFromRedis, "numRetries", 0)
lastExecAttempt = mu.getval(dataFromRedis, "lastExecAttempt")
remainInErrorQUntil = mu.getval(dataFromRedis,
"remainInErrorQUntil",
datetime.now())
if numRetries == 0:
return dataFromRedis, False
# elif lastExecAttempt and datetime.now() >= lastExecAttempt + timedelta(seconds=1.5 ** numRetries):
elif priority in [
'H', 'M', 'L'
] and lastExecAttempt and datetime.now(
) >= lastExecAttempt + timedelta(seconds=2 * numRetries):
return dataFromRedis, False
elif priority in ['ERR'] and remainInErrorQUntil and datetime.now(
) >= remainInErrorQUntil:
return dataFromRedis, False
else:
sqlB = MonkeeSQLblock()
sqlB.makeFromSerial(serial_=dataFromRedis)
self.sqlBHandler.toQ(sqlB, priority=priority)
return None, True
def _statementsAsTransaction(self, tx, statements):
results = []
duration = []
statementList = []
for statement in statements:
# try:
startTs = datetime.now(timezone.utc)
if "batch" in statement:
cyph = statement["cypher"][0:100]
print(f'batch-mode statement[cypher] to be run: {cyph}')
res = tx.run(statement["cypher"],
statement["parameters"],
batch=statement["batch"])
else:
cyph = statement["cypher"][0:100]
print(f'statement[cypher] to be run: {cyph}')
res = tx.run(statement["cypher"], statement["parameters"])
results.append(res)
endTs = datetime.now(timezone.utc)
duration.append(um.dateDiff('sec', startTs, endTs))
statementList.append({
"cypher": statement["cypher"],
"parameters": statement["parameters"],
"duration": um.dateDiff('sec', startTs, endTs),
"status": "OK",
"error": None
})
# except Exception as e:
# endTs = datetime.now(timezone.utc)
# duration.append(um.dateDiff('sec', startTs, endTs))
# logging.error(repr(e))
# statementList.append({"cypher": statement["cypher"], "parameters": statement["parameters"], "duration": um.dateDiff('sec', startTs, endTs),
# "status": "ERROR", "error": repr(e)})
return results, statementList
def lookForExpiredWorkingBlocks(self, expiryInSeconds=60):
wb = self.copyBlockFromWorkingQueue()
if wb:
matchingSerial = json.dumps(wb.instanceToSerial(),
cls=um.RoundTripEncoder)
if wb.statements:
timeDiff = um.dateDiff('s', wb.lastUpdatedAt, datetime.now())
print(f' Age of workingQ item ={timeDiff}')
if timeDiff >= expiryInSeconds:
stmt = wb.statements
print(
f'Picking up item from workingQ. Statements = {stmt}')
rem = self.removeBlockFromWorkingQueue(matchingSerial)
if rem > 0:
wb.registerChangeInSql('outOfWorkingQ')
wb.registerChangeInSql('RecycledToWaiting')
wb.lastUpdatedAt = datetime.now()
wb.numRetries += 1
self.cypherQueues.pushCtbToWaitingQ(ctBlock=wb,
jumpTheQ=True)
def makeFromSerial(self, serial_):
self.isTransaction = mu.getval(serial_, "isTransaction", 0)
if self.isTransaction == 0:
self.query = mu.getval(serial_, "query")
self.insertList = mu.getval(serial_, "insertList")
self.queryTypeId = mu.getval(serial_, "queryTypeId")
self.numRetries = mu.getval(serial_, "numRetries")
self.maxRetries = mu.getval(serial_, "maxRetries")
self.soloExecution = mu.getval(serial_, "soloExecution")
self.lastExecAttempt = mu.getval(serial_, "lastExecAttempt")
self.priority = mu.getval(serial_, "priority")
self.remainInErrorQUntil = mu.getval(serial_,
"remainInErrorQUntil")
self.serial_ = self.instanceToSerial()
elif self.isTransaction == 1:
self.statements = mu.getval(serial_, "statements", [])
self.transactionSqb = mu.getval(serial_, "transactionSqb", [])
self.numRetries = mu.getval(serial_, "numRetries")
self.maxRetries = mu.getval(serial_, "maxRetries")
self.lastExecAttempt = mu.getval(serial_, "lastExecAttempt")
self.priority = mu.getval(serial_, "priority")
self.remainInErrorQUntil = mu.getval(serial_,
"remainInErrorQUntil")
if len(self.statements) > 0 and len(self.transactionSqb) == 0:
for statement in self.statements:
sqb = MonkeeSQLblock()
sqb.query = mu.getval(statement, "query")
sqb.insertList = mu.getval(statement, "insertList")
sqb.queryTypeId = mu.getval(statement, "queryTypeId")
sqb.numRetries = mu.getval(statement, "numRetries")
sqb.maxRetries = mu.getval(statement, "maxRetries")
sqb.soloExecution = mu.getval(statement, "soloExecution")
sqb.lastExecAttempt = mu.getval(statement,
"lastExecAttempt")
sqb.serial_ = sqb.instanceToSerial()
self.transactionSqb.append(sqb)
self.instanceToSerial()
def makeFromSerial(self, serial_):
self.isTransaction = mu.getval(serial_, "isTransaction", 0)
if self.isTransaction == 0:
self.query = mu.getval(serial_, "query")
self.insertList = mu.getval(serial_, "insertList")
self.queryTypeId = mu.getval(serial_, "queryTypeId")
self.numRetries = mu.getval(serial_, "numRetries")
self.maxRetries = mu.getval(serial_, "maxRetries")
self.soloExecution = mu.getval(serial_, "soloExecution")
self.lastExecAttempt = mu.getval(serial_, "lastExecAttempt")
self.serial_ = self.instanceToSerial()
elif self.isTransaction == 1:
self.statements = self.query = mu.getval(serial_, "statements", [])
for statement in self.statements:
sqb = MonkeeSQLblock()
sqb.query = mu.getval(statement, "query")
sqb.insertList = mu.getval(statement, "insertList")
sqb.queryTypeId = mu.getval(statement, "queryTypeId")
sqb.numRetries = mu.getval(statement, "numRetries")
sqb.maxRetries = mu.getval(statement, "maxRetries")
sqb.soloExecution = mu.getval(statement, "soloExecution")
sqb.lastExecAttempt = mu.getval(statement, "lastExecAttempt")
sqb.serial_ = sqb.instanceToSerial()
self.transactionSqb.append(sqb)
def makeFromSerial(self, serial):
if not isinstance(serial, dict):
dict_ = json.loads(serial, cls=um.RoundTripDecoder)
else:
dict_ = serial
self.priority = um.getval(dict_, "priority")
self.numRetries = um.getval(dict_, "numRetries")
self.createdAt = um.getval(dict_, "createdAt")
self.lastUpdatedAt = um.getval(dict_, "lastUpdatedAt")
self.transactionUid = um.getval(dict_, "uid")
self.origin = um.getval(dict_, "origin")
self.statements = um.getval(dict_, "statements")
self.runTime = um.getval(dict_, "runTime")
self.status = um.getval(dict_, "status")
self.errors = um.getval(dict_, "errors")
self.durations = um.getval(dict_, "durations")
self.callingCF = um.getval(dict_, "callingCF")
self.timeInQ = um.getval(dict_, "timeInQ")
self.docUid = um.getval(dict_, "docUid")
self.appUid = um.getval(dict_, "appUid")
self.setJson()
def executeBlock(self, ctBlock: CypherTransactionBlock):
"""
Executes all statments in the ctb as one transaction.
Returns success boolean
"""
matchingSerial = json.dumps(ctBlock.instanceToSerial(),
cls=um.RoundTripEncoder)
if ctBlock.statements:
try:
ctBlock.registerChangeInSql('executeStart')
with self.neoDriver.session() as session:
startTs = datetime.now(timezone.utc)
print('to NEO: {}'.format(ctBlock.statements))
_, durations = session.write_transaction(
self._statementsAsTransaction, ctBlock.statements)
print('back from NEO')
endTs = datetime.now(timezone.utc)
elapsedSec = um.dateDiff('sec', startTs, endTs)
ctBlock.runTime = elapsedSec
ctBlock.durations = durations
ctBlock.status = 'done'
ctBlock.registerChangeInSql('executeEnd')
rem = self.removeBlockFromWorkingQueue(matchingSerial)
if rem > 0:
ctBlock.registerChangeInSql('outOfWorkingQ')
self.cypherQueues.pushCtbToCompletedQ(ctBlock)
return True
except CypherSyntaxError as e:
print('CypherSyntaxError')
logging.error(repr(e))
ctBlock.numRetries += 1
ctBlock.status = 'CypherSyntaxError'
ctBlock.errors = repr(e)
rem = self.removeBlockFromWorkingQueue(matchingSerial)
if rem > 0:
ctBlock.registerChangeInSql('outOfWorkingQ')
self.cypherQueues.pushCtbToCompletedQ(ctBlock)
ctBlock.registerChangeInSql('error', repr(e))
return False
except ServiceUnavailable as e:
print('ServiceUnavailable')
logging.error(repr(e))
ctBlock.numRetries += 1
ctBlock.status = 'ServiceUnavailable'
ctBlock.errors = repr(e)
rem = self.removeBlockFromWorkingQueue(matchingSerial)
if rem > 0:
ctBlock.registerChangeInSql('outOfWorkingQ')
self.cypherQueues.pushCtbToWaitingQ(ctBlock)
ctBlock.registerChangeInSql('error', repr(e))
return False
except ConstraintError as e:
print('ConstraintError')
logging.error(repr(e))
ctBlock.numRetries += 1
ctBlock.status = 'ConstraintError'
ctBlock.errors = repr(e)
rem = self.removeBlockFromWorkingQueue(matchingSerial)
if rem > 0:
ctBlock.registerChangeInSql('outOfWorkingQ')
ctBlock.setJson()
if ctBlock.numRetries <= 50:
print(f'>>>>ctBlock.numRetries={ctBlock.numRetries}')
self.cypherQueues.pushCtbToWaitingQ(ctBlock)
ctBlock.registerChangeInSql('error', repr(e))
else:
self.cypherQueues.pushCtbToCompletedQ(ctBlock)
ctBlock.registerChangeInSql('givenUp', 'GIVEN UP')
return False
except ClientError as e:
print('ClientError')
logging.error(repr(e))
ctBlock.numRetries += 1
ctBlock.status = 'ClientError'
ctBlock.errors = repr(e)
rem = self.removeBlockFromWorkingQueue(matchingSerial)
if rem > 0:
ctBlock.registerChangeInSql('outOfWorkingQ')
ctBlock.setJson()
self.cypherQueues.pushCtbToCompletedQ(ctBlock)
ctBlock.registerChangeInSql('error', repr(e))
return False
except Exception as e:
logging.error(repr(e))
rem = self.removeBlockFromWorkingQueue(matchingSerial)
if rem > 0:
ctBlock.registerChangeInSql('outOfWorkingQ')
self.cypherQueues.pushCtbToWaitingQ(ctBlock)
return False