2

I'm trying my first BigDump import of a large db file but I get the following error:

Error at the line 52: ) ENGINE=MyISAM AUTO_INCREMENT=8245 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Query:

CREATE TABLE `wp_actionscheduler_actions` (
`action_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`hook` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
`status` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
`scheduled_date_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`scheduled_date_local` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`args` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`schedule` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`group_id` bigint(20) unsigned NOT NULL DEFAULT 0,
`attempts` int(11) NOT NULL DEFAULT 0,
`last_attempt_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`last_attempt_local` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`claim_id` bigint(20) unsigned NOT NULL DEFAULT 0,
`extended_args` varchar(8000) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
PRIMARY KEY (`action_id`),
KEY `hook` (`hook`),
KEY `status` (`status`),
KEY `scheduled_date_gmt` (`scheduled_date_gmt`),
KEY `args` (`args`),
KEY `group_id` (`group_id`),
KEY `last_attempt_gmt` (`last_attempt_gmt`),
KEY `claim_id` (`claim_id`)
) ENGINE=MyISAM AUTO_INCREMENT=8245 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

MySQL: Invalid default value for 'scheduled_date_gmt'

The import is from the hosting server to localhost and I wonder if the differences between the two installetion could play a role.

Localserver information: Server: Localhost via UNIX socket Server type: MySQL Server version: 5.7.30-0ubuntu0.18.04.1 - (Ubuntu) Protocol version: 10 user: xxxxxxxxx Server charset: UTF-8 Unicode (utf8) Web server: Apache/2.4.29 (Ubuntu) Database Client version: libmysql - mysqlnd 5.0.12-dev - 20150407 - $Id: 3591daad22de08524295e1bd073aceeff11e6579 $ PHP extension: mysqliDocumentazione mbstringDocumentazione VPHP version: 7.2.31-1+ubuntu18.04.1+deb.sury.org+1

Hosting server information Server: Localhost via UNIX socket Server type: MariaDB Server connection: SSL inattivo Documentazione Server version: 10.3.28-MariaDB-cll-lve - MariaDB Server Protocol version: 10 user: xxxxxxx Server charset: cp1252 West European (latin1) Web server: cpsrvd 11.94.0.8 Database Client version: libmysql - 5.6.43 PHP extension: mysqliDocumentazione curlDocumentazione mbstringDocumentazione PHP version: 7.3.27

I'm really really new to all this stuff, thanks if someone will drop a reply.

Dharman
  • 30,962
  • 25
  • 85
  • 135
franc
  • 21
  • 1
  • Check the sql_mode, see e.g. [Error in MySQL when setting default value for DATE or DATETIME](https://stackoverflow.com/q/36374335) – Solarflare May 09 '21 at 16:57

1 Answers1

1

you can't have '0000-00-00 00:00:00' as default value for datetime columns , this is not a valid datetime.

from Mysql Doc:

The DATETIME type is used for values that contain both date and time parts. MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD hh:mm:ss' format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.

otherwise , it works fine.

db<>fiddle here

eshirvana
  • 23,227
  • 3
  • 22
  • 38