How to Fix bug when i migrate: psycopg2.errors.UndefinedFunction: function gen_random_uuid() does not exist

[peelar]

Discussed in https://github.com/saleor/saleor/discussions/10644

^{Originally posted by yetqdn September 13, 2022}

(saleor-venv) root@vps:/home/saleor# python manage.py migrate 2022-09-07 14:06:34,394 WARNING saleor.core.jwt_manager RSA_PRIVATE_KEY is missing. Using temporary key for local development with DEBUG mode. [PID:109884:MainThread] 2022-09-07 14:06:34,639 WARNING py.warnings /home/saleor/saleor/urls.py:60: UserWarning: The debug toolbar was not installed. Ignore the error. settings.py should already have warned the user about it. warnings.warn( [PID:109884:MainThread] Operations to perform: Apply all migrations: account, app, attribute, auth, channel, checkout, contenttypes, core, csv, discount, django_celery_beat, django_prices_openexchangerates, django_prices_vatlayer, giftcard, invoice, menu, order, page, payment, plugins, product, schedulers, shipping, site, sites, thumbnail, warehouse, webhook Running migrations: Applying account.0068_user_uuid...Traceback (most recent call last): File "/home/saleor/saleor-venv/lib/python3.9/site-packages/django/db/backends/utils.py", line 87, in _execute return self.cursor.execute(sql) psycopg2.errors.UndefinedFunction: function gen_random_uuid() does not exist HINT: No function matches the given name and argument types. You might need to add explicit type casts.

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

Traceback (most recent call last): File "/home/saleor/manage.py", line 10, in execute_from_command_line(sys.argv) File "/home/saleor/saleor-venv/lib/python3.9/site-packages/django/core/management/init.py", line 446, in execute_from_command_line utility.execute() File "/home/saleor/saleor-venv/lib/python3.9/site-packages/django/core/management/init.py", line 440, in execute self.fetch_command(subcommand).run_from_argv(self.argv) File "/home/saleor/saleor-venv/lib/python3.9/site-packages/django/core/management/base.py", line 414, in run_from_argv self.execute(*args, **cmd_options) File "/home/saleor/saleor-venv/lib/python3.9/site-packages/django/core/management/base.py", line 460, in execute output = self.handle(*args, **options) File "/home/saleor/saleor-venv/lib/python3.9/site-packages/django/core/management/base.py", line 98, in wrapped res = handle_func(*args, **kwargs) File "/home/saleor/saleor-venv/lib/python3.9/site-packages/django/core/management/commands/migrate.py", line 290, in handle post_migrate_state = executor.migrate( File "/home/saleor/saleor-venv/lib/python3.9/site-packages/django/db/migrations/executor.py", line 131, in migrate state = self._migrate_all_forwards( File "/home/saleor/saleor-venv/lib/python3.9/site-packages/django/db/migrations/executor.py", line 163, in _migrate_all_forwards state = self.apply_migration( File "/home/saleor/saleor-venv/lib/python3.9/site-packages/django/db/migrations/executor.py", line 248, in apply_migration state = migration.apply(state, schema_editor) File "/home/saleor/saleor-venv/lib/python3.9/site-packages/django/db/migrations/migration.py", line 131, in apply operation.database_forwards( File "/home/saleor/saleor-venv/lib/python3.9/site-packages/django/db/migrations/operations/special.py", line 106, in database_forwards self._run_sql(schema_editor, self.sql) File "/home/saleor/saleor-venv/lib/python3.9/site-packages/django/db/migrations/operations/special.py", line 133, in _run_sql schema_editor.execute(statement, params=None) File "/home/saleor/saleor-venv/lib/python3.9/site-packages/django/db/backends/base/schema.py", line 192, in execute cursor.execute(sql, params) File "/home/saleor/saleor-venv/lib/python3.9/site-packages/django/db/backends/utils.py", line 103, in execute return super().execute(sql, params) File "/home/saleor/saleor-venv/lib/python3.9/site-packages/django/db/backends/utils.py", line 67, in execute return self._execute_with_wrappers( File "/home/saleor/saleor-venv/lib/python3.9/site-packages/django/db/backends/utils.py", line 80, in _execute_with_wrappers return executor(sql, params, many, context) File "/home/saleor/saleor-venv/lib/python3.9/site-packages/django/db/backends/utils.py", line 89, in _execute return self.cursor.execute(sql, params) File "/home/saleor/saleor-venv/lib/python3.9/site-packages/django/db/utils.py", line 91, in exit raise dj_exc_value.with_traceback(traceback) from exc_value File "/home/saleor/saleor-venv/lib/python3.9/site-packages/django/db/backends/utils.py", line 87, in _execute return self.cursor.execute(sql) django.db.utils.ProgrammingError: function gen_random_uuid() does not exist HINT: No function matches the given name and argument types. You might need to add explicit type casts0068_user_uuid.txt

[peelar]

Hello @yetqdn 👋

I have to ask you to do two things:

1. Please follow the template for an issue.
2. Please format the code with "```" next time you post it.

[yetqdn]

I fixed it by deleting the error files in the migrations folder, but I'm not sure what errors or missing features will arise later when I use it

[roperi]

@yetqdn

I'm getting the same error.

Applying account.0068_user_uuid...Traceback (most recent call last):
  File "/home/admin/.virtualenvs/mysite/lib/python3.8/site-packages/django/db/backends/utils.py", line 82, in _execute
    return self.cursor.execute(sql)
psycopg2.errors.UndefinedFunction: function gen_random_uuid() does not exist
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

What to do?

EDIT:

Using Saleor 3.7, Python 3.8

[roperi]

It seems we need to load the pgcrypto extension in the current database/schema before applying the function.

Example:

Execute: CREATE EXTENSION pgcrypto;

Before applying the 'broken' migration file (saleor/account/migrations/0068_user_uuid.py) which contains this sql statement: ALTER TABLE account_user ALTER COLUMN uuid SET DEFAULT gen_random_uuid();

EDIT I solved the error by manually logging into the saleor database and creating the extension, then running the python manage.py migrate again.

[roperi]

@yetqdn @peelar

I hope you guys don't mind I submitted a bug report based on your comment, peelar.

[yetqdn]

@yetqdn @peelar

I hope you guys don't mind I submitted a bug report based on your comment, peelar. Hi @roperi @peelar, Thanks for your comment, In the last few days I try to do this way: "CREATE EXTENSION pgcrypto;" but it did not work. Then I deleted the error file 068 and all files behind it. I did the syntax migrate and it was successful. Then I copied the deleted files one by one and migrated successfully. However, I thought I made the mistake of not selecting the update branch. So I deleted and reinstall it. I did syntax " git checkout -b 3.7 remotes/origin/3.7" for version saleor3.7.3 and it installed successfully without any errors Best regrads!

[peelar]

Hello @yetqdn and @roperi

Thanks for your reports. We added it to our backlog and I will let you know as soon as it's fixed.

Have a nice day 🌻

[roperi]

@yetqdn ,

Did you first connect to the saleor database with \c saleor before executing CREATE EXTENSION pgcrypto; ? The extension has to be created within the Saleor database.

Thanks, @peelar !

[yetqdn]

@roperi I sign in Postgresql with user postgres and make CREATE EXTENSION pgcrypto; Thank you for the helpful advise!

[denny-sam]

I think this can be solved by executing an SQL statement for creating the extension via psycopg2. I can raise a PR for this.

[maarcingebala]

Closing, as the solution was posted in https://github.com/saleor/saleor/issues/10678/.