Well, I dived deeper into this and finally figured it out. It's weird though.

There's a mysql command called "show full processlist" that shows all the threads and what they're doing. This never showed anything untoward.

There's other queries you can do though, that show varying levels of detail. I finally landed on
SELECT * FROM information_schema.innodb_trx order by trx_started;
and noticed that I had a few rows with trx_started that were like... 15 or 20 minutes old. They had no associated query, they claimed that nothing in them was locked (trx_tables_locked=0, trx_rows_locked=0 etc)

Except for 1 or 2 old ones, every other transaction would be less than 1s old.

But, if I delete the old ones, and then create my index, poof, it works immediately.

I wonder if there is something that is starting a transaction and then having an error occur without every doing any queries, and subsequently not getting closed? My connections have a 30 minute TTL so even if that's the case, they don't "pile up"

Anyway, annoying but at least not insurmountable.

PostgreSQL can also freeze up if there is a transaction lock running. This is important for MVCC and ACID, though I don't like the idea of messing with information_schema to alter behavior. I suppose RDS doesn't give a mysql prompt?

I'm not altering behavior using information_schema, just selecting for what transactions are open. I don't really know what "open" means in this sense, since there is no running query, and according to the results I get, nothing is actually locked. Somehow there is an open transaction not doing anything, blocking access to getting a metadata lock.

I "killed" the threads that were holding these bogus locks.

You can get a prompt via RDS, sure. I was using a local client which is more or less the same thing.

Sounds possible that there is a piece of code in the application that does:

-- open transaction

-- run insert / update / delete query

-- (forgets to close transaction).

This would block everything else.

Right, but this would also show up in several places. The query that shows open transactions also shows what they're doing (what query is being run in the transaction).

What I suspect is actually happening is
-- open transaction
-- don't run any sql queries
-- forget to close transaction
like maybe an error is happening before it even gets a chance to use the connection.

Anyway, hopefully I can get that figured out.

I also suspect that wrapping a SELECT in a transaction, even if it's closed, could possibly be causing an issue. I'm not entirely sure of this, but I think that caused some issue with me once before. It may have just returned an empty tuple and I could be wrong on the transaction state.

Is it possible that a return tuple is sent back before closing the transaction? I feel like this could show a completed / empty query even though the system is locked up.

I guess I couldn't really say. It actually "shouldn't" be possible for a transaction to not get closed at all. There's a piece of middleware that is supposed to run at the end of every web request that cleans up open connections. But clearly we're beyond should/shouldn't here because things aren't acting like they "should"

It would be nice if I could find out what used a given mysql thread id last, so maybe I can find a way to start logging that or something.

Is there some way to log all the PIDs? That would be a good start. If you could output spawn time/kill time to terminal that would give you a good idea of where to look.

Welcome to the world of

I can probably log them all, although I'm a little concerned about performance. I guess I'll try it and see how it goes. Actually I guess first I'll have to figure out how to get the pid of the connection I've been given.

I thought MySQL allowed reads without lock by default. A bit of research suggests that this is for innodb tables only. Anyone who knows more about dbs know any more on the subject?

I'm not sure if RDS has it, but is it possible to access server logs? That tends to give a lot of information...

This is true but we're not talking about row-level or table-level locks here. The problem I'm having is that in order to change the structure of a table (add or remove a column, etc, which includes adding indexes) you have to pass a "metadata" lock. This metadata lock prevents you from altering the structure of a table while a query is running on it. This was actually only added in like 5.5 (current version is 5.7.something)

So anyone, something is holding onto metadata locks way longer than they should, preventing me from adding indexes.

There used to be a great little feature in Chrome that allows all computers on a network to share and entire browser config, same session cookies, and so on. This means you can look at other people's emails, grab other people's session data, see their browsing history, and download porn on everyone's computer.

****

I guess I can share my recent Windows story. I was working a contract and some system needed to talk to the database via ODBC. I seriously thought about declining this because a) it's Windows and b) other people already tried to configure this.

I connect to the machine and you guessed it; whoever worked on this last downloaded a propriety driver, that driver was not paid for, and good luck getting anything to work. I immediately uninstall said driver and continue on.

Windows and ODBC are violent enemies, as far as I can tell. ODBC makes an effort by creating MSI files, but... it doesn't always work as expected. Some drivers are x64, some x86, some are both. The trick is figuring out which driver is the correct one, and if you guessed that a 64 bit Windows machine takes a 64 bit driver, nope, very wrong on that one. And if you think ODBC is easy to configure, let it be known that exactly one source on the internet shows the correct technique. I spent a good 40 hours of my life figuring it all out.

The x86 driver didn't work, so I tried to install the x64. I wasn't completely surprised that the download process was corrupted and had to think of some reasons why:

1- I used the wrong ODBC driver, but I know it can be one of either versions 1 to 6, either x86 or x64 or dual, and I don't know if I want the original or updated release. To be sure, i'd have to install, uninstall, and test each of 25 drivers.

2- The propriety driver successfully uninstalled, but there is still a bunch of cruft in the registry which has to be manually deleted, and until this very dangerous process is done, I won't be able to install ODBC drivers.

3- Who the **** knows why and it won't ever work on this machine.

Even I, who has done this for about 50 computers across Windows, Mac, and Linux, ended up taking over 4 hours to get this issue figured out. If I was in control of 50 dote heads running on a network of Windows computers, I'd lock their stuff down tighter than a library computer.

Oh god, ODBC can bite me. Some of the worst experiences of my life.

Oh, I think JDBC is even worse. I'll never associate pom poms with zeal again in my life.

This post and the others related to it have totally changed how I code, and I've called this style GOOD Programming, where GOOD stands for "Get Out Of Dodge".

I don't get everyone freaking out about the react.js license.

Don't sue anyone for about patents and things are good.

indeed, it's a complete nothing / people (perhaps intentionally) not understanding / parroting of wrongness

Rusty, I had a thought about your issue. If you have a middleware that closes all transactions, then perhaps my suggestion is wrong.

However, if you have manually open transactions, using open() or whatever, then is it possible that you are running a query outside of a transaction because the programmer forgot to open a transaction? It seems logical to me that a query running in a function is a session-level transaction**, but this case may end up creating a system-level transaction, if that makes any sense. In other words, if the query is running, it is automagically wrapped in a transaction within the database but not within the app itself. To discover something like this, find functions that return "none" regardless of the params.

** I would think forgetting to open a transaction would completely fail at every level, but I'm not really sure, to be honest.

The way I have sqlalchemy configured, which is more or less the recommended/default way, every time you start using a connection, it's a transaction. So even selects are in a transaction. It's supposed to automatically roll back if you close the connection without ending the transaction. Again, "supposed to", so it's possible that isn't happening, or the connection isn't getting properly closed.

I suspect the latter, because any time I look, I can find a few of these phanton transactions. They are never more than 30 minutes old, which is the timeout I have to recycle connections in the connection pool. I am guessing an error or bug in the middleware itself, or something that somehow is skipping the middleware.

I've put the issue aside for now. The urgency is much lower than before because at least now I can get around it when I need to.

Also, a complicating factor is that "other things" connect to the database too - my own local SQL sessions through Sequel Pro. There's a read-only account that our product manager uses - probably with sequel pro or whatever tool he likes. There's a process that does backups, and so forth. it *could* be one of those things. It's sometimes easy to forget that your website is not the only thing connecting to your database.

I periodically have to deal with FoxPro databases. My naive solution is to use an ODBC connection to turn it into plain text. It seems virtually impossible to install FoxPro on 64 bit W10, but it may just be beyond my skills.

https://en.wikipedia.org/wiki/FoxPro

FoxPro was a text-based procedurally oriented programming language and database management system (DBMS), and it is also an object-oriented programming language, originally published by Fox Software and later by Microsoft, for MS-DOS, Windows, Macintosh, and UNIX. The final published release of FoxPro was 2.6. Development continued under the Visual FoxPro label, which in turn was discontinued in 2007.

I'm sorry....

I came in to say this was a recent request, but I think you beat me.

"I have 3 different database systems for all these miniature programs I'm using. Is it possible to join the tables from each database?"

The FoxPro thing isn't too bad in the overall scheme of things - a rival system uses it. It writes intermediate results to disk using FoxPro for later use. This isn't very efficient, so gives us a marketing opportunity.

There is so much about mysql I don't know. I need to find a community of experts or some kind of really good tuning instructions either online or in a paper book.

Today I ran into something weird... For a while if you had a query like
select * from users where username='foo' or user_id in (2, 3, 4)
and you had an index for username and an index for user_id, it wouldn't use both of them. Supporting both indexes in OR was added about 5.0 I think. Even now it doesn't always choose properly.

But I found out today that my query, which is more like
select * from users where username='foo' or user_id in (select user_id from other table)
it DOESN'T use the user_id index.

Now I'm getting ready to try a bunch of different approaches, like, 2 queries with a union, hard-coding the results of the in-clause into the query, and so forth, and I guess see which ones shake out as the best with my data.

Sounds like good business to me.