Error: ER_SP_DOES_NOT_EXIST: FUNCTION box.JSON_ARRAYAGG does not exist



  • Hi,

    Since some days my cloudron is broken, I'm not able to open the web ui or any app.
    If I try to open an app I get an SSL error, when I try to open the cloudron webpanel I get this error page:
    https://my.00000.art/error.html
    0d34b86f-aa97-4289-912c-2f938f37d7e3-image.png

    If I have a look at the logs directly after restarting the box service I see the this:

    Dec 01 20:54:13 my.00000.art node[23265]: ==========================================
    Dec 01 20:54:13 my.00000.art node[23265]:  Cloudron will use the following settings
    Dec 01 20:54:13 my.00000.art node[23265]: ==========================================
    Dec 01 20:54:13 my.00000.art node[23265]:  Environment:                     CLOUDRON
    Dec 01 20:54:13 my.00000.art node[23265]:  Version:                         3.3.4
    Dec 01 20:54:13 my.00000.art node[23265]:  Admin Origin:                    https://my.00000.art
    Dec 01 20:54:13 my.00000.art node[23265]:  Appstore API server origin:      https://api.cloudron.io
    Dec 01 20:54:13 my.00000.art node[23265]:  Appstore Web server origin:      https://cloudron.io
    Dec 01 20:54:13 my.00000.art node[23265]:  SysAdmin Port:                   3001
    Dec 01 20:54:13 my.00000.art node[23265]:  LDAP Server Port:                3002
    Dec 01 20:54:13 my.00000.art node[23265]:  Docker Proxy Port:               3003
    Dec 01 20:54:13 my.00000.art node[23265]: ==========================================
    Dec 01 20:54:13 my.00000.art node[23265]: Warning: connect.session() MemoryStore is not
    Dec 01 20:54:13 my.00000.art node[23265]: designed for a production environment, as it will leak
    Dec 01 20:54:13 my.00000.art node[23265]: memory, and will not scale past a single process.
    Dec 01 20:54:13 my.00000.art node[23265]: box:shell reload execFile: /usr/bin/sudo -S /home/yellowtent/box/src/scripts/reloadnginx.sh
    Dec 01 20:54:13 my.00000.art node[23265]: box:cloudron configureWebadmin: adminDomain:00000.art status:{"dns":false,"tls":false,"configuring":false,"restore":{"active":false,"error":null}}
    Dec 01 20:54:13 my.00000.art sudo[23274]: pam_unix(sudo:session): session opened for user root by (uid=0)
    Dec 01 20:54:13 my.00000.art node[23265]: box:platform initializing addon infrastructure
    Dec 01 20:54:13 my.00000.art node[23265]: box:platform Updating infrastructure from 48.11.0 to 48.12.1
    Dec 01 20:54:13 my.00000.art node[23265]: box:locker Acquired : platform_start
    Dec 01 20:54:13 my.00000.art node[23265]: box:platform stopping all containers for infra upgrade
    Dec 01 20:54:13 my.00000.art node[23265]: box:shell docker ps -qa | xargs --no-run-if-empty docker stop
    Dec 01 20:54:13 my.00000.art sudo[23274]: pam_unix(sudo:session): session closed for user root
    Dec 01 20:54:13 my.00000.art node[23265]: box:shell docker ps -qa | xargs --no-run-if-empty docker rm -f
    Dec 01 20:54:13 my.00000.art node[23265]: box:platform startApps: reconfiguring installed apps
    Dec 01 20:54:13 my.00000.art node[23265]: box:reverseproxy configureDefaultServer: done
    Dec 01 20:54:13 my.00000.art node[23265]: box:dockerproxy startDockerProxy: started proxy on port 3003
    Dec 01 20:54:13 my.00000.art node[23265]: Cloudron is up and running
    Dec 01 20:54:13 my.00000.art node[23265]: box:cloudron AppsError: Internal error
    Dec 01 20:54:13 my.00000.art node[23265]:     at /home/yellowtent/box/src/apps.js:1331:36
    Dec 01 20:54:13 my.00000.art node[23265]:     at /home/yellowtent/box/src/apps.js:465:31
    Dec 01 20:54:13 my.00000.art node[23265]:     at /home/yellowtent/box/src/appdb.js:233:27
    Dec 01 20:54:13 my.00000.art node[23265]:     at Query.args.(anonymous function) [as _callback] (/home/yellowtent/box/src/database.js:153:9)
    Dec 01 20:54:13 my.00000.art node[23265]:     at Query.Sequence.end (/home/yellowtent/box/node_modules/mysql/lib/protocol/sequences/Sequence.js:88:24)
    Dec 01 20:54:13 my.00000.art node[23265]:     at Query.ErrorPacket (/home/yellowtent/box/node_modules/mysql/lib/protocol/sequences/Query.js:90:8)
    Dec 01 20:54:13 my.00000.art node[23265]:     at Protocol._parsePacket (/home/yellowtent/box/node_modules/mysql/lib/protocol/Protocol.js:279:23)
    Dec 01 20:54:13 my.00000.art node[23265]:     at Parser.write (/home/yellowtent/box/node_modules/mysql/lib/protocol/Parser.js:76:12)
    Dec 01 20:54:13 my.00000.art node[23265]:     at Protocol.write (/home/yellowtent/box/node_modules/mysql/lib/protocol/Protocol.js:39:16)
    Dec 01 20:54:13 my.00000.art node[23265]:     at Socket.<anonymous> (/home/yellowtent/box/node_modules/mysql/lib/Connection.js:103:28)
    Dec 01 20:54:13 my.00000.art node[23265]: box:domains upsertDNSRecord: my on 00000.art type A values [ '37.000.000.73' ]
    Dec 01 20:54:13 my.00000.art node[23265]: box:dns/cloudflare upsert: my for zone 00000.art of type A with values ["37.000.000.73"]
    Dec 01 20:54:14 my.00000.art node[23265]: box:cloudron addWebadminDnsRecord: updated records with error: null
    Dec 01 20:54:14 my.00000.art node[23265]: box:dns/waitfordns waitForDns: domain my.00000.art to be 37.000.000.73 in zone 00000.art.
    Dec 01 20:54:14 my.00000.art node[23265]: box:dns/waitfordns waitForDns (try 1): my.00000.art to be 37.000.000.73 in zone 00000.art
    Dec 01 20:54:14 my.00000.art node[23265]: box:dns/waitfordns resolveIp: Checking if my.00000.art has A record at 173.245.59.105
    Dec 01 20:54:14 my.00000.art node[23265]: box:dns/waitfordns resolveIp: Checking if my.00000.art has A record at 173.245.58.145
    Dec 01 20:54:14 my.00000.art node[23265]: box:dns/waitfordns isChangeSynced: my.00000.art (A) was resolved to 37.000.000.73 at NS brad.ns.cloudflare.com (173.245.59.105). Expecting 37.000.000.73. Match true
    Dec 01 20:54:14 my.00000.art node[23265]: box:dns/waitfordns isChangeSynced: my.00000.art (A) was resolved to 37.000.000.73 at NS sue.ns.cloudflare.com (173.245.58.145). Expecting 37.000.000.73. Match true
    Dec 01 20:54:14 my.00000.art node[23265]: box:dns/waitfordns waitForDns: my.00000.art done ns: ["brad.ns.cloudflare.com","sue.ns.cloudflare.com"]
    Dec 01 20:54:14 my.00000.art node[23265]: box:dns/waitfordns waitForDns: my.00000.art has propagated
    Dec 01 20:54:14 my.00000.art node[23265]: box:cloudron configureReverseProxy: error null
    Dec 01 20:54:14 my.00000.art node[23265]: box:reverseproxy ensureCertificate: my.00000.art certificate already exists at /home/yellowtent/boxdata/certs/my.00000.art.key
    Dec 01 20:54:14 my.00000.art node[23265]: box:reverseproxy isExpiringSync: /home/yellowtent/boxdata/certs/my.00000.art.cert Certificate will not expire 0
    Dec 01 20:54:14 my.00000.art node[23265]: box:reverseproxy providerMatchesSync: /home/yellowtent/boxdata/certs/my.00000.art.cert subject= /CN=my.00000.art domain=my.00000.art issuer= /C=US/O=Let's Encrypt/CN=Let's Encrypt Authority X3 
    Dec 01 20:54:14 my.00000.art node[23265]: box:shell reload execFile: /usr/bin/sudo -S /home/yellowtent/box/src/scripts/reloadnginx.sh
    Dec 01 20:54:14 my.00000.art sudo[23306]: pam_unix(sudo:session): session opened for user root by (uid=0)
    Dec 01 20:54:14 my.00000.art sudo[23306]: pam_unix(sudo:session): session closed for user root
    Dec 01 20:54:14 my.00000.art node[23265]: box:cloudron configureWebadmin: done error: {}
    

    Everytime I open the Web UI this error appears in the log:

    Dec 01 21:04:13 my.00000.art node[23265]: box:locker Lock unreleased platform_start
    Dec 01 21:04:26 my.00000.art node[23265]: ERROR Internal error [ /home/yellowtent/box/node_modules/connect-lastmile/lib/index.js:37:25 ]
    Dec 01 21:04:26 my.00000.art node[23265]: stack:
    Dec 01 21:04:26 my.00000.art node[23265]:   """
    Dec 01 21:04:26 my.00000.art node[23265]:     HttpError: Internal error
    Dec 01 21:04:26 my.00000.art node[23265]:         at /home/yellowtent/box/src/routes/apps.js:84:32
    Dec 01 21:04:26 my.00000.art node[23265]:         at /home/yellowtent/box/src/apps.js:495:27
    Dec 01 21:04:26 my.00000.art node[23265]:         at /home/yellowtent/box/src/apps.js:465:31
    Dec 01 21:04:26 my.00000.art node[23265]:         at /home/yellowtent/box/src/appdb.js:233:27
    Dec 01 21:04:26 my.00000.art node[23265]:         at Query.args.(anonymous function) [as _callback] (/home/yellowtent/box/src/database.js:153:9)
    Dec 01 21:04:26 my.00000.art node[23265]:         at Query.Sequence.end (/home/yellowtent/box/node_modules/mysql/lib/protocol/sequences/Sequence.js:88:24)
    Dec 01 21:04:26 my.00000.art node[23265]:         at Query.ErrorPacket (/home/yellowtent/box/node_modules/mysql/lib/protocol/sequences/Query.js:90:8)
    Dec 01 21:04:26 my.00000.art node[23265]:         at Protocol._parsePacket (/home/yellowtent/box/node_modules/mysql/lib/protocol/Protocol.js:279:23)
    Dec 01 21:04:26 my.00000.art node[23265]:         at Parser.write (/home/yellowtent/box/node_modules/mysql/lib/protocol/Parser.js:76:12)
    Dec 01 21:04:26 my.00000.art node[23265]:         at Protocol.write (/home/yellowtent/box/node_modules/mysql/lib/protocol/Protocol.js:39:16)
    Dec 01 21:04:26 my.00000.art node[23265]:   """
    Dec 01 21:04:26 my.00000.art node[23265]: name:          HttpError
    Dec 01 21:04:26 my.00000.art node[23265]: status:        500
    Dec 01 21:04:26 my.00000.art node[23265]: message:       Internal error
    Dec 01 21:04:26 my.00000.art node[23265]: internalError:
    Dec 01 21:04:26 my.00000.art node[23265]:   """
    Dec 01 21:04:26 my.00000.art node[23265]:     stack:
    Dec 01 21:04:26 my.00000.art node[23265]:       """
    Dec 01 21:04:26 my.00000.art node[23265]:         AppsError: Internal error
    Dec 01 21:04:26 my.00000.art node[23265]:             at /home/yellowtent/box/src/apps.js:465:40
    Dec 01 21:04:26 my.00000.art node[23265]:             at /home/yellowtent/box/src/appdb.js:233:27
    Dec 01 21:04:26 my.00000.art node[23265]:             at Query.args.(anonymous function) [as _callback] (/home/yellowtent/box/src/database.js:153:9)
    Dec 01 21:04:26 my.00000.art node[23265]:             at Query.Sequence.end (/home/yellowtent/box/node_modules/mysql/lib/protocol/sequences/Sequence.js:88:24)
    Dec 01 21:04:26 my.00000.art node[23265]:             at Query.ErrorPacket (/home/yellowtent/box/node_modules/mysql/lib/protocol/sequences/Query.js:90:8)
    Dec 01 21:04:26 my.00000.art node[23265]:             at Protocol._parsePacket (/home/yellowtent/box/node_modules/mysql/lib/protocol/Protocol.js:279:23)
    Dec 01 21:04:26 my.00000.art node[23265]:             at Parser.write (/home/yellowtent/box/node_modules/mysql/lib/protocol/Parser.js:76:12)
    Dec 01 21:04:26 my.00000.art node[23265]:             at Protocol.write (/home/yellowtent/box/node_modules/mysql/lib/protocol/Protocol.js:39:16)
    Dec 01 21:04:26 my.00000.art node[23265]:             at Socket.<anonymous> (/home/yellowtent/box/node_modules/mysql/lib/Connection.js:103:28)
    Dec 01 21:04:26 my.00000.art node[23265]:             at emitOne (events.js:116:13)
    Dec 01 21:04:26 my.00000.art node[23265]:       """
    Dec 01 21:04:26 my.00000.art node[23265]:     name:        AppsError
    Dec 01 21:04:26 my.00000.art node[23265]:     reason:      Internal Error
    Dec 01 21:04:26 my.00000.art node[23265]:     message:     Internal error
    Dec 01 21:04:26 my.00000.art node[23265]:     nestedError:
    Dec 01 21:04:26 my.00000.art node[23265]:       """
    Dec 01 21:04:26 my.00000.art node[23265]:         stack:
    Dec 01 21:04:26 my.00000.art node[23265]:           """
    Dec 01 21:04:26 my.00000.art node[23265]:             DatabaseError: Internal error
    Dec 01 21:04:26 my.00000.art node[23265]:                 at /home/yellowtent/box/src/appdb.js:233:36
    Dec 01 21:04:26 my.00000.art node[23265]:                 at Query.args.(anonymous function) [as _callback] (/home/yellowtent/box/src/database.js:153:9)
    Dec 01 21:04:26 my.00000.art node[23265]:                 at Query.Sequence.end (/home/yellowtent/box/node_modules/mysql/lib/protocol/sequences/Sequence.js:88:24)
    Dec 01 21:04:26 my.00000.art node[23265]:                 at Query.ErrorPacket (/home/yellowtent/box/node_modules/mysql/lib/protocol/sequences/Query.js:90:8)
    Dec 01 21:04:26 my.00000.art node[23265]:                 at Protocol._parsePacket (/home/yellowtent/box/node_modules/mysql/lib/protocol/Protocol.js:279:23)
    Dec 01 21:04:26 my.00000.art node[23265]:                 at Parser.write (/home/yellowtent/box/node_modules/mysql/lib/protocol/Parser.js:76:12)
    Dec 01 21:04:26 my.00000.art node[23265]:                 at Protocol.write (/home/yellowtent/box/node_modules/mysql/lib/protocol/Protocol.js:39:16)
    Dec 01 21:04:26 my.00000.art node[23265]:                 at Socket.<anonymous> (/home/yellowtent/box/node_modules/mysql/lib/Connection.js:103:28)
    Dec 01 21:04:26 my.00000.art node[23265]:                 at emitOne (events.js:116:13)
    Dec 01 21:04:26 my.00000.art node[23265]:                 at Socket.emit (events.js:211:7)
    Dec 01 21:04:26 my.00000.art node[23265]:           """
    Dec 01 21:04:26 my.00000.art node[23265]:         name:        DatabaseError
    Dec 01 21:04:26 my.00000.art node[23265]:         reason:      Internal error
    Dec 01 21:04:26 my.00000.art node[23265]:         message:     Internal error
    Dec 01 21:04:26 my.00000.art node[23265]:         nestedError:
    Dec 01 21:04:26 my.00000.art node[23265]:           """
    Dec 01 21:04:26 my.00000.art node[23265]:             stack:
    Dec 01 21:04:26 my.00000.art node[23265]:               """
    Dec 01 21:04:26 my.00000.art node[23265]:                 Error: ER_SP_DOES_NOT_EXIST: FUNCTION box.JSON_ARRAYAGG does not exist
    Dec 01 21:04:26 my.00000.art node[23265]:                     at Query.Sequence._packetToError (/home/yellowtent/box/node_modules/mysql/lib/protocol/sequences/Sequence.js:52:14)
    Dec 01 21:04:26 my.00000.art node[23265]:                     at Query.ErrorPacket (/home/yellowtent/box/node_modules/mysql/lib/protocol/sequences/Query.js:77:18)
    Dec 01 21:04:26 my.00000.art node[23265]:                     at Protocol._parsePacket (/home/yellowtent/box/node_modules/mysql/lib/protocol/Protocol.js:279:23)
    Dec 01 21:04:26 my.00000.art node[23265]:                     at Parser.write (/home/yellowtent/box/node_modules/mysql/lib/protocol/Parser.js:76:12)
    Dec 01 21:04:26 my.00000.art node[23265]:                     at Protocol.write (/home/yellowtent/box/node_modules/mysql/lib/protocol/Protocol.js:39:16)
    Dec 01 21:04:26 my.00000.art node[23265]:                     at Socket.<anonymous> (/home/yellowtent/box/node_modules/mysql/lib/Connection.js:103:28)
    Dec 01 21:04:26 my.00000.art node[23265]:                     at emitOne (events.js:116:13)
    Dec 01 21:04:26 my.00000.art node[23265]:                     at Socket.emit (events.js:211:7)
    Dec 01 21:04:26 my.00000.art node[23265]:                     at addChunk (_stream_readable.js:263:12)
    Dec 01 21:04:26 my.00000.art node[23265]:                     at readableAddChunk (_stream_readable.js:250:11)
    Dec 01 21:04:26 my.00000.art node[23265]:                     --------------------
    Dec 01 21:04:26 my.00000.art node[23265]:                     at Protocol._enqueue (/home/yellowtent/box/node_modules/mysql/lib/protocol/Protocol.js:145:48)
    Dec 01 21:04:26 my.00000.art node[23265]:                     at PoolConnection.query (/home/yellowtent/box/node_modules/mysql/lib/Connection.js:208:25)
    Dec 01 21:04:26 my.00000.art node[23265]:                     at Object.query (/home/yellowtent/box/src/database.js:156:30)
    Dec 01 21:04:26 my.00000.art node[23265]:                     at Object.getAll (/home/yellowtent/box/src/appdb.js:225:14)
    Dec 01 21:04:26 my.00000.art node[23265]:                     at /home/yellowtent/box/src/apps.js:464:15
    Dec 01 21:04:26 my.00000.art node[23265]:                     at /home/yellowtent/box/src/domaindb.js:51:9
    Dec 01 21:04:26 my.00000.art node[23265]:                     at Query.args.(anonymous function) [as _callback] (/home/yellowtent/box/src/database.js:153:9)
    Dec 01 21:04:26 my.00000.art node[23265]:                     at Query.Sequence.end (/home/yellowtent/box/node_modules/mysql/lib/protocol/sequences/Sequence.js:88:24)
    Dec 01 21:04:26 my.00000.art node[23265]:                     at Query._handleFinalResultPacket (/home/yellowtent/box/node_modules/mysql/lib/protocol/sequences/Query.js:139:8)
    Dec 01 21:04:26 my.00000.art node[23265]:                     at Query.EofPacket (/home/yellowtent/box/node_modules/mysql/lib/protocol/sequences/Query.js:123:8)
    Dec 01 21:04:26 my.00000.art node[23265]:               """
    Dec 01 21:04:26 my.00000.art node[23265]:             message:    ER_SP_DOES_NOT_EXIST: FUNCTION box.JSON_ARRAYAGG does not exist
    Dec 01 21:04:26 my.00000.art node[23265]:             code:       ER_SP_DOES_NOT_EXIST
    Dec 01 21:04:26 my.00000.art node[23265]:             errno:      1305
    Dec 01 21:04:26 my.00000.art node[23265]:             sqlMessage: FUNCTION box.JSON_ARRAYAGG does not exist
    Dec 01 21:04:26 my.00000.art node[23265]:             sqlState:   42000
    Dec 01 21:04:26 my.00000.art node[23265]:             index:      0
    Dec 01 21:04:26 my.00000.art node[23265]:             sql:        SELECT apps.id,apps.appStoreId,apps.installationState,apps.installationProgress,apps.runState,apps.health,apps.containerId,apps.manifestJson,apps.httpPort,subdomains.subdom
    Dec 01 21:04:26 my.00000.art node[23265]:           """
    Dec 01 21:04:26 my.00000.art node[23265]:       """
    Dec 01 21:04:26 my.00000.art node[23265]:   """
    Dec 01 21:04:26 my.00000.art node[23265]: Box GET /api/v1/apps 500 8.294 ms - 70
    

    I don't know when this problem started to happen and I don't know if this problem has been intruduced with an update, however according to the last entry on one of the app logs:
    /home/yellowtent/platformdata/logs/d7bcfaae-83b3-4ce0-babd-6406b6557559/app.log
    it probably started on the 20th of november.

    I don't know if this has something to do with the problem but after over one year of using cloudron with a subscription I cancelled it. If I'm right the subscription expired on the 22nd of november.

    Maybe someone is able to help me to get cloudron running again.

    Thanks in advance



  • @aft2d I think the issue is that the mysql is not on the correct version. I think you will see that MySQL is on 5.7.18 or something.

    root@my:~# mysql --version
    mysql  Ver 14.14 Distrib 5.7.24, for Linux (x86_64) using  EditLine wrapper
    

    You can update it like so:

    apt-get update
    apt-get install mysql-server
    mysql --version # should have the 5.7.24
    systemctl restart cloudron.target # this will restart cloudron box code
    


  • Yep, thanks. it worked.