Migration SQL Error

ekulnitsua · June 4, 2021, 3:09pm

Hi,

I’ve managed to get the two implementations working - now in migration I’m getting an SQL error:

SQLSTATE[HY000] [1045] Access denied for user 'ninja'@'localhost' (using password: YES) (SQL: select exists(select * from `users` where (`email` = junk@biketrax.co.uk) and `users`.`deleted_at` is null) as `exists`)
        SQLSTATE[HY000] [1045] Access denied for user 'ninja'@'localhost' (using password: YES) (SQL: select exists(select * from `users` where (`email` = junk@biketrax.co.uk) and `users`.`deleted_at` is null) as `exists`)

invoiceninja.log:

[2021-06-04 14:56:19] production.INFO: Starting Migration
[2021-06-04 14:56:19] production.INFO: creating fresh company
[2021-06-04 14:56:19] production.INFO: starting migration job
[2021-06-04 14:56:19] production.INFO: migrations/acp2cibpfgkfbirvnstfadg3ifa3wawm-2021-06-04-invoiceninja.zip
[2021-06-04 14:56:19] production.INFO: Inside Migration Job
[2021-06-04 15:00:01] production.INFO: Sending recurring invoices 2021-06-04 03:00:01
[2021-06-04 15:00:01] production.INFO: 2021-06-04 Sending Recurring Invoices. Count = 0

I’ve made sure that the users of the two databases match, the passwords are correct, and the the php optimize command run.

Any ideas?

hillel · June 5, 2021, 6:16pm

Hi,

Can you confirm the user/pass is correct by connecting to mysql directly?

ekulnitsua · June 6, 2021, 7:16am

Hi @hillel

Yep - one single user, ‘ninja’ with access to both databases ‘ninjadb’ and ‘invoiceninja’ - full privileges granted and both apps running - v4 on HTTP, v5 on HTTPS. SQL error occurs after compnay is created,and logo transferred. I’m not sure where the reference to ‘junk@biketrax.co.uk’ comes from, as that ios not an email used in either apps userbase.

I’ve checked:

Both .env files
Force HTTPS off on v4
all redirects removed from apache2 confs

hillel · June 6, 2021, 7:27am

@david do you have any thoughts?

david · June 6, 2021, 9:15am

you may want to optimize

php artisan optimize

the only reason i can think of is the DB credentials aren’t right.

ekulnitsua · June 6, 2021, 9:27am

Thanks for that @david - I’ve already run the optimize routine.

Both implementations are running, so the db credentials must be correct, no?

There’s a lot of trash, and references to databases that don’t exist in my V5 .env:

APP_NAME="Invoice Ninja"
APP_ENV=production
APP_KEY=base64:6Q78QZfF1s8OPRMH/prM959A7yW95096eP/OESKGL64=
APP_DEBUG=false

APP_URL=https://invoices.biketrax.co.uk

DB_CONNECTION=db-ninja-01
MULTI_DB_ENABLED=false

DB_HOST1=localhost
DB_DATABASE1=ninjadb
DB_USERNAME1=ninja
DB_PASSWORD1=ninjapass
DB_PORT1=3306

DB_HOST2=localhost
DB_DATABASE2=ninja2
DB_USERNAME2=ninja
DB_PASSWORD2=ninja
DB_PORT2=3306

DEMO_MODE=false

BROADCAST_DRIVER=log
LOG_CHANNEL=stack
CACHE_DRIVER=file
QUEUE_CONNECTION=sync
SESSION_DRIVER=file
SESSION_LIFETIME=120

REDIS_HOST=127.0.0.1
REDIS_PASSWORD=null
REDIS_PORT=6379

MAIL_MAILER=smtp
MAIL_HOST=biketrax.co.uk
MAIL_PORT=587
MAIL_USERNAME=info@biketrax.co.uk
MAIL_PASSWORD=******
MAIL_ENCRYPTION=tls
MAIL_FROM_ADDRESS=info@biketrax.co.uk
MAIL_FROM_NAME="Biketrax Front Desk"

POSTMARK_API_TOKEN=
REQUIRE_HTTPS=true

GOOGLE_MAPS_API_KEY=
ERROR_EMAIL=
TRUSTED_PROXIES=

NINJA_ENVIRONMENT=selfhost

#options - snappdf / phantom / hosted_ninja
PDF_GENERATOR=phantom

PHANTOMJS_KEY='a-demo-key-with-low-quota-per-ip-address'
PHANTOMJS_SECRET=secret

UPDATE_SECRET=secret
EXPANDED_LOGGING=true
COMPOSER_AUTH='{"github-oauth": {"github.com": "${{ secrets.GITHUB_TOKEN }}"}}'

should I clear it out?

david · June 6, 2021, 10:18am

@ekulnitsua

I don’t think clearing out the .env wil help.

I think something whacky is going on here, especially if you don’t know why the app is trying to seach for ‘junk@biketrax.co.uk’

ekulnitsua · June 6, 2021, 11:08am

@david

There’s an archived user in v4 - what’s the best way to deal with this? Delete through the app, or remove from the table in Mariadb?

david · June 6, 2021, 10:11pm

Ok, As long as the user exists in the database, the migration can handle that.

Can you try running

php artisan tinker

In the root folder of your v5 installation. If it doesn’t throw a MySQL error, try running

Account::all();

This sequence will confirm the v5 app is connecting properly to the DB.

ekulnitsua · June 7, 2021, 7:42am

luke@BIKETRAX-SERVER:/var/www/html/ninjav5$ php artisan tinker
Psy Shell v0.10.8 (PHP 7.4.18 — cli) by Justin Hileman
>>> Account::all();
[!] Aliasing 'Account' to 'App\Models\Account' for this Tinker session.
=> Illuminate\Database\Eloquent\Collection {#5394
     all: [
       App\Models\Account {#5395
         id: 1,
         plan: null,
         plan_term: null,
         plan_started: null,
         plan_paid: null,
         plan_expires: null,
         user_agent: "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/90.0.4430.212 Safari/537.36",
         key: "VQ5IHEpT3qJDs1mRutH2y0K0lnGTXWRc",
         payment_id: null,
         default_company_id: 1,
         trial_started: null,
         trial_plan: null,
         plan_price: null,
         num_users: 1,
         utm_source: null,
         utm_medium: null,
         utm_campaign: null,
         utm_term: null,
         utm_content: null,
         latest_version: "5.1.70",
         report_errors: 0,
         referral_code: "",
         created_at: "2021-06-03 18:15:19.835873",
         updated_at: "2021-06-07 07:40:03.301105",
         is_scheduler_running: 1,
         trial_duration: null,
         +hashed_id: "VolejRejNm",
       },
     ],
   }
>>> exit
Exit:  Goodbye
luke@BIKETRAX-SERVER:/var/www/html/ninjav5$

david · June 7, 2021, 8:46am

@ekulnitsua

I am baffled. I haven’t seen this issue before, and do not understand how the app could get into such a state.

ekulnitsua · June 7, 2021, 8:52am

@david - Grand! Glad to be of service

Well we made a couple of new quotes yesterday, so i’m going to have to suspend migration pending the customers approving them anyway.

In the meantime, if you’ve got the will to get to the bottom of it, is there anything else that might help with debugging?

david · June 7, 2021, 9:07am

@ekulnitsua

One thing to try - can you create a different MySQL user with full access only to your V5 database, and then update your .env vars to match.

ekulnitsua · June 7, 2021, 9:27am

Yep, no dice with that one either, still throwing the same error:

SQLSTATE[HY000] [1045] Access denied for user 'ninja'@'localhost' (using password: YES) (SQL: select exists(select * from `users` where (`email` = junk@biketrax.co.uk) and `users`.`deleted_at` is null) as `exists`)
        SQLSTATE[HY000] [1045] Access denied for user 'ninja'@'localhost' (using password: YES) (SQL: select exists(select * from `users` where (`email` = junk@biketrax.co.uk) and `users`.`deleted_at` is null) as `exists`)

It seems to me this is an error/corruption with the v4 database though - is there a data integrity check tool for v4?

david · June 7, 2021, 9:41am

What is the name of the user you have configured?

Have you modified any other files in the v5 system?

ekulnitsua · June 7, 2021, 9:42am

New user: ninjadebug

No other modified files.

david · June 7, 2021, 9:04pm

Have you ever enabled the multi_db flag?

Can you confirm in the companies table that the db column has db-ninja-01 as its value.

ekulnitsua · June 8, 2021, 10:47am

Multi_db flag: Not as far as I’m aware - but i was fiddling around for ages trying to get it to run, so it’s not impossible. Shall i do a fresh install?
Companies table in v5 has db-ninja-01 in db it’s only row

v4 table doesn’t have a db column

david · June 8, 2021, 11:00am

Yeh, perhaps a free install with exorcise the demons

ekulnitsua · June 8, 2021, 11:11am

sudo composer install -exorcise