title: Long running innodb query generate a big undo file in mariadb
body: <p>I have a big query in php using MYSQLI_USE_RESULT not to put all the results into the php memory.
  Because if I use MYSQLI_STORE_RESULT it will put all of the data into memory for all results, which takes multiple GB of ram, instead of getting row by row.
   It returns millions of rows and each row will generate an api request, so the query will be running for days.
  In the mean  time, I have other mysql queries that update/insert the tables related to the first query, and I think it cause the undo log to grow without stopping.</p>
  
  <p>I setup innodb_undo_tablespaces=2 and innodb_undo_log_truncate = ON
  so the undo log is separated from ibdata1, but the undo files are still big until I kill the queries that have been running for days.</p>
  
  <p>I executed "SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;" before running the long running query, hoping that it would prevent undo file to grow, but it didn't.</p>
  
  <p>The other queries that are updating/inserting have autocommit.</p>
  
  <p>In 1-2 day, the undo file is already 40GB large. </p>
  
  <p>The question : how to prevent this undo file to increase ? As I don't want to keep the previous version of the data while the query is running. It's not important if I get updated data instead of the data that was at the time of the query.</p>