# 8.15 MySQL connector how to sync using a sql statement

**URL:** <https://discuss.elastic.co/t/8-15-mysql-connector-how-to-sync-using-a-sql-statement/369312>\
**Category:** Elastic Agent\
**Created:** [October 24, 2024, 2:36am UTC](https://discuss.elastic.co/t/8-15-mysql-connector-how-to-sync-using-a-sql-statement/369312 "2024-10-24T02:36:13Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![Gertjan](https://avatars.discourse-cdn.com/v4/letter/g/76d3ee/32.png) [@Gertjan](https://discuss.elastic.co/u/Gertjan)\
**Post date:** [October 24, 2024, 2:36am UTC](https://discuss.elastic.co/t/8-15-mysql-connector-how-to-sync-using-a-sql-statement/369312/1 "2024-10-24T02:36:13Z")

</div>

Greetings,

I'm using the 8.15 free trial and want to retrieve data from MySQL.  
I'm using the MySQL connector and in the Configuration, I add some tables under Comma-separated list of tables. On sync, all the rows of the tables are correctly indexed.  
I want to index the result of a sql statement, one having multiple inner joins. You cannot add the statement under comma-separated list of tables. I tried to add it in the Sync rules, but it didn't pass the validation rules.  
I then created a view from that statement in MySQL and used the view name in the Configuration/list of tables. No errors on sync but no documents have been indexed.  
I then created a table from that statement in MySQL. I can see and use select in MySQL on that table name. But when I use that table name in the Configuration/List of tables, no errors on sync, but nothing has been indexed.

How can I index a sql statement using the MySQL connector?

Any help will be appreciated!  
Thanks!

---

<div class="post-metadata">

**Author:** ![ashishtiwari1993](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ashishtiwari1993/32/135241_2.png) [@ashishtiwari1993](https://discuss.elastic.co/u/ashishtiwari1993)\
**Post date:** [October 25, 2024, 12:50am UTC](https://discuss.elastic.co/t/8-15-mysql-connector-how-to-sync-using-a-sql-statement/369312/2 "2024-10-25T00:50:20Z")

</div>

Hi Gertjan, You can use [Advance sync rule](https://www.elastic.co/guide/en/enterprise-search/current/connectors-mysql.html#connectors-mysql-sync-rules) to specify the sql statement on given table.

```auto
[
    {
        "tables": ["table1", "table2"],
        "query": "SELECT ... FROM ..."
    }
]

```

---

<div class="post-metadata">

**Author:** ![Gertjan](https://avatars.discourse-cdn.com/v4/letter/g/76d3ee/32.png) [@Gertjan](https://discuss.elastic.co/u/Gertjan)\
**Post date:** [October 25, 2024, 3:05am UTC](https://discuss.elastic.co/t/8-15-mysql-connector-how-to-sync-using-a-sql-statement/369312/3 "2024-10-25T03:05:53Z")

</div>

Hi Ashish,

Thanks for your response.  
And I could get my sql statement to work on the condition that you define the same tables in Configuration.

I do have now another interesting problem.

Running my sql statement in MySQL results in 384 rows. However, Elastic only ingest 1 document (the last one).  
This is a part of the sql statement:

```auto
    , min(case when (project_property_definition_id = 135) then coalesce(value_int, value_float, value_double, value_string, value_datetime) else null end) as client_reference
    from project_properties   
group by project_id;

```

In Documents, it shows only 1 ingested document, the last one, with this reference:  
**Document id: project\_properties\_**

It looks that it overwrites the documents.  
How can I ensure that each mysql row will be 1 document?

Thanks

---

<div class="post-metadata">

**Author:** ![Gertjan](https://avatars.discourse-cdn.com/v4/letter/g/76d3ee/32.png) [@Gertjan](https://discuss.elastic.co/u/Gertjan)\
**Post date:** [October 25, 2024, 4:38pm UTC](https://discuss.elastic.co/t/8-15-mysql-connector-how-to-sync-using-a-sql-statement/369312/5 "2024-10-25T16:38:42Z")

</div>

Hi,  
Thanks for the tips, it helped to find the problem.  
My observations:

1. using a complicated sql statement in Advanced Sync Rules, will not work. (0 docus)
2. creating a view in MySQL and use the view in the configuration will not index any doc.
3. create a table, based on the complicated sql statement, then Elastic can index all the rows.

BUT only if the new table has a PK! Without the PK it will not index any doc!