Cannot connect to large MySQL databases

[raphael-yapla]

Bug Description

First off, thank you for the great extension!

We have very large production databases, to the tune of more than ~300,000 tables per db. When trying to connect to a database that large, the connection always fail and prompts the following error:

"Error getting databases for connection x, error: Query execution was interrupted, maximum statement execution time exceeded"

I also tried connecting directly using the "Open table" command but it fails as well

Repro steps

1. Add a connection to a large database (more than 300k tables)
2. Click on the connection
3. Watch it fail

Expected Behavior

I would expect to be able to connect to the database normally, I don't want to list the tables but I'd like to be able to run queries, open a specific table that I know the name of or to use the MCP server nonetheless.

Actual Behavior

Cannot connect in any way to these large databases

Environment

• DBCode version: 1.19.6
• VS Code (or fork) version: 1.105.1
• OS: Darwin arm64 25.0.0
• Database: MySQL
• Connection: (direct/SSH/SSL) ssh tunnel

Error Meesages (if applicable)

Error getting databases for connection x, error: Query execution was interrupted, maximum statement execution time exceeded

Logs

If available, paste relevant logs from the Output panel (View > Output > DBCode).

2025-11-27 15:09:28.809 [info] Tunnel: opened 'bastion'
2025-11-27 15:09:30.384 [info] Connection: opened 'null' on 'x' (mysql:8.0.42)
2025-11-27 15:09:57.060 [info] Connection: opened 'null' on 'x' (mysql:8.0.42)
2025-11-27 15:10:23.715 [info] Connection: opened 'null' on 'x' (mysql:8.0.42)
2025-11-27 15:10:50.649 [info] Connection: opened 'null' on 'x' (mysql:8.0.42)
2025-11-27 15:11:17.318 [info] Connection: opened 'null' on 'x' (mysql:8.0.42)
2025-11-27 15:11:43.999 [info] Connection: opened 'null' on 'x' (mysql:8.0.42)
2025-11-27 15:12:10.610 [info] Connection: opened 'null' on 'x' (mysql:8.0.42)
2025-11-27 15:12:37.141 [info] Connection: opened 'null' on 'x' (mysql:8.0.42)
2025-11-27 15:13:03.691 [info] Connection: opened 'null' on 'x' (mysql:8.0.42)
2025-11-27 15:13:52.462 [info] Connection: opened 'null' on 'x' (mysql:8.0.42)
2025-11-27 15:14:48.774 [error] Connection: 'x' failed:  SqlError: (conn:1943360, no: 1040, SQLState: 08004) Too many connections
    at Object.C.exports.createError (/Users/raphael/.cursor/extensions/dbcode.dbcode-1.19.6-universal/out/vendor/mariadb.js:5:179)
    at a.readError (/Users/raphael/.cursor/extensions/dbcode.dbcode-1.19.6-universal/out/vendor/mariadb.js:5:9902)
    at a.handshakeResult (/Users/raphael/.cursor/extensions/dbcode.dbcode-1.19.6-universal/out/vendor/mariadb.js:27:31821)
    at a.onPacketReceive (/Users/raphael/.cursor/extensions/dbcode.dbcode-1.19.6-universal/out/vendor/mariadb.js:27:29810)
    at di.receivePacketBasic (/Users/raphael/.cursor/extensions/dbcode.dbcode-1.19.6-universal/out/vendor/mariadb.js:19:330)
    at di.onData (/Users/raphael/.cursor/extensions/dbcode.dbcode-1.19.6-universal/out/vendor/mariadb.js:19:1299)
    at TLSSocket.emit (node:events:519:28)
    at TLSSocket.emit (node:domain:489:12)
    at addChunk (node:internal/streams/readable:561:12)
    at readableAddChunkPushByteMode (node:internal/streams/readable:512:3)
    at TLSSocket.Readable.push (node:internal/streams/readable:392:5)
    at TLSWrap.onStreamRead (node:internal/stream_base_commons:189:23)
    at TLSWrap.callbackTrampoline (node:internal/async_hooks:130:17)

[mikeburgh]

I just released 1.19.7 which should resolve this, but there are other challenges once the database load with such large tables… check out #861 which also contains some updates for this in 1.19.7

[mikeburgh]

Had to push another update, found a small issue.. try out 1.19.8

[raphael-yapla]

Thank you so much for the quick response time!

I tried 1.19.8 and I can confirm that I can connect to that large database now! Thank you!

However the introspection fails. I get the following error after a few minutes:

"Error introspecting database db, error: Query execution was interrupted, maximum statement execution time exceeded"

2025-12-01 08:46:47.285 [info] Connection: opened 'table' on 'db' (mysql:8.0.42)
2025-12-01 08:47:12.546 [error] Connection: lightweight introspection of 'table' on 'db' error: Query execution was interrupted, maximum statement execution time exceeded
2025-12-01 08:47:13.592 [info] [Debug - 8:47:13 AM] SchemaFactory: Attempting to load schema from storage for 'table' on connection 'PnCgkgYXFqhBM0zZs8CpN'
2025-12-01 08:47:13.592 [info] [Debug - 8:47:13 AM] [STUB] Skipping createDirectory for /Users/raphael/Library/Application Support/Cursor/User/globalStorage/dbcode.dbcode (read-only mode)
2025-12-01 08:47:13.592 [info] [Debug - 8:47:13 AM] FileStore: failed to find file 'schemas/PnCgkgYXFqhBM0zZs8CpN/table.data'
2025-12-01 08:47:13.592 [info] [Debug - 8:47:13 AM] SchemaFactory: No schema found for 'table' on connection 'PnCgkgYXFqhBM0zZs8CpN'
2025-12-01 08:47:38.346 [error] Connection: introspection of 'table' on 'db' error: Query execution was interrupted, maximum statement execution time exceeded

We know we're an edge case scenario though and we never use the introspection queries from other tools on our production servers because we have way too many tables for it to work or be useful, we usually just query our things directly with SQL. We actually had issues in the past with these kind of queries creating performance issues on our database servers.

Would you be open to adding an option on the connections to disable introspection? Or if possible to disable the introspection on some tables based on a pattern? 99.99% of our tables are created dynamically and we're not interested in having introspections for them.

[mikeburgh]

Yeah I expected intro to fail, once you can list the databases now, you can click the icon next to them when you hover to then run queries etc.. but without introspetion it's still sub par (no auto complete, no table editing, no browsing etc).

I am open to the option to disable introspection on some tables.. and yeah maybe a name filter is the solution to apply across the board (eg filter any schema item who's name matches this pattern)..

I am also wondering if I can bump up the max execution time when running introspection just for the session, that way in theory it will just work..

Can you run this and share the results, curious what you max_execution_time is set to now..

SELECT @@global.max_execution_time, @@session.max_execution_time;

Other idea I am thinking about.. at the moment the introspection is 8 or 9 queries all run in one batch.. if I break them up into single queries.. and one fails ignore the failure and work with the rest.. that gives you some introspection data at least.. Still maybe not as good an option as filtering, but for an initial connect experience it might be better (and I can alert that xx failed and put towards the filter setting on the connection to reduce things).. again still just thinking it through.

Also can you run this query, it's the column fetching, which is I am guessing where it's failing..

select table_name
	,column_name
	,ordinal_position
	,LOWER(data_type)
	,case when COLUMN_KEY = 'PRI' then true else false end as primaryKey
	,Case when IS_NULLABLE = 'YES' then true else false end as is_nullable 
	,case when generation_expression != '' then true else false end as is_generated
	,case when extra like '%auto_increment%' then true else false end as is_identity
	,GENERATION_EXPRESSION as generation_expression
	,case when NUMERIC_PRECISION is not null then NUMERIC_PRECISION else character_maximum_length end as length
	,numeric_scale as scale
	,case when  extra like '%auto_increment%' then extra ELSE
		case when column_default = 'NULL' then null else column_default end 
	end as column_default
	,COLUMN_COMMENT as comment
	,Case when data_type = 'enum' then  CONCAT('[', TRIM(TRAILING ')' FROM SUBSTRING(COLUMN_TYPE, 6)) , ']') else null end as column_values
FROM information_schema.columns
where table_schema = DATABASE()
ORDER BY table_name, ordinal_position;

I don't need the output, just the time it takes (assuming it finishes before your session execution).

[raphael-yapla]

Here's our execution time configuration, we don't want it any higher to avoid some requests spinning out of control, also because some of our backend is using lambdas limited to 30s execution time.

Global max execution time	Session max execution time
25000	25000

And yes you're right that introspection query does timeout on our smallest server!

My concern is that the introspection might cause some severe performance issues with our bigger server if we were to connect to it. We found that the SHOW TABLE STATUS queries are especially bad for us. Is that something you currently use within that introspection phase?

[mikeburgh]

Figured that was going to be the case on the columns, it's the big one.. Since this issue was opened there has been an update to break multiple queries up for MySQL (You can enable it on the config, but it given that columns one fails by itself it wont help (full introspection still expects them all to work or it fails.. and I would need to make more changes to let it accept one or more failing)

No I don't use show table status, I do use show function/procedure status, but the rest are similar to columns, getting it from the information_schema views.

I am not familiar enough with MySQL to speak to what might cause it to lock etc, and I didn't like the idea of changing execution time's automatically for users to do the introspection once I thought about.. as people who set them do so for a reason, I don't want to change it.

I am actually working through a change at the moment for databases that support schemas, to fetch single schemas only.. and do them on demand.. I think I might need to do something similar here.. if I detect a huge amount of tables, switch to on demand fetching.. it will make things like auto complete a bit of a pain.. but it's either that or have some sort of filter option up front, but that kinda sucks when you want to see something different (go back edit filter, refresh) etc..

[raphael-yapla]

I think that approach might make more sense for us yeah, like I said we're aware that we're a special case and are very used not to have all of the introspection niceties. Our main concern is that we don't want the tool to burden the database performance!

Let me know if you want us to test things for you with our setup, I'd be happy to provide feedback!