Setting environment variables.

[xAzigo]

Hi,

I'm Having trouble setting the environment variables. i have ran:

dtd2mysql DATABASE_NAME=fares
dtd2mysql DATABASE_USERNAME=root

dtd2mysql SET DATABASE_NAME=fares
dtd2mysql SET DATABASE_USERNAME=root

and the only response i get back is the default list of things to type

when i run

dtd2mysql --fares-clean

i get

Error: Please set the DATABASE_NAME environment variable. at Container.get databaseConfiguration [as databaseConfiguration] (/usr/local/lib/node_modules/dtd2mysql/dist/src/cli/Container.js:111:19) at Container.getDatabaseConnection (/usr/local/lib/node_modules/dtd2mysql/dist/src/cli/Container.js:104:82) at Container.descriptor.(anonymous function) (/usr/local/lib/node_modules/dtd2mysql/node_modules/memoized-class-decorator/index.js:28:42) at Container.getCleanFaresCommand (/usr/local/lib/node_modules/dtd2mysql/dist/src/cli/Container.js:60:69) at Container.descriptor.(anonymous function) (/usr/local/lib/node_modules/dtd2mysql/node_modules/memoized-class-decorator/index.js:28:42) at Container.getCommand (/usr/local/lib/node_modules/dtd2mysql/dist/src/cli/Container.js:29:47) at Container.descriptor.(anonymous function) (/usr/local/lib/node_modules/dtd2mysql/node_modules/memoized-class-decorator/index.js:28:42) at Object.<anonymous> (/usr/local/lib/node_modules/dtd2mysql/dist/src/index.js:6:6) at Module._compile (module.js:652:30) at Object.Module._extensions..js (module.js:663:10)

Could i get some assistance please.

[linusnorton]

Hi,

Assuming you are using Linux or a Mac, the environment variables need to be set at the start:

DATABASE_NAME=fares SFTP_USERNAME=something dtd2mysql --get-fares

[xAzigo]

Hello,

Thanks for your reply, i guess it was simpler than i thought it would be.

Running this:

DATABASE_USERNAME=root DATABASE_PASSWORD=XXXXX DATABASE_NAME=fares dtd2mysql --fares-clean

Produces this:

`{ Error: connect ECONNREFUSED ::1:3306
at PromisePool.query (/usr/local/lib/node_modules/dtd2mysql/node_modules/mysql2/promise.js:346:20)
at CleanFaresCommand.applyRestrictionDates (/usr/local/lib/node_modules/dtd2mysql/dist/src/cli/CleanFaresCommand.js:68:51)
at CleanFaresCommand.run (/usr/local/lib/node_modules/dtd2mysql/dist/src/cli/CleanFaresCommand.js:55:22)
at container.getCommand.then.c (/usr/local/lib/node_modules/dtd2mysql/dist/src/index.js:7:18)
at
at process._tickCallback (internal/process/next_tick.js:188:7)
at Function.Module.runMain (module.js:695:11)
at startup (bootstrap_node.js:191:16)
at bootstrap_node.js:612:3
message: 'connect ECONNREFUSED ::1:3306',
code: 'ECONNREFUSED',
errno: 'ECONNREFUSED',
sqlState: undefined,
sqlMessage: undefined }

`

[linusnorton]

Is your server running on localhost?

[xAzigo]

As far as i’m aware. Is there anything special I need to set from the “Mysql” side of this? I’ve created a database named fares with the username/pwd allowing for root. From: Linus Norton Sent: 16 September 2018 08:41 To: planarnetwork/dtd2mysql Cc: Lerring; Author Subject: Re: [planarnetwork/dtd2mysql] Setting environment variables. (#40) Is your server running on localhost? — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub, or mute the thread.

[linusnorton]

Nothing special. As far as I can see the connection is being refused so it sounds like MySQL is either not running or running on a different port. The default is 3306. Maybe try setting DATABASE_HOSTNAME=localhost or DATABASE_HOSTNAME=127.0.0.1 although it shouldn't make a difference with a default MySQL install.

[PoNienC]

Hi,

I am running in MacOS and I have the same trouble and I have tried all the above solutions. But I still get errors:

$ DATABASE_USERNAME=root DATABASE_PASSWORD=****** DATABASE_NAME=fares dtd2mysql --fares-clean

{ Error: Unknown database 'fares'
at PromisePool.query (/usr/local/lib/node_modules/dtd2mysql/node_modules/mysql2/promise.js:323:22)
at CleanFaresCommand.applyRestrictionDates (/usr/local/lib/node_modules/dtd2mysql/dist/src/cli/CleanFaresCommand.js:68:51)
at CleanFaresCommand.run (/usr/local/lib/node_modules/dtd2mysql/dist/src/cli/CleanFaresCommand.js:55:22)
at container.getCommand.then.c (/usr/local/lib/node_modules/dtd2mysql/dist/src/index.js:7:18)
at process._tickCallback (internal/process/next_tick.js:43:7)
at Function.Module.runMain (internal/modules/cjs/loader.js:777:11)
at executeUserCode (internal/bootstrap/node.js:342:17)
at startExecution (internal/bootstrap/node.js:276:5)
at startup (internal/bootstrap/node.js:227:5)
at bootstrapNodeJSCore (internal/bootstrap/node.js:743:3)
message: "Unknown database 'fares'",
code: 'ER_BAD_DB_ERROR',
errno: 1049,
sqlState: '42000',
sqlMessage: "Unknown database 'fares'" }

Looks like it couldn't find the database. I would appreciate any solutions. Thanks.

[linusnorton]

In this case it looks like it can connect to the server but the fares database doesn't exist

[PoNienC]

Hi linusnorton,
Thanks for the reply. In this case, did you mean that the fares database doesn't exist on the server or I have to create a local database in my laptop to fetch the data? Could you please tell me more specifically how can I fix this problem? I really appreciate your help.

[linusnorton]

Unless you are specifying a remote database server it will assume that there is a server running locally with a database set up called fares.

I believe you need to install mysql or mariadb and then run the CREATE DATABASE fares; query.

[linusnorton]

Although it's not clear from the error message, it is failing to connect to the SFTP server.

You will need to set your DTD credentials with SFTP_USERNAME and SFTP_PASSWORD.

[xAzigo]

I'll have another look at attempting this when i get the time, could be i was missing SFTP Usernames / Passwords also

[PoNienC]

Thank you for the replies, I also found that to --get ot --download require SFTP_USERNAME and SFTP_PASSWORD. May I ask how can I get/set SFTP username and password?

[linusnorton]

Sign up to http:https://data.atoc.org/

[PoNienC]

I have no problem to use my username and password to login the above webpage and download the data (as the image). However, I still got error. Any idea how it happends?

eduroam-int-dhcp-97-183-81:dtd2mysql pnc$ DATABASE_NAME=fares SFTP_USERNAME=PNCHEN SFTP_PASSWORD=********* dtd2mysql --get-fares
{ Error: All configured authentication methods failed
at tryNextAuth (/usr/local/lib/node_modules/dtd2mysql/node_modules/ssh2/lib/client.js:392:17)
at SSH2Stream.onUSERAUTH_FAILURE (/usr/local/lib/node_modules/dtd2mysql/node_modules/ssh2/lib/client.js:599:5)
at SSH2Stream.emit (events.js:189:13)
at parsePacket (/usr/local/lib/node_modules/dtd2mysql/node_modules/ssh2-streams/lib/ssh.js:3930:10)
at SSH2Stream._transform (/usr/local/lib/node_modules/dtd2mysql/node_modules/ssh2-streams/lib/ssh.js:671:13)
at SSH2Stream.Transform._read (_stream_transform.js:190:10)
at SSH2Stream._read (/usr/local/lib/node_modules/dtd2mysql/node_modules/ssh2-streams/lib/ssh.js:253:15)
at SSH2Stream.Transform._write (_stream_transform.js:178:12)
at doWrite (_stream_writable.js:415:12)
at writeOrBuffer (_stream_writable.js:399:5) level: 'client-authentication' }

[linusnorton]

Can you try connecting manually?

I believe the default SFTP server is set to to DTD you might need to use a different one. You can set SFTP_HOSTNAME to override this

[PoNienC]

I am not sure if this setting of SFTP server is correct? It looks like the port or something else is incorrect. Could you please advice me how to alter it? Or any other issues I have to solve?

eduroam-int-dhcp-97-183-81:dtd2mysql pnc$ DATABASE_NAME=fares SFTP_USERNAME=PNCHEN SFTP_PASSWORD=********* SFTP_HOSTNAME=dtd_hostname dtd2mysql --get-fares
{ Error: getaddrinfo ENOTFOUND dtd_hostname dtd_hostname:22
at GetAddrInfoReqWrap.onlookup [as oncomplete] (dns.js:57:26)
errno: 'ENOTFOUND',
code: 'ENOTFOUND',
syscall: 'getaddrinfo',
hostname: 'dtd_hostname',
host: 'dtd_hostname',
port: 22,
level: 'client-socket' }

[linusnorton]

It looks like you're setting SFTP_HOSTNAME to dtd_hostname - it needs to be a valid hostname. I can't remember what the data.atoc.org SFTP server host name is.