SQLITE

[buoyant lark]

⌛ This post has been reserved for your question.

Hey @pulsar dove! Please use /close or the Close Post button above when your problem is solved. Please remember to follow the help guidelines. This post will be automatically marked as dormant after 300 minutes of inactivity.

TIP: Narrow down your issue to simple and precise questions to maximize the chance that others will reply in here.

[pulsar dove]

1Does deleting or inserting or query being called multiple times at the same time cause problems for sqlite

userID (is discord id)

PlayerName (is the player name in minecraft with a name length from 3 to 16 characters)

Cooldown (is an 11-digit timestamp)

2 Have I used data types well?

[buoyant lark]

💤 Post marked as dormant

This post has been inactive for over 300 minutes, thus, it has been archived.
If your question was not answered yet, feel free to re-open this post or create a new one.
In case your post is not getting any attention, you can try to use /help ping.
Warning: abusing this will result in moderative actions taken against you.

[buoyant lark]

💤 Post marked as dormant

This post has been inactive for over 300 minutes, thus, it has been archived.
If your question was not answered yet, feel free to re-open this post or create a new one.
In case your post is not getting any attention, you can try to use /help ping.
Warning: abusing this will result in moderative actions taken against you.

[buoyant lark]

💤 Post marked as dormant

This post has been inactive for over 300 minutes, thus, it has been archived.
If your question was not answered yet, feel free to re-open this post or create a new one.
In case your post is not getting any attention, you can try to use /help ping.
Warning: abusing this will result in moderative actions taken against you.

[woeful ember]

How are you inserting/deleting stuff?

if you have multiple INSERT INTO statements with the same content, it may fail/throw an exception if it violates constraints

or you might have multiple rows if it doesn't violate constraints (e.g. you use AUTOINCREMENT for the primary key and no relevant unique constraints)

If you have multiple DELETE FROM statements that delete the same data, the later ones would just finished with "0 rows modified"

[pulsar dove]

are never the same in value

[woeful ember]

?

[pulsar dove]

public void insertUser(long userID, String playerName, long coolDown) throws SQLException { String sql = "INSERT INTO Linked (userID, PlayerName, CoolDown) VALUES (?, ?, ?)"; try (PreparedStatement pstmt = connection.prepareStatement(sql)) { pstmt.setLong(1, userID); pstmt.setString(2, playerName); pstmt.setLong(3, coolDown); pstmt.executeUpdate(); } }

[woeful ember]

public void insertUser(long userID, String playerName, long coolDown) throws SQLException {
  String sql = "INSERT INTO Linked (userID, PlayerName, CoolDown) VALUES (?, ?, ?)";
  try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
    pstmt.setLong(1, userID);
    pstmt.setString(2, playerName);
    pstmt.setLong(3, coolDown);
    pstmt.executeUpdate();
  }
} 

What about it?

[pulsar dove]

Insert

[woeful ember]

what about it?

If the userID is the primary key, you won't be able to insert rows with the same ID multiple times

doing it the second time will give you an SQLException

[pulsar dove]

ya discord ID is not duplicated

[woeful ember]

Then what's the question?

[pulsar dove]

I mean many users do and many insertusers will be sent

[pulsar dove]

I add index for playername and wal on

[woeful ember]

if you do that twice with the same user ID, nothing from the second INSERT will be inserted

[woeful ember]

If you want to ensure the player name is unique, you can use a UNIQUE constraint in the schema

[pulsar dove]

will not be possible with the same user

[woeful ember]

Then what exactly is happening that you don't want to happen?

[pulsar dove]

As far as I know, you can only insert it once, not at the same time

[woeful ember]

yes

[pulsar dove]

So What happens if 2 users do it at the same time?

[woeful ember]

With the same user ID?

[pulsar dove]

No that different

[woeful ember]

then it would insert both

[pulsar dove]

Will it not be ignored??

[pulsar dove]

Shouldn't it only be inserted once in a period of time?

[woeful ember]

Why?

[woeful ember]

Why should it ignore anything?

[pulsar dove]

it will lock and insert will be rejected, maybe it will put it on the waiting list?

[woeful ember]

Why would SQL lock anything?

Normally databases want to process as many requests as possible

[pulsar dove]

I thought it would be locked when recording to secure the data

[woeful ember]

the DB makes sure that there are no data corruptions

so it might insert rows after each other if two requests are coming in at the same time or use other mechanisms to protect against that

but it does allow you to insert multiple things at the same time

[pulsar dove]

I don't really understand what a .comic is, is it necessary?

[woeful ember]

the DB makes sure that statements (or transactions) are either fully executed or not at all

[woeful ember]

you mean atomic?

[pulsar dove]

That is Synchronize?

[woeful ember]

?

[pulsar dove]

setAutoCommit

connection.commit()

Idk @@

[woeful ember]

What do you want to know about committing?

[pulsar dove]

Yea

[woeful ember]

I ask you "what do you want to know about" and you answer with "yes". How should I interpret that?

[pulsar dove]

i don't know how it works, new database with

[woeful ember]

How what works?

commits/transactions?

[pulsar dove]

Sqlite

[pulsar dove]

Idk that

[woeful ember]

There are many things about sqlite

[pulsar dove]

What is this?

[woeful ember]

Transactions?

[pulsar dove]

I don't understand why someone said I should have a commic

List +for

The database really has so much to learn

[woeful ember]

Databases have a concept of transactions. A transaction is started, then you can use as many SQL statements as you want and then you can commit the transaction. The DB then makes sure that the SQL statements only apply when you commit it. If you don't commit it, these won't be executed. If you rollback a transaction, it will revert everything that happened in that transaction. This makes sure that if you do two things together, you don't end up in an in-between state where one part has been executed and the other part hasn't.

[woeful ember]

There is no "a commic" but there is the concept of "atomicity" or "atomic"

atomic means "It's either completely executed or not at all" - you won't get partial executions. This is what I just said about transactions. They are either committed or rolled back but not partially executed

[pulsar dove]

Is it like multi insert?

[woeful ember]

?

[pulsar dove]

.

[woeful ember]

If you include multiple INSERTs in a single transaction, then these will be atomic meaning that you won't end up in a state where some are executed and others aren't

autocommit means that every statement is automatically committed/every statement is its own transaction

[pulsar dove]

In short, my previous insert needs to add anything, should I use asynchronous + wal?

[woeful ember]

?

I have no idea what you are talking about

[pulsar dove]

Sorry

[pulsar dove]

.

[woeful ember]

I still have no idea what you mean with asynchronous + wal

[pulsar dove]

inherently synchronous is the default, but would asynchronous be better in terms of performance?

[woeful ember]

Not necessarily

[pulsar dove]

The language gap left me quite helpless

[woeful ember]

it depends

Do you mean having to talk English?

[pulsar dove]

Depend?

[pulsar dove]

I mean I'm not good at English, I'm only good at Chinese and Vietnamese

[woeful ember]

What would you like to be doing asynchronously?

[pulsar dove]

I don't know if using it will be better, I see it has the risk of losing data

[pulsar dove]

.

Will this code not have problems if called at the same time?

[woeful ember]

If it isn't using the same connection, no

[pulsar dove]

I'm afraid it's the same...

[pulsar dove]

So....

Do you have a good plan?

[woeful ember]

For some DBs, Connections are thread safe/safe to use concurrently

In other cases, you could use a connection pool

[pulsar dove]

other cases?

[pulsar dove]

some DB

it makes me very confused

[woeful ember]

If the JDBC driver doesn't have thread-safe connections

[woeful ember]

there are multiple different types of databases

According to the SQLite docs, you shouldn't use the same Connection in a multithreaded environment
https://www2.sqlite.org/cvstrac/wiki?p=MultiThreading

[pulsar dove]

But now what to do about the connection problem?

[woeful ember]

you can create multiple Connections?

and e.g. give one to each thread that does DB access

[pulsar dove]

Whqat

[woeful ember]

or use a connection pool

[buoyant lark]

💤 Post marked as dormant

This post has been inactive for over 300 minutes, thus, it has been archived.
If your question was not answered yet, feel free to re-open this post or create a new one.
In case your post is not getting any attention, you can try to use /help ping.
Warning: abusing this will result in moderative actions taken against you.

[pulsar dove]

Queues will slow down, but it's not as important as data being guaranteed
Is it okay if I do this?

[woeful ember]

What kind of queue are you talking about?

If you just want the DB operations to be executed after each other, you can use synchronize

[pulsar dove]

lock and unlock

[woeful ember]

yeah you can do that

[pulsar dove]

So which functions are considered multi-threaded?

[woeful ember]

or you just have one Connection for each important thread (but make sure you are closing these connections when necessary)

[woeful ember]

?

synchronized is just locking

[pulsar dove]

meaning call the function and the function will be postponed until it completes then it will continue to the next request?

[woeful ember]

yes

[pulsar dove]

Is it considered multi-threaded?

[woeful ember]

JDA uses a single thread for its listeners

[pulsar dove]

and will it have to wait if there are 2 requests at the same time?

[pulsar dove]

synchronize is better ?

[woeful ember]

not necessarily

[woeful ember]

yes, that's what JDA does

[pulsar dove]

...

[pulsar dove]

Please answer these 3 small questions

1.Is lock different from synchronize in any important way?

2. "ensureOffThread(false);" What is this, is it necessary?

[woeful ember]

java.util.concurrent.Lock gives you more control and there are different types of Locks

[woeful ember]

idk where you got that from

that isn't a JDK thing

[nocturne kindle]

protected void ensureOffThread(boolean single) {
        if (!Bukkit.isPrimaryThread()) return;

        StackTraceElement[] elements = Thread.currentThread().getStackTrace();
        String apiUser = elements[3].toString();
        if (!nagged.add(apiUser)) return;

        if (apiUser.startsWith("github.scarsz.discordsrv")) {
            DiscordSRV.warning("Linked account data requested on main thread, please report this to DiscordSRV: " + apiUser);
            for (StackTraceElement element : elements) DiscordSRV.debug(Debug.ACCOUNT_LINKING, element.toString());
            return;
        }

        DiscordSRV.warning("API user " + apiUser + " requested linked account information on the main thread while MySQL is enabled in DiscordSRV's settings");
        if (single) {
            DiscordSRV.warning("Requesting data for offline players on the main thread will lead to an exception in the future, if being on the main thread is explicitly required use getDiscordIdBypassCache / getUuidBypassCache");
        } else {
            DiscordSRV.warning("Managing / Requesting bulk linked account data on the main thread will lead to an exception in the future");
        }
        DiscordSRV.debug(Debug.ACCOUNT_LINKING, "Full callstack:");
        for (StackTraceElement element : elements) DiscordSRV.debug(Debug.ACCOUNT_LINKING, element.toString());
    } ```

This message has been formatted automatically. You can disable this using /preferences.

[pulsar dove]

here

How to avoid main thread?

[woeful ember]

This seems to result in warnings when being used from outside the main thread

[pulsar dove]

maybe it's not necessary

[woeful ember]

I think they just don't want to block the main thread

which seems very necessary if you don't want an unresponsive application

[pulsar dove]

3.      synchronized (this) {
            connection.setAutoCommit(false);
            try {
                transaction.run();
                connection.commit();
            } catch (SQLException e) {
                handleSQLException(e);
                throw e;
            } finally {
                try {
                    connection.setAutoCommit(true);
                } catch (SQLException e) {
                    logger.severe(e.getMessage());
                }
            }
        }
    }
   

[buoyant lark]

3.     private void executeTransaction(Runnable transaction) throws SQLException {
        synchronized (this) {
            connection.setAutoCommit(false);
            try {
                transaction.run();
                connection.commit();
            } catch (SQLException e) {
                handleSQLException(e);
                throw e;
            } finally {
                try {
                    connection.setAutoCommit(true);
                } catch (SQLException e) {
                    logger.severe(e.getMessage());
                }
            }
        }
    }

[pulsar dove]

Is it okay?

[woeful ember]

It might be a good idea to also roll back in case of a RuntimeException and just rethrow it in that case

[pulsar dove]

Well, for example, there are people who are crazy about spam and force it to be filtered constantly. Do you have a good way to solve this problem?

[buoyant lark]

If you are finished with your post, please close it.
If you are not, please ignore this message.
Note that you will not be able to send further messages here after this post have been closed but you will be able to create new posts.

[woeful ember]

?

[pulsar dove]

spam get info user

[pulsar dove]

what, idont understand...

[woeful ember]

So what's the problem with that

[pulsar dove]

1 user get username or cooldown

[woeful ember]

If something bad happens in the Runnable, you might want to execute connection.rollback()

Also in case of an SQLException

[pulsar dove]

what ?

[woeful ember]

that Runnable here

[pulsar dove]

Can you get a sample? It sounds so confusing to me

[woeful ember]

If it throws a RuntimeException, what should happen with the data?

[pulsar dove]

error? or dupe?

[woeful ember]

yeah but should the DB still be changed?

[pulsar dove]

Do you mean calling it again to insert it into insert?

[woeful ember]

If you run connection.rollback();, it makes sure it isn't executed

private void executeTransaction(Runnable transaction) throws SQLException {
    synchronized (this) {
        connection.setAutoCommit(false);
        try {
            try {
                transaction.run();
                connection.commit();
            } catch(RuntimeException | SQLException e) {
                connection.rollback();
                throw e;
            }
        } catch (SQLException e) {
            handleSQLException(e);
            throw e;
        } finally {
            try {
                connection.setAutoCommit(true);
            } catch (SQLException e) {
                logger.severe(e.getMessage());
            }
        }
    }
}

something like that

[pulsar dove]

oh ty

[pulsar dove]

Did I just put the callback in the wrong place???

[woeful ember]

?

whether you use synchronized or locks doesn't matter that much

[pulsar dove]

i mean rollback

[woeful ember]

yeah you can do that as well

[pulsar dove]

what

[pulsar dove]

Yes, but things that might be okay should be left alone @@

[pulsar dove]

If a person needs to get their info id and they spam requests continuously through commands or buttons, causing select to be called continuously, what should they do? Save it in cache?

[woeful ember]

you can make a rate limit per user?

[pulsar dove]

Here's an example: What if it's a 1-player connection to the server?

Minecraft

[woeful ember]

I have no idea what you are talking about

but I have no diea about Minecraft development

[pulsar dove]

Why u use "try" in" try"

[woeful ember]

so that the connection can be rolled back in case of both a RuntimeException and SQLException

[pulsar dove]

private void executeTransaction(Runnable transaction) throws SQLException {
    synchronized (this) {
        connection.setAutoCommit(false);
        try {
            try {
                transaction.run();
                connection.commit();
            } catch(RuntimeException | SQLException e) {
                connection.rollback();
                throw e;
            }
        } catch (SQLException e) {
            handleSQLException(e);
            throw e;
        } finally {
            try {
                connection.setAutoCommit(true);
            } catch (SQLException e) {
                logger.severe(e.getMessage());
            }
        }
    }
}

If I delete the external catch, is there any problem?

[woeful ember]

you'd need to move the handleSQLException(e)

[pulsar dove]

yae it only sends when there is an error

[pulsar dove]

Finally, I was able to leave the database dynamic, thank you so much

[buoyant lark]

If you are finished with your post, please close it.
If you are not, please ignore this message.
Note that you will not be able to send further messages here after this post have been closed but you will be able to create new posts.