berglh commented on issue #57234:
URL: https://github.com/apache/airflow/issues/57234#issuecomment-3471067794
To continue this troubleshooting, I replicated our RDS to a test DB
instance. I then created interactive Airflow pods using `kubectl` to interact
with the test RDS instance.
### Start Airflow 2.11.0 container
1. `airflow db check`
2. `airflow db shell` (\conninfo to check connection details are using the
correct test DB)
3. `airflow db clean --clean-before-timestamp '2025-09-01 00:00:00.000'
--skip-archive -y --dry-run`
4. `airflow db clean --clean-before-timestamp '2025-09-01 00:00:00.000'
--skip-archive -y`
5. `airflow db migrate -s ` to see if any migrations are pending, which
there were not (happy cat output)'
### Start Airflow 3.1.1 container
1. `airflow db check`
2. `airflow db shell` (\conninfo to check connection details are using the
correct test DB)
3. `airflow db migrate -s` to capture the SQL upgrade commands
4. `airflow db migrate`
This resulted in the same issue as I experienced during the Helm upgrade.
This removed all config from Airflow except for the DB connection string. So,
the query before the failure is a query to update the `xcom` table where it
converts values to be compatible with the `JSONB` data format `Remove pickled
data from xcom table`.
```sql
UPDATE xcom
SET value = convert_to(replace(convert_from(value, 'UTF8'),
'NaN', '"nan"'), 'UTF8')
WHERE value IS NOT NULL AND get_byte(value, 0) != 128;
```
Then directly after this, I believe is the query to cast the column to
`JSONB`:
```sql
ALTER TABLE xcom
ALTER COLUMN value TYPE JSONB
USING CASE
WHEN value IS NOT NULL THEN CAST(CONVERT_FROM(value, 'UTF8')
AS JSONB)
ELSE NULL
END;
```
With the error:
```
Traceback (most recent call last):
File
"/home/airflow/.local/lib/python3.12/site-packages/sqlalchemy/engine/base.py",
line 1910, in _execute_context
self.dialect.do_execute(
File
"/home/airflow/.local/lib/python3.12/site-packages/sqlalchemy/engine/default.py",
line 736, in do_execute
cursor.execute(statement, parameters)
psycopg2.errors.InvalidTextRepresentation: invalid input syntax for type json
DETAIL: Token "nan" is invalid.
CONTEXT: JSON data, line 1:
...2FSDH3SbQ8Q2TmlrUFDaCptskUXZOdM6bwpZShBKsJfp1"nan...
```
I will try to find the entry in the `xcom` table that it's tripping up on.
Here are the full SQL queries and db migrate logs:
[Airflow DB Migrate SQL upgrade
commands](https://github.com/user-attachments/files/23248337/github-airflow-db-migrate-sql.sql)
[Airflow DB Migrate Full
Log](https://github.com/user-attachments/files/23248344/github-airflow-db-upgrade-3-1.log)
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
