mysql / mariadb no longer seems to work.

[venohm69]

It had been a while since I updated. So attempted to upgrade my manual install from 0.16.8 to 1.0.1. Once I got to the step to run "python manage.py migrate" things failed:
jango.db.utils.OperationalError: (4161, "Unknown data type: 'tsvector'")

Don't believe tsvector type is a data type know to mysql / mariadb. I believe that would fall under LONGTEXT in mysql/mariadb.
https://dev.mysql.com/doc/workbench/en/wb-migration-database-postgresql-typemapping.html

I'm guessing I'll have to migrate to postgress with pgloader unless someone knows of a solution besides migrating. I don't really know postgresql, but knew at least a bit about mysql/mariadb, which was the reason I ran it on my internal web server.

[vabene1111]

That is possible, i always tought django migrations would be DB agnostic but maybe something in the full text search update broke that. I recommend strongly to use postgresql as the application is not tested against mariadb.
If you have any questions during the migration feel free to ask and i will see what i can do.

[venohm69]

I think I've fixed everything but the error on edit and the search failure. Both of those have me stumped.

Doesn't seem to be fatal, but just not sure what to do about it. I get an error pop up when I select to edit a recipe. I can save the changes fine, but if I select edit again, I get the same error.

Failure
There was an error fetching a resource!

Nothing in the PostgreSQL logs but I see the following in the tandoor logs.

[2021-11-18 11:22:08 -0500] [62306] [DEBUG] GET /edit/recipe/76/
[2021-11-18 11:22:08 -0500] [62306] [DEBUG] GET /edit/recipe/internal/76/
[2021-11-18 11:22:08 -0500] [62306] [DEBUG] GET /api/recipe/76/
[2021-11-18 11:22:09 -0500] [62306] [DEBUG] GET /api/unit/
[2021-11-18 11:22:09 -0500] [62306] [DEBUG] GET /api/food/
[2021-11-18 11:22:09 -0500] [62306] [DEBUG] GET /api/keyword/
[2021-11-18 11:22:09 -0500] [62306] [DEBUG] GET /api/user-file/
[2021-11-18 11:22:09 -0500] [62306] [DEBUG] GET /api/recipe/
/opt/tandoor/.pyenv/versions/3.9.5/envs/tandorRecipes/lib/python3.9/site-packages/rest_framework/pagination.py:200: UnorderedObjectListWarning: Pagination may yield inconsistent results with an unordered object_list: <class 'cookbook.models.Recipe'> QuerySet.
paginator = self.django_paginator_class(queryset, page_size)

The other is that when I go to Books and select one of my books they don't open . Just get the spinning icons. I see nothing in any of the logs regarding this one. Not a big deal but wanted to mention it as well.

UPDATE:

Okay, found one more issue and this one is fatal. I cannot add additional recipes.
from tandoor gunicorn log:

[2021-11-18 12:08:45 -0500] [62306] [DEBUG] PUT /api/recipe/130/
WARNING:django.request:Bad Request: /api/recipe/130/

UPDATE 2:
Decided to try and run the manage.py again: Now I can add a recipe.

bin/python3.9 manage.py migrate
bin/python3.9 manage.py collectstatic
bin/python3.9 manage.py collectstatic_js_reverse

UPDATE 3:

Search fails with an error 500.

PostgreSQL log:

21-11-18 13:34:42.204 EST [72047] tandoor@tandoorrecipes ERROR: function similarity(character varying, unknown) does not exist at character 30
2021-11-18 13:34:42.204 EST [72047] tandoor@tandoorrecipes HINT: No function matches the given name and argument types. You might need to add explicit type casts.
2021-11-18 13:34:42.204 EST [72047] tandoor@tandoorrecipes STATEMENT: SELECT COUNT(*) FROM (SELECT SIMILARITY("cookbook_recipe"."name", 'crumpet') AS "similarity" FROM "cookbook_recipe" WHERE ("cookbook_recipe"."space_id" = 1 AND "cookbook_recipe"."space_id" = 1 AND (SIMILARITY("cookbook_recipe"."name", 'crumpet') > 0.1 OR UPPER(UNACCENT("cookbook_recipe"."name")::text) LIKE '%' || UPPER(REPLACE(REPLACE(REPLACE(UNACCENT('crumpet'), E'\', E'\\'), E'%', E'\%'), E'', E'\')) || '%'))) subquery
2021-11-18 13:34:42.233 EST [72047] tandoor@tandoorrecipes ERROR: function similarity(character varying, unknown) does not exist at character 674
2021-11-18 13:34:42.233 EST [72047] tandoor@tandoorrecipes HINT: No function matches the given name and argument types. You might need to add explicit type casts.
2021-11-18 13:34:42.233 EST [72047] tandoor@tandoorrecipes STATEMENT: SELECT "cookbook_recipe"."id", "cookbook_recipe"."name", "cookbook_recipe"."description", "cookbook_recipe"."servings", "cookbook_recipe"."servings_text", "cookbook_recipe"."image", "cookbook_recipe"."storage_id", "cookbook_recipe"."file_uid", "cookbook_recipe"."file_path", "cookbook_recipe"."link", "cookbook_recipe"."cors_link", "cookbook_recipe"."working_time", "cookbook_recipe"."waiting_time", "cookbook_recipe"."internal", "cookbook_recipe"."nutrition_id", "cookbook_recipe"."created_by_id", "cookbook_recipe"."created_at", "cookbook_recipe"."updated_at", "cookbook_recipe"."name_search_vector", "cookbook_recipe"."desc_search_vector", "cookbook_recipe"."space_id", SIMILARITY("cookbook_recipe"."name", 'crumpet') AS "similarity" FROM "cookbook_recipe" WHERE ("cookbook_recipe"."space_id" = 1 AND "cookbook_recipe"."space_id" = 1 AND (SIMILARITY("cookbook_recipe"."name", 'crumpet') > 0.1 OR UPPER(UNACCENT("cookbook_recipe"."name")::text) LIKE '%' || UPPER(REPLACE(REPLACE(REPLACE(UNACCENT('crumpet'), E'\', E'\\'), E'%', E'\%'), E'', E'\')) || '%')) ORDER BY "similarity" DESC LIMIT 21

tandoor gunicorn log:

[2021-11-18 13:35:20 -0500] [71453] [DEBUG] GET /search/
ERROR:django.request:Internal Server Error: /search/
Traceback (most recent call last):
File "/opt/tandoor/.pyenv/versions/3.9.5/envs/tandorRecipes/lib/python3.9/site-packages/django/db/backends/utils.py", line 84, in _execute
return self.cursor.execute(sql, params)
psycopg2.errors.UndefinedFunction: function similarity(character varying, unknown) does not exist
LINE 1: SELECT COUNT(*) FROM (SELECT SIMILARITY("cookbook_recipe"."n...
^
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 "/opt/tandoor/.pyenv/versions/3.9.5/envs/tandorRecipes/lib/python3.9/site-packages/django/core/handlers/exception.py", line 47, in inner
response = get_response(request)
File "/opt/tandoor/.pyenv/versions/3.9.5/envs/tandorRecipes/lib/python3.9/site-packages/django/core/handlers/base.py", line 181, in _get_response
response = wrapped_callback(request, callback_args, **callback_kwargs)
File "/opt/tandoor/tandoor/recipes-1.0.1/cookbook/views/views.py", line 70, in search
RequestConfig(request, paginate={'per_page': 25}).configure(table)
File "/opt/tandoor/.pyenv/versions/3.9.5/envs/tandorRecipes/lib/python3.9/site-packages/django_tables2/config.py", line 61, in configure
table.paginate(**kwargs)
File "/opt/tandoor/.pyenv/versions/3.9.5/envs/tandorRecipes/lib/python3.9/site-packages/django_tables2/tables.py", line 584, in paginate
self.page = self.paginator.page(page)
File "/opt/tandoor/.pyenv/versions/3.9.5/envs/tandorRecipes/lib/python3.9/site-packages/django/core/paginator.py", line 76, in page
number = self.validate_number(number)
File "/opt/tandoor/.pyenv/versions/3.9.5/envs/tandorRecipes/lib/python3.9/site-packages/django/core/paginator.py", line 54, in validate_number
if number > self.num_pages:
File "/opt/tandoor/.pyenv/versions/3.9.5/envs/tandorRecipes/lib/python3.9/site-packages/django/utils/functional.py", line 48, in get
res = instance.dict[self.name] = self.func(instance)
File "/opt/tandoor/.pyenv/versions/3.9.5/envs/tandorRecipes/lib/python3.9/site-packages/django/core/paginator.py", line 103, in num_pages
if self.count == 0 and not self.allow_empty_first_page:
File "/opt/tandoor/.pyenv/versions/3.9.5/envs/tandorRecipes/lib/python3.9/site-packages/django/utils/functional.py", line 48, in get
res = instance.dict[self.name] = self.func(instance)
File "/opt/tandoor/.pyenv/versions/3.9.5/envs/tandorRecipes/lib/python3.9/site-packages/django/core/paginator.py", line 98, in count
return len(self.object_list)
File "/opt/tandoor/.pyenv/versions/3.9.5/envs/tandorRecipes/lib/python3.9/site-packages/django_tables2/rows.py", line 325, in len
length = len(self.data)
File "/opt/tandoor/.pyenv/versions/3.9.5/envs/tandorRecipes/lib/python3.9/site-packages/django_tables2/data.py", line 153, in len
self._length = self.data.count()
File "/opt/tandoor/.pyenv/versions/3.9.5/envs/tandorRecipes/lib/python3.9/site-packages/django/db/models/query.py", line 412, in count
return self.query.get_count(using=self.db)
File "/opt/tandoor/.pyenv/versions/3.9.5/envs/tandorRecipes/lib/python3.9/site-packages/django/db/models/sql/query.py", line 519, in get_count
number = obj.get_aggregation(using, ['__count'])['__count']
File "/opt/tandoor/.pyenv/versions/3.9.5/envs/tandorRecipes/lib/python3.9/site-packages/django/db/models/sql/query.py", line 504, in get_aggregation
result = compiler.execute_sql(SINGLE)
File "/opt/tandoor/.pyenv/versions/3.9.5/envs/tandorRecipes/lib/python3.9/site-packages/django/db/models/sql/compiler.py", line 1175, in execute_sql
cursor.execute(sql, params)
File "/opt/tandoor/.pyenv/versions/3.9.5/envs/tandorRecipes/lib/python3.9/site-packages/django/db/backends/utils.py", line 66, in execute
return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
File "/opt/tandoor/.pyenv/versions/3.9.5/envs/tandorRecipes/lib/python3.9/site-packages/django/db/backends/utils.py", line 75, in _execute_with_wrappers
return executor(sql, params, many, context)
File "/opt/tandoor/.pyenv/versions/3.9.5/envs/tandorRecipes/lib/python3.9/site-packages/django/db/backends/utils.py", line 84, in _execute
return self.cursor.execute(sql, params)
File "/opt/tandoor/.pyenv/versions/3.9.5/envs/tandorRecipes/lib/python3.9/site-packages/django/db/utils.py", line 90, in exit
raise dj_exc_value.with_traceback(traceback) from exc_value
File "/opt/tandoor/.pyenv/versions/3.9.5/envs/tandorRecipes/lib/python3.9/site-packages/django/db/backends/utils.py", line 84, in _execute
return self.cursor.execute(sql, params)
django.db.utils.ProgrammingError: function similarity(character varying, unknown) does not exist
LINE 1: SELECT COUNT() FROM (SELECT SIMILARITY("cookbook_recipe"."n...
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts

[vabene1111]

1. the warning is always there, need to fix something for it to go
2. glad you got it working
3. you might need to activate and additonal postgres addon to support the trigram similartity features. This is the thing you need https://www.postgresql.org/docs/12/pgtrgm.html i hope this works (this gets activated by a migration but since you ran them on mysql it wasn't activated there as it did not have that feature)

[venohm69]

I'll have a look and see if I can figure out how to activate it. I don't really know PostgreSQL.

[venohm69]

Okay, at this point since I had access to my recipes I decided it would probably be quicker to export them, recreate the database just like a new install and import them. Now I have an app that shows I have 5 pages of recipes but doesn't show any of them. I can search and it returns a blank recipe. None of them display after the import.

[vabene1111]

sorry that you are having such a hard time, migrating from mysql to postgresql was never intended but i should probably put up a warning somewhere that i dont recommend/support mysql so people dont use it and regret it later.

Regarding your export: did you complelty setup a clean instance with the same version you pulled the export from? If you want you can send me the export file (via email to info@tandoor.dev) and the version you exported from and i will see if i can reproduce this and maybe even provide you a fix. Please also open an issue for this if you want to follow up here as this is a new problem/topic