mysql

There are 9 posts tagged mysql (this is page 1 of 2).

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 MySQL 8 – after upgrade 8.0.22_1 -> 8.0.23

This morning, I tried to upgrade MySQL 8: 

mysql80-server upgraded: 8.0.22_1 -> 8.0.23

But then after upgrading. MySQL didn't start anymore !
It was broken:

2021-02-22T06:20:33.856568Z 4 [System] [MY-013381] [Server] Server upgrade from '80022' to '80023' started.
2021-02-22T06:20:34.351272Z 4 [ERROR] [MY-013178] [Server] Execution of server-side SQL statement '-- Create slow_log CREATE TABLE IF NOT EXISTS slow_log (start_time TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6), user_host MEDIUMTEXT NOT NULL, query_time TIME(6) NOT NULL, lock_time TIME(6) NOT NULL, rows_sent INTEGER NOT NULL, rows_examined INTEGER NOT NULL, db VARCHAR(512) NOT NULL, last_insert_id INTEGER NOT NULL, insert_id INTEGER NOT NULL, server_id INTEGER UNSIGNED NOT NULL, sql_text MEDIUMBLOB NOT NULL, thread_id BIGINT UNSIGNED NOT NULL) engine=CSV CHARACTER SET utf8 comment="Slow log"; ' failed with error code = 13, error message = 'Can't get stat of './mysql/slow_log.CSV' (OS errno 2 - No such file or directory)'.
2021-02-22T06:20:34.354494Z 0 [ERROR] [MY-013380] [Server] Failed to upgrade server.
2021-02-22T06:20:34.354946Z 0 [ERROR] [MY-010119] [Server] Aborting

Googling didn't solve my issue directly. So I tried to create this missing CSV file. (in the mysql database directory)
After this error I also received a missing slog_log.CSV.
Did the same thing.

touch ./mysql/general_log.CSV
chown mysql:mysql  ./mysql/general_log.CSV
touch ./mysql/slow_log.CSV
chown mysql:mysql  ./mysql/slow_log.CSV

Then it works again!

This seems to happen if you skip a certain upgrade. (from now on these CSV files are required). And these CSV files are only created in that particular update.. (not very robust!)