User table isn't created during installation

[sepro]

Hi @hariszaf !

I've tried to run the tool locally but ran across a few minor issues setting up and linking docker (which I'd rather discuss in person later on as this might be my setup or a bit of extra documentation that needs to be added for windows users). But one thing I think is an issue is that a Users table, required by the app isn't created during the setup. I've logged into the docker container running mysql and there is a table structure but no spot for the Users data.

ProgrammingError
sqlalchemy.exc.ProgrammingError: (pymysql.err.ProgrammingError) (1146, "Table 'bacterialgrowth.users' doesn't exist")
[SQL: SELECT `Users`.id, `Users`.uuid, `Users`.`orcidId`, `Users`.`orcidToken`, `Users`.name, `Users`.`isAdmin`, `Users`.`createdAt`, `Users`.`updatedAt`, `Users`.`lastLoginAt` 
FROM `Users` 
WHERE `Users`.uuid = %(uuid_1)s 
 LIMIT %(param_1)s]
[parameters: {'uuid_1': '8bb059ad-e553-49d3-aa92-1fd672abc485', 'param_1': 1}]
(Background on this error at: https://sqlalche.me/e/20/f405)

Of note: I don't have the ORCID api so I left that blank in the config.

[hariszaf]

Hi @sepro!

Hmm, that's funny, so you ran

bin/migrations-run

the rest of the tables are there, but Users is not ?

We'll have a look but since then, I guess you can always run directly the corresponding migration.

Also, I am not sure I'm following you regarding the ORCID, where did you leave that blank?
You are not supposed to have an ORCID to fire an instance of the app, is that right @AndrewRadev ?

[AndrewRadev]

You are not supposed to have an ORCID to fire an instance of the app, is that right @AndrewRadev ?

Yes, the ORCID environment variables are only necessary for logging in, their presence shouldn't be needed to boot.

Is the rest of the database structure successfully created? Were there any errors during bin/migrations-run? I remember I had some odd issues with one particular migration, I've been thinking it might be a good idea to just reset the migration state to a single one that creates the structure.

Maybe you could share the SHOW TABLES output and maybe a SELECT * from MigrationVersions; to debug if anything is missing. You could also rebuild it by using the db/schema.sql file that is stored in git, something like this, but for the development database: https://github.com/msysbio/bacterial_growth/blob/e7c2f2b9b7cefe518a1a3d62f43601af340cd194/scripts/dev/reset_test_database

I will say, the NCBI and ChEBI dumps are not included in the instructions, I need to make those importable as well, but it will likely happen in the next week or so.

[AndrewRadev]

sqlalchemy.exc.ProgrammingError: (pymysql.err.ProgrammingError) (1146, "Table 'bacterialgrowth.users' doesn't exist")

What's odd to me in that message is that the database name and table name are lowercased. You can see that the query is for Users with a capital U, but the message is lowercase. I thought it might just be some odd reporting quirk from mysql, but I dropped the table locally, and I'm getting this:

sqlalchemy.exc.ProgrammingError: (pymysql.err.ProgrammingError) (1146, "Table 'BacterialGrowth.Users' doesn't exist")
[SQL: SELECT `Users`.id, `Users`.uuid, `Users`.`orcidId`, `Users`.`orcidToken`, `Users`.name, `Users`.`isAdmin`, `Users`.`createdAt`, `Users`.`updatedAt`, `Users`.`lastLoginAt` 
FROM `Users` 
WHERE `Users`.uuid = %(uuid_1)s 
 LIMIT %(param_1)s]
[parameters: {'uuid_1': '_andrew', 'param_1': 1}]
(Background on this error at: https://sqlalche.me/e/20/f405)`

The error message uses correct casing, "Table 'BacterialGrowth.Users' doesn't exist". But I don't know why the installation on your side would show a lowercased version of that message.

[sepro]

@AndrewRadev You might be on to something with that migration issue. I tried to re-run the migrations and you were right, there actually is an issue there. Full error is included below.

It has been ages since I've used Flask and SQLAlchemy, though I usually included a bit of code which allowed new installations to set up the database using SQLAlchemy based on the models in the code. That might be a way to work around migrations not working.

https://github.com/sepro/CoNekT/blob/25d0187030bcb85bb99125af4fd0c0c11aa012cb/run.py#L11

I don't know if that is still a semi-decent solution or if your implementation would even support that.

(C:\GBW_MyPrograms\mgrowthdb) D:\Git\bacterial_growth>python migrations-run
> Migrating 2025_02_13_163206_create_measurements...
Traceback (most recent call last):
  File "C:\GBW_MyPrograms\mgrowthdb\Lib\site-packages\sqlalchemy\engine\base.py", line 1963, in _exec_single_context
    self.dialect.do_execute(
  File "C:\GBW_MyPrograms\mgrowthdb\Lib\site-packages\sqlalchemy\engine\default.py", line 943, in do_execute
    cursor.execute(statement, parameters)
  File "C:\GBW_MyPrograms\mgrowthdb\Lib\site-packages\pymysql\cursors.py", line 153, in execute
    result = self._query(query)
             ^^^^^^^^^^^^^^^^^^
  File "C:\GBW_MyPrograms\mgrowthdb\Lib\site-packages\pymysql\cursors.py", line 322, in _query
    conn.query(q)
  File "C:\GBW_MyPrograms\mgrowthdb\Lib\site-packages\pymysql\connections.py", line 563, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
                          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\GBW_MyPrograms\mgrowthdb\Lib\site-packages\pymysql\connections.py", line 825, in _read_query_result
    result.read()
  File "C:\GBW_MyPrograms\mgrowthdb\Lib\site-packages\pymysql\connections.py", line 1199, in read
    first_packet = self.connection._read_packet()
                   ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\GBW_MyPrograms\mgrowthdb\Lib\site-packages\pymysql\connections.py", line 775, in _read_packet
    packet.raise_for_error()
  File "C:\GBW_MyPrograms\mgrowthdb\Lib\site-packages\pymysql\protocol.py", line 219, in raise_for_error
    err.raise_mysql_exception(self._data)
  File "C:\GBW_MyPrograms\mgrowthdb\Lib\site-packages\pymysql\err.py", line 150, in raise_mysql_exception
    raise errorclass(errno, errval)
pymysql.err.OperationalError: (3780, "Referencing column 'studyId' and referenced column 'studyId' in foreign key constraint 'studyId' are incompatible.")

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "D:\Git\bacterial_growth\migrations-run", line 94, in <module>
    main()
  File "D:\Git\bacterial_growth\migrations-run", line 48, in main
    run(migration_module.__file__, migration_module.up, migration_module.down, direction="up")
  File "D:\Git\bacterial_growth\app\model\lib\migrate.py", line 32, in run
    up(conn)
  File "D:\Git\bacterial_growth\db\migrations\2025_02_13_163206_create_measurements.py", line 28, in up
    conn.execute(sql.text(query))
  File "C:\GBW_MyPrograms\mgrowthdb\Lib\site-packages\sqlalchemy\engine\base.py", line 1415, in execute
    return meth(
           ^^^^^
  File "C:\GBW_MyPrograms\mgrowthdb\Lib\site-packages\sqlalchemy\sql\elements.py", line 523, in _execute_on_connection
    return connection._execute_clauseelement(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\GBW_MyPrograms\mgrowthdb\Lib\site-packages\sqlalchemy\engine\base.py", line 1637, in _execute_clauseelement
    ret = self._execute_context(
          ^^^^^^^^^^^^^^^^^^^^^^
  File "C:\GBW_MyPrograms\mgrowthdb\Lib\site-packages\sqlalchemy\engine\base.py", line 1842, in _execute_context
    return self._exec_single_context(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\GBW_MyPrograms\mgrowthdb\Lib\site-packages\sqlalchemy\engine\base.py", line 1982, in _exec_single_context
    self._handle_dbapi_exception(
  File "C:\GBW_MyPrograms\mgrowthdb\Lib\site-packages\sqlalchemy\engine\base.py", line 2351, in _handle_dbapi_exception
    raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
  File "C:\GBW_MyPrograms\mgrowthdb\Lib\site-packages\sqlalchemy\engine\base.py", line 1963, in _exec_single_context
    self.dialect.do_execute(
  File "C:\GBW_MyPrograms\mgrowthdb\Lib\site-packages\sqlalchemy\engine\default.py", line 943, in do_execute
    cursor.execute(statement, parameters)
  File "C:\GBW_MyPrograms\mgrowthdb\Lib\site-packages\pymysql\cursors.py", line 153, in execute
    result = self._query(query)
             ^^^^^^^^^^^^^^^^^^
  File "C:\GBW_MyPrograms\mgrowthdb\Lib\site-packages\pymysql\cursors.py", line 322, in _query
    conn.query(q)
  File "C:\GBW_MyPrograms\mgrowthdb\Lib\site-packages\pymysql\connections.py", line 563, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
                          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\GBW_MyPrograms\mgrowthdb\Lib\site-packages\pymysql\connections.py", line 825, in _read_query_result
    result.read()
  File "C:\GBW_MyPrograms\mgrowthdb\Lib\site-packages\pymysql\connections.py", line 1199, in read
    first_packet = self.connection._read_packet()
                   ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\GBW_MyPrograms\mgrowthdb\Lib\site-packages\pymysql\connections.py", line 775, in _read_packet
    packet.raise_for_error()
  File "C:\GBW_MyPrograms\mgrowthdb\Lib\site-packages\pymysql\protocol.py", line 219, in raise_for_error
    err.raise_mysql_exception(self._data)
  File "C:\GBW_MyPrograms\mgrowthdb\Lib\site-packages\pymysql\err.py", line 150, in raise_mysql_exception
    raise errorclass(errno, errval)
sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (3780, "Referencing column 'studyId' and referenced column 'studyId' in foreign key constraint 'studyId' are incompatible.")
[SQL:
        CREATE TABLE Measurements (
            id                   INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
            bioreplicateUniqueId INT NOT NULL,
            studyId              VARCHAR(100) COLLATE utf8mb4_bin NOT NULL,
            position             VARCHAR(100) NOT NULL,
            timeInSeconds        INT NOT NULL,
            pH                   VARCHAR(100),
            unit                 VARCHAR(100),
            technique            VARCHAR(100) NOT NULL,
            value                DECIMAL(20, 3),
            std                  DECIMAL(20, 3),
            subjectType          VARCHAR(100) NOT NULL,
            subjectId            VARCHAR(100) NOT NULL,

            CONSTRAINT bioreplicateUniqueId FOREIGN KEY (bioreplicateUniqueId)
                REFERENCES BioReplicatesPerExperiment (bioreplicateUniqueId)
                ON DELETE CASCADE ON UPDATE CASCADE,
            CONSTRAINT studyId FOREIGN KEY (studyId)
                REFERENCES Study (studyId)
                ON DELETE CASCADE ON UPDATE CASCADE
        );
    ]
(Background on this error at: https://sqlalche.me/e/20/e3q8)

[AndrewRadev]

sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (3780, "Referencing column 'studyId' and referenced column 'studyId' in foreign key constraint 'studyId' are incompatible.")

This feels like a problem with character sets, it might be the case that the default character set of your mysql installation is not utf8mb4. Could you try putting this in a test.sql and checking if bin/dbconsole < test.sql successfully creates a Measurements table?

CREATE TABLE Measurements (
  id                   INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  bioreplicateUniqueId INT NOT NULL,
  studyId              VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
  position             VARCHAR(100) NOT NULL,
  timeInSeconds        INT NOT NULL,
  pH                   VARCHAR(100),
  unit                 VARCHAR(100),
  technique            VARCHAR(100) NOT NULL,
  value                DECIMAL(20, 3),
  std                  DECIMAL(20, 3),
  subjectType          VARCHAR(100) NOT NULL,
  subjectId            VARCHAR(100) NOT NULL,

  CONSTRAINT bioreplicateUniqueId FOREIGN KEY (bioreplicateUniqueId)
  REFERENCES BioReplicatesPerExperiment (bioreplicateUniqueId)
  ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT studyId FOREIGN KEY (studyId)
  REFERENCES Study (studyId)
  ON DELETE CASCADE ON UPDATE CASCADE
);

It has been ages since I've used Flask and SQLAlchemy, though I usually included a bit of code which allowed new installations to set up the database using SQLAlchemy based on the models in the code.

I do know that there's some way through SQLAlchemy to generate migrations based on the current state of the code, but I feel that this doesn't matter too much for the initial bootstrapping process, because you should just be able to do:

bin/dbconsole < db/schema.sql

Which might just be the thing I should put in the README instead. And after that I should create a "bootstrap" script that does this plus also loads NCBI and Chebi dumps. I'll try to put some time into this maybe tomorrow and I'll share a branch you could try that might include the more reliable bootstrapping instructions.

[AndrewRadev]

What's odd to me in that message is that the database name and table name are lowercased.

Ahhhh, that's just MySQL being incredibly weird... https://dev.mysql.com/doc/refman/8.4/en/windows-restrictions.html

File names are not case-sensitive on Windows, so MySQL database and table names are also not case-sensitive on Windows. The only restriction is that database and table names must be specified using the same case throughout a given statement. See Section 11.2.3, “Identifier Case Sensitivity”.

[sepro]

yeah, I noticed a few other windows specific things that didn't quite match the documentation. We can set a meeting in person if you like to go over some, that is probably more efficient. @hariszaf has my contact details and knows where to find me in the institute ;-)

With mysql not readily available on windows, the dbconsole script doesn't do the trick, so having a bootstrap script would be useful. And also on a fresh database I get the above error. (using the included docker-compose file to set up mysql). I can set this up later to test. Or check in a linux environment if there everything works, but I don't have the time to do so in the upcoming week unfortunately.

I do know that there's some way through SQLAlchemy to generate migrations based on the current state of the code, but I feel that this doesn't matter too much for the initial bootstrapping process, because you should just be able to do:

It isn't really a migration, rather it sets up all tables to match what is defined in the models, so if all indices, constrains, ... are defined in the code it is a nice way to get started. I liked it, as I never got the migrations to work reliably and consistently and those weren't really needed for that platform anyway. Though there are other options to choose from, that is the one that did the trick for me in the past.

[AndrewRadev]

@sepro I've implemented a full docker-compose image in a PR in my own fork of the repo, the branch is docker-setup: AndrewRadev#58

I still have to try the db.create_all method to create tables, but independently of that, I'd like to have a working docker setup that someone could use to start the app. When you have the time, could you try out that branch with the instructions in the README and let me know what breaks, what is under-documented, etc? https://github.com/AndrewRadev/bacterial_growth/blob/docker-setup/README.md#local-installation-using-docker

[sepro]

I'm actually getting the same error message (#35 (comment)) when running

docker-compose -f docker-compose-full.yml up --build

It sets up the containers for redis and mysql as intended and one for the app, installs dependencies but fails to go through the various migrations.

This can also be an issue that the foreign key in that file isn't the same type (or collation) as in the table it is referencing (suggesting an issue with the migration file. So I removed COLLATE utf8mb4_bin from the offending migration step, but hits the same issue on a next migration step with projectUniqueID. (When creating the table ProjectUsers)

Potentially a migration step changing the collation of the these earlier ?

[AndrewRadev]

The init script only runs migrations if the database exists: https://github.com/AndrewRadev/bacterial_growth/blob/436f632288972534fc04c81f5c8d2740f8e570ba/scripts/init.sh#L13-L37

Could you try completely removing your database (the contents of the var/ directory, or maybe just run a DROP DATABASE BacterialGrowth) and rerunning? I should have thought of that.

[sepro]

Hi Andrew,

I can see there is an issue there as well. If I drop the database, and try to bring the docker containers back up it complains about the database 'BacterialGrowth' not existing. If I create an empty database with that name it goes into the migration (which probably has some issues). Is there something that creates/requires/checks the database before that part? There probably is, given even with a fresh install, the migration is started.

I did get around it dropping the database BacterialGrowth, creating an empty one with the same name and manually running ./db/shema.sql from within the mysql container to create the required structure.

After that step I could bring up all containers successfully with docker-compose -f ..... up --build

Potential easy fix (as the migration strategy would work weren't it for those COLLATE issues) would be to make the current schema in ./db/schema.sql the initial version for the migrations (copy to ./db/migrations/000_initial_schema.sql) and remove the python migrations in ./db/migration. Essentially making this the first released version and only offering migrations from this point onwards.

[AndrewRadev]

make the current schema in ./db/schema.sql the initial version for the migrations (copy to ./db/migrations/000_initial_schema.sql) and remove the python migrations in ./db/migration. Essentially making this the first released version and only offering migrations from this point onwards.

Yes, I've considered this, if nothing else, then at least for cleanup purposes -- going forward, all these migrations will not be necessary, so there's no need for them other than maybe for documentation purposes. But doing this to solve the problem just pushes the issues down the line. It should be possible to load from the schema, and you do say it works, so it's just about the init script correctly detecting it should do that.

I think I actually know what the problem is here, it's this check:

DB_NAME=$(echo "SHOW DATABASES LIKE 'BacterialGrowth'" | bin/dbconsole | tail -1)

if [ "$DB_NAME" = "BacterialGrowth" ]; then

Once again, mysql is probably outputting "bacterialgrowth" on windows rather than "bacterialgrowth" 😅. I've pushed a (potential) fix to the docker-setup branch: AndrewRadev@0f651d7. Could you pull and try again?

[sepro]

I found what happens: when you use docker compose to build/start the app (I'm using docker for the app, not trying to run it natively on windows anymore) docker-compose-full.yml starts ./bin/prod-server which will attempt the migration bin/migrations-run which creates the database if it doesn't exist, but fails (at least on my end) after a few migrations.

So at the point the docs mention running ./script/init.sh that script pick up that the database exists (correctly as it has been created by simply creating the containers) and try to run the migrations again (which fail again). So at that point removing the existing database manually is required for this to work.

[AndrewRadev]

Oh, that makes sense, I wonder how I didn't hit that myself. Or maybe it did happen, I just didn't notice it in the logs.

The original reason I added the migrations to the prod server was to avoid forgetting to run them before restarting, but this does come with a number of drawbacks anyway. If nothing else, it's a bit counterintuitive that launching the server would also run additional setup code.

I've removed the two lines from the prod server, and removed the automatic database creation by the mysql docker image. This should avoid the problem, I think.