Repository: dmwm/wmcore
Issue: Not all binds created for DBS3Buffer NewSubscription DAO

**Impact of the bug**
WMAgent

**Describe the bug**
While testing WMAgent `2.0.4` with Oracle backend, I hit this [1] error in WorkQueueManager. In other words, when a subscription dataset doesn't define a lifetime, we do not create a key/value pair to the DAO binds dictionary.

Bug introduced with: https://github.com/dmwm/WMCore/pull/11115

**How to reproduce it**
Create a workflow with output data placement settings but without lifetime definition.

**Expected behavior**
If no dataset lifetime is defined, it should default to the current value table schema (0, null is not allowed).

**Additional context and error message**
[1]
```
2022-06-10 06:51:11,255:140310655690496:INFO:WMBSHelper:Child subscription 212 created for amaltaro_TC_Drop_PhEDEx_Ext_Agent204_Val_220610_054233_1613
2022-06-10 06:51:11,280:140310655690496:ERROR:WMBSHelper:Failed to create subscription. Error: (cx_Oracle.DatabaseError) ORA-01008: not all variables bound
[SQL: INSERT INTO dbsbuffer_dataset_subscription
             (id, dataset_id, site, custodial, auto_approve, move, priority, subscribed, phedex_group, delete_blocks, dataset_lifetime)
             SELECT dbsbuffer_dataset_sub_seq.nextval, :id, :site, :custodial, :auto_approve,
                    :move, :priority, 0, :phedex_group, :delete_blocks, :dataset_lifetime
             FROM DUAL
             WHERE NOT EXISTS
               ( SELECT *
                 FROM dbsbuffer_dataset_subscription
                 WHERE dataset_id = :id
                 AND site = :site
                 AND custodial = :custodial
                 AND auto_approve = :auto_approve
                 AND move = :move
                 AND priority = :priority
                 AND dataset_lifetime = :dataset_lifetime )]
[parameters: [{'id': 10, 'site': 'T1_US_FNAL_MSS', 'custodial': 1, 'auto_approve': 0, 'move': 0, 'priority': 'Normal', 'phedex_group': 'FacOps', 'delete_blocks': None}, {'id': 10, 'site': 'T1_US_FNAL_Disk', 'custodial': 0, 'auto_approve': 1, 'move': 1, 'priority': 'Normal', 'phedex_group': 'AnalysisOps', 'delete_blocks': None}]]
(Background on this error at: http://sqlalche.me/e/4xp6)
Traceback (most recent call last):
  File "/data/srv/wmagent/v2.0.4-b954b0745339a347ea28afd5b5767db4/sw.amaltaro/slc7_amd64_gcc630/external/py3-sqlalchemy/1.3.3-comp/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1223, in _execute_context
    self.dialect.do_executemany(
  File "/data/srv/wmagent/v2.0.4-b954b0745339a347ea28afd5b5767db4/sw.amaltaro/slc7_amd64_gcc630/external/py3-sqlalchemy/1.3.3-comp/lib/python3.8/site-packages/sqlalchemy/dialects/oracle/cx_oracle.py", line 1080, in do_executemany
    cursor.executemany(statement, parameters)
cx_Oracle.DatabaseError: ORA-01008: not all variables bound

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/data/srv/wmagent/v2.0.4-b954b0745339a347ea28afd5b5767db4/sw.amaltaro/slc7_amd64_gcc630/cms/wmagentpy3/2.0.4-b954b0745339a347ea28afd5b5767db4/lib/python3.8/site-packages/WMCore/WorkQueue/WMBSHelper.py", line 413, in createSubscriptionAndAddFiles
    self.createSubscription(self.topLevelTask, self.topLevelFileset)
  File "/data/srv/wmagent/v2.0.4-b954b0745339a347ea28afd5b5767db4/sw.amaltaro/slc7_amd64_gcc630/cms/wmagentpy3/2.0.4-b954b0745339a347ea28afd5b5767db4/lib/python3.8/site-packages/WMCore/WorkQueue/WMBSHelper.py", line 266, in createSubscription
    self._createDatasetSubscriptionsInDBSBuffer()
  File "/data/srv/wmagent/v2.0.4-b954b0745339a347ea28afd5b5767db4/sw.amaltaro/slc7_amd64_gcc630/cms/wmagentpy3/2.0.4-b954b0745339a347ea28afd5b5767db4/lib/python3.8/site-packages/WMCore/WorkQueue/WMBSHelper.py", line 519, in _createDatasetSubscriptionsInDBSBuffer
    dbsDataset.addSubscription(subInfo[dataset])
  File "/data/srv/wmagent/v2.0.4-b954b0745339a347ea28afd5b5767db4/sw.amaltaro/slc7_amd64_gcc630/cms/wmagentpy3/2.0.4-b954b0745339a347ea28afd5b5767db4/lib/python3.8/site-packages/WMComponent/DBS3Buffer/DBSBufferDataset.py", line 140, in addSubscription
    action.execute(self['id'], subscriptionInformation,
  File "/data/srv/wmagent/v2.0.4-b954b0745339a347ea28afd5b5767db4/sw.amaltaro/slc7_amd64_gcc630/cms/wmagentpy3/2.0.4-b954b0745339a347ea28afd5b5767db4/lib/python3.8/site-packages/WMComponent/DBS3Buffer/MySQL/NewSubscription.py", line 80, in execute
    self.dbi.processData(self.sql, binds=binds, conn=conn, transaction=transaction)
  File "/data/srv/wmagent/v2.0.4-b954b0745339a347ea28afd5b5767db4/sw.amaltaro/slc7_amd64_gcc630/cms/wmagentpy3/2.0.4-b954b0745339a347ea28afd5b5767db4/lib/python3.8/site-packages/WMCore/Database/DBCore.py", line 159, in processData
    result.extend(self.executemanybinds(sqlstmt[0], subBinds,
  File "/data/srv/wmagent/v2.0.4-b954b0745339a347ea28afd5b5767db4/sw.amaltaro/slc7_amd64_gcc630/cms/wmagentpy3/2.0.4-b954b0745339a347ea28afd5b5767db4/lib/python3.8/site-packages/WMCore/Database/DBCore.py", line 113, in executemanybinds
    result = connection.execute(s, b)
  File "/data/srv/wmagent/v2.0.4-b954b0745339a347ea28afd5b5767db4/sw.amaltaro/slc7_amd64_gcc630/external/py3-sqlalchemy/1.3.3-comp/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 982, in execute
    return self._execute_text(object_, multiparams, params)
  File "/data/srv/wmagent/v2.0.4-b954b0745339a347ea28afd5b5767db4/sw.amaltaro/slc7_amd64_gcc630/external/py3-sqlalchemy/1.3.3-comp/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1149, in _execute_text
    ret = self._execute_context(
  File "/data/srv/wmagent/v2.0.4-b954b0745339a347ea28afd5b5767db4/sw.amaltaro/slc7_amd64_gcc630/external/py3-sqlalchemy/1.3.3-comp/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1247, in _execute_context
    self._handle_dbapi_exception(
  File "/data/srv/wmagent/v2.0.4-b954b0745339a347ea28afd5b5767db4/sw.amaltaro/slc7_amd64_gcc630/external/py3-sqlalchemy/1.3.3-comp/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1466, in _handle_dbapi_exception
    util.raise_from_cause(sqlalchemy_exception, exc_info)
  File "/data/srv/wmagent/v2.0.4-b954b0745339a347ea28afd5b5767db4/sw.amaltaro/slc7_amd64_gcc630/external/py3-sqlalchemy/1.3.3-comp/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 383, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/data/srv/wmagent/v2.0.4-b954b0745339a347ea28afd5b5767db4/sw.amaltaro/slc7_amd64_gcc630/external/py3-sqlalchemy/1.3.3-comp/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 128, in reraise
    raise value.with_traceback(tb)
  File "/data/srv/wmagent/v2.0.4-b954b0745339a347ea28afd5b5767db4/sw.amaltaro/slc7_amd64_gcc630/external/py3-sqlalchemy/1.3.3-comp/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1223, in _execute_context
    self.dialect.do_executemany(
  File "/data/srv/wmagent/v2.0.4-b954b0745339a347ea28afd5b5767db4/sw.amaltaro/slc7_amd64_gcc630/external/py3-sqlalchemy/1.3.3-comp/lib/python3.8/site-packages/sqlalchemy/dialects/oracle/cx_oracle.py", line 1080, in do_executemany
    cursor.executemany(statement, parameters)
```

Changed files:
src/python/WMComponent/DBS3Buffer/MySQL/NewSubscription.py

diff --git a/src/python/WMComponent/DBS3Buffer/MySQL/NewSubscription.py b/src/python/WMComponent/DBS3Buffer/MySQL/NewSubscription.py
index 1529e1aa2..6a8212d39 100644
--- a/src/python/WMComponent/DBS3Buffer/MySQL/NewSubscription.py
+++ b/src/python/WMComponent/DBS3Buffer/MySQL/NewSubscription.py
@@ -39,6 +39,8 @@ class NewSubscription(DBFormatter):
 
         # DeleteFromSource is not supported for move subscriptions
         delete_blocks = None
+        # if None, force it to 0 as per the table schema
+        dataLifetime = subscriptionInfo.get('DatasetLifetime', 0) or 0
         if custodialFlag:
             sites = subscriptionInfo['CustodialSites']
             phedex_group = subscriptionInfo['CustodialGroup']
@@ -59,9 +61,8 @@ class NewSubscription(DBFormatter):
                     'move': isMove,
                     'priority': subscriptionInfo['Priority'],
                     'phedex_group': phedex_group,
-                    'delete_blocks': delete_blocks}
-            if subscriptionInfo['DatasetLifetime'] is not None:
-                bind.update(dict(dataset_lifetime=subscriptionInfo['DatasetLifetime']))
+                    'delete_blocks': delete_blocks,
+                    'dataset_lifetime': dataLifetime}
             binds.append(bind)
         return binds