Creation of temporary table with `force: true` issues a non-temporary table drop when using the MySQL adapter

[iridakos]

Steps to reproduce

Create a vanilla Rails 6.1.3.2 application using the mysql2 adapter and execute the following command inside a rails console:

ActiveRecord::Base.connection.create_table(:foo, force: true, temporary: true) { |t| t.string :name }

Expected behavior

The drop statement should include the TEMPORARY keyword.

DROP TEMPORARY TABLE IF EXISTS `foo`
CREATE TEMPORARY TABLE `foo` (`id` bigint NOT NULL AUTO_INCREMENT PRIMARY KEY, `name` varchar(255))

Actual behavior

The drop statement does not include the TEMPORARY keyword.

DROP TABLE IF EXISTS `foo`
CREATE TEMPORARY TABLE `foo` (`id` bigint NOT NULL AUTO_INCREMENT PRIMARY KEY, `name` varchar(255))

Investigation details

This bug was found due to the following error reported by a failing spec that was trying to create a temporary table with force: true in an rspec's before block.

ActiveRecord::StatementInvalid:
  Mysql2::Error: SAVEPOINT active_record_1 does not exist

From ActiveRecord::Transactions documentation:

If you're on MySQL, then do not use Data Definition Language (DDL) operations in nested transactions blocks that are emulated with savepoints. That is, do not execute statements like 'CREATE TABLE' inside such blocks. This is because MySQL automatically releases all savepoints upon executing a DDL operation. When transaction is finished and tries to release the savepoint it created earlier, a database error will occur because the savepoint has already been automatically released

The drop operation for non-temporary tables commits the current transaction in MariaDB:

Note: DROP TABLE automatically commits the current active transaction, unless you use the TEMPORARY keyword.

The following part in version 6.1.3.2 of ActiveRecord::ConnectionAdapters::SchemaStatements

if force
  drop_table(table_name, force: force, if_exists: true)
else
  schema_cache.clear_data_source_cache!(table_name.to_s)
end

does not pass the temporary option in the drop_table call and thus the ActiveRecord::ConnectionAdapters::AbstractMysqlAdapter

def drop_table(table_name, **options)
  schema_cache.clear_data_source_cache!(table_name.to_s)
  execute "DROP#{' TEMPORARY' if options[:temporary]} TABLE#{' IF EXISTS' if options[:if_exists]} #{quote_table_name(table_name)}#{' CASCADE' if options[:force] == :cascade}"
end

does not include the TEMPORARY keyword in the produced SQL.

System configuration

Rails version: 6.1.3.2

Ruby version: 2.7.3

[ghiculescu]

Thanks for the bug report and the investigation. Would this be the fix:

  drop_table(table_name, force: force, if_exists: true, temporary: options[:temporary])

?

Would you be interested in making a PR and adding a test?

[iridakos]

Hi @ghiculescu , yes, I think that's what's needed here.

Would you be interested in making a PR and adding a test?

Of course, I will try to open it as soon as possible.

[rails-bot]

This issue has been automatically marked as stale because it has not been commented on for at least three months.
The resources of the Rails team are limited, and so we are asking for your help.
If you can still reproduce this error on the 6-1-stable branch or on main, please reply with all of the information you have about it in order to keep the issue open.
Thank you for all your contributions.