firefly-iii / firefly-iii

Firefly III: a personal finances manager
https://firefly-iii.org/
GNU Affero General Public License v3.0
16.22k stars 1.47k forks source link

Error: SQLSTATE[HY000] [2002] Connection timed out #1852

Closed filoor closed 6 years ago

filoor commented 6 years ago

After adding a try to add a big csv 5000 rows it freezd and from then on every file it try i will get the next error:

2018-11-03 11:46:17,971 DEBG 'apache2' stdout output: [2018-11-03 11:46:17] local.ERROR: It seems the database has not yet been initialized. Did you run the correct upgrade or installation commands? Error: SQLSTATE[HY000] [2002] Connection timed out (SQL: select * fromuserswhereid= 1 limit 1) {"userId":1,"email":"*@gmail.com","exception":"[object] (FireflyIII\\Exceptions\\FireflyException(code: 0): It seems the database has not yet been initialized. Did you run the correct upgrade or installation commands? Error: SQLSTATE[HY000] [2002] Connection timed out (SQL: select * fromuserswhereid` = 1 limit 1) at /var/www/firefly-iii/app/Http/Middleware/Authenticate.php:115) [stacktrace]

0 /var/www/firefly-iii/app/Http/Middleware/Authenticate.php(71): FireflyIII\Http\Middleware\Authenticate->authenticate(Object(Illuminate\Http\Request), Array)

1 /var/www/firefly-iii/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(151): FireflyIII\Http\Middleware\Authenticate->handle(Object(Illuminate\Http\Request), Object(Closure))

2 /var/www/firefly-iii/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(53): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}(Object(Illuminate\Http\Request))

3 /var/www/firefly-iii/vendor/laravel/passport/src/Http/Middleware/CreateFreshApiToken.php(50): Illuminate\Routing\Pipeline->Illuminate\Routing\{closure}(Object(Illuminate\Http\Request))

4 /var/www/firefly-iii/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(151): Laravel\Passport\Http\Middleware\CreateFreshApiToken->handle(Object(Illuminate\Http\Request), Object(Closure))

5 /var/www/firefly-iii/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(53): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}(Object(Illuminate\Http\Request))

6 /var/www/firefly-iii/vendor/laravel/framework/src/Illuminate/Foundation/Http/Middleware/VerifyCsrfToken.php(75): Illuminate\Routing\Pipeline->Illuminate\Routing\{closure}(Object(Illuminate\Http\Request))

7 /var/www/firefly-iii/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(151): Illuminate\Foundation\Http\Middleware\VerifyCsrfToken->handle(Object(Illuminate\Http\Request), Object(Closure))

8 /var/www/firefly-iii/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(53): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}(Object(Illuminate\Http\Request))

9 /var/www/firefly-iii/vendor/laravel/framework/src/Illuminate/View/Middleware/ShareErrorsFromSession.php(49): Illuminate\Routing\Pipeline->Illuminate\Routing\{closure}(Object(Illuminate\Http\Request))

10 /var/www/firefly-iii/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(151): Illuminate\View\Middleware\ShareErrorsFromSession->handle(Object(Illuminate\Http\Request), Object(Closure))

11 /var/www/firefly-iii/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(53): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}(Object(Illuminate\Http\Request))

12 /var/www/firefly-iii/vendor/laravel/framework/src/Illuminate/Session/Middleware/StartSession.php(63): Illuminate\Routing\Pipeline->Illuminate\Routing\{closure}(Object(Illuminate\Http\Request))

13 /var/www/firefly-iii/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(151): Illuminate\Session\Middleware\StartSession->handle(Object(Illuminate\Http\Request), Object(Closure))

14 /var/www/firefly-iii/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(53): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}(Object(Illuminate\Http\Request))

15 /var/www/firefly-iii/vendor/laravel/framework/src/Illuminate/Cookie/Middleware/AddQueuedCookiesToResponse.php(37): Illuminate\Routing\Pipeline->Illuminate\Routing\{closure}(Object(Illuminate\Http\Request))

16 /var/www/firefly-iii/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(151): Illuminate\Cookie\Middleware\AddQueuedCookiesToResponse->handle(Object(Illuminate\Http\Request), Object(Closure))

17 /var/www/firefly-iii/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(53): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}(Object(Illuminate\Http\Request))

18 /var/www/firefly-iii/vendor/laravel/framework/src/Illuminate/Cookie/Middleware/EncryptCookies.php(66): Illuminate\Routing\Pipeline->Illuminate\Routing\{closure}(Object(Illuminate\Http\Request))

19 /var/www/firefly-iii/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(151): Illuminate\Cookie\Middleware\EncryptCookies->handle(Object(Illuminate\Http\Request), Object(Closure))

20 /var/www/firefly-iii/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(53): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}(Object(Illuminate\Http\Request))

21 /var/www/firefly-iii/app/Http/Middleware/Sandstorm.php(54): Illuminate\Routing\Pipeline->Illuminate\Routing\{closure}(Object(Illuminate\Http\Request))

22 /var/www/firefly-iii/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(151): FireflyIII\Http\Middleware\Sandstorm->handle(Object(Illuminate\Http\Request), Object(Closure))

23 /var/www/firefly-iii/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(53): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}(Object(Illuminate\Http\Request))

24 /var/www/firefly-iii/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(104): Illuminate\Routing\Pipeline->Illuminate\Routing\{closure}(Object(Illuminate\Http\Request))

25 /var/www/firefly-iii/vendor/laravel/framework/src/Illuminate/Routing/Router.php(681): Illuminate\Pipeline\Pipeline->then(Object(Closure))

26 /var/www/firefly-iii/vendor/laravel/framework/src/Illuminate/Routing/Router.php(656): Illuminate\Routing\Router->runRouteWithinStack(Object(Illuminate\Routing\Route), Object(Illuminate\Http\Request))

27 /var/www/firefly-iii/vendor/laravel/framework/src/Illuminate/Routing/Router.php(622): Illuminate\Routing\Router->runRoute(Object(Illuminate\Http\Request), Object(Illuminate\Routing\Route))

28 /var/www/firefly-iii/vendor/laravel/framework/src/Illuminate/Routing/Router.php(611): Illuminate\Routing\Router->dispatchToRoute(Object(Illuminate\Http\Request))

29 /var/www/firefly-iii/vendor/laravel/framework/src/Illuminate/Foundation/Http/Kernel.php(176): Illuminate\Routing\Router->dispatch(Object(Illuminate\Http\Request))

30 /var/www/firefly-iii/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(30): Illuminate\Foundation\Http\Kernel->Illuminate\Foundation\Http\{closure}(Object(Illuminate\Http\Request))

31 /var/www/firefly-iii/vendor/fideloper/proxy/src/TrustProxies.php(57): Illuminate\Routing\Pipeline->Illuminate\Routing\{closure}(Object(Illuminate\Http\Request))

32 /var/www/firefly-iii/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(151): Fideloper\Proxy\TrustProxies->handle(Object(Illuminate\Http\Request), Object(Closure))

33 /var/www/firefly-iii/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(53): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}(Object(Illuminate\Http\Request))

34 /var/www/firefly-iii/vendor/laravel/framework/src/Illuminate/Foundation/Http/Middleware/TransformsRequest.php(31): Illuminate\Routing\Pipeline->Illuminate\Routing\{closure}(Object(Illuminate\Http\Request))

35 /var/www/firefly-iii/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(151): Illuminate\Foundation\Http\Middleware\TransformsRequest->handle(Object(Illuminate\Http\Request), Object(Closure))

36 /var/www/firefly-iii/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(53): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}(Object(Illuminate\Http\Request))

37 /var/www/firefly-iii/vendor/laravel/framework/src/Illuminate/Foundation/Http/Middleware/TransformsRequest.php(31): Illuminate\Routing\Pipeline->Illuminate\Routing\{closure}(Object(Illuminate\Http\Request))

38 /var/www/firefly-iii/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(151): Illuminate\Foundation\Http\Middleware\TransformsRequest->handle(Object(Illuminate\Http\Request), Object(Closure))

39 /var/www/firefly-iii/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(53): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}(Object(Illuminate\Http\Request))

40 /var/www/firefly-iii/vendor/laravel/framework/src/Illuminate/Foundation/Http/Middleware/ValidatePostSize.php(27): Illuminate\Routing\Pipeline->Illuminate\Routing\{closure}(Object(Illuminate\Http\Request))

41 /var/www/firefly-iii/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(151): Illuminate\Foundation\Http\Middleware\ValidatePostSize->handle(Object(Illuminate\Http\Request), Object(Closure))

42 /var/www/firefly-iii/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(53): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}(Object(Illuminate\Http\Request))

43 /var/www/firefly-iii/vendor/laravel/framework/src/Illuminate/Foundation/Http/Middleware/CheckForMaintenanceMode.php(62): Illuminate\Routing\Pipeline->Illuminate\Routing\{closure}(Object(Illuminate\Http\Request))

44 /var/www/firefly-iii/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(151): Illuminate\Foundation\Http\Middleware\CheckForMaintenanceMode->handle(Object(Illuminate\Http\Request), Object(Closure))

45 /var/www/firefly-iii/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(53): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}(Object(Illuminate\Http\Request))

46 /var/www/firefly-iii/app/Http/Middleware/SecureHeaders.php(45): Illuminate\Routing\Pipeline->Illuminate\Routing\{closure}(Object(Illuminate\Http\Request))

47 /var/www/firefly-iii/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(151): FireflyIII\Http\Middleware\SecureHeaders->handle(Object(Illuminate\Http\Request), Object(Closure))

48 /var/www/firefly-iii/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(53): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}(Object(Illuminate\Http\Request))

49 /var/www/firefly-iii/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(104): Illuminate\Routing\Pipeline->Illuminate\Routing\{closure}(Object(Illuminate\Http\Request))

50 /var/www/firefly-iii/vendor/laravel/framework/src/Illuminate/Foundation/Http/Kernel.php(151): Illuminate\Pipeline\Pipeline->then(Object(Closure))

51 /var/www/firefly-iii/vendor/laravel/framework/src/Illuminate/Foundation/Http/Kernel.php(116): Illuminate\Foundation\Http\Kernel->sendRequestThroughRouter(Object(Illuminate\Http\Request))

52 /var/www/firefly-iii/public/index.php(76): Illuminate\Foundation\Http\Kernel->handle(Object(Illuminate\Http\Request))

53 {main}

"} 2018-11-03T11:46:17.972888800Z 172.17.0.1 - - [03/Nov/2018:11:45:47 +0000] "GET /import/job/json/GShnQgoobikx HTTP/1.1" 500 2234 "-" "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/70.0.3538.77 Safari/537.36" 2018-11-03 11:46:17,978 DEBG 'apache2' stdout output: 172.17.0.1 - - [03/Nov/2018:11:45:47 +0000] "GET /import/job/json/GShnQgoobikx HTTP/1.1" 500 2234 "-" "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/70.0.3538.77 Safari/537.36" 2018-11-03T11:46:17.978859400Z 172.17.0.1 - - [03/Nov/2018:11:46:37 +0000] "GET / HTTP/1.1" 302 1891 "-" "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Kitematic/0.17.3 Chrome/45.0.2454.85 Electron/0.35.4 Safari/537.36" 2018-11-03 11:46:37,362 DEBG 'apache2' stdout output: 172.17.0.1 - - [03/Nov/2018:11:46:37 +0000] "GET / HTTP/1.1" 302 1891 "-" "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Kitematic/0.17.3 Chrome/45.0.2454.85 Electron/0.35.4 Safari/537.36" 2018-11-03T11:46:37.362745900Z 172.17.0.1 - - [03/Nov/2018:11:46:37 +0000] "GET /login HTTP/1.1" 200 2712 "-" "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Kitematic/0.17.3 Chrome/45.0.2454.85 Electron/0.35.4 Safari/537.36" 2018-11-03 11:46:37,454 DEBG 'apache2' stdout output: 172.17.0.1 - - [03/Nov/2018:11:46:37 +0000] "GET /login HTTP/1.1" 200 2712 "-" "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Kitematic/0.17.3 Chrome/45.0.2454.85 Electron/0.35.4 Safari/537.36" 2018-11-03T11:46:37.455285700Z `

JC5 commented 6 years ago

OK. I am not really able to help you without a little background info. Could you share the table from the /debug page?

filoor commented 6 years ago

Hi Jope you mean this

`Debug information generated at 2018-11-03 12:35:03 UTC for Firefly III version 4.7.8.

Variable Content
FF version 4.7.8
FF API version 0.81
App environment local
App debug mode ''
App cache driver file
App logging , stdout
PHP version 7.2.11
Display errors Off
Session start 2018-11-01 00:00:00
Session end 2018-11-30 23:59:59
Session first 2011-06-14 00:00:00
Error reporting ALL errors
Host Linux
Interface apache2handler
UserID 1
Attempt at "en" false
Attempt at "English" false
Attempt at "en_US.utf8" 'en_US.utf8'
Attempt at "en_US.UTF-8" 'en_US.UTF-8'
DB drivers mysql, pgsql, sqlite
Current driver mysql
Using Sandstorm? no
Is Sandstorm (.env) false
Is Docker (.env) true
bunq uses sandbox false
Trusted proxies (.env)
User agent Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/70.0.3538.77 Safari/537.36
Loaded extensions Core, date, libxml, openssl, pcre, sqlite3, zlib, ctype, curl, dom, fileinfo, filter, ftp, hash, iconv, json, mbstring, SPL, PDO, bz2, posix, Reflection, session, SimpleXML, pdo_sqlite, standard, tokenizer, xml, xmlreader, xmlwriter, mysqlnd, apache2handler, bcmath, Phar, gd, intl, ldap, pdo_mysql, pdo_pgsql, sodium, tidy, zip
Installed packages adldap2/adldap2@v8.1.5, adldap2/adldap2-laravel@v4.0.10, bacon/bacon-qr-code@1.0.3, bunq/sdk_php@dev-master, danhunsaker/laravel-flysystem-others@v1.3.4, danhunsaker/laravel-flysystem-service@v1.2.2, davejamesmiller/laravel-breadcrumbs@5.1.2, defuse/php-encryption@v2.2.1, doctrine/cache@v1.8.0, doctrine/dbal@v2.8.0, doctrine/event-manager@v1.0.0, doctrine/inflector@v1.3.0, doctrine/lexer@v1.0.1, dragonmantank/cron-expression@v2.2.0, egulias/email-validator@2.1.6, erusev/parsedown@1.7.1, fideloper/proxy@4.0.0, firebase/php-jwt@v5.0.0, guzzlehttp/guzzle@6.3.3, guzzlehttp/promises@v1.3.1, guzzlehttp/psr7@1.4.2, laravel/framework@v5.7.11, laravel/passport@v7.0.3, laravelcollective/html@v5.7.1, lcobucci/jwt@3.2.4, league/commonmark@0.18.0, league/csv@9.1.4, league/event@2.1.2, league/flysystem@1.0.48, league/flysystem-replicate-adapter@1.0.1, league/flysystem-sftp@1.0.17, league/fractal@0.17.0, league/oauth2-server@7.2.0, litipk/flysystem-fallback-adapter@0.1.2, monolog/monolog@1.23.0, mschindler83/fints-hbci-php@1.0.4, nesbot/carbon@1.34.0, opis/closure@3.1.1, paragonie/constant_time_encoding@v2.2.2, paragonie/random_compat@v9.99.99, phpseclib/phpseclib@2.0.11, pragmarx/google2fa@v3.0.3, pragmarx/google2fa-laravel@v0.2.0, psr/container@1.0.0, psr/http-message@1.0.1, psr/log@1.0.2, psr/simple-cache@1.0.1, ramsey/uuid@3.8.0, rcrowe/twigbridge@v0.9.9, swiftmailer/swiftmailer@v6.1.3, symfony/console@v4.1.6, symfony/css-selector@v4.1.6, symfony/debug@v4.1.6, symfony/event-dispatcher@v4.1.6, symfony/finder@v4.1.6, symfony/http-foundation@v4.1.6, symfony/http-kernel@v4.1.6, symfony/polyfill-ctype@v1.9.0, symfony/polyfill-mbstring@v1.9.0, symfony/polyfill-php56@v1.9.0, symfony/polyfill-php72@v1.9.0, symfony/polyfill-util@v1.9.0, symfony/process@v4.1.6, symfony/psr-http-message-bridge@v1.1.0, symfony/routing@v4.1.6, symfony/translation@v4.1.6, symfony/var-dumper@v4.1.6, tijsverkoyen/css-to-inline-styles@2.2.1, twig/twig@v1.35.4, vlucas/phpdotenv@v2.5.1, zendframework/zend-diactoros@1.8.6,

`

filoor commented 6 years ago

I have the database running on windows "Mysql" and the firefly on Docker same system

filoor commented 6 years ago

After adding a file of 500 rows it went through

filoor commented 6 years ago

a file off 1800 give the same error again

frontend error is "De import is tegen een fout aangelopen waar-ie niet meer van terug kan komen. Excuses!

Cannot get status of current job: error: Internal Server Error"

filoor commented 6 years ago

After trying 3 times the 1800 whent throu so i now wanted to try the main file with 7697 records i get this error when try to import in the last step of importing

Allowed memory size of 134217728 bytes exhausted (tried to allocate 81813504 bytes)

image

filoor commented 6 years ago

sorry for making you crazy

JC5 commented 6 years ago

No problem!

It's not a problem I can fix. You'll have to import smaller files. I would advice against importing your entire history anyway, but if you really want to do it then do it in small bursts.

filoor commented 6 years ago

Oki Thank you

i will delete all the transactions of rabobank and only import 2018

filoor commented 6 years ago

is there a way for a quick cleanup without loosing my rules an my bunq tranactions?

JC5 commented 6 years ago

The easiest way is to delete the account from the interface, and then open the database. From the database, delete all entries in the table accounts where the deleted_at field is not null. Also delete all entries from the table transaction_journals where the deleted_at field is not null.

It's a bit complex but should be fool proof.

Make sure you backup everything.

filoor commented 6 years ago

Hi,

Me again i have done that but i had a lot of troubles with my bunq imort after that so i did a new install but i'm now getting constantly

"It seems the database has not yet been initialized. Did you run the correct upgrade or installation commands? Error: SQLSTATE[HY000] [2002] Connection timed out (SQL: select * from users where id = 1 limit 1)"

JC5 commented 6 years ago

There's not much I can do when the connection to the database times out, unfortunately.

filoor commented 6 years ago

i have moved DB thanks