ERROR 1305 (42000): FUNCTION UUID_SHORT does not exist

[xiuwensu]

Please answer these questions before submitting your issue. Thanks!

1. What did you do?
   If possible, provide a recipe for reproducing the error.

SELECT UUID_SHORT();

2. What did you expect to see?

+-------------------+
| UUID_SHORT() |
+-------------------+
| 97316296756035585 |
+-------------------+
1 row in set (0.00 sec)

3. What did you see instead?

ERROR 1305 (42000): FUNCTION UUID_SHORT does not exist

4. What version of TiDB are you using (tidb-server -V)?

Release Version: 0.9.0
Git Commit Hash: fa87ec4
Git Commit Branch: HEAD
UTC Build Time: 2017-09-24 01:38:26

[XuHuaiyu]

Hi, @GuoYuan-DiYiDan , if possible, can you send us a PR to support this function?
This can be referred.

[xiuwensu]

@XuHuaiyu Thanks for your invitation.
I'll give a try to help the community.

Here is more info about this issue:
In our MySQL application, we use UUID_SHORT() to generate global unique id for our application.
I've noticed an Auto-Increment ID Feature implemented from Compatibility with MySQL docs.
That feature is what we need, but I don't find the API or CMD the get this Auto-Increment ID from TiDB-Server instance.

[XuHuaiyu]

@GuoYuan-DiYiDan
I'm not sure what the auto-increment ID means, and why we need an auto-increment ID?

According to the MySQL manual, (server_id & 255) << 56 + (server_startup_time_in_seconds << 24) seems to be an initial value for this function, and it'll increment 1 every time when this funtion be invoked if the server has not been restarted.

Can we maintain a uint64 variable as an attribute in the implementation of uuid_short,
and increment it with global mutex lock every time this function be invoked.

[xiuwensu]

@XuHuaiyu
The Auto-Increment ID Feature can be found here . You may use Ctrl + F to located it.

For lots of companies and applications, auto-incremented global unique id is a needed feature in back-end distributed service development . Up to now, our auto-incremented global unique id generation strategy depends on MySQL's UUID_SHORT() function.

MySQL's UUID_SHORT implementation is easy to understand and I agree with your solution.
But I advice you check Auto-Increment ID Feature in the docs first. If this feature is already implemented, you may just wrapper it and provide an API.

[XuHuaiyu]

@GuoYuan-DiYiDan
Ok, I know what you mean here, what you really need is a global auto-increment unique id?
This auto-increment id which is usually used for auto-increment primary key id can only be promised to be global auto-increment unique for the same table.

In TiDB, if you want to get a global auto-increment unique id, you can use

begin; select @@tidb_current_ts;

Or, if you want to keep using uuid_short(), to keep consistent with MySQL behavior,
we may use a member attribute to implement this.

[xiuwensu]

@XuHuaiyu
Thanks for your detailed answer.
In my machine I get this from TiDB:

MySQL [test]> begin; select @@tidb_current_ts;
Query OK, 0 rows affected (0.01 sec)

+--------------------+
| @@tidb_current_ts |
+--------------------+
| 394875078526107649 |
+--------------------+
1 row in set (0.00 sec)

MySQL [test]> begin; select @@tidb_current_ts;
Query OK, 0 rows affected (0.00 sec)

+--------------------+
| @@tidb_current_ts |
+--------------------+
| 394875078526107649 |
+--------------------+
1 row in set (0.00 sec)

MySQL [test]> begin; select @@tidb_current_ts;
Query OK, 0 rows affected (0.00 sec)

+--------------------+
| @@tidb_current_ts |
+--------------------+
| 394875078526107649 |
+--------------------+
1 row in set (0.00 sec)

You can see that the result is not auto-incrementing after per call.

MySQL 5.7 works well:

mysql> SELECT UUID_SHORT();
+-------------------+
| UUID_SHORT() |
+-------------------+
| 97329517219020800 |
+-------------------+
1 row in set (0.02 sec)

mysql> SELECT UUID_SHORT();
+-------------------+
| UUID_SHORT() |
+-------------------+
| 97329517219020801 |
+-------------------+
1 row in set (0.00 sec)

mysql> SELECT UUID_SHORT();
+-------------------+
| UUID_SHORT() |
+-------------------+
| 97329517219020802 |
+-------------------+
1 row in set (0.00 sec)

I think its a better choice to support auto-increment after per call for a global auto-increment unique id generation strategy. So, I think we still need a uuid_short() implementation in TiDB.

Note: What I mean global auto-increment unique id is TiDB Cluster Instance scope, not just the same table scope.

[XuHuaiyu]

TiDBCurrentTS is used to get the current transaction timestamp which is a global auto-increment unique id is TiDB Cluster Instance scope, it should change every time a new transaction begins, we'll find what's wrong with it.

[xiuwensu]

@XuHuaiyu
OK. Waiting for your good news.

[XuHuaiyu]

#4628 will fix this.
But TiDBCurrentTS can only be promised to be unique among different transactions,
we can only get one TiDBCurrentTS during the same txn.

[xiuwensu]

So, currently, it means TiDB doesn't have a battery included solution for global auto-increment unqiue id like MySQL UUID_SHORT? Actually, I can solve this problem for my application with Redis. But I still hope that TiDB will implement this feature before the release of v1.0.0.

[morgo]

I think the semantics are different from an auto_increment-like ID. Auto-increment needs to be crash-safe so values are not re-used, whereas uuid_short() only promises uniqueness during server lifetime. Having said that, besides the monotonic counter, TIDB requires:

• Seconds since startup. In MySQL this is exposed via SHOW GLOBAL STATUS LIKE 'uptime'
• A numeric server-id like property.