Streaming does not work for MySQL

I wrote the following code

val xs = Transactor.fromDriverManager[IO](
           "com.mysql.cj.jdbc.Driver", 
           "jdbc:mysql://myserver:3306/mydb?useLegacyDatetimeCode=false&nullNamePatternMatchesAll=true&serverTimezone=America/Chicago&useSSL=false", 
           "user", 
           "pass")

sql"select id from foo"
       .query[Long]
       .stream
       .take(10)
       .compile
       .toList
       .transact(xs)
       .unsafeRunSync
       .foreach(println)

Here the table foo has 100 million records. when I run this code I get an error

java.lang.OutOfMemoryError: Java heap space

So even though I am using Streaming, the program is trying to load all the IDs into memory and that is why it is getting a out of memory exception.

In the past, I tried to do Streaming of query results using Slick and I faced similar issue there as well. It turned out that in order to enable Streaming on MySQL. we need a special setup. This was documented here

I want to know what is the right syntax in Doobie so that we can stream results from MySQL as opposed to loading the entire resultset in memory.

I have posted this problem here as well https://stackoverflow.com/questions/50008051/doobie-streaming-not-working-for-mysql

Start with the reported Transactor.fromDriverManager setup and the .stream call, then compare the MySQL JDBC streaming requirements documented in Slick issue 1218. Reproduce the query against a large table and verify that taking 10 rows does not load the complete result set into memory; the Stack Overflow report provides additional context.