CodeIgniter Forums
Migrating Migrations Table from CI3 for existing databases - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: CodeIgniter 4 (https://forum.codeigniter.com/forumdisplay.php?fid=28)
+--- Forum: CodeIgniter 4 Support (https://forum.codeigniter.com/forumdisplay.php?fid=30)
+--- Thread: Migrating Migrations Table from CI3 for existing databases (/showthread.php?tid=86564)



Migrating Migrations Table from CI3 for existing databases - objecttothis - 02-02-2023

The documentation gives update instructions in step 9 to run the migration in development after dropping the existing table then exporting the table to production and import the data.  This works fine for new installs, but does nothing for people upgrading the WebApp from the previous version which was on CI3 to the new version which will be on CI4. What I (and I think anyone else converting an existing CI3 WebApp) need is a means to upgrade the CI3 migrations table to CI4 without losing the current migration version, since CI4 migrate re-running migrations that have already been run in a CI3 version of the webapp will likely break things.

I was able to generate the CI4 version of the table by renaming the CI3 table and running php spark migrateConfusedtatus.  From that I generated a script to modify the CI3 table to match the structure of the CI4 version.
Code:
ALTER TABLE `ospos_migrations`
MODIFY COLUMN `version` VARCHAR(255) NOT NULL,
ADD `id` bigint(20) UNSIGNED NOT NULL,
ADD `class` varchar(255) NOT NULL,
ADD `group` varchar(255) NOT NULL,
ADD `namespace` varchar(255) NOT NULL,
ADD `time` int(11) NOT NULL,
ADD `batch` int(11) UNSIGNED NOT NULL;

ALTER TABLE `ospos_migrations`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `ospos_migrations`
  MODIFY COLUMN `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

UPDATE `ospos_migrations`
SET
`namespace` = 'App',
`time` = 0,
`batch` = 0
WHERE `ospos_migrations`.`id` = 1

The problem is that if I run php spark migrateConfusedtatus again, it doesn't see the existing version

The above script leaves me with a single row containing:
version 20210714140000 (this is the second to last migration)
id 1
class NULL
group NULL
namespace App
time 0
batch 0

Code:
php spark migrate:status

CodeIgniter v4.3.1 Command Line Tool - Server Time: 2023-02-03 00:23:29 UTC+04:00

+-----------+----------------+------------------------------+-------+-------------+-------+
| Namespace | Version        | Filename                    | Group | Migrated On | Batch |
+-----------+----------------+------------------------------+-------+-------------+-------+
| App      | 20170501150000 | upgrade_to_3_1_1            | ---  | ---        | ---  |
| App      | 20170502221506 | sales_tax_data              | ---  | ---        | ---  |
| App      | 20180225100000 | 2_0                          | ---  | ---        | ---  |
| App      | 20180501100000 | 3_2_1                        | ---  | ---        | ---  |
| App      | 20181015100000 | attributes                  | ---  | ---        | ---  |
| App      | 20190111270000 | upgrade_to_3_3_0            | ---  | ---        | ---  |
| App      | 20190129212600 | indiagst                    | ---  | ---        | ---  |
| App      | 20190213210000 | indiagst1                    | ---  | ---        | ---  |
| App      | 20190220210000 | indiagst2                    | ---  | ---        | ---  |
| App      | 20190301124900 | decimal_attribute_type      | ---  | ---        | ---  |
| App      | 20190317102600 | add_iso_4217                | ---  | ---        | ---  |
| App      | 20190427100000 | paymenttracking              | ---  | ---        | ---  |
| App      | 20190502100000 | refundtracking              | ---  | ---        | ---  |
| App      | 20190612100000 | dbfix                        | ---  | ---        | ---  |
| App      | 20190615100000 | fix_attribute_datetime      | ---  | ---        | ---  |
| App      | 20190712150200 | fix_empty_reports            | ---  | ---        | ---  |
| App      | 20191008100000 | receipttaxindicator          | ---  | ---        | ---  |
| App      | 20191231100000 | paymentdatefix              | ---  | ---        | ---  |
| App      | 20200125100000 | saleschangeprice            | ---  | ---        | ---  |
| App      | 20200202000000 | taxamount                    | ---  | ---        | ---  |
| App      | 20200215100000 | taxgroupconstraint          | ---  | ---        | ---  |
| App      | 20200508000000 | image_upload_defaults        | ---  | ---        | ---  |
| App      | 20200819000000 | modify_attr_links_constraint | ---  | ---        | ---  |
| App      | 20201108100000 | cashrounding                | ---  | ---        | ---  |
| App      | 20201110000000 | add_item_kit_number          | ---  | ---        | ---  |
| App      | 20210103000000 | modify_session_datatype      | ---  | ---        | ---  |
| App      | 20210422000000 | database_optimizations      | ---  | ---        | ---  |
| App      | 20210422000001 | remove_duplicate_links      | ---  | ---        | ---  |
| App      | 20210714140000 | move_expenses_categories    | ---  | ---        | ---  |
| App      | 20220127000000 | convert_to_ci4              | ---  | ---        | ---  |
+-----------+----------------+------------------------------+-------+-------------+-------+

And of course it tries to run all the migrations when I run php spark migrate. CI3 migrations is much simpler, so I'm trying to figure out missing information. \App\Config\Migrations $timestampFormat is 'YmdHis_'
  • Do I need a row for each migration in the new table that has run?
  • What needs to be entered in class, group time and batch columns in order for CI4 migrations to pick up the migrations that have already run from CI3?
  • I think I need to manually run whatever script I generate to convert the migrations table when it detects that the installation still has a CI3 migrations table unless \MigrationRunner\force() with the migration file will actually work... I kinda doubt force() would work, because it's probably expecting a CI4 table. Is this assumption accurate?



RE: Migrating Migrations Table from CI3 for existing databases - kenjis - 02-02-2023

(02-02-2023, 01:59 PM)objecttothis Wrote: The documentation gives update instructions in step 9 to run the migration in development after dropping the existing table then exporting the table to production and import the data.  This works fine for new installs, but does nothing for people upgrading the WebApp from the previous version which was on CI3 to the new version which will be on CI4.

Why?


RE: Migrating Migrations Table from CI3 for existing databases - objecttothis - 02-02-2023

(02-02-2023, 05:24 PM)kenjis Wrote:
(02-02-2023, 01:59 PM)objecttothis Wrote: The documentation gives update instructions in step 9 to run the migration in development after dropping the existing table then exporting the table to production and import the data.  This works fine for new installs, but does nothing for people upgrading the WebApp from the previous version which was on CI3 to the new version which will be on CI4.

Why?

The next sentence of what I wrote answers your question

Quote:What I (and I think anyone else converting an existing CI3 WebApp) need is a means to upgrade the CI3 migrations table to CI4 without losing the current migration version, since CI4 migrate re-running migrations that have already been run in a CI3 version of the webapp will likely break things.

If a user of version 1.0 of the webapp (which ran on CI3) runs migrations 1-5, then upgrades to version 1.1 (which runs on CI4), you need migrations to pick up where version 1.0 left off and run, say, migrations 6-8. Following the current upgrade procedure starts migrations over, so it will re-run migrations 1-5. If any of those migrations does something like subtract 1 from the price of all products because of some previous bug, it's going to do that twice and you don't want that. More likely is sql to error because, say, migration 2 alters the name of a column that was deleted in migration 4 but since it was already deleted when the migration ran the first time, the script will error when executed the second time.


RE: Migrating Migrations Table from CI3 for existing databases - kenjis - 02-02-2023

(02-02-2023, 10:55 PM)objecttothis Wrote: Following the current upgrade procedure starts migrations over, so it will re-run migrations 1-5.

No, don't run migrations again in the production server.

You just run all migrations 1-5 in a new development server,
and export the migration table in the development server
and import it to the production server.


RE: Migrating Migrations Table from CI3 for existing databases - objecttothis - 02-02-2023

(02-02-2023, 11:16 PM)kenjis Wrote:
(02-02-2023, 10:55 PM)objecttothis Wrote: Following the current upgrade procedure starts migrations over, so it will re-run migrations 1-5.

No, don't run migrations again in the production server.

You just run all migrations 1-5 in a new development server,
and export the migration table in the development server
and import it to the production server.

If we were talking about a single installation, then what you're saying would be fine. This is going into opensource software where we can't just have each user of the webapp spin up a development server, run migrations, then import to production. Even if we could, migrations would fail on the development copy of the production database for the same reason I described. The migrations I'm talking about from versions of the webapp prior to the CI4 changeover are not just database table structure changes. Some of them are data correction changes, so the migrations require the production data in order to work.

Further, one user might be migrating from 1.0 of the webapp while another might be migrating from 1.0.12, so people will be at different points in the migration when they go to version 1.1 which is on CI4. This is why the 1.1 version of the webapp (on CI4) needs to start from where the previous CI3 migrations version number left off.

I think I might understand what you mean now... If, on my dev box, I start from a clean install of the webapp, then run migrations against it so that it does all the migrations, I can then export that table and create a script which will need to run before the first CI4 migration and import all the rows up to the CI3 version on that install, then run migrations... that could work. I wish it was less complicated though. It would be nicer if migrate recognized the version number from CI3 migrations and didn't try to run migrations before that.


RE: Migrating Migrations Table from CI3 for existing databases - kenjis - 02-03-2023

Yes, that's what I mean.

If you are creating an open source product and need to make it easy for its users to migrate from multiple versions in CI3 to a new version in CI4, you will need to create a tool like this:

1. read the CI3 migration table to get the last migration version
2. create a new migration table for CI4 and create migration table data corresponding to that CI3' migration versions from the first to the last (do not run real migrations, just generating the same table records as if you run migrations)
3. drop the CI3 migration table and rename the new CI4 migration table created in 2.


RE: Migrating Migrations Table from CI3 for existing databases - kenjis - 02-03-2023

(02-02-2023, 11:24 PM)objecttothis Wrote: It would be nicer if migrate recognized the version number from CI3 migrations and didn't try to run migrations before that.

If you don't need to go back to version 0 with migrations,
what if you just delete the migration files that were run on CI3?


RE: Migrating Migrations Table from CI3 for existing databases - objecttothis - 02-03-2023

(02-03-2023, 12:50 AM)kenjis Wrote:
(02-02-2023, 11:24 PM)objecttothis Wrote: It would be nicer if migrate recognized the version number from CI3 migrations and didn't try to run migrations before that.

If you don't need to go back to version 0 with migrations,
what if you just delete the migration files that were run on CI3?

This is an interesting idea. No, I don't think there is a need to revert once a migration has run.

I could programmatically:
- Check the CI3 version in the database.
- delete all migrations files to that point.
- drop the old table.
- Run CI4 migrations.

The only potential hiccup I can see is needing to have write access to that folder from the PID running php.