~cloutier/bird.makeup#4: error thrown at startup

~eric-pierce 1 year, 9 months ago

I ran into this as well, but solved it by seeding the database with manually populated mastodon account, as well as one twitter account to follow. I was migrating from birdsitelive so I just copied the content over from the birdsitelive database. There are some minor differences in table structure, but after copying the content over the web-ui successfully started.

~felixmueller referenced this from #6 1 year, 9 months ago

~perler 1 year, 9 months ago

ok, so I can just connect the bird.makeup container to a db container from birdsitelive?

~felixmueller 1 year, 9 months ago

~perler: no you can't. As ~eric-pierce mentioned, there are some minor differences in the table structure. You have to first let bird.makeup create the tables and then populate the first rows manually. Hope this will be fixed in the future! Thanks a lot ~cloutier!

~vzeebrod 1 year, 9 months ago

can someone post a sql file with the minimum records? trying to figure out what needs to be in the tables but no luck

~eric-pierce 1 year, 8 months ago*

~vzeebrod Sure here are some sample records for @Gargron@mastodon.social following @npr on twitter.

followers table:

id,followings,followingssyncstatus,acct,host,inboxroute,sharedinboxroute,actorid,postingerrorcount

1,{1},{"1": 1632785881919549441},gargron,mastodon.social,/users/gargron/inbox,/inbox,https://mastodon.social/users/gargron,0

twitter_users table:

id,acct,lasttweetpostedid,lasttweetsynchronizedforallfollowersid,lastsync,fetchingerrorcount,twitteruserid

1,npr,1632785881919549441,1632785881919549441,2023-03-06 11:32:04.5,0,5392522

~cloutier assigned ~cloutier 1 year, 8 months ago

~cloutier closed duplicate ticket #2 1 year, 8 months ago

~ctron 1 year, 7 months ago

It would really be helpful if this got fixed. Here is my SQL snippet which worked around the issue:

DO $$
DECLARE
    id INTEGER;
    mastodon_account TEXT;
    mastodon_domain TEXT;
    mastodon_host TEXT;
BEGIN
    id := nextval('twitter_users_id_seq');
    mastodon_account := 'you';
    mastodon_domain := 'your.instance';
    -- depending on your setup, this may be the domain itself or a specific host
    mastodon_host := 'mastodon.your.instance';

    INSERT INTO twitter_users (
        id, acct, lasttweetpostedid, lasttweetsynchronizedforallfollowersid, lastsync, fetchingerrorcount, twitteruserid
    ) VALUES (
        id,
        'npr', -- twitter account (npr in this case)
        1646138106595139584, -- id/timestamp
        1646138106595139584, -- id/timestamp
        CURRENT_TIMESTAMP, -- timestamp
        0, -- error count
        5392522 -- twitter user id of 'npr'
    );
    
    INSERT INTO followers (
        followings, followingssyncstatus, acct, host, inboxroute, sharedinboxroute, actorid, postingerrorcount
    ) VALUES (
        ARRAY[id],
        JSON_BUILD_OBJECT(id::text, 1646138106595139584),
        mastodon_account,
        mastodon_domain,
        '/users/' || mastodon_account || '/inbox',
        '/inbox',
        'https://' || mastodon_host || '/users/' || mastodon_account,
        0
    );

    -- comment out to actually store
    ROLLBACK;
    
END
$$;

Of course you need to put in your mastodon account, and drop the ROLLBACK once you want to commit.

I also had to restart the container in order for the process to pick up the change, this feels like a transaction issue somewhere.

And don't forget to delete those rows afterwards, as your mastodon instance doesn't have an idea about this subscription.

So the steps for me had been:

Configure and run the script
Restart the container
Create an actual subscription from Mastodon (ensure that it made it into the database)
Remove manually inserted records

~rursache 1 year, 7 months ago*

~ctron I tried running your SQL snippet with the Mastodon vars updated, but I got an error message saying "ERROR: column 'twitteruserid' of relation 'twitter_users' does not exist."

Can anyone provide a fix or clear installation instructions that actually work? I've been trying for two hours to get this running and I just can't seem to figure it out. I also contributed via Patreon, if that helps...

There should also be a way to set the PostgreSQL port used when connecting, as I'm not usually running it on the default 5432 port.

Thanks to everyone for their hard work!

~eric-pierce 1 year, 7 months ago

~rursache are you running the SQL in a BirdMakeup schema or an older birdsitelive schema? I'm betting you're running it in birdsitelive as that table structure doesn't include the twitteruserid column.

~rursache 1 year, 7 months ago*

~eric-pierce I'm running it on a fresh PostgreSQL instance and DB, after I've started the bird.makeup Docker image. Can anyone provide a clean working DB export so new users like me can start with a working instance? Really weird a clean install is bugged from the start. Thanks!

~ctron 1 year, 7 months ago

I used this on a bird.makeup instance, but I did create the database instance a few weeks back. Maybe something has changed in the meantime.

Assuming you use docker-compose, you don't need to change the port of the database. However, you need to change the variable Db:Host to db (which is the name of the database service in the compose file). And remove the port mapping on the db service. This also rejects connections to the database from outside the host. You can then connect to the database using:
docker exec -ti birdsite_db_1 psql -U birdsitelive

~rursache 1 year, 7 months ago*

Hi ~ctron , I'm not using compose but raw docker run. I run PostgreSQL with multiple databases on a different port and manage them with pgAdmin. That's why I want to manually set the port to the PostgreSQL. However, that's the least of my worries right now, as I can't get the bird.makeup instance working even with a brand new PostgreSQL instance on the default port. It's stuck on error 500 with the same errors as the OP.

Important to mention that the original birdslive instance works (clean DB, different name), but I don't have a Twitter API key so I can't use it.

Can you share a valid DB export of your instance or an "almost-vanilla" one? I just want to get this working because using the official bird.makeup site is bugged for me. If I follow any account, it's stuck on "waiting for approval." I'm running my own Mastodon instance and I didn't block any domain, bots or added any filters.

~eric-pierce 1 year, 7 months ago

~rursache if you're using pgAdmin you can just insert the values I listed in https://todo.sr.ht/~cloutier/bird.makeup/4#event-228633 manually using the pgAdmin UI (open the table, hit add row, and copy/paste the column values in) for the two relevant tables and it should boot up

~eric-pierce referenced this from #4 1 year, 7 months ago

~rursache 1 year, 7 months ago

~eric-pierce Thank you for your patience. I managed to get it working closely following your instructions

~eric-pierce 1 year, 7 months ago

~rursache glad to hear!

~eric-pierce 1 year, 7 months ago

~rursache be sure to update the followers table with your information rather than Gargron's, or just delete Gargron's row after you've set up your account

~rursache 1 year, 7 months ago*

~eric-pierce I replaced his data with mine before inserting it. It looks like this:

id,followings,followingssyncstatus,acct,host,inboxroute,sharedinboxroute,actorid,postingerrorcount

1,{1},{"1": 1632785881919549441},radu,myinstance.tld,/users/radu/inbox,/inbox,https://myinstance.tld/users/radu,0

However I think I'm still doing something wrong as searching for the twitter user (@npr@twitter.myinstance.tld) brings "user not found" on web and on my mastodon client. Same for any new other twitter account

In logs there are errors coming from this lookup:

fail: Microsoft.AspNetCore.Diagnostics.ExceptionHandlerMiddleware[1]
      An unhandled exception has occurred while executing the request.
      System.IndexOutOfRangeException: Index was outside the bounds of the array.
         at BirdsiteLive.Cryptography.MagicKey..ctor(String key) in /home/build/bird.makeup/src/BirdsiteLive.Cryptography/MagicKey.cs:line 63
         at BirdsiteLive.Domain.Factories.MagicKeyFactory.GetMagicKey() in /home/build/bird.makeup/src/BirdsiteLive.Domain/Factories/MagicKeyFactory.cs:line 38
         at BirdsiteLive.Domain.CryptoService.GetUserPem(String id) in /home/build/bird.makeup/src/BirdsiteLive.Domain/CryptoService.cs:line 28
         at BirdsiteLive.Domain.UserService.GetUser(TwitterUser twitterUser) in /home/build/bird.makeup/src/BirdsiteLive.Domain/UserService.cs:line 80
         at BirdsiteLive.Controllers.UsersController.Index(String id) in /home/build/bird.makeup/src/BirdsiteLive/Controllers/UsersController.cs:line 119
         at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.TaskOfIActionResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)
         at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeActionMethodAsync>g__Awaited|12_0(ControllerActionInvoker invoker, ValueTask`1 actionResultValueTask)
         at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeNextActionFilterAsync>g__Awaited|10_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
         at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context)
         at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
         at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeInnerFilterAsync()
      --- End of stack trace from previous location ---
         at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeNextResourceFilter>g__Awaited|25_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
         at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Rethrow(ResourceExecutedContextSealed context)
         at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
         at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.InvokeFilterPipelineAsync()
      --- End of stack trace from previous location ---
         at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope)
         at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope)
         at Microsoft.AspNetCore.Routing.EndpointMiddleware.<Invoke>g__AwaitRequestTask|6_0(Endpoint endpoint, Task requestTask, ILogger logger)
         at Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context)
         at Microsoft.AspNetCore.Diagnostics.ExceptionHandlerMiddleware.<Invoke>g__Awaited|6_0(ExceptionHandlerMiddleware middleware, HttpContext context, Task task)

My full docker run command (seems like using host:port in the Db:Host env works):

docker run -d \
  --name birdmakeup \
  --restart unless-stopped \
  -p 5992:80 \
  -p 5993:443 \
  -e TZ=Europe/Bucharest \
  -e Instance:Domain="twitter.myinstance.tld" \
  -e Instance:Name="Twitter Proxy - myinstance.tld" \
  -e Instance:AdminEmail="radu@myinstance.tld" \
  -e Instance:ParallelTwitterRequests=50 \
  -e Instance:ParallelFediverseRequests=20 \
  -e Moderation:FollowersWhiteListing="@radu@myinstance.tld;@ana@myinstance.tld;myinstance.tld" \
  -e Db:Type=postgres \
  -e Db:Host=HOSTIP:MYPORT \
  -e Db:Name=birdmakeup \
  -e Db:User=birdmakeup \
  -e Db:Password=PASS \
  -v /home/radu/.birdmakeup/config/key.json:/app/key.json \
  cloutier/bird.makeup:latest

I'm using Cloudflare Tunnels to expose HOSTIP:5992 to twitter.myinstance.tld (which works and loads). Maybe I should expose the HTTPS port (5993) instead?

Also not sure if I should create a new issue instead of spamming this one?

Thanks again for your patience, happy to send you a tip on your platform of choice!

~eric-pierce 1 year, 7 months ago

~rursache it looks like birdmakeup is looking for the follower account but isn't finding it - did you also insert the row into the twitter_users table copied below:

id,acct,lasttweetpostedid,lasttweetsynchronizedforallfollowersid,lastsync,fetchingerrorcount,twitteruserid

1,npr,1632785881919549441,1632785881919549441,2023-03-06 11:32:04.5,0,5392522

~rursache 1 year, 7 months ago

~eric-pierce

I did!

~eric-pierce 1 year, 7 months ago

~rursache I don't see any obvious problems unless you haven't restarted the container after updating the rows in the tables.

Unless you copied the post ids from ~ctron's SQL, based on your screenshot it does look like bird makeup is pulling the correct information from twitter as the post id is the most recent for the NPR account (1646138106595139584). NPR actually just quit twitter so I don't expect we'll see them post any new tweets in the near term.

It probably is worth forwarding both http and https ports.

One other note: You shouldn't need to specify specific account and the entire domain in the Moderation:FollowersWhiteListing parameter, the domain alone should cover you.

~rursache 1 year, 7 months ago

~eric-pierce I did restart the container multiple times.

cached_tweets table is empty tho, could that be a sign of some issue? should the key.json file contain anything or it's just a leftover from the original birdslive project?

It also could be a ports issue but I can't forward multiple ports to the same domain/subdomain. twitter.domain.tld resolves to MYIP:5992 via Cloudflare Tunnels.

Thanks for mentioning the Moderation:FollowersWhiteListing, it does make sense indeed!

~eric-pierce 1 year, 7 months ago

~rursache cached tweets has always been empty for me too.

It looks like the volume mounting for key.json was added about a month ago via https://git.sr.ht/~cloutier/bird.makeup/commit/5d727c18aa70b7e081e256bbffed2e5161f8b03a#docker-compose.yml I don't have it mounted in a volume on my install (https://github.com/eric-pierce/Mastadon-and-MC/blob/main/docker-compose.yml#L554) though I may add it. I just checked and my key.json file does have what looks to be a cryptographic key in it, so it may be worth removing the volume piece of your docker run command and potentially deleting/re-installing the container. I'm betting the volume command is preventing key.json from being populated.

~rursache 1 year, 7 months ago

~eric-pierce you are right once again! once I removed the volume mapping the users are loading and I get tweets from the accounts I follow. They seem to be stuck at "waiting for approval" just like in this open issue (happening on the official bird.makeup instance too, reported to the dev here) but as long as it works...

thanks again for all your help!

~eric-pierce 1 year, 7 months ago

~rursache glad it's up and running! I have seen some of the "waiting for approval" issue on my side as well, I ended up unfollowing and re-following those accounts from my mastodon instance ui, and giving the account a few minutes to update and all generally works.

There is a separate issue (https://todo.sr.ht/~cloutier/bird.makeup/12) where bmu encounters some error parsing a user feed and then purges/removes the user from any future tweets, which I found impacts around 10% of the accounts I follow - that can also result in mastodon reporting "waiting for approval"

~perler 1 year, 7 months ago*

Ok, I didn't completely folllow the procedures, but can't you pack the modifications into a pull request/commit, so that the end user doesn't have jump thru all these rings?

~rursache 1 year, 7 months ago

~perler I agree that a fix must be applied on the source code but I have 0 dotnet knowledge to do it. The dev also doesn't seem too active. However I made sure to provide every step I took with examples and configs to be even easier for the next people that want to get this up and running.

On another note maybe we can move to GitHub as this platform is horrible and I'm sure more people will onboard on this project if it would be hosted on a more UI friendly and feature complete platform.

~ctron 1 year, 7 months ago

+1 for moving to GitHub, or at least GitLab.

~eric-pierce 1 year, 7 months ago

~perler the "fix" outlined in this thread is really just a band-aid/workaround (inserting dummy rows so the application starts, then adding 'real' rows and deleting the dummy rows), the issue needs to be fixed by updating the application to not require these dummy rows in the first place.

~rursache ~ctron I agree that things would be much easier on github/gitlab, but I wouldn't get any hopes up. Even though Vincent the developer does have a github account, in his most recent patreon post he mentioned that he just bought an annual subscription to this website/git management system and that he's a big fan of the sourcehut company.

~cloutier REPORTED FIXED 1 year, 4 months ago

Fixed within https://git.sr.ht/~cloutier/bird.makeup/commit/01acc03dca51109775dd678f550360eed3f190ea