How to deploy MySQL database from v5.7.19 to a remote MySQL database of v5.5.6

Question

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 to utf8, and
• from utf8mb4_unicode_ci to utf8_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_KEYs, FOREIGN KEYS and INDEXes to a v5.5 MySQL database keeping the data intact?

Answer 1

Change your key names. You can overwrite the "default generated" very long key names when you create them. See https://laravel.com/docs/5.8/migrations Available index types for the documentation

I ran in a similar issue when migrating from SQL server to MySQL and the autogenerated key names that had full long namespaces and key names were simply too long. So by replacing those all by hand crafted unique index names I got around those problems.

You don't really need unique names in MySQL, but if you use SQLITE for unit tests you do need unique names.

so instead of:

public function up() 
{
   ....
   $table->primary('id');
  // generates something like work_mayeenul_islam_workhorse_models_model_name_id_primary_key
   $table->index(['foobar','bazbal']);
   // generates something like work_mayeenul_islam_workhorse_models_model_name_foobar_bazbal_index
}

You use your own defined, you know these to be short index names.

public function up() 
{
   ....
   $table->primary('id', 'PK_short_namespace_modelname_id');
   $table->index(['foobar', 'bazbal'], 'IX_short_namespace_modelname_foobar_bazbal');
}

Answer 2

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 than 191. But,
• with collation utf8_unicode_ci in MySQL 5.5/5.6 the field value cannot be greater than 255. But with utf8_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 to utf8_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.