Compatibility testing with your database

This document describes ideas for testing the compatibility of your database and queries with PlanetScaleDB using your database link.

Overview

Because PlanetScaleDB uses Vitess, which supports most but not all MySQL queries, migrating a database to PlanetScaleDB may involve changing some queries. One approach to testing for compatibiility issues is to create a database link for your external database. Linking an external database to your PlanetScaleDB account allows you to test how PlanetScaleDB will behave with your database. PlanetScaleDB will create a database in your cluster, called a database link, that sends its queries to your external database. This allows you to test the compatibility of your database with PlanetScaleDB.

Link a testing or staging environment database

Database Links do not make changes to your database, other than those resulting from queries you send. However, using a staging or development database may give you greater flexibility to test or change queries.

Connect a command line MySQL client to the Database Link.

One way to test out query compatibility is by connecting to your database link using the MySQL command line interface. This allows you to manually test how PlanetScaleDB behaves in response to specific queries, and provides real-time error messages.

For example, you can copy the connection string from your Database Link and past it into a terminal to connect to your database, substituting your user credentials:

mysql --host ae4614fa2b48f59c4b60fb7d28cf11cb-2011681225.us-west-2.elb.amazonaws.com --port 3306 -p --user admin
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 50
Server version: 5.7.9-Vitess MySQL Community Server (GPL)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

From the MySQL command line, you can issue queries to test their compatibility with PlanetScaleDB:

mysql> SELECT * FROM users;
+--------+-----------+
| name   | role      |
+--------+-----------+
| Teddy  | President |
| Lyndon | President |
+--------+-----------+
2 rows in set (0.09 sec)

If PlanetScaleDB does not support a query, you should receive an error message from the VTGate component:

mysql> SELECT * FROM users NATURAL JOIN users2;
ERROR 1105 (HY000): vtgate: http://isuyu-testing2-awsuswest2c1-vtgate-dc3ec064-7df4758dcc-kw6s6:15000/: unsupported: natural join

See the Vitess documentation for a list of specific example queries that are not supported.

Connect your application to the Database Link.

Connecting your application to the Database Link can simulate the behavior of PlanetScaleDB in response to the actual queries your application issues. This may require reconfiguring your application to use the connection string from your Database Link. Attempting to use your application while connected to your Database Link should reproduce any errors that are likely to occur after migration.

You may have a log that captures any errors from MySQL. If not, you can access the query logs for your Database Link inside PlanetScaleDB.

Identify compatibility-related errors

If you encounter errors, you can compare them against known limitations in PlanetScaleDB compatibility with MySQL. If errors arise from compatibility limitations, it may be possible to rewrite queries to make your application compatible with PlanetScaleDB.

Next Steps

See also