Repository: dmwm/wmcore
Issue: Out of range value for column 'events' in wmbs_file_details

While draining submit5, I noticed there were a couple of LQE stuck in Available/Acquired not going through the wmbs preparation. Checking the WorkQueueManager log brings me to this traceback [1].

The table schema is
```
MariaDB [wmagent]> desc wmbs_file_details;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | int(11)      | NO   | PRI | NULL    | auto_increment |
| lfn         | varchar(700) | NO   | UNI | NULL    |                |
| filesize    | bigint(20)   | YES  |     | NULL    |                |
| events      | int(11)      | YES  |     | NULL    |                |
| first_event | bigint(20)   | NO   |     | 0       |                |
| merged      | int(1)       | NO   |     | 0       |                |
+-------------+--------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)
```

which has a clear inconsistency between `events` and `first_event` as well.

And for the record, this was the GQE mask:
```
"Mask":{"LastRun":1,"FirstRun":1,"inclusivemask":true,"runAndLumis":{},"LastEvent":2662796000,"FirstEvent":1,"LastLumi":1000,"FirstLumi":1}
```


[1]
```
2019-07-23 03:12:40,264:140718653933312:INFO:WorkQueue:Got 2 elements matching the constraints
2019-07-23 03:12:40,289:140718653933312:INFO:WorkQueue:Running WMBS preparation for pdmvserv_task_HIN-RunIIpp5Spring18GS-00057__v1_T_190616_090720_438 with ParentQueueId 507b0e3a42504a1edccd1977c2619e4f
...
2019-07-23 03:12:40,454:140718653933312:INFO:WMBSHelper:"pdmvserv_task_HIN-RunIIpp5Spring18GS-00057__v1_T_190616_090720_438" Injecting production 1:1:1 - 1:1000:3509387000 (run:lumi:event) into wmbs
2019-07-23 03:12:40,522:140718653933312:ERROR:WorkQueue:Failed to create subscription for pdmvserv_task_HIN-RunIIpp5Spring18GS-00057__v1_T_190616_090720_438 with block name None
Error: (_mysql_exceptions.DataError) (1264, "Out of range value for column 'events' at row 1")
[SQL: INSERT INTO wmbs_file_details (lfn, filesize, events,
                                            first_event, merged)
             VALUES (%s, %s, %s, %s,
                     %s)]
[parameters: ('MCFakeFile-pdmvserv_task_HIN-RunIIpp5Spring18GS-00057__v1_T_190616_090720_438-HIN-RunIIpp5Spring18GS-00057_0-acb1135a457786d9e90a1a71730dad8e', 0, 3509387000, 1, 0)]
(Background on this error at: http://sqlalche.me/e/9h9h)
Traceback (most recent call last):
  File "/data/srv/wmagent/v1.2.2.patch3/sw/slc7_amd64_gcc630/cms/wmagent/1.2.2.patch3/lib/python2.7/site-packages/WMCore/WorkQueue/WorkQueue.py", line 354, in getWork
    dbsBlock)
  File "/data/srv/wmagent/v1.2.2.patch3/sw/slc7_amd64_gcc630/cms/wmagent/1.2.2.patch3/lib/python2.7/site-packages/WMCore/WorkQueue/WorkQueue.py", line 427, in _wmbsPreparation
    sub, match['NumOfFilesAdded'] = wmbsHelper.createSubscriptionAndAddFiles(block=dbsBlock)
  File "/data/srv/wmagent/v1.2.2.patch3/sw/slc7_amd64_gcc630/cms/wmagent/1.2.2.patch3/lib/python2.7/site-packages/WMCore/WorkQueue/WMBSHelper.py", line 423, in createSubscriptionAndAddFiles
    addedFiles = self.addMCFakeFile()
  File "/data/srv/wmagent/v1.2.2.patch3/sw/slc7_amd64_gcc630/cms/wmagent/1.2.2.patch3/lib/python2.7/site-packages/WMCore/WorkQueue/WMBSHelper.py", line 389, in addMCFakeFile
    isDBS=self.isDBS)
  File "/data/srv/wmagent/v1.2.2.patch3/sw/slc7_amd64_gcc630/cms/wmagent/1.2.2.patch3/lib/python2.7/site-packages/WMCore/WMBS/Fileset.py", line 249, in addFilesToWMBSInBulk
    transaction=self.existingTransaction())
  File "/data/srv/wmagent/v1.2.2.patch3/sw/slc7_amd64_gcc630/cms/wmagent/1.2.2.patch3/lib/python2.7/site-packages/WMCore/WMBS/File.py", line 590, in addFilesToWMBSInBulk
    transaction=transaction)
  File "/data/srv/wmagent/v1.2.2.patch3/sw/slc7_amd64_gcc630/cms/wmagent/1.2.2.patch3/lib/python2.7/site-packages/WMCore/WMBS/MySQL/Files/Add.py", line 41, in execute
    conn=conn, transaction=transaction)
  File "/data/srv/wmagent/v1.2.2.patch3/sw/slc7_amd64_gcc630/cms/wmagent/1.2.2.patch3/lib/python2.7/site-packages/WMCore/Database/DBCore.py", line 173, in processData
    returnCursor=returnCursor)
  File "/data/srv/wmagent/v1.2.2.patch3/sw/slc7_amd64_gcc630/cms/wmagent/1.2.2.patch3/lib/python2.7/site-packages/WMCore/Database/MySQLCore.py", line 127, in executebinds
    return DBInterface.executebinds(self, s, b, connection, returnCursor)
  File "/data/srv/wmagent/v1.2.2.patch3/sw/slc7_amd64_gcc630/cms/wmagent/1.2.2.patch3/lib/python2.7/site-packages/WMCore/Database/DBCore.py", line 64, in executebinds
    resultProxy = connection.execute(s, b)
  File "/data/srv/wmagent/v1.2.2.patch3/sw/slc7_amd64_gcc630/external/py2-sqlalchemy/1.3.3/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 982, in execute
    return self._execute_text(object_, multiparams, params)
  File "/data/srv/wmagent/v1.2.2.patch3/sw/slc7_amd64_gcc630/external/py2-sqlalchemy/1.3.3/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1155, in _execute_text
    parameters,
  File "/data/srv/wmagent/v1.2.2.patch3/sw/slc7_amd64_gcc630/external/py2-sqlalchemy/1.3.3/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1248, in _execute_context
    e, statement, parameters, cursor, context
  File "/data/srv/wmagent/v1.2.2.patch3/sw/slc7_amd64_gcc630/external/py2-sqlalchemy/1.3.3/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1466, in _handle_dbapi_exception
    util.raise_from_cause(sqlalchemy_exception, exc_info)
  File "/data/srv/wmagent/v1.2.2.patch3/sw/slc7_amd64_gcc630/external/py2-sqlalchemy/1.3.3/lib/python2.7/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/v1.2.2.patch3/sw/slc7_amd64_gcc630/external/py2-sqlalchemy/1.3.3/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1244, in _execute_context
    cursor, statement, parameters, context
  File "/data/srv/wmagent/v1.2.2.patch3/sw/slc7_amd64_gcc630/external/py2-sqlalchemy/1.3.3/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 552, in do_execute
    cursor.execute(statement, parameters)
  File "/data/srv/wmagent/v1.2.2.patch3/sw/slc7_amd64_gcc630/external/py2-mysqldb/1.2.4b4-comp2/lib/python2.7/site-packages/MySQLdb/cursors.py", line 202, in execute
    self.errorhandler(self, exc, value)
  File "/data/srv/wmagent/v1.2.2.patch3/sw/slc7_amd64_gcc630/external/py2-mysqldb/1.2.4b4-comp2/lib/python2.7/site-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
    raise errorclass, errorvalue
DataError: (_mysql_exceptions.DataError) (1264, "Out of range value for column 'events' at row 1")
```

Changed files:
src/python/WMCore/WMBS/CreateWMBSBase.py, src/python/WMCore/WMBS/MySQL/Create.py, src/python/WMComponent/DBS3Buffer/MySQL/Create.py

diff --git a/src/python/WMComponent/DBS3Buffer/MySQL/Create.py b/src/python/WMComponent/DBS3Buffer/MySQL/Create.py
index 28c5af261..f81a0dfe2 100644
--- a/src/python/WMComponent/DBS3Buffer/MySQL/Create.py
+++ b/src/python/WMComponent/DBS3Buffer/MySQL/Create.py
@@ -90,7 +90,7 @@ class Create(DBCreator):
                  id                    INTEGER      AUTO_INCREMENT,
                  lfn                   VARCHAR(500) NOT NULL,
                  filesize              BIGINT,
-                 events                INTEGER,
+                 events                BIGINT UNSIGNED,
                  dataset_algo          INTEGER      NOT NULL,
                  block_id              INTEGER,
                  status                VARCHAR(20),
@@ -111,7 +111,7 @@ class Create(DBCreator):
                  filename    INTEGER NOT NULL,
                  run         INTEGER NOT NULL,
                  lumi        INTEGER NOT NULL,
-                 num_events      INTEGER)"""
+                 num_events  BIGINT UNSIGNED)"""
 
         self.create[len(self.create)] = \\
             """CREATE TABLE dbsbuffer_location (
diff --git a/src/python/WMCore/WMBS/CreateWMBSBase.py b/src/python/WMCore/WMBS/CreateWMBSBase.py
index c7b1292f1..435d9d589 100644
--- a/src/python/WMCore/WMBS/CreateWMBSBase.py
+++ b/src/python/WMCore/WMBS/CreateWMBSBase.py
@@ -77,8 +77,8 @@ class CreateWMBSBase(DBCreator):
                id           INTEGER      PRIMARY KEY AUTO_INCREMENT,
                lfn          VARCHAR(700) NOT NULL,
                filesize     BIGINT,
-               events       INTEGER,
-               first_event  BIGINT       NOT NULL DEFAULT 0,
+               events       BIGINT UNSIGNED,
+               first_event  BIGINT       UNSIGNED NOT NULL DEFAULT 0,
                merged       INT(1)       NOT NULL DEFAULT 0,
                UNIQUE (lfn))"""
 
@@ -104,7 +104,7 @@ class CreateWMBSBase(DBCreator):
                fileid  INTEGER NOT NULL,
                run     INTEGER NOT NULL,
                lumi    INTEGER NOT NULL,
-               num_events  INTEGER,
+               num_events  BIGINT UNSIGNED,
                PRIMARY KEY (fileid, run, lumi),
                FOREIGN KEY (fileid) references wmbs_file_details(id)
                  ON DELETE CASCADE)"""
diff --git a/src/python/WMCore/WMBS/MySQL/Create.py b/src/python/WMCore/WMBS/MySQL/Create.py
index 6feff9b8f..e2a49ca87 100644
--- a/src/python/WMCore/WMBS/MySQL/Create.py
+++ b/src/python/WMCore/WMBS/MySQL/Create.py
@@ -67,6 +67,5 @@ class Create(CreateWMBSBase):
     def execute(self, conn=None, transaction=None):
         for i in self.create.keys():
             self.create[i] += " ENGINE=InnoDB ROW_FORMAT=DYNAMIC"
-            self.create[i] = self.create[i].replace('INTEGER', 'INT(11)')
 
         return CreateWMBSBase.execute(self, conn, transaction)