Validate a SQL database file

The vip import validate-sql VIP-CLI command should be run against a SQL database file—and all reported errors resolved—before importing the file to a VIP Platform environment.

The validate-sql command scans the format of a SQL database file for errors and potential incompatibilities with VIP Platform databases.

An identical validation scan is run against files during import with the vip import sql VIP-CLI command. Performing the validation scan against a SQL database file prior to import streamlines the import process.

VIP-CLI command: vip import validate-sql <path/to/file.sql>

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.
✅ ALTER USER statement was found 0 times.
✅ DROP TABLE was found 1 times.
✅ CREATE TABLE was found 1 times.
 - wp_ prefix tables found: 1
✅ ALTER TABLE statement was found 0 times.
✅ SET UNIQUE_CHECKS = 0 was found 0 times. 
'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:

• ALTER TABLE
• ALTER USER
• DROP DATABASE
• SET @@SESSION.sql_log_bin
• SET PASSWORD
• SET UNIQUE_CHECKS = 0
• TRIGGER

SQL validation errors

The validate-sql command identifies and reports specific SQL validation errors and some potential incompatibilities. Resolving the SQL validation errors that are reported by the validate-sql command increases the likelihood of a successful outcome for a database import. However, results of the validation scan are unable to determine if the SQL will execute successfully after import.

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.

If the issues reported by the validate-sql command are not resolved, those issues will also be reported during the database import process 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_table4
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.