Issue
It's actually my fault that I did not think about it earlier that, my remote server MySQL version (on shared hosting) is 5.5.6
, but my local MySQL version is 5.7.19
.
I developed a Laravel (v6.6.0
) Web Application, where I ran the migration on the very first run, but as it's completely a personal project, I continued modifying the database by hand where and how necessary, (but off-the-record, I kept changing the migration files as well though I never ran them after the first instance).
I migrated all the data from some other tables and my application was ready to deploy. But when I was exporting the local database tables, and importing them to the remote database, it's giving me a well-known error:
Specified key was too long; max key length is 767 bytes
I actually ignored it because all the tables were imported nicely. But recently I found its caveats - all the AUTO_INCREAMENT
and PRIMARY_KEY
are not present on my remote database.
I searched what I could, but all the solutions are suggesting to delete the database and create it again with UTF-8 actually could not be my case. And a solution like the following PHP-way is also not my case as I'm using PHPMyAdmin to Import my table while I'm getting the error:
// File: app/Providers/AppServiceProvider.php
use Illuminate\Support\Facades\Schema;
public function boot()
{
Schema::defaultStringLength(191);
}
I also tried running the following command on my target database:
SET @global.innodb_large_prefix = 1;
But no luck. I also tried replacing all the occurrences of my .sql
local file:
- from
utf8mb4
toutf8
, and - from
utf8mb4_unicode_ci
toutf8_general_ci
but found no luck again.
From where the error specifically is coming from, actually the longer foreign keys, like xy_section_books_price_unit_id_foreign
, and at this stage when everything is done, I don't know how can I refactor all the foreign keys to 5.5
compatible.
Can anybody please shed some light on my issue?
How can I deploy my local database (v5.7
) without losing my PRIMARY_KEY
s, FOREIGN KEYS and INDEX
es to a v5.5
MySQL database keeping the data intact?
Solution
Thank you @Tschallacka for your answer. My problem was, I cannot run php artisan migrate
anymore because I've live data on those tables. First of all, the issue let me learn newer things (Thanks to my colleague Nazmul Hasan):
Lesson Learnt
Keys are unique but could even be gibberish
First, I found a pattern in the foreign keys: {table_name}_{column_name}_foreign
. Similarly in index keys: {table_name}_{column_name}_index
. Lesson learned that the foreign key or index key doesn't have to be in such a format to make work. It has to be unique, but it can be anything and could be gibberish too. So password_resets_email_index
key can easily be pre_idx
or anything else.
But that was not the issue.
Solution
For the solution, I tried digging the .sql
file table by table and scope by scope. And I found only 2 of the UNIQUE
key declaration was showing blocking error. And there were 3 other occasions where there were warnings:
ALTER TABLE `contents` ADD KEY `contents_slug_index` (`slug`); --- throwing warning
ALTER TABLE `foo_bar` ADD UNIQUE KEY `slug` (`slug`); --- throwing error
ALTER TABLE `foo_bar` ADD KEY `the_title_index` (`title`) USING BTREE; --- throwing warning
ALTER TABLE `password_resets` ADD KEY `password_resets_email_index` (`email`); --- throwing waring
ALTER TABLE `users` ADD UNIQUE KEY `users_email_unique` (`email`); --- throwing error
Finally, the solution came from this particular StackOverflow thread:
- INNODB
utf8
VARCHAR(255) - INNODB
utf8mb4
VARCHAR(191)
With inspection on those table with the knowledge of that SO thread, I found:
- The issue is: with collation
utf8mb4_unicode_ci
in MySQL 5.5/5.6 the field value cannot be greater than191
. But, - with collation
utf8_unicode_ci
in MySQL 5.5/5.6 the field value cannot be greater than255
. But withutf8_unicode_ci
you cannot save emoji etc.
So I decided to stay with the utf8_unicode_ci
for a comparatively longer value. So for a temporary remedy:
- I changed all those particular columns, I changed from
utf8mb4_unicode_ci
toutf8_unicode_ci
- If those particular columns exceed 255, I reduced them to 255
So for example, if the table is like below:
CREATE TABLE `foo_bar` (
`id` bigint(20) UNSIGNED NOT NULL,
`cover` varchar(500) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`title` varchar(300) COLLATE utf8mb4_unicode_ci NOT NULL,
`slug` varchar(300) COLLATE utf8mb4_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
I changed only the necessary columns:
CREATE TABLE `foo_bar` (
`id` bigint(20) UNSIGNED NOT NULL,
`cover` varchar(500) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`title` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`slug` varchar(255) COLLATE utf8_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
And that's it. This temporary remedy is working just fine, and I didn't have to change the foreign key or index key.
Why this temporary remedy? Because eventually I'll go with MySQL 5.7+, but before that, at least try to cope with the previous versions.
Answered By - Mayeenul Islam Answer Checked By - David Goodson (PHPFixing Volunteer)
0 Comments:
Post a Comment
Note: Only a member of this blog may post a comment.