jrSearch errors in Activity Log

Melih
Melih
@melih
7 years ago
198 posts
Hello guys,

I am seeing these errors in the Activitiy Log:
jrCore_db_verify_table() fulltext index on jr_jrsearch_fulltext requires MyISAM table type!

I tried to change that table's type to MyISAM but integrity check changes back:
changed MySQL engine type to InnoDB for table: jr_jrsearch_fulltext
I delete the Search module and reinstall but no luck.
I don't know what to do, any ideas?
updated by @melih: 08/05/17 03:23:09AM
michael
@michael
7 years ago
7,697 posts
the current version of the jrSearch module has this as its schema.php file
/**
 * db_schema
 */
function jrSearch_db_schema(){
.............

    // Full text search
    $_tmp = array(
        "s_module VARCHAR(64) NOT NULL DEFAULT ''",
        "s_id INT(11) UNSIGNED NOT NULL DEFAULT '0'",
        "s_mod TINYINT(1) UNSIGNED NOT NULL DEFAULT '1'",
        "s_text TEXT NOT NULL",
        "UNIQUE s_unique (s_module, s_id, s_mod)",
        "FULLTEXT s_text (s_text)"
    );

    // NOTE: MySQL 5.6+ and MariaDB can use InnoDB
    $_db = jrCore_db_query("SHOW VARIABLES WHERE Variable_name = 'version'", 'SINGLE');
    if ($_db && is_array($_db) && isset($_db['Value'])) {
        $ver = $_db['Value'];
    }
    else {
        $msi = jrCore_db_connect();
        $ver = mysqli_get_server_info($msi);
    }
    if (strpos($ver, '-')) {
        list($ver,) = explode('-', $ver);
    }
    $engine = 'MyISAM';
    if ($ver && $ver > 5.5) {
        $engine = 'InnoDB';
    }
    jrCore_db_verify_table('jrSearch', 'fulltext', $_tmp, $engine);
.........
}

So it seams like the issue you're hitting is taken into account on the current repo version. Probably that hasn't been released in your version.

Solution is:
* wait for the next release OR
* add that code to your jrSearch/schema.php OR
* upgrade your database version to MySQL 5.6+
Melih
Melih
@melih
7 years ago
198 posts
I am using jrSearch 1.6.5. I began to see those errors after update the Search module. And ıt's shema.php is
/**
 * db_schema
 */
function jrSearch_db_schema(){
    // Popular searches DS
    jrCore_db_create_datastore('jrSearch', 'search');

    // Full text search
    $_tmp = array(
        "s_module VARCHAR(64) NOT NULL DEFAULT ''",
        "s_id INT(11) UNSIGNED NOT NULL DEFAULT '0'",
        "s_mod TINYINT(1) UNSIGNED NOT NULL DEFAULT '1'",
        "s_text TEXT NOT NULL",
        "UNIQUE s_unique (s_module, s_id, s_mod)",
        "FULLTEXT s_text (s_text)"
    );

    // NOTE: MySQL 5.6+ and MariaDB can use InnoDB
    $_db = jrCore_db_query("SHOW VARIABLES WHERE Variable_name = 'version'", 'SINGLE');
    if ($_db && is_array($_db) && isset($_db['Value'])) {
        $ver = $_db['Value'];
    }
    else {
        $msi = jrCore_db_connect();
        $ver = mysqli_get_server_info($msi);
    }
    if (strpos($ver, '-')) {
        list($ver,) = explode('-', $ver);
    }
    $engine = 'MyISAM';
    if ($ver && $ver > 5.5) {
        $engine = 'InnoDB';
    }
    jrCore_db_verify_table('jrSearch', 'fulltext', $_tmp, $engine);
    return true;
}
I couldn't find any difference

MySQL is v. 5.6.34

So i think i need to wait the next release

Thanks Michael.
michael
@michael
7 years ago
7,697 posts
Im using:
mysql  Ver 15.1 Distrib 10.1.22-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2
and am unable to reproduce the issue.

1.6.5 is where I got the code from.

Checking here:
https://blogs.oracle.com/mysqlinnodb/entry/innodb_full_text_search_is

It looks like you're JUST below the cut-off point.
Quote:
InnoDB Full-Text Search is in MySQL 5.6.4
.........

But its also written in 2011. any chance of upgrading mysql?
Melih
Melih
@melih
7 years ago
198 posts
Ok, these things are beyond my knowladge actually.

I checked with command mysql -V:
mysql  Ver 14.14 Distrib 5.5.43, for debian-linux-gnu (x86_64) using readline 6.2

I have a friend who knows these thing better than me :) I will ask him about mysql version and upgrade it
brian
@brian
7 years ago
10,144 posts
This should be fixed in the latest Search 1.6.6 module - sorry about that.


--
Brian Johnson
Founder and Lead Developer - Jamroom
https://www.jamroom.net
Melih
Melih
@melih
7 years ago
198 posts
Sorry to say that but i'm still seeing the same error after update
brian
@brian
7 years ago
10,144 posts
Melih:
Sorry to say that but i'm still seeing the same error after update

Hmm.. tested here on MySQL 5.5.55, 5.7 and MariaDB 10.1 and am not seeing any problems. Are you sure the error is still coming through? Delete the debug log and try an integrity check. If it is still erroring I would have to get access to the system to see what is unique about your MySQL version.


--
Brian Johnson
Founder and Lead Developer - Jamroom
https://www.jamroom.net
Melih
Melih
@melih
7 years ago
198 posts
I did them all. Even i delete the jrsearch module and db tables and install again.

Let me try with my demo site
Melih
Melih
@melih
7 years ago
198 posts
Unfortunately i am getting same error with a fresh install. I will send an email to you Brian.
brian
@brian
7 years ago
10,144 posts
Melih:
Unfortunately i am getting same error with a fresh install. I will send an email to you Brian.

OK - will be watching for your email.

Thanks!


--
Brian Johnson
Founder and Lead Developer - Jamroom
https://www.jamroom.net
brian
@brian
7 years ago
10,144 posts
This is weird - your MySQL is version 5.6.34, which should support Full Text for InnoDB:

https://dev.mysql.com/doc/refman/5.6/en/fulltext-restrictions.html

So my guess is that for some unknown reason it has been disabled on your server. I will see if there is a way to change the detection check to be based on querying the DB versus a version.


--
Brian Johnson
Founder and Lead Developer - Jamroom
https://www.jamroom.net
Melih
Melih
@melih
7 years ago
198 posts
I will get in touch with my hosting company to see they can do anything about this.
Thank you so much Brian
brian
@brian
7 years ago
10,144 posts
Melih:
I will get in touch with my hosting company to see they can do anything about this.
Thank you so much Brian

Yep - no problem. I'm hoping to find a way where it can test for the support versus just checking the version numbers (which is what it does now), so hopefully that can be worked around.


--
Brian Johnson
Founder and Lead Developer - Jamroom
https://www.jamroom.net
brian
@brian
7 years ago
10,144 posts
So just to add, there is an InnoDB variable:

https://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_optimize_fulltext_only

That we can check - if it exists, it means InnoDB supports a FULLTEXT index. What's weird is that your server DOES have this variable, which tells me the support has been disabled for some reason.


--
Brian Johnson
Founder and Lead Developer - Jamroom
https://www.jamroom.net

Tags