search

fisharebest / webtrees

Online genealogy
https://webtrees.net
GNU General Public License v3.0
457 stars 298 forks source link

[sqlite] Find duplicates failed #3794

Open slavkoja opened 3 years ago

slavkoja commented 3 years ago

Hi, i recently installed webtrees (with sqlite) for first time. It seems mostly to work, but when i try to find dupicates i got this error:

SQLSTATE[HY000]: General error: 1 near "ORDER": syntax error (SQL: select distinct GROUP_CONCAT(DISTINCT d_gid ORDER BY d_gid) AS xrefs from "wt_dates" inner join "wt_name" on "d_file" = "n_file" and "d_gid" = "n_id" where "d_file" = 2 and "d_fact" in (BIRT, CHR, BAPM, DEAT, BURI) group by "d_year", "d_month", "d_day", "d_type", "d_fact", "n_type", "n_full" having COUNT(DISTINCT d_gid) > 1) …/vendor/illuminate/database/Connection.php:664
#0 …/vendor/illuminate/database/Connection.php(624): Illuminate\Database\Connection->runQueryCallback('select distinct...', Array, Object(Closure))
#1 …/vendor/illuminate/database/Connection.php(333): Illuminate\Database\Connection->run('select distinct...', Array, Object(Closure))
#2 …/vendor/illuminate/database/Query/Builder.php(2130): Illuminate\Database\Connection->select('select distinct...', Array, true)
#3 …/vendor/illuminate/database/Query/Builder.php(2324): Illuminate\Database\Query\Builder->runSelect()
#4 …/vendor/illuminate/database/Query/Builder.php(2604): Illuminate\Database\Query\Builder->Illuminate\Database\Query\{closure}()
#5 …/vendor/illuminate/database/Query/Builder.php(2326): Illuminate\Database\Query\Builder->onceWithColumns(Array, Object(Closure))
#6 …/app/Services/AdminService.php(142): Illuminate\Database\Query\Builder->pluck('xrefs')
#7 …/app/Http/RequestHandlers/FindDuplicateRecords.php(63): Fisharebest\Webtrees\Services\AdminService->duplicateRecords(Object(Fisharebest\Webtrees\Tree))
#8 …/app/Http/Middleware/WrapHandler.php(83): Fisharebest\Webtrees\Http\RequestHandlers\FindDuplicateRecords->handle(Object(Nyholm\Psr7\ServerRequest))
#9 …/vendor/oscarotero/middleland/src/Dispatcher.php(136): Fisharebest\Webtrees\Http\Middleware\WrapHandler->process(Object(Nyholm\Psr7\ServerRequest), Object(Middleland\Dispatcher))
#10 …/modules_v4/reminder/src/Modules/ReminderModule.php(98): Middleland\Dispatcher->handle(Object(Nyholm\Psr7\ServerRequest))
#11 …/vendor/oscarotero/middleland/src/Dispatcher.php(136): UksusoFF\WebtreesModules\Reminder\Modules\ReminderModule->process(Object(Nyholm\Psr7\ServerRequest), Object(Middleland\Dispatcher))
#12 …/app/Module/HitCountFooterModule.php(157): Middleland\Dispatcher->handle(Object(Nyholm\Psr7\ServerRequest))
#13 …/vendor/oscarotero/middleland/src/Dispatcher.php(136): Fisharebest\Webtrees\Module\HitCountFooterModule->process(Object(Nyholm\Psr7\ServerRequest), Object(Middleland\Dispatcher))
#14 …/app/Http/Middleware/AuthManager.php(57): Middleland\Dispatcher->handle(Object(Nyholm\Psr7\ServerRequest))
#15 …/vendor/oscarotero/middleland/src/Dispatcher.php(136): Fisharebest\Webtrees\Http\Middleware\AuthManager->process(Object(Nyholm\Psr7\ServerRequest), Object(Middleland\Dispatcher))
#16 …/app/Http/Middleware/CheckCsrf.php(75): Middleland\Dispatcher->handle(Object(Nyholm\Psr7\ServerRequest))
#17 …/vendor/oscarotero/middleland/src/Dispatcher.php(136): Fisharebest\Webtrees\Http\Middleware\CheckCsrf->process(Object(Nyholm\Psr7\ServerRequest), Object(Middleland\Dispatcher))
#18 …/vendor/oscarotero/middleland/src/Dispatcher.php(118): Middleland\Dispatcher->handle(Object(Nyholm\Psr7\ServerRequest))
#19 …/app/Http/Middleware/Router.php(166): Middleland\Dispatcher->dispatch(Object(Nyholm\Psr7\ServerRequest))
#20 …/vendor/oscarotero/middleland/src/Dispatcher.php(136): Fisharebest\Webtrees\Http\Middleware\Router->process(Object(Nyholm\Psr7\ServerRequest), Object(Middleland\Dispatcher))
#21 …/app/Http/Middleware/BootModules.php(62): Middleland\Dispatcher->handle(Object(Nyholm\Psr7\ServerRequest))
#22 …/vendor/oscarotero/middleland/src/Dispatcher.php(136): Fisharebest\Webtrees\Http\Middleware\BootModules->process(Object(Nyholm\Psr7\ServerRequest), Object(Middleland\Dispatcher))
#23 …/app/Http/Middleware/LoadRoutes.php(76): Middleland\Dispatcher->handle(Object(Nyholm\Psr7\ServerRequest))
#24 …/vendor/oscarotero/middleland/src/Dispatcher.php(136): Fisharebest\Webtrees\Http\Middleware\LoadRoutes->process(Object(Nyholm\Psr7\ServerRequest), Object(Middleland\Dispatcher))
#25 …/app/Http/Middleware/UseTransaction.php(45): Middleland\Dispatcher->handle(Object(Nyholm\Psr7\ServerRequest))
#26 …/vendor/illuminate/database/Concerns/ManagesTransactions.php(29): Fisharebest\Webtrees\Http\Middleware\UseTransaction::Fisharebest\Webtrees\Http\Middleware\{closure}(Object(Illuminate\Database\SQLiteConnection))
#27 …/app/Http/Middleware/UseTransaction.php(46): Illuminate\Database\Connection->transaction(Object(Closure), 3)
#28 …/vendor/oscarotero/middleland/src/Dispatcher.php(136): Fisharebest\Webtrees\Http\Middleware\UseTransaction->process(Object(Nyholm\Psr7\ServerRequest), Object(Middleland\Dispatcher))
#29 …/app/Http/Middleware/DoHousekeeping.php(74): Middleland\Dispatcher->handle(Object(Nyholm\Psr7\ServerRequest))
#30 …/vendor/oscarotero/middleland/src/Dispatcher.php(136): Fisharebest\Webtrees\Http\Middleware\DoHousekeeping->process(Object(Nyholm\Psr7\ServerRequest), Object(Middleland\Dispatcher))
#31 …/app/Http/Middleware/UseTheme.php(70): Middleland\Dispatcher->handle(Object(Nyholm\Psr7\ServerRequest))
#32 …/vendor/oscarotero/middleland/src/Dispatcher.php(136): Fisharebest\Webtrees\Http\Middleware\UseTheme->process(Object(Nyholm\Psr7\ServerRequest), Object(Middleland\Dispatcher))
#33 …/app/Http/Middleware/CheckForMaintenanceMode.php(51): Middleland\Dispatcher->handle(Object(Nyholm\Psr7\ServerRequest))
#34 …/vendor/oscarotero/middleland/src/Dispatcher.php(136): Fisharebest\Webtrees\Http\Middleware\CheckForMaintenanceMode->process(Object(Nyholm\Psr7\ServerRequest), Object(Middleland\Dispatcher))
#35 …/app/Http/Middleware/UseLanguage.php(70): Middleland\Dispatcher->handle(Object(Nyholm\Psr7\ServerRequest))
#36 …/vendor/oscarotero/middleland/src/Dispatcher.php(136): Fisharebest\Webtrees\Http\Middleware\UseLanguage->process(Object(Nyholm\Psr7\ServerRequest), Object(Middleland\Dispatcher))
#37 …/app/Http/Middleware/UseSession.php(71): Middleland\Dispatcher->handle(Object(Nyholm\Psr7\ServerRequest))
#38 …/vendor/oscarotero/middleland/src/Dispatcher.php(136): Fisharebest\Webtrees\Http\Middleware\UseSession->process(Object(Nyholm\Psr7\ServerRequest), Object(Middleland\Dispatcher))
#39 …/app/Http/Middleware/UpdateDatabaseSchema.php(58): Middleland\Dispatcher->handle(Object(Nyholm\Psr7\ServerRequest))
#40 …/vendor/oscarotero/middleland/src/Dispatcher.php(136): Fisharebest\Webtrees\Http\Middleware\UpdateDatabaseSchema->process(Object(Nyholm\Psr7\ServerRequest), Object(Middleland\Dispatcher))
#41 …/app/Http/Middleware/UseDebugbar.php(67): Middleland\Dispatcher->handle(Object(Nyholm\Psr7\ServerRequest))
#42 …/vendor/oscarotero/middleland/src/Dispatcher.php(136): Fisharebest\Webtrees\Http\Middleware\UseDebugbar->process(Object(Nyholm\Psr7\ServerRequest), Object(Middleland\Dispatcher))
#43 …/app/Http/Middleware/UseDatabase.php(117): Middleland\Dispatcher->handle(Object(Nyholm\Psr7\ServerRequest))
#44 …/vendor/oscarotero/middleland/src/Dispatcher.php(136): Fisharebest\Webtrees\Http\Middleware\UseDatabase->process(Object(Nyholm\Psr7\ServerRequest), Object(Middleland\Dispatcher))
#45 …/app/Http/Middleware/BadBotBlocker.php(217): Middleland\Dispatcher->handle(Object(Nyholm\Psr7\ServerRequest))
#46 …/vendor/oscarotero/middleland/src/Dispatcher.php(136): Fisharebest\Webtrees\Http\Middleware\BadBotBlocker->process(Object(Nyholm\Psr7\ServerRequest), Object(Middleland\Dispatcher))
#47 …/app/Http/Middleware/CompressResponse.php(75): Middleland\Dispatcher->handle(Object(Nyholm\Psr7\ServerRequest))
#48 …/vendor/oscarotero/middleland/src/Dispatcher.php(136): Fisharebest\Webtrees\Http\Middleware\CompressResponse->process(Object(Nyholm\Psr7\ServerRequest), Object(Middleland\Dispatcher))
#49 …/app/Http/Middleware/RegisterFactories.php(74): Middleland\Dispatcher->handle(Object(Nyholm\Psr7\ServerRequest))
#50 …/vendor/oscarotero/middleland/src/Dispatcher.php(136): Fisharebest\Webtrees\Http\Middleware\RegisterFactories->process(Object(Nyholm\Psr7\ServerRequest), Object(Middleland\Dispatcher))
#51 …/vendor/middlewares/client-ip/src/ClientIp.php(81): Middleland\Dispatcher->handle(Object(Nyholm\Psr7\ServerRequest))
#52 …/app/Http/Middleware/ClientIp.php(47): Middlewares\ClientIp->process(Object(Nyholm\Psr7\ServerRequest), Object(Middleland\Dispatcher))
#53 …/vendor/oscarotero/middleland/src/Dispatcher.php(136): Fisharebest\Webtrees\Http\Middleware\ClientIp->process(Object(Nyholm\Psr7\ServerRequest), Object(Middleland\Dispatcher))
#54 …/app/Http/Middleware/HandleExceptions.php(90): Middleland\Dispatcher->handle(Object(Nyholm\Psr7\ServerRequest))
#55 …/vendor/oscarotero/middleland/src/Dispatcher.php(136): Fisharebest\Webtrees\Http\Middleware\HandleExceptions->process(Object(Nyholm\Psr7\ServerRequest), Object(Middleland\Dispatcher))
#56 …/app/Http/Middleware/BaseUrl.php(77): Middleland\Dispatcher->handle(Object(Nyholm\Psr7\ServerRequest))
#57 …/vendor/oscarotero/middleland/src/Dispatcher.php(136): Fisharebest\Webtrees\Http\Middleware\BaseUrl->process(Object(Nyholm\Psr7\ServerRequest), Object(Middleland\Dispatcher))
#58 …/app/Http/Middleware/ReadConfigIni.php(69): Middleland\Dispatcher->handle(Object(Nyholm\Psr7\ServerRequest))
#59 …/vendor/oscarotero/middleland/src/Dispatcher.php(136): Fisharebest\Webtrees\Http\Middleware\ReadConfigIni->process(Object(Nyholm\Psr7\ServerRequest), Object(Middleland\Dispatcher))
#60 …/app/Http/Middleware/SecurityHeaders.php(47): Middleland\Dispatcher->handle(Object(Nyholm\Psr7\ServerRequest))
#61 …/vendor/oscarotero/middleland/src/Dispatcher.php(136): Fisharebest\Webtrees\Http\Middleware\SecurityHeaders->process(Object(Nyholm\Psr7\ServerRequest), Object(Middleland\Dispatcher))
#62 …/app/Http/Middleware/EmitResponse.php(57): Middleland\Dispatcher->handle(Object(Nyholm\Psr7\ServerRequest))
#63 …/vendor/oscarotero/middleland/src/Dispatcher.php(136): Fisharebest\Webtrees\Http\Middleware\EmitResponse->process(Object(Nyholm\Psr7\ServerRequest), Object(Middleland\Dispatcher))
#64 …/app/Http/Middleware/PhpEnvironment.php(49): Middleland\Dispatcher->handle(Object(Nyholm\Psr7\ServerRequest))
#65 …/vendor/oscarotero/middleland/src/Dispatcher.php(136): Fisharebest\Webtrees\Http\Middleware\PhpEnvironment->process(Object(Nyholm\Psr7\ServerRequest), Object(Middleland\Dispatcher))
#66 …/vendor/oscarotero/middleland/src/Dispatcher.php(118): Middleland\Dispatcher->handle(Object(Nyholm\Psr7\ServerRequest))
#67 …/index.php(57): Middleland\Dispatcher->dispatch(Object(Nyholm\Psr7\ServerRequest))
#68 {main}

It happens in both family trees, the one imported from myheritage and one freshly created.

fisharebest commented 3 years ago

This page gives some tips on implementing this feature in sqlite: https://learnsql.com/blog/group-concat/

slavkoja commented 3 years ago

If you give me the MySQL query with a little description, i can try to convert it for SQLite.