Error when using mariadb with SQL Database Agent

[Razikale365]

Observation: Error: (pymysql.err.ProgrammingError) (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'TO {example_schema}' at line 1") [SQL: SET search_path TO {example_schema}] (Background on this error at: https://sqlalche.me/e/14/f405) --> It did recover from the error and finished the chain, but i seems that it caused more api requests than necessary. In the error above. I ofuscated my schema name as '{example_schema}'. I ofuscated my schema name as '{example_schema}'. I am also using a uri that was made with sqlalchemy.engine.URL.create() in my script... The answers are impressive. I am trying to find the code where it triggers "SQL: SET search_path TO {example_schema}", to maybe try to contribute a little, but no luck so far... Would be great if suport for mariadb was improved. But anyways, you guys are a amazing for making this. Sincerously :D. I only hope this project continues to grow. It is awesome :D

[Razikale365]

I opened a PULL REQUEST #1527 to atempt to fix this issue and improve compatibility

[huineng]

I have the same problem but for db2 (using ibm_db_sa)

Thought: I should query the table to get the number of records
Action: query_sql_db
Action Input: "SELECT COUNT(*) FROM jira.jira_initiatives"
Observation: Error: (ibm_db_dbi.ProgrammingError) ibm_db_dbi::ProgrammingError: Statement Execute Failed: 
[IBM][CLI Driver][DB2/LINUXX8664] SQL0104N  An unexpected token "TO" was found following "SET search_path ".  Expected tokens may include:  "=".  SQLSTATE=42601 SQLCODE=-104
[SQL: SET search_path TO jira]
(Background on this error at: https://sqlalche.me/e/14/f405)
Thought: I should double check my query before executing it
Action: query_checker_sql_db
Action Input: "SELECT COUNT(*) FROM jira.jira_initiatives"

the query in itself was perfect as i provided the schema, unfortunately i cannot see the final query sent to the backend but it had that error (so action input is not what really is executed, can this be changed to see what really is requested ?

db = SQLDatabase.from_uri(
    db2_connection_string,
    schema='jira',
    include_tables=['jira_initiatives'], # including only one table for illustration
	sample_rows_in_table_info=3
)

and thanks, i'm looking forward to use this on our ecosystem

[huineng]

i think this line needs a structural solution for many types of db's i think you need to apply it only to dialects that support it

https://github.com/hwchase17/langchain/blob/ce5d97bcb3e263f6aa69da6c334e35e20bf4db11/langchain/sql_database.py#L174-L175

[huineng]

meanwhile i solved it by initialising the db with the schema and then delete the schema afterward

db = SQLDatabase.from_uri(
    db2_connection_string,
    schema='jira',
    include_tables=['jira_initiatives'], 
    sample_rows_in_table_info=3,
)

db._schema=None

[dosubot[bot]]

Hi, @Razikale365! I'm Dosu, and I'm here to help the LangChain team manage their backlog. I wanted to let you know that we are marking this issue as stale.

From what I understand, you encountered an error when using MariaDB with the SQL Database Agent. You were trying to identify the code that triggers the error and suggested improving support for MariaDB. You also opened a pull request (#1527) to fix the issue and enhance compatibility. Additionally, another user named huineng reported a similar problem with DB2 and proposed a solution by initializing the database with the schema and then deleting it afterward.

Before we proceed, we would like to confirm if this issue is still relevant to the latest version of the LangChain repository. If it is, please let us know by commenting on this issue. Otherwise, feel free to close the issue yourself, or it will be automatically closed in 7 days.

Thank you for your contribution, and we appreciate your understanding as we work to manage our backlog effectively. If you have any further questions or concerns, please don't hesitate to reach out.