Importing SQL Database for InvoiceNinja

[msbt]

I'm guessing you have to either export the old database with drop table or remove the create table part and only import contents of the old db. Not sure though if it will work if you drop the existing tables.

[girish]

I guess there should be a step 3.5 to drop all the existing tables first.

Can you try this command as-is to drop all the tables?

mysqldump --user=${MYSQL_USERNAME} --password=${MYSQL_PASSWORD} --host=${MYSQL_HOST} --add-drop-table --no-data ${MYSQL_DATABASE} | grep -e '^DROP \| FOREIGN_KEY_CHECKS' | mysql --user=${MYSQL_USERNAME} --password=${MYSQL_PASSWORD} --host=${MYSQL_HOST} ${MYSQL_DATABASE}

[ahelton]

@girish said in Importing SQL Database for InvoiceNinja:

mysqldump --user=${MYSQL_USERNAME} --password=${MYSQL_PASSWORD} --host=${MYSQL_HOST} --add-drop-table --no-data ${MYSQL_DATABASE} | grep -e '^DROP | FOREIGN_KEY_CHECKS' | mysql --user=${MYSQL_USERNAME} --password=${MYSQL_PASSWORD} --host=${MYSQL_HOST} ${MYSQL_DATABASE}

The command runs successfully, it appears. I then run the other command afterwards. Now, however, I'm not getting any output. Does it take a while for the command to complete? My file is only 3MB, so I wouldn't think so.

Here's a pic from the terminal:

[girish]

@ahelton I gave this the steps a try now just to confirm that I got it right and I was able to migrate an invoiceninja installation using dumps from one cloudron to another. So, the commands appear correct.

Can you give the app more memory to check if that is the issue? https://cloudron.io/documentation/apps/#increasing-the-memory-limit-of-an-app It's in the Advanced pane. Maybe give it 1GB and see if that helps?

[ahelton]

@girish That did the trick! Thanks for all your help. Looks like I need to go purchase some additional memory on DigitalOcean

[ahelton]

I think I will need to edit the .env file as I need to include a Shared Secret so that my Android app can communicate with the server. I'm going to give a go to see if I can figure it out first.

[girish]

@ahelton Ah cool! BTW, the memory is not "reserved" for the app as such, it is only an upper limit. Also, you may not require that much memory after the import is done... (so maybe try with 512mb after the import).

As for the API secret, are you unable to edit the API_SECRET in /app/data/.env ? Then restart the app.

[ahelton]

Re: Importing SQL Database for InvoiceNinja

@girish Good news about the memory. I'll adjust it back after I'm done getting everything set up.

I noticed after viewing the ENV file an API_Secret already existed. I guess one is created automatically? I initially edited the ENV file to overwrite the include one with my old API_Secret thinking it would save me from having to type in that rather long string of characters on my phone. However, this didn't seem to work no matter what I tried. I eventually went back to the API_Secret that was included by default and everything works. Not sure why I couldn't use my own, but it works.

The only thing I need to get setup now is email relay. I've made several attempts, but so far no luck. I am using a Google Business account (G-Suite) for my email and have set up the appropriate MX records on my DigitalOcean account. Everything works as I am sending/receiving emails from BlueMail on my phone and also Mailbird and Thunderbird. However, I can't get it to work in InvoiceNinja. I found a tutorial on the InvoiceNinja with the following instructions:

""""
Enable SMTP Access

Log in to your Google Apps for Business admin console.
Go to Apps –> Google Apps –> G-Mail –> Advanced Settings then scroll down until you see SMTP relay service, however over it and click on Configure.
Select Only addresses in my domains then tick Require SMTP Authentication and Require TLS encryption and click on Add Setting then click Save.
Configure .env which is located in the root of your Invoice Ninja installation.

MAIL_DRIVER=smtp
MAIL_PORT=587
MAIL_ENCRYPTION=tls
MAIL_HOST=smtp-relay.gmail.com
MAIL_USERNAME=username@yourdomain.com
MAIL_FROM_NAME=username@yourdomain.com
MAIL_PASSWORD=yourpassword

It also mentions enabling 'less secure apps' in my account security settings, but I have 2FA enabled so this isn't available. I instead created an APP password and tried to use it. So far I'm just getting this error message when I click the 'test email' button in InvoiceNinja
""""

 Expected response code 250 but got code "535", with message "535-5.7.8 Username and Password not accepted. Learn more at 535 5.7.8 https://support.google.com/mail/?p=BadCredentials k78sm1762576qkh.13 - gsmtp "

One other thing. My google maps aren't working in InvoiceNinja. Do I need to include an API reference in the ENV file? I know I did on A2 Hosting, but noticed there is a Google_Maps = True (or something similar) already in my ENV file.

[girish]

@ahelton For email relay, use the Cloudron Email panel instead. Email -> Outbound relay. Once you do that, invoince ninja will automatically use gmail. This approach is better than managing invoiceninja directly because the relay can then be shared across multiple apps as well. Be sure to undo your settings in invoiceninja mail settings (alternately, just click on the app's configure button and click save by making no changes, that will restore the mail settings).

[ahelton]

@girish Okay. I am actually getting an error there as well. I initially tried my username with my normal password, and it said 'application password required,' which wasn't unexpected. I then created an application password, and now it says 'username and password not accepted.'

[ahelton]

@girish Wow! I got it to work. What a pain Google made that process. I ended up having to disable 2FA, go in to my G-Suite management console and enable less secure apps options for users, then go to my admin console for my actual email account and enable less secure apps there, then enable 2FA, then create an app password. Finally, it accepted my credentials in the Cloudron Email setting. I sent a test email in InvoiceNinja and it appears to be working.

Alright. Any ideas on how to get Google Maps working in InvoiceNinja? When I got to a client page, it appears a map attempts to load and then just altogether disappears. Do I need to add API information?

[girish]

@ahelton Is there anything in the browser console or the app logs? I will give this a shot tomorrow.

[girish]

I tried this quickly now and it seems to work here atleast out of the box: