json error while migrating database from odoo 17 to 18

[tintu018]

I was migrating database from odoo 14 to 18. Successfully migrated the database upto version 17 . now while migrating from 17 to 18 am getting below error. Unable to resolve this. Tried multiple solutions from chatgpt and other sources but nothing works.

psycopg2.errors.InvalidTextRepresentation: invalid input syntax for type json
DETAIL: Token "optional" is invalid.
CONTEXT: JSON data, line 1: optional

Traceback (most recent call last):
File "/home/tintu/Odoos/odoo-18.0/odoo/service/server.py", line 1361, in preload_registries
registry = Registry.new(dbname, update_module=update_module)
File "/home/tintu/.local/lib/python3.10/site-packages/decorator.py", line 232, in fun
return caller(func, *(extras + args), **kw)
File "/home/tintu/Odoos/odoo-18.0/odoo/tools/func.py", line 97, in locked
return func(inst, *args, **kwargs)
File "/home/tintu/Odoos/odoo-18.0/odoo/modules/registry.py", line 129, in new
odoo.modules.load_modules(registry, force_demo, status, update_module)
File "/home/tintu/Odoos/odoo-18.0/odoo/modules/loading.py", line 485, in load_modules
processed_modules += load_marked_modules(env, graph,
File "/home/tintu/Odoos/odoo-18.0/odoo/modules/loading.py", line 365, in load_marked_modules
loaded, processed = load_module_graph(
File "/home/tintu/Odoos/odoo-18.0/odoo/modules/loading.py", line 206, in load_module_graph
registry.init_models(env.cr, model_names, {'module': package.name}, new_install)
File "/home/tintu/Odoos/odoo-18.0/odoo/modules/registry.py", line 605, in init_models
model._auto_init()
File "/home/tintu/Odoos/odoo-18.0/addons/analytic/models/analytic_plan.py", line 95, in _auto_init
super()._auto_init()
File "/home/tintu/Odoos/odoo-18.0/odoo/models.py", line 3468, in _auto_init
new = field.update_db(self, columns)
File "/home/tintu/Odoos/odoo-18.0/odoo/fields.py", line 1089, in update_db
self.update_db_column(model, column)
File "/home/tintu/Odoos/odoo-18.0/odoo/fields.py", line 1125, in update_db_column
self._convert_db_column(model, column)
File "/home/tintu/Odoos/odoo-18.0/odoo/fields.py", line 1129, in _convert_db_column
sql.convert_column(model._cr, model._table, self.name, self.column_type[1])
File "/home/tintu/Odoos/odoo-18.0/odoo/tools/sql.py", line 347, in convert_column
_convert_column(cr, tablename, columnname, columntype, using)
File "/home/tintu/Odoos/odoo-18.0/odoo/tools/sql.py", line 371, in _convert_column
cr.execute(query, log_exceptions=False)
File "/home/tintu/Odoos/odoo-18.0/odoo/sql_db.py", line 357, in execute
res = self._obj.execute(query, params)
psycopg2.errors.InvalidTextRepresentation: invalid input syntax for type json
DETAIL: Token "optional" is invalid.
CONTEXT: JSON data, line 1: optional

2025-08-20 19:07:18,645 47463 INFO power188 odoo.service.server: Initiating shutdown

Please help

[pedrobaeza]

This sounds the same as #5041

[tintu018]

does the solution is updated in the script? i pulled the latest code from the 18.0, but am getting the same error

[pedrobaeza]

Well, maybe it's another column, not the exact one, but with the same problem. Check all the possible columns that are converted to see if there's one existing before migration.

[tintu018]

What exactly should i look for? I tried many solutions in chat gpt nothing resolve the issue. What chat gpt told is that the value in the json fields are incorrect and i run queries to fix the content of json field, the queries executed successfully but the error was still there.

[cvinh]

Hello

@hbrunn gave feed back here #5041 (comment)

select default_applicability , name , id from account_analytic_plan;

As I had no valuable data and I migrated to v17 before the PR #5048, I dropped the column
alter table account_analytic_plan drop column default_applicability;

It worked

[pedrobaeza]

That was fixed in #5048. Maybe you migrate to 17 before that patch?

[cvinh]

That was fixed in #5048. Maybe you migrate to 17 before that patch?

Yes indeed