Importing SQL Database for InvoiceNinja

[ahelton]

I'm transitioning from A2 Hosting to Cloudron. I've been using InvoiceNinja for a couple of years now. I performed a mySQL dump on my A2 account, but am unsure how to import to my Cloudron install of InvoiceNinja. Since I didn't assign a password to the database during the install, I have no idea what the password is. I'd also prefer to rename the database to something more readable, but am unsure how to do this. Any help will be much appreciated. (Note: I think I will also need to transfer my .env file as well...)

[girish]

Hi @ahelton,
On Cloudron, the database is managed by the platform and you don't create/remove database by yourself. It's part of the app management lifecycle in Cloudron.

1. First install invoiceninja on Cloudron. This step creates a database internally. You don't need to worry about the database username/password etc. The db itself is available only to this invoiceninja app and not accessible by any other app installed on the Cloudron and not exposed to the internet as well (it's designed this way for security).

2. Open the web terminal for Invoice Ninja (https://cloudron.io/documentation/apps/#web-terminal).

3. Then upload your dump file to tmp (it's a button on the top of the terminal).

4. Now import this into the database. For this, click the MySQL button on the top. This will put a command line. Just append the path to the dump and press enter. So, it will look like this:

root@7da526a22202:/app/code# mysql --user=${MYSQL_USERNAME} --password=${MYSQL_PASSWORD} --host=${MYSQL_HOST} ${MYSQL_DATABASE} < /tmp/dump.mysql

5. The env file is located at /app/data/.env. In general, you don't need to edit this file unless you have some custom settings. This file will be updated by Cloudron automatically based on how you configure InvoiceNinja (for example, change of domain name etc). If you do edit this file, be sure to restart InvoiceNinja using the 'Restart' button on the terminal.

[ahelton]

@girish Thanks! I was close. I had uploaded my dump file to the /tmp file and was fiddling around with the mySQL button. I'll try your suggestions.

And thanks for the tip on the .env file.

I'll give this a shot now.

[ahelton]

I'm getting the following error (on a brand new install),

Error 1050 (42s01) at line 31: Table 'accounts' already exists

Not sure exactly what this is referring to, but I haven't created any accounts as of yet.

[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: