Different results with Python MySQL.connector than MySQL Workbench
<p>When I run a query in MySQL Workbench I receive 11 results (as expected), but when I run exactly the same query using python-mysql.connector I get 0 results. Any idea how that is possible?</p>

<p>I have enabled slow log with query_time=0 to log details for every query.</p>

<p>Query using MySQL Workbench:</p>

<pre><code># Time: 2016-07-24T16:09:29.154764Z
# User@Host: root[root] @ localhost [127.0.0.1]  Id:     4
# Query_time: 0.007341  Lock_time: 0.000326 Rows_sent: 11  Rows_examined: 536
SET timestamp=1469376569;
SELECT rank, IF(rank&gt;10,NULL,ccy) AS ccy, SUM(req_count)
    FROM (
      SELECT @rank := (@rank := @rank+1) AS rank, ccy, req_count
      FROM (
        SELECT want_currency AS ccy, COUNT(*) AS req_count
        FROM stage.data_tracking
        WHERE user_location LIKE '%Singapore%' AND
          search_country = 'SG' AND
          have_currency = 'SGD' AND
          want_currency != 'SGD' AND
          amount_usd != 1000
        GROUP BY want_currency
        ORDER BY req_count DESC
      ) AS d1
    ) AS d2
    CROSS JOIN (SELECT @rank := 0) AS param
      WHERE rank IS NOT NULL
    GROUP BY LEAST(rank, 11);
</code></pre>

<p>Python:</p>

<pre><code># Time: 2016-07-24T16:01:12.230648Z
# User@Host: root[root] @ localhost [127.0.0.1]  Id:    11
# Query_time: 0.003171  Lock_time: 0.000188 Rows_sent: 0 Rows_examined: 514
SET timestamp=1469376072;
SELECT rank, IF(rank&gt;10,NULL,ccy) AS ccy, SUM(req_count)
    FROM (
      SELECT @rank := (@rank := @rank+1) AS rank, ccy, req_count
      FROM (
        SELECT want_currency AS ccy, COUNT(*) AS req_count
        FROM stage.data_tracking
        WHERE user_location LIKE '%Singapore%' AND
          search_country = 'SG' AND
          have_currency = 'SGD' AND
          want_currency != 'SGD' AND
          amount_usd != 1000
        GROUP BY want_currency
        ORDER BY req_count DESC
      ) AS d1
    ) AS d2
    CROSS JOIN (SELECT @rank := 0) AS param
      WHERE rank IS NOT NULL
    GROUP BY LEAST(rank, 11);
</code></pre>

<p>As far as I can tell these queries are identical, still the number of rows sent and rows examined are different.</p>

<p>My Python code is straightforward:</p>

<pre><code>config = {
  'user': os.environ.get("DB_USERNAME"),
  'password': os.environ.get("DB_PASSWORD"),
  'host': os.environ.get("DB_HOST"),
  'database': os.environ.get("DB_DATABASE"),
  'raise_on_warnings': True,
  'buffered': True,
}

try:
  conn = mysql.connector.connect(**config)
except mysql.connector.Error as err:
  if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
    print("Something is wrong with your user name or password")
    raise
  elif err.errno == errorcode.ER_BAD_DB_ERROR:
    print("Database does not exist")
    raise
  else:
    print(err)
    raise

query = """
  SELECT rank, IF(rank&gt;10,NULL,ccy) AS ccy, SUM(req_count)
  FROM (
    SELECT @rank := (@rank := @rank+1) AS rank, ccy, req_count
    FROM (
      SELECT want_currency AS ccy, COUNT(*) AS req_count
      FROM stage.data_tracking
      WHERE user_location LIKE %s AND
        search_country = %s AND
        have_currency = %s AND
        want_currency != %s AND
        amount_usd != 1000
      GROUP BY want_currency
      ORDER BY req_count DESC
    ) AS d1
  ) AS d2
  CROSS JOIN (SELECT @rank := 0) AS param
    WHERE rank IS NOT NULL
  GROUP BY LEAST(rank, 11)
"""

params = ("%Singapore%", "SG", "SGD", "SGD")

cursor = conn.cursor()
cursor.execute(query, params)
row = cursor.fetchone()
print(row)
while row is not None:
  print(row)
  row = cursor.fetchone()
</code></pre>

<p>I tried adding/removing connection params (like buffered), fetchall instead of fetchone, different mysql user.</p>

<p>I'm running:</p>

<ul>
<li>python 3.5.2</li>
<li>python-mysql.connector 2.0.4</li>
<li>mysql 5.7.13</li>
</ul>

<p><strong>Update</strong></p>

<p>Found a workaround! If I change the code to this:</p>

<pre><code>cursor = conn.cursor()
cursor.execute(query, params)
cursor.close()
cursor = conn.cursor()
cursor.execute(query, params)
row = cursor.fetchone()
print(row)
while row is not None:
  print(row)
  row = cursor.fetchone()
</code></pre>

<p>Then the correct result will be shown! What would cause such behavior? Would that be a bug in the python.mysql-connector?</p>