Solved Error: ER_SP_DOES_NOT_EXIST: FUNCTION box.JSON_ARRAYAGG does not exist

[aft2d]

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

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

[girish]

@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

[aft2d]

Yep, thanks. it worked.