bolt · macintoshplus · Mar 11, 2025
diff --git a/.github/workflows/database_migration_mysql.yaml b/.github/workflows/database_migration_mysql.yaml
@@ -0,0 +1,85 @@
+name: Test MySQL Database migration
+
+on:
+    pull_request:
+
+
+jobs:
+    db_migration:
+        strategy:
+            matrix:
+                php-version: [ '8.1' ]
+                db-source-version: ['4.2', '5.0', '5.1']
+            #                db-source-version: ['4.2']
+            fail-fast: false
+        services:
+            mysql:
+                image: mysql:5.7
+                ports:
+                    - "3306:3306"
+                env:
+                    MYSQL_DATABASE: bolt
+                    MYSQL_USER: bolt
+                    MYSQL_PASSWORD: bolt
+                    MYSQL_ROOT_PASSWORD: bolt
+                options: --health-cmd="mysqladmin ping" --health-interval=10s --health-timeout=5s --health-retries=3
+
+        name: MySQL DB Migration test from ${{ matrix.db-source-version }}
+        runs-on: ubuntu-latest
+        steps:
+            - uses: actions/checkout@v4
+
+            - name: restore database
+              run: mysql -h 127.0.0.1 -u root --password=bolt bolt < tests/assets/database_dump_mysql/bolt-${{ matrix.db-source-version }}_database.sql
+
+            - uses: shivammathur/setup-php@v2
+              with:
+                  # test the lowest version, to make sure checks pass on it
+                  php-version: ${{ matrix.php-version }}
+                  extensions: json, mbstring, pdo, curl, pdo_mysql
+                  coverage: none
+              env:
+                  GITHUB_TOKEN: ${{ secrets.GITHUB_TOKEN }}
+            - name: Install composer dependencies
+              run: composer install --prefer-dist --no-progress
+              env:
+                  DATABASE_URL: mysql://bolt:bolt@127.0.0.1/bolt?serverVersion=5.7&charset=utf8mb4
+
+            - name: upgrade
+              run: bin/console doctrine:migrations:migrate -n
+              env:
+                  DATABASE_URL: mysql://bolt:bolt@127.0.0.1/bolt?serverVersion=5.7&charset=utf8mb4
+
+            - name: list migration
+              run: |
+                  bin/console doctrine:migration:status -n
+                  bin/console doctrine:migration:list -n
+              env:
+                  DATABASE_URL: mysql://bolt:bolt@127.0.0.1/bolt?serverVersion=5.7&charset=utf8mb4
+            - name: Check DB DIFF
+              run: |
+                  set +e
+                  bin/console doctrine:migrations:diff -n 
+                  result=$?
+                  if [ $result -eq 0 ]; then
+                      migration_file=migrations/$(ls -1 migrations | tail -n 1)
+                      # The `$this->addSql('ALTER TABLE bolt_field_translation CHANGE value value JSON NOT NULL');` change is due doctrine/migration and database server misconfiguration
+                      count=$(grep -c 'ALTER TABLE bolt_field_translation CHANGE value value JSON NOT NULL' $migration_file || true)
+                      # count all `addSql` method call 
+                      countAddSql=$(grep -c 'addSql' $migration_file || true)
+                      if (( $countAddSql - $count > 0 )); then
+                          echo ' '
+                          echo '**************************** CHANGE ****************************'
+                          echo ' '
+                          cat $migration_file
+                          echo ' '
+                          echo '********************* MISSING A MIGRATION *********************'
+                          echo ' '
+                          exit 1
+                      else
+                          echo 'Great Job! No unexpected change!'
+                      fi
+                  fi
+                  exit 0
+              env:
+                  DATABASE_URL: mysql://bolt:bolt@127.0.0.1/bolt?serverVersion=5.7&charset=utf8mb4
diff --git a/compose.yml b/compose.yml
@@ -21,10 +21,10 @@ services:
     ports:
       - 3306
     environment:
-      MYSQL_ROOT_PASSWORD: ${DATABASE_PASSWORD}
-      MYSQL_PASSWORD: ${DATABASE_PASSWORD}
-      MYSQL_USER: ${DATABASE_USER}
-      MYSQL_DATABASE: ${DATABASE_NAME}
+      MYSQL_ROOT_PASSWORD: ${DATABASE_PASSWORD:-bolt}
+      MYSQL_PASSWORD: ${DATABASE_PASSWORD:-bolt}
+      MYSQL_USER: ${DATABASE_USER:-bolt}
+      MYSQL_DATABASE: ${DATABASE_NAME:-bolt}
       MYSQL_INITDB_SKIP_TZINFO: 0
 
   web:

diff --git a/migrations/Version20201210105836.php b/migrations/Version20201210105836.php
@@ -6,6 +6,7 @@
 
 use Doctrine\DBAL\Connection;
 use Doctrine\DBAL\Schema\Schema;
 use Doctrine\DBAL\Schema\SchemaConfig;
 use Doctrine\Migrations\AbstractMigration;
 use Psr\Log\LoggerInterface;
 use Symfony\Component\DependencyInjection\ContainerAwareInterface;
@@ -23,31 +24,68 @@ public function __construct(Connection $connection, LoggerInterface $logger)
         $this->tablePrefix = 'bolt';
     }
 
-    public function getDescription() : string
+    public function getDescription(): string
     {
         return 'Bolt 4.2 Migration: bolt_reset_password and bolt_column.avatar';
     }
 
-    public function up(Schema $schema) : void
+    public function up(Schema $schema): void
     {
+        $schemaConfig = new SchemaConfig();
+        $options = [
+            'charset' => 'utf8',
+        ];
+        if ($this->connection->getDatabasePlatform()->getName() === 'mysql') {
+            $options = [
+                'engine' => 'InnoDB',
+                'charset' => 'utf8mb4',
+                'collate' => 'utf8mb4_unicode_ci'
+            ];
+        }
+        $schemaConfig->setDefaultTableOptions($options);
+
         // Create the user avatar. See https://github.com/bolt/core/pull/2114
         $userTable = $schema->getTable($this->tablePrefix . '_user');
 
-        if (! $userTable->hasColumn('avatar')) {
+        if (!$userTable->hasColumn('avatar')) {
             $userTable->addColumn('avatar', 'string', ['notnull' => false, 'length' => 250]);
         }
 
         // Create the reset password table. See https://github.com/bolt/core/pull/2131
-        if (!$schema->hasTable($this->tablePrefix . '_password_request')) {
-            $resetPaswordTable = $schema->createTable($this->tablePrefix . '_password_request');
-            $resetPaswordTable->addColumn('id', 'integer', ['autoincrement' => true]);
-            $resetPaswordTable->setPrimaryKey(["id"]); // MySQL / MariaDB needs autoincrement column to be the primary key
-            $resetPaswordTable->addColumn('user_id', 'integer', ['notnull' => true, '', 'default' => 0]);
-            $resetPaswordTable->addForeignKeyConstraint($this->tablePrefix . '_user', ['user_id'], ['id'], ['onUpdate' => 'CASCADE']);
+        if ($schema->hasTable($this->tablePrefix . '_reset_password_request') === false) {
+
+            $table = $schema->createTable($this->tablePrefix . '_reset_password_request');
+            $table->addColumn('id', 'integer', ['autoincrement' => true, 'notnull' => true]);
+            $table->addColumn('user_id', 'integer', ['notnull' => true]);
+            $table->addColumn('selector', 'string
8000
9;, ['notnull' => true, 'length' => 20]);
+            $table->addColumn('hashed_token', 'string', ['notnull' => true, 'length' => 100]);
+            $table->addColumn('requested_at', 'datetime',
+                ['notnull' => true, 'comment' => '(DC2Type:datetime_immutable)']);
+            $table->addColumn('expires_at', 'datetime',
+                ['notnull' => true, 'comment' => '(DC2Type:datetime_immutable)']);
+
+            $table->setPrimaryKey(['id']);
+            $table->addIndex(['user_id'], 'IDX_D04070DCA76ED395');
+            $table->setSchemaConfig($schemaConfig);
+            $table->addForeignKeyConstraint(
+                $this->tablePrefix . '_user',
+                ['user_id'],
+                ['id'],
+                [],
+                'FK_D04070DCA76ED395'
+            );
+        }
+
+        $fieldTranstationTable = $schema->getTable($this->tablePrefix . '_field_translation');
+        foreach ($fieldTranstationTable->getIndexes() as $index) {
+            if ($index->getName() === 'bolt_field_translation_unique_translation') {
+                $fieldTranstationTable->renameIndex('bolt_field_translation_unique_translation',
+                    'field_translation_unique_translation');
+            }
         }
     }
 
-    public function down(Schema $schema) : void
+    public function down(Schema $schema): void
     {
     }
 }
diff --git a/migrations/Version20211123103530.php b/migrations/Version20211123103530.php
@@ -32,6 +32,20 @@ public function up(Schema $schema): void
         $userTable = $schema->getTable($this->tablePrefix . '_user_auth_token');
         $indexes = $userTable->getIndexes();
 
+        // Add index needed by foreign key before remove unique index
+        $hasIndex = false;
+        foreach ($indexes as $index) {
+            if (strtoupper($index->getName()) === 'IDX_8B90D313A76ED395') {
+                $hasIndex = true;
+            }
+        }
+
+        // If no index found, add it
+        if (!$hasIndex) {
+            $userTable->addIndex(['user_id'], 'IDX_8B90D313A76ED395');
+        }
+
+        // Remove unique index if found
         foreach($indexes as $index) {
             if ($index->getColumns() === [0 => 'user_id'] && $index->isUnique()) {
                 $userTable->dropIndex($index->getName());

diff --git a/migrations/Version20221004090755.php b/migrations/Version20221004090755.php
@@ -34,12 +34,28 @@ public function up(Schema $schema): void
         if (! $userTable->hasColumn('about')) {
             $userTable->addColumn('about', 'string', ['notnull' => false, 'length' => 1024]);
         }
+
+        $contentTable = $schema->getTable($this->tablePrefix . '_content');
+
+        if (! $contentTable->hasColumn('title')) {
+            $contentTable->addColumn('title', 'string', ['notnull' => false, 'length' => 191]);
+        }
+        if (! $contentTable->hasColumn('list_format')) {
+            $contentTable->addColumn('list_format', 'string', ['notnull' => false, 'length' => 191]);
+        }
+
     }
 
     public function down(Schema $schema): void
     {
         $userTable = $schema->getTable($this->tablePrefix . '_user');
 
         $userTable->dropColumn('about');
+
+        $contentTable = $schema->getTable($this->tablePrefix . '_content');
+
+        $contentTable->dropColumn('title');
+        $contentTable->dropColumn('list_format');
+
     }
 }