Installing WordPress with TiDB

I took a stab at testing TiDB with WordPress today, but since the setup went past the famous five-minute mark, I’ve decided to jot down a note for myself and those who try to use TiDB with WordPress.

Install TiDB

First, you’ll need to install TiDB. Since TiDB is designed to be compatible with the MySQL protocol, it’s a drop-in replacement for your MySQL or MariaDB database.

1. Download the latest TiDB binary from the PingCAP website or use a package manager if available.
2. Extract the binary files into a desired directory.
3. Start the TiDB server along with the required PD (Placement Driver) and TiKV (Key-Value store) components.

Here’s an example of how to start each component (you may need to adjust paths and configuration details):

# Start PD
./bin/pd-server --data-dir=pd --log-file=pd.log &

# Start TiKV
./bin/tikv-server --pd="127.0.0.1:2379" --data-dir=tikv --log-file=tikv.log &

# Start TiDB
./bin/tidb-server --store=tikv --path="127.0.0.1:2379" --log-file=tidb.log &

I’m not sure if those installation steps are correct since they were provided by ChatGPT. However, you can follow the official documentation here: https://docs.pingcap.com/tidb/stable/quick-start-with-tidb

Since I’m a lazy person, I’ve started with TiDB cloud. (AND I think you should do the same)

(If you don’t know how to install TiDB or create an account on the cloud, what the hell are you reading this post?)

Once you log in to your account, you can access the connection details:

Next, we need to create a branch:

Install WordPress

The default configuration for WordPress is not designed to work with TiDB; manual modifications to the wp-config.php file are necessary prior to initiating the installation. (Copy wp-config-sample.php to wp-config.php and make the following adjustments)

1) Define Secure Connections:

define('MYSQL_CLIENT_FLAGS', MYSQLI_CLIENT_SSL);

2) Set DB_COLLATE

define( 'DB_COLLATE', 'utf8mb4_general_ci' );

Here is a screenshot if you want to learn it from the hard way:

utf8mb4_unicode_520_ci is not supported on TiDB.

3) Enter the rest of DB details (obviously)

The remaining installation steps remain unchanged. Simply enter the details for your site and proceed to finalize the installation.

You will see the WordPress tables in the schema:

The usage of deprecated SQL_CALC_FOUND_ROWS

SQL_CALC_FOUND_ROWS has been deprecated as of MySQL 8.0.17, and TiDB doesn’t support it at all. Fortunately, there is an ongoing process to address this, as noted in #47280

Until then, you can use something like https://github.com/it2911/wordpress-tidb-compatibility-plugin/blob/main/tidb-compatibility/tidb-compatibility.php (it doesn’t look like an elegant solution, tho)