UCP: make "AutoRandom" to be auto_increment_increment-aware.

[bb7133]

Description

Feature "AutoRandom" is added to TiDB v3.1 and v4.0, as a replacement of "AutoIncrement". This feature aims to provide a better solution to automatically generate primary key ID and avoid hotspots during a heavy write scenario.

For now, as an alternative to generate "auto" keys, "AutoRandom" is not auto_increment_increment aware, which lead to the following unexpected results when using JDBC, for example:

# Using MySQL client.
tidb> create table t(a bigint key auto_random)
Query OK, 0 rows affected, 1 warning (0.02 sec)
tidb> set global auto_increment_increment=2;                                                           
Query OK, 0 rows affected (0.00 sec)

And then,

// Scala code
object Test {
  val tidbUrl = s"jdbc:mysql://127.0.0.1:4000/test"

  def main(args: Array[String]): Unit = {
    val conn = DriverManager.getConnection(tidbUrl, username, password)
    val stmt = conn.createStatement()
    val incremental = {
      val res = stmt.executeQuery("select @@auto_increment_increment")
      res.next()
      res.getInt(1)
    }
    println(s"Current auto_increment_increment: $incremental")
    stmt.executeUpdate("insert into t values (), (), (), ()", Statement.RETURN_GENERATED_KEYS)
    val res = stmt.getGeneratedKeys()
    new Iterator[String] {
      def hasNext = res.next()
      def next() = res.getString(1)
    }.toStream.foreach(println)
    stmt.close()
    conn.close()
  }
}

Run the codes above, you'll get something like:

Current auto_increment_increment: 2
1879048217
1879048219
1879048221
1879048223

However, the actual inserted keys are:

tidb> select * from t;
+------------+
| a          |
+------------+
| 1879048217 |
| 1879048218 |
| 1879048219 |
| 1879048220 |
+------------+
4 rows in set (0.00 sec)

Reason of the wrong answer is that JDBC tries to calculate the generated keys through LastInsertID() and auto_increment_increment.

You can refer to #14301 to get the way how it is supported by "AutoIncrement".

Score

• 1680

Mentor(s)

• @AilinKid
• @tangenta

Contact the mentors: #tidb-challenge-program channel in TiDB Community Slack Workspace

Recommended Skills

• Golang only.

Learning Materials

• AUTO_RANDOM column option
• System variable 'auto_increment_increment' and 'auto_increment_offset'
• Closed issue 14245
• Retrieving AUTO_INCREMENT Column Values through JDBC

[niedhui]

@bb7133 @tangenta Hi mentors, currently I had a pending pr waiting for review, may I do this without pick-up-challenge first?

[tangenta]

@niedhui Sorry, I am working on this issue because the deadline is coming soon. We are appreciated that if you could pick another challenge.