Unable to update RIR database: "Undefined index: COUNT(*)"

[gino0631]

After setting the Service to "RIRData" and pressing "Update Database", the following message is displayed:

Status of the chosen service:

"RIR Data": ERROR: The database is corrupted. Please run update again. Last error message: No entries in the database. Please run update.

Date of the database:
No database available!

Version info: GeoBlocker: 0.5.1 Nextcloud: 21.0.4

The oc_geoblocker_ls_rir table does exist and contains 280627 rows.

The log file contains multiple entries of the following info:

{
   "url":"/index.php/apps/geoblocker/service/getAllServiceData/2",
   "message":{
      "Exception":"Error",
      "Message":"Undefined index: COUNT(*) at /srv/nextcloud/apps/geoblocker/lib/Db/RIRServiceMapper.php#85",
      "Code":0,
      "Trace":[
         {
            "file":"/srv/nextcloud/apps/geoblocker/lib/Db/RIRServiceMapper.php",
            "line":85,
            "function":"onError",
            "class":"OC\\Log\\ErrorHandler",
            "type":"::",
            "args":[
               8,
               "Undefined index: COUNT(*)",
               "/srv/nextcloud/apps/geoblocker/lib/Db/RIRServiceMapper.php",
               85,
               {
                  "version":0,
                  "qb":{
                     "__class__":"OC\\DB\\QueryBuilder\\QueryBuilder"
                  },
                  "res":{
                     "count":280627
                  }
               }
            ]
         },
         {
            "file":"/srv/nextcloud/apps/geoblocker/lib/LocalizationServices/RIRData.php",
            "line":359,
            "function":"getNumberOfEntries",
            "class":"OCA\\GeoBlocker\\Db\\RIRServiceMapper",
            "type":"->",
            "args":[
               0
            ]
         },
         {
            "file":"/srv/nextcloud/apps/geoblocker/lib/LocalizationServices/RIRData.php",
            "line":101,
            "function":"checkIfEntriesForVersionExists",
            "class":"OCA\\GeoBlocker\\LocalizationServices\\RIRData",
            "type":"->",
            "args":[
               0
            ]
         },
         {
            "file":"/srv/nextcloud/apps/geoblocker/lib/LocalizationServices/RIRData.php",
            "line":136,
            "function":"checkDBPlausibleAndSetToError",
            "class":"OCA\\GeoBlocker\\LocalizationServices\\RIRData",
            "type":"->",
            "args":[

            ]
         },
         {
            "file":"/srv/nextcloud/apps/geoblocker/lib/Controller/ServiceController.php",
            "line":130,
            "function":"getStatusString",
            "class":"OCA\\GeoBlocker\\LocalizationServices\\RIRData",
            "type":"->",
            "args":[

            ]
         },
         {
            "file":"/srv/nextcloud/lib/private/AppFramework/Http/Dispatcher.php",
            "line":218,
            "function":"getAllServiceData",
            "class":"OCA\\GeoBlocker\\Controller\\ServiceController",
            "type":"->",
            "args":[
               2
            ]
         },
         {
            "file":"/srv/nextcloud/lib/private/AppFramework/Http/Dispatcher.php",
            "line":127,
            "function":"executeController",
            "class":"OC\\AppFramework\\Http\\Dispatcher",
            "type":"->",
            "args":[
               {
                  "__class__":"OCA\\GeoBlocker\\Controller\\ServiceController"
               },
               "getAllServiceData"
            ]
         },
         {
            "file":"/srv/nextcloud/lib/private/AppFramework/App.php",
            "line":157,
            "function":"dispatch",
            "class":"OC\\AppFramework\\Http\\Dispatcher",
            "type":"->",
            "args":[
               {
                  "__class__":"OCA\\GeoBlocker\\Controller\\ServiceController"
               },
               "getAllServiceData"
            ]
         },
         {
            "file":"/srv/nextcloud/lib/private/Route/Router.php",
            "line":302,
            "function":"main",
            "class":"OC\\AppFramework\\App",
            "type":"::",
            "args":[
               "OCA\\GeoBlocker\\Controller\\ServiceController",
               "getAllServiceData",
               {
                  "__class__":"OC\\AppFramework\\DependencyInjection\\DIContainer"
               },
               {
                  "id":"2",
                  "_route":"geoblocker.service.getAllServiceData"
               }
            ]
         },
         {
            "file":"/srv/nextcloud/lib/base.php",
            "line":993,
            "function":"match",
            "class":"OC\\Route\\Router",
            "type":"->",
            "args":[
               "/apps/geoblocker/service/getAllServiceData/2"
            ]
         },
         {
            "file":"/srv/nextcloud/index.php",
            "line":37,
            "function":"handleRequest",
            "class":"OC",
            "type":"::",
            "args":[

            ]
         }
      ],
      "File":"/srv/nextcloud/lib/private/Log/ErrorHandler.php",
      "Line":92,
      "CustomMessage":"--"
   },
}

[HomeITAdmin]

The result for the number of elements in the database should be in the framework in the $res variable under the index "COUNT(*)", which worked fine for all my tests. But in your case it is under the index "count" :

"res":{
       "count":280627
 }

Before fixing this, I would like to understand, why is it different in your setup. Which database type are you using?

[gino0631]

Which database type are you using?

PostgreSQL 12.7

[HomeITAdmin]

Ok, I can reproduce the issue with PostgreSQL. I would have expected the API to abstract from the database used, so that this is not happening. But good to know that this also needs to be tested. It probably does not make sense to wait for the API to be fixed, but to make code that cover both ways. When I do this I run into another Problem with PostgreSQL. I need more time to look into this.

[gino0631]

Thank you for taking a look.

Maybe COUNT(*) and count are just different names for that column that different DBMS create, and you should not rely on them. Instead, maybe it is possible to specify an alias somehow ("SELECT COUNT(*) AS X"), or retrieve the result in a different way.

I'm not a specialist in PHP frameworks, but maybe this will make sense.

[HomeITAdmin]

Thanks, that is the right solution and I found how to do it in the framework. Now it seems to work the same in all DBs.

If you do not want to wait for the next release you could manually change the file "/srv/nextcloud/apps/geoblocker/lib/Db/RIRServiceMapper.php" in line 85 from return intval($res['COUNT(*)']); to return intval($res['count']); in the Postgres case.

The other problem I saw afterwards was just a local test issue on my system, so it should work fine this way.