500er bei Levensthein Suche nach Update

[pcartist]

Hallo,

ich habe ein Kundenprojekt von Contao 3.5 auf eine 4.4 aktualisiert. Seitdem läuft die Levensthein Suche nicht mehr. In der Konsole bekomme ich bei der GET-Abfrage einen 500er angezeigt. Im Contao Log ist folgendes zu finden:

[2020-07-15 23:07:40] request.INFO: Matched route "metamodels.attribute_levenshtein.ajax_search". {"route":"metamodels.attribute_levenshtein.ajax_search","route_parameters":{"_scope":"frontend","_token_check":true,"_controller":"metamodels.attribute_levenshtein.controller.ajax_search","table":"mm_employees","attr":"52","_route":"metamodels.attribute_levenshtein.ajax_search"},"request_uri":"https://shoe4.local/mm_lv_search/mm_employees/52?language=de&search=klem","method":"GET"} []
[2020-07-15 23:07:40] security.INFO: Attempting SimplePreAuthentication. {"key":"frontend","authenticator":"Contao\\CoreBundle\\Security\\ContaoAuthenticator"} []
[2020-07-15 23:07:40] request.CRITICAL: Uncaught PHP Exception Doctrine\DBAL\Exception\DriverException: "An exception occurred while executing 'SELECT DISTINCT word FROM tl_metamodel_levensthein_index LEFT JOIN tl_metamodel_levensthein ON (tl_metamodel_levensthein.id=tl_metamodel_levensthein_index.pid)WHERE tl_metamodel_levensthein.item IN (3,363,376,375,374,373,372,371,370,369,368,367,366,365,364,362,378,361,360,359,358,357,356,355,354,353,352,351,350,349,377,379,347,402,418,416,415,414,413,412,411,409,407,406,405,404,403,401,382,399,398,397,395,394,392,391,390,388,387,386,385,384,383,348,346,420,290,307,306,305,304,302,301,299,298,295,294,293,292,291,289,309,288,287,286,285,284,283,282,281,280,279,278,277,276,308,310,345,330,344,343,342,341,339,338,337,336,335,334,333,332,331,329,311,328,327,326,324,322,321,320,319,318,317,316,314,313,312,419,421,274,514,530,529,528,526,525,523,522,521,520,519,518,517,515,513,532,511,510,509,508,507,506,505,504,503,502,501,500,499,531,533,496,551,566,564,562,561,560,559,558,557,556,555,554,553,552,549,534,548,547,546,545,544,543,542,541,540,539,538,537,536,535,497,495,422,438,458,457,456,455,454,453,452,451,450,449,448,443,439,437,462,436,435,434,433,432,431,430,429,428,427,426,425,424,423,461,463,494,480,493,492,491,490,489,488,487,486,485,484,483,482,481,479,464,478,477,476,475,474,473,472,471,470,469,468,467,466,465,275,273,4,87,101,100,98,97,96,95,94,93,92,91,90,89,88,86,104,85,84,83,82,81,80,79,78,77,76,75,74,73,102,105,71,122,137,136,135,134,133,132,131,130,128,127,126,124,123,121,106,120,119,118,117,116,115,114,113,112,111,110,109,108,107,72,70,139,20,33,32,31,30,29,28,27,26,25,24,23,22,21,19,35,17,16,15,14,13,12,11,10,9,8,7,6,5,34,36,69,54,68,67,66,64,63,62,61,60,59,58,57,56,55,53,37,52,51,50,49,48,46,45,44,43,42,41,40,39,38,138,140,272,224,237,236,235,234,233,232,231,230,229,228,227,226,225,223,239,222,221,220,219,218,217,216,215,214,213,212,211,210,238,240,208,258,271,270,269,268,267,266,265,264,263,262,261,260,259,257,241,256,255,254,253,252,251,250,249,248,247,245,244,243,242,209,207,142,158,171,170,169,168,167,166,165,164,163,162,161,160,159,157,173,156,155,154,153,152,151,150,149,148,147,146,145,144,143,172,174,206,191,205,204,203,202,201,200,199,198,197,196,194,193,192,190,175,189,188,187,186,185,184,183,182,181,180,179,178,177,176,567) AND (tl_metamodel_levensthein_index.language='de') AND (tl_metamodel_levensthein_index.attribute IN ('20')) AND (word LIKE 'klem%')ORDER BY FIELD(tl_metamodel_levensthein_index.attribute,'20'),word':  SQLSTATE[HY000]: General error: 3065 Expression #1 of ORDER BY clause is not in SELECT list, references column 'shoe_c4.tl_metamodel_levensthein_index.attribute' which is not in SELECT list; this is incompatible with DISTINCT" at shoe/Contao_4.4/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php line 106 {"exception":"[object] (Doctrine\\DBAL\\Exception\\DriverException(code: 0): An exception occurred while executing 'SELECT DISTINCT word FROM tl_metamodel_levensthein_index LEFT JOIN tl_metamodel_levensthein ON (tl_metamodel_levensthein.id=tl_metamodel_levensthein_index.pid)WHERE tl_metamodel_levensthein.item IN (3,363,376,375,374,373,372,371,370,369,368,367,366,365,364,362,378,361,360,359,358,357,356,355,354,353,352,351,350,349,377,379,347,402,418,416,415,414,413,412,411,409,407,406,405,404,403,401,382,399,398,397,395,394,392,391,390,388,387,386,385,384,383,348,346,420,290,307,306,305,304,302,301,299,298,295,294,293,292,291,289,309,288,287,286,285,284,283,282,281,280,279,278,277,276,308,310,345,330,344,343,342,341,339,338,337,336,335,334,333,332,331,329,311,328,327,326,324,322,321,320,319,318,317,316,314,313,312,419,421,274,514,530,529,528,526,525,523,522,521,520,519,518,517,515,513,532,511,510,509,508,507,506,505,504,503,502,501,500,499,531,533,496,551,566,564,562,561,560,559,558,557,556,555,554,553,552,549,534,548,547,546,545,544,543,542,541,540,539,538,537,536,535,497,495,422,438,458,457,456,455,454,453,452,451,450,449,448,443,439,437,462,436,435,434,433,432,431,430,429,428,427,426,425,424,423,461,463,494,480,493,492,491,490,489,488,487,486,485,484,483,482,481,479,464,478,477,476,475,474,473,472,471,470,469,468,467,466,465,275,273,4,87,101,100,98,97,96,95,94,93,92,91,90,89,88,86,104,85,84,83,82,81,80,79,78,77,76,75,74,73,102,105,71,122,137,136,135,134,133,132,131,130,128,127,126,124,123,121,106,120,119,118,117,116,115,114,113,112,111,110,109,108,107,72,70,139,20,33,32,31,30,29,28,27,26,25,24,23,22,21,19,35,17,16,15,14,13,12,11,10,9,8,7,6,5,34,36,69,54,68,67,66,64,63,62,61,60,59,58,57,56,55,53,37,52,51,50,49,48,46,45,44,43,42,41,40,39,38,138,140,272,224,237,236,235,234,233,232,231,230,229,228,227,226,225,223,239,222,221,220,219,218,217,216,215,214,213,212,211,210,238,240,208,258,271,270,269,268,267,266,265,264,263,262,261,260,259,257,241,256,255,254,253,252,251,250,249,248,247,245,244,243,242,209,207,142,158,171,170,169,168,167,166,165,164,163,162,161,160,159,157,173,156,155,154,153,152,151,150,149,148,147,146,145,144,143,172,174,206,191,205,204,203,202,201,200,199,198,197,196,194,193,192,190,175,189,188,187,186,185,184,183,182,181,180,179,178,177,176,567) AND (tl_metamodel_levensthein_index.language='de') AND (tl_metamodel_levensthein_index.attribute IN ('20')) AND (word LIKE 'klem%')ORDER BY FIELD(tl_metamodel_levensthein_index.attribute,'20'),word':\n\nSQLSTATE[HY000]: General error: 3065 Expression #1 of ORDER BY clause is not in SELECT list, references column 'shoe_c4.tl_metamodel_levensthein_index.attribute' which is not in SELECT list; this is incompatible with DISTINCT at shoe/Contao_4.4/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php:106, Doctrine\\DBAL\\Driver\\PDOException(code: HY000): SQLSTATE[HY000]: General error: 3065 Expression #1 of ORDER BY clause is not in SELECT list, references column 'shoe_c4.tl_metamodel_levensthein_index.attribute' which is not in SELECT list; this is incompatible with DISTINCT at shoe/Contao_4.4/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php:83, PDOException(code: HY000): SQLSTATE[HY000]: General error: 3065 Expression #1 of ORDER BY clause is not in SELECT list, references column 'shoe_c4.tl_metamodel_levensthein_index.attribute' which is not in SELECT list; this is incompatible with DISTINCT at shoe/Contao_4.4/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php:78)"} []

Hier meine composer.json

{
    "type": "project",
    "require": {
        "codefog/contao-news_categories": "^3.1",
        "contao/calendar-bundle": "4.4.*",
        "contao/comments-bundle": "4.4.*",
        "contao/conflicts": "*@dev",
        "contao/faq-bundle": "4.4.*",
        "contao/listing-bundle": "4.4.*",
        "contao/manager-bundle": "4.4.*",
        "contao/news-bundle": "4.4.*",
        "contao/newsletter-bundle": "4.4.*",
        "do-while/contao-backupdb-bundle": "^1.4",
        "erdmannfreunde/euf_grid": "^3.1",
        "fipps/contao-rssimport-bundle": "^4.1",
        "kmielke/calendar-extended-bundle": "^1.0",
        "madeyourday/contao-rocksolid-custom-elements": "^2.2",
        "metamodels/attribute_levensthein": "^2.1",
        "metamodels/bundle_all": "^2.1",
        "w3scout/contao-dompdf-bundle": "^1.0"
    },
    "extra": {
        "contao-component-dir": "assets"
    },
    "scripts": {
        "post-install-cmd": [
            "Contao\\ManagerBundle\\Composer\\ScriptHandler::initializeApplication"
        ],
        "post-update-cmd": [
            "Contao\\ManagerBundle\\Composer\\ScriptHandler::initializeApplication"
        ]
    }
}

Jemand eine Idee?

Danke vorab für Eure Hilfe!

[zonky]

=> Ticket Github

ggf. kannst Du ONLY_FULL_GROUP_BY aus den MySQL-Settings entfernen

SET @@sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY','')); 

oder Query wie hier umbauen https://github.com/Piwigo/Piwigo/iss...ment-421137840

[pcartist]

=> Ticket Github

ggf. kannst Du ONLY_FULL_GROUP_BY aus den MySQL-Settings entfernen

SET @@sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY','')); 

oder Query wie hier umbauen https://github.com/Piwigo/Piwigo/iss...ment-421137840

Danke! Das hat funktioniert. Ticket ist erstellt: https://github.com/MetaModels/attrib...hein/issues/17

[zonky]

Danke! Das hat funktioniert.

was von den beiden Sachen?

[pcartist]

Ich habe eben die my.cnf um diesen Eintrag erweitert:

[mysqld]
sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

Also wie von Dir vorgeschlagen ONLY_FULL_GROUP_BY entfernt.

[zonky]

o.k. - im Shared Hosting kann man das sicher nicht so einfach umstellen...

[zonky]

Ticket ist erstellt: https://github.com/MetaModels/attrib...hein/issues/17

kannst Du das bitte nochmal prüfen: https://github.com/MetaModels/attrib...ment-672112355

[pcartist]

kannst Du das bitte nochmal prüfen: https://github.com/MetaModels/attrib...ment-672112355

Klar. Nach den Ferien sehe ich nochmal drüber.