mysql crashes on ping()

[bisikli]

Hello,

I have a scenario where the same client makes several api calls asynchronously. In each call, i ping the database if the existing connection continues, if not, try to reconnect etc.

In on of these ping's program crashes with the following error:

malloc: *** error for object 0x101faf630: pointer being freed was not allocated *** set a breakpoint in malloc_error_break to debug

I also tried doWithLock{} for each api call to be thread safe, but it did not work.

[timotheeduran]

Hello,

I have a similar error malloc: *** error for object 0x1029220f0: pointer being freed was not allocated when I send multiple requests to the server. The error is in MySQLStmt.swift.

Basically, I made a loop which sends 10000 requests to test the server scalability. After between 4 or 7 successful requests, I get this error. It has to do with the number of threads created.

Did you found a way to fix those memory allocation problems?

Thanks.

[kjessup]

Are your database connections being shared across threads? They shouldn't be, but if they are you will see crashes like these because things get deallocated from one connection while they are being used in another.

If that's not the situation, can you please post your test loop here @burgercode and I will try and get to the bottom of it.

[timotheeduran]

Thank you for your help @kjessup !
It's hard to tell if my database connections are shared across thread but I think it might be the source of the bug. Basically here is how my server-side code works:

I have a class DatabaseContext which is used that way which PerfectCRUD when we want access to the database somewhere: (example)

public func get(byId id: UUID) throws -> T? {
    let entity = try self.databaseContext.set(T.self).where(\T.id == id).first()
    return entity
}

The class itself looks like this:

public class DatabaseContext: DatabaseContextProtocol {
    ...
    private var syncQueue = DispatchQueue(label: "databaseQueue")
    ...
    
    public func set<T>(_ type: T.Type) throws -> Table<T, Database<MySQLDatabaseConfiguration>> where T: Decodable, T: Encodable {
        return getDatabase().table(type)
    }
    
    private func getDatabase() -> Database<MySQLDatabaseConfiguration> {
        
        if self.isValidConnection() {
            return self.database
        }
        
        syncQueue.sync {
            if !self.isValidConnection() {

                let sqliteConfiguration = try! MySQLDatabaseConfiguration(database: databaseName, host: host, username: username, password: password)
                self.database = Database(configuration: sqliteConfiguration)
            }
        }
        return self.database
    }

I don't generate threads elsewhere in my code, I think they are generated by Perfect (right ?).
Do you think that this DatabaseContext class with the getDatabase() could be the cause of the bug? When potentially different threads call getDatabase() at the same time?

Let me know if you need more precisions! Thank you very much!

[timotheeduran]

Alright, I found a way to "fix" it. Basically, I use semaphore before accessing the database.

let semaphore = DispatchSemaphore(value: 1) // Only one thread can use the semaphore
public func get(byEmail email: String) throws -> User? {
    semaphore.wait() // Request the Lock
    let user = try self.databaseContext.set(User.self).where(\User.email == email).first()
    semaphore.signal() // Release the Lock
    return user
}

The problem is that, despite the fact that it works fine, it forces the database accesses to be sequential. When receiving many requests form a client, those are apparently handled on different threads, thus different threads can use the get(byEmail: String) method above at the same time. It this case, I'm only reading values so concurrent access to the database shouldn't be a problem.
Making things sequentially drastically reduces performances.

If I don't do that, I get some pointer exceptions as described in the previous messages or Lost connection to MySQL server during query.

@kjessup Did I missed a key concept about Perfect, Perfect-MySQL or Perfect-CRUD ? Or does it exist a way to allow concurrent accesses to the database?

Thank you very much!

[bisikli]

What I have done in my case was to create a 'connection pool', which basically finds an available unoccupied database connection in each request and if they are all busy waits a while.

You can find the basic implementation in the following link:

https://stackoverflow.com/questions/46193738/is-there-a-db-connection-pool-for-swift-in-perfect/49798374#49798374

[kjessup]

@burgercode @kjessup Did I missed a key concept about Perfect, Perfect-MySQL or Perfect-CRUD ? Or does it exist a way to allow concurrent accesses to the database?

Yes, you can certainly access the database simultaneously as long as you are using different database connections (CRUD database objects). None of the database connectors (Postgres, SQLite, etc.) permit using the same connection/database object on multiple threads at the same time.

I'd really need to see how you're using your DatabaseContext object, but if you are making an instance of it and sharing it amongst your handlers then that's a problem. You would be better off just making a new connection whenever you need to talk to the database.

However, if you are seeing performance issues with your database connections under load then the suggestion from @bisikli is correct: you can make your own connection pool which claims existing connections and then releases them back into the pool when they are done.

[timotheeduran]

Hello,

Thank you very much for your answers!

if you are making an instance of it and sharing it amongst your handlers then that's a problem.

That's what I've done. So I changed it like so in my DatabaseContext and it works fine for now.

public var database: Database<MySQLDatabaseConfiguration> {
    return Database(configuration: /*sqliteConfigurationHere*/)
}

However your solution @bisikli is very elegent and I will try to implement it in the future.

Thank you 💪🏽