When update is done directly from 3.10.x to 4.1.x then we can hit this problem because scheme checker from 3.10.x don't know about RENAME TABLE type of query. Support for this type of query was added in 4.0.x to the scheme checker.

Never used RENAME TABLE #__old TO #__new;, it doesn't work without some conditions (temp tables, lack of privileges, etc.)

The mariadb docs suggest to not rename table but to create a new table, copy the data and then delete the old table.

They suggest a good and "safe" way.

For me the most compatible and supported way is ALTER TABLE #__ucm_history RENAME TO #__history;

For me the most compatible and supported way is ALTER TABLE #__ucm_history RENAME TO #__history;

@simbus82 How shall this help with a lock problem? According to the MySQL docs, the statements RENAME TABLE and ALTER TABLE ... RENAME TO are equivalent. So if there are issues with the first one, they should also apply to the second one.

I think the only safe way to find out if a change will help or not is to find someone who can reliably reproduce the issue on their environment. Anything else would be just a guess.

As far as I can understand the comments in https://dev.mysql.com/doc/refman/8.0/en/rename-table.html , the lock problem only happens when there are more, concurrent rename operations in a sequence.

Can we be sure that in the cases where this statement failed, the reason was not because the table already existed with the new name, e.g. after a failed update attempt from 3 to 4 and then restoring the old backup in database without deleting the new J4 tables?

Sorry to be clear. I am not saying that the rename table query is the cause of failed installations. There are a million different reasons why that happens. The problem I am raising is that there are a million blog posts or forum users who say "if it didnt install the first time then do it again" and it is this scenario I am suggesting that we can solve here. They might have fixed the original reason for the update to fail but cant update because of this "to them" new error

#36506 together with some changes on the old update SQL scripts could solve that.

The changes on the old update SQL scripts are made with #37156 .

The mariadb docs suggest to not rename table but to create a new table, copy the data and then delete the old table.

@brianteeman Could you try to find that again and provide a link to it?

From my browser history it must have been this page https://mariadb.com/kb/en/rename-table/

@brianteeman I see, that comment at the bottom. Well, that could be a symptom of the following problem.

It happens from time to time that there is a buggy version of a MySQL or a MariaDB release. It gets released and you use that when you set up your server, or update to that when being offered that update. Now the bug gets fixed with the next minor. Your Linux distribution my update their packages so you get offered an update to that fix, and hosters will update their software. But if you manage your database server yourself or on local development or testing environments with any AMP you might miss that and stick with a buggy version.

We had cases in past where we had to do workarounds for buggy MariaDB versions as far as I remember, I think it was the thing using subqueries on the same table as the target table in an insert statement.

So it can be that there are certain MariaDB or MySQL versions which have a problem with table renames and locks.

It could make sense to use a workaround for that problem here by doing what that comment suggests.

I will think about it a bit and check if we can do that.

just food for thought

I've used following failsafe for other projects:

CREATE TABLE IF NOT EXISTS new_table LIKE existing_table;
INSERT new_table SELECT * FROM existing_table;
DROP TABLE IF EXISTS existing_table;

@richard67 did this get fixed?

@brianteeman There is nothing to be fixed in Joomla in my opinion. We still have no evidence that it was the rename table which has failed, and all I‘ve said in my previous comments is still right.

oh I must have misunderstood your last comment

So it can be that there are certain MariaDB or MySQL versions which have a problem with table renames and locks.
It could make sense to use a workaround for that problem here by doing what that comment suggests.
I will think about it a bit and check if we can do that.

Well, I thought about it and don't think there's something useful to do.

ok thats fine. I can close this now.