teamhg-memex / agnostic Goto Github PK

When I tried bootstrapping my app (brand new, empty migrations, empty database) I got a connection error as follows:

Error: Cannot connect to database: Authentication method 10 not recognized by pg8000.

Dug in, found that this is an issue with the version of pg8000 currently in the setup.py: it does not support method 10 at all.

The latest pg8000 does support it, and updating my local requirement to 1.23.0 allowed me to bootstrap.

Will happily report back once I've developed on this application a bit to make sure that this upgraded requirement doesn't cause any obvious issues.

(Also glad to submit a pull request to update the requirement.)

The schema support for PostgreSQL is still marked as "experimental" because nobody has really used it in production. An integration test that uses Postgres schemas would give me confidence that this feature is actually working and useful.

This is the last big feature before a 1.0 release

I am building a multi-tenant application where each tenant's data will be stored in their own postgres schema. Note that in postgres, a schema != database, it is more like a namespace within a database. See, for example: https://github.com/radiant/radiant/wiki/Using-multiple-PostgreSQL-schemas

Anyway, I would like to use a single admin user to be able to run the same set of migrations repeatedly targeting all of the different schemas. To do this, we have to be able to change the database connection's search path before running the migration scripts.

Looking at the code, it looks like this would come down to:

• Adding a configuration variable for schema (unfortunately, "schema" is already used for "database")
• Add code at connection time to issue the proper "SET search_path..." commands.

An alternative would be be to allow some kind of pre-migration code hook where you could do whatever you wanted to the connection.

There is one restore method in the interface that serves double duty: it restores full database backups (including data) and it also restores snapshots (DDL only). Backups can often be done more efficiently than a snapshot, i.e. a SQLite database can be backed up by copying a file and Postgres has a binary database backup format. However, because there is only one restore command, backups must be written as SQL files. If there were separate "restore backup" and "restore snapshot" methods, then "restore backup" could use the more efficient approach, and "restore snapshot" would use SQL files.

It appears that agnostic is unable to find mysqldump despite it being present in PATH. This is happening to me on macOS Catalina using Python 3.6.7.

I created a new virtualenv using pyenv and installed agnostic. I then attempted to create a snapshot of an existing DB:

00:38 karthicr@ws1:agnostic_test $ pyenv virtualenv 3.6.7 agnostic_test
Looking in links: /var/folders/yq/82xt_rsn3ds1473grw012dqxtx0bq9/T/tmp0qi57336
Requirement already satisfied: setuptools in /Users/karthicr/.pyenv/versions/3.6.7/envs/agnostic_test/lib/python3.6/site-packages (39.0.1)
Requirement already satisfied: pip in /Users/karthicr/.pyenv/versions/3.6.7/envs/agnostic_test/lib/python3.6/site-packages (10.0.1)

00:39 karthicr@ws1:agnostic_test $ pyenv local agnostic_test

(agnostic_test) 00:39 karthicr@ws1:agnostic_test $ pip install agnostic[mysql]
Looking in indexes: https://pypi.python.org/simple
Collecting agnostic[mysql]
  Using cached https://files.pythonhosted.org/packages/35/7f/30dfb7689a58ff1a2f9bf9ac9750c21e08744b96ad1490fd2d3407fbf480/agnostic-1.0.1.tar.gz
Collecting Click<8.0,>=7.0 (from agnostic[mysql])
  Using cached https://files.pythonhosted.org/packages/fa/37/45185cb5abbc30d7257104c434fe0b07e5a195a6847506c074527aa599ec/Click-7.0-py2.py3-none-any.whl
Collecting sqlparse<0.3.0,>=0.2.4 (from agnostic[mysql])
  Using cached https://files.pythonhosted.org/packages/65/85/20bdd72f4537cf2c4d5d005368d502b2f464ede22982e724a82c86268eda/sqlparse-0.2.4-py2.py3-none-any.whl
Collecting PyMySQL<0.10.0,>=0.9.2 (from agnostic[mysql])
  Using cached https://files.pythonhosted.org/packages/ed/39/15045ae46f2a123019aa968dfcba0396c161c20f855f11dea6796bcaae95/PyMySQL-0.9.3-py2.py3-none-any.whl
Installing collected packages: Click, sqlparse, PyMySQL, agnostic
  Running setup.py install for agnostic ... done
Successfully installed Click-7.0 PyMySQL-0.9.3 agnostic-1.0.1 sqlparse-0.2.4

(agnostic_test) 00:39 karthicr@ws1:agnostic_test $ pip freeze
agnostic==1.0.1
Click==7.0
PyMySQL==0.9.3
sqlparse==0.2.4

(agnostic_test) 00:40 karthicr@ws1:agnostic_test $ mkdir migrations

(agnostic_test) 00:40 karthicr@ws1:agnostic_test $ agnostic -t mysql -u karthicr -d playground snapshot current.sql
Enter password for "karthicr" on "playground":
Creating snapshot of database [playground]…
Error: Not able to create snapshot: [Errno 2] No such file or directory: 'mysqldump': 'mysqldump'

(agnostic_test) 00:40 karthicr@ws1:agnostic_test $ python --version
Python 3.6.7

(agnostic_test) 00:43 karthicr@ws1:agnostic_test $ pip --version
pip 19.3.1 from /Users/karthicr/.pyenv/versions/3.6.7/envs/agnostic_test/lib/python3.6/site-packages/pip (python 3.6)

As you can see from the above, I get: Error: Not able to create snapshot: [Errno 2] No such file or directory: 'mysqldump': 'mysqldump'.

However I can confirm mysqldump is available and present in the PATH and running the snapshot command manually results in an output.

(agnostic_test) 00:43 karthicr@ws1:agnostic_test $ which mysql
/usr/local/opt/[email protected]/bin/mysql

(agnostic_test) 00:43 karthicr@ws1:agnostic_test $ which mysqldump
/usr/local/opt/[email protected]/bin/mysqldump

(agnostic_test) 00:44 karthicr@ws1:agnostic_test $ mysqldump -h localhost -u karthicr -p --no-create-db --no-data --compact playground
Enter password:
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `agnostic_migrations` (
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `status` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `started_at` timestamp NULL DEFAULT NULL,
  `completed_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

I was going through the code and I feel like the issue is occurring because of the env dictionary at

I understand that this is necessary to pass the password to the underlying mysqldump call. However per Python 3 documentation at https://docs.python.org/3/library/subprocess.html:

If env is not None, it must be a mapping that defines the environment variables for the new process; these are used instead of the default behavior of inheriting the current process’ environment. It is passed directly to Popen.

So when the env dictionary is passed as a parameter to subprocess.Popen, it is overwriting the existing environment variables and as a result agnostic fails.

At least that is what I think is happening. Looking forward to your thoughts.

The documentation says:

Re-order the migrations by prefixing the file names with special characters. An at-sign (@) sorts to the top, while an underscore (_) sorts to the bottom.

But this apparently isn't true. I was in an environment this morning where an @ was sorted below a 0 (zero). I wonder if this depends on locale? (The environment was inside a docker container with LC_ALL=C.UTF-8.)

I'm currently using agnostic in a bash run script, where the database can exist from previous versions, and means the database can be in one of 3 states:
1 It is a fresh installation, and the database will be created and agnostic will be bootstrapped.
2 The database is already created, but agnostic has not been bootstrapped.
3 The database is created and agnostic has been bootstrapped.

Dealing with cases 2 and 3 are awkward, because I need to catch the error thrown by agnostic, and parse the message to see if agnostic needs to run bootstrap or migrate.

It would be nice if there were some way to run migrations which would fallback to bootstrapping with agnostic. At the very least a silent run option would be nice, where I could run both bootstrap and then migrate, and ignore all errors.

Hi,

Is it possible to run the migration on a database without password? Is seems to be a requirement and I couldn't find a way to pass an empty string…

Thanks

Hi,

When I bootstrap agnostic, it fills the agnostic_migrations table with my migrations with a "boostrapped" status. And then, when I try to run the migrate command, it tells me "There are no pending migrations." but my migrations were never run.

The debug flag is meant to print full stack traces when an exception is uncaught, but in practice some stack traces are printed and others are suppressed, because the debug handling is sprinkled through a bunch of different places. This needs to be cleaned up and all uncaught exceptions handled in one place.

This repo hasn't been maintained in a long time and I recently realized it doesn't even install on one of my projects due to dependency conflicts. I've ported it over to mehaase/agnostic, cleaned it up, and released a new version.

The postgres schema support is marked as "experimental" because I haven't tried using it in production. An integration test suite would give me confidence that this feature is implemented usefully and correctly.

Does agnostic have basic support for MariaDB?

Thank you and congratulations for this great project

It will be good to add possibility to work with any db to be truly agnostic.

It may be plugin model or some custom python code.

Hi Team,

We are using agnostic to run all migration scripts (for mysql). As pert of this i have to drop a column if exists in table. for this i have added below script in migration file.

set @START_DATE_Check := (
select count(*) from information_schema.columns
where TABLE_NAME='COMMAND'
and COLUMN_NAME='START_DATE'
and TABLE_SCHEMA= 'ICPS'
) ;
set @sqlstmt := if(@START_DATE_Check > 0, 'alter table COMMAND drop column START_DATE', 'select "START_DATE does not exist in COMMAND" AS "Comment"') ;
prepare stmt from @sqlstmt ;
execute stmt ;

The above script working fine when i ran in mysql workbench, the column is removed if exists or else displayed the provided message.

Where as while running migration scripts by agnostic, there is no error and no impact on the table as well. (not removed the column).

Can you please help me here ?

If you run agnostic migrate and there are no pending migrations, you get an error message and a non-zero exit code! This should not be treated like an error; it's a totally normal and harmless condition. It should print an informational message and exit with 0.

Cc: @bcmackintosh