Answers.Body: <p>The problem was that innodb_ft_max_token_size was set to 10 in the my.ini.</p>
  <p>Updating this and re-saving the index solved the issue. Thanks!</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2024-04-16T20:55:04.887Z
Answers.LastActivityDate: 2024-04-16T20:55:04.887Z
Answers.UserDisplayName: Watsonian
Body: <p>I have been testing our site using MySQL 8.0.36, and all seems great other than the search index seems to be not working as expected as any entry search with a string over 10 characters in length returns 0 results.</p>
  <p>I resaved entries with --update-search-index and the problem still persist.</p>
  <p>I was wondering if it could be something to do with the collation, so I tried converting all tables to utf8mb4_0900_ai_ci, but this fails with the following error:</p>
  <p>Error: SQLSTATE[42000]: Syntax error or access violation: 1118 Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
  The SQL being executed was: ALTER TABLE <code>craft_content</code> CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci.</p>
  <p>Any pointers to what my problem may be are gladly received. I'm sure it's something I'm not thinking about somewhere. Thanks!</p>
  
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2024-04-13T14:14:07.693Z
Comments.Text: what happens if you use utf8mb4_unicode_ci?
Comments.UserDisplayName: funkysoul
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2024-04-15T16:27:05.887Z
Comments.Text: I had to change some fields to text to convert the content database to utf8mb4_unicode_ci, but I'm having the same issues with the search.
Comments.UserDisplayName: Watsonian
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2024-04-16T14:42:45.417Z
Comments.Text: I'm at a loss as to what I need to change.
  
  The following returns results
  SELECT `elementId`
  FROM `craft_searchindex`
  WHERE (((`siteId` = '1')) AND MATCH(`keywords`) AGAINST('experience*' IN BOOLEAN MODE))
  
  If I change it to experienced i get 0 results:
  
  SELECT `elementId`
  FROM `craft_searchindex`
  WHERE (((`siteId` = '1')) AND MATCH(`keywords`) AGAINST('experienced*' IN BOOLEAN MODE))
Comments.UserDisplayName: Watsonian
Tags: <search><mysql><index>
Title: Problems with search index using MySQL 8 and CraftCMS 4.8.0
ThreadText: # Problems with search index using MySQL 8 and CraftCMS 4.8.0
  
  (Asked by: username_0 on 2024-04-12)
  
  I have been testing our site using MySQL 8.0.36, and all seems great other than the search index seems to be not working as expected as any entry search with a string over 10 characters in length returns 0 results.
  
  I resaved entries with --update-search-index and the problem still persist.
  
  I was wondering if it could be something to do with the collation, so I tried converting all tables to utf8mb4\_0900\_ai\_ci, but this fails with the following error:
  
  Error: SQLSTATE[42000]: Syntax error or access violation: 1118 Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
  The SQL being executed was: ALTER TABLE `craft_content` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4\_0900\_ai\_ci.
  
  Any pointers to what my problem may be are gladly received. I'm sure it's something I'm not thinking about somewhere. Thanks!
  
  **Tags:** search, mysql, index
  
  > **Comments:**
  > **username_1**: what happens if you use utf8mb4_unicode_ci?  
  > **username_0**: I had to change some fields to text to convert the content database to utf8mb4_unicode_ci, but I'm having the same issues with the search.  
  > **username_0**: I'm at a loss as to what I need to change.  The following returns results SELECT `elementId` FROM `craft_searchindex` WHERE (((`siteId` = '1')) AND MATCH(`keywords`) AGAINST('experience*' IN BOOLEAN MODE))  If I change it to experienced i get 0 results:  SELECT `elementId` FROM `craft_searchindex` WHERE (((`siteId` = '1')) AND MATCH(`keywords`) AGAINST('experienced*' IN BOOLEAN MODE))  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_0 on 2024-04-16. Score: 1 | Accepted answer
  
  The problem was that innodb\_ft\_max\_token\_size was set to 10 in the my.ini.
  
  Updating this and re-saving the index solved the issue. Thanks!
  
  
  ---
  
  