title: Pandas DataFrame.to_sql consumes/leaves out one extra automatically generated id
body: <p>I am observing a strange behavior using Pandas <code>DataFrame.to_sql</code> function with auto incremented primary keys in a Python script (Python version: 3.9.1). After creating a database table using (A) and executing Python code (B), as expected two rows are inserted into the database table, however, retrieving the next id using command (C) shows 4 instead of the expected 3. In fact, executing code (B) three times results in the following table:</p>
  <pre><code>+----+------+------+
  | id | a    | b    |
  +----+------+------+
  |  1 |    1 |    3 |
  |  2 |    2 |    4 |
  |  4 |    1 |    3 |
  |  5 |    2 |    4 |
  |  7 |    1 |    3 |
  |  8 |    2 |    4 |
  +----+------+------+
  
  </code></pre>
  <p>Somehow id 3 and 6 are missing.</p>
  <p>I retrieve the max id at some point and add the numbers of rows to it to have the new max id, which differs by 1. Just adding one is unproblematic if that behavior is expected, but I could not find anything in the <a href="https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.to_sql.html" rel="nofollow noreferrer">docs</a>.</p>
  <p><strong>Can someone shed some light on this behavior or  explain where the example code below is causing this issue? Thanks.</strong></p>
  <h3>Environment:</h3>
  <ul>
  <li>Python: 3.9.1</li>
  <li>Mariadb: 15.1</li>
  <li>Aqlalchemy: 1.4.23)</li>
  <li>Pandas: 1.4.1</li>
  </ul>
  <h3>Code:</h3>
  <p>(A) Database table creation:</p>
  <pre><code>create table if not exists test_table (id INT auto_increment primary key, a int, b int);
  
  </code></pre>
  <p>(B) Python code:</p>
  <pre class="lang-py prettyprint-override"><code>import pandas as pd
  import sqlalchemy as sqla
  
  engine = sqla.create_engine(
      'mariadb+mariadbconnector://user:password@127.0.0.1:3306/unit_test',
      echo=True,
      future=True
  )
  
  df = pd.DataFrame({'a' : [1,2], 'b': [3,4]})
  
  _ = df.to_sql(
      'test_table',
      con=engine,
      if_exists='append',
      index=False,
      chunksize=500,
      dtype={
          'a': sqla.Integer,
          'b': sqla.Integer
      }
  
  )
  
  </code></pre>
  <p>(C) Retrieving the next id:</p>
  <pre><code>select auto_increment from information_schema.tables where table_name='test_table';
  </code></pre>