TcaplusDB TDR Table MySQL Protocol Compatibility Interface Get Started

In order to improve the usability of TcaplusDB and reduce the threshold for users to switch from MySQL to TcaplusDB, TcaplusDB has supported MySQL 5.7 protocol since version 3.63.x. Users can directly use MySQL-Client, MySQL-Driver, MySQL-Workbench and other tools or drive reading and writing data in TDR Generic tables (MySQL protocol does not support accessing List tables).

Compatibility with MySQL protocol is a new feature of TcaplusDB. Currently, the supported SQL capabilities are limited.

Currently, the supported capabilities include:

1. Support the SELECT, INSERT, DELETE, and UPDATE statements of a single table, and one or more primary keys must be explicitly specified in the WHERE clause of the SELECT, DELETE, and UPDATE operations;
2. Support the basic aggregate function when the global index is configured;
3. Support PREPARED STATEMENT;
4. Support full table traversal, but differ from MySQL syntax;
5. Support ORDER BY with limited functions;
6. Support SHOW TABLES, DESC TABLE and other basic metadata queries

Currently, the unsupported capabilities include:

1. DDL, i.e. CREATE/DROP TABLE, etc;
2. GROUP BY；
3. Cross table JOIN

The preparations for using MySQL protocol to access TcaplusDB mainly include:

1. Prepare the environment;
2. Create an app;
3. Create a game zone;
4. Create a table;
5. Collect environmental information;
6. Download Client or SDK;
7. Connect to TcaplusDB.

If the above preparations have been completed, you can skip them directly.

1. Prepare the Environment

Refer to Create a Cluster to deploy the TcaplusDB cluster, or apply for the TcaplusDB service.

2. Create an App

Refer to Create an App to create an App.

3. Create A Game Zone

Refer to Create a Game Zone to create a game zone (Zone).

4. Create A Table

Refer to Add a Table to add Generic tables.

4.1. Generic Table Definition Descriptions

mysql_table Table description file content example:

<?xml version="1.0" encoding="GBK" standalone="yes" ?>
<metalib tagsetversion="1" name="tcaplus" version="1" >
   <struct name="mysql_table" version="1" primarykey="k1,k2,k3" splittablekey="k1" >
       <entry name="k1" type="int32" notnull="true" />
       <entry name="k2" type="int64" />
       <entry name="k3" type="string" size="64" />
       <entry name="v1" type="int16" />
       <entry name="v2" type="float" />
       <entry name="v3" type="double" defaultvalue="9.900000" />
       <entry name="v4" type="string" size="128" />
       <index name="index_k1_k2" column="k1, k2" />
   </struct>
</metalib>

Refer to TDR Table for the format description of the TDR table description file.

5. Collect Environmental Information

During use, it requires some environment related parameters. See the following table for specific parameters and collection methods.

6. Download Client or SDK

TcaplusDB is compatible with MySQL 5.7 protocol, and can be connected using clients or SDKs that support MySQL 5.7 protocol (including 5.7).

There may be compatibility differences between different versions in actual use. It is recommended to use the Client or SDK with MySQL 5.0 above 5.7 (including 5.7).

After testing, the following versions of the client or SDK can be used normally:

• MySQL client 5.5.24
• mysql-workbench-community-6.2.5
• Python MySQLdb, the underlying layer uses mysql c api version 5.5.24
• JDBC, mysql-connector-java-5.1.49
• .NET mysql driver 8.0.25
• MariaDB C Connector 3.2.5

7. Connect to TcaplusDB

Currently, TcaplusDB only supports mysql_native_password plug-in authentication, which is also the default connection authentication of MySQL 5.7.

7.1 Using MySQL Client to Connect to TcaplusDB

Just like connecting to MySQL, execute the following command and use MySQL-client to connect to TcaplusDB:

mysql -hAccessIp -PAccessPort -uUserName -pUserPassword -DAppID.ZoneID

7.2 Using MySQL Driver to Connect to TcaplusDB

Use .NET 5.0 and MySQL Connector/NET 8.0.25 to demonstrate the process of initializing the connection:

public class Database
{
    static MySqlConnection conn;          // MySql Connection
    const String server = "AccessIp";     // Access Ip
    const String port = "AccessPort";     // Access Port
    const String uid = "UserName";        // User Name
    const String pw = "UserPassword";     // Password
    const String db = "AppID.ZoneID";     // Database Name, spliced from the app ID and partition ID in TcaplusDB

    public static Boolean Init()
    {
        try
        {
            if (conn == null)
            {
                conn = new MySqlConnection("server=" + server + ";port=" + port + ";user id=" + uid + ";password=" + pw + ";database=" + db);
                conn.Open();
                Console.WriteLine("database connected.");
            }
            return true;
        }
        catch (Exception e)
        {
            Console.WriteLine("Exception caught: {0}", e);
            return false;
        }
    }
}

8. Read and Write Data in TcaplusDB

See MySQL protocol compatibility Syntax Description.

9. FAQ

In the process of accessing TcaplusDB, if errors are reported, refer to Meaning and Handling of Error Codes.