CODEWITHSUNDEEP
mysqlinnodbmyisam
alancc
alancc

Reputation: 799

Cannot convert MySQL Table from MyISAM to InnoDB

I am trying to convert a MySQL Table from MyISAM to InnoDB.

I use the following command:

ALTER TABLE `wp_wpr_rucss_used_css` ENGINE=InnoDB

But get the following error:

#1067 - Invalid default value for 'modified'

Why? How to solve it?

Update

I run the following command

SHOW CREATE TABLE `wp_wpr_rucss_used_css`

and

get the following result:

CREATE TABLE `wp_wpr_rucss_used_css` (
 `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 `url` varchar(2000) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
 `css` longtext COLLATE utf8mb4_unicode_520_ci,
 `unprocessedcss` longtext COLLATE utf8mb4_unicode_520_ci NOT NULL,
 `retries` tinyint(1) NOT NULL DEFAULT '1',
 `is_mobile` tinyint(1) NOT NULL DEFAULT '0',
 `modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
 `last_accessed` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
 PRIMARY KEY (`id`),
 KEY `url` (`url`(150),`is_mobile`),
 KEY `modified` (`modified`),
 KEY `last_accessed` (`last_accessed`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci

Upvotes: 0

Views: 252

Answers (1)

ROHIT KHURANA
ROHIT KHURANA

Reputation: 983

Seems you have 0000-00-00 00:00:00 as the default value So generally, The problem is because of sql_modes. Please check your current sql_modes by command:

show variables like 'sql_mode';

remove the sql_mode "NO_ZERO_IN_DATE,NO_ZERO_DATE" if you have. Then run your alter command.

Upvotes: 4

Related Questions