2e253b3c-55fc-46c6-806c-f9d32de29a88

Is the interval the same between posts? Ie. every 10 minutes?

hey, you could probably use Railway's cron jobs for this

https://docs.railway.app/reference/cron-jobs

its really easy to setup and monitor

the interval can be defined by the user

daily at X time

2 daily at X time

or weekly at X time

so yes the intervals are the same but have to be determined by the user, not the bot

oh its per user and not a fixed time

Are you trying to connect to the database from your local machine, or from a service deployed on railway?

no per discord server. (sorry)

not local

I use github and railway

and only 1 schedule an be active so everytime you set a schedule the previous one gets overwritten.

all that I got covered in my first code

But then I ran into the issue that if railway has a "hick up" and the bot loses connection the schedule is lost.

what do you mean by "schedule is lost"? its erased from the database?

at first I didn't have a database, just memory

but then the schedule got lost so someone suggested saving the schedule in an SQL database to prevent losing it after loosing connection

I had a perfectly working code just the way I wanted it

But then railway went offline for a few seconds and the schedule got lost

And then it hit me that that is not convenient 🙈

what error are u getting when connecting to mysql? (you could probably use sqlite for this tbh)

I'm getting a connection error when trying to connect to my MySQL database on Railway. The error states 'Access denied for user 'root'@'...' (using password: YES)'. I’ve checked the credentials multiple times and even tried starting over with a fresh setup, but I still can’t connect.

send your mysql connection code

def create_connection():

connection = None

try:

connection = mysql.connector.connect(

host=os.environ.get("DB_HOST"),

user=os.environ.get("DB_USERNAME"),

password=os.environ.get("DB_PASSWORD"),

database=os.environ.get("Database")

)

print("Connection to MySQL DB successful")

except Error as e:

print(f"The error '{e}' occurred")

return connection

did you do a print of all the env variables in there at runtime in Railway? maybe Database is causing case sensitive issues

okay I'm making progress, I changed the Database to database and I am getting a bit further 🙂

I will try some other stuff.

Thank you for the help (sometimes a second pair of eyes does wonders)

great! i would recommend you to follow the convention of every env being uppercase, it prevents those pitfalls

you're also missing the database port variable

I successfully connected to the database using your feedback—thank you! However, I'm encountering a NameError indicating that guild_id is not defined during the insertion process.

I have the following code for creating the schedules table:

cursor.execute('''

CREATE TABLE IF NOT EXISTS schedules (

guild_id BIGINT PRIMARY KEY,

channel_id BIGINT NOT NULL,

hour INT NOT NULL,

minute INT NOT NULL,

timezone VARCHAR(50) NOT NULL,

frequency ENUM('daily', '2-daily', 'weekly') NOT NULL

)

''')

And this for the callback:

async def schedule_callback(interaction, frequency, hour, minute, timezone):

await interaction.response.defer()

ctx = interaction.channel
guild_id = interaction.guild.id

whats your insertion code

connection = create_connection()

cursor = connection.cursor()

cursor.execute('''

INSERT INTO schedules (guild_id, channel_id, hour, minute, timezone, frequency)

VALUES (%s, %s, %s, %s, %s, %s)

ON DUPLICATE KEY UPDATE

guild_id = VALUES(guild_id), # Adding guild_id here

channel_id = VALUES(channel_id),

hour = VALUES(hour),

minute = VALUES(minute),

timezone = VALUES(timezone),

frequency = VALUES(frequency)

''', (guild_id, channel_id, hour, minute, timezone, frequency))

connection.commit()

cursor.close()

connection.close()

try this one:

cursor.execute('''
    INSERT INTO schedules (`guild_id`, `channel_id`, `hour`, `minute`, `timezone`, `frequency`)
    VALUES (%s, %s, %s, %s, %s, %s)
    ON DUPLICATE KEY UPDATE
    `guild_id = `guild_id`,
    `channel_id` = `channel_id`,
    `hour` = `hour`,
    `minute` = `minute`,
    `timezone` = `timezone`,
    `frequency` = `frequency`
''', (guild_id, channel_id, hour, minute, timezone, frequency))

its been a while since i've written a mysql query 😄

or maybe this one:

cursor.execute('''
    INSERT INTO schedules (guild_id, channel_id, hour, minute, timezone, frequency)
    VALUES (%s, %s, %s, %s, %s, %s)
    ON DUPLICATE KEY UPDATE
    guild_id = VALUES.guild_id,
    channel_id = VALUES.channel_id,
    hour = VALUES.hour,
    minute = VALUES.minute,
    timezone = VALUES.timezone,
    frequency = VALUES.frequency
''', (guild_id, channel_id, hour, minute, timezone, frequency))

cursor.execute('''

INSERT INTO Schedules (guild_id, channel_id, hour, minute, timezone, frequency)

VALUES (%s, %s, %s, %s, %s, %s)

ON DUPLICATE KEY UPDATE

guild_id = VALUES.guild_id,

channel_id = VALUES.channel_id,

hour = VALUES.hour,

minute = VALUES.minute,

timezone = VALUES.timezone,

frequency = VALUES.frequency

''', (guild_id, channel_id, hour, minute, timezone, frequency))

connection.commit()

cursor.close()

connection.close()

this gave the same error as before.

weird, its activately saying that guild_id is not defined, can you do a print on guild_id?

and just to be sure, its right below this?

async def schedule_callback(interaction, frequency, hour, minute, timezone):
    await interaction.response.defer()

    ctx = interaction.channel
    guild_id = interaction.guild.id
    
    <...query?>

async def schedule_callback(interaction, frequency, hour, minute, timezone):

await interaction.response.defer()

if frequency == "daily":
    days_interval = 1
elif frequency == "2-daily":
    days_interval = 2
elif frequency == "weekly":
    days_interval = 7

ctx = interaction.channel
guild_id = interaction.guild.id
channel_id = ctx.id  # Get the current channel ID

await interaction.followup.send(f"Scheduling {frequency} posts at {hour}:{minute} in {timezone}.", ephemeral=True)
    # Print values for debugging
print(f"Guild ID: {guild_id}")
print(f"Channel ID: {channel_id}")
print(f"Hour: {hour}")
print(f"Minute: {minute}")
print(f"Timezone: {timezone}")
print(f"Frequency: {frequency}")
# Ensure only one schedule exists for each guild
reset_schedule_for_guild(guild_id)

#Save the new task
scheduled_tasks[guild_id] = asyncio.create_task(schedule_fact(ctx, int(hour), int(minute), timezone, days_interval, guild_id))

#Save schedule to the database (with channel_id)

connection = create_connection()

cursor = connection.cursor()

cursor.execute('''

INSERT INTO schedules (guild_id, channel_id, hour, minute, timezone, frequency)

VALUES (%s, %s, %s, %s, %s, %s)

ON DUPLICATE KEY UPDATE

guild_id = VALUES.guild_id,

channel_id = VALUES.channel_id,

hour = VALUES.hour,

minute = VALUES.minute,

timezone = VALUES.timezone,

frequency = VALUES.frequency

''', (guild_id, channel_id, hour, minute, timezone, frequency))

connection.commit()

cursor.close()

connection.close()

I think I'm going crazy 🙈

Going to close this out as we can't provide coding assistance here.

I would suggest searching sites like stack overflow for these kinds of questions.