Validate a SQL database file

The vip import 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.

VIP-CLI command: 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.
✅ 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

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_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.