Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

PHP Fatal error "Duplicate key name" - missing existence-check for key 'meta_value' #91

Open
gnanet opened this issue Oct 1, 2024 · 6 comments
Assignees
Labels
bug Something isn't working more info needed Further information is requested

Comments

@gnanet
Copy link

gnanet commented Oct 1, 2024

this command (amngst ohers too) resulting in a "Duplicate key name" error.

wp index-mysql enable wp_postmeta

Versions
Plugin:1.5.2
MySQL: 10.3.39-MariaDB-0ubuntu0.20.04.2
WordPress: 6.6.2
WordPress database: 57155
php: 8.1.30

Duplicate key name 'meta_value' error thrown

ALTER TABLE `wp_postmeta` 
    ADD UNIQUE KEY meta_id (meta_id), 
    DROP PRIMARY KEY,
    ADD PRIMARY KEY (post_id, meta_key, meta_id),
    DROP KEY meta_key,
    ADD KEY meta_key (meta_key, meta_value(32), post_id, meta_id),
    ADD KEY meta_value (meta_value(32), meta_id),
    DROP KEY post_id

No wonder, wp_postmeta contains indeed FULLTEXT key named 'meta_value':

SHOW CREATE TABLE `wp_postmeta`;

CREATE TABLE `wp_postmeta` (
 `meta_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 `post_id` bigint(20) unsigned NOT NULL DEFAULT 0,
 `meta_key` varchar(255) DEFAULT NULL,
 `meta_value` longtext DEFAULT NULL,
 PRIMARY KEY (`meta_id`),
 KEY `post_id` (`post_id`),
 KEY `meta_key` (`meta_key`(191)),
 FULLTEXT KEY `meta_value` (`meta_value`)
) ENGINE=InnoDB AUTO_INCREMENT=<REDACTED> DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci

A further search in other wp installations resulted in all sites containing FULLTEXT key meta_value in '...meta' tables.

I remember i searched for such FT indexes while doing the myisam to innodb conversion, but seems it was not required to remove them: according to this article i could keep FULLTEXT while converting to innodb, if mysql version is above 5.5, which indeed was the case.

So for the issue being fixed, i would sugest to check for the existence of key names, which should be ADD-ed, and if the key exists, but not the desired declaration,it should also DROP-ed before.

That way a "duplicate key error" could be avoided.

@OllieJones OllieJones self-assigned this Oct 4, 2024
@OllieJones OllieJones added bug Something isn't working more info needed Further information is requested labels Oct 4, 2024
@OllieJones
Copy link
Owner

OllieJones commented Oct 4, 2024

This is indeed a defect. We should avoid trying to overwrite existing indexes.

I wonder, though, what WordPress code uses MATCH() AGAINST() querying on the postmeta table. It isn't natural language text and probably will yield strange results. Are you using a particular plugin? (Notice that FULLTEXT indexes aren't exploited except by MATCH() AGAINST() queries. Core contains none of those. Only a couple of plugins known to this author do contain them, and none on wp_*whatever*meta tables.

You could work around this problem by doing this query instead of the one we use for high-performance keys. This changes the name of the FULLTEXT key.

ALTER TABLE `wp_postmeta` 
    ADD UNIQUE KEY meta_id (meta_id), 
    DROP PRIMARY KEY,
    ADD PRIMARY KEY (post_id, meta_key, meta_id),
    DROP KEY meta_key,
    ADD KEY meta_key (meta_key, meta_value(32), post_id, meta_id),
    DROP KEY meta_value,
    ADD KEY meta_value (meta_value(32), meta_id),
    ADD FULLTEXT KEY meta_value_ft (meta_value), 
    DROP KEY post_id

@gnanet
Copy link
Author

gnanet commented Oct 4, 2024

I am also wondering what added that fulltext index, if you have time idid the diagnostic upload, the id is this: PCXPu8HB

I did not choose the theme + framework ( which is divi) but i have a feeling that the FT index was created by them.

@OllieJones
Copy link
Owner

I have uploads from some other Divi sites and they don't have this index. Strange stuff.

@gnanet
Copy link
Author

gnanet commented Oct 7, 2024

I was searching for all plugins and sources where it could come from, but the search for 'FULLTEXT' did not deliver any useful results. Is there any other way to find what is using that index?
Asking the other way: is the name if the index of any matter?

@OllieJones
Copy link
Owner

Yes, the name of the index is what causes our plugin to function incorrectly. The workaround I showed above changes the name of the FULLTEXT index.

I have heard of situations where people created FULLTEXT indexes in the belief that they would automatically improve search performance. That doesn't work; exploiting those indexes requires using the MATCH() ... AGAINST() query syntax.

Changing the name of this plugin's index would be a really heavy lift both for testing and for the plugin's users.

@gnanet
Copy link
Author

gnanet commented Oct 12, 2024

Yes, the name of the index is what causes our plugin to function incorrectly. The workaround I showed above changes the name of the FULLTEXT index.

I have heard of situations where people created FULLTEXT indexes in the belief that they would automatically improve search performance. That doesn't work; exploiting those indexes requires using the MATCH() ... AGAINST() query syntax.

Changing the name of this plugin's index would be a really heavy lift both for testing and for the plugin's users.

My question aimed the installation, where i haven't find anything what would create that index, making it unnecessary to keep the index at that name, or even the fulltext index itself.

I would say, the corrective steps would (depending on the mysql version pre 5.7, or 5.7 and past): recreate, or rename the index simultaneously presenting a warning about the action taken.

Question: do you need this issue to be open?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
bug Something isn't working more info needed Further information is requested
Projects
None yet
Development

No branches or pull requests

2 participants