apache / apache/shardingsphere

An error occurred on the migration command in dajngo throungt Sharding-Proxy to connect postgresql

Open
#28,938 0 comments 0 reactions 0 assignees View on GitHub
db: PostgreSQL in: proxy
Dominant language
Java
Stars
20.8k
Forks
6.9k
Avg merge
11h 35m
Merged PRs (30d)
326

Description

When django connects directly to PG, execute python manage.py migrate and everything works fine. But when django adds shardingsphere-proxy, executing command return an error

```
environment:

- Django 3.2.12
- psycopg2-binary 2.8.6
- psycopg2-binary 2.8.6
- apache/shardingsphere-proxy 5.4.1
- postgres 11.14
```
```
exception detail:

Traceback (most recent call last):
File "/data/code/dtmg/.env/lib/python3.9/site-packages/django/db/backends/base/base.py", line 219, in ensure_connection
self.connect()
File "/data/code/dtmg/.env/lib/python3.9/site-packages/django/utils/asyncio.py", line 33, in inner
return func(*args, **kwargs)
File "/data/code/dtmg/.env/lib/python3.9/site-packages/django/db/backends/base/base.py", line 202, in connect
self.init_connection_state()
File "/data/code/dtmg/.env/lib/python3.9/site-packages/django/db/backends/postgresql/base.py", line 224, in init_connection_state
timezone_changed = self.ensure_timezone()
File "/data/code/dtmg/.env/lib/python3.9/site-packages/django/db/backends/postgresql/base.py", line 217, in ensure_timezone
cursor.execute(self.ops.set_time_zone_sql(), [timezone_name])
psycopg2.errors.SystemError: Unknown exception: null
```
PG timezone is Asia/Shanghai
```
sharding_db=> show time zone;
timezone
----------
Etc/UTC
(1 row)
```
and in ensure_timezone function:
```
def ensure_timezone(self):
if self.connection is None:
return False
# conn_timezone_name = None
conn_timezone_name = self.connection.get_parameter_status('TimeZone')
timezone_name = self.timezone_name
if timezone_name and conn_timezone_name != timezone_name:
with self.connection.cursor() as cursor:
cursor.execute(self.ops.set_time_zone_sql(), [timezone_name])
return True
return False
```
conn_timezone_name = None

sharding-proxy log:
```
dev_shardingsphere-proxy | [ERROR] 2023-11-04 16:37:22.657 [ShardingSphere-Command-28] o.a.s.p.f.c.CommandExecutorTask - Exception occur:
dev_shardingsphere-proxy | java.util.NoSuchElementException: null
dev_shardingsphere-proxy | at java.base/java.util.LinkedList$ListItr.next(LinkedList.java:894)
dev_shardingsphere-proxy | at org.apache.shardingsphere.proxy.backend.postgresql.handler.admin.executor.PostgreSQLSetVariableAdminExecutor.execute(PostgreSQLSetVariableAdminExecutor.java:42)
dev_shardingsphere-proxy | at org.apache.shardingsphere.proxy.backend.handler.admin.DatabaseAdminUpdateBackendHandler.execute(DatabaseAdminUpdateBackendHandler.java:44)
dev_shardingsphere-proxy | at org.apache.shardingsphere.proxy.frontend.postgresql.command.query.simple.PostgreSQLComQueryExecutor.execute(PostgreSQLComQueryExecutor.java:77)
dev_shardingsphere-proxy | at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.doExecuteCommand(CommandExecutorTask.java:126)
dev_shardingsphere-proxy | at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.executeCommand(CommandExecutorTask.java:121)
dev_shardingsphere-proxy | at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.run(CommandExecutorTask.java:78)
dev_shardingsphere-proxy | at com.alibaba.ttl.TtlRunnable.run(TtlRunnable.java:60)
dev_shardingsphere-proxy | at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136)
dev_shardingsphere-proxy | at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635)
dev_shardingsphere-proxy | at java.base/java.lang.Thread.run(Thread.java:833)
dev_shardingsphere-proxy | [ERROR] 2023-11-04 17:06:29.105 [ShardingSphere-Command-32] o.a.s.p.f.c.CommandExecutorTask - Exception occur:
dev_shardingsphere-proxy | java.util.NoSuchElementException: null
dev_shardingsphere-proxy | at java.base/java.util.LinkedList$ListItr.next(LinkedList.java:894)
dev_shardingsphere-proxy | at org.apache.shardingsphere.proxy.backend.postgresql.handler.admin.executor.PostgreSQLSetVariableAdminExecutor.execute(PostgreSQLSetVariableAdminExecutor.java:42)
dev_shardingsphere-proxy | at org.apache.shardingsphere.proxy.backend.handler.admin.DatabaseAdminUpdateBackendHandler.execute(DatabaseAdminUpdateBackendHandler.java:44)
dev_shardingsphere-proxy | at org.apache.shardingsphere.proxy.frontend.postgresql.command.query.simple.PostgreSQLComQueryExecutor.execute(PostgreSQLComQueryExecutor.java:77)
dev_shardingsphere-proxy | at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.doExecuteCommand(CommandExecutorTask.java:126)
dev_shardingsphere-proxy | at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.executeCommand(CommandExecutorTask.java:121)
dev_shardingsphere-proxy | at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.run(CommandExecutorTask.java:78)
dev_shardingsphere-proxy | at com.alibaba.ttl.TtlRunnable.run(TtlRunnable.java:60)
dev_shardingsphere-proxy | at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136)
dev_shardingsphere-proxy | at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635)
dev_shardingsphere-proxy | at java.base/java.lang.Thread.run(Thread.java:833)

```

I can't find a solution. If anyone can help, thank you very much

Contributor guide

Open the contributing guide

Research direction

Reproduce `python manage.py migrate` with Django 3.2.12 through ShardingSphere-Proxy 5.4.1 against PostgreSQL 11.14, then inspect PostgreSQLSetVariableAdminExecutor.java at the failing stack-trace line. Compare the proxy's handling of the timezone SET command with the successful direct PostgreSQL connection; done means the migration no longer raises the reported exception.

Written by the indexing model from the issue text.

Assessment

Tech stack
django, java, postgresql, python
Domain
databases
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
38/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.