You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@airflow.apache.org by GitBox <gi...@apache.org> on 2022/08/21 13:20:00 UTC
[GitHub] [airflow] hewerthomn opened a new issue, #25851: PL/SQL statement stop working after upgrade common-sql to 1.1.0
hewerthomn opened a new issue, #25851:
URL: https://github.com/apache/airflow/issues/25851
### Apache Airflow Provider(s)
common-sql, oracle
### Versions of Apache Airflow Providers
apache-airflow-providers-common-sql==1.1.0
apache-airflow-providers-oracle==3.3.0
### Apache Airflow version
2.3.3
### Operating System
Debian GNU/Linux 11 (bullseye)
### Deployment
Docker-Compose
### Deployment details
_No response_
### What happened
After upgrade provider common-sql==1.0.0 to 1.1.0 version, SQL with DECLARE stop working.
Using OracleProvider 3.2.0 with common-sql 1.0.0:
```
[2022-08-19, 13:16:46 -04] {oracle.py:66} INFO - Executing: DECLARE
v_sql LONG;
BEGIN
v_sql := '
create table usr_bi_cgj.dim_tarefa
(
id_tarefa NUMBER(22) not null primary key,
ds_tarefa VARCHAR2(4000) not NULL
);
';
EXECUTE IMMEDIATE v_sql;
COMMIT;
EXCEPTION
WHEN OTHERS
THEN EXECUTE IMMEDIATE 'TRUNCATE TABLE usr_bi_cgj.dim_tarefa';
COMMIT;
END;
[2022-08-19, 13:16:46 -04] {base.py:68} INFO - Using connection ID 'bitjro' for task execution.
[2022-08-19, 13:16:46 -04] {sql.py:255} INFO - Running statement: DECLARE
v_sql LONG;
BEGIN
v_sql := '
create table usr_bi_cgj.dim_tarefa
(
id_tarefa NUMBER(22) not null primary key,
ds_tarefa VARCHAR2(4000) not NULL
);
';
EXECUTE IMMEDIATE v_sql;
COMMIT;
EXCEPTION
WHEN OTHERS
THEN EXECUTE IMMEDIATE 'TRUNCATE TABLE usr_bi_cgj.dim_tarefa';
COMMIT;
END;, parameters: None
[2022-08-19, 13:16:46 -04] {sql.py:264} INFO - Rows affected: 0
[2022-08-19, 13:16:46 -04] {taskinstance.py:1420} INFO - Marking task as SUCCESS. dag_id=caixa_tarefa_pje, task_id=cria_temp_dim_tarefa, execution_date=20220819T080000, start_date=20220819T171646, end_date=20220819T171646
[2022-08-19, 13:16:46 -04] {local_task_job.py:156} INFO - Task exited with return code 0
```
After upgrade OracleProvider to 3.3.0 with common-sql to 1.1.0 version, same statement now throws an exception:
```
[2022-08-20, 14:58:14 ] {sql.py:315} INFO - Running statement: DECLARE
v_sql LONG;
BEGIN
v_sql := '
create table usr_bi_cgj.dim_tarefa
(
id_tarefa NUMBER(22) not null primary key,
ds_tarefa VARCHAR2(4000) not NULL
);
';
EXECUTE IMMEDIATE v_sql;
COMMIT;
EXCEPTION
WHEN OTHERS
THEN EXECUTE IMMEDIATE 'TRUNCATE TABLE usr_bi_cgj.dim_tarefa';
COMMIT;
END, parameters: None
[2022-08-20, 14:58:14 ] {taskinstance.py:1909} ERROR - Task failed with exception
Traceback (most recent call last):
File "/home/airflow/.local/lib/python3.7/site-packages/airflow/providers/oracle/operators/oracle.py", line 69, in execute
hook.run(self.sql, autocommit=self.autocommit, parameters=self.parameters)
File "/home/airflow/.local/lib/python3.7/site-packages/airflow/providers/common/sql/hooks/sql.py", line 295, in run
self._run_command(cur, sql_statement, parameters)
File "/home/airflow/.local/lib/python3.7/site-packages/airflow/providers/common/sql/hooks/sql.py", line 320, in _run_command
cur.execute(sql_statement)
File "/home/airflow/.local/lib/python3.7/site-packages/oracledb/cursor.py", line 378, in execute
impl.execute(self)
File "src/oracledb/impl/thin/cursor.pyx", line 121, in oracledb.thin_impl.ThinCursorImpl.execute
File "src/oracledb/impl/thin/protocol.pyx", line 375, in oracledb.thin_impl.Protocol._process_single_message
File "src/oracledb/impl/thin/protocol.pyx", line 376, in oracledb.thin_impl.Protocol._process_single_message
File "src/oracledb/impl/thin/protocol.pyx", line 369, in oracledb.thin_impl.Protocol._process_message
oracledb.exceptions.DatabaseError: ORA-06550: linha 17, coluna 3:
PLS-00103: Encontrado o símbolo "end-of-file" quando um dos seguintes símbolos era esperado:
; <um identificador>
<um identificador delimitado por aspas duplas>
O símbolo ";" foi substituído por "end-of-file" para continuar.
```
### What you think should happen instead
I think stripping `;` from statement is causing this error
### How to reproduce
_No response_
### Anything else
_No response_
### Are you willing to submit PR?
- [ ] Yes I am willing to submit a PR!
### Code of Conduct
- [X] I agree to follow this project's [Code of Conduct](https://github.com/apache/airflow/blob/main/CODE_OF_CONDUCT.md)
--
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: commits-unsubscribe@airflow.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] potiuk closed issue #25851: PL/SQL statement stop working after upgrade common-sql to 1.1.0
Posted by GitBox <gi...@apache.org>.
potiuk closed issue #25851: PL/SQL statement stop working after upgrade common-sql to 1.1.0
URL: https://github.com/apache/airflow/issues/25851
--
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: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] potiuk commented on issue #25851: PL/SQL statement stop working after upgrade common-sql to 1.1.0
Posted by GitBox <gi...@apache.org>.
potiuk commented on issue #25851:
URL: https://github.com/apache/airflow/issues/25851#issuecomment-1221561383
cc: @kazanzhy - > will you take a look at that one or should I attempt to fix it ? I think having optional "strip_semicolon" and setting it to False by default in Oracle, should solve the issue.
--
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: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] hewerthomn commented on issue #25851: PL/SQL statement stop working after upgrade common-sql to 1.1.0
Posted by GitBox <gi...@apache.org>.
hewerthomn commented on issue #25851:
URL: https://github.com/apache/airflow/issues/25851#issuecomment-1224718643
Thanks!!! @kazanzhy @potiuk
--
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: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] hewerthomn commented on issue #25851: PL/SQL statement stop working after upgrade common-sql to 1.1.0
Posted by GitBox <gi...@apache.org>.
hewerthomn commented on issue #25851:
URL: https://github.com/apache/airflow/issues/25851#issuecomment-1221605019
Yes @kazanzhy , every statement has a semicolon at the end, and after END too
--
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: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] potiuk commented on issue #25851: PL/SQL statement stop working after upgrade common-sql to 1.1.0
Posted by GitBox <gi...@apache.org>.
potiuk commented on issue #25851:
URL: https://github.com/apache/airflow/issues/25851#issuecomment-1224132243
Fantastic! thanks for confirmation! It's already merged and will go out in the next wave of providers :). Thanks @kazanzhy also for very quickl fixing ot that one :)
--
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: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] potiuk commented on issue #25851: PL/SQL statement stop working after upgrade common-sql to 1.1.0
Posted by GitBox <gi...@apache.org>.
potiuk commented on issue #25851:
URL: https://github.com/apache/airflow/issues/25851#issuecomment-1222439112
> how can I test this update?
@hewerthomn
You can BYOP (build your own provider):
1) checkout main
2) Install breeze `pipx install -e ./dev/breeze`
3) `breeze release-management prepare-provider-packages --version-suffix-for-pypi dev0`
The .whl package should be prepared in your `./dist` folder and you should be able to install it with `pip install ./dist/WHEEL_FILE_NAME.whl`
--
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: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] hewerthomn commented on issue #25851: PL/SQL statement stop working after upgrade common-sql to 1.1.0
Posted by GitBox <gi...@apache.org>.
hewerthomn commented on issue #25851:
URL: https://github.com/apache/airflow/issues/25851#issuecomment-1224128510
@potiuk I builded the package and installed in my container with airflow, it worked!
```
[2022-08-23, 09:59:17 -04] {oracle.py:66} INFO - Executing: DECLARE
v_sql LONG;
BEGIN
v_sql := '
create table usr_bi_cgj.dim_tarefa
(
id_tarefa NUMBER(22) not null primary key,
ds_tarefa VARCHAR2(4000) not NULL
);
';
EXECUTE IMMEDIATE v_sql;
COMMIT;
EXCEPTION
WHEN OTHERS
THEN EXECUTE IMMEDIATE 'TRUNCATE TABLE usr_bi_cgj.dim_tarefa';
COMMIT;
END;
[2022-08-23, 09:59:17 -04] {base.py:68} INFO - Using connection ID 'bitjro' for task execution.
[2022-08-23, 09:59:18 -04] {sql.py:313} INFO - Running statement: DECLARE
v_sql LONG;
BEGIN
v_sql := '
create table usr_bi_cgj.dim_tarefa
(
id_tarefa NUMBER(22) not null primary key,
ds_tarefa VARCHAR2(4000) not NULL
);
';
EXECUTE IMMEDIATE v_sql;
COMMIT;
EXCEPTION
WHEN OTHERS
THEN EXECUTE IMMEDIATE 'TRUNCATE TABLE usr_bi_cgj.dim_tarefa';
COMMIT;
END;, parameters: None
[2022-08-23, 09:59:18 -04] {sql.py:322} INFO - Rows affected: 0
[2022-08-23, 09:59:18 -04] {taskinstance.py:1420} INFO - Marking task as SUCCESS. dag_id=caixa_tarefa_pje, task_id=cria_temp_dim_tarefa, execution_date=20220822T090000, start_date=20220823T135917, end_date=20220823T135918
[2022-08-23, 09:59:18 -04] {local_task_job.py:156} INFO - Task exited with return code 0
```
:smile:
--
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: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] kazanzhy commented on issue #25851: PL/SQL statement stop working after upgrade common-sql to 1.1.0
Posted by GitBox <gi...@apache.org>.
kazanzhy commented on issue #25851:
URL: https://github.com/apache/airflow/issues/25851#issuecomment-1221568844
I also thought about the `;` stripping as the cause of this error.
@hewerthomn does your original query has the trailing `;`?
--
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: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] kazanzhy commented on issue #25851: PL/SQL statement stop working after upgrade common-sql to 1.1.0
Posted by GitBox <gi...@apache.org>.
kazanzhy commented on issue #25851:
URL: https://github.com/apache/airflow/issues/25851#issuecomment-1221629622
It will be great if you have a look at https://github.com/apache/airflow/pull/25855 and help me to test it.
I worked with a lot of databases, but unfortunately, Oracle isn't one of them.
--
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: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] hewerthomn commented on issue #25851: PL/SQL statement stop working after upgrade common-sql to 1.1.0
Posted by GitBox <gi...@apache.org>.
hewerthomn commented on issue #25851:
URL: https://github.com/apache/airflow/issues/25851#issuecomment-1222260421
@kazanzhy I want to test it, but the last version of common-sql is 1.1.0
how can I test this update?
--
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: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] potiuk commented on issue #25851: PL/SQL statement stop working after upgrade common-sql to 1.1.0
Posted by GitBox <gi...@apache.org>.
potiuk commented on issue #25851:
URL: https://github.com/apache/airflow/issues/25851#issuecomment-1222442817
(you need to have `pipx` installed first BTW)
--
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: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org