Ruby On Rails多对多关系下Sqlite3转MySQL数据迁移问题

Hey, let's get your Rails app migrated from SQLite3 to MySQL smoothly. You've already started with seed_dump and updating database.yml, but let's cover all the critical steps and fix common pitfalls you might be facing:

1. Verify & Refine Your Seed Data Export #

First, make sure seed_dump actually captured all your data correctly:

• Double-check your db/seeds.rb file: Look for records in the right order (parent tables first, then child tables with foreign keys—MySQL enforces foreign key constraints strictly, unlike SQLite).
• If you ran rake db:seed:dump without flags, add these to avoid issues:

  rake db:seed:dump --exclude=schema_migrations --append
  

  • --exclude=schema_migrations skips migration records (we don't want to duplicate these in MySQL)
  • --append preserves any existing seed data you already had
• For large datasets, consider splitting seeds or adding batch inserts to speed up the import process later.

2. Fix Your database.yml Configuration #

Your current config is a start, but let's make it robust for MySQL:

default: &default
  adapter: mysql2
  encoding: utf8mb4  # Use utf8mb4 instead of utf8 to support emojis and full Unicode
  collation: utf8mb4_unicode_ci
  username: root
  password: your_root_password_here
  socket: /tmp/mysql.sock  # Adjust path based on your OS: Linux uses /var/run/mysqld/mysqld.sock

development:
  <<: *default
  database: MyDB_development  # Add _development suffix to follow Rails conventions

test:
  <<: *default
  database: MyDB_test

production:
  <<: *default
  database: MyDB_production
  # Add production-specific settings like host, port if needed

• Critical: Create the MySQL databases first via the MySQL console:

  mysql -u root -p
  

  Then run:

  CREATE DATABASE MyDB_development CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  CREATE DATABASE MyDB_test CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  

3. Rebuild the MySQL Database Structure #

Don't rely on the SQLite-generated schema.rb—use your Rails migrations to build the MySQL tables properly:

1. First, make sure you're using the new MySQL config (no leftover SQLite references)
2. Run these commands in order:

   rake db:create  # Creates the databases defined in database.yml
   rake db:migrate  # Runs all migrations to build table structures in MySQL
   

• This ensures the schema matches MySQL's requirements (e.g., correct data types, indexes, foreign keys)

4. Import Your Seed Data Safely #

Now import the data from seeds.rb, but handle foreign key constraints:

• Add these lines to the top and bottom of your db/seeds.rb to temporarily disable foreign key checks during import:

  # Top of seeds.rb
  ActiveRecord::Base.connection.execute('SET FOREIGN_KEY_CHECKS = 0;')
  
  # ... your existing seed data ...
  
  # Bottom of seeds.rb
  ActiveRecord::Base.connection.execute('SET FOREIGN_KEY_CHECKS = 1;')
  

• Then run:

  rake db:seed
  

• If you get errors about duplicate records, regenerate seeds with the --find flag: rake db:seed:dump --find=id—this uses find_or_create_by instead of create to avoid duplicates.

Common Issues & Fixes #

• Connection Errors: If Rails can't connect to MySQL, check:
  • Is MySQL running? (Run brew services start mysql on Mac, sudo systemctl start mysql on Linux)
  • Is the socket path in database.yml correct?
  • Does the root user have permission to access the database?
• Data Type Mismatches:
  • SQLite allows flexible data types, but MySQL is strict. If you get errors about string lengths, update your migrations to use t.text instead of t.string for long fields.
  • Boolean values: SQLite uses 0/1, but seed_dump should convert these to true/false for MySQL—double-check seeds.rb if you see boolean errors.
• Seed Import Failures: If records are missing, verify the order in seeds.rb (e.g., create User records before Post records that reference user_id).

内容的提问来源于stack exchange，提问作者Afsanefda