Rails mysql structure.sql dump contains AUTO_INCREMENT

When rails generates a structure.sql dump for MySQL it contains the AUTO_INCREMENT value. Which is anoying because this is not something you want to happen.

CREATE TABLE `active_storage_variant_records` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `blob_id` bigint NOT NULL,
  `variation_digest` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `index_active_storage_variant_records_uniqueness` (`blob_id`,`variation_digest`),
  CONSTRAINT `fk_rails_993965df05` FOREIGN KEY (`blob_id`) REFERENCES `active_storage_blobs` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=471 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Look at the part: AUTO_INCREMENT=471

After digging through the activerecord code, I saw I could sneak in a mysqldump option for the dump call. Yeah! 👍🏻

But there isn't such option 😕. It's an ancient BUG/Feature of mysql, which of course still isn't resolved in MySQL 8. https://bugs.mysql.com/bug.php?id=20786

Why would you like to dump the table structure (without data) with the AUTO_INCREMENT value!?

As a workaround it's possible to enhance the db:schema:dump task in a custom rake file (lib/tasks/remove_autoincrement_from_dump.rake).
So the AUTO_INCREMENT part is removed from it.

Rake::Task['db:schema:dump'].enhance do
  structure_sql_path = Rails.root.join("db/structure.sql")
  if File.exist?(structure_sql_path)
    sql = File.read(structure_sql_path)
    File.write(structure_sql_path, sql.gsub(/AUTO_INCREMENT=[0-9]+/, ""))
  end
end

References:

Rails generate both structure.sql and schema.rb

Default behaviour for rails is to generate a db/schema.rb when running migrations or dumping the database.

Schema.rb is great because it can be used for populating other database types and is used to populate the test database.
And I personally think it's great becaus of the vscode plugin Rails Schema which shows the database structure of your application in a sidebar (TIP, move this bar to the right panel).

The rake task db:migrate default behaviour is to invoke a db:schema:dump when migrating is done. By default this generates the schema.rb file.
This output of the generated file is used to popuplate the test-database..

But somethimes the db/schema.rb isn't good enough for popuplating the test database or using the file to generate the basic structure. Contraints are quirky.
(For example see previous article mysql json constraints).
To generate a native sql dump of the database structure, you can change the default structure to sql. Place the following line in your config/application.rb.

    config.active_record.schema_format = :sql

When enabling this dumps are generated to db/structure.sql.
But you loose the schema.rb dumps.

Here's a tip to work with structure.sql and just generate the schema.rb (for your lovely sidebar).

Create a file in lib/tasks/schema_dump.rb and enhance the dump task, so it also creates the schema.rb file.

Rake::Task['db:schema:dump'].enhance do
  File.open(Rails.root.join('db/schema.rb'), 'w') do |stream|
    ActiveRecord::SchemaDumper.dump(ActiveRecord::Base.connection, stream)
  end
end

NOTE: structure.sql contains yet another MySQL bug. (See next article for workaround)
Tip: use postgresql !

Rails incorrect constraints in schema.rb mysql dumps (for json)

The default dumping strategy for rails is to generate a ruby schema.rb file.
Having a table created by the following migration. (simplified version)

class CreateOrganisation < ActiveRecord::Migration[7.0]
  def change
    create_table :organisations do |t|
      t.string :name
      t.json :settings
    end
  end
end

It generates the following dump

ActiveRecord::Schema[7.0].define(version: 2022_12_29_082458) do
  create_table "organisations", charset: "utf8mb4", collation: "utf8mb4_0900_ai_ci", force: :cascade do |t|
    t.string "name"
    t.text "settings", size: :long, collation: "utf8mb4_bin"
    t.check_constraint "son_valid(`settings`", name: "organisations_chk_1"
  end

Trying to use this dump results in an error. Which isn't strange when you look at the generated constraint code. The MySQL adapter doesn't extract the constraints correctly

    t.check_constraint "son_valid(`settings`", name: "organisations_chk_1"

TIP: Don't use the 'json' datatype when MySQL. Event better use Postgres when possible, with it's jsonb column.

(See the next post for a good workaround to use structure.sql and schema.rb)

Broken WordPress 6.1 when using FreeBSD FTPD server

The latest WordPress update breaks the FTP server capabilities. )
So don't update to 6.1 yet, because it will break the update possibility of your installation.
And you cannot update anymore after it.

I've created an issue for it (Technically the exists method of the ftpext filesystem always returns true for the default FreeBSD ftpd server)
https://core.trac.wordpress.org/ticket/57063

The coming 6.1.1 version should have reverted this change, but when you already upgraded it's already too late of course ;-)

When you've already installed, to only way forward is to
copy the WordPress 6.0 wp-admin/includeas/class-wp-filesystem-ftpext.php file into the installation.

You can download this 6.0 file here (remove the .txt extension)