DataStore on Android throws SQLiteException no such column on "<TableName>2"

[RobsonT]

Description

I have all the backend done in aws amplify and it works normally in the web system (react) and it worked in the flutter app. But since I added @belongsto relationships to some fields with 1:n relationships the system stopped working in flutter (still working in react). Some tables are returning data normally, but others generate the error: message=Error in querying the model., cause=DataStoreException{message=Invalid SQL statement...}

Categories

• Analytics
• API (REST)
• API (GraphQL)
• Auth
• Authenticator
• DataStore
• Storage

Steps to Reproduce

No response

Screenshots

Platforms

• iOS
• Android
• Web
• macOS
• Windows
• Linux

Flutter Version

3.3.10

Amplify Flutter Version

0.6.10

Deployment Method

Amplify CLI

[fjnoyp]

Hi @RobsonT thanks for sharing this issue with us.

Can you please confirm if you did the following:

1. regenerate your models by running amplify codegen models
2. did you push changes with amplify cli command amplify push
3. did you clear your local data by calling Amplify.Datastore.clear() within your app client code?

[RobsonT]

Hello @fjnoyp,
Yes, I did. I've also run the flutter clean commands and tried uninstalling and reinstalling the app. In addition, I tested it on other devices, with the error occurring both on android and ios.

[HuiSF]

Hi @RobsonT at a glance of the exception message, it's foreign key constraint error. This would happen when you created some "child" records that belong to a "parent", but the "parent" record doesn't exist in the DB.

Take this schema:

type Insumo @model @auth(rules: [{allow: public}]) {
  id: ID!
  nome: String
  grupoinsumoID: ID! @index(name: "byGrupoInsumo")
  PropriedadeInsumos: [PropriedadeInsumo] @hasMany(indexName: "byInsumo", fields: ["id"])
  grupoInsumo: GrupoInsumo @belongsTo(fields: ["grupoinsumoID"])
}

When you create a record of Insumo, grupoinsumoID becomes the foreign key in local SQLite DB, and when you save the record, the foreign key has to exist in GrupoInsumo table in order to create a complete relationship without "orphaned" child. You can ensure this by creating associated GrupoInsumo before saving a new Insumo.

[RobsonT]

Hi @HuiSF ,
Thanks for the answer. I'll see if I can find any items that have this problem.

[RobsonT]

Hi @HuiSF,

I tried deleting all the data from the tables and starting from scratch to make sure I didn't have any inconsistent data. But when I tried to register the data I came across the following error:
Cannot find associated fields to build UsuariosCannot find associated fields to build Usuarios

Do you know what could be causing this problem?

[HuiSF]

Hi @RobsonT apologies for the delayed response, and thank you for providing more info.

Re: the screenshot, were you trying to add model records inside Amplify Studio? Could you describe in details of the steps you were doing?

[RobsonT]

Hi @HuiSF

I was following the previous tip about the problem being the foreign key pointing to non-existent data. For this I deleted all data from all tables. Then I started inserting data, doing it directly through amplify studio. My idea was to start inserting table by table and test in the flutter app if any errors were generated. However, when registering in the Company table, which was the first table in which I went to register, the image error appeared, not showing the registration form. This error started after I ran amplify upgrade, so I don't know if it was a bug in the latest version. I saw that there was an issue with the same problem at aws-amplify/amplify-studio#825 .

However, this error is different from my initial error, but I need it resolved so that I can solve my first problem. In addition to the error in amplify studio, when I ran amplify codegen models, with the latest version of amplify I noticed that the model columns that served as foreign keys were no longer being generated in the schema.js file, causing some errors in my web system as well.

[HuiSF]

OK thanks for the clarification @RobsonT

It looks like an Amplify Studio issue, and there was a fix implemented already. You probably need to wait for the fix to be released on Amplify Studio side.

[RobsonT]

ok. Is there an estimate of when it will be released @HuiSF ?

[HuiSF]

I don't have visibility into the release schedule of Amplify Studio at this moment, @RobsonT , I can try to find out. But this shouldn't be a blocker for your issue.

You can go ahead to continue testing in your App with amplify-flutter library using Amplify.DataStore.save API.

e.g. an Employee model @belongsTo a Company model. You can try

final company = Company(...);
final employee = Employee(..., compnay: compnay); // associate the employee to the parent company

await Amplify.DataStore.save(company);   // ensure the parent is written into local DB
await Amplify.DataStore.save(employee);

The newly created records should be seen in your DynamoDB table.
You can delete the App and reinstall let the DataStore to sync with cloud.
When you query the Employee you should see the previously saved records.

Please give it a try and let me know if anything.

[RobsonT]

@HuiSF, since yesterday, amplify studio has the problem solved and I managed to insert the data I needed. I inserted it in some tables, and for each table I inserted a single row. However, when inserting in the Manejo table and making a query (await Amplify.DataStore.query(Manejo.classType);) the error "Error in querying the model." happened again. None of the inserted data had been deleted, so I can say with certainty that no foreign key is referencing non-existent data.

[HuiSF]

Hi @RobsonT Thanks for the follow up.

It sounds like the DataStore couldn't sync data from cloud.

I just noticed that you have 42 models defined. AppSync allows 100 subscriptions per App instance, each model creates 3 subscriptions onCreate, onUpdate and onDelete, considering the AppSync subscription limit, you can create about 33 models in your App. Subscriptions will fail the number of models is beyond the limit, which fails DataStore sync engine, so the DataStore may not be able to sync all models/records into local DB, which may cause the exception while querying with a incomplete data set.

You may need to consider to optimize the model schema and reduce the model number to ensure the sync work correctly. Here's a thread discussing about this limit.

[RobsonT]

Hi @HuiSF

my subscription limit per connection has already been increased to 200 in December 2022. Shouldn't that be enough to solve the problem?

[HuiSF]

Good to know @RobsonT that shouldn't be the case then.

I inserted it in some tables, and for each table I inserted a single row. However, when inserting in the Manejo table and making a query (await Amplify.DataStore.query(Manejo.classType);)

• Were you inserting the data on Amplify Studio?

Looking at the schema of Manejo, it has 4 @belongsTo relationships, did you assign values to all these 4 fields: safraID, propriedadeservicoID, modoaplicacaoID, equipamentoaplicacaoID? And did you insert records associated with these 4 fields in Safra, PropriedadeServico, ModoAplicacao, EquipamentoAplicacao respectively?

If so, could you also try to query these 4 models respectively see if you can get corresponding records from the local DB.

[RobsonT]

@HuiSF, I did not fill in modoaplicacaoID and equipamentoaplicacaoID as they are optional fields in our system. As for the other 2 fields, the Safra one also generated an error, but the PropriedadeServico returned the data without problems.
Error in Safra:

DataStoreException (DataStoreException(message: Error in querying the model., recoverySuggestion: See attached exception for details., underlyingException: DataStoreException{message=Invalid SQL statement: SELECT PropriedadeCultivo.id AS PropriedadeCultivo_id, PropriedadeCultivo.codigoDeBarra AS PropriedadeCultivo_codigoDeBarra, PropriedadeCultivo.createdAt AS PropriedadeCultivo_createdAt, PropriedadeCultivo.responsavel_atualizacao_id AS PropriedadeCultivo_responsavel_atualizacao_id, PropriedadeCultivo.responsavel_cadastro_id AS PropriedadeCultivo_responsavel_cadastro_id, PropriedadeCultivo.updatedAt AS PropriedadeCultivo_updatedAt, PropriedadeCultivo.cultivoID AS PropriedadeCultivo_cultivoID, PropriedadeCultivo.propriedadeID AS PropriedadeCultivo_propriedadeID, Propriedade.id AS Propriedade_id, Propriedade.cpf_cnpj AS Propriedade_cpf_cnpj, Propriedade.createdAt AS Propriedade_createdAt, Propriedade.nome AS Propriedade_nome, Propriedade.propriedadeEnderecoPropriedadeId AS Propriedade_propriedadeEnderecoPropriedadeId, Propriedade.responsavel_atualizacao_id AS Propriedade_responsavel_atualizacao_id, Propriedade.responsavel_cadastro_id AS Propriedade_responsavel_cadastro_id, Propriedade.updatedAt AS Propriedade_updatedAt, Propriedade.empresaID AS Propriedade_empresaID, Empresa.id AS Empresa_id, Empresa.cpf_cnpj AS Empresa_cpf_cnpj, Empresa.createdAt AS Empresa_createdAt, Empresa.email AS Empresa_email, Empresa.empresaLicensaEmpresaId AS Empresa_empresaLicensaEmpresaId, Empresa.nome AS Empresa_nome, Empresa.nome_fantasia AS Empresa_nome_fantasia, Empresa.telefone AS Empresa_telefone, Empresa.updatedAt AS Empresa_updatedAt, Empresa2.id AS Empresa_id2, Empresa2.cpf_cnpj AS Empresa_cpf_cnpj2, Empresa2.createdAt AS Empresa_createdAt2, Empresa2.email AS Empresa_email2, Empresa2.empresaLicensaEmpresa2Id AS Empresa_empresaLicensaEmpresaId2, Empresa2.nome AS Empresa_nome2, Empresa2.nome_fantasia AS Empresa_nome_fantasia2, Empresa2.telefone AS Empresa_telefone2, Empresa2.updatedAt AS Empresa_updatedAt2, Empresa4.id AS Empresa_id4, Empresa4.cpf_cnpj AS Empresa_cpf_cnpj4, Empresa4.createdAt AS Empresa_createdAt4, Empresa4.email AS Empresa_email4, Empresa4.empresaLicensaEmpresa4Id AS Empresa_empresaLicensaEmpresaId4, Empresa4.nome AS Empresa_nome4, Empresa4.nome_fantasia AS Empresa_nome_fantasia4, Empresa4.telefone AS Empresa_telefone4, Empresa4.updatedAt AS Empresa_updatedAt4, Empresa3.id AS Empresa_id3, Empresa3.cpf_cnpj AS Empresa_cpf_cnpj3, Empresa3.createdAt AS Empresa_createdAt3, Empresa3.email AS Empresa_email3, Empresa3.empresaLicensaEmpresa3Id AS Empresa_empresaLicensaEmpresaId3, Empresa3.nome AS Empresa_nome3, Empresa3.nome_fantasia AS Empresa_nome_fantasia3, Empresa3.telefone AS Empresa_telefone3, Empresa3.updatedAt AS Empresa_updatedAt3, Propriedade2.id AS Propriedade_id2, Propriedade2.cpf_cnpj AS Propriedade_cpf_cnpj2, Propriedade2.createdAt AS Propriedade_createdAt2, Propriedade2.nome AS Propriedade_nome2, Propriedade2.propriedadeEnderecoPropriedade2Id AS Propriedade_propriedadeEnderecoPropriedadeId2, Propriedade2.responsavel_atualizacao_id AS Propriedade_responsavel_atualizacao_id2, Propriedade2.responsavel_cadastro_id AS Propriedade_responsavel_cadastro_id2, Propriedade2.updatedAt AS Propriedade_updatedAt2, Propriedade2.empresaID AS Propriedade_empresaID2, Propriedade3.id AS Propriedade_id3, Propriedade3.cpf_cnpj AS Propriedade_cpf_cnpj3, Propriedade3.createdAt AS Propriedade_createdAt3, Propriedade3.nome AS Propriedade_nome3, Propriedade3.propriedadeEnderecoPropriedade3Id AS Propriedade_propriedadeEnderecoPropriedadeId3, Propriedade3.responsavel_atualizacao_id AS Propriedade_responsavel_atualizacao_id3, Propriedade3.responsavel_cadastro_id AS Propriedade_responsavel_cadastro_id3, Propriedade3.updatedAt AS Propriedade_updatedAt3, Propriedade3.empresaID AS Propriedade_empresaID3, Propriedade4.id AS Propriedade_id4, Propriedade4.cpf_cnpj AS Propriedade_cpf_cnpj4, Propriedade4.createdAt AS Propriedade_createdAt4, Propriedade4.nome AS Propriedade_nome4, Propriedade4.propriedadeEnderecoPropriedade4Id AS Propriedade_propriedadeEnderecoPropriedadeId4, Propriedade4.responsavel_atualizacao_id AS Propriedade_responsavel_atualizacao_id4, Propriedade4.responsavel_cadastro_id AS Propriedade_responsavel_cadastro_id4, Propriedade4.updatedAt AS Propriedade_updatedAt4, Propriedade4.empresaID AS Propriedade_empresaID4, Talhao2.id AS Talhao_id2, Talhao2.createdAt AS Talhao_createdAt2, Talhao2.nome AS Talhao_nome2, Talhao2.responsavel_atualizacao_id AS Talhao_responsavel_atualizacao_id2, Talhao2.responsavel_cadastro_id AS Talhao_responsavel_cadastro_id2, Talhao2.updatedAt AS Talhao_updatedAt2, Talhao2.propriedadeID AS Talhao_propriedadeID2, CategoriaSubdivisao.id AS CategoriaSubdivisao_id, CategoriaSubdivisao.createdAt AS CategoriaSubdivisao_createdAt, CategoriaSubdivisao.nome AS CategoriaSubdivisao_nome, CategoriaSubdivisao.updatedAt AS CategoriaSubdivisao_updatedAt, Talhao.id AS Talhao_id, Talhao.createdAt AS Talhao_createdAt, Talhao.nome AS Talhao_nome, Talhao.responsavel_atualizacao_id AS Talhao_responsavel_atualizacao_id, Talhao.responsavel_cadastro_id AS Talhao_responsavel_cadastro_id, Talhao.updatedAt AS Talhao_updatedAt, Talhao.propriedadeID AS Talhao_propriedadeID, Safra.id AS Safra_id, Safra.createdAt AS Safra_createdAt, Safra.dataFinalizacao AS Safra_dataFinalizacao, Safra.data_plantio AS Safra_data_plantio, Safra.finalizada AS Safra_finalizada, Safra.responsavel_atualizacao_id AS Safra_responsavel_atualizacao_id, Safra.responsavel_cadastro_id AS Safra_responsavel_cadastro_id, Safra.updatedAt AS Safra_updatedAt, Safra.propriedadecultivoID AS Safra_propriedadecultivoID, Safra.subdivisaoID AS Safra_subdivisaoID, Safra.talhaoID AS Safra_talhaoID, Cultivo.id AS Cultivo_id, Cultivo.createdAt AS Cultivo_createdAt, Cultivo.cultura AS Cultivo_cultura, Cultivo.updatedAt AS Cultivo_updatedAt, Cultivo.variedade AS Cultivo_variedade, Subdivisao.id AS Subdivisao_id, Subdivisao.complemento AS Subdivisao_complemento, Subdivisao.createdAt AS Subdivisao_createdAt, Subdivisao.responsavel_atualizacao_id AS Subdivisao_responsavel_atualizacao_id, Subdivisao.responsavel_cadastro_id AS Subdivisao_responsavel_cadastro_id, Subdivisao.subdivisao_pai AS Subdivisao_subdivisao_pai, Subdivisao.updatedAt AS Subdivisao_updatedAt, Subdivisao.cateroriasubdivisaoID AS Subdivisao_cateroriasubdivisaoID, Subdivisao.propriedadeID AS Subdivisao_propriedadeID, Subdivisao.talhaoID AS Subdivisao_talhaoID FROM Safra LEFT JOIN PropriedadeCultivo ON Safra.propriedadecultivoID=PropriedadeCultivo.id LEFT JOIN Cultivo ON PropriedadeCultivo.cultivoID=Cultivo.id INNER JOIN Propriedade ON PropriedadeCultivo.propriedadeID=Propriedade.idLEFT JOIN Empresa ON Propriedade.empresaID=Empresa.idLEFT JOIN Subdivisao ON Safra.subdivisaoID=Subdivisao.id LEFT JOIN CategoriaSubdivisao ON Subdivisao.cateroriasubdivisaoID=CategoriaSubdivisao.id LEFT JOIN Propriedade AS Propriedade2 ON Subdivisao.propriedadeID=Propriedade2.id LEFT JOIN Empresa AS Empresa2 ON Propriedade2.empresaID=Empresa2.idLEFT JOIN Talhao ON Subdivisao.talhaoID=Talhao.idLEFT JOIN Propriedade AS Propriedade3 ON Talhao.propriedadeID=Propriedade3.idLEFT JOIN Empresa AS Empresa3 ON Propriedade3.empresaID=Empresa3.idLEFT JOIN Talhao AS Talhao2 ON Safra.talhaoID=Talhao2.idLEFT JOIN Propriedade AS Propriedade4 ON Talhao2.propriedadeID=Propriedade4.idLEFT JOIN Empresa AS Empresa4 ON Propriedade4.empresaID=Empresa4.id;, cause=android.database.sqlite.SQLiteException: no such column: Empresa2.empresaLicensaEmpresa2Id (code 1 SQLITE_ERROR): , while compiling: SELECT PropriedadeCultivo.id AS PropriedadeCultivo_id, PropriedadeCultivo.codigoDeBarra AS PropriedadeCultivo_codigoDeBarra, PropriedadeCultivo.createdAt AS PropriedadeCultivo_createdAt, PropriedadeCultivo.responsavel_atualizacao_id AS PropriedadeCultivo_responsavel_atualizacao_id, PropriedadeCultivo.responsavel_cadastro_id AS PropriedadeCultivo_responsavel_cadastro_id, PropriedadeCultivo.updatedAt AS PropriedadeCultivo_updatedAt, PropriedadeCultivo.cultivoID AS PropriedadeCultivo_cultivoID, PropriedadeCultivo.propriedadeID AS PropriedadeCultivo_propriedadeID, Propriedade.id AS Propriedade_id, Propriedade.cpf_cnpj AS Propriedade_cpf_cnpj, Propriedade.createdAt AS Propriedade_createdAt, Propriedade.nome AS Propriedade_nome, Propriedade.propriedadeEnderecoPropriedadeId AS Propriedade_propriedadeEnderecoPropriedadeId, Propriedade.responsavel_atualizacao_id AS Propriedade_responsavel_atualizacao_id, Propriedade.responsavel_cadastro_id AS Propriedade_responsavel_cadastro_id, Propriedade.updatedAt AS Propriedade_updatedAt, Propriedade.empresaID AS Propriedade_empresaID, Empresa.id AS Empresa_id, Empresa.cpf_cnpj AS Empresa_cpf_cnpj, Empresa.createdAt AS Empresa_createdAt, Empresa.email AS Empresa_email, Empresa.empresaLicensaEmpresaId AS Empresa_empresaLicensaEmpresaId, Empresa.nome AS Empresa_nome, Empresa.nome_fantasia AS Empresa_nome_fantasia, Empresa.telefone AS Empresa_telefone, Empresa.updatedAt AS Empresa_updatedAt, Empresa2.id AS Empresa_id2, Empresa2.cpf_cnpj AS Empresa_cpf_cnpj2, Empresa2.createdAt AS Empresa_createdAt2, Empresa2.email AS Empresa_email2, Empresa2.empresaLicensaEmpresa2Id AS Empresa_empresaLicensaEmpresaId2, Empresa2.nome AS Empresa_nome2, Empresa2.nome_fantasia AS Empresa_nome_fantasia2, Empresa2.telefone AS Empresa_telefone2, Empresa2.updatedAt AS Empresa_updatedAt2, Empresa4.id AS Empresa_id4, Empresa4.cpf_cnpj AS Empresa_cpf_cnpj4, Empresa4.createdAt AS Empresa_createdAt4, Empresa4.email AS Empresa_email4, Empresa4.empresaLicensaEmpresa4Id AS Empresa_empresaLicensaEmpresaId4, Empresa4.nome AS Empresa_nome4, Empresa4.nome_fantasia AS Empresa_nome_fantasia4, Empresa4.telefone AS Empresa_telefone4, Empresa4.updatedAt AS Empresa_updatedAt4, Empresa3.id AS Empresa_id3, Empresa3.cpf_cnpj AS Empresa_cpf_cnpj3, Empresa3.createdAt AS Empresa_createdAt3, Empresa3.email AS Empresa_email3, Empresa3.empresaLicensaEmpresa3Id AS Empresa_empresaLicensaEmpresaId3, Empresa3.nome AS Empresa_nome3, Empresa3.nome_fantasia AS Empresa_nome_fantasia3, Empresa3.telefone AS Empresa_telefone3, Empresa3.updatedAt AS Empresa_updatedAt3, Propriedade2.id AS Propriedade_id2, Propriedade2.cpf_cnpj AS Propriedade_cpf_cnpj2, Propriedade2.createdAt AS Propriedade_createdAt2, Propriedade2.nome AS Propriedade_nome2, Propriedade2.propriedadeEnderecoPropriedade2Id AS Propriedade_propriedadeEnderecoPropriedadeId2, Propriedade2.responsavel_atualizacao_id AS Propriedade_responsavel_atualizacao_id2, Propriedade2.responsavel_cadastro_id AS Propriedade_responsavel_cadastro_id2, Propriedade2.updatedAt AS Propriedade_updatedAt2, Propriedade2.empresaID AS Propriedade_empresaID2, Propriedade3.id AS Propriedade_id3, Propriedade3.cpf_cnpj AS Propriedade_cpf_cnpj3, Propriedade3.createdAt AS Propriedade_createdAt3, Propriedade3.nome AS Propriedade_nome3, Propriedade3.propriedadeEnderecoPropriedade3Id AS Propriedade_propriedadeEnderecoPropriedadeId3, Propriedade3.responsavel_atualizacao_id AS Propriedade_responsavel_atualizacao_id3, Propriedade3.responsavel_cadastro_id AS Propriedade_responsavel_cadastro_id3, Propriedade3.updatedAt AS Propriedade_updatedAt3, Propriedade3.empresaID AS Propriedade_empresaID3, Propriedade4.id AS Propriedade_id4, Propriedade4.cpf_cnpj AS Propriedade_cpf_cnpj4, Propriedade4.createdAt AS Propriedade_createdAt4, Propriedade4.nome AS Propriedade_nome4, Propriedade4.propriedadeEnderecoPropriedade4Id AS Propriedade_propriedadeEnderecoPropriedadeId4, Propriedade4.responsavel_atualizacao_id AS Propriedade_responsavel_atualizacao_id4, Propriedade4.responsavel_cadastro_id AS Propriedade_responsavel_cadastro_id4, Propriedade4.updatedAt AS Propriedade_updatedAt4, Propriedade4.empresaID AS Propriedade_empresaID4, Talhao2.id AS Talhao_id2, Talhao2.createdAt AS Talhao_createdAt2, Talhao2.nome AS Talhao_nome2, Talhao2.responsavel_atualizacao_id AS Talhao_responsavel_atualizacao_id2, Talhao2.responsavel_cadastro_id AS Talhao_responsavel_cadastro_id2, Talhao2.updatedAt AS Talhao_updatedAt2, Talhao2.propriedadeID AS Talhao_propriedadeID2, CategoriaSubdivisao.id AS CategoriaSubdivisao_id, CategoriaSubdivisao.createdAt AS CategoriaSubdivisao_createdAt, CategoriaSubdivisao.nome AS CategoriaSubdivisao_nome, CategoriaSubdivisao.updatedAt AS CategoriaSubdivisao_updatedAt, Talhao.id AS Talhao_id, Talhao.createdAt AS Talhao_createdAt, Talhao.nome AS Talhao_nome, Talhao.responsavel_atualizacao_id AS Talhao_responsavel_atualizacao_id, Talhao.responsavel_cadastro_id AS Talhao_responsavel_cadastro_id, Talhao.updatedAt AS Talhao_updatedAt, Talhao.propriedadeID AS Talhao_propriedadeID, Safra.id AS Safra_id, Safra.createdAt AS Safra_createdAt, Safra.dataFinalizacao AS Safra_dataFinalizacao, Safra.data_plantio AS Safra_data_plantio, Safra.finalizada AS Safra_finalizada, Safra.responsavel_atualizacao_id AS Safra_responsavel_atualizacao_id, Safra.responsavel_cadastro_id AS Safra_responsavel_cadastro_id, Safra.updatedAt AS Safra_updatedAt, Safra.propriedadecultivoID AS Safra_propriedadecultivoID, Safra.subdivisaoID AS Safra_subdivisaoID, Safra.talhaoID AS Safra_talhaoID, Cultivo.id AS Cultivo_id, Cultivo.createdAt AS Cultivo_createdAt, Cultivo.cultura AS Cultivo_cultura, Cultivo.updatedAt AS Cultivo_updatedAt, Cultivo.variedade AS Cultivo_variedade, Subdivisao.id AS Subdivisao_id, Subdivisao.complemento AS Subdivisao_complemento, Subdivisao.createdAt AS Subdivisao_createdAt, Subdivisao.responsavel_atualizacao_id AS Subdivisao_responsavel_atualizacao_id, Subdivisao.responsavel_cadastro_id AS Subdivisao_responsavel_cadastro_id, Subdivisao.subdivisao_pai AS Subdivisao_subdivisao_pai, Subdivisao.updatedAt AS Subdivisao_updatedAt, Subdivisao.cateroriasubdivisaoID AS Subdivisao_cateroriasubdivisaoID, Subdivisao.propriedadeID AS Subdivisao_propriedadeID, Subdivisao.talhaoID AS Subdivisao_talhaoID FROM Safra LEFT JOIN PropriedadeCultivo ON Safra.propriedadecultivoID=PropriedadeCultivo.id LEFT JOIN Cultivo ON PropriedadeCultivo.cultivoID=Cultivo.id INNER JOIN Propriedade ON PropriedadeCultivo.propriedadeID=Propriedade.idLEFT JOIN Empresa ON Propriedade.empresaID=Empresa.idLEFT JOIN Subdivisao ON Safra.subdivisaoID=Subdivisao.id LEFT JOIN CategoriaSubdivisao ON Subdivisao.cateroriasubdivisaoID=CategoriaSubdivisao.id LEFT JOIN Propriedade AS Propriedade2 ON Subdivisao.propriedadeID=Propriedade2.id LEFT JOIN Empresa AS Empresa2 ON Propriedade2.empresaID=Empresa2.idLEFT JOIN Talhao ON Subdivisao.talhaoID=Talhao.idLEFT JOIN Propriedade AS Propriedade3 ON Talhao.propriedadeID=Propriedade3.idLEFT JOIN Empresa AS Empresa3 ON Propriedade3.empresaID=Empresa3.idLEFT JOIN Talhao AS Talhao2 ON Safra.talhaoID=Talhao2.idLEFT JOIN Propriedade AS Propriedade4 ON Talhao2.propriedadeID=Propriedade4.idLEFT JOIN Empresa AS Empresa4 ON Propriedade4.empresaID=Empresa4.id;, recoverySuggestion=There is a possibility that there is a bug if this error persists. Please take a look at 
https://github.com/aws-amplify/amplify-android/issues to see if there are any existing issues that 
match your scenario, and file an issue with the details of the bug if there isn't.}))

[HuiSF]

That exception reminds me about this amplify-android issue aws-amplify/amplify-android#2035
And I saw you commented under @RobsonT .

But that should be a separate issue from foreign key constraint error. Lets focus on foreign key related issue here.
Could you try to dump the local DB for me to inspect if the data actually gets sync into the local DB.

You can do this by

1. Open your Flutter Android project by Android studio (using Android Studio) to open at the directory <your_flutter_project>/android
2. Got to Android Studio menu View -> Tool Windows -> Device File Explore
3. You should see a window opened and shows the file content of connected device
4. In this window navigate to data/data/<your_application_id>/databases you should see a file named AmplifyDataStore.db
5. Right click on this file, choose "save as..." to save the db file, then upload in this issue thread

In addition, please provide complete App running log, starting from App launch till the exception occurs.

So far I saw all the exception logs from Android, you also checked this issue is happening on iOS, please confirm if you are actually experiencing the same issue on iOS.

[RobsonT]

Here is the logs on android @HuiSF . It's been a while since I ran it on ios, but there it was with the same error. I will test again and let you know if the error continues.

logs.txt
databases.zip

[HuiSF]

Thanks for providing the attachments @RobsonT

I took a look at the logs, the original foreign key constraint error seems no long appearing in your App after syncing data. Which I believe re-inserting data fixed it. The only issue I can see from the log is the linked android issue, while querying join table.

I've spotted the issue in the SQL command that amplify-android generates, exactly the same cause described in the linked amplify-android issue. I will reach out to the amplify-android maintainers and try to get the fix prioritized.

[RobsonT]

Hello @HuiSF,

Do you know when the fix will be available?

[HuiSF]

Hi @RobsonT we are expecting the fix for 0.6.x version to be released by this week.

[HuiSF]

hi @RobsonT amplify-flutter 0.6.13 has been released, which, includes the fix merged in amplify-android to resolve the last issue we found in your use case of invalid SQL command. Please upgrade to gain the fix, and let me know if anything else. Thanks.

[RobsonT]

Hello @HuiSF,

sorry for the delay in responding. We have tested the new version and confirm that the problem has been resolved. Thanks.

[HuiSF]

Thanks for confirming and the feedback!
Closing as completed.