Nikhil Shinde
Nikhil Shinde

Reputation: 21

How to fix 1709 - Index column size too large. The maximum column size is 767 bytes. in mysql in XAMPP

I'm importing a new table as Users, into my database but it is showing an error

1709 - Index column size too large. The maximum column size is 767 bytes.

I am having Server version: 10.1.16-MariaDB PHP version: 7.2.14 phpmyAdmin Version : Version information: 4.8.4

DROP TABLE IF EXISTS `users`;
CREATE TABLE IF NOT EXISTS `users` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `first_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `last_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `username` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `email` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `email_show` tinyint(1) NOT NULL DEFAULT '0',
  `email_confirmed` tinyint(1) NOT NULL DEFAULT '0',
  `iso2` varchar(5) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `phone` varchar(16) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `dob` date DEFAULT NULL,
  `gender` tinyint(1) NOT NULL DEFAULT '0' COMMENT '0=> Not Specified,1=> Male, 2=> Female',
  `phone_show` tinyint(1) NOT NULL DEFAULT '0',
  `phone_confirmed` tinyint(1) NOT NULL DEFAULT '0',
  `country_id` int(10) UNSIGNED DEFAULT NULL,
  `state_id` int(10) UNSIGNED DEFAULT NULL,
  `city_id` int(11) DEFAULT NULL,
  `confirmation_code` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_by` int(10) UNSIGNED DEFAULT NULL,
  `updated_by` int(10) UNSIGNED DEFAULT NULL,
  `password` varchar(60) COLLATE utf8mb4_unicode_ci NOT NULL,
  `options` text COLLATE utf8mb4_unicode_ci,
  `confirmed` tinyint(1) NOT NULL DEFAULT '0',
  `active` tinyint(1) NOT NULL DEFAULT '0',
  `phone_code` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `provider` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `provider_id` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `remember_token` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `company_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `portfolio` text COLLATE utf8mb4_unicode_ci,
  `opening_time` time DEFAULT NULL,
  `closing_time` time DEFAULT NULL,
  `address` text COLLATE utf8mb4_unicode_ci,
  `education` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `notary_number` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `monday_opening_time` time DEFAULT NULL,
  `monday_closing_time` time DEFAULT NULL,
  `thuesday_opening_time` time DEFAULT NULL,
  `thuesday_closing_time` time DEFAULT NULL,
  `wednesday_opening_time` time DEFAULT NULL,
  `wednesday_closing_time` time DEFAULT NULL,
  `thursday_opening_time` time DEFAULT NULL,
  `thursday_closing_time` time DEFAULT NULL,
  `friday_opening_time` time DEFAULT NULL,
  `friday_closing_time` time DEFAULT NULL,
  `saturday_opening_time` time DEFAULT NULL,
  `saturday_closing_time` time DEFAULT NULL,
  `sunday_opening_time` time DEFAULT NULL,
  `sunday_closing_time` time DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `users_email_unique` (`email`),
  KEY `users_country_id_index` (`country_id`),
  KEY `users_area_id_index` (`state_id`),
  KEY `users_created_by_index` (`created_by`),
  KEY `users_updated_by_index` (`updated_by`)
) ENGINE=InnoDB AUTO_INCREMENT=33 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

MySQL said:

1709 - Index column size too large. The maximum column size is 767 bytes.

Upvotes: 2

Views: 12415

Answers (1)

Carlos GR
Carlos GR

Reputation: 23

In the last line of the creation table code try changing this:

ENGINE=InnoDB AUTO_INCREMENT=33 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

To this:

ENGINE=InnoDB AUTO_INCREMENT=33 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COLLATE=utf8mb4_unicode_ci;

Upvotes: 1

Related Questions