Title: Error when importing database backup Last modified: September 25, 2023 --- # Error when importing database backup * Resolved [metricmedia](https://wordpress.org/support/users/metricmedia/) * (@metricmedia) * [2 years, 8 months ago](https://wordpress.org/support/topic/error-when-importing-database-backup/) * I have a site set up to back up the database only. We recently added the addon to allow including non-WordPress tables. * When download a backup from the server and attempt to import it into a new database, I get errors. * When I use phpMyAdmin the error is “Incorrect format parameter”. * When I use Sequel Ace I get “[ERROR in query 13] Invalid default value for ‘user_registered'” * I don’t have issues if I export the site database using the phpMyAdmin installed on the host. * What should I try? Viewing 13 replies - 1 through 13 (of 13 total) * Plugin Contributor [bcrodua](https://wordpress.org/support/users/bcrodua/) * (@bcrodua) * [2 years, 8 months ago](https://wordpress.org/support/topic/error-when-importing-database-backup/#post-17079574) * Hi, * The error message “Invalid default value for ‘user_registered'” suggests that there might be an issue with the table structure. Check the table structure of the exported SQL dump and compare it to the structure of the new database. * Thanks, Bryle * Thread Starter [metricmedia](https://wordpress.org/support/users/metricmedia/) * (@metricmedia) * [2 years, 8 months ago](https://wordpress.org/support/topic/error-when-importing-database-backup/#post-17080924) * I compared the Updraft backup of the table in question with the phpMyAdmin exported version and the table definitions are identical. The phpMyAdmin version imports, the Updraft version does not. * Then I noticed that phpMyAdmin adds this directive to its export: * SET SQL_MODE = “NO_AUTO_VALUE_ON_ZERO”; * I copied that value into the Updraft backup and it now works. * Perhaps you should consider adding this to your backups, since it seems to be the default in the industry standard phpMyAdmin. * Plugin Support [vupdraft](https://wordpress.org/support/users/vupdraft/) * (@vupdraft) * [2 years, 8 months ago](https://wordpress.org/support/topic/error-when-importing-database-backup/#post-17083928) * I have had the error before, you need to increase these two vaules; * upload_max_filesize post_max_size * Please see here for a full guide: [https://jonasmaro.medium.com/how-to-fix-phpmyadmin-error-incorrect-format-parameter-2e665c8b7f09](https://jonasmaro.medium.com/how-to-fix-phpmyadmin-error-incorrect-format-parameter-2e665c8b7f09) * Thread Starter [metricmedia](https://wordpress.org/support/users/metricmedia/) * (@metricmedia) * [2 years, 8 months ago](https://wordpress.org/support/topic/error-when-importing-database-backup/#post-17086942) * Those settings aren’t available in Sequel Ace (formerly something else – the one with the pancakes icon). Sequel Ace reads the file differently than phpMyAdmin and either has enough memory or doesn’t require the same memory as phpMyAdmin. Sequel Ace continues to give “[ERROR in query 13] Invalid default value for ‘ user_registered’” for the Updraft backup and no error for a backup generated from the host’s phpMyAdmin, or from an Updraft backup with SET SQL_MODE = “NO_AUTO_VALUE_ON_ZERO”; added at the top. * Importing the file with phpMyAdmin and the two size limits you mention increased to 200M, I run into a script timeout (uncompressed db is about 130MB). I am able to resume import but unfortunately it fails on resumption with the error “1231– Variable ‘time_zone’ can’t be set to the value of ‘NULL'” when it tries to run: /*! 40103 SET TIME_ZONE=@OLD_TIME_ZONE */; * My guess is a value of OLD_TIME_ZONE was set prior to the timeout and was not retained when continuing the upload. * Note there is no “OLD_TIME_ZONE” in the phpMyAdmin-generated backup of the live site, and this backup imports without difficulty in both Sequel Ace and phpMyAdmin( even with script timeouts/resumptions). This seems to be another place where Updraft differs from what is a very common/standard MySQL database tool. * Are these differences necessary? Could you add an option to disable them (or add, in the case of NO_AUTO_VALUE_ON_ZERO)? * Plugin Support [vupdraft](https://wordpress.org/support/users/vupdraft/) * (@vupdraft) * [2 years, 8 months ago](https://wordpress.org/support/topic/error-when-importing-database-backup/#post-17088707) * I will add a ticket to our internal development board for our developers to look into * Plugin Contributor [Prashant Baldha](https://wordpress.org/support/users/pmbaldha/) * (@pmbaldha) * [2 years, 8 months ago](https://wordpress.org/support/topic/error-when-importing-database-backup/#post-17098153) * Open your PHPMyAdmin backup database and find the below text: * ```wp-block-code -- -- Table structure for table wp_users -- ``` * Give me exact below CREATE SQL query. We need to tweak this query. - This reply was modified 2 years, 8 months ago by [Prashant Baldha](https://wordpress.org/support/users/pmbaldha/). Reason: code block used for code * Thread Starter [metricmedia](https://wordpress.org/support/users/metricmedia/) * (@metricmedia) * [2 years, 8 months ago](https://wordpress.org/support/topic/error-when-importing-database-backup/#post-17099630) * Here it is (wp table prefix is wp_mic_) * `CREATE TABLE wp_mic_users ( ID bigint(20) UNSIGNED NOT NULL,user_login varchar( 60) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',user_pass varchar(255) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',user_nicename varchar(50) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',user_email varchar(100) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',user_url varchar(100) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',user_registered datetime NOT NULL DEFAULT '0000-00-00 00: 00:00',user_activation_key varchar(255) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',user_status int(11) NOT NULL DEFAULT '0',display_name varchar(250) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '') ENGINE=InnoDB DEFAULT CHARSET =utf8mb4 COLLATE=utf8mb4_unicode_520_ci; * Plugin Contributor [Prashant Baldha](https://wordpress.org/support/users/pmbaldha/) * (@pmbaldha) * [2 years, 8 months ago](https://wordpress.org/support/topic/error-when-importing-database-backup/#post-17101394) * You need to open the `.sql` file and put these lines at the top id these aren’t already exists: * ```wp-block-code SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO"; SET time_zone = "+00:00"; ``` * Thread Starter [metricmedia](https://wordpress.org/support/users/metricmedia/) * (@metricmedia) * [2 years, 8 months ago](https://wordpress.org/support/topic/error-when-importing-database-backup/#post-17102918) * Yes, if you read my post up above, I did this and it fixes it. Asking if the plugin can add this value in the backup so we don’t have to. * Plugin Contributor [Prashant Baldha](https://wordpress.org/support/users/pmbaldha/) * (@pmbaldha) * [2 years, 8 months ago](https://wordpress.org/support/topic/error-when-importing-database-backup/#post-17106762) * Can you please give me exact steps to replicate the issue? - This reply was modified 2 years, 8 months ago by [Prashant Baldha](https://wordpress.org/support/users/pmbaldha/). * Plugin Contributor [Prashant Baldha](https://wordpress.org/support/users/pmbaldha/) * (@pmbaldha) * [2 years, 8 months ago](https://wordpress.org/support/topic/error-when-importing-database-backup/#post-17109238) * Are you trying to import a database backup in PhpMyadmin that is created by UpdraftPlus? We are not able to replicate the issue so we are asking * If we know the flow, we can assist you. * Thread Starter [metricmedia](https://wordpress.org/support/users/metricmedia/) * (@metricmedia) * [2 years, 8 months ago](https://wordpress.org/support/topic/error-when-importing-database-backup/#post-17112001) * Per the original post: * > I have a site set up to back up the database only. We recently added the addon > to allow including non-WordPress tables. > When download a backup from the server and attempt to import it into a new > database, I get errors. > When I use phpMyAdmin the error is “Incorrect format parameter”. > When I use Sequel Ace I get “[ERROR in query 13] Invalid default value for ‘ > user_registered’” > I don’t have issues if I export the site database using the phpMyAdmin installed > on the host. * Would you like a copy of the database I’m working with? * I can send a dropbox link or otherwise. I don’t want to share it publicly of course. * Plugin Contributor [Prashant Baldha](https://wordpress.org/support/users/pmbaldha/) * (@pmbaldha) * [2 years, 8 months ago](https://wordpress.org/support/topic/error-when-importing-database-backup/#post-17112675) * Thanks for giving us detailed steps to replicate the issue and we are able to replicate the issue. Viewing 13 replies - 1 through 13 (of 13 total) The topic ‘Error when importing database backup’ is closed to new replies. * ![](https://ps.w.org/updraftplus/assets/icon-256x256.jpg?rev=1686200) * [UpdraftPlus: WP Backup & Migration Plugin](https://wordpress.org/plugins/updraftplus/) * [Frequently Asked Questions](https://wordpress.org/plugins/updraftplus/#faq) * [Support Threads](https://wordpress.org/support/plugin/updraftplus/) * [Active Topics](https://wordpress.org/support/plugin/updraftplus/active/) * [Unresolved Topics](https://wordpress.org/support/plugin/updraftplus/unresolved/) * [Reviews](https://wordpress.org/support/plugin/updraftplus/reviews/) * 13 replies * 4 participants * Last reply from: [Prashant Baldha](https://wordpress.org/support/users/pmbaldha/) * Last activity: [2 years, 8 months ago](https://wordpress.org/support/topic/error-when-importing-database-backup/#post-17112675) * Status: resolved