Flutter (app) to MySQL(Hosted on Google Cloud) via use of the PHP-CRUD-API Diagnoses and Resolution

flutter_mysql_notes.txt

Note: This example contains Chinese text and a payload of nested JSON. The payload had to be encoded as a string before being passed on to the API. I found that using the flutter http package one has to use a workaround to decode the characters properly (dart-lang/http#243). The alternative is to use the dio package which encodes everything as utf-8 properly. When decoding the get request, one has to remember to also decode the nested json separately.

Fix for others having this issue:

Flutter has a new network profiler in the Dart tools. Using this I was able to see that an error returned that Field payload does not have a default value.

Solution:
Add a default NULL value to the payload field and with that inserts are now working.
My payload is a json field so I had to jsonEncode(payload) before adding it to the post (in my toJson method)
Also found that I could simply wrap the entire jsonEncode(post) with single quotes
'$jsonString' without escaping the quotes in the use case below (code)

CREATE TABLE `posts` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `payload` json DEFAULT NULL,
  `donorid` char(36) DEFAULT NULL,
  `categories` varchar(25) DEFAULT NULL,
  `srclang` int(11) DEFAULT NULL,
  `votes` int(11) DEFAULT NULL,
  `datecreated` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `datemodified` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4;
Current Error
flutter: '{"payload":{"normal":"王先生","phonetic":"Wáng xiān shēng","translation":"Mister Wong"},"categories":"testing"}'
flutter: {"code":1010,"message":"Data integrity violation"}

UPDATES
Adjusting the post to enforce a single quote surrounding the json encoded string delivers the following error message:

code change >>> body: '\'$jsonString\'')
produces:
flutter: {"code":1010,"message":"Data integrity violation"}
btw:
Same thing if I place double quotes here...

??? Does API correctly forward a JSON object to a JSON field?

Original Error
Object of class stdClass could not be converted to string in ... on line 5580
API Error - Catchable fatal error: Object of class stdClass could not be converted to string in /data/.../api/index.php on line 5580

Environment:
Hosting the API script at a website and connect to a Google MySQL instance.
Attempting the post from a Flutter application (iOS Sim & Device)
Noteworthy - This version of MySQL (57.x) supports JSON column type and I've confirmed that I can insert the JSON payload using the Sequel Pro tool.

Table Structure
CREATE TABLE posts (
id int(11) unsigned NOT NULL AUTO_INCREMENT,
payload json NOT NULL,
donorid char(36) DEFAULT NULL,
categories varchar(25) DEFAULT NULL,
srclang int(11) DEFAULT NULL,
votes int(11) DEFAULT NULL,
datecreated datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
datemodified timestamp NULL DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4;

The Dart code:
import 'dart:convert';

import 'package:http/http.dart' as http;

createPost(Map<String, dynamic> content) {
var jsonString = jsonEncode(content);
print('encoded json string $jsonString');
http
.post(
'http://.../api.php/records/posts',
headers: <String, String>{
'Content-Type': 'application/json; charset=UTF-8', //;
},
body: jsonString)
.then((value) {
print(value.body);
}).catchError(handlePostingError);
}

handlePostingError(dynamic response) {
print('httpErrorHandled- $response');
}

The following are the current test results:

All feedback welcome

Test of php-crud-api file: Attempt to identify correct method of posting to GCP/MySQL instance.

URL: http://.../api.php/records/posts

Verified that behavior is same if the file is renamed: http://.../index.php/records/posts

Confirmed that direct posting of an encoded JSON String works using Sequel Pro (SP) from (outside of the API): e.g. from Flutter produced a json encoded string and verified that I can stick it into the table using SP.

Note - Double quotes were not a problem in the SP UI

{"id":null,"payload":{"normal":"郭先生","phonetic":"Guō xiān shēng","translation":"Mister Guo"},"donorid":null,"categories":"testing","srclang":8,"votes":null,"datecreated":"2020-05-15T19:20:31.782268","datemodified":"2020-05-15T19:20:31.782265"}

Confirmed that direct posting of unencoded JSON String FAILS when using SP.

{normal: 郭先生, phonetic: Guō xiān shēng, translation: Mister Guo}, donorid: null, categories: testing, srclang: 8, votes: null, datecreated: 2020-05-15T19:20:31.782268, datemodified: 2020-05-15T19:20:31.782265}

Confirmed the IP address of the source website hosting the API: ccc.xy.xxz.xab

Confirm the source website address is on list of authorized access to connect to the GCP/MySQL instance.

Note - Future.catchError argument must be dynamic or one gets the following error:
The following ArgumentError was thrown while handling a gesture:
Invalid argument (onError): Error handler must accept one Object or one Object and a StackTrace as arguments, and return a a valid result: Closure: (Response) => dynamic from Function 'handlePostingError':.

API Error - Catchable fatal error: Object of class stdClass could not be converted to string in /data/.../api/index.php on line 5580

Note - Similar error observed on this reported issue - #630

Headers monitored in Flutter Dev Tools seem correct - 200 received

Connection to SQL monitored from different IP address and updated.

SQL Log shows connection but no evidence of submission.

SHOW PROCESSLIST on MySQL client shows the connection from the api host when I initiate a test from the app