apache / apache/shardingsphere
An error occurred on the migration command in dajngo throungt Sharding-Proxy to connect postgresql
- 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
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