mysql is not valid at this position expecting eof

Question

SELECT ref,
       Sum(transactions.amount)
         OVER(
           ORDER BY id)
FROM   transactions  

localhost mysql phpmyadmin runs the code with no problems. However when uploading to heroku ClearDB "mysql is not valid at this position expecting eof" error shows

Answer 1

MySQL didn't support window functions (e.g. SUM OVER()) until version 8:

MySQL now supports window functions that, for each row from a query, perform a calculation using rows related to that row. These include functions such as RANK(), LAG(), and NTILE(). In addition, several existing aggregate functions now can be used as window functions (for example, SUM() and AVG()).

But ClearDB defaults to version 5.6 and doesn't offer anything newer than version 5.7.

There are other Heroku addons that provide MySQL, though:

• JawsDB for MySQL addon does support MySQL 8, and currently uses that version by default
• Stackhero for MySQL says it supports "up-to-date versions", but doesn't have a free tier

I suggest you switch to JawsDB for MySQL. Provision the add-on, copy your data from ClearDB to JawsDB, and point your application to JawsDB. Once you're sure everything is working you can de-provision the ClearDB database.