search

ExpressionEngine / ExpressionEngine

ExpressionEngine is a flexible, feature-rich, free open-source content management platform that empowers hundreds of thousands of individuals and organizations around the world to easily manage their web site.
https://expressionengine.com
Other
458 stars 125 forks source link

Entries search kills MySQL #1311

Closed itelligence closed 11 months ago

itelligence commented 3 years ago

When attempting to perform a entry search in entries section MySQL server load goes high and stays high. No mater what keyword you enter

Debugging the query log shows something really strange, the LIMIT parameter doesn't makes sense.

SELECT COUNT(*) ASnumrows FROM (exp_channel_titlesas ChannelEntry_channel_titles,exp_channel_dataas ChannelEntry_channel_data) LEFT JOINexp_channel_data_field_12AS ChannelEntry_field_id_12 ONChannelEntry_field_id_12.entry_id=ChannelEntry_channel_data.entry_id LEFT JOINexp_channel_data_field_20AS ChannelEntry_field_id_20 ONChannelEntry_field_id_20.entry_id=ChannelEntry_channel_data.entry_id LEFT JOINexp_channel_data_field_26AS ChannelEntry_field_id_26 ONChannelEntry_field_id_26.entry_id=ChannelEntry_channel_data.entry_id LEFT JOINexp_channel_data_field_32AS ChannelEntry_field_id_32 ONChannelEntry_field_id_32.entry_id=ChannelEntry_channel_data.entry_id LEFT JOINexp_channel_data_field_34AS ChannelEntry_field_id_34 ONChannelEntry_field_id_34.entry_id=ChannelEntry_channel_data.entry_id LEFT JOINexp_channel_data_field_49AS ChannelEntry_field_id_49 ONChannelEntry_field_id_49.entry_id=ChannelEntry_channel_data.entry_id LEFT JOINexp_channel_data_field_50AS ChannelEntry_field_id_50 ONChannelEntry_field_id_50.entry_id=ChannelEntry_channel_data.entry_id LEFT JOINexp_channel_data_field_51AS ChannelEntry_field_id_51 ONChannelEntry_field_id_51.entry_id=ChannelEntry_channel_data.entry_id LEFT JOINexp_channel_data_field_52AS ChannelEntry_field_id_52 ONChannelEntry_field_id_52.entry_id=ChannelEntry_channel_data.entry_id LEFT JOINexp_channel_data_field_53AS ChannelEntry_field_id_53 ONChannelEntry_field_id_53.entry_id=ChannelEntry_channel_data.entry_id LEFT JOINexp_channel_data_field_54AS ChannelEntry_field_id_54 ONChannelEntry_field_id_54.entry_id=ChannelEntry_channel_data.entry_id LEFT JOINexp_channel_data_field_55AS ChannelEntry_field_id_55 ONChannelEntry_field_id_55.entry_id=ChannelEntry_channel_data.entry_id LEFT JOINexp_channel_data_field_56AS ChannelEntry_field_id_56 ONChannelEntry_field_id_56.entry_id=ChannelEntry_channel_data.entry_id LEFT JOINexp_channel_data_field_57AS ChannelEntry_field_id_57 ONChannelEntry_field_id_57.entry_id=ChannelEntry_channel_data.entry_id LEFT JOINexp_channel_data_field_61AS ChannelEntry_field_id_61 ONChannelEntry_field_id_61.entry_id=ChannelEntry_channel_data.entry_id LEFT JOINexp_channel_data_field_65AS ChannelEntry_field_id_65 ONChannelEntry_field_id_65.entry_id=ChannelEntry_channel_data.entry_id LEFT JOINexp_channel_data_field_144AS ChannelEntry_field_id_144 ONChannelEntry_field_id_144.entry_id=ChannelEntry_channel_data.entry_id LEFT JOINexp_channel_data_field_146AS ChannelEntry_field_id_146 ONChannelEntry_field_id_146.entry_id=ChannelEntry_channel_data.entry_id LEFT JOINexp_channel_data_field_150AS ChannelEntry_field_id_150 ONChannelEntry_field_id_150.entry_id=ChannelEntry_channel_data.entry_id LEFT JOINexp_channel_data_field_154AS ChannelEntry_field_id_154 ONChannelEntry_field_id_154.entry_id=ChannelEntry_channel_data.entry_id LEFT JOINexp_channel_data_field_156AS ChannelEntry_field_id_156 ONChannelEntry_field_id_156.entry_id=ChannelEntry_channel_data.entry_id LEFT JOINexp_channel_data_field_157AS ChannelEntry_field_id_157 ONChannelEntry_field_id_157.entry_id=ChannelEntry_channel_data.entry_id LEFT JOINexp_channel_data_field_158AS ChannelEntry_field_id_158 ONChannelEntry_field_id_158.entry_id=ChannelEntry_channel_data.entry_id LEFT JOINexp_channel_data_field_159AS ChannelEntry_field_id_159 ONChannelEntry_field_id_159.entry_id=ChannelEntry_channel_data.entry_id LEFT JOINexp_channel_data_field_160AS ChannelEntry_field_id_160 ONChannelEntry_field_id_160.entry_id=ChannelEntry_channel_data.entry_id LEFT JOINexp_channel_data_field_161AS ChannelEntry_field_id_161 ONChannelEntry_field_id_161.entry_id=ChannelEntry_channel_data.entry_id LEFT JOINexp_channel_entries_autosaveAS Autosaves_channel_entries_autosave ONAutosaves_channel_entries_autosave.original_entry_id=ChannelEntry_channel_titles.entry_id LEFT JOINexp_channelsAS Channel_channels ONChannel_channels.channel_id=ChannelEntry_channel_titles.channel_id LEFT JOINexp_membersAS Author_members ONAuthor_members.member_id=ChannelEntry_channel_titles.author_id LEFT JOINexp_member_dataas Author_member_data ONAuthor_member_data.member_id=Author_members.member_id LEFT JOINexp_category_postsas ChannelEntry_Categories_category_posts ONChannelEntry_Categories_category_posts.entry_id=ChannelEntry_channel_titles.entry_id LEFT JOINexp_categoriesas Categories_categories ONCategories_categories.cat_id=ChannelEntry_Categories_category_posts.cat_id LEFT JOINexp_category_field_dataas Categories_category_field_data ONCategories_category_field_data.cat_id=Categories_categories.cat_id WHERE ChannelEntry_channel_data.entry_id = ChannelEntry_channel_titles.entry_id AND ( ChannelEntry_channel_data.site_id= 1 ) AND ( ( ChannelEntry_channel_titles.titleLIKE '%keyword%' ) OR ( ChannelEntry_field_id_20.field_id_20LIKE '%keyword%' ) OR ( ChannelEntry_field_id_144.field_id_144LIKE '%keyword%' ) OR ( ChannelEntry_field_id_146.field_id_146LIKE '%keyword%' ) OR ( ChannelEntry_field_id_26.field_id_26LIKE '%keyword%' ) OR ( ChannelEntry_field_id_32.field_id_32LIKE '%keyword%' ) OR ( ChannelEntry_field_id_34.field_id_34LIKE '%keyword%' ) OR ( ChannelEntry_field_id_65.field_id_65LIKE '%keyword%' ) OR ( ChannelEntry_field_id_154.field_id_154LIKE '%keyword%' ) OR ( ChannelEntry_field_id_159.field_id_159LIKE '%keyword%' ) OR ( ChannelEntry_field_id_160.field_id_160LIKE '%keyword%' ) OR ( ChannelEntry_field_id_161.field_id_161LIKE '%keyword%' ) OR ( ChannelEntry_field_id_12.field_id_12LIKE '%keyword%' ) OR ( ChannelEntry_field_id_61.field_id_61LIKE '%keyword%' ) OR ( ChannelEntry_field_id_49.field_id_49LIKE '%keyword%' ) OR ( ChannelEntry_field_id_50.field_id_50LIKE '%keyword%' ) OR ( ChannelEntry_field_id_51.field_id_51LIKE '%keyword%' ) OR ( ChannelEntry_field_id_52.field_id_52LIKE '%keyword%' ) OR ( ChannelEntry_field_id_53.field_id_53LIKE '%keyword%' ) OR ( ChannelEntry_field_id_54.field_id_54LIKE '%keyword%' ) OR ( ChannelEntry_field_id_55.field_id_55LIKE '%keyword%' ) OR ( ChannelEntry_field_id_56.field_id_56LIKE '%keyword%' ) OR ( ChannelEntry_field_id_57.field_id_57LIKE '%keyword%' ) OR ( ChannelEntry_field_id_150.field_id_150LIKE '%keyword%' ) OR ( ChannelEntry_field_id_156.field_id_156LIKE '%keyword%' ) OR ( ChannelEntry_field_id_157.field_id_157LIKE '%keyword%' ) OR ( ChannelEntry_field_id_158.field_id_158LIKE '%keyword%' ) ) LIMIT 18446744073709551615

intoeetive commented 3 years ago

@itelligence what is the error shown by EE? Screenshot with stack trace would be appreciated

itelligence commented 3 years ago

I am not sure how to get to stack trace. All that happens is that MySQL server essentially hangs with that query and nothing else works until MySQL service is restarted.

That query effectively kills the MySQL server.

On 27 Aug 2021, at 7:09 pm, Yuri Salimovskiy @.***> wrote:

 @itelligence what is the error shown by EE? Screenshot with stack trace would be appreciated

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub, or unsubscribe. Triage notifications on the go with GitHub Mobile for iOS or Android.

itelligence commented 3 years ago

I am not sure what else I need to add but this issue is really frustrating. The Ajax call: https://www.somedomain.com/admin.php?/cp/publish/edit&search_in=titles_and_content&perpage=25&sort_col=column_entry_date&sort_dir=desc&S=2290e7585e6a5779b9360823eaf78610

Untitled

What else can I provide to help with this bug?

robsonsobral commented 3 years ago

Some suggestions.

  1. Does the same happens if you search only titles?
  2. Does it happens if you limit the search by channel?
  3. Something similar happened to me years ago. It was some weird character on some field. I don't remember which one. Run the query by yourself and go limiting it until find the database column with problems.
intoeetive commented 1 year ago

Internal Slack discussion: https://packettide.slack.com/archives/C030Y8HG9L3/p1681311790526679

intoeetive commented 11 months ago

The fix for this has been release in EE 7.3.11: https://github.com/ExpressionEngine/ExpressionEngine/pull/3501