[MONARC-FO install] Problem initializing the DB (ref. 20160602124700_remove_anr_id_rolf_tables.php)

[verzulli]

Hi!
I'm trying to fully automate a simple MONARC deployment, including ONLY the FO (with no BO, RPX and CFG).
I'm quite comfortable with ansible, and based my activities on the official ansible-ubuntu project.

At the moment, I'm able to almost deploy a fully-working FO, on a stock Ubuntu 20.04 box, via the ansible playbook and roles I released here [A-GPL] [my monarcco and monarcfo roles are HEAVILY based on official ones]

Unfortunately, at the moment, I'm having problems INITIALIZING THE DATABASE.

After creating the two databases monarc_common and monarc_cli [see here] and creating the user [see here], I tried to initialize the database by the way of:

• the Core migrations I found in [...]/module/Monarc/Core/migrations/db/
• the FrontOffice migrations I found in [...]/module/Monarc/FrontOffice/migrations/db/

(related ansible tasks are here)

Unfortunately, after succesfully applying the first migration ( 20160513123336_initial_db.php ), the second one ( 20160602124700_remove_anr_id_rolf_tables.php ) raise an error.

[...]
 == 20160602124700 RemoveAnrIdRolfTables: migrating
START TRANSACTION;
 -- Altering table rolf_tags
ALTER TABLE `rolf_tags` DROP COLUMN `anr_id`;
PDOException: SQLSTATE[42000]: Syntax error or access violation: 1072 Key column 'anr_id' doesn't exist in table in /var/lib/monarc/releases/MonarcAppFO-v2.12.5-p4/vendor/robmorgan/phinx/src/Phinx/Db/Adapter/PdoAdapter.php:184
[...]

namely, the SQL statement ALTER TABLE 'rolf_tags' DROP COLUMN 'anr_id'; failed!

If I connect to MariaDB to give a look:

root@monarc-demo:/var/lib/monarc/fo# mysql -u root monarc_common
[...]
MariaDB [monarc_common]>  desc rolf_tags;
+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| id         | int(11) unsigned | NO   | PRI | NULL    | auto_increment |
| anr_id     | int(11) unsigned | YES  | MUL | NULL    |                |
| code       | char(100)        | YES  |     | NULL    |                |
| label1     | varchar(255)     | YES  |     | NULL    |                |
| label2     | varchar(255)     | YES  |     | NULL    |                |
| label3     | varchar(255)     | YES  |     | NULL    |                |
| label4     | varchar(255)     | YES  |     | NULL    |                |
| creator    | varchar(255)     | YES  |     | NULL    |                |
| created_at | datetime         | YES  |     | NULL    |                |
| updater    | varchar(255)     | YES  |     | NULL    |                |
| updated_at | datetime         | YES  |     | NULL    |                |
+------------+------------------+------+-----+---------+----------------+
11 rows in set (0.003 sec)

MariaDB [monarc_common]>

it seems that the anr_id field is there. But if I launch the ALTER TABLE statement used by the migration:

MariaDB [monarc_common]> ALTER TABLE `rolf_tags` DROP COLUMN `anr_id`;
ERROR 1072 (42000): Key column 'anr_id' doesn't exist in table
MariaDB [monarc_common]>

I got a "doesn't exist" error.

As I'm not much a SQL-voodoo guy... I'm not understanding where the error is.

So, here I am, asking you the main question:

1. Which is the proper way to initialize the database, with tables, indexes and the likes, right after its creation? Should I run the migrations? Or there are other/better approaches (BTW: I see a db-bootstrap folder, in the sources, so I'm a bit puzzled);

2. once above initialization will be properly performed, how can I "seed" database content properly? Via including Core & FrontOffice "seeds"? Or with some other content/dump?

Please note that, in the meantime, I simply dumped/restored the database included in the OVA virtual appliance.

Thanks in advance for your help!

[ruslanbaidan]

Hello,

The issue that you faced is due to the Monarc version upgrade and use of PHP8. Some packages required updates and some migrations were deprecated/broken as a result.
It was fixed and pushed 2 days ago, but only today the new versions of FrontOffice and BackOffice were tagged.
We have not resolved all the ansible challenges to make it work with the new release and necessary libraries installations.
It would be recommended to wait till mid of next week and retry the process.

Regarding the database initialisation.
It would be better to load the structure and data first, as you noted from the db-bootstrap (but only once) to have pre-filled data in the monarc_common db and only after run the upgrade-db.sh. More details are here.

[verzulli]

Thanks for answering.

I just moved the deploy from focal/20.04 to jammy/22.04 so to have a stock php8.1 ready to go.

I also updated my ansible playbook, regarding database initialization, in such a way:

1. db monarc_common is created and initialized with db-bootstrap/monarc_structure.sql and db-bootstrap/monarc_data.sql dump files;
2. phinx core/migrations are applied;
3. phinx FrontOffice/migrations are applied;
4. phinx FrontOffice/migrations are seeded.

Actually, I have NOT run the ./scripts/update-all.sh -d script, as it seems to not fit my environment. That's why I opted for point 2, 3 and 4 above.

Now the web-application seems to run properly so... I'll take some time to test it a bit deeper.

At the moment, I have some minor problems with language (something is in french, even tough I configured EN as the language in the UI) but... that's not a big issue.

Thanks again for your support.

---

P.S.: in case you're curious, here is the commit where I applied edits mentioned above

[ruslanbaidan]

Hello,

Thanks for the details and your contributions to Monarc.
Although you are making it for your needs, I think the work can be useful for the community.

Regarding the version that you are using. We have not performed all the live cycle of tests with it. I'd say this is still a pre-release.
But it's a good occasion that you could help with the testing.