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 createconnection(): connection = None try: connection = mysql.connector.connect( host=os.environ.get("DBHOST"),
user=os.environ.get("DBUSERNAME"), password=os.environ.get("DBPASSWORD"),
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 (
guildid BIGINT PRIMARY KEY, channelid 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](interaction.channel)
guild_id = [interaction.guild.id](interaction.guild.id)

whats your insertion code

connection = createconnection() cursor = connection.cursor() cursor.execute(''' INSERT INTO schedules (guildid, channelid, hour, minute, timezone, frequency) VALUES (%s, %s, %s, %s, %s, %s) ON DUPLICATE KEY UPDATE guildid = VALUES(guildid), # Adding guildid here
channelid = VALUES(channelid),
hour = VALUES(hour),
minute = VALUES(minute),
timezone = VALUES(timezone),
frequency = VALUES(frequency)
''', (guildid, channelid, 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 (guildid, channelid, hour, minute, timezone, frequency)
VALUES (%s, %s, %s, %s, %s, %s)
ON DUPLICATE KEY UPDATE
guildid = VALUES.guildid,
channelid = VALUES.channelid,
hour = VALUES.hour,
minute = VALUES.minute,
timezone = VALUES.timezone,
frequency = VALUES.frequency
''', (guildid, channelid, hour, minute, timezone, frequency))

connection.commit()
cursor.close()
connection.close()

this gave the same error as before.

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

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](interaction.channel)
guild_id = [interaction.guild.id](interaction.guild.id)
channel_id = [ctx.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 (guildid, channelid, hour, minute, timezone, frequency)
VALUES (%s, %s, %s, %s, %s, %s)
ON DUPLICATE KEY UPDATE
guildid = VALUES.guildid,
channelid = VALUES.channelid,
hour = VALUES.hour,
minute = VALUES.minute,
timezone = VALUES.timezone,
frequency = VALUES.frequency
''', (guildid, channelid, 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.