Support for exporting FULLTEXT indices

[jcalonso]

Just noticed that all my full-text indices are missing after exporting a database. I don't see any mention to full-text indices in the codebase/issues/comments so I guess is not supported yet?

Edit: The indices are in the export but are normal indices:

$table->index(['col1', 'col2', 'col3', 'col4'], 'fullnamesearch');

[cviebrock]

Quite possibly this is a missing feature. Would you mind pasting a dump of your database (structure only, not data) ... i.e. what mysqldump would output so I can test?

[jcalonso]

Sure! :)

CREATE TABLE `users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(45) COLLATE utf8_unicode_ci NOT NULL,
  `middle_name` varchar(45) COLLATE utf8_unicode_ci NOT NULL,
  `last_name` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  `email` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  FULLTEXT KEY `fullnamesearch` (`first_name`,`middle_name`,`last_name`,`email`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Output:

<?php

use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

/**
 * Migration auto-generated by Sequel Pro Laravel Export
 * @see https://github.com/cviebrock/sequel-pro-laravel-export
 */
class CreateUsersTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('users', function (Blueprint $table) {
            $table->increments('id');
            $table->string('first_name', 45);
            $table->string('middle_name', 45);
            $table->string('last_name', 45)->nullable();
            $table->string('email', 255)->nullable();

            $table->index(['first_name', 'middle_name', 'last_name', 'email'], 'fullnamesearch');

        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('users');
    }
}

[cviebrock]

Can you try the 1.4.0 release (or the lastest version of master) and see if that does it for you?

[jcalonso]

Hi @cviebrock thanks for the quick update! I just tested the last version and found 2 things:

1) There is a problem with the syntax used to define the columns, the export created something like:

\DB::statement("ALTER TABLE users ADD FULLTEXT INDEX fullnamesearch (['first_name', 'middle_name', 'last_name', 'email'])");

 [PDOException]
  SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '['first_name', 'middle_name', 'last_name', 'email'])' at line 1

Which should be (no array):

\DB::statement("ALTER TABLE users ADD FULLTEXT INDEX fullnamesearch (`first_name`, `middle_name`, `last_name`, `email`)");

2) The index statement should be outside the Schema::create closure: (Since its a raw sql I Imagine laravel run it as soon as it appears in the code, before the actual table is created)

  SQLSTATE[42S02]: Base table or view not found: 1146 Table 'tests.users' doesn't exist

It should be something like:

 Schema::create('users', function (Blueprint $table) {
            $table->increments('id');
            $table->string('first_name', 45);
            $table->string('middle_name', 45);
            $table->string('last_name', 45)->nullable();
            $table->string('email', 255)->nullable();
        });
\DB::statement("ALTER TABLE users ADD FULLTEXT INDEX fullnamesearch (`first_name`, `middle_name`, `last_name`, `email`)");

Let me know if you need more info, thanks :)

[cviebrock]

My bad! Try the latest 1.4.1 release.

[jcalonso]

It works! thanks for this @cviebrock!