Error when importing database backup

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

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.

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

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)?

I will add a ticket to our internal development board for our developers to look into

Open your PHPMyAdmin backup database and find the below text:

--

-- Table structure for table wp_users

--

Give me exact below CREATE SQL query. We need to tweak this query.

• This reply was modified 1 year, 5 months ago by Prashant Baldha. Reason: code block used for code

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;

You need to open the?.sql?file and put these lines at the top id these aren’t already exists:

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO"; 
SET time_zone = "+00:00";

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.

Can you please give me exact steps to replicate the issue?

• This reply was modified 1 year, 5 months ago by Prashant Baldha.

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.

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.

Thanks for giving us detailed steps to replicate the issue and we are able to replicate the issue.