Getting duplicate rows when querying Cloud SQL in AppMaker

Question

I migrated from Drive tables to a 2nd gen MySQL Google Cloud SQL data model. I was able to insert 19 rows into the following Question table in AppMaker:

+-------------------+--------------+------+-----+---------+-------+
| Field             | Type         | Null | Key | Default | Extra |
+-------------------+--------------+------+-----+---------+-------+
| SurveyType        | varchar(64)  | NO   | PRI | NULL    |       |
| QuestionNumber    | int(11)      | NO   | PRI | NULL    |       |
| QuestionType      | varchar(64)  | NO   |     | NULL    |       |
| Question          | varchar(512) | NO   |     | NULL    |       |
| SecondaryQuestion | varchar(512) | YES  |     | NULL    |       |
+-------------------+--------------+------+-----+---------+-------+

I queried the data from the command line and know it is good. However, when I query the data in AppMaker like this:

var newQuery = app.models.Question.newQuery();
newQuery.filters.SurveyType._equals = surveyType;
newQuery.sorting.QuestionNumber._ascending();  
var allRecs = newQuery.run();

I get 19 rows with the same data (the first row) instead of the 19 different rows. Any idea what is wrong? Additionally (and possibly related) my list rows in AppMaker are not showing any data. I did notice that _key is not being set correctly in the records.

(Edit: I thought maybe having two columns as the primary key was the problem, but I tried having the PK be a single identity column, same result.)

Thanks for any tips or pointers.

Steve Johnson Steve Johnson · Accepted Answer · 2017-03-22T18:33:02

You have two primary key fields in your table, which is problematic according to the App Maker Cloud SQL documentation: https://developers.google.com/appmaker/models/cloudsql

App Maker can only write to tables that have a single primary key field—If you have an existing Google Cloud SQL table with zero or multiple primary keys, you can still query it in App Maker, but you can't write to it.

This may account for the inability of the view to be able to properly display each row and to properly set the _key.

Getting duplicate rows when querying Cloud SQL in AppMaker

2 Answers