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 import validate-sql <path/to/file.sql>
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:
SET UNIQUE_CHECKS = 0
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'!
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 03, 2023