search

mikeizbicki / cmc-csci143

big data course materials
40 stars 76 forks source link

'Port Connection Refused' Error #504

Closed AvidThinkerArsum closed 7 months ago

AvidThinkerArsum commented 7 months ago

Hi All,

I'm working on the hw assignment and am coming across this port rejected error on github actions. This is the port I designated for the pg_normalized_batch. Initially, I was passing the previous test cases but now I'm failing them all. I'm not sure why that is the case. I have attached the error as follows:

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/home/runner/work/twitter_postgres_parallel/twitter_postgres_parallel/load_tweets_batch.py", line 415, in <module>
    connection = engine.connect()
  File "/home/runner/.local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 3325, in connect
    return self._connection_cls(self, close_with_result=close_with_result)
  File "/home/runner/.local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 96, in __init__
    else engine.raw_connection()
  File "/home/runner/.local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 3404, in raw_connection
    return self._wrap_pool_connect(self.pool.connect, _connection)
  File "/home/runner/.local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 3374, in _wrap_pool_connect
    Connection._handle_dbapi_exception_noconnection(
  File "/home/runner/.local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 2208, in _handle_dbapi_exception_noconnection
    util.raise_(
  File "/home/runner/.local/lib/python3.10/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
    raise exception
  File "/home/runner/.local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 3371, in _wrap_pool_connect
    return fn()
  File "/home/runner/.local/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 327, in connect
    return _ConnectionFairy._checkout(self)
  File "/home/runner/.local/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 894, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "/home/runner/.local/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 493, in checkout
    rec = pool._do_get()
  File "/home/runner/.local/lib/python3.10/site-packages/sqlalchemy/pool/impl.py", line 145, in _do_get
    with util.safe_reraise():
  File "/home/runner/.local/lib/python3.10/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
    compat.raise_(
  File "/home/runner/.local/lib/python3.10/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
    raise exception
  File "/home/runner/.local/lib/python3.10/site-packages/sqlalchemy/pool/impl.py", line 143, in _do_get
    return self._create_connection()
  File "/home/runner/.local/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 273, in _create_connection
    return _ConnectionRecord(self)
  File "/home/runner/.local/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 388, in __init__
    self.__connect()
  File "/home/runner/.local/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 690, in __connect
    with util.safe_reraise():
  File "/home/runner/.local/lib/python3.10/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
    compat.raise_(
  File "/home/runner/.local/lib/python3.10/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
    raise exception
  File "/home/runner/.local/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 686, in __connect
    self.dbapi_connection = connection = pool._invoke_creator(self)
  File "/home/runner/.local/lib/python3.10/site-packages/sqlalchemy/engine/create.py", line 574, in connect
    return dialect.connect(*cargs, **cparams)
  File "/home/runner/.local/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 598, in connect
    return self.dbapi.connect(*cargs, **cparams)
  File "/home/runner/.local/lib/python3.10/site-packages/psycopg2/__init__.py", line 122, in connect
    conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) connection to server at "localhost" (::1), port 9999 failed: Connection refused
    Is the server running on that host and accepting TCP/IP connections?
connection to server at "localhost" (127.0.0.1), port 9999 failed: Connection refused
    Is the server running on that host and accepting TCP/IP connections?

(Background on this error at: https://sqlalche.me/e/14/e3q8)
Traceback (most recent call last):
  File "/home/runner/.local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 3371, in _wrap_pool_connect
    return fn()
  File "/home/runner/.local/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 327, in connect
    return _ConnectionFairy._checkout(self)
  File "/home/runner/.local/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 894, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "/home/runner/.local/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 493, in checkout
    rec = pool._do_get()
  File "/home/runner/.local/lib/python3.10/site-packages/sqlalchemy/pool/impl.py", line 145, in _do_get
    with util.safe_reraise():
  File "/home/runner/.local/lib/python3.10/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
    compat.raise_(
  File "/home/runner/.local/lib/python3.10/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
    raise exception
  File "/home/runner/.local/lib/python3.10/site-packages/sqlalchemy/pool/impl.py", line 143, in _do_get
    return self._create_connection()
  File "/home/runner/.local/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 273, in _create_connection
    return _ConnectionRecord(self)
  File "/home/runner/.local/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 388, in __init__
    self.__connect()
  File "/home/runner/.local/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 690, in __connect
    with util.safe_reraise():
  File "/home/runner/.local/lib/python3.10/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
    compat.raise_(
  File "/home/runner/.local/lib/python3.10/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
    raise exception
  File "/home/runner/.local/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 686, in __connect
    self.dbapi_connection = connection = pool._invoke_creator(self)
  File "/home/runner/.local/lib/python3.10/site-packages/sqlalchemy/engine/create.py", line 574, in connect
    return dialect.connect(*cargs, **cparams)
  File "/home/runner/.local/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 598, in connect
    return self.dbapi.connect(*cargs, **cparams)
  File "/home/runner/.local/lib/python3.10/site-packages/psycopg2/__init__.py", line 122, in connect
    conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
psycopg2.OperationalError: connection to server at "localhost" (::1), port 9999 failed: Connection refused
    Is the server running on that host and accepting TCP/IP connections?
connection to server at "localhost" (127.0.0.1), port 9999 failed: Connection refused
    Is the server running on that host and accepting TCP/IP connections?
ains-arch commented 7 months ago

If you didn't change the ports, it may be that there's nothing on that connection because the containers aren't successfully building. You should do the whole bring the containers down / remove the volumes / build the containers / bring them up thing and then run docker ps and see if all three are actually up. If not, you can look at docker logs to figure out what the error is.

AvidThinkerArsum commented 7 months ago

@ains-arch I did change the ports. In this case, I set it to be '9999'. Also, even if lets say the volumes are messed up, shouldn't it still run on the github actions even if it doesn't on the lambda server. I'm not sure why its saying the following precisely:

psycopg2.OperationalError: connection to server at "localhost" (::1), port 9999 failed: Connection refused
    Is the server running on that host and accepting TCP/IP connections?
connection to server at "localhost" (127.0.0.1), port 9999 failed: Connection refused
    Is the server running on that host and accepting TCP/IP connections?
ains-arch commented 7 months ago

I meant if you hadn't changed the ports since the last time it was working. If your docker-compose ports match the ports in your .sh files then I don't think that's the problem.

If the container isn't up there's not going to be anything listening at that port for stuff to connect to when it runs in github actions. Clearing the volumes and rebuilding locally lets you start with clean containers, where you can then check if the containers build properly, and hopefully be able to reproduce what's happening in github. If it is coming up locally then I'm not sure what else to try.

Again, because this wasn't obvious to me when I was doing it -

docker-compose down
docker volume rm [names of volumes]
docker-compose build
docker-compose up -d
docker ps

And if you're missing a container,

docker logs

This is especially important if you've changed the schema, because any bugs there will only be obvious if you a) rebuild the container and b) actually check if the containers are up.

AvidThinkerArsum commented 7 months ago

I'm not sure what you mean by 'If the container isn't up there's not going to be anything listening at that port for stuff to connect to when it runs in github actions.' I'm a bit confused with what containers running on the lambda server have to do with github actions? Am I correct to assume that containers running on lambda server and passing the tests translates into containers being correctly built through github actions and passing them? My docker-compose ports do match the ports in the .sh files and when I did docker ps I did see 3 containers and this was after I emptied all the volumes.

ains-arch commented 7 months ago

Sorry, github actions doesn't talk to the containers on the lambda server, I didn't mean to imply that. I just meant that if the containers weren't coming up properly in github actions I think that's what the error would look like.

If you've removed volumes and rebuilt everything before bringing the containers up on the lambda server and they pass the tests there then yeah I think mostly that means they should also pass on github, with a few exceptions about like sqlalchemy versions, but that would probably throw a different error in github. So I'm not sure.

AvidThinkerArsum commented 7 months ago

Yes, I feel like I did all of the .sh files correct so my ports should be good. Right now all of the github actions fail. I haven't tested my code on the lambda server though. How exactly would I go about doing that? And thank you, I appreciate your help.

ains-arch commented 7 months ago

bring down containers and rm volumes. then

docker-compose build
docker-compose up -d
docker ps

note the name for the container you want to run the tests on, probably something like twitter_postgres_parallel_pg_[denormalized or normalized or normalized_batch]_1

then run the tests, and note that they all fail because you haven't loaded the tweets yet

docker exec [container name] ./run_tests.sh

run the relevant load file and try again

./load_tweets_[parallel or sequential].sh
docker exec [container name] ./run_tests.sh

and then hopefully they pass. Or you get a new and interesting error along the way.

AvidThinkerArsum commented 7 months ago

Yes, so I did this and when I do docker ps, the normalized_batch container does not show up even though it shows up with docker ps -a. Then, when I loaded the ./load_tweets_sequential.sh it worked except for normalized_batch (obviously as that container was not up.) This is the error I got (sorry for the long copy):

================================================================================
load pg_normalized_batch
================================================================================
Traceback (most recent call last):
  File "/home/anna2021/.local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 3371, in _wrap_pool_connect
    return fn()
  File "/home/anna2021/.local/lib/python3.6/site-packages/sqlalchemy/pool/base.py", line 327, in connect
    return _ConnectionFairy._checkout(self)
  File "/home/anna2021/.local/lib/python3.6/site-packages/sqlalchemy/pool/base.py", line 894, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "/home/anna2021/.local/lib/python3.6/site-packages/sqlalchemy/pool/base.py", line 493, in checkout
    rec = pool._do_get()
  File "/home/anna2021/.local/lib/python3.6/site-packages/sqlalchemy/pool/impl.py", line 146, in _do_get
    self._dec_overflow()
  File "/home/anna2021/.local/lib/python3.6/site-packages/sqlalchemy/util/langhelpers.py", line 72, in __exit__
    with_traceback=exc_tb,
  File "/home/anna2021/.local/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
    raise exception
  File "/home/anna2021/.local/lib/python3.6/site-packages/sqlalchemy/pool/impl.py", line 143, in _do_get
    return self._create_connection()
  File "/home/anna2021/.local/lib/python3.6/site-packages/sqlalchemy/pool/base.py", line 273, in _create_connection
    return _ConnectionRecord(self)
  File "/home/anna2021/.local/lib/python3.6/site-packages/sqlalchemy/pool/base.py", line 388, in __init__
    self.__connect()
  File "/home/anna2021/.local/lib/python3.6/site-packages/sqlalchemy/pool/base.py", line 691, in __connect
    pool.logger.debug("Error on connect(): %s", e)
  File "/home/anna2021/.local/lib/python3.6/site-packages/sqlalchemy/util/langhelpers.py", line 72, in __exit__
    with_traceback=exc_tb,
  File "/home/anna2021/.local/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
    raise exception
  File "/home/anna2021/.local/lib/python3.6/site-packages/sqlalchemy/pool/base.py", line 686, in __connect
    self.dbapi_connection = connection = pool._invoke_creator(self)
  File "/home/anna2021/.local/lib/python3.6/site-packages/sqlalchemy/engine/create.py", line 574, in connect
    return dialect.connect(*cargs, **cparams)
  File "/home/anna2021/.local/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 598, in connect
    return self.dbapi.connect(*cargs, **cparams)
  File "/home/anna2021/.local/lib/python3.6/site-packages/psycopg2/__init__.py", line 122, in connect
    conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
psycopg2.OperationalError: could not connect to server: Connection refused
    Is the server running on host "localhost" (127.0.0.1) and accepting
    TCP/IP connections on port 9999?

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "load_tweets_batch.py", line 415, in <module>
    connection = engine.connect()
  File "/home/anna2021/.local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 3325, in connect
    return self._connection_cls(self, close_with_result=close_with_result)
  File "/home/anna2021/.local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 96, in __init__
    else engine.raw_connection()
  File "/home/anna2021/.local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 3404, in raw_connection
    return self._wrap_pool_connect(self.pool.connect, _connection)
  File "/home/anna2021/.local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 3375, in _wrap_pool_connect
    e, dialect, self
  File "/home/anna2021/.local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 2209, in _handle_dbapi_exception_noconnection
    sqlalchemy_exception, with_traceback=exc_info[2], from_=e
  File "/home/anna2021/.local/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
    raise exception
  File "/home/anna2021/.local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 3371, in _wrap_pool_connect
    return fn()
  File "/home/anna2021/.local/lib/python3.6/site-packages/sqlalchemy/pool/base.py", line 327, in connect
    return _ConnectionFairy._checkout(self)
  File "/home/anna2021/.local/lib/python3.6/site-packages/sqlalchemy/pool/base.py", line 894, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "/home/anna2021/.local/lib/python3.6/site-packages/sqlalchemy/pool/base.py", line 493, in checkout
    rec = pool._do_get()
  File "/home/anna2021/.local/lib/python3.6/site-packages/sqlalchemy/pool/impl.py", line 146, in _do_get
    self._dec_overflow()
  File "/home/anna2021/.local/lib/python3.6/site-packages/sqlalchemy/util/langhelpers.py", line 72, in __exit__
    with_traceback=exc_tb,
  File "/home/anna2021/.local/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
    raise exception
  File "/home/anna2021/.local/lib/python3.6/site-packages/sqlalchemy/pool/impl.py", line 143, in _do_get
    return self._create_connection()
  File "/home/anna2021/.local/lib/python3.6/site-packages/sqlalchemy/pool/base.py", line 273, in _create_connection
    return _ConnectionRecord(self)
  File "/home/anna2021/.local/lib/python3.6/site-packages/sqlalchemy/pool/base.py", line 388, in __init__
    self.__connect()
  File "/home/anna2021/.local/lib/python3.6/site-packages/sqlalchemy/pool/base.py", line 691, in __connect
    pool.logger.debug("Error on connect(): %s", e)
  File "/home/anna2021/.local/lib/python3.6/site-packages/sqlalchemy/util/langhelpers.py", line 72, in __exit__
    with_traceback=exc_tb,
  File "/home/anna2021/.local/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
    raise exception
  File "/home/anna2021/.local/lib/python3.6/site-packages/sqlalchemy/pool/base.py", line 686, in __connect
    self.dbapi_connection = connection = pool._invoke_creator(self)
  File "/home/anna2021/.local/lib/python3.6/site-packages/sqlalchemy/engine/create.py", line 574, in connect
    return dialect.connect(*cargs, **cparams)
  File "/home/anna2021/.local/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 598, in connect
    return self.dbapi.connect(*cargs, **cparams)
  File "/home/anna2021/.local/lib/python3.6/site-packages/psycopg2/__init__.py", line 122, in connect
    conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) could not connect to server: Connection refused
    Is the server running on host "localhost" (127.0.0.1) and accepting
    TCP/IP connections on port 9999?

(Background on this error at: https://sqlalche.me/e/14/e3q8)
Traceback (most recent call last):
  File "/home/anna2021/.local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 3371, in _wrap_pool_connect
    return fn()
  File "/home/anna2021/.local/lib/python3.6/site-packages/sqlalchemy/pool/base.py", line 327, in connect
    return _ConnectionFairy._checkout(self)
  File "/home/anna2021/.local/lib/python3.6/site-packages/sqlalchemy/pool/base.py", line 894, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "/home/anna2021/.local/lib/python3.6/site-packages/sqlalchemy/pool/base.py", line 493, in checkout
    rec = pool._do_get()
  File "/home/anna2021/.local/lib/python3.6/site-packages/sqlalchemy/pool/impl.py", line 146, in _do_get
    self._dec_overflow()
  File "/home/anna2021/.local/lib/python3.6/site-packages/sqlalchemy/util/langhelpers.py", line 72, in __exit__
    with_traceback=exc_tb,
  File "/home/anna2021/.local/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
    raise exception
  File "/home/anna2021/.local/lib/python3.6/site-packages/sqlalchemy/pool/impl.py", line 143, in _do_get
    return self._create_connection()
  File "/home/anna2021/.local/lib/python3.6/site-packages/sqlalchemy/pool/base.py", line 273, in _create_connection
    return _ConnectionRecord(self)
  File "/home/anna2021/.local/lib/python3.6/site-packages/sqlalchemy/pool/base.py", line 388, in __init__
    self.__connect()
  File "/home/anna2021/.local/lib/python3.6/site-packages/sqlalchemy/pool/base.py", line 691, in __connect
    pool.logger.debug("Error on connect(): %s", e)
  File "/home/anna2021/.local/lib/python3.6/site-packages/sqlalchemy/util/langhelpers.py", line 72, in __exit__
    with_traceback=exc_tb,
  File "/home/anna2021/.local/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
    raise exception
  File "/home/anna2021/.local/lib/python3.6/site-packages/sqlalchemy/pool/base.py", line 686, in __connect
    self.dbapi_connection = connection = pool._invoke_creator(self)
  File "/home/anna2021/.local/lib/python3.6/site-packages/sqlalchemy/engine/create.py", line 574, in connect
    return dialect.connect(*cargs, **cparams)
  File "/home/anna2021/.local/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 598, in connect
    return self.dbapi.connect(*cargs, **cparams)
  File "/home/anna2021/.local/lib/python3.6/site-packages/psycopg2/__init__.py", line 122, in connect
    conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
psycopg2.OperationalError: could not connect to server: Connection refused
    Is the server running on host "localhost" (127.0.0.1) and accepting
    TCP/IP connections on port 9999?
ains-arch commented 7 months ago

Okay. If it is showing up with docker ps -a but not just docker ps it means that container is stopped. You should run these, from the next homework:

docker stop $(docker ps -q)
docker rm $(docker ps -qa)
docker volume prune --all

Assuming you don't get any errors running that I think you can be confident you've for real stoped and removed all your containers and pruned all your volumes, so you're starting completely fresh.

Next, rebuild and bring them up,

docker-compose build
docker-compose up -d
docker ps

and you will presumably not see all your containers. Then you should run

docker logs

and read the logs for whichever container is missing, so you can figure out what the error is. Based on my experience, you've probably got syntax errors in the batch schema, and hopefully looking at the logs will help you figure out where.