Skip to content

Validate a SQL database file

The validate-sql VIP-CLI command scans a SQL database file for errors and potential incompatibilities with VIP Platform databases. The validate-sql command should be run against a SQL database file—and all reported errors resolved—before importing the file to a VIP Platform environment.

Prerequisites

VIP-CLI is installed and has been updated to the most current version.

vip import validate-sql <path/to/file.sql>

The validate-sql command identifies and reports specific SQL validation errors and incompatibilities. In order to resolve the errors reported in the command output, a new file should be created with modifications made to the export method in order to resolve each specific issue.

In this example command output, the validate-sql command is run against a file named file.sql, and no errors are found:

$ vip import validate-sql file.sql 
Finished processing 15112 lines.

✅ SET @@SESSION.sql_log_bin statement was found 0 times.
✅ TRIGGER statement was found 0 times.
✅ DROP DATABASE statement was found 0 times.
✅ USE <DATABASE_NAME> statement was found 0 times.
✅ ALTER USER statement was found 0 times.
✅ DROP TABLE was found 1 times.
✅ CREATE TABLE was found 1 times.
 - wp_ prefix tables found: 1 
'siteurl','https://example.com'
✅ ENGINE != InnoDB was found 0 times.

Disallowed SQL statements

The presence of any of the following SQL statements will cause a file validation to fail:

  • SET @@SESSION.sql_log_bin
  • TRIGGER
  • DROP DATABASE
  • USE
  • ALTER USER
  • SET PASSWORD

SQL validation errors

SQL validation errors and incompatibilities that are reported by the validate-sql command are intended to ensure a successful outcome for a database import. By default, the vip import sql VIP-CLI command used to import databases runs an identical validation script against files for import. If the issues reported by the validate-sql command are not resolved, those issues will also be reported during a vip import sql VIP-CLI command and cause import to fail.

DROP TABLE was not found and CREATE TABLE was not found

SQL Error: DROP TABLE was not found.
Recommendation: Check import settings to include DROP TABLE statements
SQL Error: CREATE TABLE was not found.
Recommendation: Check import settings to include CREATE TABLE statements

DROP TABLE and CREATE TABLE statements are required in order for a database import to safely override tables that already exist in the destination database. DROP TABLE and CREATE TABLE statements typically occur in pairs, and the validation command will report errors if an unequal quantity of these statements appear in the file, or if either of the statements are missing entirely.

An example of paired DROP TABLE and CREATE TABLE statements as they might appear in a SQL database file:

DROP TABLE IF EXISTS wp_table_name;
CREATE TABLE IF NOT EXISTS `wp_table_name` (

ENGINE != InnoDB

SQL Error: ENGINE != InnoDB on line(s) 23001.
Recommendation: Ensure your application works with InnoDB and update your SQL dump to include only 'ENGINE=InnoDB' engine definitions in 'CREATE TABLE' statements. We suggest you search for all 'ENGINE=X' entries and replace them with 'ENGINE=InnoDB'!

If ENGINE is set to any value other than InnoDB the file will fail validation. InnoDB has many advantages including high reliability, high performance, and compatibility with VIP Platform databases.

tables without wp_ prefix found

SQL Error: tables without wp_ prefix found: xy_table1, xy_table2, xy_table3, xy_table 4
Recommendation: Please make sure all table names are prefixed with `wp_`

To prevent unexpected issues, all tables in a WordPress database should have a wp_ prefix. Use caution if adding custom tables without a wp_ prefix to a VIP Platform environment’s database.

Last updated: August 15, 2022